mysqldump 备份恢复mysql

1，先完全备份
mysqldump -uroot -p123456 --single-transaction --flush-logs --master-data=2 -B db_name> /home/backup/back-$(date+mysqldump-uroot-p123456--single-transaction--flush-logs--master-data=2-Bd{b}_{n}ame|gzip>/home/backup/back-$(date +%F).sql.gz 备份成压缩格式
操作删除添加之类
恢复完全备份
mysql -uroot -p123456 < back.sql (指定路径)
gunzip < back-2019-07-29.sql.gz | mysql -uroot -p123456 恢复压缩格式
cp /var/lib/mysql/mysql-bin.000026 /home/ 复制二进制日志
mysqlbinlog --no-defaults /home/mysql-bin.000026 > /home/000026.sql
vim 000026.sql
删除误操作的 保存退出
mysql -uroot -p123456 < 000026.sql
mysqlbinlog --no-defaults /home/mysql-bin.000017 | mysql -u root -p123456; 恢复二进制日志
恢复前 flush logs;
2，查看日志误操作的pos点
show binlog events in ‘mysql-bin.000026’;
| mysql-bin.000026 | 248 | Query | 1 | 341 | use leyuan; delet e from home where id=25 |
或者这个
show binlog events in ‘mysql-bin.000026’\G;
*************************** 4. row ***************************
Log_name: mysql-bin.000026
Pos: 248
Event_type: Query
Server_id: 1
End_log_pos: 341
Info: use leyuan; delete from home where id=25
恢复日志
mysqlbinlog --no-defaults --start-position=106 --stop-position=248 /home/mysql-bin.000026 | mysql -uroot -p123456