Logical backup and restore of MySQL

In MySQL, the greatest advantage of a logical backup is that the same method can be used to back up the various storage engines, while physical backups are different, and different storage engines have different backup methods. Therefore, it is simpler to use logical backups for databases mixed with different storage engines. The MySQL environment used in this article is 5.6.34.

1. Backup

The logical backup in MySQL is to back up the data in the database as a text file, and the backed up files can be viewed and edited. In MySQL, you can use the Mysqldump tool to complete a logical backup. We can call mysqldump using the following 3 methods.

• Backs up the specified database or some tables in this database.

Shell> mysqldump [Options] dbname [tables]

• Backs up one or more databases that you specify.

Shell> mysqldump [Options]--databases DB1 [DB2 db3 ...]

• Back up all databases.

Shell> mysqldump [Options]--all-databases

If you do not specify any tables in the database, all tables in all databases are exported by default.

Example:

1) Back Up all databases

[Email protected] mysql]# mysqldump-uroot-p123456--all-databases > All.sql

2) Back up the database test

[Email protected] mysql]# mysqldump-uroot-p123456--databases Test > Test.sql

3) Back up the EMP table under Database test

[[email protected] mysql]# mysqldump-uroot-p123456 test emp > Test_emp.sql

4) Back up the EMP and TS tables under Database test

[[email protected] mysql]# mysqldump-uroot-p123456 test emp ts > Emp_ts.sql

5) Backup the EMP table under the database test as a comma-separated document, back up to/tmp

[[email protected] tmp]# MYSQLDUMP-UROOT-P123456-T/tmp test emp--fields-terminated-by ', ' warning:using a password on The command line interface can insecure. [Email protected] tmp]# lsemp.sql emp.txt[[email protected] tmp]# more Emp.txt 1,zx,2016-01-01,9999-12-31,lx,501,zx, 2016-01-01,9999-12-31,zx,50

Get help with Mysqldump mysqldump--help

It should be emphasized that in order to ensure the consistency of data backup, the MyISAM storage engine needs to add the-l parameter to the backup, which means that all tables are read-locked, and all tables will be read-only and not data-updated during the backup. However, for the transactional storage engine (InnoDB and BDB), there is a better option--single-transaction, which will allow the InnoDB storage engine to take a snapshot (Snapshot) so that the backed-up data can be consistent.

2. Full recovery

Mysqldump recovery is also very simple, the backup as input execution, the specific syntax is as follows:

Mysql-uroot-p dbname < Bakfile

Note that after restoring the backup, the data is not complete and the logs executed after the backup need to be redo, with the following syntax:

Mysqlbinlog Binlog-file |mysql-uroot-p

Full Recovery Example

--View current status [[Email protected] 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 ()                |+----- ----------------+| 2016-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-time [[email protected]  Tmp]# mysqldump -uroot -p -l -f test > test.sqlenter password:  -----   where the-l parameter represents the read lock for all tables,-F indicates that a new log file is generated. --View the current EMP data and make changes [[email protected] 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;+-----------------+----------+--------------+----- -------------+-------------------+|&NBSp File            | position | binlog _do_db | binlog_ignore_db | executed_gtid_set |+-----------------+----------+-------- ------+------------------+-------------------+| mysqlbin.000033 |       120 |              |                   |                    |+ -----------------+----------+--------------+------------------+-------------------+1 row in set   (0.00&NBSP;SEC) Mysql> select now (), +---------------------+| now ()                 |+---------------------+|  2016-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, ' wl ', ' WL ', 50); query ok, 1 row affected  (0.01 sec) 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.01 sec) mysql>  Select now (); +---------------------+| now ()                  |+---------------------+| 2016-11-29 15:06:48 |+---------------------+1 row  in set  (0.01&NBSP;SEC) mysql> exitbye--analog recovery [[email protected] tmp]#  mysql -uroot -p test < test.sql enter password: --View the status after recovery [[email  protected] 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 (0.00 sec) mysql> exitbye--use Binlog to restore the last fully-prepared log, and to specify Stop-datetime for the time of the failure, to use when the same library is restored, to avoid the binlog[[that occurs when the recovery is applied Email protected] tmp]# mysqlbinlog /var/lib/mysql/mysqlbin.000033 --stop-datetime= ' 2016-11-29 15:06:48 '  |mysql -uroot -penter password: --view the EMP table all the data has been restored back [email  protected] 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. Not fully recovered

Due to misoperation, such as mistakenly deleted a table, then use full recovery is no use, because there are still errors in the log Operation statement, we need to revert to the state before the error operation, and then skip the error operation statement, and then recover the statements executed later, complete our recovery. This recovery is called incomplete recovery, in MySQL, incomplete recovery is divided into point-in-time recovery and location-based recovery.

1) restore operation steps based on point-in-time

A. If an error occurs at 10 o'clock in the morning, you can use the following statements to restore data to the previous Binlog

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

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

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

2) Location-based recovery

is similar to a point-in-time recovery, but more precise because multiple SQL statements may be executed simultaneously at the same point in time. The following actions are restored:

A. Analyze the binlog of the time period of misoperation

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

Find the position number before and after the error statement from the Mysql_restore.sql, if the front and rear position numbers are 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

This article is from the "DBA fighting!" blog, so be sure to keep this source http://hbxztc.blog.51cto.com/1587495/1877759

Logical backup and restore of MySQL