Mysql数据库主从复制实操

第一步:在两个服务器拉取docker 容器

docker pull mysql:8.0.34

第二步：/opt/mysql/下新建conf、logs、data目录

第三步：配置在conf目录下my.cnf并编辑内容

（主节点my.cnf）新增同步的数据库不需要重启,binlog-do-db设置需要同步的数据库可设置多个

[client]
default-character-set=utf8mb4
[mysql]
default-character-set=utf8mb4
[mysqld]
gtid_mode=ON
enforce-gtid-consistency=TRUE
init_connect='SET collation_connection = utf8mb4_general_ci'
init_connect='SET NAMES utf8mb4'
character-set-server=utf8mb4
collation-server=utf8mb4_general_ci
skip-character-set-client-handshake
datadir=/var/lib/mysql
server-id=1
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
binlog-ignore-db=performance_schema
binlog-do-db=your_db1 # 设置需要同步的数据库
binlog-do-db=your_db2 # 设置需要同步的数据库
log-bin=/var/lib/mysql/mysql-bin
read-only=0
binlog_format=mixed
slave_skip_errors=1062
expire_logs_days=7
max_binlog_size=200M
sync_binlog=1
default_authentication_plugin=mysql_native_password

配置(从节点my.cnf)

[client]
default-character-set=utf8mb4
[mysql]
default-character-set=utf8mb4
[mysqld]
gtid_mode=ON
enforce-gtid-consistency=ON
init_connect='SET collation_connection = utf8mb4_general_ci'
init_connect='SET NAMES utf8mb4'
character-set-server=utf8mb4
collation-server=utf8mb4_general_ci
skip-character-set-client-handshake
datadir=/var/lib/mysql
server-id = 2
relay-log=mysql-relay
read-only=1
log-bin=mysql-slave-bin
default_authentication_plugin=mysql_native_password
slave-skip-errors = all  #设置跳过复制过程出现的错误，因为出错会停止复制流程
read_only=1   #设置从库只读
super_read_only=1 #设置所有用户只读

第四步：启动容器

docker run -p 3306:3306 --name mysql --privileged=true \
-v /opt/mysql/conf:/etc/mysql/conf.d \
-v /opt/mysql/logs:/var/log/mysql \
-v /opt/mysql/data:/var/lib/mysql \
-v /etc/localtime:/etc/localtime \
-e MYSQL_ROOT_PASSWORD=root \
-d mysql:8.0.34 \
--init-connect="SET collation_connection=utf8mb4_0900_ai_ci" \
--init-connect="SET NAMES utf8mb4" \
--skip-character-set-client-handshake

第五步：主库创建用于仅主从复制的用户，跟从库创建的保持一致

#进入容器，切换到mysql下创建用户

mysql -u root -p

use mysql; 

create user 'slave'@'%' identified by '123456';
grant replication slave ON *.* TO 'slave'@'%';
alter user 'slave'@'%' identified with mysql_native_password by '123456';

#查看binlog信息

show master status;

第六步：导入所有的数据库和数据

第七步：配置从库

从库不需要创建数据库，主库先导入所有的数据库和数据之后，从库直接执行下面的同步就可以备份所有的数据库和数据。

#进入容器，创建用于复制的从连接用户，#切换到mysql下创建用户

use mysql; 

CREATE USER 'slave'@'%' IDENTIFIED BY '123456';
GRANT ALL ON *.* TO 'slave'@'%' WITH GRANT OPTION;
FLUSH PRIVILEGES;

stop slave;
reset slave; # 清空同步设置


# 设置同步 # get_master_public_key=1解决8.0版本的安全校验问题


CHANGE MASTER TO 
  MASTER_HOST='192.168.0.126',
  MASTER_PORT=3306,
  MASTER_USER='slave',
  MASTER_PASSWORD='123456',
 MASTER_AUTO_POSITION=1,
  MASTER_CONNECT_RETRY=30,
  get_master_public_key=1;

START SLAVE;
SHOW SLAVE STATUS;  # 在可视化工具执行
SHOW SLAVE STATUS\G  # 容器内部执行

后期维护

检查从库复制流程是否连接成功，以下命令都可以查看运行状态。

SHOW SLAVE STATUS; #在可视化工具执行

SHOW SLAVE STATUS\G #容器内部执行

关注Slave_IO_Running和Slave_SQL_Running的值必须都是Yes才是正常运行。

如果有问题可以SHOW SLAVE STATUS\G #容器内部执行，查看报错原因。

也可以执行查看报错SELECT * FROM performance_schema.replication_applier_status_by_worker;