Mysql backup: full backup and incremental backup _ MySQL

Mysql backup: full backup and incremental backup bitsCN.com

Mysql backup: full backup and incremental backup

Full Backup:

In the "external" directory, enter the mysqldirectory bindirectory and mysqldump.exe to perform the backup. Mysqldump-uroot-p911004 database or Data Table> path/file name. On the mysql console, use the source path/backup file name for restoration.

A full backup is generally performed once in a certain period of time, and when the website traffic is the smallest, regular backup of batch files is often used. It is mainly to write a batch processing file and write the absolute path of the processing program in it, and then write the content to be processed in the back, that is, completely back up the database C: /myenv/mysql5.5.27/bin/mysqldump-u root-proot temp dept> d:/temp. dept. bak. Then, create a task on the control panel. Task specifies the execution time of the batch file. However, the backup generated by executing this file will overwrite the previous file. When a dynamic name is specified, the php.exe/php file path/*. php is written in the batch file. In the php file, you can generate the file name based on the time, write the backup statement, and run the exec function. Execute the file every time you create a plan.

Php file:

Date_default_timezone_set ('prc ');

$ Bakfilename = date ("YmdHis", time ());

$ Command = "C:/myenv/mysql5.5.27/bin/mysqldump-u root-proot temp dept> d: // {$ bakfilename }";

Exec ($ command );

Incremental Backup:

Binary backup of ddl and dml statements. And 5.0 cannot be incrementally backed up. After 5.1, you can.

To implement incremental backup, configure the backup path in the my. ini file, that is, log-bin = 'd:/log/mylog under [mysqld'

Restart the mysql server and start the incremental backup.

Two file mylog. index record indexes are generated under the specified directory. mylog.000001 is the detailed information of the binary record.

To view data, you must use the mysqlbinlog tool. Use msyqlbinlog in the bin in the mysql directory in cmd

After mylog.000001, you can see all ddl and dml operations, and each operation has a specific location and time point record.

Based on this information, the operation can be restored,

You can restore data by using the time and location methods:

Mysqlbinlog -- stop-datetime = "18:20:21" d:/log/mylog.000001 | mysql-uroot-p911004

From start to this time point

Mysqlbinlog -- start-datetime = "18:20:21" d:/log/mylog.000001 | mysql-uroot-p911004

From this time point to the end

Mysqlbinlog -- start-datetime = "18:20:21" -- stop-datetime = "18:20:21" d:/log/mylog.000001 | mysql-uroot-p911004

Restore data for this period of time.

Mysqlbinlog -- stop-position = "N" d:/log/mylog.000001 | mysql-uroot-p911004

From the beginning to this place

Mysqlbinlog -- start-position = "18:20:21" d:/log/mylog.000001 | mysql-uroot-p911004

From this place to the end

Mysqlbinlog -- start-position = "18:20:21" -- stop-position = "18:20:21" d:/log/mylog.000001 | mysql-uroot-p911004

Restore the data of this local segment. With the passage of time, the data in binary files is getting bigger and bigger, so we need to regularly clean up the data.

1. the reset master can delete all binary logs listed in the index file,

Set the binary log index file to null and create a new binary log file.

2. PURGE {MASTER | BINARY} logs to 'log _ name'

PURGE {MASTER | BINARY} logs before 'date'

Deletes all binary logs listed in the log index before the specified log or date.

These logs will also be deleted from the list of records in the log index file, so that the given log becomes the first.

3. set-EXPIRE_LOGS_DAYS under the parameter [mysqld] in my. ini. This parameter sets the log expiration days. expired logs will be automatically deleted,

Generally, a full backup is performed once a week, followed by incremental backup, and the expiration time is greater than 7 days. If the cluster crashes, it will be restored first and then restored in incremental mode. If a misoperation occurs, you can check the incremental log for incremental recovery.

BitsCN.com