Mysqldump for data backup and disaster recovery

Directory

1. Introduction of test environment

2. Backup strategy

3. Backup

4. Disaster recovery

5. Summary

1. Introduction of Test ring

Mysql> select version (); +------------+| version ()   |+------------+| 5.5.36- log |+------------+mysql> show databases;+--------------------+| database            |+--------------------+| information_schema | |  mydb1              | |  mysql              | |  performance_schema | |  test               |+-------- ------------+mysql> select * from mydb1.tb1;+----+------+------+| id |  name | age  |+----+------+------+|  1 | tom  |    10 | |   2 | jack |   20 |+----+------+------+

Prepare a two backup directory:

[Email protected] ~]# ls/backup/data_dir/#这个目录用来存放备份的数据文件 [[email protected] ~]# ls/backup/binlog_dir/#这个目录用来存放利用二进 Incremental backups made by the system log.

2. Backup strategy

Data backup and disaster recovery in a full backup + incremental backup.

3. Backup

3.1. Make a full backup of all database servers first:

[Email protected] ~]# mysqldump-uroot-p123456--lock-all-tables--flush-logs--events--routines--master-data=2--all -databases >/backup/data_dir/fulldata-' Date +%f '. sql

Option Description:

--lock-all-tables

Lock all the tables in the library, because when the whole library is backed up, MySQL default several libraries of the table in the storage engine is not InnoDB, but there is MyISAM, CSV, merge, etc., so the full library backup can not implement hot standby

--flush-logs

Refreshes the binary log so that the binary log files used by the current service are turned off, resulting in a new binary log file, which starts with a new binary log file at a later incremental backup

--master-data=2

Enabling this option logs the refreshed binary log file and the position point to the backup file at the time of the backup, making it easy to specify from which point backups should be made for incremental backups

[Email protected] ~]# Ls/backup/data_dir/fulldata-2015-04-14.sql

3.2, make data modification, so that it generates incremental data

Enter MySQL interactive mode, create databases, tables, insert data, and more:

mysql> Create DATABASE mydb2;mysql> use mydb2;mysql> create TABLE tb2 (ID int,name CHAR (15)); #只是新建了一个库和表, there are no data in the tables, and the operation of the table is recorded in the binary log, and our incremental backup is based on the binary log.

3.3. Incremental backup

Because the '--master-data=2 ' option is added to the full standby, it is possible to view the location of the binary log files and position used by the MySQL service in the backup file, which is a very important reference point for incremental backups, and because there are '-- Flush-logs ' option, so the binary log of the post-increment data is written in the new binary log file, so when doing an incremental backup only need to scroll the log again, the log backup before scrolling is the incremental binary log.

Open MySQL interactive interface, scroll log:

mysql> show master status;  #查看当前所使用的二进制日志文件 +------------------+----------+------------- -+------------------+| file              | position | binlog_do_db | binlog_ignore_db |+------------------+----------+--- -----------+------------------+| mysql-bin.000004 |      295 |               |                   |+------------------+----- -----+--------------+------------------+mysql> flush logs;  #滚动日志文件mysql > SHOW  master status;   #再次查看已是使用新的日志文件 +------------------+----------+--------------+------------------ +| file             | position  | binlog_do_db | binlog_ignore_db |+------------------+----------+--------------+------------------+|  mysql-bin.000005 |      107 |               |                   |+------------------+----------+--------------+--------- ---------+

Backup binary log files:

[[email protected] ~]# cp/var/log/mysql_log/mysql-bin.000004/backup/binlog_dir//mysql-bin.000004-increment. ' Date + %F ' [[email protected] ~]# ls/backup/binlog_dir/mysql-bin.000004-increment.2015-04-14

This binary log file is an incremental backup of the data, MySQL binary log files are not stored with the data, so as to avoid the loss of data directory after the use of binary files for disaster recovery, this test of binary logs stored under "/var/log/mysql_log", And the Data directory is under "/mydata/data".

3.4, modify the data, in order to do time-point recovery to do some data modification

Modify the database again to generate a new binary event

Mysql> INSERT into MYDB2.TB2 (id,name) VALUES (1, ' timepoint ');mysql> SELECT * from mydb2.tb2;+------+-----------+| ID |    Name |+------+-----------+| 1 | Timepoint |+------+-----------+

4. Disaster recovery

4.1, Analog database data loss

mysql> DROP DATABASE myddb1; Query OK, 1 row affected (0.02 sec) mysql> DROP DATABASE myddb2; Query OK, 1 row affected (0.03 sec)

4.2. Server offline

When the data is really lost, let the MySQL server offline, if in the field can directly dial off the network, if it is a remote server, it can restart the MySQL service, but do not enable network features:

[Email protected] ~]#/opt/lamp/mysql55/bin/mysqld_safe--skip-networking &

If the MySQL service does not start, the troubleshooting will not start, then have to reinstall the database before doing recovery operations.

4.3. Preparation of data recovery

To temporarily turn off the binary logging feature, the recovered operation does not need to be logged to the binary log file

mysql> set global sql_log_bin=0; query ok, 0 rows affected  (0.00 sec) mysql> show global  variables like  ' sql_log_bin '; +---------------+-------+| variable_name | value |+ ---------------+-------+| sql_log_bin   | off   |+---------------+---- ---+mysql> flush logs;  #滚动一下日志, because the last time to do a point of recovery, will be used after the incremental backup of the log file. mysql> show master status;  #确认日志已滚动 +------------------+----------+--------------+------ ------------+| file             |  position | binlog_do_db | binlog_ignore_db |+------------------+----------+---------- ----+------------------+| mysql-bin.000006 |      107 |               |     & nbsp;            |+------------------+----------+--- -----------+------------------+

4.4. Restore with full backup first:

[[email protected] ~]# mysql -uroot -p123456 < /backup/data_dir/ fulldata-2015-04-14.sqlmysql> show databases;+--------------------+| database            |+--------------------+| information_schema | |  mydb1              | |  mysql              | |  performance_schema | |  test               |+-------- ------------+mysql> select * from  mydb1.tb1;   #mydb1数据库的数据已恢复了 +----+--- ---+------+| id | name | age  |+----+------+------+|  1 |  tom  |   10 | |   2 | jack |   20 |+----+------+ +-----+ 

4.5. Restore data with incremental backups:

[[email protected] ~]# mysqlbinlog /backup/binlog_dir/ mysql-bin.000004-increment.2015-04-14 > /tmp/increment.sql  #先导出二进制文件为sql脚本类型 [[email  protected] ~]# mysql -uroot -p123456 < /tmp/increment.sql  # Data Recovery mysql> show databases;   #做增量备份时的mydb2数据库已恢复 +--------------------+| database            |+--------------------+| information_ schema | |  mydb1              | |  mydb2              | |  mysql              | |  performance_schema | |  test               |+-------- ------------+6 rows in set  (0.00&NBSP;SEC) mysql> select * from mydb2.tb2;  #在做增量备份时tb2表是没有数据的, just created this table Empty set   (0.00&NBSP;SEC)

4.6, point-in-time recovery

[[email protected] ~]# ls/backup/binlog_dir/mysql-bin.000004.incremental.2015-04-14 #这是增量备份的二进制文件, in doing an incremental backup is first "flush Logs ", so the next binary log file generated by the modification operation is that we did not do the backup, that is," mysql-bin.000005 "this binary file.

Use the Mysqlbinlog tool to view the mysql-bin.000005 binary log file and find the "drop table" statement, because the previously simulated database loss is a direct use of the DELETE statement, by observing that the statement that deletes the table in the "321" this position So:

[Email protected] ~]# Mysqlbinlog--stop-position=321/var/log/mysql_log/mysql-bin.000005 >/tmp/321ttim.sql[[  Email protected] ~]# mysql-uroot-p123456 </tmp/321ttim.sqlmysql> SELECT * from MYDB2.TB2; #已把表tb2中的数据恢复 +------+-----------+| ID |    Name |+------+-----------+| 1 | Timepoint |+------+-----------+

4.7. Finishing Work

Finally, set the value of the Sql_log_bin variable to 1 or restart the MySQL service:

mysql> SET GLOBAL sql_log_bin=1; [Email protected] ~]# service mysqld restart

You can use the Check Table command to validate a table.

5. Summary:

Mysqldump Tools for InnoDB storage engine tables can be hot standby, so in the backup can not lock the table, if the whole server for the library to do a backup, there is a MyISAM table, you need to lock the table before the backup, can only implement Win Bei, even if there are innodb tables in the library. This tool is a logical backup method, backed up files are SQL statements, backup and recovery are required to participate in the MySQL process, when the amount of data backup is large, the recovery time is very long, because the recovery is a one-piece SQL statement read after the execution in MySQL, is a write operation. It is only possible to perform a hot spare when backing up the InnoDB library, and it is recommended that you use this tool for backup operations when there is a small amount of data, but when the data grows this tool will not be suitable for MySQL backup recoverable operations, you should choose another better backup recovery scheme for your data.

This article is from the "knowledge needs summary and records" blog, please be sure to keep this source http://zhaochj.blog.51cto.com/368705/1632204

Mysqldump for data backup and disaster recovery