How to Perform Database Backups with wpdb in WordPress Plugin
Posted on 18th June 2023
Introduction
In this tutorial, we will learn how to use the wpdb class to perform database backups in a WordPress plugin. The wpdb class is a WordPress core class that provides a wrapper around the most commonly used MySQL functions. It can be used to interact with any WordPress database table, not just the ones created by WordPress itself. In addition to providing a set of helper functions, the wpdb class can also be used to run custom SQL queries.
The wpdb class is located in the wp-includes/wp-db.php file. To use the class, you need to first include the file in your plugin:
include_once( ABSPATH . 'wp-admin/includes/class-wp-db.php' );
Once the file is included, you can create a new wpdb object:
$wpdb = new wpdb( 'username', 'password', 'database', 'localhost' );
Replace username, password, database, and localhost with the appropriate values for your installation.
Performing a Database Backup
There are two ways to perform a database backup with the wpdb class. The first way is to use the $wpdb->query() function to run a SQL SELECT query. The second way is to use the $wpdb->get_results() function. Both functions take an SQL query as their first parameter. The $wpdb->query() function returns the number of rows affected by the query, while the $wpdb->get_results() function returns the results of the query as an array of objects.
To perform a database backup, we will use the $wpdb->get_results() function. We will also use the $wpdb->prefix property to prefix our table names with the WordPress table prefix. This is important because it will ensure that our queries will only affect the WordPress tables and not any other tables in the database.
The following code will backup the WordPress posts table:
$posts = $wpdb->get_results( "SELECT * FROM {$wpdb->prefix}posts" );
The $posts variable will now contain an array of all the posts in the WordPress posts table. To backup other tables, simply change the table name in the SQL query. For example, to backup the WordPress comments table, you would use the following code:
$comments = $wpdb->get_results( "SELECT * FROM {$wpdb->prefix}comments" );
Once you have retrieved the data from the database, you can then store it in a file or send it to an external backup service. If you are storing the backup file on the server, it is important to ensure that the file is not publicly accessible. One way to do this is to store the file in the /wp-content/uploads/ directory. The /wp-content/uploads/ directory is not publicly accessible by default.
The following code will store the backup in a file named posts.json in the /wp-content/uploads/ directory:
$fp = fopen( ABSPATH . 'wp-content/uploads/posts.json', 'w' );
fwrite( $fp, json_encode( $posts ) );
fclose( $fp );
Replace posts.json with the desired filename for your backup. The code above will store the backup as a JSON encoded string. You can also store the backup as an XML encoded string or as a serialized PHP array. To do this, simply change the json_encode() function to xml_encode() or serialize(), respectively.
Performing a Database Restore
To restore a database backup, we will use the $wpdb->query() function. This function can be used to run any SQL query, including INSERT, UPDATE, and DELETE queries. We will use the $wpdb->prefix property to prefix our table names with the WordPress table prefix. This is important because it will ensure that our queries will only affect the WordPress tables and not any other tables in the database.
The following code will restore the WordPress posts table from a backup stored in a file named posts.json in the /wp-content/uploads/ directory:
$fp = fopen( ABSPATH . 'wp-content/uploads/posts.json', 'r' );
$posts = json_decode( fread( $fp, filesize( ABSPATH . 'wp-content/uploads/posts.json' ) ), true );
fclose( $fp );
foreach ( $posts as $post ) {
$wpdb->query( $wpdb->prepare(
"INSERT INTO {$wpdb->prefix}posts (
ID, post_author, post_date, post_date_gmt, post_content, post_title, post_excerpt, post_status, comment_status, ping_status, post_password, post_name, to_ping, pinged, post_modified, post_modified_gmt, post_content_filtered, post_parent, guid, menu_order, post_type, post_mime_type, comment_count
) VALUES (
%d, %d, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %d, %s, %d, %s, %s, %d
)",
$post['ID'],
$post['post_author'],
$post['post_date'],
$post['post_date_gmt'],
$post['post_content'],
$post['post_title'],
$post['post_excerpt'],
$post['post_status'],
$post['comment_status'],
$post['ping_status'],
$post['post_password'],
$post['post_name'],
$post['to_ping'],
$post['pinged'],
$post['post_modified'],
$post['post_modified_gmt'],
$post['post_content_filtered'],
$post['post_parent'],
$post['guid'],
$post['menu_order'],
$post['post_type'],
$post['post_mime_type'],
$post['comment_count']
) );
}
Replace posts.json with the filename of your backup. The code above will restore the backup as a JSON encoded string. You can also restore the backup as an XML encoded string or as a serialized PHP array. To do this, simply change the json_decode() function to xml_decode() or unserialize(), respectively.
Conclusion
In this tutorial, we learned how to use the wpdb class to perform database backups in a WordPress plugin. We also learned how to use the wpdb class to perform database restores. For more information on the wpdb class, please see the WordPress Codex.
