MySQL日志如何清理
2022/05/08 20:33:25
1.閿欒鏃ュ織
閿欒鏃ュ織瀵 mysql鐨勫惎鍔ㄣ佽繍琛屻佸叧闂繃绋嬭繘琛屼簡璁板綍锛
MySQL DBA 鍦ㄩ亣鍒伴棶棰樻椂鍊欙紝绗竴鏃堕棿搴旇鏌ョ湅杩欎釜閿欒鏃ュ織鏂囦欢锛
璇ユ枃浠朵笉浣嗚褰曚簡鍑洪敊淇℃伅锛岃繕璁板綍浜嗕竴浜涜鍛婁俊鎭互鍙婃纭俊鎭紝
杩欎釜 error 鏃ュ織鏂囦欢绫讳技浜 oracle 鐨 alert 鏂囦欢銆
鍙互閫氳繃鈥渟how variables like 鈥榣og_error鈥;鈥濆懡浠ゆ煡鐪嬮敊璇棩蹇楃殑璺緞
1.1 閰嶇疆鏂规硶
榛樿鏄惎鍔ㄧ殑锛屼竴鑸互err鍋氬悗缂鍚,闇瑕佸湪鍙傛暟鏂囦欢涓厤缃
鍏堟壘鍒板弬鏁版枃浠
log_error = /usr/local/mysql/data/error.err
1.2 閿欒鏃ュ織娓呯悊
[root@jeames data]# mv log.err log-old.err
mysql> FLUSH LOGS;
鏈嶅姟鍣ㄥ皢鍏抽棴骞堕噸鏂版墦寮鏃ュ織鏂囦欢
C:\Users\wangd>mysqladmin -uroot -p -P3308 flush-logs
2.鎱㈡煡璇㈡棩蹇
褰撴煡璇㈣秴杩囦竴瀹氭椂闂存病鏈夎繑鍥炵粨鏋滅殑鏃跺欙紝鎵嶄細璁板綍杩涙參鏌ヨ鏃ュ織銆
鈥 鎱㈡煡璇㈡棩蹇楀彲浠ュ府鍔 DBA 鎵惧嚭鎵ц鏁堢巼缂撴參鐨 SQ璇彞锛屼负鏁版嵁搴撲紭鍖栧伐浣滄彁渚涘府鍔┿
鈥 鎱㈡煡璇㈡棩蹇楅粯璁ゆ槸涓嶅紑鍚殑锛屽缓璁紑鍚參鏌ヨ鏃ュ織銆
鈥 褰撻渶瑕佽繘琛岄噰鏍峰垎鏋愭椂鎵嬪伐寮鍚
2.1 鍔熻兘
MySQL鐨勬參鏌ヨ鏃ュ織鏄 MySQL 鎻愪緵鐨勪竴绉嶆棩蹇楄褰曪紝
瀹冪敤鏉ヨ褰曞湪 MySQL 涓搷搴旀椂闂磋秴杩囬榾鍊肩殑璇彞锛
鍏蜂綋鎸 杩愯鏃堕棿瓒呰繃 long_query_time 鐨勫肩殑 SQL 锛屽垯浼氳璁板綍鍒版參鏌ヨ鏃ュ織涓
mysql> show variables like 鈥%long_query_time%鈥;
long_query_time 鐨勯粯璁ゅ间负 10锛屾剰鎬濇槸杩愯 10S 浠ヤ笂鐨勮鍙ャ
榛樿鎯呭喌涓嬶紝Mysql 鏁版嵁搴撳苟涓嶅惎鍔ㄦ參鏌ヨ鏃ュ織锛
闇瑕佹垜浠墜鍔ㄦ潵璁剧疆杩欎釜鍙傛暟锛
褰撶劧锛屽鏋滀笉鏄皟浼橀渶瑕佺殑璇濓紝涓鑸笉寤鸿鍚姩璇ュ弬鏁帮紝
鍥犱负寮鍚參鏌ヨ鏃ュ織浼氭垨澶氭垨灏戝甫鏉ヤ竴瀹氱殑鎬ц兘褰卞搷銆
鎱㈡煡璇㈡棩蹇楁敮鎸佸皢鏃ュ織璁板綍鍐欏叆鏂囦欢锛屼篃鏀寔灏嗘棩蹇楄褰曞啓鍏ユ暟鎹簱琛ㄣ
2.2 鎱㈡煡璇㈡棩蹇楀紑鍚
鍏充簬鎱㈡煡璇㈡棩蹇楋紝涓昏娑夊強鍒颁笅闈㈠嚑涓弬鏁帮細
slow_query_log 锛氭槸鍚﹀紑鍚參鏌ヨ鏃ュ織鍔熻兘锛堝繀濉級
slow_query_log_file锛氭參鏌ヨ鏃ュ織鏂囦欢鍙婁綅缃
long_query_time 锛氳秴杩囪瀹氬硷紝灏嗚瑙嗕綔鎱㈡煡璇紝骞惰褰曡嚦鎱㈡煡璇㈡棩蹇楁枃浠朵腑锛堝繀濉級
涔熷氨鏄锛屽彧鏈夋弧瓒充互涓婁笁涓潯浠讹紝鈥滄參鏌ヨ鍔熻兘鈥濇墠鍙兘姝ｇ‘寮鍚
mysql> show variables like 鈥%slow_%鈥;
mysql> set global slow_query_log=1;
mysql> show variables like 鈥%slow_%鈥;
show variables like 鈥%slow_query_log_file%鈥;
鍙傛暟鏂囦欢淇敼濡備笅锛
淇敼閰嶇疆鏂囦欢my.cnf锛屽湪[mysqld]涓嬬殑涓嬫柟鍔犲叆
[mysqld]
slow_query_log = ON
slow_query_log_file = /usr/local/mysql/data/slow.log
long_query_time = 1
閲嶅惎MySQL鏈嶅姟service mysqld restart鍗冲彲
2.3 娓呴櫎鎱㈡煡璇㈡棩蹇
select * from performance_schema.global_variables where variable_name in
(鈥榮low_query_log鈥,鈥榣og_output鈥,鈥榮low_query_log_file鈥,鈥榣ong_query_time鈥)
SET GLOBAL slow_query_log = 鈥極FF鈥;
mysql> flush slow logs;
2.4 鎱㈡煡璇㈠啓鍒拌〃閲
鈥 log_output 榛樿鏄疐ILE锛岃〃绀烘參鏌ヨ鏃ュ織杈撳叆鑷虫棩蹇楁枃浠讹紝鍙互閫氳繃set淇敼杈撳嚭涓篢ABLE
mysql> show variables like 鈥%log_output%鈥;
#榛樿鏄疐ILE锛岃〃绀烘參鏌ヨ鏃ュ織杈撳叆鑷虫棩蹇楁枃浠讹紝鍙互閫氳繃set淇敼杈撳嚭涓篢ABLE
mysql> set global log_output = 鈥楩ILE鈥;
mysql> set global log_output = 鈥楾ABLE鈥;
鏃犳硶鐩存帴鍒犻櫎锛屽鏋滅洿鎺ュ垹闄ょ殑璇濓紝
浼氬嚭鐜扳淓RROR 1556 (HY000): You can鈥檛 use locks with log tables.鈥濈殑閿欒鎻愮ず
SET GLOBAL slow_query_log = 鈥極FF鈥;
RENAME TABLE slow_log TO slow_log_temp;
DELETE FROM slow_log_temp WHERE start_time < DATE(NOW());
RENAME TABLE slow_log_temp TO slow_log;
SET GLOBAL slow_query_log = 鈥極N鈥;
3.鍏ㄦ煡璇㈡棩蹇
鍏ㄦ煡璇㈡棩蹇楄褰曚簡鎵鏈夊鏁版嵁搴撹姹傜殑淇℃伅锛
姝ｇ‘鐨 SQL鎵嶄細琚褰曚笅鏉ワ紙閿欒鍐欐硶鐨 SQL 璇彞涓嶄細璁板綍锛夛紝
鍖呮嫭 show銆佹煡璇 select 璇彞銆佹潈闄愪笉瓒崇殑璇彞锛圗RROR 1044 (42000): Access denied for user锛夈傞粯璁や綅缃湪鍙橀噺 datadir 涓嬶紝榛樿鏂囦欢鍚嶄负锛氫富鏈哄悕.log銆
MySQL 鐨勯氱敤鏌ヨ鏃ュ織榛樿鎯呭喌涓嬫槸涓嶅紑鍚殑,褰撻渶瑕佽繘琛岄噰鏍峰垎鏋愭椂鎵嬪伐寮鍚
mysql> show variables like 鈥榞eneral_log鈥;
mysql> show variables like 鈥%general_log_file%鈥;
3.1 鏃ュ織寮鍚
mysql> SET GLOBAL general_log=1;
mysql> show variables like 鈥榞eneral_log鈥;
榛樿鍚嶇О涓猴細鍙橀噺 datadir 涓嬶細涓绘満鍚.log
鏌ヨ鏃ュ織璁板綍鏌ヨ璇彞涓庡惎鍔ㄦ椂闂达紝寤鸿涓嶆槸鍦ㄨ皟璇曠幆澧冧笅涓嶈寮鍚煡璇㈡棩蹇楋紝
鍥犱负浼氫笉鏂崰鎹綘鐨勭鐩樼┖闂达紝骞朵細浜х敓澶ч噺鐨 IO銆
3.2 鏃ュ織娓呯悊
##log娓呯悊
SET GLOBAL general_log = 鈥極FF鈥;
[root@jeames data]# mv jeames.log jeames_old.log
mysql> flush general logs;
##濡傛灉浣犵殑鏃ュ織鏄啓鍒拌〃閲岀殑锛屽垯閫氳繃浠ヤ笅鏂规硶
SET GLOBAL general_log = 鈥極FF鈥;
RENAME TABLE general_log TO general_log_temp;
DELETE FROM general_log_temp WHERE event_time < DATE(NOW());
RENAME TABLE general_log_temp TO general_log;
SET GLOBAL general_log = 鈥極N鈥;
4.浜岃繘鍒舵棩蹇
Binlog鏄疢ySQL涓竴涓緢閲嶈鐨勬棩蹇楋紝璁板綍浜嗗鏁版嵁搴撹繘琛屽彉鏇寸殑鎿嶄綔锛
浣嗘槸涓嶅寘鎷 select鎿嶄綔浠ュ強 show 鎿嶄綔锛屽洜涓鸿繖绫绘搷浣滃鏁版嵁搴撴湰韬病鏈夋病鏈変慨鏀广
濡傛灉鎯宠褰 select鍜 show 鐨勮瘽锛岄偅灏遍渶瑕佸紑鍚叏鏌ヨ鏃ュ織銆
鍙﹀ binlog 杩樺寘鎷簡鎵ц鏁版嵁搴撴洿鏀规搷浣滄椂闂村拰鎵ц鏃堕棿绛変俊鎭
binlog 鏄 MySQL Server 灞傝褰曠殑浜岃繘鍒舵棩蹇楁枃浠讹紝閫昏緫灞傞潰
4.1 浜岃繘鍒舵棩蹇楅厤缃
娉細鑻ユ兂寮鍚簩杩涘埗鏃ュ織锛屽垯蹇呴』鍔犱笂 server_id 鍙傛暟锛屽涓
[mysqld]
log-bin
server_id=80233306
4.2 浣滅敤
锛1锛夋仮澶 recovery 銆傛煇浜涙暟鎹殑鎭㈠闇瑕佷簩杩涘埗鏃ュ織锛屽湪鍏ㄥ簱鏂囦欢鎭㈠鍚庯紝
鍙互鍦ㄦ鍩虹涓婇氳繃浜岃繘鍒舵棩蹇楄繘琛 point-to-time 鐨勬仮澶嶏紙mysqldump 鍏ㄩ噺鎭㈠+binlog澧為噺鎭㈠锛夈
锛2锛夊鍒讹紙replication锛夈傚叾鍘熺悊鍜屾仮澶嶇被浼硷紝
閫氳繃澶嶅埗鍜屾墽琛屼簩杩涘埗鏃ュ織浣垮緱涓鍙拌繙绋嬬殑 mysql鏁版嵁搴擄紙slave锛変簬涓鍙 mysql 鏁版嵁搴擄紙master锛夎繘琛屽疄鏃跺悓姝ャ
4.3 娓呯悊浜岃繘鍒舵棩蹇
mysql> show variables like 鈥%binlog_expire_logs_seconds%鈥 ;
mysql 8寮濮 expire_logs_days搴熷純
鍚敤binlog_expire_logs_seconds璁剧疆binlog鑷姩娓呴櫎鏃ュ織鏃堕棿
淇濆瓨鏃堕棿 浠ョ涓哄崟浣嶏紱榛樿2592000 30澶
14400 4灏忔椂锛86400 1澶╋紱259200 3澶
##鑷姩鍒犻櫎
mysql> set global binlog_expire_logs_seconds=86400;
mysql> set global binlog_expire_logs_seconds=2592000;
##鎵嬪姩鍒犻櫎
榛樿鏃ュ織鏂囦欢杈惧埌 1G 閮戒細閲嶆柊鐢熸垚涓涓柊鐨勪簩杩涘埗鏃ュ織鏂囦欢
mysql> select @@max_binlog_size;
#binlog.000025 涔嬪墠鐨勬棩蹇楅兘浼氳鍒犻櫎
mysql> PURGE BINARY LOGS TO 鈥榖inlog.000025鈥;
#鏃堕棿鈥2020-04-28 23:59:59鈥欎箣鍓嶇殑鏃ュ織閮戒細琚垹闄
mysql> PURGE BINARY LOGS BEFORE 鈥2020-04-28 23:59:59鈥;
#娓呯┖鍘嗗彶浜岃繘鍒舵棩蹇楋紝浠 000001 寮濮嬮噸鏂
mysql> RESET MASTER;
mysql> select @@binlog_format ;
閿欒鏃ュ織瀵 mysql鐨勫惎鍔ㄣ佽繍琛屻佸叧闂繃绋嬭繘琛屼簡璁板綍锛
MySQL DBA 鍦ㄩ亣鍒伴棶棰樻椂鍊欙紝绗竴鏃堕棿搴旇鏌ョ湅杩欎釜閿欒鏃ュ織鏂囦欢锛
璇ユ枃浠朵笉浣嗚褰曚簡鍑洪敊淇℃伅锛岃繕璁板綍浜嗕竴浜涜鍛婁俊鎭互鍙婃纭俊鎭紝
杩欎釜 error 鏃ュ織鏂囦欢绫讳技浜 oracle 鐨 alert 鏂囦欢銆
鍙互閫氳繃鈥渟how variables like 鈥榣og_error鈥;鈥濆懡浠ゆ煡鐪嬮敊璇棩蹇楃殑璺緞
1.1 閰嶇疆鏂规硶
榛樿鏄惎鍔ㄧ殑锛屼竴鑸互err鍋氬悗缂鍚,闇瑕佸湪鍙傛暟鏂囦欢涓厤缃
鍏堟壘鍒板弬鏁版枃浠
log_error = /usr/local/mysql/data/error.err
1.2 閿欒鏃ュ織娓呯悊
[root@jeames data]# mv log.err log-old.err
mysql> FLUSH LOGS;
鏈嶅姟鍣ㄥ皢鍏抽棴骞堕噸鏂版墦寮鏃ュ織鏂囦欢
C:\Users\wangd>mysqladmin -uroot -p -P3308 flush-logs
2.鎱㈡煡璇㈡棩蹇
褰撴煡璇㈣秴杩囦竴瀹氭椂闂存病鏈夎繑鍥炵粨鏋滅殑鏃跺欙紝鎵嶄細璁板綍杩涙參鏌ヨ鏃ュ織銆
鈥 鎱㈡煡璇㈡棩蹇楀彲浠ュ府鍔 DBA 鎵惧嚭鎵ц鏁堢巼缂撴參鐨 SQ璇彞锛屼负鏁版嵁搴撲紭鍖栧伐浣滄彁渚涘府鍔┿
鈥 鎱㈡煡璇㈡棩蹇楅粯璁ゆ槸涓嶅紑鍚殑锛屽缓璁紑鍚參鏌ヨ鏃ュ織銆
鈥 褰撻渶瑕佽繘琛岄噰鏍峰垎鏋愭椂鎵嬪伐寮鍚
2.1 鍔熻兘
MySQL鐨勬參鏌ヨ鏃ュ織鏄 MySQL 鎻愪緵鐨勪竴绉嶆棩蹇楄褰曪紝
瀹冪敤鏉ヨ褰曞湪 MySQL 涓搷搴旀椂闂磋秴杩囬榾鍊肩殑璇彞锛
鍏蜂綋鎸 杩愯鏃堕棿瓒呰繃 long_query_time 鐨勫肩殑 SQL 锛屽垯浼氳璁板綍鍒版參鏌ヨ鏃ュ織涓
mysql> show variables like 鈥%long_query_time%鈥;
long_query_time 鐨勯粯璁ゅ间负 10锛屾剰鎬濇槸杩愯 10S 浠ヤ笂鐨勮鍙ャ
榛樿鎯呭喌涓嬶紝Mysql 鏁版嵁搴撳苟涓嶅惎鍔ㄦ參鏌ヨ鏃ュ織锛
闇瑕佹垜浠墜鍔ㄦ潵璁剧疆杩欎釜鍙傛暟锛
褰撶劧锛屽鏋滀笉鏄皟浼橀渶瑕佺殑璇濓紝涓鑸笉寤鸿鍚姩璇ュ弬鏁帮紝
鍥犱负寮鍚參鏌ヨ鏃ュ織浼氭垨澶氭垨灏戝甫鏉ヤ竴瀹氱殑鎬ц兘褰卞搷銆
鎱㈡煡璇㈡棩蹇楁敮鎸佸皢鏃ュ織璁板綍鍐欏叆鏂囦欢锛屼篃鏀寔灏嗘棩蹇楄褰曞啓鍏ユ暟鎹簱琛ㄣ
2.2 鎱㈡煡璇㈡棩蹇楀紑鍚
鍏充簬鎱㈡煡璇㈡棩蹇楋紝涓昏娑夊強鍒颁笅闈㈠嚑涓弬鏁帮細
slow_query_log 锛氭槸鍚﹀紑鍚參鏌ヨ鏃ュ織鍔熻兘锛堝繀濉級
slow_query_log_file锛氭參鏌ヨ鏃ュ織鏂囦欢鍙婁綅缃
long_query_time 锛氳秴杩囪瀹氬硷紝灏嗚瑙嗕綔鎱㈡煡璇紝骞惰褰曡嚦鎱㈡煡璇㈡棩蹇楁枃浠朵腑锛堝繀濉級
涔熷氨鏄锛屽彧鏈夋弧瓒充互涓婁笁涓潯浠讹紝鈥滄參鏌ヨ鍔熻兘鈥濇墠鍙兘姝ｇ‘寮鍚
mysql> show variables like 鈥%slow_%鈥;
mysql> set global slow_query_log=1;
mysql> show variables like 鈥%slow_%鈥;
show variables like 鈥%slow_query_log_file%鈥;
鍙傛暟鏂囦欢淇敼濡備笅锛
淇敼閰嶇疆鏂囦欢my.cnf锛屽湪[mysqld]涓嬬殑涓嬫柟鍔犲叆
[mysqld]
slow_query_log = ON
slow_query_log_file = /usr/local/mysql/data/slow.log
long_query_time = 1
閲嶅惎MySQL鏈嶅姟service mysqld restart鍗冲彲
2.3 娓呴櫎鎱㈡煡璇㈡棩蹇
select * from performance_schema.global_variables where variable_name in
(鈥榮low_query_log鈥,鈥榣og_output鈥,鈥榮low_query_log_file鈥,鈥榣ong_query_time鈥)
SET GLOBAL slow_query_log = 鈥極FF鈥;
mysql> flush slow logs;
2.4 鎱㈡煡璇㈠啓鍒拌〃閲
鈥 log_output 榛樿鏄疐ILE锛岃〃绀烘參鏌ヨ鏃ュ織杈撳叆鑷虫棩蹇楁枃浠讹紝鍙互閫氳繃set淇敼杈撳嚭涓篢ABLE
mysql> show variables like 鈥%log_output%鈥;
#榛樿鏄疐ILE锛岃〃绀烘參鏌ヨ鏃ュ織杈撳叆鑷虫棩蹇楁枃浠讹紝鍙互閫氳繃set淇敼杈撳嚭涓篢ABLE
mysql> set global log_output = 鈥楩ILE鈥;
mysql> set global log_output = 鈥楾ABLE鈥;
鏃犳硶鐩存帴鍒犻櫎锛屽鏋滅洿鎺ュ垹闄ょ殑璇濓紝
浼氬嚭鐜扳淓RROR 1556 (HY000): You can鈥檛 use locks with log tables.鈥濈殑閿欒鎻愮ず
SET GLOBAL slow_query_log = 鈥極FF鈥;
RENAME TABLE slow_log TO slow_log_temp;
DELETE FROM slow_log_temp WHERE start_time < DATE(NOW());
RENAME TABLE slow_log_temp TO slow_log;
SET GLOBAL slow_query_log = 鈥極N鈥;
3.鍏ㄦ煡璇㈡棩蹇
鍏ㄦ煡璇㈡棩蹇楄褰曚簡鎵鏈夊鏁版嵁搴撹姹傜殑淇℃伅锛
姝ｇ‘鐨 SQL鎵嶄細琚褰曚笅鏉ワ紙閿欒鍐欐硶鐨 SQL 璇彞涓嶄細璁板綍锛夛紝
鍖呮嫭 show銆佹煡璇 select 璇彞銆佹潈闄愪笉瓒崇殑璇彞锛圗RROR 1044 (42000): Access denied for user锛夈傞粯璁や綅缃湪鍙橀噺 datadir 涓嬶紝榛樿鏂囦欢鍚嶄负锛氫富鏈哄悕.log銆
MySQL 鐨勯氱敤鏌ヨ鏃ュ織榛樿鎯呭喌涓嬫槸涓嶅紑鍚殑,褰撻渶瑕佽繘琛岄噰鏍峰垎鏋愭椂鎵嬪伐寮鍚
mysql> show variables like 鈥榞eneral_log鈥;
mysql> show variables like 鈥%general_log_file%鈥;
3.1 鏃ュ織寮鍚
mysql> SET GLOBAL general_log=1;
mysql> show variables like 鈥榞eneral_log鈥;
榛樿鍚嶇О涓猴細鍙橀噺 datadir 涓嬶細涓绘満鍚.log
鏌ヨ鏃ュ織璁板綍鏌ヨ璇彞涓庡惎鍔ㄦ椂闂达紝寤鸿涓嶆槸鍦ㄨ皟璇曠幆澧冧笅涓嶈寮鍚煡璇㈡棩蹇楋紝
鍥犱负浼氫笉鏂崰鎹綘鐨勭鐩樼┖闂达紝骞朵細浜х敓澶ч噺鐨 IO銆
3.2 鏃ュ織娓呯悊
##log娓呯悊
SET GLOBAL general_log = 鈥極FF鈥;
[root@jeames data]# mv jeames.log jeames_old.log
mysql> flush general logs;
##濡傛灉浣犵殑鏃ュ織鏄啓鍒拌〃閲岀殑锛屽垯閫氳繃浠ヤ笅鏂规硶
SET GLOBAL general_log = 鈥極FF鈥;
RENAME TABLE general_log TO general_log_temp;
DELETE FROM general_log_temp WHERE event_time < DATE(NOW());
RENAME TABLE general_log_temp TO general_log;
SET GLOBAL general_log = 鈥極N鈥;
4.浜岃繘鍒舵棩蹇
Binlog鏄疢ySQL涓竴涓緢閲嶈鐨勬棩蹇楋紝璁板綍浜嗗鏁版嵁搴撹繘琛屽彉鏇寸殑鎿嶄綔锛
浣嗘槸涓嶅寘鎷 select鎿嶄綔浠ュ強 show 鎿嶄綔锛屽洜涓鸿繖绫绘搷浣滃鏁版嵁搴撴湰韬病鏈夋病鏈変慨鏀广
濡傛灉鎯宠褰 select鍜 show 鐨勮瘽锛岄偅灏遍渶瑕佸紑鍚叏鏌ヨ鏃ュ織銆
鍙﹀ binlog 杩樺寘鎷簡鎵ц鏁版嵁搴撴洿鏀规搷浣滄椂闂村拰鎵ц鏃堕棿绛変俊鎭
binlog 鏄 MySQL Server 灞傝褰曠殑浜岃繘鍒舵棩蹇楁枃浠讹紝閫昏緫灞傞潰
4.1 浜岃繘鍒舵棩蹇楅厤缃
娉細鑻ユ兂寮鍚簩杩涘埗鏃ュ織锛屽垯蹇呴』鍔犱笂 server_id 鍙傛暟锛屽涓
[mysqld]
log-bin
server_id=80233306
4.2 浣滅敤
锛1锛夋仮澶 recovery 銆傛煇浜涙暟鎹殑鎭㈠闇瑕佷簩杩涘埗鏃ュ織锛屽湪鍏ㄥ簱鏂囦欢鎭㈠鍚庯紝
鍙互鍦ㄦ鍩虹涓婇氳繃浜岃繘鍒舵棩蹇楄繘琛 point-to-time 鐨勬仮澶嶏紙mysqldump 鍏ㄩ噺鎭㈠+binlog澧為噺鎭㈠锛夈
锛2锛夊鍒讹紙replication锛夈傚叾鍘熺悊鍜屾仮澶嶇被浼硷紝
閫氳繃澶嶅埗鍜屾墽琛屼簩杩涘埗鏃ュ織浣垮緱涓鍙拌繙绋嬬殑 mysql鏁版嵁搴擄紙slave锛変簬涓鍙 mysql 鏁版嵁搴擄紙master锛夎繘琛屽疄鏃跺悓姝ャ
4.3 娓呯悊浜岃繘鍒舵棩蹇
mysql> show variables like 鈥%binlog_expire_logs_seconds%鈥 ;
mysql 8寮濮 expire_logs_days搴熷純
鍚敤binlog_expire_logs_seconds璁剧疆binlog鑷姩娓呴櫎鏃ュ織鏃堕棿
淇濆瓨鏃堕棿 浠ョ涓哄崟浣嶏紱榛樿2592000 30澶
14400 4灏忔椂锛86400 1澶╋紱259200 3澶
##鑷姩鍒犻櫎
mysql> set global binlog_expire_logs_seconds=86400;
mysql> set global binlog_expire_logs_seconds=2592000;
##鎵嬪姩鍒犻櫎
榛樿鏃ュ織鏂囦欢杈惧埌 1G 閮戒細閲嶆柊鐢熸垚涓涓柊鐨勪簩杩涘埗鏃ュ織鏂囦欢
mysql> select @@max_binlog_size;
#binlog.000025 涔嬪墠鐨勬棩蹇楅兘浼氳鍒犻櫎
mysql> PURGE BINARY LOGS TO 鈥榖inlog.000025鈥;
#鏃堕棿鈥2020-04-28 23:59:59鈥欎箣鍓嶇殑鏃ュ織閮戒細琚垹闄
mysql> PURGE BINARY LOGS BEFORE 鈥2020-04-28 23:59:59鈥;
#娓呯┖鍘嗗彶浜岃繘鍒舵棩蹇楋紝浠 000001 寮濮嬮噸鏂
mysql> RESET MASTER;
mysql> select @@binlog_format ;