伺服器的二進位制日誌檔案由用來描述修改資料庫內容的事件組成。伺服器以二進位制方式來寫這些檔案。為了以文字格式來顯示這些內容，可以使用mysqlbinlog工具。也可以使用mysqlbinlog來顯示在複製環境中由從從slave伺服器所寫入中relay日誌檔案中的內容，因為其格式與二進位制日誌檔案格式一樣。
mysqlbinlog的使用語法如下:
Usage: mysqlbinlog [options] log-files

下面的命令用來顯示名為binlog.000001的二進位制日誌檔案的內容:

[mysql@localhost ~]$ mysqlbinlog  /mysqldata/mysql/binlog.000001
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#191115 15:39:01 server id 1  end_log_pos 123 CRC32 0x2d9d7b4f  Start: binlog v 4, server v 5.7.26-log created 191115 15:39:01 at startup
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
BINLOG '
FVbOXQ8BAAAAdwAAAHsAAAABAAQANS43LjI2LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAVVs5dEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA
AU97nS0=
'/*!*/;
# at 123
#191115 15:39:01 server id 1  end_log_pos 154 CRC32 0x42dcd61c  Previous-GTIDs
# [empty]
# at 154
#191115 15:51:15 server id 1  end_log_pos 219 CRC32 0x5bc0b021  Anonymous_GTID  last_committed=0        sequence_number=1       rbr_only=no
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 219
#191115 15:51:15 server id 1  end_log_pos 308 CRC32 0x7261eacb  Query   thread_id=2     exec_time=0     error_code=0
use `mysql`/*!*/;
SET TIMESTAMP=1573804275/*!*/;
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=1436549152/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C gb2312 *//*!*/;
SET @@session.character_set_client=24,@@session.collation_connection=24,@@session.collation_server=45/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
truncate table person
/*!*/;
# at 308
#191115 15:51:38 server id 1  end_log_pos 373 CRC32 0x6d2e39aa  Anonymous_GTID  last_committed=1        sequence_number=2       rbr_only=no
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 373
#191115 15:51:38 server id 1  end_log_pos 454 CRC32 0x7871c2ea  Query   thread_id=2     exec_time=0     error_code=0
SET TIMESTAMP=1573804298/*!*/;
BEGIN
/*!*/;
# at 454
# at 486
#191115 15:51:38 server id 1  end_log_pos 486 CRC32 0xb746cd30  Intvar
SET INSERT_ID=1/*!*/;
#191115 15:51:38 server id 1  end_log_pos 654 CRC32 0x0e926042  Query   thread_id=2     exec_time=0     error_code=0
SET TIMESTAMP=1573804298/*!*/;
insert into person(last_name,first_name,birth,death) values('yong','jing','1985-02-28',null)
/*!*/;
# at 654
#191115 15:51:38 server id 1  end_log_pos 736 CRC32 0xc5450308  Query   thread_id=2     exec_time=0     error_code=0
SET TIMESTAMP=1573804298/*!*/;
COMMIT
/*!*/;
# at 736
#191115 15:51:45 server id 1  end_log_pos 801 CRC32 0xc2c892b8  Anonymous_GTID  last_committed=2        sequence_number=3       rbr_only=no
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 801
#191115 15:51:45 server id 1  end_log_pos 882 CRC32 0x51a9cd5c  Query   thread_id=2     exec_time=0     error_code=0
SET TIMESTAMP=1573804305/*!*/;
BEGIN
/*!*/;
# at 882
# at 914
#191115 15:51:45 server id 1  end_log_pos 914 CRC32 0x40a98fae  Intvar
SET INSERT_ID=2/*!*/;
#191115 15:51:45 server id 1  end_log_pos 1082 CRC32 0x3396c40d         Query   thread_id=2     exec_time=0     error_code=0
SET TIMESTAMP=1573804305/*!*/;
insert into person(last_name,first_name,birth,death) values('yan','huang','1990-08-25',null)
/*!*/;
# at 1082
#191115 15:51:45 server id 1  end_log_pos 1164 CRC32 0xf6f6efad         Query   thread_id=2     exec_time=0     error_code=0
SET TIMESTAMP=1573804305/*!*/;
COMMIT
/*!*/;
# at 1164
#191115 15:51:53 server id 1  end_log_pos 1229 CRC32 0x55b50dbe         Anonymous_GTID  last_committed=3        sequence_number=4       rbr_only=no
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 1229
#191115 15:51:53 server id 1  end_log_pos 1310 CRC32 0xd0f6a335         Query   thread_id=2     exec_time=0     error_code=0
SET TIMESTAMP=1573804313/*!*/;
BEGIN
/*!*/;
# at 1310
# at 1342
#191115 15:51:53 server id 1  end_log_pos 1342 CRC32 0xfad94baf         Intvar
SET INSERT_ID=3/*!*/;
#191115 15:51:53 server id 1  end_log_pos 1508 CRC32 0x26c5b3bb         Query   thread_id=2     exec_time=0     error_code=0
SET TIMESTAMP=1573804313/*!*/;
insert into person(last_name,first_name,birth,death) values('yali','ye','1994-12-23',null)
/*!*/;
# at 1508
#191115 15:51:53 server id 1  end_log_pos 1590 CRC32 0xbb6a2b4c         Query   thread_id=2     exec_time=0     error_code=0
SET TIMESTAMP=1573804313/*!*/;
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*/;

上面輸出了binlog.000001二進位制日誌檔案中所包含的內容。對於基於語句的日誌，事件資訊包括SQL語句，執行語句的伺服器ID，語句被執行的時間戳，執行時間等。對於基於行記錄的日誌，事件資訊指示行的改變而不是SQL語句。

# at 486
#191115 15:51:38 server id 1  end_log_pos 486 CRC32 0xb746cd30  Intvar SET INSERT_ID=1/*!*/;
#191115 15:51:38 server id 1  end_log_pos 654 CRC32 0x0e926042  Query   thread_id=2
exec_time=0     error_code=0 SET TIMESTAMP=1573804298/*!*/;

第一行，at後面的數字指示事件在二進位制日誌檔案中的偏移量或開始位置。

第二行是以日期和時間開始指示語句開始執行的時間。對於複製來說，這個時間戳將傳播到從屬伺服器。server id是事件起源伺服器的server_id值。end_log_pos指示下一個事件開始的位置(它是當前事件的終止位置+1)。thread_id那個執行緒來執行這個事件。exec_time是在主伺服器上執行事件所花費的時間。在從屬伺服器上，它是從屬伺服器上執行結束時間減去主伺服器上的執行開始時間的差值。這種差值可以作為一種指示來表示複製程式落後於主伺服器多長時間。error_code指示執行事件的結果。零意味著沒有出現錯誤。

mysqlbinglog的輸出可以用來重新執行日誌檔案中的語句(例如，透過使用mysql工具)。這在伺服器崩潰時用來恢復是很有用的。

正常來說，使用mysqlbinlog直接讀取二進位制日誌檔案並應用它們到本地MySQL伺服器。它也可以透過使用--read-from-remote-server選項來從遠端伺服器上讀取二進位制日誌檔案。為了讀取遠端二進位制日誌檔案，連線引數選項可以被指定用來指示如何連線伺服器。這些選項有--host,--password,--port,--protocol,--socket和--user，除非使用了--read-from-remote-server選項否則它們會被忽略。

當對一個大的二進位制日誌檔案執行mysqlbinlog時，要小心注意檔案系統是否有足夠的空間來儲存結果檔案。為了配置目錄給mysqlbinlog臨時使用儲存檔案，使用TMPDIR環境變數。

使用mysqlbinlog備份二進位制日誌檔案
預設情況下，mysqlbinlog讀取二進位制日誌檔案並以文字格式來顯示它的內容。這能讓你更容易使用檔案來檢查發生的事件和重新執行它們(例如，透過使用輸出作為mysql的輸入)。mysqlbinlog可以直接從本地檔案系統中讀取日誌檔案或者使用--read-from-remote-server選項來連線遠端伺服器並從遠端伺服器上讀取二進位制日誌檔案。mysqlbinlog以文字格式將內容輸出到標準輸出，或者如果指定了--result-file=file_name選項會將內容寫入檔案。

mysqlbinlog可以讀了二進位制日誌檔案並將其包含的內容以二進位制格式而不是文字格式寫入新檔案。這種能力可以讓你以原來的格式來備份二進位制日誌檔案。mysqlbinlog可以生成靜態備份，在備份一組日誌檔案時當備份完最後的檔案時而停止。它也可以生成一種連續(live)備份，當備份到最後的日誌檔案時仍然保持對伺服器的連線並當生成新的事件時繼續複製新的事件。在連續備份操作時，mysqlbinlog會執行到連線中斷為止(比如，伺服器退出)或mysqlbinlog被強制中斷為止。當連線中斷，mysqlbinlog不會進行等待並重新進行連線，不像從屬複製伺服器那樣。為了在伺服器重啟之後繼續一個live備份，必須重新啟動mysqlbinlog。

二進位制日誌檔案備份要求在呼叫mysqlbinlog時最少要使用兩個選項:
.--read-from-remote-server(或-R)選項來告訴mysqlbinlog連線到一個伺服器並讀取它的二進位制日誌檔案(這類似於一個從屬複製伺服器連線到它的主伺服器).

.--raw選項告訴mysqlbinlog以原始(二進位制)格式輸出，而不是文字格式。

與--read-from-remote-server一起通常還指定其它選項:--host指示伺服器執行在哪裡，並且可能需要指定連線選項--user和password。

與--raw聯合使用的幾個其它選項:.--stop-never:在讀取到最後日誌檔案後保持對伺服器的連線並繼續讀取新的事件。

.--stop-never-slave-server-id=id:當--stop-never被使用時mysqlbinlog報告的伺服器ID，預設值65535。這可以避免與從屬伺服器或其它的mysqlbinlog程式的ID衝突。

.--result-file:輸出檔名的字首

為了使用mysqlbinlog來備份伺服器的二進位制日誌檔案，你必須指定在伺服器上真實存在的檔名。如果你不知道檔名，連線到伺服器並使用show binary logs語句來檢視當前的日誌檔名。

mysql> show binary logs;
+---------------+-----------+
| Log_name      | File_size |
+---------------+-----------+
| binlog.000001 |      2530 |
+---------------+-----------+
1 row in set (0.00 sec)

使用這些資訊可以使用mysqlbinlog來備份二進位制日誌檔案到當前目錄:
為了對binlog.000130到binlog.000132的日誌檔案進行靜態備份，使用以下命令:
mysqlbinlog --read-from-remote-server --host=host_name --raw
binlog.000130 binlog.000131 binlog.000132

mysqlbinlog --read-from-remote-server --host=host_name --raw
--to-last-log binlog.000130

第一個命令顯式指定每個檔名。第二個只指定了第一個日誌檔案並使用了--to-last-log來讀取到最後一個日誌檔案。在這些命令之間的差異是在mysqlbinlog到達binlog.000132的末尾之前如果伺服器開啟了binlog.000133檔案，第一個命令將不會讀取，但第二個命令會讀取。

為了進行live備份mysqlbinlog從binlog.000130開始備份現有的日誌檔案，然後保持對伺服器的連線來複制生成的新事件:
mysqlbinlog --read-from-remote-server --host=host_name --raw
--stop-never binlog.000130

使用--stop-never選項，不需要指定--to-last-log來讀取最後的日誌檔案因為這個選項是隱含的

輸出檔名
在沒有使用--raw選項時，mysqlbinlog會生成文字格式的輸出，如果指定--result-file選項，指定將所有輸出寫入一個檔案中。使用--raw選項時，mysqlbinlog會將伺服器的每個日誌檔案轉換成一個二進位制輸出檔案。預設情況下，mysqlbinlog會在當前目錄中生成與源日誌檔案同名的檔案。為了修改輸出檔名，使用--result-file選項。與--raw聯合使用，--result-file選項值將作為字首來命名輸出檔名。

現在對遠端伺服器中的binlog.000001日誌檔案進行備份

[mysql@localhost ~]$ mysqlbinlog --read-from-remote-server --host=192.168.1.250 --raw binlog.000001 --result-file=jy_
[mysql@localhost ~]$ ls -lrt
-rw-r-----. 1 mysql mysql    2530 11月 22 10:24 jy_binlog.000001

可以看到備份的日誌檔案為以jy_為字首，其檔名為jy_binlog.000001

使用mysqldump與mysqlbinlog執行備份與還原操作
下面將介紹一個簡單的例子顯示如何使用mysqldump與mysqlbinlog一起來備份MySQL伺服器的資料和二進位制日誌檔案以及在資料丟失時如何使用備份來還原資料。

現在主機上的MySQL伺服器的第一個二進位制日誌檔案為binlog.000001

mysql> show binary logs;
+---------------+-----------+
| Log_name      | File_size |
+---------------+-----------+
| binlog.000001 |      2530 |
+---------------+-----------+
1 row in set (0.01 sec)

使用mysqlbinlog來對二進位制日誌檔案執行連續備份:

[mysql@localhost ~]$ mysqlbinlog --read-from-remote-server --host=192.168.1.250 --raw  --stop-never binlog.000001
[mysql@localhost ~]$ ls -lrt
-rw-r-----. 1 mysql mysql    2530 11月 22 10:38 binlog.000001

建立了一個名為t的測試表並插入了三行記錄

mysql> select * from t;
+----+----------+------+
| id | name     | date |
+----+----------+------+
|  1 | jingyong | NULL |
|  2 | yeyali   | NULL |
|  3 | huangyan | NULL |
+----+----------+------+
3 rows in set (0.00 sec)

使用mysqldump來建立一個dump檔案作為對MySQL伺服器的資料快照。使用--all-databases,--events和--routines來備份所有的資料，--master-data=2用來指示在dump檔案中包括當前的二進位制日誌檔案。

[mysql@localhost ~]$ mysqldump --host=192.168.1.250 --port=3306 -uroot -pabcd --all-databases --events --routines --master-data=2> dump_mysql.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[mysql@localhost ~]$ ls -lrt
-rw-r--r--. 1 mysql mysql 3290497 11月 22 10:51 dump_mysql.sql

現在刪除mysql庫中的表t

mysql> drop table t;
Query OK, 0 rows affected (0.18 sec)
mysql> desc t;
ERROR 1146 (42S02): Table 'mysql.t' doesn't exist

現在假設mysql庫中的表t丟失了，使用最近的dump檔案來還原資料:

[mysql@localhost ~]$ mysql --host=192.168.1.250 --port=3306 -uroot -pabcd  mysql

還原資料後mysql.t表就恢復了

mysql> desc t;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   |     | NULL    |       |
| name  | varchar(20) | NO   |     | NULL    |       |
| date  | date        | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.01 sec)
mysql> select * from t;
+----+----------+------+
| id | name     | date |
+----+----------+------+
|  1 | jingyong | NULL |
|  2 | yeyali   | NULL |
|  3 | huangyan | NULL |
+----+----------+------+
3 rows in set (0.00 sec)

現在向mysql.t表中插入一條記錄並刪除這條記錄,然後使用備份的二進位制日誌檔案來重新執行事件來恢復這條記錄

mysql> insert into t value(4,'wenyao',NULL);
Query OK, 1 row affected (0.03 sec)
mysql> select * from t;
+----+----------+------+
| id | name     | date |
+----+----------+------+
|  1 | jingyong | NULL |
|  2 | yeyali   | NULL |
|  3 | huangyan | NULL |
|  4 | wenyao   | NULL |
+----+----------+------+
4 rows in set (0.00 sec)
mysql> delete from t where id=4;
Query OK, 1 row affected (0.13 sec)
mysql> select * from t;
+----+----------+------+
| id | name     | date |
+----+----------+------+
|  1 | jingyong | NULL |
|  2 | yeyali   | NULL |
|  3 | huangyan | NULL |
+----+----------+------+
3 rows in set (0.01 sec)

我們需要找到插入這條記錄在日誌檔案中的開始與結束的位置

# at 3306211
#191122 11:04:34 server id 1  end_log_pos 3306323 CRC32 0x88f89864      Query   thread_id=11    exec_time=0     error_code=0
SET TIMESTAMP=1574391874/*!*/;
insert into t value(4,'wenyao',NULL)
/*!*/;
# at 3306323
#191122 11:04:34 server id 1  end_log_pos 3306354 CRC32 0x966500de      Xid = 1041
COMMIT/*!*/;
# at 3306354
#191122 11:07:26 server id 1  end_log_pos 3306419 CRC32 0x1f3e6e28      Anonymous_GTID  last_committed=160      sequence_number=161     rbr_only=no
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 3306419
#191122 11:07:26 server id 1  end_log_pos 3306500 CRC32 0x883ecef4      Query   thread_id=11    exec_time=0     error_code=0
SET TIMESTAMP=1574392046/*!*/;
BEGIN
/*!*/;
# at 3306500
#191122 11:07:26 server id 1  end_log_pos 3306600 CRC32 0xecae0a57      Query   thread_id=11    exec_time=0     error_code=0
SET TIMESTAMP=1574392046/*!*/;
delete from t where id=4

從上面的日誌檔案內容可以看到插入的開始位置為3306211，結束位置為3306323

現在當前備份的二進位制日誌檔名為binlog.000001，重新執行事件的命令如下:

[mysql@localhost ~]$ mysqlbinlog --start-position=3306211 --stop-position=3306323 binlog.000001 | mysql --host=192.168.1.250 --port=3306 -uroot -pabcd  mysql
mysql: [Warning] Using a password on the command line interface can be insecure.

現在檢查mysql.t表中的記錄，可以看到被刪除的這條記錄恢復了。

mysql> select * from t;
+----+----------+------+
| id | name     | date |
+----+----------+------+
|  1 | jingyong | NULL |
|  2 | yeyali   | NULL |
|  3 | huangyan | NULL |
|  4 | wenyao   | NULL |
+----+----------+------+
4 rows in set (0.00 sec)

設定msyqlbinlog 伺服器ID
在使用--read-from-remote-server選項來呼叫mysqlbinlog時，mysqlbinlog會連線到一個MySQL伺服器，指定了一個伺服器ID來標識它並且從該伺服器獲取所需要的二進位制日誌檔案。可以使用mysqlbinlog以以下幾種方式來從伺服器中獲取日誌檔案:
.對檔案集指定顯式的名字。對每個檔案，mysqlbinlog會執行連線操作並執行binlog dump命令。伺服器會傳送檔案並斷開連線。每個檔案都有一個連線。

.指定開始檔案與--to-last-log選項,mysqlbinlog會執行連線並對所有的日誌檔案執行binlog dump命令。伺服器會傳送所有日誌檔案並斷開連線

.指定開始檔案與--stop-never選項(隱式實現--to-last-log選項的功能),mysqlbinlog會執行連線並對所有日誌檔案執行binlog dump命令。伺服器會傳送所有日誌檔案，但在傳送最後一個日誌檔案後不會斷開與伺服器的連線。

只有使用--read-from-remote-server選項時，mysqlbinlog使用一個為0的server ID進行連線，它將告訴伺服器在傳送所請求的日誌檔案後斷開連線。

使用--read-from-remote-server與--stop-never選項時，mysqlbinlog將使用一個非0的server ID進行連線，因此在最後的日誌檔案傳送之後伺服器不會斷開連線。預設的server ID為65535，但這個可以透過使用--stop-never-slave-server-id選項來修改。

因此，對於使用前兩種方式來獲取日誌檔案人，因為mysqlbinlog指定的server ID為0，所有伺服器會斷開連線，如果--stop-never選項被指定因為mysqlbinlog指定一個非0的server ID，所以伺服器將不會斷開連線。

mysqldump+mysqlbinlog執行備份與還原

相關文章