mysql二進制日志

1.binlog 日志組成分析

2.binlog日志複制的幾種複制模式

3.now()和sysdate()在語義化複制要注意的點

知識點

binary log日志簡介

檢視日志格式

show variables like 'binlog_format';

+---------------+-----------+
| Variable_name | Value     |
+---------------+-----------+
| binlog_format | STATEMENT |
+---------------+-----------+

二進制日志的内容結構

二進制日志的内容由以下三部分組成

(1) 檔案頭：格式描述事件

(2)檔案主體:二進制日志事件

(3)檔案尾:日志輪換事件

格式描述事件包含的内容:伺服器版本,通用頭長度,檔案狀态資訊

binlog事件體：日志檔案,位置,事件類型(query,xid,user_var),伺服器id,結束位置,詳細語句資訊

檔案尾:事件輪換事件

什麼時候輪換?

重新開機資料庫

binlog檔案達到最大尺寸

flush logs 操作

異常輪換

奔潰或者非正常關閉(kill殺程序)資料庫時,輪換日志表示未正常的關閉(沒有輪換記錄)

使用者資料庫奔潰的系統自動修複,或者dba幹擾重放

mysql> show binary logs;

+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |     19730 |
| mysql-bin.000002 |    765307 |
| mysql-bin.000003 |       624 |
| mysql-bin.000004 |       327 |
| mysql-bin.000005 |       409 |
+------------------+-----------+
5 rows in set (0.00 sec)

mysql> show binlog events in 'mysql-bin.000005';
+------------------+-----+-------------+-----------+-------------+--------------------------------------------+
| Log_name         | Pos | Event_type  | Server_id | End_log_pos | Info                                       |
+------------------+-----+-------------+-----------+-------------+--------------------------------------------+
| mysql-bin.000005 |   4 | Format_desc |       250 |         106 | Server ver: 5.1.73-log, Binlog ver: 4      |
| mysql-bin.000005 | 106 | Query       |       250 |         207 | use `hk`; insert into test values(4,now()) |
| mysql-bin.000005 | 207 | Query       |       250 |         308 | use `hk`; insert into test values(5,now()) |
| mysql-bin.000005 | 308 | Query       |       250 |         409 | use `hk`; insert into test values(6,now()) |
+------------------+-----+-------------+-----------+-------------+--------------------------------------------+
mysql> flush logs;
Query OK, 0 rows affected (0.02 sec)

mysql> show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |     19730 |
| mysql-bin.000002 |    765307 |
| mysql-bin.000003 |       624 |
| mysql-bin.000004 |       327 |
| mysql-bin.000005 |       452 |
| mysql-bin.000006 |       106 |
+------------------+-----------+

mysql> show binlog events in 'mysql-bin.000005';
+------------------+-----+-------------+-----------+-------------+--------------------------------------------+
| Log_name         | Pos | Event_type  | Server_id | End_log_pos | Info                                       |
+------------------+-----+-------------+-----------+-------------+--------------------------------------------+
| mysql-bin.000005 |   4 | Format_desc |       250 |         106 | Server ver: 5.1.73-log, Binlog ver: 4      |
| mysql-bin.000005 | 106 | Query       |       250 |         207 | use `hk`; insert into test values(4,now()) |
| mysql-bin.000005 | 207 | Query       |       250 |         308 | use `hk`; insert into test values(5,now()) |
| mysql-bin.000005 | 308 | Query       |       250 |         409 | use `hk`; insert into test values(6,now()) |
| mysql-bin.000005 | 409 | Rotate      |       250 |         452 | mysql-bin.000006;pos=4                     |
+------------------+-----+-------------+-----------+-------------+--------------------------------------------+
5 rows in set (0.00 sec)

mysql> show binlog events in 'mysql-bin.000006';
+------------------+-----+-------------+-----------+-------------+---------------------------------------+
| Log_name         | Pos | Event_type  | Server_id | End_log_pos | Info                                  |
+------------------+-----+-------------+-----------+-------------+---------------------------------------+
| mysql-bin.000006 |   4 | Format_desc |       250 |         106 | Server ver: 5.1.73-log, Binlog ver: 4 |
+------------------+-----+-------------+-----------+-------------+---------------------------------------+
1 row in set (0.00 sec)
ps -ef|grep mysql
mysql      2759      1  0 Feb07 ?        00:00:16 /usr/libexec/mysqld --basedir=/usr --datadir=/var/lib/mysql --user=mysql --log-error=/var/log/mysqld.log --pid-file=/var/run/mysqld/mysqld.pid --socket=/var/lib/mysql/mysql.sock
root       5161   4530  0 08:06 pts/0    00:00:00 grep mysql
[root@centos1 ~]# kill -9 2759
[root@centos1 ~]# service mysqld status;
mysqld 已死，但 pid 檔案仍存
[root@centos1 log]# service mysqld start
正在啟動 mysqld：

mysql> show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |     19730 |
| mysql-bin.000002 |    765307 |
| mysql-bin.000003 |       624 |
| mysql-bin.000004 |       327 |
| mysql-bin.000005 |       452 |
| mysql-bin.000006 |       106 |
| mysql-bin.000007 |       106 |
+------------------+-----------+
7 rows in set (0.00 sec)

mysql> show binlog events in 'mysql-bin.000006';
+------------------+-----+-------------+-----------+-------------+---------------------------------------+
| Log_name         | Pos | Event_type  | Server_id | End_log_pos | Info                                  |
+------------------+-----+-------------+-----------+-------------+---------------------------------------+
| mysql-bin.000006 |   4 | Format_desc |       250 |         106 | Server ver: 5.1.73-log, Binlog ver: 4 |
+------------------+-----+-------------+-----------+-------------+---------------------------------------+
1 row in set (0.00 sec)

mysql> show binlog events in 'mysql-bin.000007';
+------------------+-----+-------------+-----------+-------------+---------------------------------------+
| Log_name         | Pos | Event_type  | Server_id | End_log_pos | Info                                  |
+------------------+-----+-------------+-----------+-------------+---------------------------------------+
| mysql-bin.000007 |   4 | Format_desc |       250 |         106 | Server ver: 5.1.73-log, Binlog ver: 4 |
+------------------+-----+-------------+-----------+-------------+---------------------------------------+
mysql-bin.000006 沒有輪換事件

mysql二進制的複制有3中格式

1.基于語句的複制

配置選項 :binlog_format=STAEMENT

特點:相對落後,把主庫的語句放到從庫再執行一次,速度較慢,無法保證所有語句都正确複制,但是二進制日志相對較小

2.基于行的複制

配置選項 : binlog_format=row

特點:将主庫的改動記為二進制日志的一行,儲存的是實體的更新資料,更加友善,速度相對較快,保證所有語句都能正确複制

但二進制日志檔案相對較大

3.混合類型

binlog_format=mixed

大多情況下是statement 格式,隻有特定情況下是row 格式

在statement下

在slave庫

stop slave io_thread;
show slave status\G
  Connect_Retry: 60
              Master_Log_File: mysql-bin.000008
          Read_Master_Log_Pos: 120
               Relay_Log_File: mysqld-relay-bin.000019
                Relay_Log_Pos: 283
        Relay_Master_Log_File: mysql-bin.000008
             Slave_IO_Running: No
            Slave_SQL_Running: Yes
在主庫
mysql> select * from test;
+----+---------------------+
| id | time                |
+----+---------------------+
|  1 | 2017-02-04 10:04:12 |
|  2 | 2017-02-04 13:53:35 |
|  3 | 2017-02-08 19:46:49 |
|  4 | 2017-02-08 20:34:49 |
|  5 | 2017-02-09 09:34:42 |
|  6 | 2017-02-09 10:08:48 |
|  7 | 2017-02-09 10:18:06 |
|  8 | 2017-02-09 17:35:10 |
+----+---------------------+

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

mysql> show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       143 |
| mysql-bin.000002 |       367 |
| mysql-bin.000003 |       120 |
| mysql-bin.000004 |       677 |
| mysql-bin.000005 |       367 |
| mysql-bin.000006 |       619 |
| mysql-bin.000007 |       591 |
| mysql-bin.000008 |       167 |
| mysql-bin.000009 |       120 |
+------------------+-----------+
9 rows in set (0.00 sec)
mysql> insert into test values(9,now());
Query OK, 1 row affected (0.00 sec)

mysql> insert into test values(10,sysdate());
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> select * from test;
+----+---------------------+
| id | time                |
+----+---------------------+
|  1 | 2017-02-04 10:04:12 |
|  2 | 2017-02-04 13:53:35 |
|  3 | 2017-02-08 19:46:49 |
|  4 | 2017-02-08 20:34:49 |
|  5 | 2017-02-09 09:34:42 |
|  6 | 2017-02-09 10:08:48 |
|  7 | 2017-02-09 10:18:06 |
|  8 | 2017-02-09 17:35:10 |
|  9 | 2017-02-10 09:21:17 |
| 10 | 2017-02-10 09:21:33 |

因為從庫的io程序沒開

是以沒有把主庫的二進制日志取到relay log裡,slave裡自然沒有記錄

現在slave開啟io_thread ;

start slave io_thread;

在slave

mysql> select * from test;
+----+---------------------+
| id | time                |
+----+---------------------+
|  1 | 2017-02-04 10:04:12 |
|  2 | 2017-02-04 13:53:35 |
|  3 | 2017-02-08 19:46:49 |
|  4 | 2017-02-08 20:34:49 |
|  5 | 2017-02-09 09:34:42 |
|  6 | 2017-02-09 10:09:40 |
|  7 | 2017-02-09 10:18:06 |
|  8 | 2017-02-09 17:35:10 |
+----+---------------------+
8 rows in set (0.01 sec)

mysql> start slave io_thread;
Query OK, 0 rows affected (0.01 sec)

mysql> select * from test;
+----+---------------------+
| id | time                |
+----+---------------------+
|  1 | 2017-02-04 10:04:12 |
|  2 | 2017-02-04 13:53:35 |
|  3 | 2017-02-08 19:46:49 |
|  4 | 2017-02-08 20:34:49 |
|  5 | 2017-02-09 09:34:42 |
|  6 | 2017-02-09 10:09:40 |
|  7 | 2017-02-09 10:18:06 |
|  8 | 2017-02-09 17:35:10 |
|  9 | 2017-02-10 09:21:17 |
| 10 | 2017-02-10 09:24:45 |
+----+---------------------+
10 rows in set (0.00 sec)

發現隻有id為9的記錄和主庫一樣,id為10的記錄有延遲,和主庫不一緻

在master

mysql> show binlog events in 'mysql-bin.000009';
+------------------+-----+-------------+-----------+-------------+-------------------------------------------------+
| Log_name         | Pos | Event_type  | Server_id | End_log_pos | Info                                            |
+------------------+-----+-------------+-----------+-------------+-------------------------------------------------+
| mysql-bin.000009 |   4 | Format_desc |       108 |         120 | Server ver: 5.6.35-log, Binlog ver: 4           |
| mysql-bin.000009 | 120 | Query       |       108 |         203 | BEGIN                                           |
| mysql-bin.000009 | 203 | Query       |       108 |         313 | use `hk`; insert into test values(9,now())      |
| mysql-bin.000009 | 313 | Xid         |       108 |         344 | COMMIT /* xid=24 */                             |
| mysql-bin.000009 | 344 | Query       |       108 |         427 | BEGIN                                           |
| mysql-bin.000009 | 427 | Query       |       108 |         542 | use `hk`; insert into test values(10,sysdate()) |
| mysql-bin.000009 | 542 | Xid         |       108 |         573 | COMMIT /* xid=25 */                             |
+------------------+-----+-------------+-----------+-------------+-------------------------------------------------+

二進制日志裡記錄了這2次插入

到slave

show slave status\G
Master_Log_File: mysql-bin.000009
          Read_Master_Log_Pos: 573
               Relay_Log_File: mysqld-relay-bin.000022
                Relay_Log_Pos: 736
        Relay_Master_Log_File: mysql-bin.000009
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

現在的Relay_Log_File: mysqld-relay-bin.000022

到slave的mysql外面

mysql二進制日志

[root@bogon ~]# cd /var/lib/mysql
[root@bogon mysql]# ll
總用量 110624
-rw-rw----. 1 mysql mysql       56 2月   8 19:44 auto.cnf
drwx------. 2 mysql mysql       49 2月   4 10:03 hk
-rw-rw----. 1 mysql mysql 12582912 2月  10 09:24 ibdata1
-rw-rw----. 1 mysql mysql 50331648 2月  10 09:24 ib_logfile0
-rw-rw----. 1 mysql mysql 50331648 2月   3 16:53 ib_logfile1
-rw-rw----. 1 mysql mysql      127 2月  10 09:24 master.info
drwx------. 2 mysql mysql     4096 2月   3 16:53 mysql
-rw-rw----. 1 mysql mysql      221 2月  10 09:24 mysqld-relay-bin.000021
-rw-rw----. 1 mysql mysql      736 2月  10 09:24 mysqld-relay-bin.000022
-rw-rw----. 1 mysql mysql       52 2月  10 09:24 mysqld-relay-bin.index
srwxrwxrwx. 1 mysql mysql        0 2月  10 09:07 mysql.sock
drwx------. 2 mysql mysql     4096 2月   3 16:53 performance_schema
-rw-rw----. 1 mysql mysql       59 2月  10 09:24 relay-log.info

View Code

mysqlbinlog mysqld-relay-bin.000022

/*!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
#170210  9:24:45 server id 163  end_log_pos 120 CRC32 0xad8f9f6a        Start: binlog v 4, server v 5.6.35 created 170210  9:24:45
BINLOG '
XRadWA+jAAAAdAAAAHgAAABAAAQANS42LjM1AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAXAAEGggAAAAICAgCAAAACgoKGRkAAWqf
j60=
'/*!*/;
# at 120
#170210  9:24:45 server id 108  end_log_pos 0 CRC32 0x0d3f1bdd  Rotate to mysql-bin.000009  pos: 4
# at 167
#170210  9:20:26 server id 108  end_log_pos 120 CRC32 0x79450af9        Start: binlog v 4, server v 5.6.35-log created 170210  9:20:26
BINLOG '
WhWdWA9sAAAAdAAAAHgAAAAAAAQANS42LjM1LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAXAAEGggAAAAICAgCAAAACgoKGRkAAfkK
RXk=
'/*!*/;
# at 283
#170210  9:21:17 server id 108  end_log_pos 203 CRC32 0xaceb5c00        Query   thread_id=3     exec_time=0     error_code=0
SET TIMESTAMP=1486689677/*!*/;
SET @@session.pseudo_thread_id=3/*!*/;
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=8/*!*/;
SET @@session.time_zone='SYSTEM'/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
BEGIN
/*!*/;
# at 366
#170210  9:21:17 server id 108  end_log_pos 313 CRC32 0x03e32c0a        Query   thread_id=3     exec_time=0     error_code=0
use `hk`/*!*/;
SET TIMESTAMP=1486689677/*!*/;
insert into test values(9,now())
/*!*/;
# at 476
#170210  9:21:17 server id 108  end_log_pos 344 CRC32 0x3a657546        Xid = 24
COMMIT/*!*/;
# at 507
#170210  9:21:33 server id 108  end_log_pos 427 CRC32 0x0e4bf105        Query   thread_id=3     exec_time=0     error_code=0
SET TIMESTAMP=1486689693/*!*/;
BEGIN
/*!*/;
# at 590
#170210  9:21:33 server id 108  end_log_pos 542 CRC32 0x8b162e58        Query   thread_id=3     exec_time=0     error_code=0
SET TIMESTAMP=1486689693/*!*/;
insert into test values(10,sysdate())
/*!*/;
# at 705
#170210  9:21:33 server id 108  end_log_pos 573 CRC32 0x1d0f5307        Xid = 25
COMMIT/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

mysql二進制日志

繼續閱讀

建立表和增删改(DML)

MySQL 常用函數字元串函數

MYSQL學習筆記——多表查詢

MYSQL時間差函數timestampdiff/datediff/timediff/date_sub/date_add

MySQL基礎文法序言1.查詢文法2.篩選條件分類3.常見函數4.線上練習

SQL語句inner join 與left join的差別

學習筆記(02):MySQL資料庫之資料庫操作第2節資料庫與表的基本操作-DDL之資料庫

MySQL必知必會4使用視圖使用存儲過程觸發器管理事務處理全球化和本地化安全管理

MYSQL必知必會2使用資料處理函數彙總資料分組資料使用子查詢聯結表進階聯結

MySQL學習筆記_删除表的三種方法

MyISAM存儲引擎MySQL引擎之MyISAM

mysql 學習記錄（六）-- 檢視引擎、myisam引擎、自增長、主外鍵關聯、memory引擎、merge引擎

MySQL江湖路 | 專欄目錄五分鐘搞定MySQL知識點系列

純文科生學MySQL（1）安裝MySQL和了解基礎知識

sql常用的基本文法（修改表名，修改表字段，修改字段類型，增加表字段,查找,修改，删除）

QT學習——QSqlQuery基本操作