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

This article mainly introduces the use of Mysqldump recovery backup method, the content of the article is carefully selected and edited by the author, with a certain pertinence, the reference significance for everyone is still relatively large, the following with the author to understand the subject content bar.

mysql is a logical backup tool: based on mysql client protocol, remote implementation, remote backup, to consider disk I/O, it is recommended not to use multiple engines in the same library One of the reasons is that backup tools support different degrees

Full backup, partial backup;

InnoDB: hot standby or warm standby;

MyISAM: warm standby;

Secondary packaging tools:

mydumper: perl script, which simulates parallel backup and consumes IO of Cloud Virtual Machine

phpMyAdmin

The backup mechanism is to create the database first, then create the table, and finally insert all the data through insert into

mysqldump backup mechanism: first create a library, then create related tables, and finally insert data into the table

Library: create database

Table: Create table

Data: insert into, use an insert into statement to insert all the data of the table and complete the recovery. Each table must be created first and then inserted into to restore it.

Executing myslqdump will put all the contents on the screen now, and then in the step-by-step operation, you can see the specific operation in the displayed results, if you want to restore, you need to redirect these displayed results to a file, and use the newly generated file to restore.

mysqldump usage, there are three, as follows:

Method 1:

mysqldump [OPTIONS] database [tables] #Backup a single database, only a part of the tables can be backed up (partial backup);

Method 2:

mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...] #Backup multi-library;, recommended in this way, even backup a single library, because you do not have to create your own library

Examples:

[root@CentOS7E ~]#mysqldump -u root -pPass123456 -h 192.168.1.71 --databases sunny

After executing the statement, the contents of the sunny database will be printed to the screen. At this time, there is no redirection to the relevant files and cannot be used for recovery, but you can view the operation performed using the mysqldump command procedure.

Method 3:

mysqldump [OPTIONS] --all-databases [OPTIONS] #Backup all libraries; also create your own libraries

Note: When performing backup, add options to lock the corresponding table to prevent inconsistent time points and cannot be used to restore data.

MyISAM storage engine: support warm backup, lock tables during backup;

-x, --lock-all-tables: lock all tables of all libraries, read lock; lock range is large.

-l, --lock-tables: lock all tables in the specified library; when backing up which table, only lock the corresponding table

InnoDB storage engine: support warm standby and hot standby;

You can use-x or-l to achieve warm standby

--single-transaction: Create a transaction, perform backup based on this snapshot, and realize hot backup; however, if some things at the time of backup are not committed or rolled back, resulting in problems in recovering data, so recovery from crash must be performed during recovery, so recovery after crash must be used to achieve data integrity.

Other options:

-R, --routines: Backup stored procedures and stored functions of specified libraries;

--triggers: triggers for backing up the specified library;

-E, --events：

example

Data backup and restore with mysql

Hot standby, add relevant options, redirect to file/root/sunny.sql

[root@CentOS7E sunny]#mysqldump -u root -pPass123456 -h 192.168.1.71 --single-transaction -R --triggers -E --databases sunny > /root/sunny.sql

Example: Using redirection, restore the generated file to the local mysql library. If there is already a local database, you will not create a database with the same name. You will directly create a new table and import the data into the new table.

Note: Before recovery, close the binary log of the recovered local Cloud Virtual Machine. Otherwise, the newly imported data will be recorded in the binary log. However, this part of the log is the recovered file and does not need to be recorded again

Close binary log function as follows

MariaDB [sunny]> set @@session.sql_log_bin=off;

recover the database

[root@CentOS7E ~]#mysql -uroot -pPass1234 /root/sunny-$(date +%F-%H-%M-%S).sql

[root@CentOS7E ~]#less sunny-2018-01-15-13-10-17.sql

Check out the newly generated backup file with the following key comment information:

When restoring, you can restore from the 245th byte of the file master-log.000005, because there was CHANGE MASTER TO=2 in the last backup, and there was scrolling in the last backup. When restoring, you just need to replay the contents after 245.

-- CHANGE MASTER TO MASTER_LOG_FILE='master-log.000005', MASTER_LOG_POS=245;

Assuming that the sunny database on Cloud Virtual Machine 71 is operating at this time, the newly generated log will be recorded to binary log master-log.000005, assuming that 71 operates as follows

MariaDB [sunny]> delete from students where id=1000;

MariaDB [sunny]> delete from students where id=1002;

MariaDB [sunny]> insert into students(id,name,age,gender) values (1050,"new005",18,"M");

At this point, master-log.000005 at 71 will record these newly generated logs, as shown below

[root@CentOS7A mysql]#mysqlbinlog /mydata/log/master-log.000005

Restore to 75, the mysql Cloud Virtual Machine that just imported full backup

Recovery operations are as follows

First, redirect the file master-log.000005 to restore it, then copy it to 75 for playback

Redirect, mysqlbinlog This tool will convert the data into sql statements after reading it. Note that-j 245 can not be specified here, because there is no valid content before 245 bytes. If it starts after 245, pay attention to adding the-j option.

[root@CentOS7A mysql]#mysqlbinlog -j 245 /mydata/log/master-log.000005 >/tmp/binlog005.sql

copy data

[root@CentOS7A mysql]#scp /tmp/binlog005.sql 192.168.1.75:/root/

Import binlog005.sql back into the database at 75

[root@CentOS7E ~]#mysql -uroot -pPass123456 < binlog005.sql

Note that when using mysqldump for backup in the future, it is recommended to copy a copy of the binary file for abnormal backup. When backing up in the future, use mysqlbinlog command to read out the file that generates mysql statement, and then restore the binary file. However, mysqldump recovery efficiency is low, it is recommended to use xtrabackup this tool to achieve

After reading the above on the use of Mysqldump recovery backup method, many readers must have some understanding, if you need to obtain more industry knowledge information, you can continue to pay attention to our industry information column.

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.