Shulou(Shulou.com)06/01 Report--

The following content mainly brings you mysql database full backup and incremental backup script handouts, the knowledge here is slightly different from books, are professional and technical personnel in the process of contact with users, summed up, has a certain experience sharing value, hope to bring help to the majority of readers.

Automatic full backup and incremental backup of mysql database

Incremental backup, in order to enhance the security and integrity of mysql database, it is necessary to back up the data. Our strategy here is to make a full backup at 5:30 every Friday afternoon and an incremental backup at 6:00, 12:10, and 17:00 every day.

1. The full backup script is as follows: (after backup and package storage)

two。 The incremental backup script is as follows: (read the contents of the generated binary file and import it into a new sql file)

For incremental backups, turn on the binary function in the configuration file of mysql:

Log-bin=mysql

3. Add these two to the planned task

4. Execution result:

Database backup all files are stored in the path: / backup/mysqldb_full

Database incremental backup storage path: / backup/mysqldb_add

Full backup file: (path is / backup/mysqldb-full/20150525, date of each day is the file name, * full.sql is the full backup file, * add.sql is the incremental backup file)

Packaged backup file: (path at / backup/mysql_backup)

Generated binaries:

5. Data recovery:

(1) restore full backup method

Mysql-p < full.sql

(2) restore incremental backup

Mysqladmin bin-log.000001 | mysql-uroot-p

Point-in-time recovery:

If you delete a table by mistake, it is useless to use full recovery, because the deleted sql statements are also retained in the log, so we need to return to the state before the misoperation, and then skip the misoperation statement.

If I delete a table by mistake at 20:00, I can restore it using the following statement:

Mysqlbinlog-- stop-date='2012-06-05 1919 mysql- uroot mysql- uroot 59' / var/log/mysql-bin.000001

Skip the time point of erroneous deletion, and then execute:

Mysqlbinlog-- start-date='2012-06-05 20 var/log/mysql-bin.000001 01Switzerland 00' / var/log/mysql-bin.000001 | mysql- uroot-p

Location-based recovery:

More accurate data can be obtained by location-based recovery.

As shown in the figure above, the starting position of the drop table test statement is 889107 and the ending position is 889189, so we can use the following statement to restore:

Mysqlbinlog-- stop-position='889107' / var/lib/mysql/mysql-bin.000001 | mysql- uroot-pmysqlbinlog-- start-position='889189' / var/lib/mysql/mysql-bin.000001 | mysql- uroot-p

For the above handouts on mysql database full backup and incremental backup scripts, if you need to know more, you can continue to follow our industry promotion. If you need professional solutions, you can contact the pre-sale and after-sale ones on the official website. I hope this article can bring you some knowledge updates.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.