← ServerPilot Docs

How to Use MySQL from the Command Line

Introduction

MySQL is a database server that your apps such as WordPress use to store data. In some cases, you may want to interact with MySQL while SSH'd into your server. To do that, you can use MySQL's command line tools.

Accessing the MySQL Shell

While SSH'd into your server as root, you can open a MySQL shell with the command:

sudo -i mysql

To open a MySQL shell while SSH'd in as your app's system user, run the command:

mysql --user DATABASE_USERNAME --password

Enter the database name and the username for that database as created through the ServerPilot control panel. You'll be prompted for the database user's password.

Using the MySQL Shell

One you open a MySQL shell, you'll see a prompt similar to this:

mysql>

At that point, run the command use DATABASE_NAME; to switch to a specific database. For example, if you had a database called wordpress, you'd type and then see the following output:

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

At that point, you can enter any SQL commands to run on the database.

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

Using the MySQL CLI

You can also use the mysql command to execute SQL queries without opening an interactive shell. To do this, enter the name of the database on the command line use the -e flag to pass a SQL query to execute. The general form of the command is:

mysql DATABASE_NAME --user DATABASE_USERNAME --password -e "SQL_QUERY"

Following our example above where we have a database called wordpress, you could run the following command while SSH'd in to your server as root without providing a database username or password:

mysql wordpress --user DATABASE_USERNAME --password -e "SELECT COUNT(*) from wp_posts;"

You will be prompted for the database password when you run these commands.