MySQL的逻辑备份和恢复（含binlog恢复）

在MySQL中，逻辑备份的最大优点是对于各种存储引擎都可以用同样的方法来备份；

备份

MSQL 中的逻辑备份是将数据库中的数据备份为一个文本文件，备份的文件可以被查看和编辑。在MySQL中，可以使用 mysqldump工具来完成逻辑备份。

备份命令：

-- 备份指定的数据库或者此数据库中的某些表
shel1> mysqldump [options] db_name [tables]

-- 备份指定的一个或多个数据库
shel1> mysqldump [options] ---database DBl [DB2 DB3...]

-- 备份所有数据库。
she11> mysqldump [options] --a11-database           

如果没有指定数据库中的任何表，默认导出所有数据库中的所有表。

举例：

-- 备份所有数据库:
[zzxelocalhost ~]$ mysqldump -uroot -p123456 --all-database > al1.sql

-- 备份数据库 test:
[zzxelocalhost ~]$ mysqldump -uroot -p123456 test > test.sql

-- 备份数据库 test 下的表emp:
[zzxelocalhost ~]$ mysqldump -uroot -p123456 test emp > emp.sql

-- 备份数据库 test 下的表emp和dept
[zzxelocalhost ~]$ mysqldump -uroot -p123456 test emp dept > emp_dept.sql

           

为了保证数据备份的一致性，MyISAM 存储引整在备份时需要加上-l参数表示将所有表加上读锁，在备份期间，所有表将只能读而不能进行数据更新。

但是对于事务存储引擎(InnoDB和BDB)来说，可以采用更好的选项--single-transaction，此选项将使得 InnoDB存储引擎得到一个快照，使得备份的数据能够保证一致性。

完全恢复

mysqldump 的恢复也很简单，将备份作为输入执行即可，具体语法如下：

mysql-urout p dbname < bakfile           

注意，将备份恢复后数据并不完整，还需要将备份后执行的日志（binlog）进行重做，语法如下：

mysqlbinlog binlog-file | mysql -u root -p -l -F test > test.dmp           

其中-l参数表示给所有表加读锁，-F 表示生成一个新的日志文件。

基于时间点恢复

由于误操作，比如误删除了一张表，这时使用完全恢复是没有用的，因为日志里面还存在误操作的语句，我们需要的是恢复到误操作之前的状态，然后跳过误操作语句，再恢复后面执行的语句，完成我们的恢复。这种恢复叫不完全恢复，在 MySQL 中，不完全恢复分为基于时间点的恢复和基于位置的恢复。

（1）如果上午10点发生了误操作，可以用以下语句用备份和 binlog 将数据复到故障前：

shell> mysqlbinlog --stop-date='2023-04-20 9:59:59' /var/log/mysql/bin.123456 | mysql -u root -pmypwd             

(2)跳过故障时的时间点，继续执行后面的binlog，完成恢复。

shell> mysqlbinlog --stop-date='2023-04-20 10:01:00' /var/log/mysql/bin.123456 | mysql -u root -pmypwd             

基于位置恢复

和基于时间点的恢复类似，但是更精确，因为同一个时间点可能有很多条 SQL 语句同时执行。恢复的操作步骤如下。

(1)在 shell 下执行如下命令:

she11>mysqlbinlog --start-date="2023-04-20 9:55:00" \
--stop-date="2023-04-20 10:05:00 /var/1og/mysq1/bin.123456 > /tmp/mysql_restore.sg1           

该命令将在/tmp 目录创建小的文本文件，编辑此文件，找到出错语句前后的位置号，例如前后位置号分别是368312和368315。

(2)恢复了以前的备份文件后，应从命令行输入下面的内容：

she11s> mysq1binlog --stop-position="368312" /var/log/mysql/bin.123456 \ 
| mysql-u root -pmypwd

she1l>mysqlbinlog --start-position-"368315" /var/1og/mysql/bin.123456 \
| mysql -u root -pmypwd           

上面的第一行将恢复到停止位置为止的所有事务。下一行将恢复从给定的起始位置直到binlog日志结束的所有事务。