使用Docker模拟实现两台MySQL主从同步复制

版本：MySQL8.0.30

一、准备两台MySQL

1、启动两台MySQL 服务器

# mysql-master
docker run \
--privileged \
-itd \
--name mysql-master \
-v /sys/fs/cgroup:/sys/fs/cgroup:ro \
centos:centos7 /usr/sbin/init

# mysql-slave
docker run \
--privileged \
-itd \
--name mysql-slave \
-v /sys/fs/cgroup:/sys/fs/cgroup:ro \
centos:centos7 /usr/sbin/init

查看两台机器的ip地址

$ docker inspect --format='{
   {.Name}} - {
   {range .NetworkSettings.Networks}}{
   {.IPAddress}}{
   {end}}' \
> $(docker ps -aq)
/mysql-slave - 172.17.0.3
/mysql-master - 172.17.0.2

2、下载MySQL

# 下载 mysql-8.0
wget https://downloads.mysql.com/archives/get/p/23/file/mysql-8.0.30-1.el7.x86_64.rpm-bundle.tar

3、上传解压

mysql-master

# 拷贝到服务器
docker cp mysql-8.0.30-1.el7.x86_64.rpm-bundle.tar mysql-master:/opt

# 进入服务器 
docker exec -it mysql-master /bin/bash

mysql-slave

docker cp mysql-8.0.30-1.el7.x86_64.rpm-bundle.tar mysql-slave:/opt

# 进入服务器
docker exec -it mysql-slave /bin/bash

解压安装(mysql-master + mysql-slave)

## 解压 + 安装
mkdir /opt/mysql-8.0.30 && \
tar -xvf /opt/mysql-8.0.30-1.el7.x86_64.rpm-bundle.tar -C /opt/mysql-8.0.30 && \
cd /opt/mysql-8.0.30 && \
yum install -y mysql-community-{
   server,client,common,libs,icu-data-files}-*

4、配置MySQL

mysql-master

/etc/my.cnf

[mysqld]
log-bin=mysql-bin
server-id=100
default-authentication-plugin=mysql_native_password

mysql-slave

/etc/my.cnf

[mysqld]
server-id=101
default-authentication-plugin=mysql_native_password

参数说明

• log-bin: 启动二进制日志
• server-id: 服务器唯一id
• default-authentication-plugin: 修改默认的认证方式

5、启动服务器

为了方便测试，两台服务器都重置一下密码

# 启动MySQL
systemctl start mysqld

# 查看初始密码
grep 'temporary password' /var/log/mysqld.log

# 登录
mysql -uroot -p

# 重置密码
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'MyNewPass4!';

# 修改密码复杂度限制参数（为了测试简单，修改为简单策略，正式环境不建议修改）
mysql> set global validate_password.policy = LOW;
mysql> set global validate_password.length = 6;

# 修改root密码
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY '123456';

 

  
 

mysql-master

# 创建用户 + 授权，注意:mysql8.0需要分开执行
mysql> CREATE USER 'slave01'@'%' IDENTIFIED BY '123456';
mysql> GRANT REPLICATION SLAVE ON *.* TO 'slave01'@'%';
mysql> FLUSH PRIVILEGES;

# 显示master的同步点
show master status \G
*************************** 1. row ***************************
File: mysql-bin.000002
Position: 1413

mysql-slave

# 修改为master节点的同步点
mysql> change master to 
master_host='172.17.0.2', 
master_user='slave01', 
master_password='123456', 
master_log_file='mysql-bin.000002', 
master_log_pos=1413;

# 启动
mysql> start slave;

# 显示slave状态
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_Running: Yes
Slave_SQL_Running: Yes

 

  
 

二、主从同步复制测试

mysql-master

-- 创建数据库
create database db_data;
use db_data;

-- 创建数据表
CREATE TABLE `tb_user` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
);

insert into tb_user (name) values ('Tom');

insert into tb_user (name) values ('Jack');

insert into tb_user (name) values ('Steve');

 

  
 

mysql-slave

show databases;
+--------------------+
| Database           |
+--------------------+
| db_data            |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+

use db_data;

show tables;
+-------------------+
| Tables_in_db_data |
+-------------------+
| tb_user           |
+-------------------+

select * from tb_user;
+----+-------+
| id | name  |
+----+-------+
|  1 | Tom   |
|  2 | Jack  |
|  3 | Steve |
+----+-------+

 

  
 

可以看到，主库写入的数据都自动同步到了从库

参考
MySQL8报错：授权语句中的（IDENTIFIED BY）报语法错误
解决Worker 1 failed executing transaction ‘ANONYMOUS‘ at master log mall-mysql-bin.000001, end_log_pos

转载：https://blog.csdn.net/mouday/article/details/128923517