proxysql 主从复制读写分离配置过程记录
发表于：2026-11-02 作者：千家信息网编辑
千家信息网最后更新 2026年11月02日，1、环境信息软件GitHub地址： https://github.com/sysown/proxysql/软件官网：https://proxysql.com/系统版本：[root@12c proxys
千家信息网最后更新 2026年11月02日proxysql 主从复制读写分离配置过程记录
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 <3、docker 启动容器
[root@12c proxysql]# docker run -p 3306:3306 --name mysqlmaster -e MYSQL_ROOT_PASSWORD=123456 -d docker.io/centos/mysql-57-centos75dd187415052bc46d8daa8b8045f1337c2e1fe4f139d5e6ef6a29be1e408547d[root@12c proxysql]# docker run -p 3307:3306 --name mysqlslave -e MYSQL_ROOT_PASSWORD=123456 -d docker.io/centos/mysql-57-centos71cfc67f4144b026bae1539be5abe313756c5595b8cf7be5223f80e1a7782f311[root@12c proxysql]# docker ps -aCONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES1cfc67f4144b docker.io/centos/mysql-57-centos7 "container-entrypo..." 9 seconds ago Up 8 seconds 0.0.0.0:3307->3306/tcp mysqlslave5dd187415052 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 bashbash-4.2# vi /etc/my.cnf.d/rep.cnf "/etc/opt/rh/rh-mysql57/my.cnf.d/rep.cnf" [New] 5L, 48C writtenbash-4.2# cat /etc/my.cnf.d/rep.cnf [mysqld]server-id=1log-binbinlog-format=rowbash-4.2# exitexit[root@12c proxysql]# docker exec -it --user root 1 bashbash-4.2# vi /etc/my.cnf.d/rep.cnf "/etc/opt/rh/rh-mysql57/my.cnf.d/rep.cnf" [New] 5L, 49C writtenbash-4.2# cat /etc/my.cnf.d/rep.cnf [mysqld]server-id=11log-binbinlog-format=rowbash-4.2# exitexit[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 -aCONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES1cfc67f4144b docker.io/centos/mysql-57-centos7 "container-entrypo..." 4 minutes ago Exited (0) 44 seconds ago mysqlslave5dd187415052 docker.io/centos/mysql-57-centos7 "container-entrypo..." 4 minutes ago Exited (0) 44 seconds ago mysqlmaster[root@12c proxysql]# docker start 55[root@12c proxysql]# docker start 11[root@12c proxysql]# docker ps -a CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES1cfc67f4144b docker.io/centos/mysql-57-centos7 "container-entrypo..." 4 minutes ago Up 4 seconds 0.0.0.0:3307->3306/tcp mysqlslave5dd187415052 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 -p123456Welcome to the MariaDB monitor. Commands end with ; or \g.Your MySQL connection id is 2Server 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>>>exitBye6、配置主从复制
[root@12c proxysql]# mysql -h227.0.0.1 -p123456 -P3307Welcome to the MariaDB monitor. Commands end with ; or \g.Your MySQL connection id is 3Server 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\GERROR 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 1root@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: 0Master_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>>>exitBye7、创建proxysql_test库验证主从同步情况
[root@12c proxysql]# mysql -h227.0.0.1 -p123456Welcome to the MariaDB monitor. Commands end with ; or \g.Your MySQL connection id is 5Server 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>>>exitBye[root@12c proxysql]# mysql -h227.0.0.1 -p123456 -P3307Welcome to the MariaDB monitor. Commands end with ; or \g.Your MySQL connection id is 6Server 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>>>exitBye8、启动proxysql检查目前配置情况，因为我没有配置，都为空
[root@12c proxysql]# /etc/init.d/proxysql startStarting ProxySQL: 2019-07-18 14:24:37 [INFO] Using config file /etc/proxysql.cnf2019-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 1Server 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>>>exitBye10、创建监控用户并配置proxysql
[root@12c proxysql]# mysql -h227.0.0.1 -p123456Welcome to the MariaDB monitor. Commands end with ; or \g.Your MySQL connection id is 22Server 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>>>exitBye[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 2Server 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_hostgroupsCreate 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>>>exitBye[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 40Server 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>>>exitBye[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 3Server 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>>>exitBye[root@12c proxysql]# mysql -h227.0.0.1 -p123456 Welcome to the MariaDB monitor. Commands end with ; or \g.Your MySQL connection id is 44Server 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>>>exitBye[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 4Server 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>>>exitBye13、配置读写分离路由规则，配置路由前都是用用户的默认规则
[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 8Server 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>>>exitBye[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 |+-------------+
配置
主从
用户
规则
情况
环境
路由
软件
必要
信息
地址
客户
客户端
容器
数据
版本
系统
同步
检查
监控
数据库的安全要保护哪些东西
数据库安全各自的含义是什么
生产安全数据库录入
数据库的安全性及管理
数据库安全策略包含哪些
海淀数据库安全审计系统
建立农村房屋安全信息数据库
易用的数据库客户端支持安全管理
连接数据库失败ssl安全错误
数据库的锁怎样保障安全
咖啡豆
互联网新科技
重生之互联网科技帝国
科技互联网产品
金融科技 互联网金融
奇酷互联网络科技
小马过河互联网科技
互联网金融科技平台
汇久创客互联网科技
睿民互联网科技
友谊互联网络科技
互联网科技实习生岗位职责
现代互联网科技股
世界互联网科技公司市值排名
荆州市昕露互联网络科技有限公司
上海钢联属于互联网科技股吗
容联科技互联网电商
恒生科技指数 中概互联网
广东猫王互联网科技有限公司电话
宁波允成互联网科技有限公司
上海喔噻互联网科技有限公司
互联网科技财经
互联网科技未来
高科技与互联网
互联网与未来科技
消费医药和互联网科技
深圳互联网科技的发展
半导体行业属于互联网高科技类吗
互联网科技手抄报图片
山东蛋芒互联网科技有限公司
