返回首页
当前位置: 主页 > 网络编程 > Php实例教程 >

实现解锁MySQL备份恢复的4种正确方法

时间:2017-06-08 22:24来源:知行网www.zhixing123.cn 编辑:麦田守望者

备份高于一切,今天汇总一下常用的几种备份方法,以及恢复的步骤。

一、mysqldump

在日常工作中,我们会使用mysqldump命令创建SQL格式的转储文件来备份数据库。或者我们把数据导出后做数据迁移,主备搭建等操作。mysqldump是一个逻辑备份工具,复制原始的数据库对象定义和表数据产生一组可执行的SQL语句。 默认情况下,生成insert语句,也能生成其它分隔符的输出或XML格式的文件。

shell> mysqldump [arguments] > file_name

我们简单来看一下日常的用法:

备份所有的数据库:

shell> mysqldump –all-databases > dump.sql (不包含INFORMATION_SCHEMA,performance_schema,sys,如果想要导出的话还要结合–skip-lock-tables和–database一起用)

 

备份指定的数据库:

shell> mysqldump –databases db1 db2 db3 > dump.sql

 

当我们只备份一个数据的时候可以省去 –databases 直接写成:mysqldump test > dump.sql 不过有一些细微的差别,如果不加的话,数据库转储输出不包含创建数据库和use语句,所以可以不加这个参数直接导入到其它名字的数据库里。

当然我们也可以只备份某个表 :

mysqldump –user [username] –password=[password] [database name] [table name] table_name.sql

 

了解了简单的一些用法后我们再着重看一下几个参数:

  • master-data 获取备份数据的Binlog位置和Binlog文件名,用于通过备份恢复的实例之间建立复制关系时使用,该参数会默认开启。
  • dump-slave 用于在slave上dump数据,建立新的slave。因为我们在使用mysqldump时会锁表,所以大多数情况下,我们的导出操作一般会在只读备库上做,为了获取主库的Relay_Master_Log_File和Exec_Master_Log_Pos,需要用到这个参数,不过这个参数只有在5.7以后的才会有
  • no-data, -d 不导出任何数据,只导出数据库表结构

刚刚我们说过在使用mysqldump的时候会锁表,我们来详细看一下它的锁机制。

我们开两个窗口,在第一个里面执行mysqldump -uroot -pxxxxx –master-data=2 –databases dbname > /tmp/dbnamedate +%F.sql 然后第二个窗口登陆进去,使用show process的命令可以看到目前dump的session正在执行。

SELECT /!40001 SQL_NO_CACHE / * FROM table_name; 可以看到这条SQL正在以no_cache的模式查询数据。

然后我们在同样的表上执行一下select,发现被阻塞了。光标一直不返回。

一般遇到这种文件,我们会想是不是有锁呢? 为了验证我们查看一下锁的信息,可以发现dump的进程实际上是加了锁的。

一般遇到这种文件,我们会想是不是有锁呢? 为了验证我们查看一下锁的信息,可以发现dump的进程实际上是加了锁的。

我们把具体的general_log打开,然后看一下当时的操作:

4101044 Query FLUSH /!40101 LOCAL / TABLES

4101044 Query FLUSH TABLES WITH READ LOCK

 

(关闭所有打开的表,同时对于所有数据库中的表都加一个读锁,直到显示地执行unlock tables,该操作常常用于数据备份的时候。)

4101044 Query SHOW MASTER STATUS

 

(这是因为我用了–master-data=2)

所以这个时候表就会被锁住。

如果我不加–master-data参数(mysqldump -uroot -pxx –databases db > /tmp/dbnamedate +%F.sql) mysql会显示的对每一张要备份的表执行 LOCK TABLES table_name1 READ,LOCK TABLES table_name2 READ ,并且也不会有读的阻塞。

那有没有不锁的方法,其实也是有的,就是使用–single-transaction把备份的操作放在一个事务里去进行。

带上–single-transaction参数的mysqldump备份过程:

如果是5.6版本的MySQL

在备份之间同样的先FLUSH TABLES WITH READ LOCK,然后设置事务级别SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ,然后开启一个事务START TRANSACTION进行备份,这个时候备份的过程就很意思,它先创建了一个savepoint,然后把数据库里的表依次的进行备份,备份完成了之后又回滚到了之前的savepoint,来保证数据的一致性。

如果是5.7版本的MySQL

备份前的操作相同,只是没有了savepoint。

不过不管是哪个版本,只有InnoDB表是在一个一致性的状态。其它的任何MyISAM表或内存表是没有用的。 mysqldump的优势是可以查看或者编辑十分方便,它也可以灵活性的恢复之前的数据。它也不关心底层的存储引擎,既适用于支持事务的,也适用于不支持事务的表。不过它不能作为一个快速备份大量的数据或可伸缩的解决方案。如果数据库过大,即使备份步骤需要的时间不算太久,但有可能恢复数据的速度也会非常慢,因为它涉及的SQL语句插入磁盘I/O,创建索引等等。 对于大规模的备份和恢复,更合适的做法是物理备份,复制其原始格式的数据文件,可以快速恢复:如果你的表主要是InnoDB表,或者如果你有一个InnoDB和MyISAM表,可以考虑使用MySQL的mysqlbackup命令备份。

恢复操作:

先看一下当前的数据:

dbadmin@test 11:10:34>select * from t;
+——-+
|  id  |
+——-+
|  1  |
+——-+
1 row in set (0.00 sec)

 

备份;

mysqldump -uroot -proot@1234 –master-data=1 test >test.sql

 

模拟增量操作:

dbadmin@test 11:15:17>insert into t values (2);

Query OK, 1 row affected (0.00 sec)

dbadmin@test 11:15:36>select * from t; +——+ | id | +——+ | 1 | | 2 | +——+ 2 rows in set (0.00 sec)

 

模拟误操作:

dbadmin@test 11:15:41>truncate table t;

Query OK, 0 rows affected (0.01 sec)

dbadmin@test 11:16:14>select * from t;

Empty set (0.00 sec)

 

模拟恢复操作:

step 1:找到误操作的log position

dbadmin@test 11:20:57>show master logs;

dbadmin@(none) 11:21:37>show binlog events in ‘mysql-bin.000004’;

 

查看可以看到是444。

step 2:恢复到备份

dbadmin@test 11:16:25>source test.sql
dbadmin@test 11:17:26>select from t;
+——-+
|  id  |
+——-+
|  1  |
+——-+
1 row in set (0.00 sec)

 

step 3: 因为我们在备份的时候使用了master-data的参数,所以可以直接看到备份时候的最后位置,然后应用中间的log。查看可以看到是187。

我们使用mysqlbinlog得到这一段时间的操作,其实我们也可以用这个工具得到操作后使用sed进行undo的操作。

mysqlbinlog –start-position=187 –stop-position=444 mysql-bin.000004 > increment.sql

dbadmin@test 11:44:37>source /u01/my3307/log/increment.sql dbadmin@test 11:44:50>select from t; +——+ | id | +——+ | 1 | | 2 | +——+

 

至此数据恢复。

顶一下
(1)
100%
踩一下
(0)
0%
标签(Tag):MYSQL MySQL备份恢复
------分隔线----------------------------
------分隔线----------------------------
发表评论
请自觉遵守互联网相关的政策法规,严禁发布色情、暴力、反动的言论。
评价:
表情:
验证码:点击我更换图片
猜你感兴趣