阅读量:1
目录
一、MySQL主从复制原理就不做讲解了，详情请查看MySQL专栏
2、进入/usr/mysql/mysql-master/conf目录下新建my.cnf
7、进去/usr/mysql/mysql-slave/conf目录下新建my.cnf
一、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]> 测试完成！