proxysql 主从复制读写分离配置过程记录
发表于：2025-11-08 作者：千家信息网编辑
千家信息网最后更新 2025年11月08日，1、环境信息软件GitHub地址： https://github.com/sysown/proxysql/软件官网：https://proxysql.com/系统版本：[root@12c proxys
千家信息网最后更新 2025年11月08日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安全错误
数据库的锁怎样保障安全
人脸数据库的搜集
接入网络技术支撑具体做啥
英文介绍网络安全
华为4年软件开发工程师年薪
软件开发维护保修协议
杭州音视频软件开发
足疗仪软件开发公司
网络安全 活动总结
对网络安全事件的反思
数据库系统一般是由
电脑做微信小程序服务器
北京科技大学知名互联网校友
游戏软件开发客户分析
服务器虚拟化安全性
网络共享数据库
太原专业做软件开发的
深圳网络安全三级建设公司
服务器测试工程师有哪些证可以考
服务器.
gta4 服务器
云服务器外网映射端口
后段开发学什么数据库
数据库中最常见的三种关系运算
网络安全的etf代码
软件工程数据库技术考试资料
太原网络安全工资高
佛山支付软件开发常见问题
吉安高性价比服务器哪里比较好
中国网络安全大赛最强俱乐部
数据库和应用服务器
