目录
概述主从介绍
主从作用
主从作用有：
主从形式有：
配置步骤
主要配置
1>创建三个进程
2>修改配置文件
3>主机配置
4>从机配置
5>将文件修改后，复制到容器里面
6>进入主机进行配置
6.1>创建用户
6.2>给用户授权
6.3>刷新权限
7>进入从机进行配置
对M1S1
对M2S2
8>最后开启主从
9>测试
概述主从介绍
MySQL主从又叫Replication、AB复制。简单讲就是A与B两台机器做主从后，在A上写数据，另外一台B 也会跟着写数据，实现数据实时同步。有这样几个关键点：
1）MySQL主从是基于binlog，主上需开启binlog才能进行主从；
2）主从过程大概有3个步骤；
3）主将更改操作记录到binlog里；
4）从将主的binlog事件（SQL语句） 同步本机上并记录在relaylog里；
5）从根据relaylog里面的SQL语句按顺序执行。
主从作用
主从作用有：
实时灾备，用于故障切换；读写分离，提供查询服务；备份，避免影响业务。
主从形式有：
而我们今天的配置是一主两从
配置步骤
1）确保从数据库与主数据库里的数据一致
2）在主数据库里创建一个同步账户授权给从数据库使用
3）配置主数据库（修改配置文件）
4）配置从数据库（修改配置文件）
5）需求
6）搭建两台MySQL服务器，一台作为主服务器，一台作为从服务器，主服务器进行写操作，从服务器 进行读操作
环境说明（使用docker通过不通端口启用多台mysql进程）
| 名称 | IP | port |
| M1 | 192.168.38.132 | 3307 |
| M1S1 | 192.168.38.132 | 3308 |
| M1S2 | 192.168.38.132 | 3309 |
主要配置
1>创建三个进程
docker run --name M1 -p 3307:3306 -e MYSQL_ROOT_PASSWORD=123456 -d mysql:5.7 --
lower_case_table_names=1
docker run --name M1S1 -p 3308:3306 -e MYSQL_ROOT_PASSWORD=123456 -d mysql:5.7 -
-lower_case_table_names=1
docker run --name M1S2 -p 3308:3306 -e MYSQL_ROOT_PASSWORD=123456 -d mysql:5.7 -
-lower_case_table_names=1
2>修改配置文件
将容器里面的配置文件复制出来，主要修改服务器的配置；在root目录下创建一个mysqlms的目录存放 从Docker容器里面复制过来的配置文件。进入目录：cd /mysqlms
mkdir /mysqlms
cd /mysqlms
docker cp M1:/etc/mysql/conf.d/docker.cnf m1.cnf
docker cp M1S1:/etc/mysql/conf.d/docker.cnf m1s1.cnf
docker cp M1S2:/etc/mysql/conf.d/docker.cnf m1s2.cnf3>主机配置
[root@localhost mysqlms]# vim m1.cnf
[mysqld]
skip-host-cache
skip-name-resolve
server-id=1
log-bin=master.bin
4>从机配置
[root@localhost mysqlms]# vim m1s1.cnf
[mysqld]
skip-host-cache
skip-name-resolve
server-id=3
[root@localhost mysqlms]# vim m1s2.cnf
[mysqld]
skip-host-cache
skip-name-resolve
server-id=2
然后记着重启三个进程
docker restart M1 M1S1 M1S2
5>将文件修改后，复制到容器里面
docker cp m1.cnf M1:/etc/mysql/conf.d/docker.cnf
docker cp m1s1.cnf M1S1:/etc/mysql/conf.d/docker.cnf
docker cp m1s2.cnf M1S2:/etc/mysql/conf.d/docker.cnf6>进入主机进行配置
[root@localhost mysqlms]# docker exec -it M1 bash
root@40b67125e6ce:/#
root@40b67125e6ce:/# mysql -uroot -p123456
6.1>创建用户
mysql> create user 'rep'@'%' identified by '123456';
Query OK, 0 rows affected (0.01 sec)
6.2>给用户授权
mysql> grant replication slave on *.* to 'rep'@'%';
Query OK, 0 rows affected (0.01 sec)6.3>刷新权限
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
此时我们使用M1里面的rep用户登录
root@40b67125e6ce:/# mysql -urep -p123456
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.36 MySQL Community Server (GPL)
Copyright (c) 2000, 2021, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>7>进入从机进行配置
对M1S1
[root@localhost mysqlms]# docker exec -it M1 bash
root@40b67125e6ce:/# mysql -uroot -p123456
mysql> change master to
-> master_host="192.168.38.133",master_port=3307,
-> master_user="rep",
-> master_password="123456",
-> master_log_file="master.000001",
-> master_log_pos=154;
Query OK, 0 rows affected, 2 warnings (0.03 sec)对M2S2
[root@localhost ~]# docker exec -it M1S2 bash
root@401c6f11cfc8:/# mysql -uroot -p123456
mysql> change master to
-> master_host="192.168.38.133",master_port=3307,
-> master_user="rep",
-> master_password="123456",
-> master_log_file="master.000001",
-> master_log_pos=154;
Query OK, 0 rows affected, 2 warnings (0.01 sec)
其中，master_log_file：该文件具体叫什么名称，需要从主机里面去看看。进入M1 里面使用root 用户 登录M1，执行下面的SQL：show master status;
8>最后开启主从
在M1S1和M2S2中都开启
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)最后查看主从状态
M1S1
mysql> show slave status \G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.38.133
Master_User: rep
Master_Port: 3307
Connect_Retry: 60
Master_Log_File: master.000001
Read_Master_Log_Pos: 154
Relay_Log_File: ffe391517b79-relay-bin.000002
Relay_Log_Pos: 317
Relay_Master_Log_File: master.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 154
Relay_Log_Space: 531
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_UUID: 9c13ed93-2971-11ee-8e34-0242ac110003
Master_Info_File: /var/lib/mysql/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
M1S2
mysql> show slave status \G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.38.133
Master_User: rep
Master_Port: 3307
Connect_Retry: 60
Master_Log_File: master.000001
Read_Master_Log_Pos: 154
Relay_Log_File: 401c6f11cfc8-relay-bin.000002
Relay_Log_Pos: 317
Relay_Master_Log_File: master.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 154
Relay_Log_Space: 531
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_UUID: 9c13ed93-2971-11ee-8e34-0242ac110003
Master_Info_File: /var/lib/mysql/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
9>测试
在M1 里面创建数据库，看M1S1 ,M1S2有没有复制过去
此时我们可以看到有了所以成功文章来源：https://www.toymoban.com/news/detail-600662.html
文章来源地址https://www.toymoban.com/news/detail-600662.html
到了这里，关于使用docker进行MYSQL主从复制（一主两从）的文章就介绍完了。如果您还想了解更多内容，请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章，希望大家以后多多支持TOY模板网！
