停电,mysql8挂了,记录一下通过mysqlbinlog恢复数据库
- 备份源数据
先去原来的数据库下把binlog开头的,所有的文件都备份下来。
我的mysql8是nfs外挂的,位置是 ${nfs_path}/var/lib/mysql/下,如图:
- 恢复数据
第一次玩,找个新的数据库恢复,用下面的命令完成。
"C:\Program Files (x86)\mysql-8.0.23-winx64\bin\mysqlbinlog" --no-defaults --host=192.168.1.11 --port=30006 --user=root --password=kgb007dnmt008 "D:\mine\binlog.000001" "D:\mine\binlog.000002" "D:\mine\binlog.000003" "D:\mine\binlog.000004" "D:\mine\binlog.000005" "D:\mine\binlog.000006" | "C:\Program Files (x86)\mysql-8.0.23-winx64\bin\mysql" -u root -pkgb007dnmt008 --host=192.168.1.11 --port=30006
mysqlbinlog 命令
–no-defaults 防止utf8字符集报错
001-006 所有文件一起恢复
| 通过管道
mysqlbinlog命令不是每个版本都有的,我的目标数据库就没有,碰巧我本地windows的电脑上有,只能通过我的电脑执行命令远程恢复了。
缺点,超级慢,可能是数据有点多
报错时参考:
ERROR 1418 (HY000) at line 3438812: This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)
mysqlbinlog: Error writing file '<fd out of range>' (OS errno 0 - No error)
show variables like ‘log_bin_trust_function_creators’;
set global log_bin_trust_function_creators=1;
show variables like ‘log_bin_trust_function_creators’;