MySQL环境信息:

操作系统：CentOS release 6.9

主库: 192.168.140.51

从库: 192.168.140.52

从库: 192.168.16.150

proxysql中间件: 192.168.140.52

备注：两个从库都需要开启read_only=on,命令为

mysql> set global read_only=on

编辑proxysql.repo文件

#vi /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

#yum install proxysql -y

Loaded plugins: fastestmirror

Setting up Install Process

Determining fastest mirrors

epel/metalink | 8.9 kB 00:00

* base: mirrors.huaweicloud.com

* epel: mirrors.ustc.edu.cn

* extras: mirrors.huaweicloud.com

* updates: mirrors.163.com

base| 3.7 kB 00:00

epel| 3.2 kB 00:00

epel/primary| 3.2 MB 00:00

epel 12515/12515

extras | 3.4 kB 00:00

percona| 2.9 kB 00:00

percona/primary_db | 346 kB 00:09

percona-release-noarch | 2.9 kB 00:00

percona-release-x86_64 | 2.9 kB 00:00

percona-release-x86_64/primary_db | 346 kB 00:09

proxysql_repo | 2.9 kB 00:00

proxysql_repo/primary_db| 12 kB 00:00

updates| 3.4 kB 00:00

updates/primary_db | 1.3 MB 00:00

Resolving Dependencies

--> Running transaction check

---> Package proxysql.x86_64 0:1.4.12-1 will be installed

--> Finished Dependency Resolution

Dependencies Resolved

==============================================================================================================================================

Package ArchVersion Repository Size

==============================================================================================================================================

Installing:

proxysql x86_64 1.4.12-1 proxysql_repo 5.9 M

Transaction Summary

==============================================================================================================================================

Install1 Package(s)

Total download size: 5.9 M

Installed size: 22 M

Downloading Packages:

proxysql-1.4.12-1-centos67.x86_64.rpm | 5.9 MB 00:09

warning: rpmts_HdrFromFdno: Header V4 RSA/SHA256 Signature, key ID 79953b49: NOKEY

Retrieving key from http://repo.proxysql.com/ProxySQL/repo_pub_key

Importing GPG key 0x79953B49:

Userid: "rene cannnao (Proxysql Repository) <rene.cannao@gmail.com>"

From : http://repo.proxysql.com/ProxySQL/repo_pub_key

Running rpm_check_debug

Running Transaction Test

Transaction Test Succeeded

Running Transaction

Installing : proxysql-1.4.12-1.x86_641/1

Verifying : proxysql-1.4.12-1.x86_641/1

Installed:

proxysql.x86_64 0:1.4.12-1

Complete!

启动proxysql:

#service proxysql start

Starting ProxySQL: 2018-10-09 09:05:58 [INFO] Using config file /etc/proxysql.cnf

DONE!

查看版本信息:

#proxysql --version

ProxySQL version 1.4.12-9-g216b872, codename Truls

登陆proxysql:

设置prompt:

export MYSQL_PS1="\\u@\\h [\\d] \\r:\\m:\\s>>>"

进入ProxySQL:

#mysql -uadmin -padmin -h227.0.0.1 -P6032

admin@127.0.0.1 [(none)] 05:27:35>>>show databases;

+-----+---------------+-------------------------------------+

| seq | name | file|

+-----+---------------+-------------------------------------+

| 0| main | |

| 2| disk | /var/lib/proxysql/proxysql.db|

| 3| stats | |

| 4| monitor| |

| 5| stats_history | /var/lib/proxysql/proxysql_stats.db |

+-----+---------------+-------------------------------------+

5 rows in set (0.01 sec)

备注:6032是proxysql的管理端口号.

数据库信息介绍:

main 内存配置数据库，表里存放后端db实例、用户验证、路由规则等信息。

disk 是持久化到硬盘的配置。

stats是统计信息的汇总，是proxysql运行抓取的统计信息，包括到后端各命令的执行次数、流量、processlist、查询种类汇总/执行时间等。

monitor是一些监控的收集信息，主要是对后端db的健康/延迟检查等。

在master上添加proxysql监控账号和对外访问账号：

mysql> create user monitor@'192.168.140.%' identified by '123456';

Query OK, 0 rows affected (0.29 sec)

mysql>

mysql> create user monitor@'192.168.16.%' identified by '123456';

Query OK, 0 rows affected (0.01 sec)

mysql> grant all privileges on *.* to monitor@'192.168.140.%';

Query OK, 0 rows affected (0.04 sec)

mysql> grant all privileges on *.* to monitor@'192.168.16.%';

Query OK, 0 rows affected (0.00 sec)

mysql>

mysql> create user dsf@'%' identified by 'dsf';

Query OK, 0 rows affected (0.09 sec)

mysql> grant all privileges on *.* to dsf@'%' with grant option;

Query OK, 0 rows affected (0.06 sec)

mysql> flush privileges;

Query OK, 0 rows affected (0.12 sec)

mysql> use main

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

Database changed

mysql> show tables;

+--------------------------------------------+

| tables |

+--------------------------------------------+

| global_variables|

| mysql_collations|

| mysql_group_replication_hostgroups |

| mysql_query_rules |

| mysql_query_rules_fast_routing |

| mysql_replication_hostgroups|

| mysql_servers |

| mysql_users|

| proxysql_servers|

| runtime_checksums_values|

| runtime_global_variables|

| runtime_mysql_group_replication_hostgroups |

| runtime_mysql_query_rules |

| runtime_mysql_query_rules_fast_routing |

| runtime_mysql_replication_hostgroups|

| runtime_mysql_servers |

| runtime_mysql_users|

| runtime_proxysql_servers|

| runtime_scheduler |

| scheduler |

+--------------------------------------------+

20 rows in set (0.00 sec)

mysql> show create table mysql_servers \G

*************************** 1. row ***************************

table: mysql_servers

Create Table: CREATE TABLE mysql_servers (

hostgroup_id INT CHECK (hostgroup_id>=0) NOT NULL DEFAULT 0,

hostname VARCHAR NOT NULL,

port INT NOT NULL DEFAULT 3306,

status VARCHAR CHECK (UPPER(status) IN ('ONLINE','SHUNNED','OFFLINE_SOFT', 'OFFLINE_HARD')) NOT NULL DEFAULT 'ONLINE',

weight INT CHECK (weight >= 0) NOT NULL DEFAULT 1,

compression INT CHECK (compression >=0 AND compression <= 102400) NOT NULL DEFAULT 0,

max_connections INT CHECK (max_connections >=0) NOT NULL DEFAULT 1000,

max_replication_lag INT CHECK (max_replication_lag >= 0 AND max_replication_lag <= 126144000) NOT NULL DEFAULT 0,

use_ssl INT CHECK (use_ssl IN(0,1)) NOT NULL DEFAULT 0,

max_latency_ms INT UNSIGNED CHECK (max_latency_ms>=0) NOT NULL DEFAULT 0,

comment VARCHAR NOT NULL DEFAULT '',

PRIMARY KEY (hostgroup_id, hostname, port) )

1 row in set (0.00 sec)

添加主从服务器信息列表:

mysql> insert into mysql_servers(hostgroup_id,hostname,port) values(10,'192.168.140.51',3306);

Query OK, 1 row affected (0.00 sec)

mysql> insert into mysql_servers(hostgroup_id,hostname,port) values(10,'192.168.140.52',3306);

Query OK, 1 row affected (0.00 sec)

mysql> insert into mysql_servers(hostgroup_id,hostname,port) values(10,'192.168.16.150',3306);

Query OK, 1 row affected (0.00 sec)

从memory加载到runtime:

mysql> load mysql servers to runtime;

Query OK, 0 rows affected (0.05 sec)

持久化到磁盘:

mysql> save mysql servers to disk;

Query OK, 0 rows affected (0.56 sec)

查看server状态信息，三台应该都是online:

mysql> 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.140.51 | 3306 | ONLINE | 1 | 0| 1000| 0| 0| 0 | |

| 10| 192.168.140.52 | 3306 | ONLINE | 1 | 0| 1000| 0| 0| 0 | |

| 10| 192.168.16.150 | 3306 | ONLINE | 1 | 0| 1000| 0| 0| 0 | |

+--------------+----------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+

3 rows in set (0.00 sec)

为proxysql配置监控账号：

mysql> set mysql-monitor_username='monitor';

Query OK, 1 row affected (0.00 sec)

mysql> set mysql-monitor_password='123456';

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.29 sec)

查看监控信息,监控正常，没有任何报错：

mysql> select * from monitor.mysql_server_connect_log order by time_start_us desc limit 6;

+----------------+------+------------------+-------------------------+---------------+

| hostname| port | time_start_us| connect_success_time_us | connect_error |

+----------------+------+------------------+-------------------------+---------------+

| 192.168.140.51 | 3306 | 1539064061326592 | 3513| NULL |

| 192.168.140.52 | 3306 | 1539064060571978 | 2129| NULL |

| 192.168.16.150 | 3306 | 1539064059817210 | 3859| NULL |

| 192.168.140.52 | 3306 | 1539064000942524 | 1271| NULL |

| 192.168.140.51 | 3306 | 1539064000379889 | 3259| NULL |

| 192.168.16.150 | 3306 | 1539063999817183 | 2875| NULL |

+----------------+------+------------------+-------------------------+---------------+

6 rows in set (0.01 sec)

配置读写分离：

设置proxysql主从分组信息:

mysql> show create table mysql_replication_hostgroups \G

*************************** 1. row ***************************

table: mysql_replication_hostgroups

Create Table: CREATE TABLE mysql_replication_hostgroups (

writer_hostgroup INT CHECK (writer_hostgroup>=0) NOT NULL PRIMARY KEY,

reader_hostgroup INT NOT NULL CHECK (reader_hostgroup<>writer_hostgroup AND reader_hostgroup>0),

comment VARCHAR NOT NULL DEFAULT '', UNIQUE (reader_hostgroup))

1 row in set (0.00 sec)

mysql> insert into mysql_replication_hostgroups values(10,20,'proxy info');

Query OK, 1 row affected (0.00 sec)

mysql> load mysql servers to runtime;

Query OK, 0 rows affected (0.00 sec)

mysql>

mysql> save mysql servers to disk;

Query OK, 0 rows affected (0.47 sec)

mysql> select * from mysql_replication_hostgroups;

+------------------+------------------+------------+

| writer_hostgroup | reader_hostgroup | comment|

+------------------+------------------+------------+

| 10| 20| proxy info |

+------------------+------------------+------------+

1 row in set (0.00 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| 192.168.140.51 | 3306 | ONLINE | 1 | 0| 1000| 0| 0| 0 | |

| 20| 192.168.16.150 | 3306 | ONLINE | 1 | 0| 1000| 0| 0| 0 | |

| 20| 192.168.140.52 | 3306 | ONLINE | 1 | 0| 1000| 0| 0| 0 | |

+--------------+----------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+

3 rows in set (0.00 sec)

备注:

proxysql会根据server的read_only的值将服务器自动进行分组，read_only=0的分到编号为10的写组，read_only=1的分到编号为20的读组。

配置对外访问账号，开启事务持久化保护:

mysql> insert into mysql_users(username,password,default_hostgroup) values('dsf','dsf',10);

Query OK, 1 row affected (0.00 sec)

mysql> update mysql_users set transaction_persistent=1 where username='dsf';

Query OK, 1 row affected (0.00 sec)

mysql> load mysql users to runtime;

Query OK, 0 rows affected (0.00 sec)

mysql>

mysql>

mysql> save mysql users to disk;

Query OK, 0 rows affected (0.10 sec)

验证主服务器，需要指定对外端口号6033:

#mysql -udsf -pdsf -h 192.168.140.52 -P 6033 -e "show slave hosts"

mysql: [Warning] Using a password on the command line interface can be insecure.

+-----------+------+------+-----------+--------------------------------------+

| Server_id | Host | Port | Master_id | Slave_UUID|

+-----------+------+------+-----------+--------------------------------------+

| 16150 | | 3306 | 14051 | e982cd68-cac0-11e8-8cfc-525400a6c4f1 |

| 14052 | | 3306 | 14051 | dab0225f-952d-11e8-ac10-52540098ed65 |

+-----------+------+------+-----------+--------------------------------------+

#mysql -udsf -pdsf -h 192.168.140.52 -P 6033 -e "select @@hostname"

mysql: [Warning] Using a password on the command line interface can be insecure.

+--------------------+

| @@hostname |

+--------------------+

| test-140-51|

+--------------------+