- 数据库需要开启binlog日志; show variables like "log_bin";
-
全数据库备份 mysqldump -uroot -p --single-transaction --master-data=1 --flush-logs --all-databases >/backup/mysql/$(date "+%Y%m%d%H%M%S")-mysql-all.sql 备份某个库 mysqldump -uroot -p --single-transaction --master-data=1 --flush-logs businessdb >/backup/mysql/$(date "+%Y%m%d%H%M%S")-mysql-businessdb.sql
-
查询binlog日志记录点,binlog记录的信息在sql前面几十行内 # head -30 mysql-business.sql |grep -i "binlog" CHANGE MASTER TO MASTER_LOG_FILE='binlog.000005',MASTER_LOG_POS=156;
-
先全量恢复全备数据 mysql -uroot -p businessdb < mysql-business.sql
-
增量恢复全备以后数据库的新增事务binlog记录的内容 mysqlbinlog --no-defaults --start-position=156 binlog.000005 |mysql -uroot -p