[root@db01 b]#mysqldump -uroot -poldboy123 -A -R --triggers --master-data=2 --single-transaction | gzip > /b/all_date +%F-%H-%M-%S.sql.gz
mysql> create table ttt.nnn(id int primary key auto_increment not null, name char(20) not null); mysql> insert into nnn values(1,'aa'); Query OK, 1 row affected (0.00 sec)
mysql> insert into nnn values(2,'bb'); Query OK, 1 row affected (0.00 sec)
mysql> insert into nnn values(3,'cc'); Query OK, 1 row affected (0.00 sec)
mysql> insert into nnn values(4,'dd'); Query OK, 1 row affected (0.00 sec)
mysql> select * from ttt.nnn; +----+------+ | id | name | +----+------+ | 1 | aa | | 2 | bb | | 3 | cc | | 4 | dd | +----+------+
mysql> show master status; +------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000028 | 850 | | | | +------------------+----------+--------------+------------------+-------------------+
mysql> drop databases ttt;
全备时候加了 master-data=2 ,生成的备份文件,22行有恢复数据的起点,120 [root@db01 ~]#sed -n '22p' /b/all_2019-03-11-19-03-55.sql -- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000028', MASTER_LOG_POS=120;
查看当前binlog是000028 mysql> show master status; +------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000028 | 939 | | | | +------------------+----------+--------------+------------------+-------------------+
结束点位置 ,就是drop pos的位置 mysql> show binlog events in "mysql-bin.000028"; +------------------+-----+-------------+-----------+-------------+-----------------------------------------------------------------------------------------------------+ | Log_name | Pos | Event_type | Server_id | End_log_pos | Info | +------------------+-----+-------------+-----------+-------------+-----------------------------------------------------------------------------------------------------+ | mysql-bin.000028 | 4 | Format_desc | 6 | 120 | Server ver: 5.6.40-log, Binlog ver: 4 | | mysql-bin.000028 | 120 | Query | 6 | 224 | create database ttt charset utf8 | | mysql-bin.000028 | 224 | Query | 6 | 384 | use ttt; create table ttt.nnn(id int primary key auto_increment not null, name char(20) not null) | | mysql-bin.000028 | 384 | Query | 6 | 455 | BEGIN | | mysql-bin.000028 | 455 | Table_map | 6 | 503 | table_id: 323 (ttt.nnn) | | mysql-bin.000028 | 503 | Write_rows | 6 | 546 | table_id: 323 flags: STMT_END_F | | mysql-bin.000028 | 546 | Table_map | 6 | 594 | table_id: 323 (ttt.nnn) | | mysql-bin.000028 | 594 | Write_rows | 6 | 637 | table_id: 323 flags: STMT_END_F | | mysql-bin.000028 | 637 | Table_map | 6 | 685 | table_id: 323 (ttt.nnn) | | mysql-bin.000028 | 685 | Write_rows | 6 | 728 | table_id: 323 flags: STMT_END_F | | mysql-bin.000028 | 728 | Table_map | 6 | 776 | table_id: 323 (ttt.nnn) | | mysql-bin.000028 | 776 | Write_rows | 6 | 819 | table_id: 323 flags: STMT_END_F | | mysql-bin.000028 | 819 | Xid | 6 | 850 | COMMIT /* xid=3690 */ | | mysql-bin.000028 | 850 | Query | 6 | 939 | drop database ttt | +------------------+-----+-------------+-----------+-------------+-----------------------------------------------------------------------------------------------------+
[root@db01 ~]#mysqlbinlog -uroot -poldboy123 --start-position=120 --stop-position=850 /data/mysql/mysql-bin.000028 > /b/delttt_binlog.sql
mysql> set sql_log_bin=1
恢复全备: mysql> source /b/all_2019-03-11-19-03-55.sql; mysql> source /b/delttt_binlog.sql;
数据恢复了 mysql> select * from ttt.nnn; +----+------+ | id | name | +----+------+ | 1 | aa | | 2 | bb | | 3 | cc | | 4 | dd | +----+------+