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

Let me tell you a little bit about what is the logical backup and recovery of Mysql. Have you learned about similar topics before? If you are interested, let's take a look at this article. I believe it will be of some help to you after reading what is the logical backup and recovery of Mysql.

Logical backup in MySQL is to back up the data in the database as a text file, which can be viewed and edited. In MySQL, you can use the mysqldump tool to complete a logical backup. We can call mysqldump in the following three ways.

Back up the specified database or some tables in this database.

Shell > mysqldump [options] dbname [tables]

Back up one or more specified databases.

Shell > mysqldump [options]-- databases db1 [db2 db3...]

Back up all databases.

Shell > mysqldump [options]-- all-databases

If no tables in the database are specified, all tables in all databases are exported by default.

Example:

1) back up all databases

[root@rhel6 mysql] # mysqldump-uroot-p123456-- all-databases > all.sql

2) backup database test

[root@rhel6 mysql] # mysqldump-uroot-p123456-- databases test > test.sql

3) back up the emp table under the database test

[root@rhel6 mysql] # mysqldump-uroot-p123456 test emp > test_emp.sql

4) backup the emp and ts tables under the database test

[root@rhel6 mysql] # mysqldump-uroot-p123456 test emp ts > emp_ts.sql

5) backup the documents separated by commas in the emp table under the backup database test, and back up to / tmp

[root@rhel6 tmp] # mysqldump-uroot-p123456-T / tmp test emp-- fields-terminated-by', 'Warning: Using a password on the command line interface can be insecure. [root@rhel6 tmp] # lsemp.sql emp.txt [root@rhel6 tmp] # more emp.txt 1

Get help from mysqldump mysqldump-help

It should be emphasized that in order to ensure the consistency of the data backup, the MyISAM storage engine needs to add the-l parameter to the backup, which means that all tables will be locked by read. During the backup, all tables will only be read and cannot be updated. But for transactional storage engines (InnoDB and BDB), a better option, single-transaction, can be used, which will give the InnoDB storage engine a Snapshot to ensure the consistency of the backed up data.

2. Full recovery

The recovery of mysqldump is also very simple. You can perform the backup as input. The specific syntax is as follows:

Mysql-uroot-p dbname

< bakfile 注意，将备份恢复后数据并不完整，还需要将备份后执行的日志进行重做，语法如下： mysqlbinlog binlog-file |mysql -uroot -p 完全恢复例子 --查看当前状态[root@rhel6 tmp]# mysql -uroot -p123456Warning: Using a password on the command line interface can be insecure.Welcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 17Server version: 5.6.34-log MySQL Community Server (GPL)Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql>

Select now (); +-+ | now () | +-+ | 0-11-29 15:02:45 | +-+ 1 row in set (0.00 sec) mysql > show master status +-+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +- -+ | mysqlbin.000032 | 13477 | +- -+ 1 row in set (0.00 sec) mysql > select @ @ autocommit +-+ | @ @ autocommit | +-+ | 1 | +-+ 1 row in set (0.00 sec) mysql > show variables like 'autocommit' +-+-+ | Variable_name | Value | +-+-+ | autocommit | ON | +-+-+ 1 row in set (0.02 sec) mysql > exitBye-- do a full [root@rhel6 tmp] # mysqldump-uroot-p-l-F test > test.sqlEnter password:-where the-l parameter indicates that all tables are read locked -F means to generate a new log file. -- View the current emp data and make changes [root@rhel6 tmp] # mysql-uroot-p123456Warning: Using a password on the command line interface can be insecure.Welcome to the MySQL monitor. Commands end with; or\ g.Your MySQL connection id is 20Server version: 5.6.34-log MySQL Community Server (GPL) Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or'\ h' for help. Type'\ c'to clear the current input statement.mysql > show master status +-+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +- -+ | mysqlbin.000033 | 120 | +- -+ 1 row in set (0.00 sec) mysql > select now () +-+ | now () | +-+ | 0-11-29 15:06:11 | +-+ 1 row in set (0.00 sec) mysql > select * from test.emp +-+-+ | id | ename | hired | separated | job | store_id | + -+ | 1 | zx | 2016-01-01 | 9999-12-31 | lx | 50 | 1 | zx | 2016-01-01 | 9999-12-31 | zx | 50 | +-+ 2 rows in set (0.00 sec) mysql > insert into test.emp (id) Ename,job,store_id) values (2 recordings and wlads) Query OK, 1 row affected (0.01sec) mysql > select * from test.emp +-+-+ | id | ename | hired | separated | job | store_id | + -+ | 1 | zx | 2016-01-01 | 9999-12-31 | lx | 50 | | 2 | wl | 2016-01-01 | 9999-12-31 | wl | 50 | 1 | zx | 2016-01-01 | 9999-12-31 | zx | 50 | +-+ -+-+ 3 rows in set (0.00 sec) mysql > show master status +-+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +- -+ | mysqlbin.000033 | 362 | +- -+ 1 row in set (0.01sec) mysql > select now () +-+ | now () | +-+ | 0-11-29 15:06:48 | +-+ 1 row in set (0.01sec) mysql > exitBye-- simulated recovery [root@rhel6 tmp] # mysql-uroot-p test

< test.sql Enter password: --查看恢复后的状态[root@rhel6 tmp]# mysql -uroot -p123456Warning: Using a password on the command line interface can be insecure.Welcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 22Server version: 5.6.34-log MySQL Community Server (GPL)Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql>

Select * from test.emp +-+-+ | id | ename | hired | separated | job | store_id | + -+ | 1 | zx | 2016-01-01 | 9999-12-31 | lx | 50 | 1 | zx | 2016-01-01 | 9999-12-31 | zx | 50 | +-+-+ 2 rows in set Sec) mysql > exitBye-- uses binlog to restore the logs since the last complete log And specify stop-datetime as the time when the failure occurs, and use it when recovering from the same library to avoid the binlog [root@rhel6 tmp] # mysqlbinlog / var/lib/mysql/mysqlbin.000033-- stop-datetime='2016-11-29 15root@rhel6 tmp 06buret 48' | mysql-uroot-pEnter password:-- check that all the data in the emp table has been restored [root@rhel6 tmp] # mysql-uroot-p123456Warning: Using a password on the command line interface can be insecure.Welcome to the MySQL monitor. Commands end with; or\ g.Your MySQL connection id is 26Server version: 5.6.34-log MySQL Community Server (GPL) Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or'\ h' for help. Type'\ c' to clear the current input statement.mysql > select * from test.emp +-+-+ | id | ename | hired | separated | job | store_id | + -+ | 1 | zx | 2016-01-01 | 9999-12-31 | lx | 50 | | 2 | wl | 2016-01-01 | 9999-12-31 | wl | 50 | 1 | zx | 2016-01-01 | 9999-12-31 | zx | 50 | +-+ -+-+ 3 rows in set (0.00 sec)

3. Incomplete recovery

Due to misoperation, such as deleting a table by mistake, it is useless to use full recovery at this time, because there are still misoperation statements in the log, what we need is to return to the state before the misoperation, and then skip the misoperation statement. then restore the statements executed later to complete our recovery. This kind of recovery is called incomplete recovery. In MySQL, incomplete recovery is divided into point-in-time recovery and location-based recovery.

1) recovery steps based on point in time

a. If a misoperation occurs at 10:00 in the morning, you can use the following statement to restore the data to before the failure using copy and binlog

Shell > mysqlbinlog-- stop-datetime='20161129 09Viru 59 var/log/mysql/mysqlbin.000033 / var/log/mysql/mysqlbin.000033 | mysql-uroot-p

b. Skip the point in time at the time of the failure and continue with the subsequent binlog to complete the recovery.

Shell > mysqlbinlog-- start-datetime='20161129 10 start-datetime='20161129 01VR 00' / var/log/mysql/mysqlbin.000033 | mysql-uroot-p

2) location-based recovery

Similar to a point-in-time recovery, but more accurate because multiple sql statements may be executed at the same point in time. The restore operation is as follows:

a. Analyze the binlog of the misoperation period

Shell > mysqlbinlog-- start-datetime='20161129 09 start-datetime='20161129 55 stop-datetime='20161129 00'-- stop-datetime='20161129 10 15 05 var/log/mysql/mysqlbin.000033 > / tmp/mysql_restore.sql

Find the location number before and after the error statement in mysql_restore.sql, if the location number is 3682 and 3685, respectively.

b. Restore using the following command

Shell > mysqlbinlog-- stop-position=3682 / var/log/mysql/mysqlbin.000033 | mysql-uroot-p

Shell > mysqlbinlog-- start-position=3685 / var/log/mysql/mysqlbin.000033 | mysql-uroot-p

What do you think of what is the logical backup and recovery of Mysql? what do you think of this article and whether it has gained anything? If you want to know more about it, you can continue to follow our industry information section.

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.