mysql第九话 - mysql主从复制集群实现

前因：为提升数据库性能，实现读写分离。本文主要记录了Mysql主从复制集群的搭建过程（一主一从）。

1.预先准备

• liunx服务器一台，或者两台 。只为演示就在一台机操作了，目录分别为mysql-master和master-slave
• docker容器化技术，docker-compose，不熟悉的可先了解mysql第一话 - mysql基于docker的安装及使用

2.Master节点配置

2.1 docker-compose.yaml文件

版本可自由选择，数据仅供测试

version: '3.7'
services:
  mysql-master:
    image: mysql:5.7
    container_name: mysql-master
    restart: always
    ports:
    - "3306:3306"
    environment:
    - MYSQL_ROOT_PASSWORD=123456
    - TZ=Asia/Shanghai
    volumes:
    - /root/local/mysql-master/data:/var/lib/mysql
    - /root/local/mysql-master/my.cnf:/etc/mysql/my.cnf
    networks:
    - my-net
networks:
  my-net:
    external: true

2.2 my.cnf配置

[mysqld]
# binlog 配置 开启二进制日志功能
log-bin=mysql-bin
# 同一局域网id要唯一
server-id=10
#二进制日志格式 row/基于行的复制 statement(默认)/根据修改数据的sql语句 mixed/混合使用
binlog-format=ROW
#binlog过期清理时间
expire_logs_days=30

2.3 运行master节点

启动成功后进入master节点容器内部，创建slave用户

#启动mysql
[root@m mysql-master]# docker-compose up -d
#进入master容器内部
[root@m mysql-master]# docker exec -it mysql-master /bin/bash
#进入mysql
root@b65d84c29ce3:# mysql -uroot -p123456
#创建slave用户
mysql> CREATE USER 'slave'@'%' IDENTIFIED BY '123456';
Query OK, 0 rows affected (0.00 sec)
#赋予权限
mysql> GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'slave'@'%';
Query OK, 0 rows affected (0.00 sec)

2.4 查看master节点的binlog文件

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

记住这个名字！

3.Slave节点配置

3.1 docker-compose.yaml配置

就目录和端口不一样

version: '3.7'
services:
  mysql-slave:
    image: mysql:5.7
    container_name: mysql-slave
    restart: always
    ports:
    - "3307:3306"
    environment:
    - MYSQL_ROOT_PASSWORD=123456
    - TZ=Asia/Shanghai
    volumes:
    - /root/local/mysql-slave/data:/var/lib/mysql
    - /root/local/mysql-slave/my.cnf:/etc/mysql/my.cnf
    networks:
    - my-net
networks:
  my-net:
    external: true

3.2 my.cnf配置

[mysqld]
# binlog 配置 开启二进制日志功能,此slave也可当做其他slave的master来用
log-bin=mysql-slave-bin
# 同一局域网id要唯一
server-id=11
#relay_log配置中继日志
relay_log=mysql=relay-bin
#设置为只读 不设置表示slave可写
read_only=1
#binlog过期清理时间
expire_logs_days=30

3.3 运行slave节点

[root@m mysql-slave]# docker-compose up -d
[root@m mysql-slave]# docker exec -it mysql-slave /bin/bash
root@6ec22dd2595d:/# mysql -uroot -p123456
#停止slave
mysql> stop slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)
#连接到master
mysql> change master to master_host='192.168.0.221',master_user='slave',master_password='123456',master_port=3306,master_log_file='mysql-bin.000003',master_log_pos=617;
Query OK, 0 rows affected, 2 warnings (0.02 sec)
#开启同步
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

3.4 查看slave运行状态

mysql> show slave status \G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.0.221
                  Master_User: slave
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000003
          Read_Master_Log_Pos: 617
               Relay_Log_File: mysql=relay-bin.000002
                Relay_Log_Pos: 320
        Relay_Master_Log_File: mysql-bin.000003
        	#这两行为yes说明slave开启成功
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
		## 。。。省略后续

4.测试

• 在master节点上添加test_db库，并在该库添加test表

root@b65d84c29ce3:/# mysql -uroot -p123456
mysql> CREATE DATABASE `test_db` /*!40100 COLLATE 'utf8_general_ci' */;
Query OK, 1 row affected (0.00 sec)

mysql> use `test_db`;
Database changed
mysql> CREATE TABLE `test` (
    -> `id` BIGINT NOT NULL,
    -> `age` INT NULL,
    -> `name` VARCHAR(50) NULL,
    -> PRIMARY KEY (`id`)
    -> )
    -> COLLATE='utf8_general_ci'
    -> ;
Query OK, 0 rows affected (0.01 sec)

• 在slave节点上查看

root@6ec22dd2595d:/# mysql -uroot -p123456
mysql> show DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test_db            |
+--------------------+
5 rows in set (0.00 sec)

mysql> use `test_db`;
Database changed
mysql> show tables;
+-------------------+
| Tables_in_test_db |
+-------------------+
| test              |
+-------------------+
1 row in set (0.00 sec)

可以看到在slave节点上也有对应的库和表，接下来就可以快乐的读写分离了。

以上就是本章的全部内容了。

上一篇：mysql第八话 - mysql的其它懒人用法
下一篇：mysql第十话 - mysql+springboot之sharding-JDBC分表分库实战

书到用时方恨少，事非经过不知难