环境: | ||
---|---|---|
xxx.xxx.xxx.85 | Replication-Manager 2.1 | 控制台 |
xxx.xxx.xxx.86 | Mysql 5.7.42 | 主库 |
xxx.xxx.xxx.87 | Mysql 5.7.42 | 从库 |
xxx.xxx.xxx.88 | Mysql 5.7.42 | 从库 |
xxx.xxx.xxx.89 | VIP |
安装步骤:
参考:https://www.jianshu.com/p/1dfd296d0a38 做的
数据库配置的是一主两从,用的mysql自带的master和slave;binlog复制同步
主库my.cnf配置如下:
[client]
port = 3306
socket = /tmp/mysql.sock
[mysqld]
port = 3306
user = mysql
server_id = 86 #修改成唯一的编号(主机地址的后四位)
socket = /tmp/mysql.sock
character_set_server = utf8
basedir = /data/mysql/app/mysql/mysql
datadir = /data/mysql/data/3306/data
pid-file = /data/mysql/data/3306/data/mysql.pid
lower_case_table_names = 1
replicate-ignore-db=mysql
max_connections = 3000
max_connect_errors = 6000
wait_timeout=31536000
connect_timeout=31536000
interactive_timeout=31536000
skip-name-resolve
sql_mode= "STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER"
plugin-dir=/data/mysql/app/mysql-5.7.42-linux-glibc2.12-x86_64/lib/plugin
log_error = /data/mysql/log/3306/mysql-error.log
log_bin = /data/mysql/log/3306/binlog/db-binlog
binlog_format = MIXED
expire_logs_days = 10
relay_log = /data/mysql/log/3306/relay-bin
relay_log_recovery =1
slow_query_log = 1
long_query_time = 1
slow_query_log_file = /data/mysql/log/3306/mysql-slow.log
log-slave-updates=true
gtid-mode=on
enforce-gtid-consistency=true
sync_binlog = 1
innodb_flush_log_at_trx_commit = 1
default-storage-engine = InnoDB
innodb_file_per_table = 1
innodb_open_files = 500
innodb_buffer_pool_size = 20000M
innodb_log_file_size = 100M
innodb_write_io_threads = 4
innodb_read_io_threads = 4
innodb_thread_concurrency = 0
innodb_purge_threads = 1
innodb_log_buffer_size = 2M
innodb_log_files_in_group = 3
innodb_max_dirty_pages_pct = 90
innodb_lock_wait_timeout = 120
rpl-semi-sync-master-enabled = 1
rpl-semi-sync-slave-enabled = 1
rpl_semi_sync_master_enabled = ON
rpl_semi_sync_master_timeout = 100000
rpl_semi_sync_master_wait_no_slave = ON
rpl_semi_sync_master_trace_level=32
log_bin_trust_function_creators=1
max_allowed_packet = 36M
group_concat_max_len=102400
tmp_table_size=200M
从库my.cnf配置如下:
[client]
port = 3306
socket = /tmp/mysql.sock
[mysqld]
port = 3306
user = mysql
server_id = 87 #修改成唯一的编号(主机地址的后四位)
socket = /tmp/mysql.sock
character_set_server = utf8
basedir = /data/mysql/app/mysql/mysql
datadir = /data/mysql/data/3306/data
pid-file = /data/mysql/data/3306/data/mysql.pid
lower_case_table_names = 1
replicate-ignore-db=mysql
max_connections = 3000
max_connect_errors = 6000
wait_timeout=31536000
interactive_timeout=31536000
skip-name-resolve
sql_mode= "STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER"
plugin-dir=/data/mysql/app/mysql-5.7.42-linux-glibc2.12-x86_64/lib/plugin
log_error = /data/mysql/log/3306/mysql-error.log
log_bin = /data/mysql/log/3306/binlog/db-binlog
binlog_format = MIXED
expire_logs_days = 10
relay_log = /data/mysql/log/3306/relay-bin
relay_log_recovery = 1
slow_query_log = 1
long_query_time = 1
slow_query_log_file = /data/mysql/log/3306/mysql-slow.log
log-slave-updates=true
gtid-mode=on
enforce-gtid-consistency=true
sync_binlog = 1
innodb_flush_log_at_trx_commit = 1
default-storage-engine = InnoDB
innodb_file_per_table = 1
innodb_open_files = 500
innodb_buffer_pool_size = 20000M
innodb_log_file_size = 100M
innodb_write_io_threads = 4
innodb_read_io_threads = 4
innodb_thread_concurrency = 0
innodb_purge_threads = 1
innodb_log_buffer_size = 2M
innodb_log_files_in_group = 3
innodb_max_dirty_pages_pct = 90
innodb_lock_wait_timeout = 120
rpl_semi_sync_slave_enabled=ON
rpl_semi_sync_slave_trace_level=32
log_bin_trust_function_creators=1
max_allowed_packet = 36M
group_concat_max_len=102400
tmp_table_size=200M
目前是搭建的测试环境,建了一张测试表
用的是增强半同步模式,切换前也有单独确认了测试表的数据有落到从库,但是当HA后发现从库测试表的部分数据丢失了
主库数据: | |
---|---|
id | name |
1 | 1 |
2 | 测试1 |
3 | 测试2 |
4 | 测试3 |
5 | 测试4 |
从库数据: | |
---|---|
id | name |
1 | 1 |
2 | 测试1 |
3 | 测试2 |
请教下各位大佬,是否该架构本身的缺陷还是我哪里没有配置好?
根据您提供的信息,可能是以下原因导致从库测试表的部分数据丢失:
建议您可以通过以下方式来解决问题: