当前位置:Gxlcms > 数据库问题 > proxysql 主从复制读写分离配置过程记录

proxysql 主从复制读写分离配置过程记录

时间:2021-07-01 10:21:17 帮助过:14人阅读

1、环境信息

软件GitHub地址: https://github.com/sysown/proxysql/
软件官网:https://proxysql.com/
系统版本:
[root@12c proxysql]# cat /etc/redhat-release 
CentOS Linux release 7.6.1810 (Core) 
主从环境dockers
+---------------+------+-----------+
| hostname      | port | status    |
+---------------+------+-----------+
| 192.168.56.11 | 3306 | master    |
| 192.168.56.11 | 3307 | slave     |
+---------------+------+-----------+

2、proxysql 安装和配置

cat <<EOF | tee /etc/yum.repos.d/proxysql.repo
[proxysql_repo]
name= ProxySQL YUM repository
baseurl=https://repo.proxysql.com/ProxySQL/proxysql-2.0.x/centos/\$releasever
gpgcheck=1
gpgkey=https://repo.proxysql.com/ProxySQL/repo_pub_key
EOF

yum install proxysql OR yum install proxysql-version
/etc/init.d/proxysql start
export MYSQL_PS1="\\u@\\h [\\d] \\r:\\m:\\s>>>"

3、docker 启动容器

[root@12c proxysql]# docker run -p 3306:3306 --name mysqlmaster -e MYSQL_ROOT_PASSWORD=123456 -d docker.io/centos/mysql-57-centos7
5dd187415052bc46d8daa8b8045f1337c2e1fe4f139d5e6ef6a29be1e408547d
[root@12c proxysql]# docker run -p 3307:3306 --name mysqlslave -e MYSQL_ROOT_PASSWORD=123456 -d docker.io/centos/mysql-57-centos7
1cfc67f4144b026bae1539be5abe313756c5595b8cf7be5223f80e1a7782f311
[root@12c proxysql]# docker ps -a
CONTAINER ID        IMAGE                               COMMAND                  CREATED             STATUS              PORTS                    NAMES
1cfc67f4144b        docker.io/centos/mysql-57-centos7   "container-entrypo..."   9 seconds ago       Up 8 seconds        0.0.0.0:3307->3306/tcp   mysqlslave
5dd187415052        docker.io/centos/mysql-57-centos7   "container-entrypo..."   20 seconds ago      Up 18 seconds       0.0.0.0:3306->3306/tcp   mysqlmaster

4、增加主从必要配置

[root@12c proxysql]# docker exec -it --user root 5 bash
bash-4.2# vi /etc/my.cnf.d/rep.cnf 
"/etc/opt/rh/rh-mysql57/my.cnf.d/rep.cnf" [New] 5L, 48C written
bash-4.2# cat /etc/my.cnf.d/rep.cnf   

[mysqld]
server-id=1
log-bin
binlog-format=row
bash-4.2# exit
exit
[root@12c proxysql]# docker exec -it --user root 1 bash
bash-4.2# vi /etc/my.cnf.d/rep.cnf 
"/etc/opt/rh/rh-mysql57/my.cnf.d/rep.cnf" [New] 5L, 49C written
bash-4.2# cat /etc/my.cnf.d/rep.cnf 

[mysqld]
server-id=11
log-bin
binlog-format=row
bash-4.2# exit
exit
[root@12c proxysql]# systemctl restart docker
[root@12c proxysql]# mysql -h127.0.0.1 -p123456                   
ERROR 2003 (HY000): Can not connect to MySQL server on ‘127.0.0.1‘ (111 "Connection refused")
[root@12c proxysql]# docker ps -a
CONTAINER ID        IMAGE                               COMMAND                  CREATED             STATUS                      PORTS               NAMES
1cfc67f4144b        docker.io/centos/mysql-57-centos7   "container-entrypo..."   4 minutes ago       Exited (0) 44 seconds ago                       mysqlslave
5dd187415052        docker.io/centos/mysql-57-centos7   "container-entrypo..."   4 minutes ago       Exited (0) 44 seconds ago                       mysqlmaster
[root@12c proxysql]# docker start 5
5
[root@12c proxysql]# docker start 1
1
[root@12c proxysql]# docker ps -a  
CONTAINER ID        IMAGE                               COMMAND                  CREATED             STATUS              PORTS                    NAMES
1cfc67f4144b        docker.io/centos/mysql-57-centos7   "container-entrypo..."   4 minutes ago       Up 4 seconds        0.0.0.0:3307->3306/tcp   mysqlslave
5dd187415052        docker.io/centos/mysql-57-centos7   "container-entrypo..."   4 minutes ago       Up 8 seconds        0.0.0.0:3306->3306/tcp   mysqlmaster

5、配置主从复制用户

[root@12c proxysql]# mysql -h127.0.0.1 -p123456
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.24-log MySQL Community Server (GPL)

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

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

root@127.0.0.1 [(none)] 02:01:13>>>show master logs;
+-------------------------+-----------+
| Log_name                | File_size |
+-------------------------+-----------+
| 5dd187415052-bin.000001 |      1035 |
| 5dd187415052-bin.000002 |       154 |
+-------------------------+-----------+
2 rows in set (0.00 sec)

root@127.0.0.1 [(none)] 02:01:20>>>reset master ;
Query OK, 0 rows affected (0.32 sec)

root@127.0.0.1 [(none)] 02:01:29>>> GRANT REPLICATION SLAVE ON *.* TO ‘repluser‘@‘%‘ IDENTIFIED BY ‘123456‘; 
Query OK, 0 rows affected, 1 warning (0.28 sec)

root@127.0.0.1 [(none)] 02:03:06>>>flush privileges;
Query OK, 0 rows affected (0.04 sec)

root@127.0.0.1 [(none)] 02:03:13>>>exit
Bye

6、配置主从复制

[root@12c proxysql]# mysql -h127.0.0.1 -p123456 -P3307
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.24-log MySQL Community Server (GPL)

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

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

root@127.0.0.1 [(none)] 02:03:17>>> CHANGE MASTER TO MASTER_HOST=‘192.168.56.11‘, MASTER_USER=‘repluser‘,MASTER_PASSWORD=‘123456‘,MASTER_PORT=3306,MASTER_LOG_FILE=‘5dd187415052-bin.000001‘,MASTER_LOG_POS=154;
Query OK, 0 rows affected, 2 warnings (0.34 sec)

root@127.0.0.1 [(none)] 02:03:21>>>start slave;
Query OK, 0 rows affected (0.30 sec)

root@127.0.0.1 [(none)] 02:03:35>>>show salve status\G
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘salve status‘ at line 1
root@127.0.0.1 [(none)] 02:03:42>>>show slave status\G  
*************************** 1. row ***************************
            Slave_IO_State: Waiting for master to send event
                Master_Host: 192.168.56.11
                Master_User: repluser
                Master_Port: 3306
                Connect_Retry: 60
            Master_Log_File: 5dd187415052-bin.000001
        Read_Master_Log_Pos: 585
            Relay_Log_File: 1cfc67f4144b-relay-bin.000002
                Relay_Log_Pos: 758
        Relay_Master_Log_File: 5dd187415052-bin.000001
            Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
            Replicate_Do_DB: 
        Replicate_Ignore_DB: 
        Replicate_Do_Table: 
    Replicate_Ignore_Table: 
    Replicate_Wild_Do_Table: 
Replicate_Wild_Ignore_Table: 
                Last_Errno: 0
                Last_Error: 
                Skip_Counter: 0
        Exec_Master_Log_Pos: 585
            Relay_Log_Space: 972
            Until_Condition: None
            Until_Log_File: 
                Until_Log_Pos: 0
        Master_SSL_Allowed: No
        Master_SSL_CA_File: 
        Master_SSL_CA_Path: 
            Master_SSL_Cert: 
            Master_SSL_Cipher: 
            Master_SSL_Key: 
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
            Last_SQL_Errno: 0
            Last_SQL_Error: 
Replicate_Ignore_Server_Ids: 
            Master_Server_Id: 1
                Master_UUID: be5e882c-a920-11e9-9acb-0242ac110002
            Master_Info_File: /var/lib/mysql/data/master.info
                    SQL_Delay: 0
        SQL_Remaining_Delay: NULL
    Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
        Master_Retry_Count: 86400
                Master_Bind: 
    Last_IO_Error_Timestamp: 
    Last_SQL_Error_Timestamp: 
            Master_SSL_Crl: 
        Master_SSL_Crlpath: 
        Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
        Replicate_Rewrite_DB: 
                Channel_Name: 
        Master_TLS_Version: 
1 row in set (0.00 sec)

root@127.0.0.1 [(none)] 02:03:50>>>exit
Bye

7、创建proxysql_test库验证主从同步情况

[root@12c proxysql]# mysql -h127.0.0.1 -p123456
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.7.24-log MySQL Community Server (GPL)

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

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

root@127.0.0.1 [(none)] 02:04:01>>>create database proxysql_test;
Query OK, 1 row affected (0.28 sec)

root@127.0.0.1 [(none)] 02:04:15>>>exit
Bye
[root@12c proxysql]# mysql -h127.0.0.1 -p123456 -P3307
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.7.24-log MySQL Community Server (GPL)

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

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

root@127.0.0.1 [(none)] 02:04:24>>>show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| proxysql_test      |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

root@127.0.0.1 [(none)] 02:04:28>>>exit
Bye

8、启动proxysql检查目前配置情况,因为我没有配置,都为空

[root@12c proxysql]# /etc/init.d/proxysql start
Starting ProxySQL: 2019-07-18 14:24:37 [INFO] Using config file /etc/proxysql.cnf
2019-07-18 14:24:37 [INFO] SSL keys/certificates found in datadir (/var/lib/proxysql): loading them.
DONE!
[root@12c proxysql]# mysql -u admin -padmin -h 127.0.0.1 -P6032 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.30 (ProxySQL Admin Module)

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

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

admin@127.0.0.1 [(none)] 02:25:09>>>select * from mysql_server_read_only_log;
Empty set (0.00 sec)

admin@127.0.0.1 [(none)] 02:26:20>>>select * from mysql_server_replication_lag_log;
Empty set (0.00 sec)

admin@127.0.0.1 [(none)] 02:26:40>>>SELECT * FROM mysql_servers;
Empty set (0.00 sec)

admin@127.0.0.1 [(none)] 02:28:11>>> SELECT * FROM mysql_replication_hostgroups;
Empty set (0.00 sec)

admin@127.0.0.1 [(none)] 02:28:46>>>SELECT * FROM mysql_users;
Empty set (0.00 sec)

admin@127.0.0.1 [(none)] 02:29:16>>>SELECT * FROM mysql_query_rules;
Empty set (0.00 sec)

9、新增server数据

admin@127.0.0.1 [(none)] 02:29:35>>> INSERT INTO mysql_servers(hostgroup_id,hostname,port) VALUES (10,‘192.168.56.11‘,3306);
Query OK, 1 row affected (0.00 sec)

admin@127.0.0.1 [(none)] 02:30:44>>>  INSERT INTO mysql_servers(hostgroup_id,hostname,port) VALUES (10,‘192.168.56.11‘,3307);
Query OK, 1 row affected (0.00 sec)

admin@127.0.0.1 [(none)] 02:31:22>>>SELECT * FROM mysql_servers;
+--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname      | port | gtid_port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| 10           | 192.168.56.11 | 3306 | 0         | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
| 10           | 192.168.56.11 | 3307 | 0         | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
+--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
2 rows in set (0.00 sec)

admin@127.0.0.1 [(none)] 02:31:31>>>exit
Bye

10、创建监控用户并配置proxysql

[root@12c proxysql]# mysql -h127.0.0.1 -p123456
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MySQL connection id is 22
Server version: 5.7.24-log MySQL Community Server (GPL)

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

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

root@127.0.0.1 [(none)] 02:33:05>>>  GRANT REPLICATION SLAVE ON *.* TO ‘proxysqlmon‘@‘%‘ IDENTIFIED BY ‘123456‘;
Query OK, 0 rows affected, 1 warning (0.28 sec)

root@127.0.0.1 [(none)] 02:33:14>>>flush privileges;
Query OK, 0 rows affected (0.02 sec)

root@127.0.0.1 [(none)] 02:33:23>>>exit
Bye
[root@12c proxysql]# mysql -u admin -padmin -h 127.0.0.1 -P6032 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.5.30 (ProxySQL Admin Module)

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

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

admin@127.0.0.1 [(none)] 02:34:00>>>SET mysql-monitor_username=‘proxysqlmon‘;
Query OK, 1 row affected (0.00 sec)

admin@127.0.0.1 [(none)] 02:34:12>>>SET mysql-monitor_password=‘123456‘;
Query OK, 1 row affected (0.00 sec)

admin@127.0.0.1 [(none)] 02:34:20>>>LOAD MYSQL VARIABLES TO RUNTIME;
Query OK, 0 rows affected (0.00 sec)

admin@127.0.0.1 [(none)] 02:34:35>>>SAVE MYSQL VARIABLES TO DISK;
Query OK, 116 rows affected (0.28 sec)

admin@127.0.0.1 [(none)] 02:34:49>>>select * from mysql_server_connect_log;
+---------------+------+------------------+-------------------------+------------------------------------------------------------------------+
| hostname      | port | time_start_us    | connect_success_time_us | connect_error                                                          |
+---------------+------+------------------+-------------------------+------------------------------------------------------------------------+
| 192.168.56.11 | 3307 | 1563431498030552 | 0                       | Access denied for user ‘monitor‘@‘192.168.56.11‘ (using password: YES) |
| 192.168.56.11 | 3306 | 1563431498668916 | 0                       | Access denied for user ‘monitor‘@‘192.168.56.11‘ (using password: YES) |
| 192.168.56.11 | 3306 | 1563431558031708 | 0                       | Access denied for user ‘monitor‘@‘192.168.56.11‘ (using password: YES) |
| 192.168.56.11 | 3307 | 1563431559067995 | 0                       | Access denied for user ‘monitor‘@‘192.168.56.11‘ (using password: YES) |
| 192.168.56.11 | 3306 | 1563431618031624 | 0                       | Access denied for user ‘monitor‘@‘192.168.56.11‘ (using password: YES) |
| 192.168.56.11 | 3307 | 1563431618808593 | 0                       | Access denied for user ‘monitor‘@‘192.168.56.11‘ (using password: YES) |
| 192.168.56.11 | 3307 | 1563431676331614 | 2304                    | NULL                                                                   |
| 192.168.56.11 | 3306 | 1563431677521700 | 2621                    | NULL                                                                   |
+---------------+------+------------------+-------------------------+------------------------------------------------------------------------+
8 rows in set (0.00 sec)

admin@127.0.0.1 [(none)] 02:34:59>>>select * from mysql_server_ping_log;
+---------------+------+------------------+----------------------+------------------------------------------------------------------------+
| hostname      | port | time_start_us    | ping_success_time_us | ping_error                                                             |
+---------------+------+------------------+----------------------+------------------------------------------------------------------------+
| 192.168.56.11 | 3306 | 1563431448313821 | 0                    | Access denied for user ‘monitor‘@‘192.168.56.11‘ (using password: YES) |
| 192.168.56.11 | 3306 | 1563431458086145 | 0                    | Access denied for user  ‘monitor‘@‘192.168.56.11‘ (using password: YES) |
| 192.168.56.11 | 3306 | 1563431648134014 | 0                    | Access denied for user ‘monitor‘@‘192.168.56.11‘ (using password: YES) |
| 192.168.56.11 | 3307 | 1563431648333984 | 0                    | Access denied for user ‘monitor‘@‘192.168.56.11‘ (using password: YES) |
| 192.168.56.11 | 3306 | 1563431658135211 | 0                    | Access denied for user ‘monitor‘@‘192.168.56.11‘ (using password: YES) |
| 192.168.56.11 | 3307 | 1563431658286566 | 0                    | Access denied for user ‘monitor‘@‘192.168.56.11‘ (using password: YES) |
| 192.168.56.11 | 3306 | 1563431668157058 | 0                    | Access denied for user ‘monitor‘@‘192.168.56.11‘ (using password: YES) |
| 192.168.56.11 | 3307 | 1563431668264603 | 0                    | Access denied for user ‘monitor‘@‘192.168.56.11‘ (using password: YES) |
| 192.168.56.11 | 3307 | 1563431676386597 | 627                  | NULL                                                                   |
| 192.168.56.11 | 3306 | 1563431676506906 | 554                  | NULL                                                                   |
| 192.168.56.11 | 3306 | 1563431686387739 | 670                  | NULL                                                                   |
| 192.168.56.11 | 3307 | 1563431686558685 | 868                  | NULL                                                                   |
| 192.168.56.11 | 3306 | 1563431696387964 | 609                  | NULL                                                                   |
| 192.168.56.11 | 3307 | 1563431696495978 | 173                  | NULL                                                                   |
| 192.168.56.11 | 3307 | 1563431706388009 | 623                  | NULL                                                                   |
| 192.168.56.11 | 3306 | 1563431706559451 | 331                  | NULL                                                                   |
+---------------+------+------------------+----------------------+------------------------------------------------------------------------+
53 rows in set (0.00 sec)

11、配置读写分离组,proxysql会按照规则自动修改server的hostgroup_id

admin@127.0.0.1 [(none)] 02:35:15>>>show create table mysql_replication_hostgroups\G
*************************** 1. row ***************************
    table: mysql_replication_hostgroups
Create Table: CREATE TABLE mysql_replication_hostgroups (
    writer_hostgroup INT CHECK (writer_hostgroup>=0) NOT NULL PRIMARY KEY,
    reader_hostgroup INT NOT NULL CHECK (reader_hostgroup<>writer_hostgroup AND reader_hostgroup>=0),
    check_type VARCHAR CHECK (LOWER(check_type) IN (‘read_only‘,‘innodb_read_only‘,‘super_read_only‘)) NOT NULL DEFAULT ‘read_only‘,
    comment VARCHAR NOT NULL DEFAULT ‘‘, UNIQUE (reader_hostgroup))
1 row in set (0.00 sec)

admin@127.0.0.1 [(none)] 02:36:16>>>INSERT INTO mysql_replication_hostgroups VALUES(10,20,"read_only","test replication with read and write separation");
Query OK, 1 row affected (0.00 sec)

admin@127.0.0.1 [(none)] 02:39:39>>>SELECT * FROM mysql_replication_hostgroups;
+------------------+------------------+------------+-------------------------------------------------+
| writer_hostgroup | reader_hostgroup | check_type | comment                                         |
+------------------+------------------+------------+-------------------------------------------------+
| 10               | 20               | read_only  | test replication with read and write separation |
+------------------+------------------+------------+-------------------------------------------------+
1 row in set (0.00 sec)

admin@127.0.0.1 [(none)] 02:39:59>>>LOAD MYSQL SERVERS TO RUNTIME;
Query OK, 0 rows affected (0.01 sec)

admin@127.0.0.1 [(none)] 02:40:12>>>SAVE MYSQL SERVERS TO DISK;
Query OK, 0 rows affected (0.05 sec)

admin@127.0.0.1 [(none)] 02:40:25>>>SELECT * FROM mysql_servers;
+--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname      | port | gtid_port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| 10           | 192.168.56.11 | 3306 | 0         | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
| 10           | 192.168.56.11 | 3307 | 0         | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
+--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
2 rows in set (0.00 sec)

admin@127.0.0.1 [(none)] 02:40:34>>>exit
Bye
[root@12c proxysql]# mysql -h127.0.0.1 -p123456 -P3307          
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MySQL connection id is 40
Server version: 5.7.24-log MySQL Community Server (GPL)

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

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

root@127.0.0.1 [(none)] 02:40:40>>>set global read_only=1;
Query OK, 0 rows affected (0.00 sec)

root@127.0.0.1 [(none)] 02:40:54>>>exit
Bye
[root@12c proxysql]# mysql -u admin -padmin -h 127.0.0.1 -P6032 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.5.30 (ProxySQL Admin Module)

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

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

admin@127.0.0.1 [(none)] 02:41:05>>>SELECT * FROM mysql_servers;
+--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname      | port | gtid_port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| 10           | 192.168.56.11 | 3306 | 0         | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
| 20           | 192.168.56.11 | 3307 | 0         | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
+--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
2 rows in set (0.00 sec)

12、配置proxysql 中用于客户端访问的用户

admin@127.0.0.1 [(none)] 02:41:09>>>exit
Bye
[root@12c proxysql]# mysql -h127.0.0.1 -p123456                 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MySQL connection id is 44
Server version: 5.7.24-log MySQL Community Server (GPL)

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

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

root@127.0.0.1 [(none)] 02:43:43>>> GRANT ALL ON *.* TO ‘rootuser‘@‘%‘ IDENTIFIED BY ‘123456‘; 
Query OK, 0 rows affected, 1 warning (0.00 sec)

root@127.0.0.1 [(none)] 02:43:50>>>flush privileges;
Query OK, 0 rows affected (0.27 sec)

root@127.0.0.1 [(none)] 02:44:03>>>exit
Bye
[root@12c proxysql]# mysql -u admin -padmin -h 127.0.0.1 -P6032 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.5.30 (ProxySQL Admin Module)

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

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

admin@127.0.0.1 [(none)] 02:44:17>>> INSERT INTO mysql_users(username,password,default_hostgroup) VALUES (‘rootuser‘,‘123456‘,10);
Query OK, 1 row affected (0.00 sec)

admin@127.0.0.1 [(none)] 02:44:25>>>SELECT * FROM mysql_users;
+----------+----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+---------+
| username | password | active | use_ssl | default_hostgroup | default_schema | schema_locked | transaction_persistent | fast_forward | backend | frontend | max_connections | comment |
+----------+----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+---------+
| rootuser | 123456   | 1      | 0       | 10                | NULL           | 0             | 1                      | 0            | 1       | 1        | 10000           |         |
+----------+----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+---------+
1 row in set (0.00 sec)

admin@127.0.0.1 [(none)] 02:45:27>>>LOAD MYSQL USERS  TO RUNTIME;       
Query OK, 0 rows affected (0.00 sec)

admin@127.0.0.1 [(none)] 02:45:54>>>SAVE MYSQL USERS TO DISK;    
Query OK, 0 rows affected (0.03 sec)

admin@127.0.0.1 [(none)] 02:45:58>>>exit
Bye

13、配置读写分离路由规则,配置路由前都是用用户的默认规则

[root@12c proxysql]#  mysql -urootuser -p123456 -h192.168.56.11  -P6033 -e "SELECT @@server_id;"
+-------------+
| @@server_id |
+-------------+
|           1 |
+-------------+
[root@12c proxysql]#  mysql -urootuser -p123456 -h192.168.56.11 -P6033 -e "BEGIN;SELECT @@server_id;commit;"
+-------------+
| @@server_id |
+-------------+
|           1 |
+-------------+
[root@12c proxysql]# mysql -u admin -padmin -h 127.0.0.1 -P6032                                             
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 5.5.30 (ProxySQL Admin Module)

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

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

admin@127.0.0.1 [(none)] 02:48:27>>>INSERT INTO mysql_query_rules(rule_id,active,match_digest,destination_hostgroup,apply) VALUES (1,1,‘^SELECT.*FOR UPDATE$‘,10,1),(2,1,‘^SELECT‘,20,1);
Query OK, 2 rows affected (0.00 sec)

admin@127.0.0.1 [(none)] 02:48:32>>>SELECT * FROM mysql_query_rules;
+---------+--------+----------+------------+--------+-------------+------------+------------+--------+----------------------+---------------+----------------------+--------------+---------+-----------------+-----------------------+-----------+--------------------+---------------+-----------+---------+---------+-------+-------------------+----------------+------------------+-----------+--------+-------------+-----------+---------------------+-----+-------+---------+
| rule_id | active | username | schemaname | flagIN | client_addr | proxy_addr | proxy_port | digest | match_digest         | match_pattern | negate_match_pattern | re_modifiers | flagOUT | replace_pattern | destination_hostgroup | cache_ttl | cache_empty_result | cache_timeout | reconnect | timeout | retries | delay | next_query_flagIN | mirror_flagOUT | mirror_hostgroup | error_msg | OK_msg | sticky_conn | multiplex | gtid_from_hostgroup | log | apply | comment |
+---------+--------+----------+------------+--------+-------------+------------+------------+--------+----------------------+---------------+----------------------+--------------+---------+-----------------+-----------------------+-----------+--------------------+---------------+-----------+---------+---------+-------+-------------------+----------------+------------------+-----------+--------+-------------+-----------+---------------------+-----+-------+---------+
| 1       | 1      | NULL     | NULL       | 0      | NULL        | NULL       | NULL       | NULL   | ^SELECT.*FOR UPDATE$ | NULL          | 0                    | CASELESS     | NULL    | NULL            | 10                    | NULL      | NULL               | NULL          | NULL      | NULL    | NULL    | NULL  | NULL              | NULL           | NULL             | NULL      | NULL   | NULL        | NULL      | NULL                | NULL | 1     | NULL    |
| 2       | 1      | NULL     | NULL       | 0      | NULL        | NULL       | NULL       | NULL   | ^SELECT              | NULL          | 0                    | CASELESS     | NULL    | NULL            | 20                    | NULL      | NULL               | NULL          | NULL      | NULL    | NULL    | NULL  | NULL              | NULL           | NULL             | NULL      | NULL   | NULL        | NULL      | NULL                | NULL | 1     | NULL    |
+---------+--------+----------+------------+--------+-------------+------------+------------+--------+----------------------+---------------+----------------------+--------------+---------+-----------------+-----------------------+-----------+--------------------+---------------+-----------+---------+---------+-------+-------------------+----------------+------------------+-----------+--------+-------------+-----------+---------------------+-----+-------+---------+
2 rows in set (0.00 sec)

admin@127.0.0.1 [(none)] 02:48:50>>>LOAD MYSQL QUERY RULES TO RUNTIME;
Query OK, 0 rows affected (0.00 sec)

admin@127.0.0.1 [(none)] 02:49:07>>>SAVE MYSQL QUERY RULES TO DISK;
Query OK, 0 rows affected (0.31 sec)

admin@127.0.0.1 [(none)] 02:49:18>>>exit
Bye
[root@12c proxysql]#  mysql -urootuser -p123456 -h192.168.56.11 -P6033 -e "BEGIN;SELECT @@server_id;commit;"
+-------------+
| @@server_id |
+-------------+
|           1 |
+-------------+
[root@12c proxysql]#  mysql -urootuser -p123456 -h192.168.56.11  -P6033 -e "SELECT @@server_id;"            
+-------------+
| @@server_id |
+-------------+
|          11 |
+-------------+

proxysql 主从复制读写分离配置过程记录

标签:art   copy   containe   systemctl   ima   oracle   pass   flag   statement   

人气教程排行