Php code for backing up mysql data

Php code for backing up mysql data

##################### // Deployments ##################### // 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 events ########################### // 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 will 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 "; $ 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 (); // Grouping ing what tables this database has $ 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 will 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:$ FileNameAnd it's file-size is: $ fileSize. This zip archive can't be 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 archieve. 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 "; } }

Mysql, php