目录

一、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.128                   Master_User: repl                   Master_Port: 3307                 Connect_Retry: 30               Master_Log_File: mysql-bin.000001           Read_Master_Log_Pos: 851                Relay_Log_File: 9a5bbcb88ded-relay-bin.000001                 Relay_Log_Pos: 4         Relay_Master_Log_File: mysql-bin.000001              Slave_IO_Running: No             Slave_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 event                   Master_Host: 192.168.153.128                   Master_User: repl                   Master_Port: 3307                 Connect_Retry: 30               Master_Log_File: mysql-bin.000001           Read_Master_Log_Pos: 851                Relay_Log_File: 9a5bbcb88ded-relay-bin.000002                 Relay_Log_Pos: 320         Relay_Master_Log_File: mysql-bin.000001              Slave_IO_Running: Yes             Slave_SQL_Running: Yes               Replicate_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 -A  Database changed (root@localhost) [test]> select * from test1; +------+------+ | id   | name | +------+------+ |    1 | wu   | |    2 | kang | +------+------+ 2 rows in set (0.00 sec)  (root@localhost) [test]> 测试完成！