WordPress stores media files in a folder named uploads. This folder is located in the wp-content directory of the website installation directory. The post, user, theme, and plugin data are saved to a database. The website builder can access the DB only if you’ve installed the php-mysql PHP extension and know its username and password. The database is password protected, and its password is stored in a PHP file named wp-config. The WordPress config file, aka wp-config.php is in the root directory of WP.
When is the WordPress DB created? If you’re using a tool such as Softaculous to install WP, the tool will create a database in the background. Once it creates the DB, the tool will create a MySQL user. The DB creation process is one of the many tasks the Softaculous one-click application installer accomplishes for installing WP on a server.
Once it creates a DB, it updates the WP-config file or replaces the default wp-config file with the one having the name of the DB, the id of the user who has access to the database, and the password that the user must use to unlock/access the DB. If you’re using an unmanaged hosting plan, you will have to do the exact thing that Softaculous does.
For some reason, you may want to access the WP database or see the records in one of the WP tables or search for a record in a table. We’ve discussed these three tasks in the following paragraphs.
Access WP DB
There are two ways to access the WordPress DB – use a MySQL client or use the terminal. To access MySQL records using the terminal, follow these steps: open the terminal and log in to the server from the terminal. Now login to the MySQL server using this command mysql -u user_name -p. You will now be asked to enter the username and password. After entering these two things, execute this command show databases. Note down the name of your DB and run this command use d_name where d_name is the name of your website’s database. You now have access to the DB. To see the list of tables, run the command – show tables.
An alternative but an easier way of accessing the WP site’s DB is to use a GUI MySQL client application. On Windows, you can use HeidiSQL, and on Linux machines, you should use PHPMyAdmin or MySQL WorkBench.
The programs are easy to use. They ship with a connection wizard that prompts you to enter the MySQL server’s port number, server IP address, username, and password. The tools will let you explore the tables, run queries, see the result set, and more. PHPMyAdmin is available in the CPanel software. So, if you’ve bought a hosting plan from companies that offer CPanel access (e.g. Bluehost or InMotion), you don’t have to install PHPMyAdmin or desktop-based MySQL client applications.
Search records in the WP DB
Once you have access to the DB, you may want to see records of the table or search records in the DB table. Finding a record is easy. MySQL offers a great function called instr() that will let you search for records matching a string of your choice. It takes the column name and string as parameters and can be a part of any SQL statement. For example, the MySQL query select * from wp_posts where instr(post_title,"how")> 0 will generate a resultset comprising all records containing the columns with post_title containing the text “how”.
Getting data
When you have access to the database, you may want to retrieve data from its tables. The simple select query will do the job for you. To see the records in a particular table, say “xyz”, you can execute the query select * from table_name where table_name is the name of the table from which you want to retrieve data. The query can have a condition. You can use the where and having clauses for the same.
A table may have hundreds of records. The select SQL query will do its job, but you may want to limit the number of records displayed on the screen. Here’s when the limit clause comes into the picture. After adding the limit clause, the query would look like this – select * from wp_posts limit 1, 10. This query (without the full stop) would show only the first ten results.
Exporting WP data
Exporting is nothing but backing up the WP DB. We’ve shared the method to back up WordPress database. What does the exported file contain? If you edit the exported file with a sophisticated code editor such as VScode, you’ll find several SQL statements and commands.
