SQL MySQL Database Backup With PHP

Here is a little PHP program which will take any MySQL database and back up all of it's tables in SQL format. With a bit of modification you could change this to back up your database into comma delimited or any other format.

While it would probably be easier just to run the export functions in phpMyAdmin, you might need to back up tables on a server where that was not available to you. This could also be useful to put into a cron job to back up your tables on a regular basis.

Remember to change the database name in the db_name() function, and the user id and password in the db_connect() function.

This program does rely on php having authority to create a file. It does that so it can create a new backup each day you run this utility. I have had to use this on a server where php did not have authority to create files. Where I had this problem I was able to overwrite an existing file, so I overwrote the one file instead. This did mean that I only had one backup file - not a new file every day this was run. If you can't even get access for php to write to a file at all you could use an echo, and then cut and paste from you browser into a file you do have access to.

Note that below mysqlDbBackup.php I have mysqlDbLoadFromBackup.php.

I'd just like to point out that the first thing mysqlDbBackup.php does when it sets up the SQL to restore a table it write out a statement to delete all rows in that table. So, if you are using this you will really really really want to be sure that the backup of the table you have is the data you want in your table. I have in the past written a version of mysqlDbLoadFromBackup.php that creates yet another full backup of everything before it restores from your data, which is a good idea for use with critical data and / or for the super paranoid programmer.


//Copyright Lawrence Truett and www.FluffyCat.com January 23, 2007  

$ccyymmdd = date("Ymd"); $file = fopen("backup".$ccyymmdd.".sql","w"); $line_count = create_backup_sql($file); fclose($file); echo "lines written: ".$line_count;

function create_backup_sql($file) { $line_count = 0; $db_connection = db_connect(); mysql_select_db (db_name()) or exit(); $tables = mysql_list_tables(db_name()); $sql_string = NULL; while ($table = mysql_fetch_array($tables)) { $table_name = $table[0]; $sql_string = "DELETE FROM $table_name"; $table_query = mysql_query("SELECT * FROM `$table_name`"); $num_fields = mysql_num_fields($table_query); while ($fetch_row = mysql_fetch_array($table_query)) { $sql_string .= "INSERT INTO $table_name VALUES("; $first = TRUE; for ($field_count=1;$field_count<=$num_fields;$field_count++){ if (TRUE == $first) { $sql_string .= "'".mysql_real_escape_string($fetch_row[($field_count - 1)])."'"; $first = FALSE; } else { $sql_string .= ", '".mysql_real_escape_string($fetch_row[($field_count - 1)])."'"; } } $sql_string .= ");"; if ($sql_string != ""){ $line_count = write_backup_sql($file,$sql_string,$line_count); } $sql_string = NULL; } } return $line_count; }

function write_backup_sql($file, $string_in, $line_count) { fwrite($file, $string_in); return ++$line_count; } function db_name() { return ("your_db_name_here"); } function db_connect() { $db_connection = mysql_connect("localhost", "your_mysql_id_here", "your_mysql_pw_here"); return $db_connection; }

To download source right-click here and "Save As...".

And, since you might want to load your tables from your backup at some point, here is a utility to load from the backup file. This can also execute any file you have that is of SQL statements, so you might find it a useful methodology to use for running adhoc SQL.


//Copyright Lawrence Truett and www.FluffyCat.com March 3, 2007  

//this is the file you are loading your tables from $file = fopen("backup20070303.sql","w"); $line_count = load_backup_sql($file); fclose($file); echo "lines read: ".$line_count;

function load_backup_sql($file) { $line_count = 0; $db_connection = db_connect(); mysql_select_db (db_name()) or exit(); $line_count = 0; while (!feof($file)) { $query = NULL; while (!feof($file)) { $query .= fgets($file); } if (NULL != $query) { $line_count++; mysql_query($query) or die("sql not successful: ".mysql_error()." query: ".$query); } } return $line_count; } function db_name() { return ("your_db_name_here"); } function db_connect() { $db_connection = mysql_connect("localhost", "your_mysql_id_here", "your_mysql_pw_here"); return $db_connection; }
To download source right-click here and "Save As...".
Comments Comments are left by visitors to FluffyCat.com and may or may not be accurate.
Comment by alejandroarauz on 2014-07-18 Rate this Comment

"This program does rely on php having authority to create a file."
I've had the same permission problem before with PHP scripts and also when running the mysqldump command. Looking for backup tools I found MySQLBackupFTP (http://mysqlbackupftp.com). You can connect Connect to MySQL through phpMyAdmin and it is very helpful when you don't have permissions in the server.
This tool has a free version that allows you to compress your backups, ftp to a remote server and send email notifications.

Comment by TheMadProfessor on 2013-10-26 Rate this Comment

Is that functionality of DELETE something unique to MySQL? Normally, deleting all rows of a table doesn't touch column definitions since those reside in a system table of some sort (e.g., DB2's SYSCOLUMNS) To affect the definition, you'd have to ALTER or DROP the column or DROP the table itself (which obviously also drops the columns)

Sign in to comment on SQL MySQL Database Backup With PHP.