Mysql的逻辑备份与恢复

32121111

在MySQL中，逻辑备份的最大优点是对于各种存储引擎都可以使用同样的方法来备份；而物理备份则不同，不同的存储引擎有着不同的备份方法。因此对于不同的存储引擎混合的数据库，用逻辑备份会更简单一些。本文使用的MySQL环境是5.6.34。1、备份

  MySQL中的逻辑备份是将数据库中的数据备份为一个文本文件，备份的文件可以被查看和编辑。在MySQL中，可以使用mysqldump工具来完成逻辑备份。我们可以使用以下3种方法调用mysqldump。

• 备份指定的数据库或者此数据库中的某些表。
  
  

    shell> mysqldump [options] dbname [tables]

• 备份指定的一个或多个数据库。
  
  

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

• 备份所有数据库。
  
  

    shell> mysqldump [options] --all-databases
  如果没有指定数据库中的任何表，默认导出所有数据库中的所有表。
  例子：

  1) 备份所有数据库

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

  2) 备份数据库test

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

  3) 备份数据库test下的emp表

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

  4) 备份数据库test下的emp和ts表

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

5) 备份数据库test下的emp表为逗号分割的文档，备份到/tmp

1 2 3 4 5 6 7

[iyunv@rhel6 tmp]# mysqldump -uroot -p123456 -T /tmp test emp --fields-terminated-by ',' Warning: Using a password on the command line interface can be insecure. [iyunv@rhel6 tmp]# ls emp.sql  emp.txt [iyunv@rhel6 tmp]# more emp.txt 1,zx,2016-01-01,9999-12-31,lx,50 1,zx,2016-01-01,9999-12-31,zx,50

获取mysqldump的帮助 mysqldump --help

  需要强调的是，为了保证数据备份的一致性，MyISAM存储引擎在备份是需要加上-l参数，表示将所有表加上读锁，在备份期间，所有表将只能读而不能进行数据更新。但是对于事务存储引擎(InnoDB和BDB)来说，可以采用更好的选项--single-transaction，此选项将使得InnoDB存储引擎得到一个快照(Snapshot)，使得备份的数据能够保证一致性。
2、完全恢复

  mysqldump的恢复也很简单，将备份作为输入执行即可，具体语法如下：
  mysql -uroot -p dbname < bakfile
  注意，将备份恢复后数据并不完整，还需要将备份后执行的日志进行重做，语法如下：
  mysqlbinlog binlog-file |mysql -uroot -p
  完全恢复例子

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180

--查看当前状态 [iyunv@rhel6 tmp]# mysql -uroot -p123456 Warning: 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 17 Server 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 its affiliates. Other names may be trademarks of their respective owners. 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> exit Bye --做一次全备 [iyunv@rhel6 tmp]# mysqldump -uroot -p -l -F test > test.sql Enter password: -----  其中-l参数表示给所有的表加读锁，-F表示生成一个新的日志文件。 --查看emp当前数据，并做更改 [iyunv@rhel6 tmp]# mysql -uroot -p123456 Warning: 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 20 Server 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 its affiliates. Other names may be trademarks of their respective owners. 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()               | +---------------------+ | 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 sec) mysql> exit Bye --模拟恢复 [iyunv@rhel6 tmp]# mysql -uroot -p test < test.sql Enter password: --查看恢复后的状态 [iyunv@rhel6 tmp]# mysql -uroot -p123456 Warning: 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 22 Server 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 its affiliates. Other names may be trademarks of their respective owners. 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> exit Bye --使用binlog恢复上次全备后的日志，并指定stop-datetime为出故障的时间，同库恢复时使用，避免应用恢复时产生的binlog [iyunv@rhel6 tmp]# mysqlbinlog /var/lib/mysql/mysqlbin.000033 --stop-datetime='2016-11-29 15:06:48' |mysql -uroot -p Enter password: --查看emp表所有数据已全部恢复回来 [iyunv@rhel6 tmp]# mysql -uroot -p123456 Warning: 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 26 Server 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 its affiliates. Other names may be trademarks of their respective owners. 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、不完全恢复

  由于误操作，比如误删除了一张表，这时使用完全恢复是没有用的，因为日志里还存在误操作语句，我们需要的是恢复到误操作之前的状态，然后跳过误操作语句，再恢复后面执行的语句，完成我们的恢复。这种恢复叫不完全恢复，在MySQL中，不完全恢复分为基于时间点的恢复和基于位置的恢复。
  1)基于时间点的恢复操作步骤
    a.如果上午10点发生了误操作，可以用以下语句使用份和binlog将数据恢复到故障前
    shell> mysqlbinlog --stop-datetime='20161129 09:59:59' /var/log/mysql/mysqlbin.000033 |mysql -uroot -p
    b.跳过故障时的时间点，继续执行后面的binlog，完成恢复。
    shell> mysqlbinlog --start-datetime='20161129 10:01:00' /var/log/mysql/mysqlbin.000033 |mysql -uroot -p

  2)基于位置恢复
    和基于时间点的恢复类似，但是更精确，因为同一个时间点可能有多条sql语句同时执行。恢复的操作如下：
    a.分析误操作时间段的binlog
    shell> mysqlbinlog --start-datetime='20161129 09:55:00' --stop-datetime='20161129 10:05:00' /var/log/mysql/mysqlbin.000033 > /tmp/mysql_restore.sql
    从mysql_restore.sql中找到出错语句前后的位置号，假如前后位置号分别是3682和3685。
    b.使用如下命令进行恢复
    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