mysqlbinlog 不能还原

xjpcj 2015-06-20 09:53:01

mysql> show binlog events in 'master-bin.000047';
+-------------------+------+----------------+-----------+-------------+--------------------------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+-------------------+------+----------------+-----------+-------------+--------------------------------------------------------------------+
| master-bin.000047 | 4 | Format_desc | 1 | 120 | Server ver: 5.6.23-log, Binlog ver: 4 |
| master-bin.000047 | 120 | Previous_gtids | 1 | 191 | 50e1e87e-d239-11e4-a306-03b2732872c0:1-18 |
| master-bin.000047 | 191 | Gtid | 1 | 239 | SET @@SESSION.GTID_NEXT= '50e1e87e-d239-11e4-a306-03b2732872c0:19' |
| master-bin.000047 | 239 | Query | 1 | 357 | use `test`; create table upl (id int , user varchar(16)) |
| master-bin.000047 | 357 | Gtid | 1 | 405 | SET @@SESSION.GTID_NEXT= '50e1e87e-d239-11e4-a306-03b2732872c0:20' |
| master-bin.000047 | 405 | Query | 1 | 477 | BEGIN |
| master-bin.000047 | 477 | Rows_query | 1 | 555 | # insert into upl values (1,'tom'),(2,'mary'),(3,'bean') |
| master-bin.000047 | 555 | Table_map | 1 | 604 | table_id: 72 (test.upl) |
| master-bin.000047 | 604 | Write_rows | 1 | 668 | table_id: 72 flags: STMT_END_F |
| master-bin.000047 | 668 | Xid | 1 | 699 | COMMIT /* xid=29 */ |
| master-bin.000047 | 699 | Gtid | 1 | 747 | SET @@SESSION.GTID_NEXT= '50e1e87e-d239-11e4-a306-03b2732872c0:21' |
| master-bin.000047 | 747 | Query | 1 | 819 | BEGIN |
| master-bin.000047 | 819 | Rows_query | 1 | 869 | # delete from upl where id=2 |
| master-bin.000047 | 869 | Table_map | 1 | 918 | table_id: 72 (test.upl) |
| master-bin.000047 | 918 | Delete_rows | 1 | 963 | table_id: 72 flags: STMT_END_F |
| master-bin.000047 | 963 | Xid | 1 | 994 | COMMIT /* xid=30 */ |
| master-bin.000047 | 994 | Gtid | 1 | 1042 | SET @@SESSION.GTID_NEXT= '50e1e87e-d239-11e4-a306-03b2732872c0:22' |
| master-bin.000047 | 1042 | Query | 1 | 1114 | BEGIN |
| master-bin.000047 | 1114 | Rows_query | 1 | 1183 | # insert into upl values (4,'moto'),(5,'nokia') |
| master-bin.000047 | 1183 | Table_map | 1 | 1232 | table_id: 72 (test.upl) |
| master-bin.000047 | 1232 | Write_rows | 1 | 1288 | table_id: 72 flags: STMT_END_F |
| master-bin.000047 | 1288 | Xid | 1 | 1319 | COMMIT /* xid=32 */ |
| master-bin.000047 | 1319 | Rotate | 1 | 1367 | master-bin.000048;pos=4 |
+-------------------+------+----------------+-----------+-------------+--------------------------------------------------------------------+

./mysqlbinlog --start-position=120 --stop-position=357 /xmmy/mysql/data/master-bin.000047 > /tmp/1.sql
1.sql :
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#150619 18:52:01 server id 1 end_log_pos 120 CRC32 0x4e0f5c0a Start: binlog v 4, server v 5.6.23-log created 150619 18:52:01
BINLOG '
UfSDVQ8BAAAAdAAAAHgAAAAAAAQANS42LjIzLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAXAAEGggAAAAICAgCAAAACgoKGRkAAQpc
D04=
'/*!*/;
# at 120
#150619 18:52:01 server id 1 end_log_pos 191 CRC32 0x21ce08d9 Previous-GTIDs
# 50e1e87e-d239-11e4-a306-03b2732872c0:1-18
# at 191
#150619 19:00:23 server id 1 end_log_pos 239 CRC32 0x35c542ad GTID [commit=yes]
SET @@SESSION.GTID_NEXT= '50e1e87e-d239-11e4-a306-03b2732872c0:19'/*!*/;
# at 239
#150619 19:00:23 server id 1 end_log_pos 357 CRC32 0x877a710d Query thread_id=2 exec_time=1 error_code=0
use `test`/*!*/;
SET TIMESTAMP=1434711623/*!*/;
SET @@session.pseudo_thread_id=2/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1075838976/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
create table upl (id int , user varchar(16))
/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog *//*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
导入sql后无效:数据表没恢复
mysql> source /tmp/1.sql;
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Database changed
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Charset changed
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

...全文