How do you show and update a MySQL database in a linux terminal?

This post will walk you through the process of showing and updating a MySQL database via terminal.

We’ll use a WordPress installation because it has an established database schema. The principles apply to any database.

You log into your database like this:

# mysql -u [database-user] -p

[enter your password]

Once logged in, view the tables you have access to:

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| wordpress          |
+--------------------+
2 rows in set (0.01 sec)

Set the database you want to work in like this:

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 have been asked to update the admin users email inside the database. A quick Google search shows us that all WordPress users are stored inside the wp_users table.

We verify all the tables in the database:

mysql> show tables;
+-----------------------+
| Tables_in_wordpress   |
+-----------------------+
| 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              |
| wp_yoast_seo_links    |
| wp_yoast_seo_meta     |
+-----------------------+
14 rows in set (0.00 sec)

Now we check to see what is inside wp_users table:

mysql> select * from wp_users;

This provides an output like this:

+----+---------------------+------------------------------------+---------------------+--------------+----------+---------------------+---------------------+-------------+---------------------+
| ID | user_login          | user_pass                          | user_nicename       | user_email   | user_url | user_registered     | user_activation_key | user_status | display_name        |
+----+---------------------+------------------------------------+---------------------+--------------+----------+---------------------+---------------------+-------------+---------------------+
|  1 | wordpress-admin     | adfadsfasdfasdfasdfasdfasasdfa   . | wordpress-admin | admin@email.com |          | 2018-11-17 06:53:57 |                     |           0 | wordpress-admin |
+----+---------------------+------------------------------------+---------------------+--------------+----------+---------------------+---------------------+-------------+---------------------+
1 row in set (0.00 sec)

So we want to update wordpress-admin users email from admin@email.com to master@email.com.

mysql> UPDATE wp_users SET user_email = 'master@email.com' WHERE user_login = 'wordpress-admin';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

Just like that you’ve updated your MySQL table via terminal. For reference, here is a great cheat sheet: http://g2pc1.bu.edu/~qzpeng/manual/MySQL%20Commands.htm

Sharing is caring!

Leave a Reply Cancel reply