저는 2개의 MySQL 서버를 가지고 있습니다:
마스터 서버: mysql 버전 5.7.14
슬레이브 서버: Docker 컨테이너의 mysql 버전 5.7.14(공식 도커 허브에서).
GTID 복제.
두 가지 문제가 있습니다.
- Binlog는 매우 빠르게 성장합니다. 2일 제한 회전을 설정했지만 이는 도움이 되지 않습니다. 매일 binlog 폴더가 최소한 두 번 증가합니다(첫 번째 날 25Gb, 두 번째 50, 세 번째 80 등).
- 슬레이브 서버 "마스터보다 몇 초 뒤처짐"이 증가합니다.
로컬 네트워크(100mbit/s)의 서버, SSD 디스크, 40Gb에 가까운 데이터베이스 크기.
Percona Xtrabackup을 사용하여 슬레이브용 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
출력슬레이브 상태 표시\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:
답변1
초등회가 갖는 것이 실용적인가요 binlog_ignore = check_passport? 그렇다면 해당 DB에 대한 트래픽이 많으면 binlog가 "많이" 줄어들 것입니다.
크 DELETEs거나 UPDATEs많은 binlog 공간을 차지하므로 binlog가 거대해집니다. (예: 백만 행 테이블의 모든 행을 업데이트합니다.) 구체적인 내용을 제공하세요. 이는 중요한 해결 방법이 될 수 있습니다.
복제본에서 몇 개의 복제본 스레드가 실행되고 있습니까? 이는 영향을 미칩니다 Seconds_behind_master. (한도 내에서 더 많은 스레드가 "뒤"로 줄어들 가능성이 높습니다.)
innodb_flush_log_at_trx_commit = 1--> 2로 변경합니다. 이렇게 하면 (충돌 시) 일부 견고성이 희생되지만 처리량이 향상됩니다.
각각의 RAM은 얼마나 됩니까? 기본에 더 큰 buffer_pool이 있습니다. 대개복제본은 더 강력한 시스템이어야 합니다.
100Mbs는 낮은 것 같습니다. 포화 상태인지 확인할 수 있나요?
매일 새로운 데이터
효율적인 교체 방법은 다음과 같습니다.모두테이블의 데이터:
CREATE TABLE new LIKE real;LOAD DATA INFILE INTO new ...-- 아니면 무슨 일이 있어도- `RENAME TABLE 실제를 이전 항목으로, 새 항목을 REAL로 변경;
DROP TABLE old;
2단계는 가장 느린 부분입니다.
3단계는 매우 빠릅니다. 테이블을 사용할 수 없는 유일한 시간입니다.
binlog 집약적 UPDATE이거나 DELETE.
답변2
내 사건에 대한 해결책을 찾았습니다
우선, 너무 큰 binlog를 생성하는 데이터베이스와 테이블(기본 키나 고유 키가 없는 테이블)을 검색합니다.
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로 늘립니다(마스터에서와 마찬가지로).
- 슬레이브의 binlog를 SSD에서 HDD로 옮깁니다. 괜찮습니다. HDD의 속도는 충분합니다.
- 내 복제 구성표가 마스터>슬레이브>슬레이브이기 때문에 log_slave_updates = 1로 설정했습니다.
그리고 이 단계는 내 문제를 해결합니다! 현재 내 binlog는 10Gb 이상 증가하지 않습니다.