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=

gpgcheck=1

gpgkey=

#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 Arch Version Repository Size

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

Installing:

proxysql x86_64 1.4.12-1 proxysql_repo 5.9 M

Transaction Summary

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

Install 1 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

Importing GPG key 0x79953B49:

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

From :

Running rpm_check_debug

Running Transaction Test

Transaction Test Succeeded

Running Transaction

Installing : proxysql-1.4.12-1.x86_64 1/1

Verifying : proxysql-1.4.12-1.x86_64 1/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 -h127.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;

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

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

| 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;

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

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

| 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;

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

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

| 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> 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.

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

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

| 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 |

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

proxysql安裝和使用小記

相關文章