Cenots搭建mysql集群：主从复制

1, 一主多从

此处为了演示，由于内存资源有限，采用docker 启动多个mysql容器

角色	主机机名/ip
master	mysql1/172.20.0.2
slave1	mysql2/172.20.0.3
slave2	mysql3/172.20.0.4

a, 启动三个mysql容器

# 创建自定义网络, 使得容器间能通过主机名访问
docker network create br 

docker rm -f mysql1
docker run -d -P --name mysql1 \
--net=br -v mysql1:/var/lib/mysql \
-e MYSQL_ROOT_PASSWORD=123456 \
mysql:5.7

docker rm -f mysql2
docker run -d -P --name mysql2 \
--net=br -v mysql2:/var/lib/mysql \
-e MYSQL_ROOT_PASSWORD=123456 \
mysql:5.7

docker rm -f mysql3
docker run -d -P --name mysql3 \
--net=br -v mysql3:/var/lib/mysql \
-e MYSQL_ROOT_PASSWORD=123456 \
mysql:5.7

[root@docker ~]# docker ps
CONTAINER ID        IMAGE                                                  COMMAND                  CREATED             STATUS                 PORTS                                               NAMES
60e91267022e        mysql:5.7                                              "docker-entrypoint.s…"   19 seconds ago      Up 17 seconds          0.0.0.0:32779->3306/tcp, 0.0.0.0:32778->33060/tcp   mysql3
bb4e67d6a4bb        mysql:5.7                                              "docker-entrypoint.s…"   26 seconds ago      Up 24 seconds          0.0.0.0:32777->3306/tcp, 0.0.0.0:32776->33060/tcp   mysql2
ff207b3b364a        mysql:5.7                                              "docker-entrypoint.s…"   35 seconds ago      Up 33 seconds          0.0.0.0:32775->3306/tcp, 0.0.0.0:32774->33060/tcp   mysql1

b, 进入容器修改配置

#-------mysql1
docker exec -it mysql1 bash
#进入容器后运行以下命令
cat >> /etc/mysql/mysql.conf.d/mysqld.cnf <<EOF
server-id=1
#主库开启binlog日志
log-bin=/var/lib/mysql/mysql-bin
EOF

#-------mysql2
docker exec -it mysql2 bash
#进入容器后运行以下命令
cat >> /etc/mysql/mysql.conf.d/mysqld.cnf <<EOF
server-id=2
EOF

#-------mysql3
docker exec -it mysql3 bash
#进入容器后运行以下命令
cat >> /etc/mysql/mysql.conf.d/mysqld.cnf <<EOF
server-id=3
EOF

#-------重启容器，使得配置生效
docker restart mysql1 mysql2 mysql3

c, 打通mysql-master和mysql-slave

#=======mysql1
grant replication slave on *.* to 'test'@'%' identified by '123456';
flush privileges

mysql>  show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      581 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)


#=======mysql2,mysql3: 分别在两台从库（slave）上操作如下命令
change master to master_host='172.20.0.2', master_port=3306, master_user='test', master_password='123456', master_log_file='mysql-bin.000001', master_log_pos=581;
flush privileges; 
start slave;

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.20.0.2
                  Master_User: test
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 1186
               Relay_Log_File: bb4e67d6a4bb-relay-bin.000002
                Relay_Log_Pos: 925
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

d, 测试：在mysql-master写入数据

在mysql-slave上，查看数据是否同步

################# 1,登陆mysql主节点： mysql1 ################
root@60e91267022e:/# mysql -uroot -p123456 -hmysql1
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 9
Server version: 5.7.27-log MySQL Community Server (GPL)

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

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> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.01 sec)

mysql> create database test;
Query OK, 1 row affected (0.01 sec)

mysql> use test;
Database changed
mysql> create table per(id int,name varchar(10));
Query OK, 0 rows affected (0.17 sec)

mysql> insert into per values(1,'a'),(2,'b');
Query OK, 2 rows affected (0.08 sec)
Records: 2  Duplicates: 0  Warnings: 0

################ 2,登陆mysql从节点： mysql2 ################
mysql> ^DBye
root@60e91267022e:/# mysql -uroot -p123456 -hmysql2
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 11
Server version: 5.7.27 MySQL Community Server (GPL)

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

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> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+
5 rows in set (0.00 sec)

mysql> 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
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| per            |
+----------------+
1 row in set (0.00 sec)

mysql> select * from per;
+------+------+
| id   | name |
+------+------+
|    1 | a    |
|    2 | b    |
+------+------+
2 rows in set (0.00 sec)

2, 双主：互为主从

角色	主机机名
master1	mysql1
master2	mysql2

a, 环境准备

# 创建自定义网络, 使得容器间能通过主机名访问
docker network create br 

docker rm -f mysql1
docker run -d -P --name mysql1 \
--net=br -v mysql1:/var/lib/mysql \
-e y=123456 \
mysql:5.7

docker rm -f mysql2
docker run -d -P --name mysql2 \
--net=br -v mysql2:/var/lib/mysql \
-e MYSQL_ROOT_PASSWORD=123456 \
mysql:5.7

b, 配置双主

##### mysql master1节点：
echo '
[mysqld]
server-id=1
log-bin=mysql-bin
auto-increment-increment=2
auto-increment-offset=1
log-slave-updates ' >> /etc/mysql/my.cnf

#重启mysql1
[root@c73 harbor]# docker exec -it mysql1 bash
root@2ff0e10914ca:/# mysql -uroot -p123456 -e 'show master status;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      125 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
root@2ff0e10914ca:/#
mysql>  show slave hosts;
+-----------+------+------+-----------+--------------------------------------+
| Server_id | Host | Port | Master_id | Slave_UUID                           |
+-----------+------+------+-----------+--------------------------------------+
|         2 |      | 3306 |         1 | 55483ff0-5665-11ec-85c3-0242ac120003 |
+-----------+------+------+-----------+--------------------------------------+
1 row in set (0.00 sec)


mysql> grant replication slave on *.* to 'repl'@'%' identified by '123456';
mysql> flush privileges;

#mysql1 设置master 为mysql2
mysql> change master to master_host='mysql2',master_port=3306,master_user='repl',master_password='123456',master_log_file='mysql-bin.000001',master_log_pos=599;
mysql> start slave;
mysql> show slave status\G
mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: mysql2
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 599
               Relay_Log_File: 2ff0e10914ca-relay-bin.000002
                Relay_Log_Pos: 320
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes



##### mysql master2节点：
echo '
[mysqld]
server-id=2
log-bin=mysql-bin
auto-increment-increment=2
auto-increment-offset=2
log-slave-updates' >> /etc/mysql/my.cnf

#重启mysql2
[root@c73 harbor]# docker exec -it mysql2 bash
root@6ae35ba87eb3:/# mysql -uroot -p123456 -e 'show master status;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      599 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
mysql> show slave hosts ;
+-----------+------+------+-----------+--------------------------------------+
| Server_id | Host | Port | Master_id | Slave_UUID                           |
+-----------+------+------+-----------+--------------------------------------+
|         1 |      | 3306 |         2 | 54792587-5665-11ec-95e4-0242ac120002 |
+-----------+------+------+-----------+--------------------------------------+
1 row in set (0.00 sec)


mysql> grant replication slave on *.* to 'repl'@'%' identified by '123456';
mysql> flush privileges;

#mysql2 设置master 为mysql1
mysql> change master to master_host='mysql1',master_port=3306,master_user='repl',master_password='123456',master_log_file='mysql-bin.000001',master_log_pos=125;
mysql> start slave;
mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: mysql1
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 125
               Relay_Log_File: 6ae35ba87eb3-relay-bin.000002
                Relay_Log_Pos: 320
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

c, 测试

#========测试=====
1，登录mysql1: create database test1; use test1; create table t1(id int); insert into t1 values(1);
2，登录mysql2: show databases; use test1; show tables; select * from t1;

3，登录mysql2: create database test2; use test2; create table t2(id int); insert into t2 values(1);
4，登录mysql1: show databases; use test2; show tables; select * from t2;