Cenots搭建mysql集群：主从复制

我不是女神ヾ 2023-06-12 05:26 15阅读 0赞

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2V5ZW9mZWFnbGU_size_16_color_FFFFFF_t_70]  
此处为了演示，由于内存资源有限，采用docker 启动多个mysql容器

<table> 
 <thead> 
  <tr> 
   <th>角色</th> 
   <th>主机机名/ip</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>master</td> 
   <td>mysql1/172.20.0.2</td> 
  </tr> 
  <tr> 
   <td>slave1</td> 
   <td>mysql2/172.20.0.3</td> 
  </tr> 
  <tr> 
   <td>slave2</td> 
   <td>mysql3/172.20.0.4</td> 
  </tr> 
 </tbody> 
</table>

# 1, 启动三个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

# 2, 进入容器修改配置 #

#-------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

# 3, 打通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

# 4, 测试：在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)

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2V5ZW9mZWFnbGU_size_16_color_FFFFFF_t_70]: /images/20230531/2d4258d91ca9466bb9c5b53b0188e187.png