How To Use MySQL / MariaDB From Command Line

While tools like phpMyAdmin interact with MySQL / MariaDB databases very easily, sometimes one must access the database directly from the command line. This article will touch on getting into a database and some common tasks but will not provide a full education on SQL syntax, database management, or other high-level topics. The examples in this guide are for CentOS 7 and MariaDB as included in our WordPress VPS image but should work on our cPanel VPSes, LAMP stack, and others. This page assumes you have Connected to Your Server with SSH.

prompts indicate something that should be entered from the Bash command line, > prompts are inside MySQL itself.

Common MySQL Tasks Done Through Command Line

Login To MySQL Database

To log in to the database as the root user, use the following command:

mysql -u root -p

Enter the root password.

Reset Your MySQL password

On CentOS 7:

systemctl stop mariadb

mysqld_safe --skip-grant-tables --skip-networking &
MySQL -u root

plaintext use mysql; update user set password=PASSWORD("InsertPasswordHere") where User='root'; where 'InsertPasswordHere' is a real password flush privileges; exit

systemctl stop mariadb
systemctl start mariadb

(Other systemd-based Linux distros may have similar commands depending on whether they run actual MySQL or MariaDB; other init systems will be different)

Once you run a the command below and enter your password, you will be presented with a prompt that tells you the program really running (MariaDB) and the database is used:

mysql -u root -p 

MariaDB [(none)]>

List Your Databases

Issue the show databases;  command, as seen below, to see all databases. An example is shown below:

MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| MySQL |
| performance_schema |
| tutorials |
+--------------------+
4 rows in set (0.00 sec)

Switch Database With The 'use ' command:

MariaDB [(none)]> use tutorials;
Database changed
MariaDB [tutorials]>

The 'show ' command is also used to list the tables in a database:

MariaDB [mysql]> show tables;
+---------------------------+
| Tables_in_mysql |
+---------------------------+
| columns_priv |
| db |
| event |
| func |
| general_log |
| help_category |

Always Take a Backup Before Making ANY Changes

Use mysqldump to make a backup of your database before continuing with this guide is strongly recommended.

mysqldump database name > databasebackup.sql

Replace the database name with your actual database name and database backup with the name of the file you would like to create and end it with .sql as the type of file to save your database. This will allow you to restore MySQL Databases Using mysqldump from this backup file at any time.

We recommend that you run this command from a directory that is not publicly accessible so that your database cannot be downloaded from your account without logging into the command line or FTP. Make sure to change your directory to /root or /home or another location in your file system that requires proper credentials.

Example: Resetting the WordPress Admin password

See the above instructions on how to take a backup of your database before proceeding.

Step One: You must know what database, username, and password are being used by the WordPress installation. These are in wp-config.php in the root directory of your WordPress installation as DB_NAME, DB_USER, and DB_PASSWORD:

// ** MySQL settings ** //
/** The name of the database for WordPress */
define( 'DB_NAME', 'appdb' );

/** MySQL database username */
define( 'DB_USER', 'appdb_user' );

/** MySQL database password */
define( 'DB_PASSWORD', '...' );

Step Two: With this information, you can adapt the instructions from How to Reset WordPress Admin Password and do the same from the command line:

mysql -u appdb_user -p
Enter password:
Welcome to the MariaDB monitor. Commands end with; or \g

.....

MariaDB [(none)]>

Step Three: Switch to the appdb database:

MariaDB [(none)]> use appdb
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

Step Four: and show the tables:

MariaDB [appdb]> show tables;
+-----------------------+
| Tables_in_appdb    |
+-----------------------+
| wp_commentmeta  |
| wp_comments         |
| wp_links |
| wp_options |
| wp_postmeta |
| wp_posts |
| wp_term_relationships |
| wp_term_taxonomy |
| wp_termmeta |
| wp_terms |
| wp_usermeta |
| wp_users |
+-----------------------+
12 rows in set (0.00 sec)

MariaDB [appdb]>

Step Five: Then, we can SELECT user_login  and user_pass  from the wp_users table to see what row we will be updating:

MariaDB [appdb]> SELECT user_login,user_pass FROM wp_users;
+--------------+------------------------------------+
| user_login | user_pass |
+--------------+------------------------------------+
| hstwnd_admin | $P$BMCbERthYHPMJK.do2SUcxjAy567jo0 |
+--------------+------------------------------------+
1 row in set (0.00 sec)

Step Six: Which lets us set the new password with

MariaDB [appdb]> UPDATE wp_users SET user_pass=MD5('newpassword') WHERE user_login = 'hstwnd_admin';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

Step Seven: And we can see the new password hash with the same SELECT again

MariaDB [appdb]> SELECT user_login,user_pass FROM wp_users;
+--------------+----------------------------------+
| user_login | user_pass |
+--------------+----------------------------------+
| hstwnd_admin | 5e9d11a14ad1c8dd77e98ef9b53fd1ba |
+--------------+----------------------------------+
1 row in set (0.00 sec)

To leave, we run 'quit'

MariaDB [appdb]> quit
Bye

That's it! Your new admin password is set.