ProxySQL实现MySQL读写分离(2)

注意事项：在实现主从复制时从节点在配置文件中必须要设置read_only，这是ProxySQL区分是用来作为读服务器还是写服务器的依据

一、实现主从复制 主节点配置

1.修改配置文件

[root@Master ~]# vim /etc/my.cnf [mysqld] server-id=1 log-bin binlog-format=row

2.启动MySQL服务

[root@Master ~]# systemctl start mariadb [root@Master ~]# mysql -e "SHOW MASTER LOGS;" +--------------------+-----------+ | Log_name | File_size | +--------------------+-----------+ | mariadb-bin.000001 | 245 | +--------------------+-----------+

3.创建用来复制的账号

[root@Master ~]# mysql -e "GRANT REPLICATION SLAVE ON *.* TO 'repluser'@'192.168.73.%' IDENTIFIED BY 'CentOS';" 从节点配置

1.修改配置文件

[root@Slave ~]# vim /etc/my.cnf [mysqld] server-id=2 log-bin binlog-format=row read-only #必须写

2.启动数据库服务

[root@Slave ~]# systemctl start mariadb

3.写入CHANGE MASTSER TO信息

MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST='192.168.73.110', MASTER_USER='repluser',MASTER_PASSWORD='centos',MASTER_PORT=3306,MASTER_LOG_FILE='mariadb-bin.000001',MASTER_LOG_POS=245; Query OK, 0 rows affected (0.00 sec)

4.启动复制线程

MariaDB [(none)]> START SLAVE; Query OK, 0 rows affected (0.00 sec)

5.查看状态

MariaDB [(none)]> SHOW SLAVE STATUS\G; *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.73.110 Master_User: repluser Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mariadb-bin.000001 Read_Master_Log_Pos: 402 Relay_Log_File: mariadb-relay-bin.000002 Relay_Log_Pos: 688 Relay_Master_Log_File: mariadb-bin.000001 Slave_IO_Running: Yes Slave_SQL_Running: Yes 测试

1.主节点导入数据库

[root@Master ~]# mysql < hellodb_innodb.sql [root@Master ~]# mysql -e "SHOW DATABASES;" +--------------------+ | Database | +--------------------+ | information_schema | | hellodb | | mysql | | performance_schema | | test | +--------------------+

2.从节点查看

[root@Slave ~]# mysql -e "SHOW DATABASES;" +--------------------+ | Database | +--------------------+ | information_schema | | hellodb | | mysql | | performance_schema | | test | +--------------------+

主从复制配置完毕

二、在ProxySQL上配置读写分离

1.在ProxySQL主机上配置yum源

[root@ProxySQL ~]# vim /etc/yum.repos.d/proxysql.repo [proxysql_repo] name= ProxySQL YUM repository baseurl=http://repo.proxysql.com/ProxySQL/proxysql-1.4.x/centos/\$releasever gpgcheck=1 gpgkey=http://repo.proxysql.com/ProxySQL/repo_pub_key

2.安装ProxySQL和mariadb客户端
ProxySQL内置了一个轻量级的数据库，所以需要有MySQL客户端连上去对其进行配置

[root@ProxySQL ~]# yum install proxysql mariadb -y

3.启动ProxySQL服务

[root@ProxySQL ~]# service proxysql start Starting ProxySQL: 2019-05-08 14:03:07 [INFO] Using config file /etc/proxysql.cnf DONE!

4.连接管理端口

[root@ProxySQL ~]# mysql -uadmin -padmin -P6032 -h127.0.0.1 Welcome to the MariaDB monitor. Commands end with ; or \g. Your MySQL connection id is 1 Server version: 5.5.30 (ProxySQL Admin Module) Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

5.将MySQL主从服务器信息添加入mysql_servers表中
先将主从服务器存放在同一组内，等指定好读写规则后，系统会根据配置文件中的read-only值自动将其分别添加至读组和写组。

MySQL [(none)]> INSERT INTO mysql_servers(hostgroup_id,hostname,port) VALUES (10,'192.168.73.110',3306); Query OK, 1 row affected (0.00 sec) MySQL [(none)]> INSERT INTO mysql_servers(hostgroup_id,hostname,port) VALUES (10,'192.168.73.111',3306); Query OK, 1 row affected (0.00 sec) MySQL [(none)]> SELECT * FROM mysql_servers -> ; +--------------+----------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+ | hostgroup_id | hostname | port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment | +--------------+----------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+ | 10 | 192.168.73.110 | 3306 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | | | 10 | 192.168.73.111 | 3306 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | | +--------------+----------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+ 2 rows in set (0.00 sec)

6.在MySQL服务器的主节点上为ProxySQL添加账号用来查看MySQL节点是主还是从

[root@Master ~]# mysql -e "GRANT REPLICATION SLAVE ON *.* TO 'monitor'@'192.168.73.%' IDENTIFIED BY 'centos';"