mysqldump+mysqlbinlog執(zhí)行備份與還原
服務器的二進制日志文件由用來描述修改數(shù)據(jù)庫內(nèi)容的事件組成。服務器以二進制方式來寫這些文件。為了以文本格式來顯示這些內(nèi)容，可以使用MySQLbinlog工具。也可以使用mysqlbinlog來顯示在復制環(huán)境中由從從slave服務器所寫入中relay日志文件中的內(nèi)容，因為其格式與二進制日志文件格式一樣。
mysqlbinlog的使用語法如下:
Usage: mysqlbinlog [options] log-files
漯河ssl適用于網(wǎng)站、小程序/APP、API接口等需要進行數(shù)據(jù)傳輸應用場景，ssl證書未來市場廣闊！成為成都創(chuàng)新互聯(lián)公司的ssl證書銷售渠道，可以享受市場價格4-6折優(yōu)惠！如果有意向歡迎電話聯(lián)系或者加微信：13518219792（備注：SSL證書合作）期待與您的合作！
下面的命令用來顯示名為binlog.000001的二進制日志文件的內(nèi)容:
[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二進制日志文件中所包含的內(nèi)容。對于基于語句的日志，事件信息包括SQL語句，執(zhí)行語句的服務器ID，語句被執(zhí)行的時間戳，執(zhí)行時間等。對于基于行記錄的日志，事件信息指示行的改變而不是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后面的數(shù)字指示事件在二進制日志文件中的偏移量或開始位置。
第二行是以日期和時間開始指示語句開始執(zhí)行的時間。對于復制來說，這個時間戳將傳播到從屬服務器。server id是事件起源服務器的server_id值。end_log_pos指示下一個事件開始的位置(它是當前事件的終止位置+1)。thread_id那個線程來執(zhí)行這個事件。exec_time是在主服務器上執(zhí)行事件所花費的時間。在從屬服務器上，它是從屬服務器上執(zhí)行結(jié)束時間減去主服務器上的執(zhí)行開始時間的差值。這種差值可以作為一種指示來表示復制進程落后于主服務器多長時間。error_code指示執(zhí)行事件的結(jié)果。零意味著沒有出現(xiàn)錯誤。
mysqlbinglog的輸出可以用來重新執(zhí)行日志文件中的語句(例如，通過使用mysql工具)。這在服務器崩潰時用來恢復是很有用的。
正常來說，使用mysqlbinlog直接讀取二進制日志文件并應用它們到本地MySQL服務器。它也可以通過使用--read-from-remote-server選項來從遠程服務器上讀取二進制日志文件。為了讀取遠程二進制日志文件，連接參數(shù)選項可以被指定用來指示如何連接服務器。這些選項有--host,--password,--port,--protocol,--socket和--user，除非使用了--read-from-remote-server選項否則它們會被忽略。
當對一個大的二進制日志文件執(zhí)行mysqlbinlog時，要小心注意文件系統(tǒng)是否有足夠的空間來存儲結(jié)果文件。為了配置目錄給mysqlbinlog臨時使用存儲文件，使用TMPDIR環(huán)境變量。
使用mysqlbinlog備份二進制日志文件
缺省情況下，mysqlbinlog讀取二進制日志文件并以文本格式來顯示它的內(nèi)容。這能讓你更容易使用文件來檢查發(fā)生的事件和重新執(zhí)行它們(例如，通過使用輸出作為mysql的輸入)。mysqlbinlog可以直接從本地文件系統(tǒng)中讀取日志文件或者使用--read-from-remote-server選項來連接遠程服務器并從遠程服務器上讀取二進制日志文件。mysqlbinlog以文本格式將內(nèi)容輸出到標準輸出，或者如果指定了--result-file=file_name選項會將內(nèi)容寫入文件。
mysqlbinlog可以讀了二進制日志文件并將其包含的內(nèi)容以二進制格式而不是文本格式寫入新文件。這種能力可以讓你以原來的格式來備份二進制日志文件。mysqlbinlog可以生成靜態(tài)備份，在備份一組日志文件時當備份完最后的文件時而停止。它也可以生成一種連續(xù)(live)備份，當備份到最后的日志文件時仍然保持對服務器的連接并當生成新的事件時繼續(xù)復制新的事件。在連續(xù)備份操作時，mysqlbinlog會運行到連接中斷為止(比如，服務器退出)或mysqlbinlog被強制中斷為止。當連接中斷，mysqlbinlog不會進行等待并重新進行連接，不像從屬復制服務器那樣。為了在服務器重啟之后繼續(xù)一個live備份，必須重新啟動mysqlbinlog。
二進制日志文件備份要求在調(diào)用mysqlbinlog時最少要使用兩個選項:
.--read-from-remote-server(或-R)選項來告訴mysqlbinlog連接到一個服務器并讀取它的二進制日志文件(這類似于一個從屬復制服務器連接到它的主服務器).
.--raw選項告訴mysqlbinlog以原始(二進制)格式輸出，而不是文本格式。
與--read-from-remote-server一起通常還指定其它選項:--host指示服務器運行在哪里，并且可能需要指定連接選項--user和password。
與--raw聯(lián)合使用的幾個其它選項:.--stop-never:在讀取到最后日志文件后保持對服務器的連接并繼續(xù)讀取新的事件。
.--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的日志文件進行靜態(tài)備份，使用以下命令:
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文件，第一個命令將不會讀取，但第二個命令會讀取。
為了進行l(wèi)ive備份mysqlbinlog從binlog.000130開始備份現(xiàn)有的日志文件，然后保持對服務器的連接來復制生成的新事件:
mysqlbinlog --read-from-remote-server --host=host_name --raw
--stop-never binlog.000130
使用--stop-never選項，不需要指定--to-last-log來讀取最后的日志文件因為這個選項是隱含的
輸出文件名
在沒有使用--raw選項時，mysqlbinlog會生成文本格式的輸出，如果指定--result-file選項，指定將所有輸出寫入一個文件中。使用--raw選項時，mysqlbinlog會將服務器的每個日志文件轉(zhuǎn)換成一個二進制輸出文件。缺省情況下，mysqlbinlog會在當前目錄中生成與源日志文件同名的文件。為了修改輸出文件名，使用--result-file選項。與--raw聯(lián)合使用，--result-file選項值將作為前綴來命名輸出文件名。
現(xiàn)在對遠程服務器中的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執(zhí)行備份與還原操作
下面將介紹一個簡單的例子顯示如何使用mysqldump與mysqlbinlog一起來備份MySQL服務器的數(shù)據(jù)和二進制日志文件以及在數(shù)據(jù)丟失時如何使用備份來還原數(shù)據(jù)。
現(xiàn)在主機上的MySQL服務器的第一個二進制日志文件為binlog.000001
mysql> show binary logs; +---------------+-----------+ | Log_name | File_size | +---------------+-----------+ | binlog.000001 | 2530 | +---------------+-----------+ 1 row in set (0.01 sec)
使用mysqlbinlog來對二進制日志文件執(zhí)行連續(xù)備份:
[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
創(chuàng)建了一個名為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來創(chuàng)建一個dump文件作為對MySQL服務器的數(shù)據(jù)快照。使用--all-databases,--events和--routines來備份所有的數(shù)據(jù)，--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
現(xiàn)在刪除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
現(xiàn)在假設mysql庫中的表t丟失了，使用最近的dump文件來還原數(shù)據(jù):
[mysql@localhost ~]$ mysql --host=192.168.1.250 --port=3306 -uroot -pabcd mysql
還原數(shù)據(jù)后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)
現(xiàn)在向mysql.t表中插入一條記錄并刪除這條記錄,然后使用備份的二進制日志文件來重新執(zhí)行事件來恢復這條記錄
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)
我們需要找到插入這條記錄在日志文件中的開始與結(jié)束的位置
# 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
從上面的日志文件內(nèi)容可以看到插入的開始位置為3306211，結(jié)束位置為3306323
現(xiàn)在當前備份的二進制日志文件名為binlog.000001，重新執(zhí)行事件的命令如下:
[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.
現(xiàn)在檢查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選項來調(diào)用mysqlbinlog時，mysqlbinlog會連接到一個MySQL服務器，指定了一個服務器ID來標識它并且從該服務器獲取所需要的二進制日志文件?？梢允褂胢ysqlbinlog以以下幾種方式來從服務器中獲取日志文件:
.對文件集指定顯式的名字。對每個文件，mysqlbinlog會執(zhí)行連接操作并執(zhí)行binlog dump命令。服務器會發(fā)送文件并斷開連接。每個文件都有一個連接。
.指定開始文件與--to-last-log選項,mysqlbinlog會執(zhí)行連接并對所有的日志文件執(zhí)行binlog dump命令。服務器會發(fā)送所有日志文件并斷開連接
.指定開始文件與--stop-never選項(隱式實現(xiàn)--to-last-log選項的功能),mysqlbinlog會執(zhí)行連接并對所有日志文件執(zhí)行binlog dump命令。服務器會發(fā)送所有日志文件，但在發(fā)送最后一個日志文件后不會斷開與服務器的連接。
只有使用--read-from-remote-server選項時，mysqlbinlog使用一個為0的server ID進行連接，它將告訴服務器在發(fā)送所請求的日志文件后斷開連接。
使用--read-from-remote-server與--stop-never選項時，mysqlbinlog將使用一個非0的server ID進行連接，因此在最后的日志文件發(fā)送之后服務器不會斷開連接。缺省的server ID為65535，但這個可以通過使用--stop-never-slave-server-id選項來修改。
因此，對于使用前兩種方式來獲取日志文件人，因為mysqlbinlog指定的server ID為0，所有服務器會斷開連接，如果--stop-never選項被指定因為mysqlbinlog指定一個非0的server ID，所以服務器將不會斷開連接。
本文名稱：mysqldump+mysqlbinlog執(zhí)行備份與還原
文章鏈接：http://jinyejixie.com/article12/iiecgc.html
成都網(wǎng)站建設公司_創(chuàng)新互聯(lián)，為您提供響應式網(wǎng)站、網(wǎng)站建設、動態(tài)網(wǎng)站、、網(wǎng)站改版、網(wǎng)站內(nèi)鏈
聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請盡快告知，我們將會在第一時間刪除。文章觀點不代表本網(wǎng)站立場，如需處理請聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時需注明來源： 創(chuàng)新互聯(lián)
- 上海網(wǎng)站建設公司和上海網(wǎng)站設計公司揭秘為何要網(wǎng)站更新 2020-11-08
- 上海網(wǎng)站設計公司怎樣選擇顏色與圖片？ 2022-05-06
- 上海網(wǎng)站建設公司和上海網(wǎng)站設計公司是現(xiàn)在的熱門 2020-11-08
- 高端網(wǎng)站設計公司如何專業(yè)可靠的設計頁面 2023-01-09
- 泰州網(wǎng)站設計公司：創(chuàng)新互聯(lián)告訴你建站行業(yè)價格差異打的原因！ 2021-11-03
- 選擇深圳網(wǎng)站設計公司需要注意的事項 2021-06-11
- 深圳網(wǎng)站設計公司談攻擊Web應用程序時最有可能遇到的狀態(tài)碼及相關的原因短語 2022-06-21
- 嘉興網(wǎng)站設計公司：做網(wǎng)站之前必須要做好這幾點！ 2021-12-21
- 金山網(wǎng)站建設_網(wǎng)絡營銷_網(wǎng)站設計公司 2023-03-14
- 網(wǎng)站設計公司解析如何做好的網(wǎng)站設計 2018-06-01
- 成都了解網(wǎng)站設計公司的設計過程以便更有效的溝通 2023-03-07
- 選擇網(wǎng)站設計公司，看這幾點就夠了 2013-10-12