proxysql 主从复制读写分离配置过程记录
发表于：2025-01-21 作者：千家信息网编辑
千家信息网最后更新 2025年01月21日，1、环境信息软件GitHub地址： https://github.com/sysown/proxysql/软件官网：https://proxysql.com/系统版本：[root@12c proxys
千家信息网最后更新 2025年01月21日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安全错误
数据库的锁怎样保障安全
怀旧服怎么弄服务器人数
肇庆app软件开发
数据库中的模块怎样修改
我的世界ios服务器地址
贵州网络安全联合实验室
长沙做网络安全的
使用数据库系统的特点
火车头数据库入库配置织梦
oracle数据库使用风险分析
千年苍鹰服务器
服务器管理员密码不能修改
网吧服务器默认浏览器
无线网络安全问题的解决方法
服务器文件连接
完美世界服务器延迟高怎么办
支付宝服务器忙请稍后重试
多群核数据库截面共振处理
b树数据库
webstar服务器电源
qt软件开发工具
网络安全相关的英语单词
剪歌软件开发
乐山软件开发行业标准
软件开发书籍电子版
关于网络安全这些你知道吗
网络安全业务落地
github 代理服务器
服务器关机命令power
南京直播软件开发解决方案
未成年人网络安全宣讲
