MySQL backup solution -- & gt; (using mysqldump and binlog binary logs) _ MySQL

MySQL backup solution-amp; gt; (using mysqldump and binlog binary logs) mysqldump

BitsCN.com

MySQL backup solution --> (using mysqldump and binlog binary logs)

As data increases and the innodb storage engine is compatible with the future, the mysqldump full backup + incremental log backup policy is considered. Using mysqldump provides good support for most mysql storage engines such as myisam and innodb.

Solution 1: mysqldump full backup + log incremental backup

1. mysqldump backup solution:

Full Backup at on Monday

Incremental backup from Tuesday to Sunday at A.M.

2. backup steps

(1) create a backup directory and store the backup script 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 log

If the log is not enabled, binlog must be enabled. to restart mysqld, first disable mysqld, open/etc/my. cnf, and add the following lines:

[Mysqld]

Log-bin

Then restart mysqld, which will generate a hostname-bin.000001 as well as a hostname-bin.index, the previous log file is to record all the update operations on the data, the subsequent file is the index that stores all binary files and cannot be easily deleted.

(3) full backup and incremental backup.

For details, see the mysqlFullBackup. sh and mysqlDailyBackup. sh scripts (note the backup directory, mysql software installation directory, compressed file name, and username and password in the script. if there is any inconsistency, modify it ).

Below are some test commands manually executed by a single 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 the crontab task and execute the backup script every day.

Shell> crontab-e

# Execute the full backup script at every Sunday.

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

# Perform incremental backup at from Monday to Saturday

0 3 ** 1-6/root/MySQLBackup/mysqlDailyBackup. sh>/dev/null 2> & 1

(5) clear old backup files.

Check the backup disk space every day and delete the old backup compressed files.

3. database recovery

[Note] If an ERROR similar to "ERROR 1051 (42S02) at line 32: Unknown table 'Fin _ cell_attr_his '" is reported in the incremental recovery operation, create an empty table, then perform incremental recovery.

Mysql> createtable tb1 ......;

(1) full recovery:

The file backed up with mysqldump is an SQL script that can be directly poured into. you can use the mysql command to restore the last full backup:

Copy the file from the backup file server, decompress it, and restore it. 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

Refresh log:

Mysql> flush logs;

Restore the last full backup command:

Shell>/usr/local/mysql/bin/mysql-uroot-p123456

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) insert point data failure misoperation after full backup on the current day based on time point Recovery 1:

If the insert data after full backup in the morning is accidentally deleted, a misoperation occurs. you can directly use the bin-log to restore the database to the moment before, and then skip the failure time, and then restore all the following operations. the command is as follows:

Refresh log:

Mysql> flush logs;

Copy the binlog from the backup file server and decompress 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

Before the log incremental recovery to the fault point

Shell>/usr/local/myql/bin/mysqlbinlog -- stop-date = "10:59:59" mysql-bin.00011 | mysql-u root-p 123456

Skip the time point of the fault, continue executing the subsequent binlog, and complete the recovery

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

Check:

Use SQL commands such as select * from tb to check whether the table and data are restored.

(3) Data misoperation at on the same day based on time point recovery 2:

If the insert data after full backup in the morning is accidentally deleted, a misoperation occurs. if the lost data is the data entered before the last backup, you must first use the full backup file for full recovery. then, you can use the bin-log to restore the database to the moment before, then, the fault time is skipped at and all the following operations are resumed. the procedure is as follows:

Refresh log:

Mysql> flush logs;

Copy it from the backup file server, decompress it, and restore it.

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

Before the log incremental recovery to the fault point

Shell>/usr/local/myql/bin/mysqlbinlog -- stop-date = "10:59:59" mysql-bin.00011 | mysql-u root-p 123456

Skip the time point of the fault, continue executing the subsequent binlog, and complete the recovery

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

Check:

Use SQL commands such as select * from tb to check whether the table and data are restored.

(4) Data misoperation at yesterday:

The recovery process is as follows:

Copy it from the backup file server, decompress it, and restore it.

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

Refresh log:

Mysql> flush logs;

Full recovery:

Shell>/usr/local/mysql/bin/mysql-uroot-p123456 </tmp/mysqlbackup/mysql_20101216_231900. SQL

Log incremental recovery to fault point:

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

Skip the fault point and continue 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) recovery based on time points 4. recovery from multiple fault points, for example, data misoperation from two fault points:

First fault point: 2010-12-211:413:42:36->

Second fault point: 2010-12-211:43:16-> 13:46:05

The restoration process is as follows:

Copy it from the backup file server, decompress it, and restore it.

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

Refresh log:

Mysql> flush logs;

Full recovery:

Shell>/usr/local/mysql/bin/mysql-uroot-p123456 </tmp/mysqlbackup/mysql_20101216_231900. SQL

Before the log incremental recovery to the first fault point:

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

Skip the fault point and continue to restore to the second fault point:

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

When the second fault point is skipped and restored to flushlogs

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

Check: use SQL statements to query tables with incorrect operations and whether the data has been recovered.

(6) restore a single database:

Shell>/usr/local/mysql/bin/mysql-uroot-pmysql -- database = csf

(7) recovery based on various time points in a single database:

Refer to the above steps (1) to (5). The process is basically the same. you only need to add a space and the database name after the recovery command.

(8) restore a single table in a single database:

Currently, the tool provided by Mysql cannot perform this step. one solution is to restore the entire database to a test data server and then export the SQL statement of a single table on this server, import the SQL statement to the production database.

Import the database to the test server:

Shell>/usr/local/mysql/bin/mysql-uroot-pmysql -- database = csf

Export a single table from the 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 to the production database

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 supply fault recovery

After the server is restarted, the database is restarted and mysql is automatically restored. the database tasks and operations that are running during power failure must be re-executed.

(10) operating system crash recovery

After the operating system is restored, the database is restarted and mysql is automatically restored. when the system crashes, the running database tasks and operations need to be re-executed.

(11) file system crash recovery

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

(12) hardware bad block recovery

Repeat the disk and restore the database.

BitsCN.com