架构说明:
服务器Ip | 状态 |
192.168.161.20 | master |
192.168.161.21 | slave |
MySQL版本:5.7.35
操作系统: centos7.9
一、master部署
1) master配置文件
说明:配置文件讲挂载进容器
[mysqld]
binlog_format = row
innodb_buffer_pool_size = 4G
enforce_gtid_consistency = on
server_id = 675
expire_logs_days = 7
slow_query_log = on
open_files_limit = 65536
log-bin = /var/lib/mysql/mysql-bin
long_query_time = 3
skip_name_resolve = true
log_output = file
gtid_mode = on_permissive
port = 3306
slow_query_log_file = /var/lib/mysql/mysql-slow.log
max_connections = 6000
sql-mode = "ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
character-set-client-handshake = FALSE
character-set-server = "utf8mb4"
collation-server = "utf8mb4_unicode_ci"
[client]
default-character-set = "utf8mb4"[mysql]
deault-character-set = "utf8mb4"
注意:配置文件路径为: /home/mysql-master/conf/mysql.conf,与后面的docker容器挂载相匹配
2) 启动master容器
docker run -d -p 3306:3306 --privileged=true \
-v /home/mysql-master/log:/var/log/mysql \
-v /home/mysql-master/data:/var/lib/mysql \
-v /home/mysql-master/conf/mysql.conf:/etc/mysql/my.cnf -e MYSQL_ROOT_PASSWORD=lxm@123456 \
-v /usr/share/zoneinfo/Asia/Shanghai:/etc/localtime \
--name mysql-master mysql:5.7.35
3)创建同步用户
CREATE USER 'slave'@'%' IDENTIFIED BY '123456';
GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'slave'@'%';
flush privileges;
二、从服务器搭建
1) 从服务器配置文件
[mysqld]
log_queries_not_using_indexes = on
innodb_buffer_pool_size = 4G
enforce_gtid_consistency = on
server_id = 50
slow_query_log = on
open_files_limit = 65536
log-bin = /var/lib/mysql/mysql-bin
long_query_time = 3
skip_name_resolve = true
log_output = file
gtid_mode = on_permissive
port = 3306
slow_query_log_file = /var/lib/mysql/mysql-slow.log
max_connections = 6000
character-set-client-handshake = FALSE
character-set-server = "utf8mb4"
collation-server = "utf8mb4_unicode_ci"
sql-mode = "ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"[client]
default-character-set = "utf8mb4"[mysql]
default-character-set = "utf8mb4"
2) 启动从服务器
docker run -d -p 3306:3306 --privileged=true \
-v /home/mysql-slave/log:/var/log/mysql \
-v /home/mysql-slave/data:/var/lib/mysql \
-v /home/mysql-slave/conf/mysql.conf:/etc/mysql/my.cnf \
-v /usr/share/zoneinfo/Asia/Shanghai:/etc/localtime \
-e MYSQL_ROOT_PASSWORD=lxm@123456 \
--name mysql-slave mysql:5.7.35
3) 主从设置
change master to master_host='192.168.161.20',
master_user='slave',
master_password='123456',
master_port=3306,
master_log_file='mysql-bin.000002',
master_log_pos=154,
master_connect_retry=30;
参数说明:
master_log_file: 通过master服务器执行:show master status\G获取
master_log_pos:通过master服务器执行:show master status\G获取
4) 启动从服务器主从复制功能
####启动主从复制
start slave;
### 确认
show slave status \G
三、测试验证
master执行数据写入
#创建数据库
CREATE DATABASE lxm DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;# 创建测试表
create table lxm_user(id int(2)not null,sname varchar(10) not null ,identify_no varchar(20),location varchar(100),primary key(id,sname));
insert into lxm_user values(1,'lxm','43900030949940930490','wuhan');
insert into lxm_user values(2,'lqingqing','43905990409292893','huangshi');
insert into lxm_user values(3,'wfengqiang','199376778794','dawang');
commit;#从服务器验证
show databases;
select * from lxm_user;