Implement mysql database partition backup using php

In this article, we will share with you how to implement mysql database volume-based backup in php, select tables for backup, and import a single SQL file and volume-based SQL. If you have any friends, refer

In this article, we will share with you how to implement mysql database volume-based backup in php, select tables for backup, and import a single SQL file and volume-based SQL. If you have any friends, refer

How to export data volumes: Count the length of SQL statement variables. Compare the data size with 1 character as 1 byte. If the data size exceeds the configured value, write an SQL file (I do not know whether such statistics are stable, which is also used by others ).

Method of volume-based import: Read the SQL file by row and save each row as a complete SQL statement to the array for recycling and insert it into the database. However, when creating a table, the statement is divided into multiple rows, this needs to be processed separately (it took me a long time );

<? Php // 正 set_time_limit (0); header ('content-type: text/html; charset = UTF-8 '); mysql_connect ('localhost ', 'root', 'root'); mysql_select_db ('test'); $ table_array = get_tables ('test'); mysql_query ('set names utf8 '); $ filesize = 1024*1024*4; $ start = $ _ GET ['start']? $ _ GET ['start']: 0; $ part = $ _ GET ['part']? $ _ GET ['part']: '1'; $ table_index = $ _ GET ['table _ Index']? $ _ GET ['table _ Index']: '0'; $ table = $ table_array [$ table_index]; $ num = 200000000; // This number must be large enough, the total number of records can be $ backupdata = ''; if ($ start = '0') {$ query =" show create table '{$ table }'"; $ result = mysql_query ($ query); $ row = mysql_fetch_row ($ result); $ backupdata. = "drop table if exists '{$ table}'; \ n ". $ row [1]. "; \ n" ;}$ limit = ($ start = '0 ')? '':" Limit $ start, $ num "; $ query =" select * from '{$ table}' $ limit "; $ result = mysql_query ($ query ); $ numfields = mysql_num_fields ($ result); // count the number of fields while ($ row = mysql_fetch_row ($ result) {$ comma = ''; // storage comma $ backupdata_tmp = "insert into '{$ table}' VALUES ("; for ($ I = 0; $ I <$ numfields; $ I ++) {$ backupdata_tmp. = $ comma. "'". mysql_escape_string ($ row [$ I]). "'"; $ comma =', ';} $ backupdata_tmp. = "); \ n"; if (strlen ($ Backupdata) + strlen ($ backupdata_tmp)> $ filesize) {// write the file and jump to $ file = 'data /'. $ table. '-'. $ part. '. SQL '; file_put_contents ($ file, $ backupdata); echo $ file. 'After the backup is completed, the program continues! '; $ Part ++; // segment // table name // start // jump to sleep (3); echo "script location. href = '? Start = {$ start} & table_index = {$ table_index} & part = {$ part} '; script "; exit ;}$ backupdata. = $ backupdata_tmp; $ start ++;} if ($ backupdata) {$ file = 'data /'. $ table. '-'. $ part. '. SQL '; file_put_contents ($ file, $ backupdata);} echo $ table. 'backup complete!
'; Sleep (2); $ table_index ++; if ($ table_array [$ table_index]) {echo "script location. href = '? Table_index = {$ table_index} '; script "; exit;} else {echo' congratulations! The database has been backed up! ';} Function get_tables ($ db) {$ tq = mysql_list_tables ($ db); while ($ tr = mysql_fetch_row ($ tq )) {$ arrtb [] = $ tr [0];} return $ arrtb ;}?>

The above is all the content of this article. I hope you will like it.