数据库集群系列（十五）使用docker-compose构建Mysql 5.7数据库主从复制、解决UUID重复导致复制失败问题

一、基本情况

    项目服务器，因经常频繁断电，出现过核心数据丢失的情况，现需要重构部署，考虑采用docker-compose容器集群方式实现。

    俩台主机：

    192.168.1.137【主数据库】、my.cnf开启binlog日志

    192.168.1.138【从数据库】、my.cnf未开启binlog日志

二、Master部署

1、创建挂载目录

    mkdir -p mysql/master/{mydir,datadir,conf,source}

2、配置yaml文件

    vim docker-compose.yaml

version: '3'
services:
mysql:
restart: always
image: mysql:5.7.34
container_name: mysql-5.7master
volumes:
- mysql/mydir:/mydir
- mysql/master/datadir:/var/lib/mysql
- mysql/master/conf/my.cnf:/etc/my.cnf
environment:
- "MYSQL_ROOT_PASSWORD=123456"
- "TZ=Asia/Shanghai"
ports:
- 3308:3306



3、配置my.cnf文件

    vim conf/my.cnf 

[mysqld]
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




log-bin=mysql-bin #开启日志
server-id=137 #唯一编号
expire_logs_days=15 #日志保存天数




###############################
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
[client]
default-character-set=utf8
[mysql]
default-character-set=utf8



4、启动master服务

docker-compose up -d
docker-compose down

5、连接并创建测试账号

    # mysql -uroot -p123456 -P3308 -h127.0.0.1

-- 创建用户
CREATE USER 'sync'@'%' IDENTIFIED BY '123456';


-- 赋予复制数据权限
grant replication slave on *.* to 'sync'@'%' IDENTIFIED BY '123456';


-- 刷新授权，使之立即生效
flush privileges;

6、查看Master相关信息

    show master status;

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000004 | 154 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)



三、Slave部署

1、创建挂载目录

    mkdir -p mysql/slave/{mydir,datadir,conf,source}

2、配置yaml文件

    vim docker-compose.yaml

version: '3'
services:
mysql:
restart: always
image: mysql:5.7.34
container_name: mysql-5.7slave
volumes:
- mysql/slave/datadir:/var/lib/mysql
- mysql/slave/conf/my.cnf:/etc/my.cnf
environment:
- "MYSQL_ROOT_PASSWORD=123456"
- "TZ=Asia/Shanghai"
ports:
- 3308:3306



3、修改my.cnf配置文件

[mysqld]
server-id=138
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
[client]
default-character-set=utf8
[mysql]
default-character-set=utf8



4、启动slave服务

docker-compose up -d
docker-compose down

5、连接并配置连接到Master

    mysql -uroot -P3308 -p123456 -h127.0.0.1

stop slave;
reset master;
change master to
master_host='192.168.1.137',
master_port=3308,
master_user='sync',
master_password='123456',
master_log_file='mysql-bin.000002',
master_log_pos=4162,
MASTER_AUTO_POSITION=0;

    start slave;

6、查看开启slave是否成功

    show slave status;

7、报错处理The slave I/O thread stops

    查看日志信息：docker logs -f mysql-5.7master

mysql-5.7slave | 2024-01-03T17:05:52.332922+08:00 8 [ERROR] Slave I/O for channel '':
Fatal error:
The slave I/O thread stops because master and slave have equal MySQL server UUIDs;
these UUIDs must be different for replication to work. Error_code: 1593
mysql-5.7slave | 2024-01-03T17:05:52.332951+08:00 8
[Note] Slave I/O thread exiting for channel '', read up to log 'mysql-bin.000002', position 154

解决：

    先查看数据文件路径：SHOW VARIABLES LIKE 'datadir';

   删除 auto.cnf文件

    重新执行docker-compose up 启动即可

三、主从模式验证

    在master数据库创建一个test库，并导入sql；可以发现slave库也同步导入。

证明主从模式配置无问题，一切使用正常。