사용할 구성에서 ProxySQL 읽기-쓰기 분리

Meituan-Dianping의 DBProxy 읽기-쓰기 분리 사용 지침 문서에 사용 목적이 설명되어 있습니다. 이 문서에서는 ProxySQL 사용 방법과 DBProxy와의 성능 차이를 소개합니다. 자세한 소개는 공식 홈페이지의 관련 안내를 참고하시기 바라며, 본 미들웨어 역시 퍼코나에서 추천하는 미들웨어입니다. 읽기와 쓰기를 분리하는 다른 미들웨어와 기능은 크게 다르지 않습니다. 자세한 내용은 기사에서 소개하겠습니다. 이 문서에서는 사용 중 몇 가지 지침을 간략하게 소개합니다. 공식 위키에서 도움말을 확인할 수도 있습니다.

환경:

배포자 ID: Ubuntu
설명: Ubuntu 14.04.5 LTS
릴리스: 14.04코드 이름: trusty

Download

percona 사이트:

https://www.percona.com/downloads/proxysql/

github/공식 웹사이트:

https://github.com/sysown/proxysql/releases

먼저 환경을 살펴보겠습니다.

MHA가 구축되었습니다.

master：172.16.16.35:3306slave：172.16.16.35:3307slave：172.16.16.34:3307

MHA 관리자는 172.16.16.34에 있고 구성 파일은 다음과 같습니다.

[root@localhost bin]# cat /etc/masterha/app1.cnf
[server default]
manager_log=/var/log/mha/app1/manager.log
manager_workdir=/var/log/mha/app1.log
master_binlog_dir=/home/mysql/db3306/log/master_ip_failover_script=/usr/local/bin/master_ip_failover
master_ip_online_change_script=/usr/local/bin/master_ip_online_change
password=123456ping_interval=1remote_workdir=/tmp
repl_password=123456repl_user=root
report_script=/usr/local/bin/send_report
shutdown_script=""ssh_user=root
user=root
[server1]
hostname=172.16.16.35port=3306[server2]
candidate_master=1check_repl_delay=0hostname=172.16.16.34port=3306[server3]
hostname=172.16.16.35port=3307

이제 이러한 MHA 환경을 기반으로 읽기-쓰기 분리를 구축합니다.

1: 172.16.16.34에 배포할 ProxySQL 소프트웨어를 설치합니다.

[root@localhost bin]# sudo yum install http://www.percona.com/downloads/percona-release/redhat/0.1-4/percona-release-0.1-4.noarch.rpm[root@localhost bin]# yum install proxysql

마지막으로 다음 팁이 있습니다.

Installed:
proxysql.x86_64 0:1.3.7-1.1.el6
Complete!

설치가 완료되었습니다. 그런 다음 특정 파일을 확인하십시오.

[root@localhost bin]# find / -name proxysql/var/lib/proxysql/var/run/proxysql/etc/rc.d/init.d/proxysql/usr/bin/proxysql

ProxySQL이 실제로 성공적으로 설치되었음을 확인했습니다.

2: ProxySQL 구성 시작

구성 파일을 확인하세요.

[root@localhost bin]# cat /etc/proxysql-admin.cnf
# proxysql admin interface credentials.
export PROXYSQL_USERNAME="admin"export PROXYSQL_PASSWORD="admin"export PROXYSQL_HOSTNAME="localhost"export PROXYSQL_PORT="6032"
 # PXC admin credentials for connecting to pxc-cluster-node.
export CLUSTER_USERNAME="admin"export CLUSTER_PASSWORD="admin"export CLUSTER_HOSTNAME="localhost"export CLUSTER_PORT="3306"
 # proxysql monitoring user. proxysql admin script will create this user in pxc to monitor pxc-nodes.
export MONITOR_USERNAME="monitor"export MONITOR_PASSWORD="monit0r"
 # Application user to connect to pxc-node through proxysql
export CLUSTER_APP_USERNAME="proxysql_user"export CLUSTER_APP_PASSWORD="passw0rd"
 # ProxySQL read/write hostgroup
export WRITE_HOSTGROUP_ID="10"export READ_HOSTGROUP_ID="11"
 # ProxySQL read/write configuration mode.
export MODE="singlewrite"

시작 up :

[root@localhost bin]# proxysql-admin --config-file=/etc/proxysql-admin.cnf --enable
This script will assist with configuring ProxySQL (currently only Percona XtraDB cluster in combination with ProxySQL is supported)
ProxySQL read/write configuration mode is singlewrite
ProxySQL is not running; please start the proxysql service

이제 ProxySQL의 라우팅이 시작되었으므로 ProxySQL 서비스를 시작하라는 메시지가 표시됩니다

[root@localhost bin]# service proxy
proxy proxysql proxysql-admin proxysql_galera_checker proxysql_node_monitor
[root@localhost bin]# service proxysql start
Starting ProxySQL: DONE![root@localhost bin]# mysql -uadmin -padmin -h127.0.0.1 -P6032
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1Server version: 5.7.14 (ProxySQL Admin Module)
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>

성공적으로 로그인되었음을 확인할 수 있습니다. 여기서 설명하고 싶은 것은 기본 구성 파일입니다:

[root@localhost bin]# find / -name proxysql.cnf/etc/proxysql.cnf

다음으로 ProxySQL 구성을 시작합니다:

[root@localhost bin]# mysql -uadmin -padmin -h127.0.0.1 -P6032
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2Server version: 5.7.14 (ProxySQL Admin Module)
 
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
 
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
 
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
 
mysql> show databases;+-----+---------+-------------------------------+
| seq | name | file |
+-----+---------+-------------------------------+
| 0 | main | |
| 2 | disk | /var/lib/proxysql/proxysql.db |
| 3 | stats | |
| 4 | monitor | |
+-----+---------+-------------------------------+4 rows in set (0.00 sec)
 
mysql> use admin
Database changed
mysql> show tables;+--------------------------------------+
| tables |
+--------------------------------------+
| global_variables |
| mysql_collations |
| mysql_query_rules |
| mysql_replication_hostgroups |
| mysql_servers |
| mysql_users |
| runtime_global_variables |
| runtime_mysql_query_rules |
| runtime_mysql_replication_hostgroups |
| runtime_mysql_servers |
| runtime_mysql_users |
| runtime_scheduler |
| scheduler |
+--------------------------------------+13 rows in set (0.00 sec)

아래에 마스터-슬레이브 정보를 추가합니다:

mysql> insert into mysql_servers(hostgroup_id,hostname,port,weight,max_connections,max_replication_lag,comment) values(100,'172.16.16.35',3306,1,1000,10,'test');
Query OK, 1 row affected (0.00 sec)
mysql> insert into mysql_servers(hostgroup_id,hostname,port,weight,max_connections,max_replication_lag,comment) values(101,'172.16.16.34',3306,1,1000,10,'test');
Query OK, 1 row affected (0.00 sec)
mysql> insert into mysql_servers(hostgroup_id,hostname,port,weight,max_connections,max_replication_lag,comment) values(101,'172.16.16.35',3307,1,1000,10,'test');
Query OK, 1 row affected (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 |
+--------------+--------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| 100 | 172.16.16.35 | 3306 | ONLINE | 1 | 0 | 1000 | 10 | 0 | 0 | test |
| 101 | 172.16.16.34 | 3306 | ONLINE | 1 | 0 | 1000 | 10 | 0 | 0 | test |
| 101 | 172.16.16.35 | 3307 | ONLINE | 1 | 0 | 1000 | 10 | 0 | 0 | test |
+--------------+--------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+3 rows in set (0.00 sec)

그런 다음 읽기 및 쓰기 매핑을 추가합니다(주로 MHA 백엔드가 전환될 때 ProxySQL이 자동으로 전환될 수 있도록 하기 위해):

mysql> insert into mysql_replication_hostgroups values(100,101,'masterha') ;
Query OK, 1 row affected (0.00 sec)
mysql> select * from mysql_replication_hostgroups;+------------------+------------------+----------+
| writer_hostgroup | reader_hostgroup | comment |
+------------------+------------------+----------+
| 100 | 101 | masterha |
+------------------+------------------+----------+1 row in set (0.00 sec)

ProxySQL에 대한 모니터링 계정 추가:

mysql> GRANT SUPER, REPLICATION CLIENT ON *.* TO 'proxysql'@'172.16.16.%' IDENTIFIED BY 'proxysql';
Query OK, 0 rows affected, 1 warning (0.09 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.10 sec)

모니터링 계정 구성(proxySQL 구성에서 구성됨):

mysql> set mysql-monitor_username='proxysql';
Query OK, 1 row affected (0.00 sec)
mysql> set mysql-monitor_password='proxysql';
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, 74 rows affected (0.02 sec)

PS: 때때로 Runtime_mysql_servers의 상태가 ONLINE이 아닌 경우 monitor.mysql_server_ping_log 테이블을 확인하여 특정 오류 정보를 볼 수 있습니다.

mysql> select * from monitor.mysql_server_ping_log;

그런 다음 프로그램 계정을 구성하고 루트: 123456 최고 권한을 사용하여 구성합니다.

mysql> insert into mysql_users(username,password,active,default_hostgroup,transaction_persistent) values('root','123456',1,100,1);
Query OK, 1 row affected (0.00 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 |
+----------+----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+
| root | 123456 | 1 | 0 | 100 | NULL | 0 | 1 | 0 | 1 | 1 | 10000 |
+----------+----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+1 row in set (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.08 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.03 sec)

다음, 라우팅 규칙 구성을 시작 :

mysql> INSERT INTO mysql_query_rules(active,match_pattern,destination_hostgroup,apply) VALUES(1,'^SELECT.*FOR UPDATE$',100,1);
Query OK, 1 row affected (0.01 sec)
mysql> INSERT INTO mysql_query_rules(active,match_pattern,destination_hostgroup,apply) VALUES(1,'^SELECT',101,1);
Query OK, 1 row affected (0.00 sec)
mysql> LOAD MYSQL QUERY RULES TO RUNTIME;
Query OK, 0 rows affected (0.00 sec)
mysql> SAVE MYSQL QUERY RULES TO DISK;
Query OK, 0 rows affected (0.04 sec)

지금까지 구성이 완료되었습니다. 3 : 테스트 읽기 및 쓰기 분리 172 .16.16.35 ProxySQL 포트 6033에 연결하고 간단한 선택 작업을 수행합니다.

[root@localhost ~]# mysql -uroot -p123456 -h172.16.16.34 -P6033
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 22Server version: 5.7.14 (ProxySQL)
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> select * from maxiangqian.test;+-----+------+
| id | name |
+-----+------+
| 1 | qq |
| 2 | qq |
| 4 | aa |
| 11 | a |
| 111 | a |
+-----+------+5 rows in set (0.04 sec)

그런 다음 확인해 보세요.

mysql> select * from stats_mysql_query_digest;

읽기와 쓰기가 분리되어 있음을 알 수 있습니다. 완료되었습니다.

mysql> select @@server_id;+-------------+
| @@server_id |
+-------------+
| 353307 |
+-------------+1 row in set (0.01 sec)

서버 ID가 슬레이브 데이터베이스 172.16.16.35:3307로 라우팅되었는지 확인하세요.

업데이트 테스트:

mysql> select * from maxiangqian.test for update;+-----+------+
| id | name |
+-----+------+
| 1 | qq |
| 2 | qq |
| 4 | aa |
| 11 | a |
| 111 | a |
+-----+------+5 rows in set (0.00 sec)

다시 확인

mysql> select * from stats_mysql_query_digest;

이 자동으로 기본 데이터베이스로 라우팅되었습니다.

이제 ProxySQL+MySQL MHA 읽기 및 쓰기 분리 테스트가 완료되었습니다. . . 계속하려면

5: ProxySQL 및 간단한 명령에 대한 생각

ProxySQL은 위의 방법을 통해 읽기-쓰기 분리를 구현할 수 있는데, 정말 이 방법이 문제가 없나요? 주문 상태를 쿼리하는 등 매우 높은 실시간 성능을 요구하는 일부 SQL인 경우에는 슬레이브 데이터베이스 BUG가 나타납니다. 터미널에서 이러한 매개변수를 제어하도록 선택할 수 있습니다. ProxySQL은 로드 밸런서로만 사용됩니다. ProxySQL에 대한 여러 계정을 생성합니다. 하나는 읽기-쓰기이고 다른 하나는 읽기 전용입니다. 그런 다음 프로그램은 읽기와 쓰기의 분리를 구현합니다.

ProxySQL은 RUNTIME, MEMORY 및 DISK라는 세 가지 계층에서 설계되고 실행됩니다.

RUNTIME은 global_variables, mysql_servers, mysql_users, mysql_query_rules를 포함하여 현재 유효한 ProxySQL 구성을 나타냅니다. 여기의 구성은 직접 수정할 수 없으며 다음 레이어에서 로드해야 합니다.

MEMORY는 일반적으로 mysql 명령줄에서 수정되는 기본 구성입니다. 이는 메모리에 있는 SQLite 데이터베이스의 이미지로 간주할 수 있습니다.

DISK / CONFIG FILE은 일반적으로 $(DATADIR에 있습니다. )/proxysql.db, 다시 시작한 후 하드 드라이브에서 로드됩니다. /etc/proxysql.cnf 파일은 첫 번째 초기화 중에만 사용됩니다. 그 이후에도 수신 포트를 수정하려면 관리 명령줄에서 수정한 다음 하드 디스크에 저장해야 합니다. 일반적인 명령:

LOAD MYSQL SERVERS TO RUNTIME -- 수정된 구성을 적용합니다. 즉, MEMORY에서 매개변수를 로드합니다. 이는 LOAD MYSQL USERS FROM MEMORY와 동일합니다. FROM은 비교적 간단합니다. 상위 레이어, TO는 이 레이어에서 특정 레이어까지를 의미합니다. 예를 들어 앞서 MySQL 모니터링 계정을 설정했지만 변수를 저장하고 적용하려면 LOAD 및 SAVE를 실행해야 합니다.

위 내용은 사용할 구성에서 ProxySQL 읽기-쓰기 분리의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!