Mysql使用mycat实现主从读写分离

实验环境

  • linux版本：rocky9.4

主机IP

角色

mysql版本

10.0.0.18

mycat

10.0.0.12

master

8.0.44

10.0.0.15

slave

8.0.44

yum install mysql-server -y
# 安装完后先不要启动

mkdir /data/mysql/logbin/ -pv
chown mysql:mysql -R /data/mysql


rm -rf /var/lib/mysql/*
rm -rf /data/mysql/logbin/*

# master  10.0.0.12


server-id=12
log_bin=/data/mysql/logbin/mysql-bin   # 文件名会以mysql-bin开头
default_authentication_plugin=mysql_native_password
gtid_mode=ON
enforce_gtid_consistency=ON
# rpl_semi_sync_master_enable                                                                                                                   
# rpl_semi_sync_master_timeout=3000
# slave 10.0.0.15

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mysql/mysqld.log
pid-file=/run/mysqld/mysqld.pid

server_id=15
read_only=ON
log_bin=/data/mysql/logbin/mysql-bin 
default_authentication_plugin=mysql_native_password
gtid_mode=ON
enforce_gtid_consistency=ON
# rpl_semi_sync_slave_enabled=1

主节点导出数据：

mysqldump -A -F --source-data=1 --single-transaction >all.sql

mysqldump -A -F --source-data=1 --single-transaction >all.sql

[root@rocky9-18 ~]# vim all.sql
...
# CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000004', MASTER_LOG_POS=157;
# 修改为如下数据
CHANGE MASTER TO
      MASTER_HOST='10.0.0.12',
      MASTER_USER='repluser',
      MASTER_PASSWORD='123456',
      MASTER_PORT=3306,
     MASTER_LOG_FILE='mysql-bin.000004', MASTER_LOG_POS=157;

# GTID方式
CHANGE MASTER TO
      MASTER_HOST='10.0.0.12',
      MASTER_USER='repluser',
      MASTER_PASSWORD='123456',
      MASTER_PORT=3306,
      MASTER_AUTO_POSITION=1;
#创建账号并授权
mysql> create user repluser@'10.0.0.%' identified by '123456';
Query OK, 0 rows affected (0.00 sec)
mysql> grant replication slave on *.* to repluser@'10.0.0.%';
Query OK, 0 rows affected (0.01 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

配置主从同步

master节点

  • 编辑/etc/my.cnf.d/mysql-server.cnf

# master  10.0.0.12
# vim /etc/my.cnf.d/mysql-server.cnf

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mysql/mysqld.log
pid-file=/run/mysqld/mysqld.pid

server-id=12
log_bin=/data/mysql/logbin/mysql-bin   # 文件名会以mysql-bin开头
default_authentication_plugin=mysql_native_password
# 设置新创建用户的默认身份验证插件为 mysql_native_password，
# 提高与旧客户端的兼容性（MySQL 8.0 默认为 caching_sha2_password）。
gtid_mode=ON  # 启用 GTID
enforce_gtid_consistency=ON   # 强制保证所有事务与 GTID 兼容

  • 启动mysql服务，进入mysql终端

# 启动服务
systemctl start mysqld

# 创建mysql主从专用账号并授权
mysql> create user repluser@'10.0.0.%' identified by '123456';
Query OK, 0 rows affected (0.00 sec)
mysql> grant replication slave on *.* to repluser@'10.0.0.%';
Query OK, 0 rows affected (0.01 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

  • 数据库备份导出

# 适用于主从复制
mysqldump -u root -p \
  --all-databases \
  --single-transaction \
  --triggers --routines --events \
  --source-data=2 \
  --set-gtid-purged=ON \
  > master_backup.sql

# 发送到从节点
scp master_backup.sql root@10.0.0.15:

slave节点

  • 编辑/etc/my.cnf.d/mysql-server.cnf

# slave  10.0.0.15 
# vim /etc/my.cnf.d/mysql-server.cnf

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mysql/mysqld.log
pid-file=/run/mysqld/mysqld.pid

server_id=15  
read_only=ON   # 开启只读
log_bin=/data/mysql/logbin/mysql-bin 
default_authentication_plugin=mysql_native_password
gtid_mode=ON
enforce_gtid_consistency=ON
# rpl_semi_sync_slave_enabled=1

  • 导入数据库

# 方式一
mysql < master_backup.sql

# 方式二 Mysql终端
mysql> source master_backup.sql

配置主从

在slave节点

  • GTID方式，通过自动检索方式去获取主从

mysql> CHANGE MASTER TO
      MASTER_HOST='10.0.0.12',
      MASTER_USER='repluser',
      MASTER_PASSWORD='123456',
      MASTER_PORT=3306,
      MASTER_AUTO_POSITION=1;

  • 启动从节点

mysql> start slave;
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for source to send event
                  Master_Host: 10.0.0.12
                  Master_User: repluser
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000003
          Read_Master_Log_Pos: 1037
               Relay_Log_File: rocky9-12-relay-bin.000002
                Relay_Log_Pos: 1253
        Relay_Master_Log_File: mysql-bin.000003
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

主节点

检查主从情况

mysql> show slave hosts;
+-----------+------+------+-----------+--------------------------------------+
| Server_id | Host | Port | Master_id | Slave_UUID                           |
+-----------+------+------+-----------+--------------------------------------+
|        15 |      | 3306 |        12 | 8bb7dd5a-e469-11f0-9589-0050563511d4 |
+-----------+------+------+-----------+--------------------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> show master status\G
*************************** 1. row ***************************
             File: mysql-bin.000004
         Position: 197
     Binlog_Do_DB: 
 Binlog_Ignore_DB: 
Executed_Gtid_Set: 6e1eb4ad-e46a-11f0-9d1d-000c29da1c8b:1-4
1 row in set (0.00 sec)

数据同步测试

  • 主节点

--- 创建表
 create database db1;
 
 use db1;

 CREATE TABLE stu (
     id int UNSIGNED AUTO_INCREMENT PRIMARY KEY,
     name VARCHAR(20) NOT NULL,
     age tinyint UNSIGNED,
     gender ENUM('M','F') default 'M'
     )ENGINE=InnoDB;

 insert into stu(name,age)values('tom',10);

  • 从节点

mysql>  show databases like '%db1%';
+------------------+
| Database (%db1%) |
+------------------+
| db1              |
+------------------+
1 row in set (0.00 sec)

mysql> select * from db1.stu;
+----+------+------+--------+
| id | name | age  | gender |
+----+------+------+--------+
|  1 | tom  |   10 | M      |
+----+------+------+--------+
1 row in set (0.00 sec)

配置 Mycat 连接账号

主节点创建账号并授权，该帐号会被同步到 slave 节点

create user 'mycater'@'10.0.0.%' IDENTIFIED BY '123456';

GRANT ALL ON db1.* TO 'mycater'@'10.0.0.%';

flush privileges;

修改 server.xml 配置 mycat 连接后端数据库的账号密码

# 直接覆盖
vim /apps/mycat/conf/server.xml


<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mycat:server SYSTEM "server.dtd">
<mycat:server xmlns:mycat="http://io.mycat/">

    <system>
        <property name="useHandshakeV10">1</property>
        <property name="serverPort">3306</property> 
    </system>

    <user name="root"> 
        <property name="password">123456</property> 
        <property name="schemas">db1</property>
        <property name="defaultSchema">db1</property>
    </user>

</mycat:server>

修改 schema.xml 实现读写分离策略

# 如果出现多个从节点，那么都以readHost的样式来进行配置
vim /apps/mycat/conf/schema.xml
 
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">

    <schema name="db1" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1">
    </schema>

    <dataNode name="dn1" dataHost="localhost1" database="db1" />

    <dataHost name="localhost1" maxCon="1000" minCon="10" 
              balance="1" 
              writeType="0" 
              dbType="mysql" 
              dbDriver="native" 
              switchType="1" 
              slaveThreshold="100">
        
        <heartbeat>select user();</heartbeat>

        <writeHost host="host1" url="10.0.0.12:3306" user="mycater" password="123456"> 
            <readHost host="host2" url="10.0.0.15:3306" user="mycater" password="123456" />
        </writeHost>
        
    </dataHost>
</mycat:schema>

测试mycat效果

  • mycat端登陆mysql

# mycat  10.0.0.18 

mysql -uroot -p123456 -h127.1

验证读写分离, 分别在master 和 slave节点上开启通用日志

echo 'general_log' >> /etc/my.cnf.d/mysql-server.cnf

systemctl restart mysqld.service

查看通用日志, master, slave节点都有,文件名与机名同名

#mycat 客户端执行查询

show tables from db1;
select * from db1.stu;

insert into db1.stu(name,age,gender)values('jerry',20,'F');
tail -f /var/lib/mysql/rocky9-12.log


12 Query     select user()
13 Query     insert into db1.stu(name,age,gender)values('jerry',20,'F')
8 Query      select user()
# salve 10.0.0.15
tail -f /var/lib/mysql/rocky9-15.log

8 Connect   mycater@10.0.0.18 on db1 using TCP/IP
8 Query     select user()    # 心跳检测
8 Query     show tables from db1
8 Query     select user()
8 Query     select * from db1.stu

当 slave节点离线

  • mycat 会将查操作移换到 master 节点

  • 恢复 slave 节点后，下一次心跳检测之后，查询将再次回到 slave 节点上

当 master 节点离线

  • 心跳检测时间范围内，读操作还可执行

  • 心跳检测时间范围外，所有操作都无法使用了

  • master节点恢复后，所有功能都可以正常使用了，如果无法立刻成功的话，请稍等10秒钟。