使用binlog对mysql进行数据恢复或迁移

目录

• 一，数据迁移
  • 1，使用binlog恢复
  • 2，直接拷贝数据文件恢复

一，数据迁移

A数据库的数据迁移到B数据库
以前的方式通常是导出数据的sql文件，再导入，或备份文件，恢复备份，这里使用binlog文件恢复数据库

1，使用binlog恢复

获取MySQL binlog的日志位置

查看/etc/my.cnf文件，dataDir指定的路径就是binlog的日志位置

进入mysql，执行命令查看binlog是否开启

mysql> show variables like '%log_bin%';
+---------------------------------+--------------------------+
| Variable_name                   | Value                    |
+---------------------------------+--------------------------+
| log_bin                         | ON                       |
| log_bin_basename                | /data/mysql/binlog       |
| log_bin_index                   | /data/mysql/binlog.index |
| log_bin_trust_function_creators | OFF                      |
| log_bin_use_v1_row_events       | OFF                      |
| sql_log_bin                     | ON                       |
+---------------------------------+--------------------------+

可以看到 log_bin 已经开启， 同时可以看到存储的位置在 /daba/mysql 目录 , 前缀是 binlog

ls /data/mysql/binlog.*
/data/mysql/binlog.000143  /data/mysql/binlog.000144  /data/mysql/binlog.000145  /data/mysql/binlog.000146  /data/mysql/binlog.index
# 查看所有binlog日志列表
show master logs;
# 查看master状态，即最后(最新)一个binlog日志的编号名称，及其最后一个操作事件pos结束点(Position)值
show master status;
# 为了防止干扰，我们flush刷新log日志，自此刻开始产生一个新编号的binlog日志文件
flush logs;
# 找到mysqlbinlog的位置
which mysqlbinlog
# 切换到binlog存放位置 通过mysqlbinlog -v 恢复文件
# 文件的全部范围（文件过大不宜执行，可将结果转成文件，或者用下面的两种办法）
/usr/bin/mysqlbinlog -v mysql-bin.000013
# 指定位置范围
/usr/bin/mysqlbinlog -v mysql-bin.000013 --start-position=0 --stop-position=986
# 指定时间范围
/usr/bin/mysqlbinlog -v mysql-bin.000013 --start-datetime="2021-06-01 03:18:00" 

总结

• 执行建表语句时，要在前面加上DROP TABLE IF EXISTS这个，保证数据库中即使存在同名表也能正常执行
• 打算做恢复时，执行flush logs，以防止被干扰;
• binlog恢复前，确保数据库表结构一致
• 细心查找需要恢复的position位置

如果没有开启bin_log，只有直接拷贝数据文件的办法了。

2，直接拷贝数据文件恢复

A：源mysql
B：目标mysql

进入B数据库，查看mysql的安装路径

show global variables;
show variables like 'datadir';
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| datadir       | /var/lib/mysql/ |
+---------------+-----------------+
1 row in set (0.01 sec)

找到对应表数据文件路径

这里为了备份，把B数据库的所有数据文件打包存起来即可，然后删除所有文件

tar -cvf digitaltwins.tar digitaltwins/
rm -rf digitaltwins/

再把A数据库的所有数据文件拷贝过来（用scp直接分发）就可以了

scp -r digitaltwins.tar root@192.168.1.181:/var/lib/mysql