mysql数据备份与恢复实践（逻辑备份mysqldump）

备份

备份有两种类型，全量备份、增量备份

全量备份

mysqldump -uxxx -pxxx --single-transaction --flush-logs --master-data=2 --all-databases > /var/lib/mysql/backup_20220407_1002.sql

参数解释：
--single-transaction： 使用一致读取并保证 mysqldump 看到的数据不会改变（其它客户端可能在mysqldump执行期间执行变更）
--flush-logs：刷新binlog日志，重新生成新的binlog文件
--master-data=2：记录binlog日志到dump的sql文件中，以注释的形式存在
--all-databases：备份所有数据库

增量备份

开启binlog，mysql.cnf配置：

[mysqld]
...
log-bin=filename
server-id=1

会在/var/lib/mysql目录下生成类似filename-bin.00001的二进制文件

恢复

全量恢复

mysql -uxxx -pxxx < /var/lib/mysql/backup_20220407_1002.sql

增量恢复

mysqlbinlog -uxxx -pxxx xxx-bin.xxx xxx-bin.xxx | mysql -uxxx -pxxx

可以指定多个binlog文件

时间点恢复

binlog日志事件格式:

// at后面的数字表示事件在二进制日志文件中的文件偏移量或起始位置
# at 141

//日期时间开头
// server id 是事件发起的源服务器的值
// end_log_pos 表示下一个事件的开始位置（当前事件结束位置+1）
// thread_id 表示哪个线程执行了事件
// exec_time 在复制源服务器上执行事件所花费的时间，在副本上，它是副本上的结束执行时间减去源上的开始执行时间的差
// error_code 表示执行事件的结果，0表示没有发生错误
#220407 10:08:08 server id end_log_pos 328 CRC32 0x765b4e59  Query   thread_id=3     exec_time=0     error_code=0

通过mysqlbinlog查找日志的关键位置，可以找出事件发生的时间，根据具体时间去恢复即可。
时间点恢复命令：

mysqlbinlog -uxxx -pxxx --start-datetime="2022-04-07 00:00:00" --stop-datetime="2022-04-07 12:00:00" /var/lib/mysql/xxx-bin.xxx --verbose | mysql -uxxx -pxxx

事件偏移位置恢复命令：

mysqlbinlog -uxxx -pxxx --start-position=155 --stop-position=233 /var/lib/mysq/xxx-bin.xxx | mysql -uxxx -pxxx

这里的position就是日志文件中的# at后的数值

扩展

docker执行容器内命令
docker exec -it 容器ID /bin/bash -c “容器内命令”