利用mycat实现mysql的读写分离

最新推荐文章于 2024-04-04 20:37:17 发布

KanoeYuko

最新推荐文章于 2024-04-04 20:37:17 发布

阅读量280

点赞数

本文链接：https://blog.csdn.net/kanoeYuko/article/details/104572683

版权

本文介绍了在一个三台服务器的环境中，如何关闭selinux和防火墙，然后通过设置MySQL主从复制，安装Mycat并配置server.xml与schema.xml来实现读写分离。在配置完成后，通过开启通用日志验证了读写分离的效果，当从节点停止时，Mycat能够自动调度读请求到主节点。

摘要由CSDN通过智能技术生成

系统环境

cat /etc/centos-release
CentOS Linux release 8.0.1905 (Core)

服务器三台

mycat-server 192.168.100.10
mysql-master 192.168.100.11
mysql-slave 192.168.100.12

关闭selinux和防火墙

systemctl stop firewalld
setenforce 0
时间同步

实现步骤

1.创建 MySQL 主从数据库

[root@centos8 ~] yum -y install mariadb-server

）1. 修改master和slave上的配置文件

#master上的my.cnf
[root@centos8 ~]#vim /etc/my.cnf.d/mariadb-server.cnf
[mysqld]
server-id = 1
log-bin

#slave上的my.cnf
[mysqld]
server-id = 2

[root@centos8 ~]#systemctl start mariadb

）2.Master上创建复制用户

[root@centos8 ~] mysql -uroot -p

MariaDB [(none)]>GRANT REPLICATION SLAVE ON *.* TO 'repluser'@'192.168.100.12' IDENTIFIED BY 'replpass';

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

）3. Slave上执行

[root@centos8 ~] mysql -uroot -p
mysql> CHANGE MASTER TO
 ->     MASTER_HOST='192.168.100.11',
 ->     MASTER_USER='repluser',
 ->     MASTER_PASSWORD='replpass',
 ->     MASTER_LOG_FILE='mariadb-bin.000001',
 ->     MASTER_LOG_POS=403;
 
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                 Master_Host: 192.168.100.11
                 Master_User: repluser
                 Master_Port: 3306
               Connect_Retry: 60
             Master_Log_File: mariadb-bin.000001
         Read_Master_Log_Pos: 439
               Relay_Log_File: mariadb-relay-bin.000002
               Relay_Log_Pos: 689
       Relay_Master_Log_File: mariadb-bin.000001
             Slave_IO_Running: Yes
           Slave_SQL_Running: Yes
...省略..,