1、环境信息
软件GitHub地址： https://github.com/sysown/proxysql/ 软件官网：https://proxysql.com/ 系统版本： [root@12c proxysql]# cat /etc/redhat-release CentOS Linux release 7.6.1810 (Core) 主从环境dockers +---------------+------+-----------+ | hostname | port | status | +---------------+------+-----------+ | 192.168.56.11 | 3306 | master | | 192.168.56.11 | 3307 | slave | +---------------+------+-----------+2、proxysql 安装和配置
cat <<EOF | tee /etc/yum.repos.d/proxysql.repo [proxysql_repo] name= ProxySQL YUM repository baseurl=https://repo.proxysql.com/ProxySQL/proxysql-2.0.x/centos/\$releasever gpgcheck=1 gpgkey=https://repo.proxysql.com/ProxySQL/repo_pub_key EOF yum install proxysql OR yum install proxysql-version /etc/init.d/proxysql start export MYSQL_PS1="\\u@\\h [\\d] \\r:\\m:\\s>>>"3、docker 启动容器
[root@12c proxysql]# docker run -p 3306:3306 --name mysqlmaster -e MYSQL_ROOT_PASSWORD=123456 -d docker.io/centos/mysql-57-centos7 5dd187415052bc46d8daa8b8045f1337c2e1fe4f139d5e6ef6a29be1e408547d [root@12c proxysql]# docker run -p 3307:3306 --name mysqlslave -e MYSQL_ROOT_PASSWORD=123456 -d docker.io/centos/mysql-57-centos7 1cfc67f4144b026bae1539be5abe313756c5595b8cf7be5223f80e1a7782f311 [root@12c proxysql]# docker ps -a CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES 1cfc67f4144b docker.io/centos/mysql-57-centos7 "container-entrypo..." 9 seconds ago Up 8 seconds 0.0.0.0:3307->3306/tcp mysqlslave 5dd187415052 docker.io/centos/mysql-57-centos7 "container-entrypo..." 20 seconds ago Up 18 seconds 0.0.0.0:3306->3306/tcp mysqlmaster4、增加主从必要配置
[root@12c proxysql]# docker exec -it --user root 5 bash bash-4.2# vi /etc/my.cnf.d/rep.cnf "/etc/opt/rh/rh-mysql57/my.cnf.d/rep.cnf" [New] 5L, 48C written bash-4.2# cat /etc/my.cnf.d/rep.cnf [mysqld] server-id=1 log-bin binlog-format=row bash-4.2# exit exit [root@12c proxysql]# docker exec -it --user root 1 bash bash-4.2# vi /etc/my.cnf.d/rep.cnf "/etc/opt/rh/rh-mysql57/my.cnf.d/rep.cnf" [New] 5L, 49C written bash-4.2# cat /etc/my.cnf.d/rep.cnf [mysqld] server-id=11 log-bin binlog-format=row bash-4.2# exit exit [root@12c proxysql]# systemctl restart docker [root@12c proxysql]# mysql -h227.0.0.1 -p123456 ERROR 2003 (HY000): Can not connect to MySQL server on '127.0.0.1' (111 "Connection refused") [root@12c proxysql]# docker ps -a CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES 1cfc67f4144b docker.io/centos/mysql-57-centos7 "container-entrypo..." 4 minutes ago Exited (0) 44 seconds ago mysqlslave 5dd187415052 docker.io/centos/mysql-57-centos7 "container-entrypo..." 4 minutes ago Exited (0) 44 seconds ago mysqlmaster [root@12c proxysql]# docker start 5 5 [root@12c proxysql]# docker start 1 1 [root@12c proxysql]# docker ps -a CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES 1cfc67f4144b docker.io/centos/mysql-57-centos7 "container-entrypo..." 4 minutes ago Up 4 seconds 0.0.0.0:3307->3306/tcp mysqlslave 5dd187415052 docker.io/centos/mysql-57-centos7 "container-entrypo..." 4 minutes ago Up 8 seconds 0.0.0.0:3306->3306/tcp mysqlmaster5、配置主从复制用户
[root@12c proxysql]# mysql -h227.0.0.1 -p123456 Welcome to the MariaDB monitor. Commands end with ; or \g. Your MySQL connection id is 2 Server version: 5.7.24-log MySQL Community Server (GPL) Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. root@127.0.0.1 [(none)] 02:01:13>>>show master logs; +-------------------------+-----------+ | Log_name | File_size | +-------------------------+-----------+ | 5dd187415052-bin.000001 | 1035 | | 5dd187415052-bin.000002 | 154 | +-------------------------+-----------+ 2 rows in set (0.00 sec) root@127.0.0.1 [(none)] 02:01:20>>>reset master ; Query OK, 0 rows affected (0.32 sec) root@127.0.0.1 [(none)] 02:01:29>>> GRANT REPLICATION SLAVE ON *.* TO 'repluser'@'%' IDENTIFIED BY '123456'; Query OK, 0 rows affected, 1 warning (0.28 sec) root@127.0.0.1 [(none)] 02:03:06>>>flush privileges; Query OK, 0 rows affected (0.04 sec) root@127.0.0.1 [(none)] 02:03:13>>>exit Bye6、配置主从复制
[root@12c proxysql]# mysql -h227.0.0.1 -p123456 -P3307 Welcome to the MariaDB monitor. Commands end with ; or \g. Your MySQL connection id is 3 Server version: 5.7.24-log MySQL Community Server (GPL) Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. root@127.0.0.1 [(none)] 02:03:17>>> CHANGE MASTER TO MASTER_HOST='192.168.56.11', MASTER_USER='repluser',MASTER_PASSWORD='123456',MASTER_PORT=3306,MASTER_LOG_FILE='5dd187415052-bin.000001',MASTER_LOG_POS=154; Query OK, 0 rows affected, 2 warnings (0.34 sec) root@127.0.0.1 [(none)] 02:03:21>>>start slave; Query OK, 0 rows affected (0.30 sec) root@127.0.0.1 [(none)] 02:03:35>>>show salve status\G ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'salve status' at line 1 root@127.0.0.1 [(none)] 02:03:42>>>show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.56.11 Master_User: repluser Master_Port: 3306 Connect_Retry: 60 Master_Log_File: 5dd187415052-bin.000001 Read_Master_Log_Pos: 585 Relay_Log_File: 1cfc67f4144b-relay-bin.000002 Relay_Log_Pos: 758 Relay_Master_Log_File: 5dd187415052-bin.000001 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 585 Relay_Log_Space: 972 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0 Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 1 Master_UUID: be5e882c-a920-11e9-9acb-0242ac110002 Master_Info_File: /var/lib/mysql/data/master.info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates Master_Retry_Count: 86400 Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: Executed_Gtid_Set: Auto_Position: 0 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: 1 row in set (0.00 sec) root@127.0.0.1 [(none)] 02:03:50>>>exit Bye7、创建proxysql_test库验证主从同步情况
[root@12c proxysql]# mysql -h227.0.0.1 -p123456 Welcome to the MariaDB monitor. Commands end with ; or \g. Your MySQL connection id is 5 Server version: 5.7.24-log MySQL Community Server (GPL) Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. root@127.0.0.1 [(none)] 02:04:01>>>create database proxysql_test; Query OK, 1 row affected (0.28 sec) root@127.0.0.1 [(none)] 02:04:15>>>exit Bye [root@12c proxysql]# mysql -h227.0.0.1 -p123456 -P3307 Welcome to the MariaDB monitor. Commands end with ; or \g. Your MySQL connection id is 6 Server version: 5.7.24-log MySQL Community Server (GPL) Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. root@127.0.0.1 [(none)] 02:04:24>>>show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | proxysql_test | | sys | +--------------------+ 5 rows in set (0.00 sec) root@127.0.0.1 [(none)] 02:04:28>>>exit Bye8、启动proxysql检查目前配置情况，因为我没有配置，都为空
[root@12c proxysql]# /etc/init.d/proxysql start Starting ProxySQL: 2019-07-18 14:24:37 [INFO] Using config file /etc/proxysql.cnf 2019-07-18 14:24:37 [INFO] SSL keys/certificates found in datadir (/var/lib/proxysql): loading them. DONE! [root@12c proxysql]# mysql -u admin -padmin -h 127.0.0.1 -P6032 Welcome to the MariaDB monitor. Commands end with ; or \g. Your MySQL connection id is 1 Server version: 5.5.30 (ProxySQL Admin Module) Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. admin@127.0.0.1 [(none)] 02:25:09>>>select * from mysql_server_read_only_log; Empty set (0.00 sec) admin@127.0.0.1 [(none)] 02:26:20>>>select * from mysql_server_replication_lag_log; Empty set (0.00 sec) admin@127.0.0.1 [(none)] 02:26:40>>>SELECT * FROM mysql_servers; Empty set (0.00 sec) admin@127.0.0.1 [(none)] 02:28:11>>> SELECT * FROM mysql_replication_hostgroups; Empty set (0.00 sec) admin@127.0.0.1 [(none)] 02:28:46>>>SELECT * FROM mysql_users; Empty set (0.00 sec) admin@127.0.0.1 [(none)] 02:29:16>>>SELECT * FROM mysql_query_rules; Empty set (0.00 sec)9、新增server数据
admin@127.0.0.1 [(none)] 02:29:35>>> INSERT INTO mysql_servers(hostgroup_id,hostname,port) VALUES (10,'192.168.56.11',3306); Query OK, 1 row affected (0.00 sec) admin@127.0.0.1 [(none)] 02:30:44>>> INSERT INTO mysql_servers(hostgroup_id,hostname,port) VALUES (10,'192.168.56.11',3307); Query OK, 1 row affected (0.00 sec) admin@127.0.0.1 [(none)] 02:31:22>>>SELECT * FROM mysql_servers; +--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+ | hostgroup_id | hostname | port | gtid_port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment | +--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+ | 10 | 192.168.56.11 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | | | 10 | 192.168.56.11 | 3307 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | | +--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+ 2 rows in set (0.00 sec) admin@127.0.0.1 [(none)] 02:31:31>>>exit Bye10、创建监控用户并配置proxysql
[root@12c proxysql]# mysql -h227.0.0.1 -p123456 Welcome to the MariaDB monitor. Commands end with ; or \g. Your MySQL connection id is 22 Server version: 5.7.24-log MySQL Community Server (GPL) Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. root@127.0.0.1 [(none)] 02:33:05>>> GRANT REPLICATION SLAVE ON *.* TO 'proxysqlmon'@'%' IDENTIFIED BY '123456'; Query OK, 0 rows affected, 1 warning (0.28 sec) root@127.0.0.1 [(none)] 02:33:14>>>flush privileges; Query OK, 0 rows affected (0.02 sec) root@127.0.0.1 [(none)] 02:33:23>>>exit Bye [root@12c proxysql]# mysql -u admin -padmin -h 127.0.0.1 -P6032 Welcome to the MariaDB monitor. Commands end with ; or \g. Your MySQL connection id is 2 Server version: 5.5.30 (ProxySQL Admin Module) Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. admin@127.0.0.1 [(none)] 02:34:00>>>SET mysql-monitor_username='proxysqlmon'; Query OK, 1 row affected (0.00 sec) admin@127.0.0.1 [(none)] 02:34:12>>>SET mysql-monitor_password='123456'; Query OK, 1 row affected (0.00 sec) admin@127.0.0.1 [(none)] 02:34:20>>>LOAD MYSQL VARIABLES TO RUNTIME; Query OK, 0 rows affected (0.00 sec) admin@127.0.0.1 [(none)] 02:34:35>>>SAVE MYSQL VARIABLES TO DISK; Query OK, 116 rows affected (0.28 sec) admin@127.0.0.1 [(none)] 02:34:49>>>select * from mysql_server_connect_log; +---------------+------+------------------+-------------------------+------------------------------------------------------------------------+ | hostname | port | time_start_us | connect_success_time_us | connect_error | +---------------+------+------------------+-------------------------+------------------------------------------------------------------------+ | 192.168.56.11 | 3307 | 1563431498030552 | 0 | Access denied for user 'monitor'@'192.168.56.11' (using password: YES) | | 192.168.56.11 | 3306 | 1563431498668916 | 0 | Access denied for user 'monitor'@'192.168.56.11' (using password: YES) | | 192.168.56.11 | 3306 | 1563431558031708 | 0 | Access denied for user 'monitor'@'192.168.56.11' (using password: YES) | | 192.168.56.11 | 3307 | 1563431559067995 | 0 | Access denied for user 'monitor'@'192.168.56.11' (using password: YES) | | 192.168.56.11 | 3306 | 1563431618031624 | 0 | Access denied for user 'monitor'@'192.168.56.11' (using password: YES) | | 192.168.56.11 | 3307 | 1563431618808593 | 0 | Access denied for user 'monitor'@'192.168.56.11' (using password: YES) | | 192.168.56.11 | 3307 | 1563431676331614 | 2304 | NULL | | 192.168.56.11 | 3306 | 1563431677521700 | 2621 | NULL | +---------------+------+------------------+-------------------------+------------------------------------------------------------------------+ 8 rows in set (0.00 sec) admin@127.0.0.1 [(none)] 02:34:59>>>select * from mysql_server_ping_log; +---------------+------+------------------+----------------------+------------------------------------------------------------------------+ | hostname | port | time_start_us | ping_success_time_us | ping_error | +---------------+------+------------------+----------------------+------------------------------------------------------------------------+ | 192.168.56.11 | 3306 | 1563431448313821 | 0 | Access denied for user 'monitor'@'192.168.56.11' (using password: YES) | | 192.168.56.11 | 3306 | 1563431458086145 | 0 | Access denied for user 'monitor'@'192.168.56.11' (using password: YES) | | 192.168.56.11 | 3306 | 1563431648134014 | 0 | Access denied for user 'monitor'@'192.168.56.11' (using password: YES) | | 192.168.56.11 | 3307 | 1563431648333984 | 0 | Access denied for user 'monitor'@'192.168.56.11' (using password: YES) | | 192.168.56.11 | 3306 | 1563431658135211 | 0 | Access denied for user 'monitor'@'192.168.56.11' (using password: YES) | | 192.168.56.11 | 3307 | 1563431658286566 | 0 | Access denied for user 'monitor'@'192.168.56.11' (using password: YES) | | 192.168.56.11 | 3306 | 1563431668157058 | 0 | Access denied for user 'monitor'@'192.168.56.11' (using password: YES) | | 192.168.56.11 | 3307 | 1563431668264603 | 0 | Access denied for user 'monitor'@'192.168.56.11' (using password: YES) | | 192.168.56.11 | 3307 | 1563431676386597 | 627 | NULL | | 192.168.56.11 | 3306 | 1563431676506906 | 554 | NULL | | 192.168.56.11 | 3306 | 1563431686387739 | 670 | NULL | | 192.168.56.11 | 3307 | 1563431686558685 | 868 | NULL | | 192.168.56.11 | 3306 | 1563431696387964 | 609 | NULL | | 192.168.56.11 | 3307 | 1563431696495978 | 173 | NULL | | 192.168.56.11 | 3307 | 1563431706388009 | 623 | NULL | | 192.168.56.11 | 3306 | 1563431706559451 | 331 | NULL | +---------------+------+------------------+----------------------+------------------------------------------------------------------------+ 53 rows in set (0.00 sec)11、配置读写分离组，proxysql会按照规则自动修改server的hostgroup_id
admin@127.0.0.1 [(none)] 02:35:15>>>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), check_type VARCHAR CHECK (LOWER(check_type) IN ('read_only','innodb_read_only','super_read_only')) NOT NULL DEFAULT 'read_only', comment VARCHAR NOT NULL DEFAULT '', UNIQUE (reader_hostgroup)) 1 row in set (0.00 sec) admin@127.0.0.1 [(none)] 02:36:16>>>INSERT INTO mysql_replication_hostgroups VALUES(10,20,"read_only","test replication with read and write separation"); Query OK, 1 row affected (0.00 sec) admin@127.0.0.1 [(none)] 02:39:39>>>SELECT * FROM mysql_replication_hostgroups; +------------------+------------------+------------+-------------------------------------------------+ | writer_hostgroup | reader_hostgroup | check_type | comment | +------------------+------------------+------------+-------------------------------------------------+ | 10 | 20 | read_only | test replication with read and write separation | +------------------+------------------+------------+-------------------------------------------------+ 1 row in set (0.00 sec) admin@127.0.0.1 [(none)] 02:39:59>>>LOAD MYSQL SERVERS TO RUNTIME; Query OK, 0 rows affected (0.01 sec) admin@127.0.0.1 [(none)] 02:40:12>>>SAVE MYSQL SERVERS TO DISK; Query OK, 0 rows affected (0.05 sec) admin@127.0.0.1 [(none)] 02:40:25>>>SELECT * FROM mysql_servers; +--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+ | hostgroup_id | hostname | port | gtid_port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment | +--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+ | 10 | 192.168.56.11 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | | | 10 | 192.168.56.11 | 3307 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | | +--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+ 2 rows in set (0.00 sec) admin@127.0.0.1 [(none)] 02:40:34>>>exit Bye [root@12c proxysql]# mysql -h227.0.0.1 -p123456 -P3307 Welcome to the MariaDB monitor. Commands end with ; or \g. Your MySQL connection id is 40 Server version: 5.7.24-log MySQL Community Server (GPL) Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. root@127.0.0.1 [(none)] 02:40:40>>>set global read_only=1; Query OK, 0 rows affected (0.00 sec) root@127.0.0.1 [(none)] 02:40:54>>>exit Bye [root@12c proxysql]# mysql -u admin -padmin -h 127.0.0.1 -P6032 Welcome to the MariaDB monitor. Commands end with ; or \g. Your MySQL connection id is 3 Server version: 5.5.30 (ProxySQL Admin Module) Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. admin@127.0.0.1 [(none)] 02:41:05>>>SELECT * FROM mysql_servers; +--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+ | hostgroup_id | hostname | port | gtid_port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment | +--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+ | 10 | 192.168.56.11 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | | | 20 | 192.168.56.11 | 3307 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | | +--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+ 2 rows in set (0.00 sec)12、配置proxysql 中用于客户端访问的用户
admin@127.0.0.1 [(none)] 02:41:09>>>exit Bye [root@12c proxysql]# mysql -h227.0.0.1 -p123456 Welcome to the MariaDB monitor. Commands end with ; or \g. Your MySQL connection id is 44 Server version: 5.7.24-log MySQL Community Server (GPL) Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. root@127.0.0.1 [(none)] 02:43:43>>> GRANT ALL ON *.* TO 'rootuser'@'%' IDENTIFIED BY '123456'; Query OK, 0 rows affected, 1 warning (0.00 sec) root@127.0.0.1 [(none)] 02:43:50>>>flush privileges; Query OK, 0 rows affected (0.27 sec) root@127.0.0.1 [(none)] 02:44:03>>>exit Bye [root@12c proxysql]# mysql -u admin -padmin -h 127.0.0.1 -P6032 Welcome to the MariaDB monitor. Commands end with ; or \g. Your MySQL connection id is 4 Server version: 5.5.30 (ProxySQL Admin Module) Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. admin@127.0.0.1 [(none)] 02:44:17>>> INSERT INTO mysql_users(username,password,default_hostgroup) VALUES ('rootuser','123456',10); Query OK, 1 row affected (0.00 sec) admin@127.0.0.1 [(none)] 02:44:25>>>SELECT * FROM mysql_users; +----------+----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+---------+ | username | password | active | use_ssl | default_hostgroup | default_schema | schema_locked | transaction_persistent | fast_forward | backend | frontend | max_connections | comment | +----------+----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+---------+ | rootuser | 123456 | 1 | 0 | 10 | NULL | 0 | 1 | 0 | 1 | 1 | 10000 | | +----------+----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+---------+ 1 row in set (0.00 sec) admin@127.0.0.1 [(none)] 02:45:27>>>LOAD MYSQL USERS TO RUNTIME; Query OK, 0 rows affected (0.00 sec) admin@127.0.0.1 [(none)] 02:45:54>>>SAVE MYSQL USERS TO DISK; Query OK, 0 rows affected (0.03 sec) admin@127.0.0.1 [(none)] 02:45:58>>>exit Bye13、配置读写分离路由规则，配置路由前都是用用户的默认规则
[root@12c proxysql]# mysql -urootuser -p123456 -h292.168.56.11 -P6033 -e "SELECT @@server_id;" +-------------+ | @@server_id | +-------------+ | 1 | +-------------+ [root@12c proxysql]# mysql -urootuser -p123456 -h292.168.56.11 -P6033 -e "BEGIN;SELECT @@server_id;commit;" +-------------+ | @@server_id | +-------------+ | 1 | +-------------+ [root@12c proxysql]# mysql -u admin -padmin -h 127.0.0.1 -P6032 Welcome to the MariaDB monitor. Commands end with ; or \g. Your MySQL connection id is 8 Server version: 5.5.30 (ProxySQL Admin Module) Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. admin@127.0.0.1 [(none)] 02:48:27>>>INSERT INTO mysql_query_rules(rule_id,active,match_digest,destination_hostgroup,apply) VALUES (1,1,'^SELECT.*FOR UPDATE$',10,1),(2,1,'^SELECT',20,1); Query OK, 2 rows affected (0.00 sec) admin@127.0.0.1 [(none)] 02:48:32>>>SELECT * FROM mysql_query_rules; +---------+--------+----------+------------+--------+-------------+------------+------------+--------+----------------------+---------------+----------------------+--------------+---------+-----------------+-----------------------+-----------+--------------------+---------------+-----------+---------+---------+-------+-------------------+----------------+------------------+-----------+--------+-------------+-----------+---------------------+-----+-------+---------+ | rule_id | active | username | schemaname | flagIN | client_addr | proxy_addr | proxy_port | digest | match_digest | match_pattern | negate_match_pattern | re_modifiers | flagOUT | replace_pattern | destination_hostgroup | cache_ttl | cache_empty_result | cache_timeout | reconnect | timeout | retries | delay | next_query_flagIN | mirror_flagOUT | mirror_hostgroup | error_msg | OK_msg | sticky_conn | multiplex | gtid_from_hostgroup | log | apply | comment | +---------+--------+----------+------------+--------+-------------+------------+------------+--------+----------------------+---------------+----------------------+--------------+---------+-----------------+-----------------------+-----------+--------------------+---------------+-----------+---------+---------+-------+-------------------+----------------+------------------+-----------+--------+-------------+-----------+---------------------+-----+-------+---------+ | 1 | 1 | NULL | NULL | 0 | NULL | NULL | NULL | NULL | ^SELECT.*FOR UPDATE$ | NULL | 0 | CASELESS | NULL | NULL | 10 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 1 | NULL | | 2 | 1 | NULL | NULL | 0 | NULL | NULL | NULL | NULL | ^SELECT | NULL | 0 | CASELESS | NULL | NULL | 20 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 1 | NULL | +---------+--------+----------+------------+--------+-------------+------------+------------+--------+----------------------+---------------+----------------------+--------------+---------+-----------------+-----------------------+-----------+--------------------+---------------+-----------+---------+---------+-------+-------------------+----------------+------------------+-----------+--------+-------------+-----------+---------------------+-----+-------+---------+ 2 rows in set (0.00 sec) admin@127.0.0.1 [(none)] 02:48:50>>>LOAD MYSQL QUERY RULES TO RUNTIME; Query OK, 0 rows affected (0.00 sec) admin@127.0.0.1 [(none)] 02:49:07>>>SAVE MYSQL QUERY RULES TO DISK; Query OK, 0 rows affected (0.31 sec) admin@127.0.0.1 [(none)] 02:49:18>>>exit Bye [root@12c proxysql]# mysql -urootuser -p123456 -h292.168.56.11 -P6033 -e "BEGIN;SELECT @@server_id;commit;" +-------------+ | @@server_id | +-------------+ | 1 | +-------------+ [root@12c proxysql]# mysql -urootuser -p123456 -h292.168.56.11 -P6033 -e "SELECT @@server_id;" +-------------+ | @@server_id | +-------------+ | 11 | +-------------+免责声明：本站发布的内容（图片、视频和文字）以原创、转载和分享为主，文章观点不代表本网站立场，如果涉及侵权请联系站长邮箱：is@yisu.com进行举报，并提供相关证据，一经查实，将立刻删除涉嫌侵权内容。
