MySQL Command Line

Introduction

The MySQL Command Line Interface (CLI) is a powerful tool that allows you to interact with your MySQL database server directly from a terminal. While graphical interfaces have their place, mastering the command line gives you more control, better performance, and the ability to script and automate database operations. Whether you're managing a production database or just learning SQL, the command line is an essential skill for any database administrator or developer.

In this tutorial, we'll explore how to connect to MySQL servers, execute queries, manage databases and tables, and perform common administrative tasks—all from the command line.

Getting Started with MySQL CLI

Connecting to MySQL Server

Before you can start working with MySQL, you need to connect to a MySQL server. The basic command to connect is:

bash

mysql -u username -p

This prompts for the password of the specified user. For security reasons, the password isn't shown as you type.

If you need to connect to a MySQL server on a different host:

bash

mysql -h hostname -u username -p

Example:

bash

mysql -h localhost -u root -p
Enter password: ********

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 12
Server version: 8.0.28 MySQL Community Server

Basic MySQL CLI Commands

Once connected, you can start using MySQL commands. Here are some essential commands to know:

Command	Description
SHOW DATABASES;	Lists all databases
USE database_name;	Selects a database to work with
SHOW TABLES;	Lists all tables in the current database
DESCRIBE table_name;	Shows the structure of a table
EXIT; or QUIT;	Exits the MySQL CLI
\h or help	Shows help
\c	Cancels the current input
source filename.sql	Executes SQL from a file

Note: MySQL commands are not case-sensitive, but conventionally are written in uppercase to distinguish them from database names, table names, etc. All commands must end with a semicolon ;.

Creating and Managing Databases

Let's start by creating a new database:

sql

CREATE DATABASE my_bookstore;

Output:

Query OK, 1 row affected (0.02 sec)

Now select this database to work with:

sql

USE my_bookstore;

Output:

Database changed

To see all your databases:

sql

SHOW DATABASES;

Output:

+--------------------+
| Database           |
+--------------------+
| information_schema |
| my_bookstore       |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

Working with Tables

Creating Tables

Let's create a simple books table in our bookstore database:

sql

CREATE TABLE books (
    book_id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(100) NOT NULL,
    author VARCHAR(50) NOT NULL,
    price DECIMAL(6,2),
    publish_date DATE,
    stock INT DEFAULT 0
);

Output:

Query OK, 0 rows affected (0.05 sec)

Checking Table Structure

To see the structure of your newly created table:

sql

DESCRIBE books;

Output:

+--------------+---------------+------+-----+---------+----------------+
| Field        | Type          | Null | Key | Default | Extra          |
+--------------+---------------+------+-----+---------+----------------+
| book_id      | int           | NO   | PRI | NULL    | auto_increment |
| title        | varchar(100)  | NO   |     | NULL    |                |
| author       | varchar(50)   | NO   |     | NULL    |                |
| price        | decimal(6,2)  | YES  |     | NULL    |                |
| publish_date | date          | YES  |     | NULL    |                |
| stock        | int           | YES  |     | 0       |                |
+--------------+---------------+------+-----+---------+----------------+
6 rows in set (0.00 sec)

Inserting Data

Now let's add some books to our table:

sql

INSERT INTO books (title, author, price, publish_date, stock) VALUES 
('The Great Gatsby', 'F. Scott Fitzgerald', 12.99, '1925-04-10', 25),
('To Kill a Mockingbird', 'Harper Lee', 14.95, '1960-07-11', 30),
('1984', 'George Orwell', 11.99, '1949-06-08', 18),
('Pride and Prejudice', 'Jane Austen', 9.99, '1813-01-28', 15);

Output:

Query OK, 4 rows affected (0.01 sec)
Records: 4  Duplicates: 0  Warnings: 0

Querying Data

Let's retrieve the data we just inserted:

sql

SELECT * FROM books;

Output:

+---------+----------------------+--------------------+-------+--------------+-------+
| book_id | title                | author             | price | publish_date | stock |
+---------+----------------------+--------------------+-------+--------------+-------+
|       1 | The Great Gatsby     | F. Scott Fitzgerald| 12.99 | 1925-04-10   |    25 |
|       2 | To Kill a Mockingbird| Harper Lee         | 14.95 | 1960-07-11   |    30 |
|       3 | 1984                 | George Orwell      | 11.99 | 1949-06-08   |    18 |
|       4 | Pride and Prejudice  | Jane Austen        |  9.99 | 1813-01-28   |    15 |
+---------+----------------------+--------------------+-------+--------------+-------+
4 rows in set (0.00 sec)

You can also run more specific queries:

sql

SELECT title, author, price FROM books WHERE price < 12.00;

Output:

+---------------------+-------------+-------+
| title               | author      | price |
+---------------------+-------------+-------+
| Pride and Prejudice | Jane Austen |  9.99 |
+---------------------+-------------+-------+
1 row in set (0.00 sec)

Advanced Command Line Features

Using Command Line Options

MySQL CLI comes with many options that can be used when connecting:

bash

mysql -u root -p -e "SELECT VERSION();"

The -e option executes the SQL command and exits.

Output:

Enter password: 
+------------+
| VERSION()  |
+------------+
| 8.0.28     |
+------------+

Importing and Exporting Data

To import a SQL file:

bash

source /path/to/file.sql;

Or from outside MySQL:

bash

mysql -u username -p database_name < file.sql

To export data:

bash

mysqldump -u username -p database_name > backup.sql

Command History and Editing

MySQL CLI keeps a history of your commands. You can:

• Press the up arrow key to access previous commands
• Use CTRL + R to search through command history
• Use \p to display the current command without executing it

Formatting Output

For better readability, you can format the output:

sql

SELECT * FROM books\G

This displays the output vertically, one record at a time:

*************************** 1. row ***************************
    book_id: 1
      title: The Great Gatsby
     author: F. Scott Fitzgerald
      price: 12.99
publish_date: 1925-04-10
      stock: 25
*************************** 2. row ***************************
    book_id: 2
      title: To Kill a Mockingbird
     author: Harper Lee
      price: 14.95
...

Practical Examples

Example 1: Creating a Users and Orders System

Let's create a more complex example with related tables:

sql

-- Create users table
CREATE TABLE users (
    user_id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL,
    registration_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Create orders table with a foreign key
CREATE TABLE orders (
    order_id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    book_id INT NOT NULL,
    quantity INT DEFAULT 1,
    order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(user_id),
    FOREIGN KEY (book_id) REFERENCES books(book_id)
);

-- Insert some users
INSERT INTO users (username, email) VALUES
('bookworm', '[email protected]'),
('reader123', '[email protected]');

-- Insert sample orders
INSERT INTO orders (user_id, book_id, quantity) VALUES
(1, 1, 2),  -- bookworm ordered 2 copies of The Great Gatsby
(1, 3, 1),  -- bookworm also ordered 1984
(2, 4, 3);  -- reader123 ordered 3 copies of Pride and Prejudice

Example 2: Generate a Sales Report

Now let's run a query that joins multiple tables to create a sales report:

sql

SELECT 
    o.order_id,
    u.username,
    b.title,
    o.quantity,
    b.price,
    (o.quantity * b.price) AS total_price,
    o.order_date
FROM 
    orders o
JOIN 
    users u ON o.user_id = u.user_id
JOIN 
    books b ON o.book_id = b.book_id
ORDER BY 
    o.order_date;

Output:

+----------+------------+----------------------+----------+-------+-------------+---------------------+
| order_id | username   | title                | quantity | price | total_price | order_date          |
+----------+------------+----------------------+----------+-------+-------------+---------------------+
|        1 | bookworm   | The Great Gatsby     |        2 | 12.99 |       25.98 | 2023-04-10 15:30:22 |
|        2 | bookworm   | 1984                 |        1 | 11.99 |       11.99 | 2023-04-10 15:30:22 |
|        3 | reader123  | Pride and Prejudice  |        3 |  9.99 |       29.97 | 2023-04-10 15:30:22 |
+----------+------------+----------------------+----------+-------+-------------+---------------------+
3 rows in set (0.00 sec)

Example 3: Database Management Tasks

Here's how to perform some common database maintenance tasks:

sql

-- Check for any issues in the tables
CHECK TABLE books, users, orders;

-- Optimize a table after many deletions
OPTIMIZE TABLE books;

-- Get information about a table
SHOW TABLE STATUS LIKE 'books';

-- View all user privileges
SHOW GRANTS FOR 'root'@'localhost';

-- Create a new user with limited permissions
CREATE USER 'bookstore_app'@'localhost' IDENTIFIED BY 'secure_password';
GRANT SELECT, INSERT ON my_bookstore.* TO 'bookstore_app'@'localhost';

Tips and Best Practices

1. Use SQL files for complex queries: For long or frequently used queries, save them in .sql files and execute them using the source command.

2. Create a .my.cnf file in your home directory to store frequently used options:

   [mysql]
   user=your_username
   password=your_password
   host=localhost

   Make sure to secure this file with appropriate permissions: chmod 600 ~/.my.cnf

3. Use aliases for common commands:

   sql

   -- Create an alias for a complex query
   delimiter //
   CREATE PROCEDURE book_inventory()
   BEGIN
     SELECT title, author, stock FROM books ORDER BY stock;
   END //
   delimiter ;
   
   -- Now just call it with:
   CALL book_inventory();

4. Use command-line editing effectively:

   • CTRL + A: Move to beginning of line
   • CTRL + E: Move to end of line
   • CTRL + K: Delete from cursor to end of line
   • CTRL + U: Delete from cursor to beginning of line

5. Always end transactions properly: Use COMMIT; or ROLLBACK; to properly complete transactions.

Common Troubleshooting

• Connection issues: If you can't connect, check if the MySQL service is running with systemctl status mysql or service mysql status.

• Access denied errors: Make sure the user has the correct permissions for the operation you're trying to perform.

• Syntax errors: Check your SQL syntax. MySQL will tell you where the error occurred.

• Lost connection: If you experience timeouts, you can adjust the wait_timeout variable or use SET SESSION wait_timeout=28800; for longer sessions.

Summary

The MySQL command line interface is a powerful tool for database management and operations. In this guide, we've covered:

• Basic connection and navigation commands
• Creating and managing databases and tables
• Inserting and querying data
• Advanced CLI features like importing/exporting and output formatting
• Practical examples including multi-table operations and reports
• Tips and best practices for efficient command-line usage

Mastering the MySQL CLI gives you direct control over your databases and is an essential skill for anyone working with MySQL. While graphical tools have their place, the command line offers speed, flexibility, and the ability to script and automate your database operations.

Additional Resources and Exercises

Exercises

1. Create a new database called practice_db and a table called employees with the following columns: id, first_name, last_name, department, hire_date, and salary.

2. Insert at least 5 records into your employees table.

3. Write a query that shows the average salary by department.

4. Create a backup of your database using mysqldump.

5. Create a stored procedure that returns all employees hired in a specific year (passed as parameter).

Additional Resources

• MySQL Official Documentation
• MySQL Command-Line Cheat Sheet
• MySQL Workbench - For when you need a GUI
• Online courses on platforms like Udemy, Coursera, and LinkedIn Learning that focus on MySQL database administration

Remember that regular practice with the command line will help you become comfortable and efficient with MySQL operations. Happy querying!