配置文件：
[root@localhost ~]# tail -1 /etc/my.cnf
server_id=1
[root@localhost ~]# systemctl restart mysqld

備份：
[root@localhost ~]# mysqldump --opt -B db1 it school > db.sql

授權(quán)用戶：
mysql> create user rep@'172.16.%.%' identified with mysql_native_password by '123456';
mysql> grant replication slave on *.* to rep@'172.16.%.%';

mysql> show master status;
+---------------+----------+--------------+------------------+--------------------------------------------------------------------------------------+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                                                                    |
+---------------+----------+--------------+------------------+--------------------------------------------------------------------------------------+
| binlog.000015 |      756 |              |                  | 2db179b1-cf96-11ee-8b00-2e6ff2d90c84:1-14,
d09e219c-cec9-11ee-baf1-2e6ff2d90c84:1-46 |
+---------------+----------+--------------+------------------+--------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

配置文件：
[root@localhost ~]# tail -1 /etc/my.cnf
server_id=2
[root@localhost ~]# systemctl restart mysqld

[root@localhost ~]# tail -1 /etc/my.cnf
server_id=3
[root@localhost ~]# systemctl restart mysqld

還原主庫(kù)備份：
# scp db.sql 172.16.100.22:/root/
# scp db.sql 172.16.100.23:/root/

mysql -uroot -pMySQL@123 < db.sql

mysql> show slave status \G
*************************** 1. row ***************************
               Slave_IO_State: Connecting to source
                  Master_Host: 192.168.150.21
                  Master_User: rep
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: binlog.000003
          Read_Master_Log_Pos: 157
               Relay_Log_File: localhost-relay-bin.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File: binlog.000003
             Slave_IO_Running: Connecting
            Slave_SQL_Running: Yes
...
              Last_IO_Error: error connecting to master 'rep@192.168.150.21:3306' - retry-time: 60 retries: 1 message: Can't connect to MySQL server on '192.168.150.21:3306' (111)


[root@localhost ~]# mysqladmin -urep -p123456 -h192.168.150.21 ping
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
mysqld is alive


# 查詢?nèi)罩?
[root@localhost ~]# tail /var/log/mysql/mysqld.log
...
2023-12-02T03:17:53.631813Z 5 [ERROR] [MY-010584] [Repl] Slave I/O for channel '': error connecting to master 'rep@192.168.150.21:3306' - retry-time: 60 retries: 3 message: Authentication plugin 'caching_sha2_password' reported error: Authentication requires secure connection. Error_code: MY-002061

mysql> alter user 'rep'@'192.168.150.%' identified with mysql_native_password by '123456';
Query OK, 0 rows affected (0.01 sec)

mysql> stop slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> change master to
    -> master_host='172.16.100.21',
    -> master_user='rep',
    -> master_password='123456',
    -> master_log_file='binlog.000015',
    -> master_log_pos=756,
    -> get_master_public_key=1;
Query OK, 0 rows affected, 9 warnings (0.00 sec)

mysql> start slave;
Query OK, 0 rows affected, 1 warning (0.01 sec)

#[可選] 0（默認(rèn)）表示讀寫（主機(jī)），1表示只讀（從機(jī)）
read-only=0
#設(shè)置日志文件保留的時(shí)長(zhǎng)，單位是秒
binlog_expire_logs_seconds=6000
#控制單個(gè)二進(jìn)制日志大小。此參數(shù)的最大和默認(rèn)值是1GB
max_binlog_size=200M
#[可選]設(shè)置不要復(fù)制的數(shù)據(jù)庫(kù)
binlog-ignore-db=test
#[可選]設(shè)置需要復(fù)制的數(shù)據(jù)庫(kù),默認(rèn)全部記錄。
binlog-do-db=需要復(fù)制的主數(shù)據(jù)庫(kù)名字
#[可選]設(shè)置binlog格式
binlog_format=STATEMENT

開(kāi)啟gtid
gtid_mode=ON
enforce-gtid-consistency=ON

mysql> CHANGE MASTER TO
     >     MASTER_HOST = host,
     >     MASTER_PORT = port,
     >     MASTER_USER = user,
     >     MASTER_PASSWORD = password,
     >     MASTER_AUTO_POSITION = 1;

Or from MySQL 8.0.23:

mysql> CHANGE REPLICATION SOURCE TO
     >     SOURCE_HOST = host,
     >     SOURCE_PORT = port,
     >     SOURCE_USER = user,
     >     SOURCE_PASSWORD = password,
     >     SOURCE_AUTO_POSITION = 1;
     
mysql> START SLAVE;
Or from MySQL 8.0.22:
mysql> START REPLICA;

查看監(jiān)控信息:
Last_SQL_Error: Error 'Can't create database 'db4'; database exists' on query. Default database: 'db4'. Query: 'create database db4'

Retrieved_Gtid_Set: 71bfa52e-4aae-11e9-ab8c-000c293b577e:1-3
Executed_Gtid_Set:  71bfa52e-4aae-11e9-ab8c-000c293b577e:1-2,
7ca4a2b7-4aae-11e9-859d-000c298720f6:1

注入空事務(wù)的方法：

stop slave;
set gtid_next='99279e1e-61b7-11e9-a9fc-000c2928f5dd:3';
begin;commit;
set gtid_next='AUTOMATIC';
    
這里的xxxxx:N 也就是你的slave sql thread報(bào)錯(cuò)的GTID，或者說(shuō)是你想要跳過(guò)的GTID。
最好的解決方案：重新構(gòu)建主從環(huán)境

# mysql -uadmin -padmin -h127.0.0.1 -P6032
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.5.30 (ProxySQL Admin Module)
mysql> show databases;
+-----+---------------+-------------------------------------+
| seq | name          | file                                |
+-----+---------------+-------------------------------------+
| 0   | main          |                                     |
| 2   | disk          | /var/lib/proxysql/proxysql.db       |
| 3   | stats         |                                     |
| 4   | monitor       |                                     |
| 5   | stats_history | /var/lib/proxysql/proxysql_stats.db |
+-----+---------------+-------------------------------------+
5 rows in set (0.00 sec)

# 配置yum源
cat > /etc/yum.repos.d/proxysql.repo << EOF
[proxysql]
name=ProxySQL YUM repository
baseurl=https://repo.proxysql.com/ProxySQL/proxysql-2.5.x/centos/\$releasever
gpgcheck=1
gpgkey=https://repo.proxysql.com/ProxySQL/proxysql-2.5.x/repo_pub_key
EOF

# 安裝proxysql
yum install -y proxysql

[root@proxy ~]# systemctl enable --now proxysql

# 管理員登錄
[root@proxy ~]# mysql -uadmin -padmin -h 127.0.0.1 -P 6032
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.

MySQL [(none)]> show databases;
+-----+---------------+-------------------------------------+
| seq | name          | file                                |
+-----+---------------+-------------------------------------+
| 0   | main          |                                     |
| 2   | disk          | /var/lib/proxysql/proxysql.db       |
| 3   | stats         |                                     |
| 4   | monitor       |                                     |
| 5   | stats_history | /var/lib/proxysql/proxysql_stats.db |
+-----+---------------+-------------------------------------+
5 rows in set (0.00 sec)

可見(jiàn)有五個(gè)庫(kù)： main、disk、stats 、monitor 和 stats_history
main: 內(nèi)存配置數(shù)據(jù)庫(kù)，即 MEMORY，表里存放后端 db 實(shí)例、用戶驗(yàn)證、路由規(guī)則等信息。main 庫(kù)中有如下信息：

MySQL [(none)]> show tables from main;
+----------------------------------------------------+
| tables                                             |
+----------------------------------------------------+
| coredump_filters                                   |
| global_variables                                   |
| mysql_aws_aurora_hostgroups                        |
| mysql_collations                                   |
| mysql_firewall_whitelist_rules                     |
| mysql_firewall_whitelist_sqli_fingerprints         |
| mysql_firewall_whitelist_users                     |
| mysql_galera_hostgroups                            |
| mysql_group_replication_hostgroups                 |
| mysql_hostgroup_attributes                         |
| mysql_query_rules                                  |
| mysql_query_rules_fast_routing                     |
| mysql_replication_hostgroups                       |
| mysql_servers                                      |
| mysql_users                                        |
| proxysql_servers                                   |
| restapi_routes                                     |
| runtime_checksums_values                           |
| runtime_coredump_filters                           |
| runtime_global_variables                           |
| runtime_mysql_aws_aurora_hostgroups                |
| runtime_mysql_firewall_whitelist_rules             |
| runtime_mysql_firewall_whitelist_sqli_fingerprints |
| runtime_mysql_firewall_whitelist_users             |
| runtime_mysql_galera_hostgroups                    |
| runtime_mysql_group_replication_hostgroups         |
| runtime_mysql_hostgroup_attributes                 |
| runtime_mysql_query_rules                          |
| runtime_mysql_query_rules_fast_routing             |
| runtime_mysql_replication_hostgroups               |
| runtime_mysql_servers                              |
| runtime_mysql_users                                |
| runtime_proxysql_servers                           |
| runtime_restapi_routes                             |
| runtime_scheduler                                  |
| scheduler                                          |
+----------------------------------------------------+
36 rows in set (0.00 sec)

庫(kù)下的主要表：
mysql_servers: 后端可以連接 MySQL 服務(wù)器的列表
mysql_users: 配置后端數(shù)據(jù)庫(kù)的賬號(hào)和監(jiān)控的賬號(hào)。
mysql_query_rules: 指定 Query 路由到后端不同服務(wù)器的規(guī)則列表。

注： 表名以 runtime_開(kāi)頭的表示 ProxySQL 當(dāng)前運(yùn)行的配置內(nèi)容，不能通過(guò) DML 語(yǔ)句修改。

只能修改對(duì)應(yīng)的不以 runtime 開(kāi)頭的表，然后 “LOAD” 使其生效，“SAVE” 使其存到硬盤以供下次重啟加載。
disk ：持久化的磁盤的配置
stats： 統(tǒng)計(jì)信息的匯總
monitor：一些監(jiān)控的收集信息，比如數(shù)據(jù)庫(kù)的健康狀態(tài)等
stats_history: 這個(gè)庫(kù)是 ProxySQL 收集的有關(guān)其內(nèi)部功能的歷史指標(biāo)

create user 'monitor'@'172.16.%.%' identified with mysql_native_password by 'Monitor@123.com';
grant all privileges on *.* to 'monitor'@'172.16.%.%' with grant option;

#proxysql 的對(duì)外訪問(wèn)賬戶
create user 'proxysql'@'172.16.%.%' identified with mysql_native_password by '123456';
grant all privileges on *.* to 'proxysql'@'172.16.%.%' with grant option;

MySQL [(none)]> 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','read_only|innodb_read_only','read_only&innodb_read_only')) NOT NULL DEFAULT 'read_only',
    comment VARCHAR NOT NULL DEFAULT '', UNIQUE (reader_hostgroup))
1 row in set (0.00 sec)

注：writer_hostgroup 和reader_hostgroup 寫組和讀組都要大于等于0且不能相同

MySQL [(none)]> insert into mysql_replication_hostgroups (writer_hostgroup,reader_hostgroup,comment) values (1,0,'proxy');
Query OK, 1 row affected (0.00 sec)

MySQL [(none)]> load mysql servers to runtime;
Query OK, 0 rows affected (0.01 sec)

MySQL [(none)]> save mysql servers to disk;
Query OK, 0 rows affected (0.02 sec)

注意：ProxySQL會(huì)根據(jù)server的read_only的取值將服務(wù)器進(jìn)行分組。read_only=0的server，master被分到編號(hào)為1的寫組，read_only=1的server，slave則分到編號(hào)為0的讀組

MySQL [(none)]> select * from mysql_replication_hostgroups;
+------------------+------------------+------------+---------+
| writer_hostgroup | reader_hostgroup | check_type | comment |
+------------------+------------------+------------+---------+
| 1                | 0                | read_only  | proxy   |
+------------------+------------------+------------+---------+
1 row in set (0.00 sec)

MySQL [(none)]> insert into mysql_servers(hostgroup_id,hostname,port) values (1,'172.16.100.21',3306);
Query OK, 1 row affected (0.00 sec)

MySQL [(none)]> insert into mysql_servers(hostgroup_id,hostname,port) values (0,'172.16.100.22',3306);
Query OK, 1 row affected (0.00 sec)

MySQL [(none)]> insert into mysql_servers(hostgroup_id,hostname,port) values (0,'172.16.100.23',3306);
Query OK, 1 row affected (0.00 sec)

MySQL [(none)]> load mysql servers to runtime;
Query OK, 0 rows affected (0.01 sec)

MySQL [(none)]> save mysql servers to disk;
Query OK, 0 rows affected (0.00 sec)

MySQL [(none)]> select * from mysql_servers;
+--------------+----------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname       | port | gtid_port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+----------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| 1            | 192.168.150.21 | 3306 | 0         | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
| 0            | 192.168.150.22 | 3306 | 0         | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
| 0            | 192.168.150.23 | 3306 | 0         | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
+--------------+----------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
3 rows in set (0.00 sec)

MySQL [(none)]> use monitor
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MySQL [monitor]> set mysql-monitor_username='monitor';
Query OK, 1 row affected (0.00 sec)

MySQL [monitor]> set mysql-monitor_password='Monitor@123.com';
Query OK, 1 row affected (0.00 sec)

上面這兩句是修改變量的方式還可以在main庫(kù)下面用sql語(yǔ)句方式修改
在main下修改：
MySQL [monitor]> use main
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MySQL [main]> UPDATE global_variables SET variable_value='monitor' WHERE variable_name='mysql-monitor_username';
Query OK, 1 row affected (0.00 sec)

MySQL [main]> UPDATE global_variables SET variable_value='Monitor@123.com' WHERE variable_name='mysql-monitor_password';
Query OK, 1 row affected (0.00 sec)

修改后，保存到runtime和disk
MySQL [monitor]> load mysql variables to runtime;
MySQL [monitor]> save mysql variables to disk;

查看監(jiān)控賬號(hào)【ProxySQL】
SELECT * FROM global_variables WHERE variable_name LIKE 'mysql-monitor_%';
//也可以這樣快速定位
MySQL [(none)]> select @@mysql-monitor_username;
+--------------------------+
| @@mysql-monitor_username |
+--------------------------+
| monitor                  |
+--------------------------+
1 row in set (0.00 sec)
MySQL [(none)]> select @@mysql-monitor_password;
+--------------------------+
| @@mysql-monitor_password |
+--------------------------+
| Monitor@123.com          |
+--------------------------+
1 row in set (0.00 sec)


MySQL [(none)]> select * from monitor.mysql_server_connect_log;
+----------------+------+------------------+-------------------------+---------------+
| hostname       | port | time_start_us    | connect_success_time_us | connect_error |
+----------------+------+------------------+-------------------------+---------------+
| 192.168.150.21 | 3306 | 1701659547601785 | 2126                    | NULL          |
| 192.168.150.23 | 3306 | 1701659548266990 | 1883                    | NULL          |
| 192.168.150.22 | 3306 | 1701659548932828 | 1626                    | NULL          |
| 192.168.150.21 | 3306 | 1701659607601353 | 1672                    | NULL          |
| 192.168.150.22 | 3306 | 1701659608066386 | 1649                    | NULL          |
| 192.168.150.23 | 3306 | 1701659608531392 | 1962                    | NULL          |
| 192.168.150.21 | 3306 | 1701659667601720 | 1283                    | NULL          |
| 192.168.150.23 | 3306 | 1701659668394216 | 2033                    | NULL          |
| 192.168.150.22 | 3306 | 1701659669186458 | 1350                    | NULL          |
+----------------+------+------------------+-------------------------+---------------+
9 rows in set (0.00 sec)

驗(yàn)證監(jiān)控信息，ProxySQL 監(jiān)控模塊的指標(biāo)都保存在monitor庫(kù)的log表中 
以下是連接是否正常的監(jiān)控，對(duì)connect指標(biāo)的監(jiān)控 ，在前面可能會(huì)有很多connect_error，這是因?yàn)闆](méi)有配置監(jiān)控信息時(shí)的錯(cuò)誤，配置后如果connect_error的結(jié)果為NULL則表示正常

MySQL [(none)]> select * from mysql_server_ping_log limit 10;
+----------------+------+------------------+----------------------+------------+
| hostname       | port | time_start_us    | ping_success_time_us | ping_error |
+----------------+------+------------------+----------------------+------------+
| 192.168.150.21 | 3306 | 1701659547602344 | 460                  | NULL       |
| 192.168.150.23 | 3306 | 1701659547604095 | 245                  | NULL       |
| 192.168.150.22 | 3306 | 1701659547604690 | 258                  | NULL       |
| 192.168.150.23 | 3306 | 1701659557601548 | 471                  | NULL       |
| 192.168.150.21 | 3306 | 1701659557601738 | 313                  | NULL       |
| 192.168.150.22 | 3306 | 1701659557601713 | 349                  | NULL       |
| 192.168.150.22 | 3306 | 1701659567602161 | 572                  | NULL       |
| 192.168.150.23 | 3306 | 1701659567602361 | 405                  | NULL       |
| 192.168.150.21 | 3306 | 1701659567602318 | 460                  | NULL       |
| 192.168.150.22 | 3306 | 1701659577602909 | 434                  | NULL       |
+----------------+------+------------------+----------------------+------------+
10 rows in set (0.00 sec)

MySQL [(none)]> select * from mysql_server_read_only_log limit 5;
+----------------+------+------------------+-----------------+-----------+-------+
| hostname       | port | time_start_us    | success_time_us | read_only | error |
+----------------+------+------------------+-----------------+-----------+-------+
| 192.168.150.23 | 3306 | 1701659547606174 | 373             | 1         | NULL  |
| 192.168.150.22 | 3306 | 1701659547606768 | 634             | 1         | NULL  |
| 192.168.150.21 | 3306 | 1701659547606639 | 858             | 0         | NULL  |
| 192.168.150.21 | 3306 | 1701659549101574 | 657             | 0         | NULL  |
| 192.168.150.22 | 3306 | 1701659549101863 | 422             | 1         | NULL  |
+----------------+------+------------------+-----------------+-----------+-------+
5 rows in set (0.00 sec)

Monitor 模塊就會(huì)開(kāi)始監(jiān)控后端的read_only值，當(dāng)監(jiān)控到read_only值，就會(huì)按照read_only的值將某些節(jié)點(diǎn)自動(dòng)移到讀寫組 
一些監(jiān)控的狀態(tài)斗志在log相關(guān)，都在monitor庫(kù)下面的 global_variables 變量。

MySQL [(none)]> show create table mysql_users\G
*************************** 1. row ***************************
       table: mysql_users
Create Table: CREATE TABLE mysql_users (
    username VARCHAR NOT NULL,
    password VARCHAR,
    active INT CHECK (active IN (0,1)) NOT NULL DEFAULT 1,
    use_ssl INT CHECK (use_ssl IN (0,1)) NOT NULL DEFAULT 0,
    default_hostgroup INT NOT NULL DEFAULT 0,
    default_schema VARCHAR,
    schema_locked INT CHECK (schema_locked IN (0,1)) NOT NULL DEFAULT 0,
    transaction_persistent INT CHECK (transaction_persistent IN (0,1)) NOT NULL DEFAULT 1,
    fast_forward INT CHECK (fast_forward IN (0,1)) NOT NULL DEFAULT 0,
    backend INT CHECK (backend IN (0,1)) NOT NULL DEFAULT 1,
    frontend INT CHECK (frontend IN (0,1)) NOT NULL DEFAULT 1,
    max_connections INT CHECK (max_connections >=0) NOT NULL DEFAULT 10000,
    attributes VARCHAR CHECK (JSON_VALID(attributes) OR attributes = '') NOT NULL DEFAULT '',
    comment VARCHAR NOT NULL DEFAULT '',
    PRIMARY KEY (username, backend),
    UNIQUE (username, frontend))
1 row in set (0.00 sec)

insert into mysql_users (username,password,default_hostgroup,transaction_persistent) values ('proxysql','123456',1,1);
 
load mysql users to runtime;
save mysql users to disk;
MySQL [(none)]> select * from mysql_users\G
*************************** 1. row ***************************
              username: proxysql
              password: 123456
                active: 1
               use_ssl: 0
     default_hostgroup: 1
        default_schema: NULL
         schema_locked: 0
transaction_persistent: 1
          fast_forward: 0
               backend: 1
              frontend: 1
       max_connections: 10000
            attributes:
               comment:
1 row in set (0.00 sec)

注：transaction_persistent 如果為1，則一個(gè)完整的SQL只可能路由到一個(gè)節(jié)點(diǎn)；這點(diǎn)非常重要，主要解決這種情況：一個(gè)事務(wù)有混合的讀操作和寫操作組成，事務(wù)未提交前，如果事務(wù)中的讀操作和寫操作路由到不同節(jié)點(diǎn)，那么讀取到的結(jié)果必然是臟數(shù)據(jù)。所以一般情況下，該值應(yīng)該設(shè)置為1，尤其是業(yè)務(wù)中使用到事務(wù)機(jī)制的情況（默認(rèn)為0）
mysql_users 表有不少字段，最主要的三個(gè)字段username,password,default_hostgroup 
A.username: 前端鏈接ProxySQL ，以及ProxySQL 將SQL 語(yǔ)句路由給MySQL所使用的的用戶名
B.password：用戶名對(duì)應(yīng)的密碼，?？梢允敲魑拿艽a，也可以是hash密碼。如果想使用hash密碼，可以先在某個(gè)MySQL節(jié)點(diǎn)上執(zhí)行select password(PASSWORD)，然后將加密結(jié)果復(fù)制到該字段。 
C.default_hostgroup：該用戶名默認(rèn)的路由目標(biāo)。例如，指定root用戶的該字段值為1時(shí)，則使用 proxysql 用戶發(fā)送的SQL語(yǔ)句默認(rèn)情況下將路由到hostgroup_id=1 組中的某個(gè)

[root@slave1 ~]# mysql -h172.16.100.24 -uproxysql -p'123456' -P 6033
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.5.30 (ProxySQL)

Copyright (c) 2000, 2022, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| db1                |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.01 sec)

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

#通過(guò)proxysql用戶，創(chuàng)建一個(gè)keme庫(kù)
mysql> create database keme;
Query OK, 1 row affected (0.00 sec)

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

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| db1                |
| information_schema |
| keme               |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
6 rows in set (0.01 sec)

insert into mysql_query_rules(rule_id,active,match_pattern,destination_hostgroup,apply) values(1,1,'^select .* for update$',1,1);

insert into mysql_query_rules(rule_id,active,match_pattern,destination_hostgroup,apply) values(2,1,'^select',0,1);

load mysql query rules to runtime;
save mysql query rules to disk;

[root@slave1 ~]# mysql -uproxysql -p123456 -h 172.16.100.24 -P 6033 -e "select @@server_id"
mysql: [Warning] Using a password on the command line interface can be insecure.
+-------------+
| @@server_id |
+-------------+
|          3 |
+-------------+
[root@slave1 ~]# mysql -uproxysql -p123456 -h 172.16.100.24 -P 6033 -e "select @@server_id"
mysql: [Warning] Using a password on the command line interface can be insecure.
+-------------+
| @@server_id |
+-------------+
|          2 |
+-------------+
[root@slave1 ~]# mysql -uproxysql -p123456 -h 172.16.100.24 -P 6033 -e "begin;select @@server_id commit;"
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------+
| commit |
+--------+
|     1 |
+--------+