数据库集群系列（十二）：基于Docker容器实现MySQL 5.7版本数据库的Master-Master双主复制

巴韭特锁螺丝 2024-07-18

122

一、基本情况

在此前已经通过传统方式实现MySQL的主从复制，先通过容器方式实现。

Master-1:192.168.1.137

Master-2: 192.168.1.138

docker容器的安装：略。

二、安装及配置Master-1

1、创建挂载目录

mkdir -p usr/local/mysql-master/log
mkdir -p usr/local/mysql-master/data
mkdir -p /usr/local/mysql-master/conf

2、拉取镜像

docker pull mysql:5.7.34

3、修改mysql配置文件

vim /usr/local/mysql-master/conf/my.cnf

[mysqld]
server_id = 137
log-bin=mysql-bin
port = 3306
auto_increment_increment=2
auto_increment_offset=1
gtid_mode = on
enforce-gtid-consistency=true




character-set-server = utf8
collation-server = utf8_general_ci
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ZERO_DATE,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO
lower_case_table_names = 1
transaction-isolation = READ-COMMITTED
default-time_zone = '+8:00'
log_timestamps = SYSTEM




###############################
innodb_buffer_pool_size = 2G
innodb_log_file_size=128M
innodb_log_files_in_group=4
innodb_log_buffer_size=16M
innodb_write_io_threads = 8
innodb_read_io_threads = 8
innodb_max_dirty_pages_pct = 90
innodb_lock_wait_timeout = 120
innodb-file-per-table=1 
auto_increment_increment=1
auto_increment_offset=1
connect_timeout=10
group_concat_max_len=1024
innodb_thread_concurrency=0
innodb_thread_sleep_delay=10000
innodb_write_io_threads=12
interactive_timeout=28800
lock_wait_timeout=31536000
long_query_time=10.000000
low_priority_updates=OFF
max_allowed_packet=500M
max_connect_errors=999999999
max_connections=1600
max_length_for_sort_data=1024
max_prepared_stmt_count=16382
max_user_connections=0
net_read_timeout=30
net_retry_count=10
net_write_timeout=60
ngram_token_size=2
open_files_limit=102400
performance_schema=OFF
query_alloc_block_size=8192
query_cache_limit=1048576
query_cache_size=0
query_cache_type=OFF
query_cache_wlock_invalidate=OFF
query_prealloc_size=8192
slow_launch_time=2
table_definition_cache=768
table_open_cache=512
table_open_cache_instances=16
thread_cache_size=512
tmp_table_size=1073741824
wait_timeout=2147483
interactive_timeout=31536000
explicit_defaults_for_timestamp = true
log-bin-trust-function-creators = 1

4、重启并创建复制账号

# mysql -uroot -p123456 -P3307 -h192.168.1.137


mysql> CREATE USER 'sync'@'%' IDENTIFIED BY '123456';
Query OK, 0 rows affected (0.00 sec)


mysql> GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'sync'@'%';
Query OK, 0 rows affected (0.00 sec)

三、安装及配置Master-2

1、创建挂载目录及拉取镜像：略。

2、修改mysql配置文件

[mysqld]
server_id = 138
log-bin=mysql-bin
auto_increment_increment=2
# 生成主键从2开始
auto_increment_offset=2
gtid_mode = on
enforce-gtid-consistency=true




character-set-server = utf8
collation-server = utf8_general_ci
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ZERO_DATE,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO
lower_case_table_names = 1
transaction-isolation = READ-COMMITTED
default-time_zone = '+8:00'
log_timestamps = SYSTEM




###############################
innodb_buffer_pool_size = 2G
innodb_log_file_size=128M
innodb_log_files_in_group=4
innodb_log_buffer_size=16M
innodb_write_io_threads = 8
innodb_read_io_threads = 8
innodb_max_dirty_pages_pct = 90
innodb_lock_wait_timeout = 120
innodb-file-per-table=1 
auto_increment_increment=1
auto_increment_offset=1
connect_timeout=10
group_concat_max_len=1024
innodb_thread_concurrency=0
innodb_thread_sleep_delay=10000
innodb_write_io_threads=12
interactive_timeout=28800
lock_wait_timeout=31536000
long_query_time=10.000000
low_priority_updates=OFF
max_allowed_packet=500M
max_connect_errors=999999999
max_connections=1600
max_length_for_sort_data=1024
max_prepared_stmt_count=16382
max_user_connections=0
net_read_timeout=30
net_retry_count=10
net_write_timeout=60
ngram_token_size=2
open_files_limit=102400
performance_schema=OFF
query_alloc_block_size=8192
query_cache_limit=1048576
query_cache_size=0
query_cache_type=OFF
query_cache_wlock_invalidate=OFF
query_prealloc_size=8192
slow_launch_time=2
table_definition_cache=768
table_open_cache=512
table_open_cache_instances=16
thread_cache_size=512
tmp_table_size=1073741824
wait_timeout=2147483
interactive_timeout=31536000
explicit_defaults_for_timestamp = true
log-bin-trust-function-creators = 1

3、重启并创建复制账号

# mysql -uroot -p123456 -P3307 -h192.168.1.138


mysql> CREATE USER 'sync'@'%' IDENTIFIED BY '123456';
Query OK, 0 rows affected (0.00 sec)


mysql> GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'sync'@'%';
Query OK, 0 rows affected (0.00 sec)

四、Master互相建立复制关系

俩台数据库配置一样

修改对应命令中的IP地址和数据库端口号即可。

flush logs;
reset master;
stop slave;
 change master to
    ->     master_host='192.168.1.137',
    ->     master_port=3307,
    ->     master_user='sync',
    ->     master_password='123456',
    ->     master_auto_position = 1;
Query OK, 0 rows affected, 1 warning (0.01 sec)
start slave;
show slave status \G

五、双Master复制验证与测试

在Master-1上新建数据库为solar的库，并导入sql；

同时观察到Master-2上数据可以实时进行同步。

同理，在Master-2上执行增加、删除、修改，验证效果一样。

mysql主从配置 mysql创建数据库数据库集群 mysql docker

文章转载自巴韭特锁螺丝，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

数据库集群系列（十二）：基于Docker容器实现MySQL 5.7版本数据库的Master-Master双主复制

评论