準備條件

作業系統  redhat 6.9

資料庫      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 -h127.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主庫建立用於監控mysql主從庫read_only是否只讀的資料庫使用者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配置用於監控mysql的資料庫使用者及密碼

（注：對應上述第3步配置的資料庫使用者）

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)

--如果配置的資料庫使用者密碼不對，根據實際情況進行調整

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主從節點與主機組的對應關係

（注：不同的mysql節點對應不同的主機組，實現讀寫分離）

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主庫建立用於讀寫分離的資料庫使用者

--讀寫資料庫使用者

mysql> grant all on *.* to rwuser@'10.0.0.11' identified by 'system';

Query OK, 0 rows affected (0.02 sec)

--只讀資料庫使用者

mysql> grant all on *.* to rouser@'10.0.0.12' identified by 'system';

Query OK, 0 rows affected (0.01 sec)

8,登陸中介軟體proxysql配置資料庫使用者與主機組的對應關係，即不同的資料庫使用者可以導流到不同的mysql主從的節點上

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，客戶端使用不同的資料庫使用者登陸中介軟體，實現mysql主從庫的讀寫分離

（注：rwuser資料庫使用者訪問mysql主庫，rouser資料庫使用者訪問mysql從庫）

[root@mysqlclient proxydir]# mysql -urwuser -psystem -h127.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 -h127.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]# 

    本人的職業經歷

中國普天 資料庫技術顧問

北京科藍軟體系統  資料庫DBA

北京雲和恩墨有限公司資料庫技術顧問

北京神州新橋科技有限公司資料庫諮詢顧問

    

    參與過的重點專案

四川達州商業銀行核心系統建設專案

中國聯通資料庫系統運維專案

貴州移動資料庫系統運維專案

京東方資料庫運維專案

拉卡拉資料庫系統運維專案

新疆匯和銀行核心系統建設專案

新疆銀行新核心系統建設專案

邢臺銀行影印平臺資料庫建設專案

保定銀行核心系統建設專案

重慶富民銀行資料中心建設專案

吉林億聯銀行資料中心建設專案

雲南工行資料庫建設專案

江西裕民銀行資料中心建設專案

湖北發改委資料庫運維專案

武漢眾邦銀行資料中心建設專案

遼寧振興銀行資料庫運維專案

總結

•  配置mysql主從讀寫分離，其實就是在proxysql中介軟體中，配置不同的主機組，不同的主機組各自對應不同的資料庫節點，這個不同的主機呢又是對應各自不同的資料庫使用者，透過不同的資料庫訪問中介軟體proxysql實現讀寫分離

• 配置主機組是對應proxysql中介軟體表 mysql_replication_hostgroups

• 配置mysql節點與主機組的隸屬關係是對應proxysql中介軟體表 mysql_servers

• 配置資料庫使用者與主機組的隸屬關係是對應proxysql中介軟體表 mysql_users

  
  

聯絡方式

  提供oracle及mysql的技術服務，安裝部署，效能最佳化，故障診斷，教育培訓。

微信公眾號

微信