PHP code to back up MySQL data

##################### Configurations ##################### Define the name of the backup directory Define (' Backup_dir ', './mybackups '); Define Database Credentials Define (' HOST ', ' localhost '); Define (' USER ', ' testd!b '); Define (' PASSWORD ', ' k^$2y4n9@ #VV '); Define (' db_name ', ' test123 '); /* Define the filename for the SQL file If you plan to upload the file to Amazon's S3 service, use only lower-case letters */ $fileName = ' mysqlbackup--'. Date (' D-m-y '). ' @ '. Date (' H.i.s '). SQL '; Set Execution time limit if (function_exists (' Max_execution_time ')) { if (Ini_get (' max_execution_time ') > 0) set_time_limit (0); } ########################### END of configurations ########################### Check If directory is already created and has the proper permissions if (!file_exists (Backup_dir)) mkdir (Backup_dir, 0700); if (!is_writable (Backup_dir)) chmod (Backup_dir, 0700); Create an ". htaccess" file, it'll restrict direct accss to the backup-directory. $content = ' Deny from all '; $file = new Splfileobject (backup_dir. '/.htaccess ', ' W '); $file->fwrite ($content); $mysqli = new Mysqli (HOST, USER, PASSWORD, db_name); if (Mysqli_connect_errno ()) { printf ("Connect failed:%s", Mysqli_connect_error ()); Exit (); } Introduction Information $return. = "--\n"; $return. = "--A Mysql Backup System \ n"; $return. = "--\n"; $return. = '--Export created: '. Date ("Y/m/d"). ' On '. Date ("H:i"). "\n\n\n"; $return = "--\n"; $return. = "--Database:". Db_name. "\ n"; $return. = "--\n"; $return. = "----------------------------------------------------\ n"; $return. = "-----------------------------------------------------\ n"; $return. = ' SET autocommit = 0; '. ' \ n "; $return. = ' SET foreign_key_checks=0; '. ' \ n "; $tables = Array (); Exploring what tables this database have $result = $mysqli->query (' SHOW TABLES '); Cycle through "$result" and put content into an array while ($row = $result->fetch_row ()) { $tables [] = $row [0]; } Cycle through each table foreach ($tables as $table) { Get content of each table $result = $mysqli->query (' SELECT * from '. $table); Get number of fields (columns) of each table $num _fields = $mysqli->field_count; ADD table Information $return. = "--\n"; $return. = '--Tabel structure for table '. $table. '`' . "\ n"; $return. = "--\n"; $return. = ' DROP TABLE IF EXISTS '. $table. ' `;' . "\ n"; Get the Table-shema $shema = $mysqli->query (' SHOW CREATE TABLE ' $table); Extract table Shema $tableshema = $shema->fetch_row (); Append Table-shema into code $return. = $tableshema [1]. ";". "\ n"; Cycle through each Table-row while ($rowdata = $result->fetch_row ()) { Prepare code that would insert data into table $return. = ' INSERT into '. $table. ' ' VALUES ('; Extract data of each row for ($i =0; $i < $num _fields; $i + +) { $return. = ' "'. $rowdata [$i]." \"," ; } Let's remove the last comma $return = substr ("$return", 0,-1); $return. = ");". " \ n "; } $return. = "\ n"; } Close the connection $mysqli->close (); $return. = ' SET foreign_key_checks = 1; ' . "\ n"; $return. = ' COMMIT; ' . "\ n"; $return. = ' SET autocommit = 1; ' . "\ n"; $file = File_put_contents ($fileName, $return); $zip = new Ziparchive (); $resOpen = $zip->open (backup_dir. '/'. $fileName. ". Zip ", ziparchive::create); if ($resOpen) { $zip->addfromstring ($fileName, "$return"); } $zip->close (); $fileSize = Get_file_size_unit (FileSize (Backup_dir. "/". $fileName. '. zip ')); $message = <<<> BACKUP completed, The archive has the name of: $fileName and it's file-size is: $fileSize. This zip archive can ' t is accessed via a Web browser, as it's stored into a protected directory. It ' s highly recomended to transfer this backup to another filesystem, use your favorite FTP client to download the Archie Ve. Msg Echo $message; Function to append proper Unit after file-size. function Get_file_size_unit ($file _size) { Switch (TRUE) { Case ($file _size/1024 < 1): Return Intval ($file _size). " Bytes "; Break Case ($file _size/1024 >= 1 && $file _size/(1024*1024) < 1): Return Intval ($file _size/1024). " KB "; Break Default Return Intval ($file _size/(1024*1024)). " MB "; } } Copy Code

MySQL, PHP

