這篇文章將為大家詳細講解有關如何使用proxysql 1.4.14中間件實現(xiàn)MySQL 5.7.26主從的讀寫分離，小編覺得挺實用的，因此分享給大家做個參考，希望大家閱讀完這篇文章后可以有所收獲。
我們提供的服務有：網站制作、成都網站建設、微信公眾號開發(fā)、網站優(yōu)化、網站認證、尼瀘西ssl等。為數(shù)千家企事業(yè)單位解決了網站和推廣的問題。提供周到的售前咨詢和貼心的售后服務，是有科學管理、有技術的尼瀘西網站制作公司
操作系統(tǒng) redhat 6.9
數(shù)據(jù)庫 mysql 5.7.26
中間件 proxysql 1.4.14
已配置mysql一主一從，具體見下
編號 服務器角色 ip地址 端口
1 proxysql 10.0.0.13 6032,6033(注:6032是proxysql的管理端口，6033是proxysql對外服務的端口）
2 mysql主庫 10.0.0.11 3306
3 mysql從庫 10.0.0.12 3306
1,登陸中間件proxysql
[root@mysqlclient ~]# mysql -uadmin -padmin -h227.0.0.1 -P6032
2,配置用于讀寫分離的不同的主機組，10用于mysql主庫的主機組，20用于mysql從庫的主機組
mysql> insert into mysql_replication_hostgroups values(10,20,'use for msyql primary replication');
Query OK, 1 row affected (0.00 sec)
mysql> select * from mysql_replication_hostgroups;
+------------------+------------------+-----------------------------------+
| writer_hostgroup | reader_hostgroup | comment |
+------------------+------------------+-----------------------------------+
| 10 | 20 | use for msyql primary replication |
+------------------+------------------+-----------------------------------+
1 row in set (0.00 sec)
3,登陸mysql主庫創(chuàng)建用于監(jiān)控mysql主從庫read_only是否只讀的數(shù)據(jù)庫用戶monitor
mysql>grant replication client on *.* to 'monitor'@'10.0.0.13' identified by 'monitor';
Query OK, 0 rows affected (0.02 sec)
4,登陸mysql從庫確認read_only=on只讀
mysql> show global variables like 'read_only';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| read_only | ON |
+---------------+-------+
1 row in set (0.00 sec)
5,登陸中間件proxysql配置用于監(jiān)控mysql的數(shù)據(jù)庫用戶及密碼
（注：對應上述第3步配置的數(shù)據(jù)庫用戶）
mysql> select * from main.global_variables where variable_name in ('mysql-monitor_username','mysql-monitor_password');
+------------------------+----------------+
| variable_name | variable_value |
+------------------------+----------------+
| mysql-monitor_password | monitor |
| mysql-monitor_username | monitor |
+------------------------+----------------+
2 rows in set (0.00 sec)
--如果配置的數(shù)據(jù)庫用戶密碼不對，根據(jù)實際情況進行調整
mysql> set mysql-monitor_username='monitor';
Query OK, 1 row affected (0.00 sec)
mysql> set mysql-monitor_password='monitor';
Query OK, 1 row affected (0.00 sec)
--持久化
mysql> load mysql variables to runtime;
Query OK, 0 rows affected (0.00 sec)
mysql> save mysql variables to disk;
Query OK, 97 rows affected (0.02 sec)
6，配置mysql主從節(jié)點與主機組的對應關系
（注：不同的mysql節(jié)點對應不同的主機組，實現(xiàn)讀寫分離）
mysql> insert into mysql_servers(hostgroup_id,hostname,port) values(10,'10.0.0.11',3306);
Query OK, 1 row affected (0.00 sec)
mysql> insert into mysql_servers(hostgroup_id,hostname,port) values(20,'10.0.0.12',3306);
Query OK, 1 row affected (0.00 sec)
持久化
mysql> load mysql servers to runtime;
Query OK, 0 rows affected (0.01 sec)
mysql> save mysql servers to disk;
Query OK, 0 rows affected (0.03 sec)
mysql> select * from mysql_servers;
+--------------+-----------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname | port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+-----------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| 10 | 10.0.0.11 | 3306 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | |
| 20 | 10.0.0.12 | 3306 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | |
+--------------+-----------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
2 rows in set (0.00 sec)
7,登陸mysql主庫創(chuàng)建用于讀寫分離的數(shù)據(jù)庫用戶
--讀寫數(shù)據(jù)庫用戶
mysql> grant all on *.* to rwuser@'10.0.0.11' identified by 'system';
Query OK, 0 rows affected (0.02 sec)
--只讀數(shù)據(jù)庫用戶
mysql> grant all on *.* to rouser@'10.0.0.12' identified by 'system';
Query OK, 0 rows affected (0.01 sec)
8,登陸中間件proxysql配置數(shù)據(jù)庫用戶與主機組的對應關系，即不同的數(shù)據(jù)庫用戶可以導流到不同的mysql主從的節(jié)點上
mysql> insert into mysql_users(username,password,default_hostgroup) values('rwuser','system',10);
Query OK, 1 row affected (0.00 sec)
mysql> insert into mysql_users(username,password,default_hostgroup) values('rouser','system',20);
Query OK, 1 row affected (0.00 sec)
--持久化
mysql> load mysql users to runtime;
Query OK, 0 rows affected (0.00 sec)
mysql> save mysql users to disk;
Query OK, 0 rows affected (0.02 sec)
mysql> select * from mysql_users;
+----------+----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+
| username | password | active | use_ssl | default_hostgroup | default_schema | schema_locked | transaction_persistent | fast_forward | backend | frontend | max_connections |
+----------+----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+
| rwuser | system | 1 | 0 | 10 | NULL | 0 | 1 | 0 | 1 | 1 | 10000 |
| rouser | system | 1 | 0 | 20 | NULL | 0 | 1 | 0 | 1 | 1 | 10000 |
+----------+----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+
2 rows in set (0.00 sec)
9，客戶端使用不同的數(shù)據(jù)庫用戶登陸中間件，實現(xiàn)mysql主從庫的讀寫分離
（注：rwuser數(shù)據(jù)庫用戶訪問mysql主庫，rouser數(shù)據(jù)庫用戶訪問mysql從庫）
[root@mysqlclient proxydir]# mysql -urwuser -psystem -h227.0.0.1 -P6033 -e 'select @@server_id'
Warning: Using a password on the command line interface can be insecure.
+-------------+
| @@server_id |
+-------------+
| 1 |
+-------------+
[root@mysqlclient proxydir]# mysql -urouser -psystem -h227.0.0.1 -P6033 -e 'select @@server_id'
Warning: Using a password on the command line interface can be insecure.
+-------------+
| @@server_id |
+-------------+
| 2 |
+-------------+
[root@mysqlclient proxydir]#
關于“如何使用proxysql 1.4.14中間件實現(xiàn)mysql 5.7.26主從的讀寫分離”這篇文章就分享到這里了，希望以上內容可以對大家有一定的幫助，使各位可以學到更多知識，如果覺得文章不錯，請把它分享出去讓更多的人看到。
重慶分公司
重慶分公司
