Use MySQL from the command line

The MySQL CLI allows you to run queries, export databases, and import databases from the command line.

Default MySQL credentials

You can create a .my.cnf file in a system user’s home directory to avoid needing to provide the database name, user, and password as arguments to MySQL CLI commands when you are SSH’d into the server as that system user.

The format of the file is:

~/.my.cnf

[client]
user = MYSQL_USER
password = MYSQL_PASSWORD
database = MYSQL_DATABASE

By default, only the system user root has a .my.cnf file.

MySQL root user

The MySQL root user has administrative privileges within MySQL and can access and manage every database.

Note

When you run MySQL CLI commands while logged into a server as the system user root, the MySQL root user’s credentials in /root/.my.cnf will be used by default unless your command specifies the -u/--user and -p/--password flags.

The MySQL root user’s password is in the file:

/root/.my.cnf

To view the file while logged into a server as the root system user, use the cat command.

sudo cat /root/.my.cnf

Execute a SQL query

To execute a SQL query, use the following command. Replace DB_USER with the database username, replace DB_NAME with the name of the database, and replace SQL_QUERY with the query to execute. You’ll be prompted for the database user’s password.

mysql DB_NAME -p -u DB_USER -e "SQL_QUERY"

Export a database

Note

To export a MySQL database with your browser, use Adminer or phpMyAdmin.

Caution

If you are SSH’d into your server as the system user root, do not leave off the DB_NAME argument when exporting a database or the resulting export will break your server when imported.

The command to export a database depends on whether you want to create a compressed or an uncompressed export file.

In the commands shown, replace DB_USER with the database username and replace DB_NAME with the name of the database. You’ll be prompted for the database user’s password.

compressed (.sql.gz)

mysqldump --single-transaction --no-tablespaces -p -u DB_USER DB_NAME | gzip > DB_NAME.sql.gz

uncompressed (.sql)

mysqldump --single-transaction --no-tablespaces -p -u DB_USER DB_NAME > DB_NAME.sql

The export can take many minutes to complete if the database is large. A successful export will not print any output.

Import a database

Note

To import a MySQL database with your browser, use Adminer or phpMyAdmin.

Caution

If you are SSH’d into your server as the system user root, importing from a file that contains exports of internal MySQL databases will break your server.

The command to import a database depends whether you are importing from a compressed or an uncompressed file.

compressed (.sql.gz)

Replace DB_USER with the database username and replace DB_NAME with the name of the database. You’ll be prompted for the database user’s password.

zcat DB_NAME.sql.gz | mysql -p -u DB_USER DB_NAME

uncompressed (.sql)

Replace DB_USER with the database username and replace DB_NAME with the name of the database. You’ll be prompted for the database user’s password.

cat DB_NAME.sql | mysql -p -u DB_USER DB_NAME

The import can take many minutes to complete if the database is large. A successful import will not print any output.

Interactive shell

To open an interactive MySQL shell, use the following command. Replace DB_USER with the database username. You’ll be prompted for the database user’s password.

mysql -u DB_USER -p

In the MySQL shell, run the following command to set the database that will be used for subsequent commands. Replace DB_NAME with the name of the database.

use DB_NAME;

For example, if your database was named wordpress:

mysql> use wordpress;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed

You can now execute SQL queries.

mysql> SELECT COUNT(*) from wp_posts;
+----------+
| COUNT(*) |
+----------+
|        6 |
+----------+
1 row in set (0.03 sec)