13518219792
下文我给大家简单讲讲关于什么是MySQL的逻辑备份与恢复，大家之前了解过相关类似主题内容吗？感兴趣的话就一起来看看这篇文章吧，相信看完什么是Mysql的逻辑备份与恢复对大家多少有点帮助吧。
为乳源等地区用户提供了全套网页设计制作服务，及乳源网站建设行业解决方案。主营业务为成都网站建设、成都网站制作、乳源网站设计，以传统方式定制建设网站，并提供域名空间备案等一条龙服务，秉承以专业、用心的态度为用户提供真诚的服务。我们深信只要达到每一位用户的要求，就会得到认可，从而选择与我们长期合作。这样，我们也可以走得更远！
MySQL中的逻辑备份是将数据库中的数据备份为一个文本文件，备份的文件可以被查看和编辑。在MySQL中，可以使用mysqldump工具来完成逻辑备份。我们可以使用以下3种方法调用mysqldump。
备份指定的数据库或者此数据库中的某些表。
shell> mysqldump [options] dbname [tables]
备份指定的一个或多个数据库。
shell> mysqldump [options] --databases db1 [db2 db3 ...]
备份所有数据库。
shell> mysqldump [options] --all-databases
如果没有指定数据库中的任何表，默认导出所有数据库中的所有表。
例子：
1) 备份所有数据库
[root@rhel6 mysql]# mysqldump -uroot -p123456 --all-databases > all.sql
2) 备份数据库test
[root@rhel6 mysql]# mysqldump -uroot -p123456 --databases test > test.sql
3) 备份数据库test下的emp表
[root@rhel6 mysql]# mysqldump -uroot -p123456 test emp > test_emp.sql
4) 备份数据库test下的emp和ts表
[root@rhel6 mysql]# mysqldump -uroot -p123456 test emp ts > emp_ts.sql
5) 备份数据库test下的emp表为逗号分割的文档，备份到/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]# ls emp.sql emp.txt [root@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
完全恢复例子
--查看当前状态 [root@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 --做一次全备 [root@rhel6 tmp]# mysqldump -uroot -p -l -F test > test.sql Enter password: ----- 其中-l参数表示给所有的表加读锁，-F表示生成一个新的日志文件。 --查看emp当前数据，并做更改 [root@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 --模拟恢复 [root@rhel6 tmp]# mysql -uroot -p test < test.sql Enter password: --查看恢复后的状态 [root@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 [root@rhel6 tmp]# mysqlbinlog /var/lib/mysql/mysqlbin.000033 --stop-datetime='2016-11-29 15:06:48' |mysql -uroot -p Enter password: --查看emp表所有数据已全部恢复回来 [root@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
大家觉得什么是Mysql的逻辑备份与恢复这篇文章怎么样，是否有所收获。如果想要了解更多相关，可以继续关注我们的行业资讯板块。
