目录
一、MySQL主从复制原理就不做讲解了，详情请查看MySQL专栏
二、主从复制搭建步骤
1、新建主服务器容器实例3307
2、进入/usr/mysql/mysql-master/conf目录下新建my.cnf
3、修改完配置后，重启master实例
4、进入mysql-master容器
5、在mysql-master容器中创建数据同步用户
6、新建从服务器容器实例3308
7、进去/usr/mysql/mysql-slave/conf目录下新建my.cnf
8、修改完配置后重启slave实例
9、在主数据库中查看主从同步状态
10、进入mysql-slave容器
11、在从数据库中配置主从复制
12、在从数据库中查看主从同步状态
13、在从数据库中开启主从同步
14、主从复制测试
一、MySQL主从复制原理就不做讲解了，详情请查看MySQL专栏
二、主从复制搭建步骤
1、新建主服务器容器实例3307
[root@localhost conf]# docker run -p 3307:3306
-v /usr/mysql/mysql-master/log:/var/log/mysql \
-v /usr/mysql/mysql-master/data:/var/lib/mysql \
-v /usr/mysql/mysql-master/conf:/etc/mysql \
-e MYSQL_ROOT_PASSWORD=123456 \
--name=mysql-master \
-d mysql:5.7
cb51f77c7b6294cb5f7b12624c2a1ac430bb406c75dd2d386d8c32a97b2a8eae
[root@localhost conf]#
[root@localhost conf]#
[root@localhost conf]# docker ps
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
cb51f77c7b62 mysql:5.7 "docker-entrypoint.s…" 5 seconds ago Up 4 seconds 33060/tcp, 0.0.0.0:3307->3306/tcp, :::3307->3306/tcp mysql-master
[root@localhost conf]#2、进入/usr/mysql/mysql-master/conf目录下新建my.cnf
[root@localhost conf]# pwd
/usr/mysql/mysql-master/conf
[root@localhost conf]# vim my.cnf
[client]
user=root
password=xxxxxx
default-character-set=utf8[mysql]
prompt=(\\u@\\h) [\\d]>\\_[mysqld]
server_id=1
log-bin=mysql-bin#设置二进制日志使用内存的大小
binlog_cache_size=1M
#二进制日志格式
binlog_format=mixed
#日志清理时间
expire_logs_days=7
collation_server = utf8_general_ci
character-set-server=utf8
slave_skip_errors=1062
3、修改完配置后，重启master实例
[root@localhost conf]# docker restart mysql-master
mysql-master
[root@localhost conf]#
[root@localhost conf]# docker ps
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
cb51f77c7b62 mysql:5.7 "docker-entrypoint.s…" 11 minutes ago Up 2 seconds 33060/tcp, 0.0.0.0:3307->3306/tcp, :::3307->3306/tcp mysql-master
[root@localhost conf]#4、进入mysql-master容器
[root@localhost conf]# docker exec -it mysql-master /bin/bash
root@cb51f77c7b62:/#
root@cb51f77c7b62:/# mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.36-log 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.(root@localhost) [(none)]>
(root@localhost) [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)(root@localhost) [(none)]> 5、在mysql-master容器中创建数据同步用户
(root@localhost) [(none)]> create user 'repl'@'%' identified by '123456';
Query OK, 0 rows affected (0.00 sec)(root@localhost) [(none)]> GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'repl'@'%';
Query OK, 0 rows affected (0.00 sec)(root@localhost) [(none)]> ALTER USER 'repl'@'%' IDENTIFIED WITH mysql_native_password BY '123456';
Query OK, 0 rows affected (0.00 sec)(root@localhost) [(none)]>6、新建从服务器容器实例3308
[root@localhost ~]# docker run -p 3308:3306
-v /usr/mysql/mysql-slave/conf:/etc/mysql
-v /usr/mysql/mysql-slave/data:/var/lib/mysql
-v/usr/mysql/mysql-slave/log:/var/log/mysql
-e MYSQL_ROOT_PASSWORD=123456
--name=mysql-slave
-d mysql:5.7
9a5bbcb88dedafc4b4485ef48e0df72641748ac95ff3af1b5a1cfd60d053a3f2
[root@localhost ~]#
[root@localhost ~]#
[root@localhost ~]# docker ps
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
9a5bbcb88ded mysql:5.7 "docker-entrypoint.s…" 4 seconds ago Up 3 seconds 33060/tcp, 0.0.0.0:3308->3306/tcp, :::3308->3306/tcp mysql-slave
cb51f77c7b62 mysql:5.7 "docker-entrypoint.s…" 48 minutes ago Up 37 minutes 33060/tcp, 0.0.0.0:3307->3306/tcp, :::3307->3306/tcp mysql-master
[root@localhost ~]# 7、进去/usr/mysql/mysql-slave/conf目录下新建my.cnf
把master的配置文件cp过来，修改下server-id 即可
[root@localhost conf]# cp /usr/mysql/mysql-master/conf/my.cnf ./
[root@localhost conf]#
[root@localhost conf]# ll
total 4
-rw-r--r--. 1 root root 347 Jun 25 14:49 my.cnf
[root@localhost conf]#
[root@localhost conf]#
[root@localhost conf]# vim my.cnf
[root@localhost conf]#8、修改完配置后重启slave实例
[root@localhost ~]# docker restart mysql-slave
mysql-slave
[root@localhost ~]#
[root@localhost ~]# docker ps
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
9a5bbcb88ded mysql:5.7 "docker-entrypoint.s…" 4 minutes ago Up 2 seconds 33060/tcp, 0.0.0.0:3308->3306/tcp, :::3308->3306/tcp mysql-slave
cb51f77c7b62 mysql:5.7 "docker-entrypoint.s…" 53 minutes ago Up 42 minutes 33060/tcp, 0.0.0.0:3307->3306/tcp, :::3307->3306/tcp mysql-master
[root@localhost ~]#9、在主数据库中查看主从同步状态
(root@localhost) [(none)]> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 | 851 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)(root@localhost) [(none)]>10、进入mysql-slave容器
[root@localhost ~]# docker exec -it mysql-slave /bin/bash
root@9a5bbcb88ded:/#
root@9a5bbcb88ded:/# mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.36-log 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.(root@localhost) [(none)]>
(root@localhost) [(none)]>11、在从数据库中配置主从复制
change master to
master_host='宿主机IP',
master_user='repl', #主数据库创建的用于同步数据的用户账号
master_password='123456',#主数据库创建的用于同步数据的用户密码
master_port=3307,#主数据库运行的端口
master_log_ffile='mysql-bin.000001',#查看主库状态获取参数
master_log_pos=851,#查看主库状态获取参数
master_connect_retry=30;#连接失败重试的时间间隔
(root@localhost) [(none)]> change master to master_host='192.168.153.128',master_user='repl',master_password='123456',master_port=3307,master_log_ffile='mysql-bin.000001',master_log_pos=851,master_connect_retry=30;
Query OK, 0 rows affected, 2 warnings (0.01 sec)(root@localhost) [(none)]>12、在从数据库中查看主从同步状态
(root@localhost) [(none)]> show slave status \G
*************************** 1. row ***************************Slave_IO_State: Master_Host: 192.168.153.128Master_User: replMaster_Port: 3307Connect_Retry: 30Master_Log_File: mysql-bin.000001Read_Master_Log_Pos: 851Relay_Log_File: 9a5bbcb88ded-relay-bin.000001Relay_Log_Pos: 4Relay_Master_Log_File: mysql-bin.000001Slave_IO_Running: NoSlave_SQL_Running: No
13、在从数据库中开启主从同步
(root@localhost) [(none)]> start slave;
Query OK, 0 rows affected (0.01 sec)(root@localhost) [(none)]>
(root@localhost) [(none)]> show slave status \G
*************************** 1. row ***************************Slave_IO_State: Waiting for master to send eventMaster_Host: 192.168.153.128Master_User: replMaster_Port: 3307Connect_Retry: 30Master_Log_File: mysql-bin.000001Read_Master_Log_Pos: 851Relay_Log_File: 9a5bbcb88ded-relay-bin.000002Relay_Log_Pos: 320Relay_Master_Log_File: mysql-bin.000001Slave_IO_Running: YesSlave_SQL_Running: YesReplicate_Do_DB:14、主从复制测试
在主库操作
(root@localhost) [(none)]> create database test;
Query OK, 1 row affected (0.00 sec)(root@localhost) [(none)]> use test;
Database changed(root@localhost) [test]> create table test1(id int,name varchar(20));
Query OK, 0 rows affected (0.00 sec)(root@localhost) [test]>
(root@localhost) [test]> insert into test1 values(1,'wu');
Query OK, 1 row affected (0.02 sec)(root@localhost) [test]>
(root@localhost) [test]> insert into test1 values(2,'kang');
Query OK, 1 row affected (0.00 sec)(root@localhost) [test]>在从库查看
(root@localhost) [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| test |
+--------------------+
5 rows in set (0.00 sec)(root@localhost) [(none)]> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -ADatabase changed
(root@localhost) [test]> select * from test1;
+------+------+
| id | name |
+------+------+
| 1 | wu |
| 2 | kang |
+------+------+
2 rows in set (0.00 sec)(root@localhost) [test]>
测试完成！
