Import a WordPress database dump into MySQL easily.

Posted: November 24, 2017. At: 10:29 AM. This was 3 weeks ago. Post ID: 11544
Page permalink.
WordPress uses cookies, or tiny pieces of information stored on your computer, to verify who you are. There are cookies for logged in users and for commenters. These cookies expire two weeks after they are set.

Importing a WordPress database dump into MySQL is very easy, I downloaded a very old backup of my old website, and I wanted to see what was in it. So I installed MySQL and then I had to create the database.

mysql> CREATE DATABASE bejiitas_wrath;

This was necessary, as importing the database would not work without it. Then I can import the database dump from the Hostgator backup archive.

[email protected]:/etc/mysql$ mysql -uroot -p bejiitas_wrath < bejiitas_wrath.sql

This successfully imported the data into the MySQL database server.

Now switch to your new database.

mysql> use bejiitas_wrath;

Now it is possible to view the data in a database.

mysql> show tables;
+--------------------------------+
| Tables_in_bejiitas_wrath       |
+--------------------------------+
| wp_commentmeta                 |
| wp_comments                    |
| wp_links                       |
| wp_makroncommentmeta           |
| wp_makroncomments              |
| wp_makronlinks                 |
| wp_makronoptions               |
| wp_makronpopularpostsdata      |
| wp_makronpopularpostsdatacache |
| wp_makronpostmeta              |
| wp_makronposts                 |
| wp_makronterm_relationships    |
| wp_makronterm_taxonomy         |
| wp_makronterms                 |
| wp_makronusermeta              |
| wp_makronusers                 |
| wp_options                     |
| wp_popularpostsdata            |
| wp_popularpostsdatacache       |
| wp_postmeta                    |
| wp_posts                       |
| wp_redirection_groups          |
| wp_redirection_items           |
| wp_redirection_logs            |
| wp_redirection_modules         |
| wp_sam_ads                     |
| wp_sam_blocks                  |
| wp_sam_places                  |
| wp_sam_zones                   |
| wp_term_relationships          |
| wp_term_taxonomy               |
| wp_terms                       |
| wp_usermeta                    |
| wp_users                       |
+--------------------------------+
34 rows in set (0.00 sec)

This is an example of searching the users table for names that match a certain wildcard.

mysql> select * from wp_users where user_login rlike "admi*";

Find all users registered on your WordPress website with a website URL.

mysql> select * from wp_users where user_url rlike "[A-Z]";

Find all WordPress user accounts that were registered in 2012.

mysql> select * from wp_users where user_registered rlike "2012";

This is how to list all fields in a MySQL table.

mysql> describe wp_comments;
+----------------------+---------------------+------+-----+---------------------+----------------+
| Field                | Type                | Null | Key | Default             | Extra          |
+----------------------+---------------------+------+-----+---------------------+----------------+
| comment_ID           | bigint(20) unsigned | NO   | PRI | NULL                | auto_increment |
| comment_post_ID      | bigint(20) unsigned | NO   | MUL | 0                   |                |
| comment_author       | tinytext            | NO   |     | NULL                |                |
| comment_author_email | varchar(100)        | NO   |     |                     |                |
| comment_author_url   | varchar(200)        | NO   |     |                     |                |
| comment_author_IP    | varchar(100)        | NO   |     |                     |                |
| comment_date         | datetime            | NO   |     | 0000-00-00 00:00:00 |                |
| comment_date_gmt     | datetime            | NO   | MUL | 0000-00-00 00:00:00 |                |
| comment_content      | text                | NO   |     | NULL                |                |
| comment_karma        | int(11)             | NO   |     | 0                   |                |
| comment_approved     | varchar(20)         | NO   | MUL | 1                   |                |
| comment_agent        | varchar(255)        | NO   |     |                     |                |
| comment_type         | varchar(20)         | NO   |     |                     |                |
| comment_parent       | bigint(20) unsigned | NO   | MUL | 0                   |                |
| user_id              | bigint(20) unsigned | NO   |     | 0                   |                |
+----------------------+---------------------+------+-----+---------------------+----------------+
15 rows in set (0.00 sec)

How to count entries in a MySQL database table.

mysql> select count(*) from wp_comments where comment_author rlike "super";
+----------+
| count(*) |
+----------+
|       11 |
+----------+
1 row in set (0.00 sec)

Count the number of posts on your WordPress blog.

mysql> select count(*) from wp_makronposts;
+----------+
| count(*) |
+----------+
|      819 |
+----------+
1 row in set (0.00 sec)

These MySQL tips will be very helpful to anyone that is working with databases and wishes to know some wildcard tips to searching WordPress database dumps.

No comments have been made. Use this form to start the conversation :)

Leave a Reply