A solid database backup strategy provides an essential safeguard for protecting critical data stored in your databases. To minimize the risk of catastrophic data loss, you should back up your databases to preserve changes performed on your data on a regular basis. A well-planned backup and restore strategy helps protect databases against data loss caused by a variety of failures.
Additionally, backups of a database are useful for routine administrative purposes, such as copying a database from one server to another. This increases data availability to other groups or perform database mirroring and archiving activities.
Using PHP for database backup
IAMBackup is database backup and restore package consisting of two PHP classes. One performs the backup of the selected MySQL database, and the second restores the database. Both classes support gzip-compressed files for improved performance.
When performing the database backup, the user is prompted to download the backup file, straight from the browser. Alternatively, you can store the backup on the local server at a predefined location. Restoring the database (or copying it to another server) is just as easy.
Example 1: database Backup (using new connection) and prompt the user to download it.
<?php #Include the class. This class takes the results of a SQL query and outputs in the CSV (comma separated values) format. require_once("iam_csvdump.php"); # Set the parameters: SQL Query, hostname, databasename, dbuser and password $dumpfile = new iam_csvdump; # Call the CSV Dumping function and THAT'S IT!!!! A file named export.csv is sent to the user for download $dumpfile->dump("SELECT * FROM `tablename`", "export", "csv", "mysql", "root", "", "localhost" ); ?>
Example 2: Backup using existing MySQL connection
<?php #Include the class. This class takes the results of a SQL query and outputs in the CSV (comma separated values) format. require_once("iam_csvdump.php"); # Set the parameters: SQL Query, hostname, databasename, dbuser and password $dumpfile = new iam_csvdump; # Call the CSV Dumping function with empty filenames and extension to send the query results to the browser as raw text $dumpfile->dump("SELECT * FROM user", "", "", "mysql", "root", "", "localhost" ); ?>
Backing up and restoring data must be customized to a particular environment and must work with the available resources. Therefore, a reliable use of backup and restore for recovery requires a backup and restore strategy. A well-designed backup and restore strategy maximizes data availability and minimizes data loss, while considering your particular business requirements.
Place the database and backups on separate devices. Otherwise, if the device containing the database fails, your backups will be unavailable. Placing the data and backups on separate devices also enhances the I/O performance for both writing backups and the production use of the database. Moreover, always test your strategy by restoring a set of backups and then recovering your database to prepare you to respond effectively to a disaster.
Last but not least, this class was published on PHPClasses and earned an Innovation Award nomination. Take a look and send your comments.