解锁MySQL备份恢复的4种正确姿势(2)

发布时间：2021-01-21 03:46 所属栏目：53 来源：网络整理

导读：在备份之间同样的先FLUSH TABLES WITH READ LOCK,然后设置事务级别SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ,然后开启一个事务START TRANSACTION进行备份,这个时候备份的过程就很意思,它先创建了一

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

如果是5.7版本的MySQL

解锁MySQL备份恢复的4种正确姿势

备份前的操作相同,只是没有了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 | +——+

至此数据恢复.

二、mysqlbackup

mysqlbackup是Oracle公司提供的针对企业的备份软件,全名叫做MySQL Enterprise Backup,是一个收费的软件.

下载地址：https://www.mysql.com/products/enterprise/backup.html?,可以试用下载.

我们简单来看一下这个工具的使用.

查看所有的帮助：

解锁MySQL备份恢复的4种正确姿势

我这里只是截取了一小部分,这个帮助很长,参数很多,功能很全,是oracle官方主推的备份方式.

全量备份

mysqlbackup –user=root –password=ucjmh –databases=’t1′ –encrypt-password=1 –with-timestamp –backup-dir=/u01/backup/ backup

解释一下参数：

databases 要备份的数据库
with-timestamp 产生一个当前时间的备份目录.mysqlbackup这个工具要求一个空目录才能做备份.所以这个会常用.
backup-dir 备份的目录 .
compress：压缩备份这个提供了多种压缩方法和压缩级别.1–9,压缩比依次递增.

backup 是备份的方式,一共有如下几种方式,我会在一个恢复案例里把常用的几个都用到.

Backup operations: backup,backup-and-apply-log,backup-to-image

Update operations: apply-log,apply-incremental-backup

Restore operations: copy-back,copy-back-and-apply-log

Validation operation: validate

Single-file backup operations: image-to-backup-dir,backup-dir-to-image,list-image,extract

（编辑：ASP站长网）