时间戳

首页 > 技术文章 > 关于MySQL的主从复制、读写分离集群

关于MySQL的主从复制、读写分离集群

zhangcz 2021-07-26 23:59 原文

实验过程

实验拓扑图

image

版本说明

8台Linux服务器，centos 7.7，MySQL 5.7.29，mysql-router 8.0.24，keepalived 1.3.5，ansible 2.9.18，zabbix 4.4.6

角色规划和分配

角色 IP 备注
mysql-master 192.168.181.151
mysql-slave1 192.168.181.152 bin_log+position方式
mysql-slave2 192.168.181.153 bin_log+GTID方式
mysql-backup 192.168.181.154 延迟备份
mysql-router1 192.168.181.155 HA-master
mysql-router2 192.168.181.156 HA-backup
zabbix 192.168.181.157
ansible 192.168.181.158

实验步骤

准备工作

  • 配置ansible主机与其他七台服务器的单向信任通道
第一步：在管理节点上使用root用户，生成密钥对
[root@ansible ~]# ssh-keygen -t ecdsa
第二步：将本地产生的公钥文件上传到被控制节点服务器里
[root@ansible ~/.ssh]# ssh-copy-id -i id_ecdsa.pub root@192.168.181.172 -p 22
第3步：验证免密通道是否配置成功
[root@ansible ~/.ssh]# ssh -p '22' 'root@192.168.181.172'

配置主从复制

  • 在master服务器上开启二进制日志并设置server_id
[root@localhost ~]# cat /etc/my.cnf
[mysqld]
......
log_bin
server_id=1
  • 在slave服务器上也开启二进制日志并设置server_id（其实从不一定要开启二进制日志的）
[mysqld]
......
log_bin
server_id=2
  • 创建用来复制二进制日志的用户并且授权（创建slave用户）只给复制权限就可以了
root@(none) 00:26  mysql>create user 'zhang_slave'@'192.168.181.153' identified by '123456';
Query OK, 0 rows affected (0.00 sec)

root@(none) 00:29  mysql>grant replication slave on *.* to 'zhang_slave'@'192.168.181.153';
Query OK, 0 rows affected (0.01 sec)
  • 在master上导出数据，并且传到slave，在slave上导入数据
[root@localhost ~]# mkdir /backup
[root@localhost ~]# mysqldump -uroot -pSanchuang123# --all-databases >/backup/all_db.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@localhost ~]# scp /backup/all_db.sql  root@192.168.181.153:/root

[root@localhost ~]# mysql -uroot -pSanchuang123# < all_db.sql 
mysql: [Warning] Using a password on the command line interface can be insecure.
  • 在slave上配置去master上拉取二进制日志的用户信息、日志文件名称和位置号
# 在master上查看信息
root@(none) 00:47  mysql>flush logs;
Query OK, 0 rows affected (0.01 sec)

root@(none) 00:48  mysql>show master status;
+----------------------+----------+--------------+------------------+-------------------+
| File                 | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+----------------------+----------+--------------+------------------+-------------------+
| localhost-bin.000003 |      154 |              |                  |                   |
+----------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

root@(none) 00:48  mysql>

# 在slave上配置
root@(none) 00:51  mysql> CHANGE MASTER TO MASTER_HOST='192.168.181.151',
    ->  MASTER_USER='zhang_slave',
    ->  MASTER_PASSWORD='123456',
    ->  MASTER_PORT=3309,
    ->  MASTER_LOG_FILE='localhost-bin.000003',
    ->  MASTER_LOG_POS=154;
Query OK, 0 rows affected, 2 warnings (0.02 sec)
  • 关闭防火墙和selinux并且设置开机不启动
service firewalld stop
systemctl disable firewalld
setenforce 0
  • 在slave上开启slave功能
root@(none) 01:08  mysql>stop slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)
root@(none) 01:09  mysql>start slave;
Query OK, 0 rows affected (0.02 sec)
  • 查看主从复制的效果
root@(none) 01:19  mysql>show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.181.151
                  Master_User: zhang_slave
                  Master_Port: 3309
                Connect_Retry: 60
              Master_Log_File: localhost-bin.000003
          Read_Master_Log_Pos: 154
               Relay_Log_File: mysql-slave-relay-bin.000007
                Relay_Log_Pos: 324
        Relay_Master_Log_File: localhost-bin.000003
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
            		.
            		.
            		.
             Master_UUID: f04f638f-b010-11eb-ac82-000c29094980
             Master_Info_File: /data/mysql/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
  • 验证主从复制效果，在master上新建一个库看看slave上有没有
# 在主服务器上操作
root@(none) 00:48  mysql>create database zhang;
Query OK, 1 row affected (0.00 sec)

root@(none) 01:28  mysql>show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| zhang              |
+--------------------+
5 rows in set (0.00 sec)

root@(none) 01:28  mysql>
# 在从服务器上操作
root@(none) 01:28  mysql>show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| zhang              |
+--------------------+
5 rows in set (0.00 sec)

root@(none) 01:28  mysql>
  • gtid配置
    1.从服务器的my.cnf内容
[mysqld]
log_bin
server_id=2
gtid-mode=ON
enforce-gtid-consistency=ON
log_slave_updates=ON

2.主服务器的my.cnf内容

[mysqld]
log_bin
server_id=1
gtid-mode=ON
enforce-gtid-consistency=ON

3.配置master信息，启动slave功能并验证效果

root@(none) 02:30  mysql> CHANGE MASTER TO MASTER_HOST='192.168.181.151',
    ->  MASTER_USER='zhang_slave',
    ->  MASTER_PASSWORD='123456',
    ->  MASTER_PORT=3309,
    ->  MASTER_LOG_FILE='mysql-master-bin.000003',
    ->  master_auto_position=0;
Query OK, 0 rows affected, 3 warnings (0.01 sec)

root@(none) 02:30  mysql>start slave;
Query OK, 0 rows affected (0.00 sec)

root@(none) 02:30  mysql>show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.181.151
                  Master_User: zhang_slave
                  Master_Port: 3309
                Connect_Retry: 60
              Master_Log_File: mysql-master-bin.000003
          Read_Master_Log_Pos: 154
               Relay_Log_File: mysql-slave-relay-bin.000002
                Relay_Log_Pos: 381
        Relay_Master_Log_File: mysql-master-bin.000003
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
            		.
            		.
            		.
                         Master_Server_Id: 1
                  Master_UUID: f04f638f-b010-11eb-ac82-000c29094980
             Master_Info_File: /data/mysql/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400

配置读写分离

  • 上传mysql-router的rpm包，然后安装
[root@localhost ~]#  rpm -ivh mysql-router-community-8.0.24-1.el7.x86_64.rpm 
警告：mysql-router-community-8.0.24-1.el7.x86_64.rpm: 头V3 DSA/SHA1 Signature, 密钥 ID 5072e1f5: NOKEY
准备中...                          ################################# [100%]
正在升级/安装...
   1:mysql-router-community-8.0.24-1.e################################# [100%]
[root@localhost ~]# 
[root@localhost ~]# cd /etc/mysqlrouter/
[root@localhost mysqlrouter]# ls
mysqlrouter.conf
[root@localhost mysqlrouter]#
  • 在/etc/mysqlrouter/mysqlrouter.conf添加以下配置
[routing:read_write]
bind_address = 192.168.181.152 
bind_port = 7001
mode = read-write
destinations = 192.168.181.151:3309
max_connections = 65535
max_connect_errors = 100
client_connect_timeout = 9

[routing:read_only]
bind_address = 192.168.181.152
bind_port = 7002
mode = read-only
destinations = 192.168.181.153:3309
max_connections = 65535
max_connect_errors = 100
client_connect_timeout = 9
  • 启动mysqlrouter服务，并检查端口
[root@localhost log]# service mysqlrouter start
Redirecting to /bin/systemctl start mysqlrouter.service
[root@localhost log]# netstat -auplnt|grep mysql
tcp        0      0 192.168.181.152:7001    0.0.0.0:*               LISTEN      1888/mysqlrouter    
tcp        0      0 192.168.181.152:7002    0.0.0.0:*               LISTEN      1888/mysqlrouter
  • 新建两个用户一个只有读的权限一个all权限
    luoluo--->select ，zhangzhang--->all
root@(none) 03:21  mysql>create user 'luoluo'@'%' identified by '123456';
Query OK, 0 rows affected (0.01 sec)

root@(none) 03:34  mysql>create user 'zhangzhang'@'%' identified by '123456';
Query OK, 0 rows affected (0.00 sec)

root@(none) 03:35  mysql>grant select on *.* to 'luoluo'@'%'
    -> ;
Query OK, 0 rows affected (0.01 sec)

root@(none) 03:36  mysql>grant all on *.* to 'zhangzhang'@'%';
Query OK, 0 rows affected (0.01 sec)

root@(none) 03:36  mysql>
  • 远程连过去验证效果
[root@localhost ~]# mysql -h 192.168.181.152 -P 7001 -uluoluo -p123456
  • 在master上看看有谁连接过来
root@(none) 03:48  mysql>show processlist;
+----+--------+-----------------------+------+---------+------+----------+------------------+
| Id | User   | Host                  | db   | Command | Time | State    | Info             |
+----+--------+-----------------------+------+---------+------+----------+------------------+
|  2 | root   | localhost             | NULL | Query   |    0 | starting | show processlist |
|  7 | luoluo | 192.168.181.152:46416 | NULL | Sleep   |    5 |          | NULL             |
+----+--------+-----------------------+------+---------+------+----------+------------------+
2 rows in set (0.00 sec)

root@(none) 03:50  mysql>
  • 用只读用户建库不可以
luoluo@(none) 10:41  mysql>create database haha;
ERROR 1044 (42000): Access denied for user 'luoluo'@'%' to database 'haha'
luoluo@(none) 10:44  mysql>
  • 用读写用户建库可以
[root@localhost ~]# mysql -h 192.168.181.152 -P 7001 -uzhangzhang -p123456
zhangzhang@(none) 10:45  mysql>create database haha;
Query OK, 1 row affected (0.01 sec)

在验证一下连接到7002端口，分别用只读用户和读写用户去建库，发现读写用户是可以建库成功的，只读用户则不可以（在slave机器上看有谁连过来）由此说明读和写的操作不是靠端口来控制的而是靠控制用户的权限来控制的。

配置keepalived

  • 安装keepalived
[root@localhost mysqlrouter]# yum install keepalived -y
[root@localhost mysqlrouter]# keepalived --version
Keepalived v1.3.5 (03/19,2017), git commit v1.3.5-6-g6fa32f2

Copyright(C) 2001-2017 Alexandre Cassen, <acassen@gmail.com>

Build options:  PIPE2 LIBNL3 RTA_ENCAP RTA_EXPIRES RTA_PREF RTA_VIA FRA_OIFNAME FRA_SUPPRESS_PREFIXLEN FRA_TUN_ID RTAX_CC_ALGO RTAX_QUICKACK LIBIPTC LIBIPSET_DYNAMIC LVS LIBIPVS_NETLINK VRRP VRRP_AUTH VRRP_VMAC SOCK_NONBLOCK SOCK_CLOEXEC FIB_ROUTING INET6_ADDR_GEN_MODE SNMP_V3_FOR_V2 SNMP SNMP_KEEPALIVED SNMP_CHECKER SNMP_RFC SNMP_RFCV2 SNMP_RFCV3 SO_MARK
[root@localhost mysqlrouter]#
  • 修改/etc/keepalived/keepalived.conf
master
[root@mysqlrouter-keepalived-master keepalived]# cat keepalived.conf 
! Configuration File for keepalived

global_defs {
	...
   #vrrp_strict
   vrrp_garp_interval 0
   vrrp_gna_interval 0
}

vrrp_instance VI_1 {
    state MASTER
    interface ens33
    virtual_router_id 51
    priority 120
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass 1111
    }
    virtual_ipaddress {
        192.168.181.222
    }
}
-------------------------
backup
[root@mysqlrouter-keepalived-backup keepalived]# cat keepalived.conf 
! Configuration File for keepalived

global_defs {
	...
   # vrrp_strict
   vrrp_garp_interval 0
   vrrp_gna_interval 0
}

vrrp_instance VI_1 {
    state BACKUP
    interface ens33
    virtual_router_id 51
    priority 100
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass 1111
    }
    virtual_ipaddress {
        192.168.181.222
    }
}
[root@mysqlrouter-keepalived-backup keepalived]#
  • 启动keepalived服务并查看效果

推荐阅读