下文我给大家简单讲讲关于什么是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的逻辑备份与恢复
网页路径：
http://cdkjz.cn/article/goidoh.html
