Code to backup a wordpress database

The code snippet below has all you need to create a WordPress database MySQL file export. This could be useful for a backup or migration plugin.


global $wpdb;

// Get a list of the tables
$tables = $wpdb->get_results('SHOW TABLES');

$upload_dir = wp_upload_dir();
$file_path = $upload_dir['basedir'] . '/backups/database-' . time() . '.sql';
$file = fopen($file_path, 'w');

foreach ($tables as $table)
    $table_name = $table->Tables_in_wordpress;
    $schema = $wpdb->get_row('SHOW CREATE TABLE ' . $table_name, ARRAY_A);
    fwrite($file, $schema['Create Table'] . ';' . PHP_EOL);

    $rows = $wpdb->get_results('SELECT * FROM ' . $table_name, ARRAY_A);

    if( $rows )
        fwrite($file, 'INSERT INTO ' . $table_name . ' VALUES ');

        $total_rows = count($rows);
        $counter = 1;
        foreach ($rows as $row => $fields)
            $line = '';
            foreach ($fields as $key => $value)
                $value = addslashes($value);
                $line .= '"' . $value . '",';

            $line = '(' . rtrim($line, ',') . ')';

            if ($counter != $total_rows)
                $line .= ',' . PHP_EOL;

            fwrite($file, $line);


        fwrite($file, '; ' . PHP_EOL);


How it works

The above code works by getting a list of tables using the MySQL 'SHOW TABLES' command, we then loop through each table and get a) the code to generate the table using 'SHOW CREATE TABLE' b) each of the rows seperated with a PHP_EOL (end of line). 

The file is written using the PHP file handler fopen.

Try it out

If you want to test the above script without creating a plugin, simply create a file in the root of your WordPress development site and then copy in the above code. To bootstrap WordPress you'll also need to add the extra line of code below, at the top of your file.

Note this method is for test purposes only, for a production environment all code should be wrapped in an admin plugin to ensure you're the only person executing the script!

require_once( dirname( __FILE__ ) . '/wp-load.php');

On execution of the script you'll see a sql file is generated inside your uploads folder in a subdirectory titled 'backups' (make sure you create this first!).


The script above demonstrates how a WordPress database can be backed up, but if you want to use something like this in a live environment you'll firstly need to add the code to an admin plugin so that no-one else can run the backups, and secondly you'll need to save your backups to secure folder (above example writes to 'uploads' with a subdirectory of 'backups', which would be publicly browseable). To make the 'backups' folder unbrowsable (so you can only get the files via FTP) you could add the following htaccess file to that directory:

Options All -Indexes
<Files "*">
Deny from all

Sign Up

NEXT: Add an admin WordPress page not shown in the menu

This snippet shows you how to add custom admin pages to WordPress without adding them to the menu.

comments powered by Disqus

Popular Tags

Need a web developer?

If you'd like to work with code synthesis on your next project get in touch via the contact page.