mysqldump+binlog的数据库备份和恢复（实战）

1、测试环境准备

        准备一台能正常使用MySQL的机器

2、mysqldump语法介绍

 mysqldump  -h 服务器  -u用户名  -p密码   数据库名  > 备份文件.sql

参数说明：

库的范围	-A, --all-databases 所有库
	school 数据库名
	school stu_info t1 是指school数据库的表stu_info、t1

--single-transaction	InnoDB 一致性 服务可用性
--master-data=1|2	该选项将会记录binlog的日志位置与文件名并追加到文件中，或添加注释
-F, --flush-logs	备份之前刷新日志，截断日志。备份之后新binlog。
--opt	同时启动各种高级选项

3、备份实战

（1）准备好库

3.1 在mysql中创建testdb.t1,并插入数据用于备份

 （2）执行备份

3.2 在Linux中创建目录，存放备份文件

mkdir /backup/

3.3  使用mysqldump对mysql进行完备

mysqldump -uroot -p'123456' \
--all-databases --single-transaction \
 --master-data=2 \
--flush-logs \
>  /backup/`date +%F-%H`-mysql-all.sql

  

  3.4 观察备份细节

vim /backup/2024-07-17-11-mysql-all.sql

   * 找到记录二进制日志截断位置，在二十多行的位置

-- CHANGE REPLICATION SOURCE TO SOURCE_LOG_FILE='binlog.000004', SOURCE_LOG_POS=158;

3.5 对备份后的数据进行修改

进入mysql中，在testdb1插入数据或者进行其他数据的更改

3.6 对修改后的数据进行二进制日志备份

cp /var/lib/mysql/*bin* ~

4、 mysql数据模拟丢失

（1）停止数据库

systemctl stop mysqld

（2）清理数据

rm -rf /var/lib/mysql/*

（3）启动数据库

systemctl start mysqld

（4）获取临时密码并修改密码

grep 'password' /var/log/mysqld.log

mysqladmin -uroot -p'临时密码' password '新密码'

（5）mysql恢复数据

 mysql -p'新密码' < /backup/2024-07-17-11-mysql-all.sql

观察细节：可以看到mysql中testdb库重点t1表只有三条数据（1,2,3），而在mysqldump对数据库备份之后的数据并没有恢复，这是因为mysqldump只会备份之前的数据，之后的数据会被丢失，这时就需要使用binlog对数据进行恢复。

（6）binlog恢复

在备份时观察到二进制截止记录，SOURCE_LOG_FILE表示要从中开始复制的二进制日志文件的名称。SOURCE_LOG_POS设置为从该日志文件的哪个位置开始复制。

-- CHANGE REPLICATION SOURCE TO SOURCE_LOG_FILE='binlog.000004', SOURCE_LOG_POS=158;

注意：后续有多少日志，要跟多少日志名字。如果SOURCE_LOG_FILE后面还有文件继续添加

mysqlbinlog localhost-bin.000004 localhost-bin.000005
 --start-position=158  | mysql -uroot -p'密码'

（7）观察数据

进入mysql可以观察数据全部恢复。