mysql binlog_format和臨時表

mysql binlog_format和臨時表

mysql 5.7.12

mysql>  show variables like '%binlog_f%';
+---------------+-----------+
| Variable_name | Value     |
+---------------+-----------+
| binlog_format | STATEMENT |
+---------------+-----------+
1 row in set (0.00 sec)

mysql> flush logs;
Query OK, 0 rows affected (0.01 sec)

mysql> use tt;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> CREATE TEMPORARY TABLE `p` (
    ->   `id` int(11) DEFAULT NULL
    -> ) ;
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> insert into p value (3);
Query OK, 1 row affected (0.00 sec)


[root@10-13-38-7 data]# mysqlbinlog  -vvv 1.000013
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#160604 23:10:35 server id 1  end_log_pos 123 CRC32 0x0148f7c9  Start: binlog v 4, server v 5.7.12-log created 160604 23:10:35
# Warning: this binlog is either in use or was not closed properly.
BINLOG '
a+9SVw8BAAAAdwAAAHsAAAABAAQANS43LjEyLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA
Acn3SAE=
'/*!*/;
# at 123
#160604 23:10:35 server id 1  end_log_pos 194 CRC32 0x8fa11a9f  Previous-GTIDs
# 14a1fb07-23e8-11e6-a7f0-52540055799f:1-31
# at 194
#160604 23:12:02 server id 1  end_log_pos 259 CRC32 0x768925a3  GTID    last_committed=0        sequence_number=1
SET @@SESSION.GTID_NEXT= '14a1fb07-23e8-11e6-a7f0-52540055799f:32'/*!*/;
# at 259
#160604 23:12:02 server id 1  end_log_pos 387 CRC32 0x8ecbb506  Query   thread_id=12    exec_time=0     error_code=0
use `tt`/*!*/;
SET TIMESTAMP=1465053122/*!*/;
SET @@session.pseudo_thread_id=12/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1436549152/*!*/;
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=8/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
CREATE TEMPORARY TABLE `p` (
  `id` int(11) DEFAULT NULL
)
/*!*/;
# at 387
#160604 23:12:11 server id 1  end_log_pos 452 CRC32 0xd3338855  GTID    last_committed=1        sequence_number=2
SET @@SESSION.GTID_NEXT= '14a1fb07-23e8-11e6-a7f0-52540055799f:33'/*!*/;
# at 452
#160604 23:12:11 server id 1  end_log_pos 527 CRC32 0x30c92812  Query   thread_id=12    exec_time=0     error_code=0
SET TIMESTAMP=1465053131/*!*/;
BEGIN
/*!*/;
# at 527
#160604 23:12:11 server id 1  end_log_pos 620 CRC32 0x8c0550c9  Query   thread_id=12    exec_time=0     error_code=0
SET TIMESTAMP=1465053131/*!*/;
insert into p value (3)
/*!*/;
# at 620
#160604 23:12:11 server id 1  end_log_pos 696 CRC32 0xe82c1eae  Query   thread_id=12    exec_time=0     error_code=0
SET TIMESTAMP=1465053131/*!*/;
COMMIT
/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;








mysql>  show variables like '%binlog_f%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW   |
+---------------+-------+
1 row in set (0.00 sec)

mysql>  flush logs;
Query OK, 0 rows affected (0.00 sec)

mysql> use tt;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> CREATE TEMPORARY TABLE `p` (
    ->   `id` int(11) DEFAULT NULL
    -> );
Query OK, 0 rows affected (0.00 sec)

mysql> insert into p value (3);
Query OK, 1 row affected (0.00 sec)

 mysqlbinlog  -vvv 1.000014
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#160604 23:14:16 server id 1  end_log_pos 123 CRC32 0xa1b90e78  Start: binlog v 4, server v 5.7.12-log created 160604 23:14:16
# Warning: this binlog is either in use or was not closed properly.
BINLOG '
SPBSVw8BAAAAdwAAAHsAAAABAAQANS43LjEyLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA
AXgOuaE=
'/*!*/;
# at 123
#160604 23:14:16 server id 1  end_log_pos 194 CRC32 0xafe00a46  Previous-GTIDs
# 14a1fb07-23e8-11e6-a7f0-52540055799f:1-35
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;


當binlog_format=statement時 臨時表的所有操作都會被記錄下來
當binlog_format=row時 臨時表的所有操作都不會被記錄下來


轉載請註明源出處
QQ 273002188 歡迎一起學習
QQ 群 236941212
oracle,mysql,PG 相互交流

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/25099483/viewspace-2114165/，如需轉載，請註明出處，否則將追究法律責任。