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.
jason@jason-desktop:/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.