MySQL複製的問題。為什麼 binlogs 增長如此之快?
我有 2 個 MySQL 伺服器:
主伺服器:mysql 版本 5.7.14
從伺服器:Docker 容器中的 mysql 版本 5.7.14(來自官方 docker hub)。
GTID 複製。
我有兩個問題:
- Binlogs 增長非常快。我設置了 2 天限制輪換,但這無濟於事 - 每天 binlog 文件夾至少增加兩次(第一天 25Gb,第二天 50,第三天 80 等)。
- 從伺服器“落後於主伺服器的秒數”增加。
本地網路中的伺服器 (100 mbit/s),SSD 磁碟,數據庫大小接近 40Gb。
我使用 Percona Xtrabackup 為 slave 複製 db。
也許伺服器的配置不正確&我需要修復一些東西?
這是大師 my.cnf 文件:
[client] port = 3306 socket = /var/run/mysqld/mysqld.sock [mysqld_safe] pid-file = /var/run/mysqld/mysqld.pid socket = /var/run/mysqld/mysqld.sock nice = 0 [mysqld] user = mysql pid-file = /var/run/mysqld/mysqld.pid socket = /var/run/mysqld/mysqld.sock port = 3306 basedir = /usr datadir = /var/lib/mysql tmpdir = /tmp lc-messages-dir = /usr/share/mysql explicit_defaults_for_timestamp max_allowed_packet = 256M log-error = /var/log/mysql/error.log symbolic-links=0 !includedir /etc/mysql/conf.d/ innodb_buffer_pool_size=15000M innodb_buffer_pool_instances=1 sql_mode = '' slow_query_log = 1 slow_query_log_file = '/var/log/mysql/slow.log' long_query_time = 1 log_queries_not_using_indexes = 0 #skip-grant-tables default_week_format = 1 skip-name-resolve sort_buffer_size=4M join_buffer_size=4M innodb_sort_buffer_size=4M tmp_table_size=5000M max_heap_table_size=5000M [mysqld] server-id = 1 binlog-format = row gtid_mode=ON enforce-gtid-consistency=ON log-slave-updates log_bin = /var/log/mysql/mysql-bin.log performance_schema_max_digest_length = 8192 max_digest_length = 8192 max_binlog_size= 1G expire_logs_days = 2 binlog-ignore-db=check_passport replicate-ignore-db=check_passport sync_binlog = 1 innodb_flush_log_at_trx_commit = 1 key_buffer_size = 16M innodb_flush_method = O_DIRECT max_connections = 200 #innodb_temp_data_file_path=ibtmp1:12M:autoextend:max:5G
這是從伺服器的my.cnf
[client] port = 3306 socket = /var/run/mysqld/mysqld.sock [mysqld_safe] socket = /var/run/mysqld/mysqld.sock nice = 0 [mysqld] sql_mode = "" character_set_server = utf8 collation_server = utf8_general_ci user = mysql pid-file = /var/run/mysqld/mysqld.pid socket = /var/run/mysqld/mysqld.sock port = 3306 basedir = /usr datadir = /var/lib/mysql tmpdir = /tmp language = /usr/share/mysql/english old_passwords = 0 bind-address = 127.0.0.1 skip-host-cache skip-name-resolve skip-external-locking # Disabling symbolic-links is recommended to prevent assorted security risks symbolic-links=0 max_allowed_packet = 256M #key_buffer_size = 16M innodb_buffer_pool_size = 2048M innodb_log_file_size = 256M innodb_file_per_table = 1 innodb_flush_method = O_DIRECT innodb_flush_log_at_trx_commit = 1 max_connections = 136 query_cache_size = 0 slow_query_log = /var/log/mysql/mysql-slow.log long_query_time = 1 expire_logs_days = 2 max_binlog_size = 1G [mysqldump] quick quote-names max_allowed_packet = 16M [mysqld] server-id = 2 binlog-format = row gtid_mode=ON enforce-gtid-consistency=ON relay-log = /var/log/mysql/mysql-relay-bin.log log_bin = /var/log/mysql/mysql-bin.log skip_slave_start log_slave_updates = 0 read_only = ON innodb_file_per_table = ON #innodb_buffer_pool_size = 3G innodb_flush_method = O_DIRECT innodb_flush_log_at_trx_commit = 1 max_binlog_size = 1G #max_relay_log_size = 1G #relay_log_space_limit = 20G relay_log_recovery = ON expire_logs_days = 2 #slave-parallel-workers = 0 binlog-ignore-db=check_passport replicate-ignore-db=check_passport replicate-ignore-table=gfk.application_insurance replicate-ignore-table=gfk.archive_client_building replicate-ignore-table=gfk.comments_passwords replicate-ignore-table=gfk.date_interval
SHOW SLAVE STATUS\G的輸出
*************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.0.4 Master_User: slave_user Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.004720 Read_Master_Log_Pos: 518759418 Relay_Log_File: mysql-relay-bin.000188 Relay_Log_Pos: 213202356 Relay_Master_Log_File: mysql-bin.004703 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: check_passport Replicate_Do_Table: Replicate_Ignore_Table: gfk.application_insurance,gfk.date_interval,gfk.archive_client_building,gfk.comments_passwords Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 213202143 Relay_Log_Space: 18773097825 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: Yes Master_SSL_CA_File: /var/lib/master_cert/ca.pem Master_SSL_CA_Path: Master_SSL_Cert: /var/lib/master_cert/client-cert.pem Master_SSL_Cipher: Master_SSL_Key: /var/lib/master_cert/client-key.pem Seconds_Behind_Master: 14488 Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 1 Master_UUID: 8ab33cfb-bb00-11e6-84cd-fa163eb352dd Master_Info_File: /var/lib/mysql/master.info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: System lock Master_Retry_Count: 86400 Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: 8ab33cfb-bb00-11e6-84cd-fa163eb352dd:62276836-70424802 Executed_Gtid_Set: 8ab33cfb-bb00-11e6-84cd-fa163eb352dd:1-67413143 Auto_Position: 1 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version:
我為我的情況找到了解決方案
首先,我搜尋了哪些數據庫和表生成了這麼大的二進制日誌(哪些表沒有主鍵或唯一鍵):
SELECT t.table_schema,t.table_name,engine FROM information_schema.tables t INNER JOIN information_schema .columns c on t.table_schema=c.table_schema and t.table_name=c.table_name and t.table_schema not in ('performance_schema','information_schema','mysql') GROUP BY t.table_schema,t.table_name HAVING sum(if(column_key in ('PRI','UNI'), 1,0)) =0;
然後:
- 我的網速已經1Gbit/s,一切正常。
- 我從每天重新創建的複制和日誌記錄數據庫中排除。
- 我將從屬 RAM 增加到 23Gb(與主控一樣)
- 我將奴隸上的二進制日誌從 SSD 移動到 HDD - 沒關係 - HDD 的速度就足夠了。
- 我設置 log_slave_updates = 1,因為我的複制方案是 master>slave>slave。
這個步驟解決了我的問題!目前我的二進制日誌不會增加超過 10Gb。
初級會擁有 實用
binlog_ignore = check_passport
嗎?如果是這樣,並且如果該數據庫有很多流量,那麼這將“大量”縮小二進制日誌。大
DELETEs
或UPDATEs
占用大量 binlog 空間,從而使 binlog 變得巨大。(例如:更新一百萬行表的每一行。)提供細節;這可能是重要的解決方法。Replica 上執行了多少個副本執行緒?這影響
Seconds_behind_master
。(更多執行緒,達到一定限度,可能會“落後”減少。)
innodb_flush_log_at_trx_commit = 1
–> 更改為 2。這犧牲了一些強韌性(在崩潰時),但提高了吞吐量。每個有多少記憶體?我在 Primary 中看到了一個更大的 buffer_pool。 通常副本應該是更強大的機器。
100Mbs 似乎很低;你能檢查一下它是否飽和。
每日新鮮數據
這是替換錶中所有數據的有效方法:
CREATE TABLE new LIKE real;
LOAD DATA INFILE INTO new ...
——或者無論如何- `RENAME TABLE real to old, new to REAL;
DROP TABLE old;
第 2 步是最慢的部分。
步驟#3 非常快;這是唯一一次您的桌子不可用。
沒有二進制日誌密集型
UPDATE
或DELETE
.