Using PHP to backup MySQL databases

This post shows how to perform a partial or full backup of MySQL databases using only PHP code. This is very useful for example when a client doesn’t provide you with access data to the database of a web application and you only have an FTP connection available, or when you do have the connection data to the database but you can’t access it through the network (only available from localhost) and you can’t use tools like mysqldump to back up the information you need because you don’t have privileges for this, or simply because you have no access to a shell to which connect and run commands.

UPDATE (08-28-2017): moved the source code from myphp-backup.php and myphp-restore.php scripts to my GitHub daniloaz/myphp-backup repository: https://github.com/daniloaz/myphp-backup

Backup of MySQL database tables

In any of the above scenarios, simply by copying and executing my myphp-backup.php PHP script as indicated below, you will be able to make a full or partial (only some tables) export of a database to a .sql or .sql.gz file that you will be able to download later from the same FTP account. The script includes the Backup_Database class that performs all the necessary operations and has different configuration options to control in which subdirectory you want the backup files to be stored and if you want them to be compressed with gzip or not. You’ll also need to establish the user credentials for accessing the database, as well as the name of that database:

/**
 * Define database parameters here
 */
define("DB_USER", 'your_username');
define("DB_PASSWORD", 'your_password');
define("DB_NAME", 'your_db_name');
define("DB_HOST", 'localhost');
define("BACKUP_DIR", 'myphp-backup-files'); // Comment this line to use same script's directory ('.')
define("TABLES", '*'); // Full backup
//define("TABLES", 'table1 table2 table3'); // Partial backup
define("CHARSET", 'utf8');
define("GZIP_BACKUP_FILE", true); // Set to false if you want plain SQL backup files (not gzipped)

PHP applications usually have a configuration file such as config.php or similar in which you probably can find database user and password if you don’t remember them or if your customer has not provided you with this information.

If you don’t have sufficient permissions to create the subdirectory where your backup files will be stored (by default myphp-backup-files/), you can use an existing directory. PHP applications usually have some subdirectory with enough permissions to create new files (cache, tmp, temp, etc.), which is where you will tell myphp-backup.php script to leave the file with the database backup. You can also use the root directory itself (DocumentRoot). To do this, simply assign a point (‘.’) to the BACKUP_DIR constant.

The .sql.gz or .sql file generated as a result of using the Backup_Database class of myphp-backup.php script will have a name with the following format (myphp-backup-{DB_NAME}-YYYYYYmmdd_HHHMMSS.sql.gz):

$ ls myphp-backup-files/*.sql.gz
myphp-backup-files/myphp-backup-smf-20160131_111735.sql.gz

Restoring tables from a MySQL database

The previous file will contain compressed (or not) SQL code ready to restore the structure and content of the original database in another database or in the same one but replacing the existing data with those of your backup file. To perform the restoration you will simply run the other script in my repository: myphp-restore.php. The only thing you will have to do to perform the restoration is to upload both the myphp-restore.php script and the backup file itself to a folder in which you have write permissions on the server where you want to import data and run it through your browser (https://www.example.com/myphp-restore.php) or from command line. Don’t forget to set backup file and folder names before using the BACKUP_FILE and BACKUP_DIR constants.

All the configuration options available for myphp-restore.php script are as follows:

/**
 * Define database parameters here
 */
define("DB_USER", 'your_username');
define("DB_PASSWORD", 'your_password');
define("DB_NAME", 'your_db_name');
define("DB_HOST", 'localhost');
define("BACKUP_DIR", 'myphp-backup-files'); // Comment this line to use same script's directory ('.')
define("BACKUP_FILE", 'your-backup-file.sql.gz'); // Script will autodetect if backup file is gzipped or not based on .gz extension
define("CHARSET", 'utf8');

The script will automatically detect whether the backup file is compressed with gzip or not based on the .gz extension, so you don’t have to make any additional settings for this.

Error: Your Requested widget " ai_widget-6" is not in the widget list.

• [do_widget_area above-nav-left]
• [do_widget_area above-nav-right]
• [do_widget_area footer-1]
  • [do_widget id="wpp-4"]
• [do_widget_area footer-2]
  • [do_widget id="recent-posts-4"]
• [do_widget_area footer-3]
  • [do_widget id="recent-comments-3"]
• [do_widget_area footer-4]
  • [do_widget id="archives-4"]
• [do_widget_area logo-bar]
  • [do_widget id="oxywidgetwpml-3"]
• [do_widget_area menu-bar]
  • [do_widget id="search-3"]
• [do_widget_area sidebar]
  • [do_widget id="search-4"]
  • [do_widget id="ai_widget-2"]
  • [do_widget id="categories-5"]
  • [do_widget id="ai_widget-3"]
  • [do_widget id="ai_widget-4"]
  • [do_widget id="ai_widget-5"]
• [do_widget_area sub-footer-1]
  • [do_widget id="text-4"]
• [do_widget_area sub-footer-2]
• [do_widget_area sub-footer-3]
• [do_widget_area sub-footer-4]
• [do_widget_area upper-footer-1]
  • [do_widget id="search-2"]
  • [do_widget id="recent-posts-2"]
  • [do_widget id="recent-comments-2"]
  • [do_widget id="archives-2"]
  • [do_widget id="categories-2"]
  • [do_widget id="meta-2"]
• [do_widget_area upper-footer-2]
• [do_widget_area upper-footer-3]
• [do_widget_area upper-footer-4]
• [do_widget_area widgets_for_shortcodes]
  • [do_widget id="search-5"]
  • [do_widget id="ai_widget-6"]
• [do_widget_area wp_inactive_widgets]
  • [do_widget id="wpp-2"]
  • [do_widget id="text-1"]
  • [do_widget id="recent-posts-3"]
  • [do_widget id="categories-3"]
  • [do_widget id="archives-3"]
  • [do_widget id="icl_lang_sel_widget-3"]

Source code of backup and restore scripts

You can find the source code for both backup and restoration scripts in my Github daniloaz/myphp-backup repository at https://github.com/daniloaz/myphp-backup.

And nothing else. If you can think of any additional feature that you miss and might be useful for you or other users, don’t hesitate to leave a comment. Good luck!