Shulou(Shulou.com)05/31 Report--

This article introduces the relevant knowledge of "the installation steps of proxysql". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

MySQL environment information:

Operating system: CentOS release 6.9

Main library: 192.168.140.51

Slave library: 192.168.140.52

Slave library: 192.168.16.150

Proxysql middleware: 192.168.140.52

Note: read_only=on needs to be enabled for both slave libraries. The command is

Mysql > set global read_only=on

Edit the proxysql.repo file

# 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 01.4.12-1 will be installed

-> Finished Dependency Resolution

Dependencies Resolved

=

Package Arch Version Repository Size

=

Installing:

Proxysql x8631 64 1.4.12-1 proxysql_repo 5.9m

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 http://repo.proxysql.com/ProxySQL/repo_pub_key

Importing GPG key 0x79953B49:

Userid: "rene cannnao (Proxysql Repository)"

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_64 1 Compact 1

Verifying: proxysql-1.4.12-1.x86_64 1 Compact 1

Installed:

Proxysql.x86_64 0Plus 1.4.12-1

Complete!

Start proxysql:

# service proxysql start

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

DONE!

View version information:

# proxysql-version

ProxySQL version 1.4.12-9-g216b872, codename Truls

Log in to proxysql:

Set up the prompt:

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

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

Note: 6032 is the management port number of proxysql.

Database information introduction:

Main memory configuration database, which stores backend db instance, user authentication, routing rules and other information in the table.

Disk is the configuration that persists to the hard disk.

Stats is a summary of statistical information, which is the statistics of proxysql running crawl, including the number of commands executed to the backend, traffic, processlist, query type summary / execution time, and so on.

Monitor is the collection of monitoring information, mainly the health / delay check of the back-end db.

Add proxysql monitoring account and external access account to master:

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 = 0) NOT NULL DEFAULT 1000

Max_replication_lag INT CHECK (max_replication_lag > = 0 AND max_replication_lag = 0) NOT NULL DEFAULT 0

Comment VARCHAR NOT NULL DEFAULT''

PRIMARY KEY (hostgroup_id, hostname, port))

1 row in set (0.00 sec)

Add a list of master and slave server information:

Mysql > insert into mysql_servers (hostgroup_id,hostname,port) values (10meme 192.168.140.51)

Query OK, 1 row affected (0.00 sec)

Mysql > insert into mysql_servers (hostgroup_id,hostname,port) values (10meme 192.168.140.52)

Query OK, 1 row affected (0.00 sec)

Mysql > insert into mysql_servers (hostgroup_id,hostname,port) values (10meme 192.168.16.150pr 3306)

Query OK, 1 row affected (0.00 sec)

Load from memory to runtime:

Mysql > load mysql servers to runtime

Query OK, 0 rows affected (0.05 sec)

Persist to disk:

Mysql > save mysql servers to disk

Query OK, 0 rows affected (.56 sec)

To view the server status information, all three should be 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)

Configure a monitoring account for 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)

Check the monitoring information, the monitoring is normal, and no errors are reported:

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)

Configure read-write separation:

Set the proxysql master-slave grouping information:

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_hostgroupwriter_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, 10, 20, 10, 10, 20, 10, 10, 10, 20, 10, 10, 20, 10, 10, 10, 20, 10, 10, 10, 20, 10, 10, 10, 20, 10, 10, 20, 10, 10, 10, 20, 10, 10, 10, 20, 10, 10, 10, 20, 10, 10, 10, 10, 20, 10, 10, 10, 10, 20, 10, 10, 10, 20, 10, 10, 10, 10, 10, 20, 10, 10, 10, 10, 10, 10, 20, 10, 10, 10, 10, 10, 20, 10, 10, 10, 10, 10, 20, 10, 10, 10, 10, 20, 10, 10, 10, 20, 10, 10, 10, 10, 10, 20, 10, 10, 10, 10,

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)

Remarks:

Proxysql automatically groups servers according to the read_only value of server, with read_only=0 assigned to the write group numbered 10 and read_only=1 to the read group numbered 20.

Configure external access account and enable transaction persistence protection:

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)

To verify the master server, you need to specify the external port number 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 |

+-+

This is the end of the content of "proxysql installation steps". Thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.