Basic MySQL Command Line Usage on Ubuntu 22.04

Using a MySQL server by a CLI can be somewhat daunting at first, but it is really quite useful! Here's some basic commands to get started.

System CLI vs. MySQL CLI

To log access the MySQL CLI enter the following command in your system CLI:

mysql -u root -p

Where "root" is the user you want to log into the server (the string after the -u flag indicates the user). After you have created other users, you can log in using the same method with those other users. The -p flag will prompt you for the password for that user in the command line after pressing enter.

After entering the password for the MySQL user in the command prompt, your CLI prompt will change from something like:

user@system: 

to:

mysql>

To exit the MySQL command line and return to the system CLI, simply type exit; and press enter:

mysql> exit;

Listing All MySQL Databases

Once logged into the MySQL CLI, show / list all of the databases on the server by entering the following (including the semi-colon):

SHOW DATABASES;

Which should output in your terminal a one-column table like:

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
+--------------------+
3 rows in set (0.00 sec)

Create a MySQL Database by Command Line

While logged into the MySQL server with your root or user-created MySQL user account, enter the following to create a database named andromeda:

CREATE DATABASE andromeda;

The database should be created successfully, and the terminal will display:

mysql> CREATE DATABASE andromeda;
Query OK, 1 row affected (0.01)

You can specify the the character set and collation for a database when creating it by appending CHARACTER SET and COLLATE to the CREATE DATABASE command, like so:

CREATE DATABASE orion CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;

To see the available character sets and their default collations on the MySQL server, enter the following at mysql>

SHOW CHARACTER SET;

Exporting a MySQL Database by Command Line

It is possible to export (i.e., 'dump') a MySQL database from the system command line, without logging into the MySQL server, by the following, where root is the user, taurus is the name of the database you want to export, and taurus_backup.sql is the name of the exported database file:

mysqldump -u root -p taurus > taurus_backup.sql