MySQL backup database and restore method through binlog timed database

vim /etc/
server_id=1
log-bin=mysql-bin

-- View the specifiedbinlogAll events in the file
show binlog events in 'binlog.000001'
-- View the specifiedbinlogFrom the specified location in the file（position）All events that started
show binlog events in 'binlog.000001' from 32556;
-- Pagination query
show binlog events in 'binlog.000001' from 32556 limit 10;

1.Normal installation
mysqldump -uroot -proot --databases test --master-data=2 --flush-logs > /backup/`date +%F-%H`-
2.passdockerdeploy
docker exec e2c326627ed5 sh -c 'exec mysqldump --databases test -u root -p"root" --master-data=2 --flush-logs' > /opt/backup/`date +%F-%H`-
Note: --databasesThe database name is followed，master-data=2 Note释掉日志记录

 /opt/backup
 
3.#!/bin/bash
#mysqldump -uroot -proot --databases test --master-data=2 --flush-logs > /backup/`date +%F-%H`-

CHANGE MASTER TO
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=154;

crontab -e   // Create a timed task and execute it from 2 a.m.0 2 * * * /opt/backup/
crontab -l   // View scheduled tasks

1.Delete the data and perform the backupsqlscript: /backup/
2.View theCHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=154;Recordmysql-bin.000001and154，implementbinlogfrommysql-bin.000001Filed154Start recovery
3.Start passingbinlogRecover data
mysqlbinlog mysql-bin.000001 --start-position=154 --stop-position=71012  | mysql -uroot -p'root'
Note: --start-position:Start recovery的位置
--stop-position：End location，如果需要implement到最后这个参数可以不写