PHP Classes
elePHPant
Icontem

Large Database Backup: Backup large MySQL databases into multiple files

Recommend this page to a friend!
Stumble It! Stumble It! Bookmark in del.icio.us Bookmark in del.icio.us
  Info   View files View files (6)   DownloadInstall with Composer Download .zip   Reputation   Support forum (6)   Blog    
Last Updated Ratings Unique User Downloads Download Rankings  
2015-01-12 (6 months ago) RSS 2.0 feedStarStarStarStar 65%Total: 3,864 This week: 2All time: 777 This week: 602Up
Version License PHP version Categories  
backupmysql 1.1GNU General Publi...4.0Databases, Systems administration
Description Author  

This class can be used to backup large MySQL databases into multiple files.

It can list the tables of a given MySQL database and generate SQL statements to create and insert records with the current values of the table rows, as if it was creating a new database. The class stores the generated SQL statements in files.

A full backup process can be split in multiple iterations to not exceed PHP default script execution time limit. Each iteration dumps a limited number of table rows. An iteration can be executed by a script that redirects the page request to itself at the end to proceed to the next iteration.

Innovation Award  
PHP Programming Innovation award nominee
July 2007
Number 2


Prize: One downloadable copy of PhpED Professional
There are many pure PHP solutions for taking backup copies of MySQL databases.

Most solutions are suitable to run on restricted PHP hosting environments. However, if you have a large database it may take more time to create a backup copy than the default 30 second PHP script execution time limit. The backup process may not finish before the script is aborted.

This class provides a clever solution that avoids that problem. It splits the backup process into multiple steps, each extracting a limited number of table rows. This way, each step may take less time than the PHP execution time limit.

Manuel Lemos
Picture of Nadir Latif
Name: Nadir Latif is available for providing paid consulting. Contact Nadir Latif .
Classes: 14 packages by
Country: Pakistan Pakistan
Age: 32
All time rank: 851 in Pakistan Pakistan
Week rank: 62 Up2 in Pakistan Pakistan Down
Innovation award
Innovation award
Nominee: 9x

Winner: 1x

Details provided by the author  
Made by: Nadir Latif (nadir.latif@yahoo.com)

1) Overview:

-This class can be used to back up very large databases that can only be accessed from a particular host. For e.g in some cases users may need to backup up a very large database, which cannot be backed up automatically using phpmyadmin because of script timeouts.

-The script retrieves a user defined number of rows from the mysql server and then saves its state in a text file. Javascript is used to refresh the page and the backup process continues from where it left off. The script continues to refresh untill all data has been backup up. After the script ends data is stored in a .sql file. After all the data has been placed in the .sql file, a gzip file is created and the .sql file is deleted from the server.

2) Usage:

-The script must be placed on a web server from where the database can be accessed. When the script is run for the first time the folder in which it is placed must be given 777 permission. This permission can be restored after the scipt has run for the first time. The backup file is placed in a folder created by the script (in gzip format).
-In connection_details.csv the first line should be in the format database_folder_path#server_name [e.g database/#localhost].
-The second line should be in the format username:password:database_name [e.g root:nadir:customer_db]
-Both connection_details.csv and backup_details.csv files must be placed outside the web root. location of the files must be given in the variable $config_file_path on line 5 in db_backup.php file
-Run the script as db_backup.php?restart=1

-The script can easily be extended to backup multiple databases and to maintain periodic backups.

-Feel free to contact me for assistance regarding this script.
  Files folder image Files  
File Role Description
Accessible without login Plain text file connection_details.csv Data Contains configuration details
Accessible without login Plain text file db_backup.php Example sample file
Plain text file mysql_backup.php Class Contains all functions
Accessible without login Plain text file readme.txt Doc. Help file
Accessible without login Plain text file backup_details.csv Data Data file
Accessible without login Plain text file LICENSE.txt Doc. Documentation

 Version Control Unique User Downloads Download Rankings  
 100%Total:3,864All time:777
 This week:2This week:602Up
User Ratings User Comments (3)  
 All time
Utility:85%StarStarStarStarStar
Consistency:82%StarStarStarStarStar
Documentation:75%StarStarStarStar
Examples:71%StarStarStarStar
Tests:-
Videos:-
Overall:65%StarStarStarStar
Rank:488
 
A very useful class.
5 years ago (Xav)
72%StarStarStarStar
Thanks for your script.
7 years ago (gao hong ying)
77%StarStarStarStar
Instructions are limited, and the backup repeatedly times out
7 years ago (Kent Parker)
7%Star