什么是Mysql的逻辑备份与恢复

下文我给大家简单讲讲关于什么是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的逻辑备份与恢复这篇文章怎么样，是否有所收获。如果想要了解更多相关，可以继续关注我们的行业资讯板块。    

当前名称：什么是Mysql的逻辑备份与恢复
分享URL：http://shouzuofang.com/article/goidoh.html