MySQL Backup solution--(using mysqldump and Binlog binary logs)

from:http://blog.csdn.net/mchdba/article/details/11575605

As data continues to grow, and in order to be compatible with future InnoDB storage engines, consider the strategy of mysqldump full + log incremental backup. Using mysqldump is good support for most MySQL MySQL storage engines such as MyISAM and InnoDB.

Scenario One: mysqldump full backup + log incremental backup

1, mysqldump Backup solution:

Monday 3 o'clock in the morning fully prepared

Tuesday to Sunday 3 o'clock in the morning incremental backup

2, backup steps

(1) Create backup directory, backup script to store directory

Shell > Mkdir/usr/mysqlbackup;

Shell > Chmod 755/usr/mysqlbackup;

Shell > mkdir/usr/mysqlbackup/daily;

Shell > Chmod 755/usr/mysqlbackup/daily;

Shell > Mkdir/usr/script;

Shell > Chmod 777/usr/script/*.sh

(2) Enable binary logging

If the log does not start, you must enable Binlog, to restart Mysqld, first, turn off mysqld, open/etc/my.cnf, and add the following lines:

[Mysqld]

Log-bin

And then restart Mysqld, will produce hostname-bin.000001 and Hostname-bin.index, the previous log file is to record all updates to the data, the following file is the index to store all the binaries, cannot be easily deleted.

(3) Full backup, incremental backup.

See mysqlfullbackup.sh, mysqldailybackup.sh script in detail ( note the backup directory inside the script, themysql Software installation directory, the compressed file name and the user name password, if there is a discrepancy, please modify ).

The following is a single, manually executed test command on a part of the shell.

Shell>/usr/local/mysql/bin/mysqldump-uroot-pnyuiman25040slave201012301124--no-create-info=FALSE-- Order-by-primary=false--force=false--no-data=false--tz-utc=true--flush-privileg

Es=false--compress=false--replace=false--insert-ignore=false--extended-insert=true--quote-names=TRUE--hex-blob =true--complete-insert=false--add-locks=true--port=3306--d

Isable-keys=true--delayed-insert=false--create-options=true--delete-master-logs=false--comments= True--default-character-set=utf8--max_allowed_packet=1g--flush-logs=false-

-dump-date=true--lock-tables=true--allow-keywords=false--events=false--single-transaction=true-- Routines--all-databases >/backup/mysql/full/mysql_20110104_195546.sql

(4) Set crontab task, execute backup script daily

Shell> crontab–e

#每个星期日凌晨3:00 Perform a full backup script

0 3 * * 0/usr/scrpit/mysqlfullbackup.sh>/dev/null 2>&1

#周一到周六凌晨3:00 do an incremental backup

0 3 * * 1-6/root/mysqlbackup/mysqldailybackup.sh >/dev/null 2>&1

(5) Clear the old backup file.

Go to see the backup disk space and delete the old backup compressed files every day.

3, Database recovery

Note if the "error1051 (42S02) at line 32:unknown table ' Fin_cell_attr_his '" errors are reported in the incremental recovery operation, you need to create an empty table now. Then perform an incremental restore.

Mysql> createtable tb1 ...;

(1) Full recovery:

The file backed up with Mysqldump is a SQL script that can be poured directly to restore the last full backup with the MySQL command:

First copy from the backup file server, unzip, and then restore, for example:

Shell>/root/s3cmd/s3cmd Get S3://csf-backup/mysqldb/mysql_20101216_231900.sql.tar.gz/usr/mysqlrestore/

Shell>/root/s3cmd/s3cmd Get S3://csf-backup/mysqldb/mysql_binlog_20101217_030100.tar.gz/usr/mysqlrestore/

Shell>cd/usr/mysqlrestore/

SHELL>TAR-XVF mysql_20101216_231900.sql.tar.gz

SHELL>TAR-XVF mysql_binlog_20101217_030100.tar.gz

To refresh the log:

Mysql>flush logs;

Restore the last full backup command:

shell>/usr/local/mysql/bin/mysql-uroot-p123456 </tmp/mysqlbackup/backup_20101215_adb.sql

Restore all incremental backup commands after the last full backup:

Shell>/usr/local/mysql/bin/mysqlbinlog mysql-bin.00007 mysql-bin.00008mysql-bin.00009 mysql-bin.00010 mysql-bin.00011 | Mysql

Check: View the database structure and data.

(2) based on point-in-time recovery one, the same day after the full backup of the Insert point data error Operation:

If the insert data after the morning full backup is mistakenly deleted, the error occurred at 11 o ' clock. You can use the Bin-log log to restore the database to the moment before 11 o'clock, and then skip the failure point 11 point, then restore all of the following operations, the command is as follows:

To refresh the log:

Mysql>flush logs;

First copy the Binlog log from the backup file server and unzip it.

Shell>/root/s3cmd/s3cmd Get S3://csf-backup/mysqldb/mysql_binlog_20101221_030100.tar.gz/usr/mysqlrestore/

Shell>cd/usr/mysqlrestore/

SHELL>TAR-XVF mysql_binlog_20101221_030100.tar.gz

Log increment before recovery to point of failure

Shell>/usr/local/myql/bin/mysqlbinlog--stop-date= "2010-12-21 10:59:59" Mysql-bin.00011|mysql-u root-p 123456
Skips the point in time of failure, resumes execution of the subsequent binlog, and completes the recovery
Shell>/usr/local/myql/bin/mysqlbinlog--start-date= "2010-12-21 10:59:59" mysql-bin.00011| Mysql-u root-p 123456

Check:

Use the SELECT * from TB and other SQL commands to view the table and whether the data is recovered.

(3) Based on point-in-time recovery two, the same day 11 points of data failure misoperation operation:

If the insert data after the morning full backup is mistakenly deleted, the error occurred at 11 o ' clock. The lost data is the data entered before the last backup, you must first full recovery with the full backup file, then you can use the Bin-log log to restore the database to the moment before 11 o'clock, Then skip the failure point 11 point, and then restore all the following operations, the procedure is as follows:

To refresh the log:

Mysql>flush logs;

Copy from the backup file server first, unzip, and then restore

Shell>/root/s3cmd/s3cmd Get S3://csf-backup/mysqldb/mysql_20101216_231900.sql.tar.gz/usr/mysqlrestore/

Shell>/root/s3cmd/s3cmd Get S3://csf-backup/mysqldb/mysql_binlog_20101217_030100.tar.gz/usr/mysqlrestore/

Shell>cd/usr/mysqlrestore/

SHELL>TAR-XVF mysql_20101216_231900.sql.tar.gz

SHELL>TAR-XVF mysql_binlog_20101217_030100.tar.gz

Restore the last full backup command:

shell>/usr/local/mysql/bin/mysql-uroot-p123456 </tmp/mysqlbackup/mysql_20101216_231900.sql

Log increment before recovery to point of failure

Shell>/usr/local/myql/bin/mysqlbinlog--stop-date= "2010-12-21 10:59:59" Mysql-bin.00011|mysql-u root-p 123456
Skips the point in time of failure, resumes execution of the subsequent binlog, and completes the recovery
Shell>/usr/local/myql/bin/mysqlbinlog--start-date= "2010-12-21 10:59:59" mysql-bin.00011| Mysql-u root-p 123456

Check:

Use the SELECT * from TB and other SQL commands to view the table and whether the data is recovered.

(4) Based on point-in-time recovery three, yesterday 11 points of data fault operation:

The recovery process, first full recovery, then the log incremental recovery skips 11 points after resuming the log incremental recovery, as follows:

Copy from the backup file server first, unzip, and then restore

Shell>/root/s3cmd/s3cmd Get S3://csf-backup/mysqldb/mysql_20101216_231900.sql.tar.gz/usr/mysqlrestore/

Shell>/root/s3cmd/s3cmd Get S3://csf-backup/mysqldb/mysql_binlog_20101217_030100.tar.gz/usr/mysqlrestore/

Shell>cd/usr/mysqlrestore/

SHELL>TAR-XVF mysql_20101216_231900.sql.tar.gz

SHELL>TAR-XVF mysql_binlog_20101217_030100.tar.gz

To refresh the log:

Mysql>flush logs;

Full recovery:

shell>/usr/local/mysql/bin/mysql-uroot-p123456 </tmp/mysqlbackup/mysql_20101216_231900.sql

Log increment recovery to the point of failure:

Shell>/usr/local/myql/bin/mysqlbinlog--stop-date= "2010-12-20 10:59:59" Mysql-bin.00010|mysql-u root-p 123456

To skip the point of failure to resume recovery:

Shell>/usr/local/myql/bin/mysqlbinlog--start-date= "2010-12-20 10:59:59" mysql-bin.00010mysql-bin.00011| Mysql-u root-p 123456

(5) Based on point-in-time recovery four, multiple fault point recovery, such as 2 fault point data fault operation:

First point of failure:2010-12-2113:41:41–> 2010-12-21 13:42:36

Second point of failure:2010-12-2113:43:16–> 2010-12-21 13:46:05

The recovery process, first full recovery, then the log incremental recovery skips the first point of failure, resumes the log increment to the second point of failure, and then skips the second point of failure to resume to the endpoint as follows:

Copy from the backup file server first, unzip, and then restore

Shell>/root/s3cmd/s3cmd Get S3://csf-backup/mysqldb/mysql_20101216_231900.sql.tar.gz/usr/mysqlrestore/

Shell>/root/s3cmd/s3cmd Get S3://csf-backup/mysqldb/mysql_binlog_20101217_030100.tar.gz/usr/mysqlrestore/

Shell>cd/usr/mysqlrestore/

SHELL>TAR-XVF mysql_20101216_231900.sql.tar.gz

SHELL>TAR-XVF mysql_binlog_20101217_030100.tar.gz

To refresh the log:

Mysql>flush logs;

Full recovery:

shell>/usr/local/mysql/bin/mysql-uroot-p123456 </tmp/mysqlbackup/mysql_20101216_231900.sql

The log increment is restored before the first point of failure:

Shell>/usr/local/mysql/bin/mysqlbinlog--stop-date= "2010-12-21 13:41:41" mysql-bin.000012| Mysql-u root-p123456;

Skip the point of failure before continuing to recover to the second point of failure:

Shell>/usr/local/mysql/bin/mysqlbinlog--start-date= "2010-12-21 13:42:36"--stop-date= "2010-12-2113:43:16" mysql-bin.000012 mysql-bin.000013|mysql-u root-p123456;

Skip the second point of failure when recovering to flushlogs

Shell>/usr/local/mysql/bin/mysqlbinlog--start-date= "2010-12-21 13:46:05"--stop-date= "2010-12-2113:46:33" mysql-bin.000012 mysql-bin.000013| Mysql-u root-p123456;

Check: Use the SQL statement to query the wrong table and whether the data has been recovered.

(6) Restore a single library:

Shell>/usr/local/mysql/bin/mysql-uroot-pmysql--DATABASE=CSF </tmp/mysqlbackup/backup_20101215_adb_003.sql

(7) Recovery based on various time-point situations in a single library:

Please refer to step (1) to (5) above for thesame procedure, as long as you add a space and a library name after the restore command.

(8) Restore a single table in a single library:

The tool provided by MySQL is not currently available for this step, and one option is to restore the entire library to a test data server, then export the SQL on this server to a single table and import the SQL into the production library.

To import a library on a test server:

Shell>/usr/local/mysql/bin/mysql-uroot-pmysql--DATABASE=CSF </tmp/mysqlbackup/backup_20101215_adb_003.sql

To export a single table from a test server:

Shell>/usr/local/mysql/bin/mysqldump--user=root-pmysql--port=3306--default-character-set=utf8-- Single-transaction=true "CSF" "tmp_excel_data_000728" >/tmp/mysqlbackup/backup_20101215_single_table_013.sql

Import this table record on the production library

Shell>/usr/local/mysql/bin/mysql-uroot-pmysql CSF </tmp/mysqlbackup/backup_20101215_single_table_013.sql

Check Data

Select* from CSF. tmp_excel_data_000728;

(9) Power failure recovery

After restarting the server, restart the database, MySQL automatically recovers, the database tasks and operations that are running when the power outage need to be re-executed again.

(10) Operating system crash recovery

After operating system recovery, restart the database, MySQL auto recovery, the system crashes when the database task is running and the operation needs to be re-executed again

(11) File system crash recovery

After the file system is restored, restart the database, the database tasks that are running when the system crashes, and the operations need to be re-executed again.

(12) Hardware bad block recovery

Re-disk, and then restore the database.

MySQL Backup solution--(using mysqldump and Binlog binary logs)