mysql5.7+proxy_orchestrator+proxysql+mysql5.7GTID主从切换测试过程

最新推荐文章于 2023-03-01 00:24:27 发布
最新推荐文章于 2023-03-01 00:24:27 发布
阅读量179 收藏
点赞数
文章标签： mysql5.7+proxy
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_39612038/article/details/113599619
版权

orchestrator+proxysql+mysql5.7GTID主从切换测试过程

发布时间：2020-07-19 22:30:58

来源：51CTO

阅读：589

作者：xingzhehxiang

[root@es2 ~]# cat <

[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

[root@es2 ~]# yum install proxysql

[root@es2 ~]# /etc/init.d/proxysql start

Starting ProxySQL: 2019-08-15 11:20:17 [INFO] Using config file /etc/proxysql.cnf

2019-08-15 11:20:17 [INFO] No SSL keys/certificates found in datadir (/var/lib/proxysql). Generating new keys/certificates.

DONE!

[root@es2 ~]# export MYSQL_PS1="\\u@\\h [\\d] \\r:\\m:\\s>>>"

[root@es2 ~]# mysql -u admin -padmin -h 127.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 1

Server version: 5.5.30 (ProxySQL Admin Module)

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

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.

admin@127.0.0.1 [(none)] 11:22:10>>> INSERT INTO mysql_servers(hostgroup_id,hostname,port) VALUES (10,'192.168.56.14',3306);

Query OK, 1 row affected (0.00 sec)

admin@127.0.0.1 [(none)] 11:22:51>>>  INSERT INTO mysql_servers(hostgroup_id,hostname,port) VALUES (10,'192.168.56.15',3306);

Query OK, 1 row affected (0.00 sec)

admin@127.0.0.1 [(none)] 11:22:51>>>    INSERT INTO mysql_servers(hostgroup_id,hostname,port) VALUES (10,'192.168.56.16',3306);

Query OK, 1 row affected (0.00 sec)

admin@127.0.0.1 [(none)] 11:22:53>>>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.14 | 3306 | 0         | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |

| 10           | 192.168.56.15 | 3306 | 0         | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |

| 10           | 192.168.56.16 | 3306 | 0         | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |

+--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+

3 rows in set (0.00 sec)

admin@127.0.0.1 [(none)] 11:23:23>>>SET mysql-monitor_username='orchestrator';

Query OK, 1 row affected (0.00 sec)

admin@127.0.0.1 [(none)] 11:25:11>>>SET mysql-monitor_password='123456';

Query OK, 1 row affected (0.00 sec)

admin@127.0.0.1 [(none)] 11:25:12>>>LOAD MYSQL VARIABLES TO RUNTIME;

Query OK, 0 rows affected (0.00 sec)

admin@127.0.0.1 [(none)] 11:25:22>>>SAVE MYSQL VARIABLES TO DISK;

Query OK, 121 rows affected (0.01 sec)

admin@127.0.0.1 [(none)] 11:25:40>>>select * from mysql_server_connect_log;

+---------------+------+------------------+-------------------------+--------------------------------------------------------------+

| hostname      | port | time_start_us    | connect_success_time_us | connect_error                                                |

+---------------+------+------------------+-------------------------+--------------------------------------------------------------+

| 192.168.56.15 | 3306 | 1565839397686017 | 0                       | Access denied for user 'monitor'@'es2' (using password: YES) |

| 192.168.56.16 | 3306 | 1565839398441691 | 0                       | Access denied for user 'monitor'@'es2' (using password: YES) |

| 192.168.56.14 | 3306 | 1565839399196500 | 0                       | Access denied for user 'monitor'@'es2' (using password: YES) |

| 192.168.56.14 | 3306 | 1565839457686993 | 0                       | Access denied for user 'monitor'@'es2' (using password: YES) |

| 192.168.56.16 | 3306 | 1565839458185679 | 0                       | Access denied for user 'monitor'@'es2' (using password: YES) |

| 192.168.56.15 | 3306 | 1565839458685105 | 0                       | Access denied for user 'monitor'@'es2' (using password: YES) |

| 192.168.56.14 | 3306 | 1565839517686866 | 0                       | Access denied for user 'monitor'@'es2' (using password: YES) |

| 192.168.56.16 | 3306 | 1565839518451995 | 0                       | Access denied for user 'monitor'@'es2' (using password: YES) |

| 192.168.56.15 | 3306 | 1565839519217338 | 0                       | Access denied for user 'monitor'@'es2' (using password: YES) |

| 192.168.56.15 | 3306 | 1565839522987508 | 666                     | NULL                                                         |

| 192.168.56.16 | 3306 | 1565839523694983 | 4102                    | NULL                                                         |

| 192.168.56.14 | 3306 | 1565839524401835 | 1277                    | NULL                                                         |

+---------------+------+------------------+-------------------------+--------------------------------------------------------------+

12 rows in set (0.00 sec)

admin@127.0.0.1 [(none)] 11:26:01>>>select * from mysql_server_ping_log;

+---------------+------+------------------+----------------------+--------------------------------------------------------------+

| hostname      | port | time_start_us    | ping_success_time_us | ping_error                                                   |

+---------------+------+------------------+----------------------+--------------------------------------------------------------+

| 192.168.56.14 | 3306 | 1565839377692552 | 0                    | Access denied for user 'monitor'@'es2' (using password: YES) |

| 192.168.56.15 | 3306 | 1565839377784500 | 0                    | Access denied for user 'monitor'@'es2' (using password: YES) |

| 192.168.56.16 | 3306 | 1565839377876466 | 0                    | Access denied for user 'monitor'@'es2' (using password: YES) |

| 192.168.56.16 | 3306 | 1565839387692396 | 0                    | Access denied for user 'monitor'@'es2' (using password: YES) |

| 192.168.56.15 | 3306 | 1565839387796187 | 0                    | Access denied for user 'monitor'@'es2' (using password: YES) |

| 192.168.56.14 | 3306 | 1565839387899871 | 0                    | Access denied for user 'monitor'@'es2' (using password: YES) |

| 192.168.56.14 | 3306 | 1565839397701776 | 0                    | Access denied for user 'monitor'@'es2' (using password: YES) |

| 192.168.56.15 | 3306 | 1565839397818564 | 0                    | Access denied for user 'monitor'@'es2' (using password: YES) |

| 192.168.56.16 | 3306 | 1565839397935581 | 0                    | Access denied for user 'monitor'@'es2' (using password: YES) |

| 192.168.56.15 | 3306 | 1565839407701496 | 0                    | Access denied for user 'monitor'@'es2' (using password: YES) |

| 192.168.56.14 | 3306 | 1565839407774308 | 0                    | Access denied for user 'monitor'@'es2' (using password: YES) |

| 192.168.56.16 | 3306 | 1565839407847409 | 0                    | Access denied for user 'monitor'@'es2' (using password: YES) |

| 192.168.56.15 | 3306 | 1565839417701921 | 0                    | Access denied for user 'monitor'@'es2' (using password: YES) |

| 192.168.56.14 | 3306 | 1565839417800955 | 0                    | Access denied for user 'monitor'@'es2' (using password: YES) |

| 192.168.56.16 | 3306 | 1565839417899369 | 0                    | Access denied for user 'monitor'@'es2' (using password: YES) |

| 192.168.56.15 | 3306 | 1565839427702073 | 0                    | Access denied for user 'monitor'@'es2' (using password: YES) |

| 192.168.56.14 | 3306 | 1565839427792125 | 0                    | Access denied for user 'monitor'@'es2' (using password: YES) |

| 192.168.56.16 | 3306 | 1565839427882348 | 0                    | Access denied for user 'monitor'@'es2' (using password: YES) |

| 192.168.56.16 | 3306 | 1565839437702941 | 0                    | Access denied for user 'monitor'@'es2' (using password: YES) |

| 192.168.56.14 | 3306 | 1565839437819812 | 0                    | Access denied for user 'monitor'@'es2' (using password: YES) |

| 192.168.56.15 | 3306 | 1565839437937009 | 0                    | Access denied for user 'monitor'@'es2' (using password: YES) |

| 192.168.56.15 | 3306 | 1565839447703826 | 0                    | Access denied for user 'monitor'@'es2' (using password: YES) |

| 192.168.56.16 | 3306 | 1565839447820965 | 0                    | Access denied for user 'monitor'@'es2' (using password: YES) |

| 192.168.56.14 | 3306 | 1565839447938195 | 0                    | Access denied for user 'monitor'@'es2' (using password: YES) |

| 192.168.56.14 | 3306 | 1565839457704500 | 0                    | Access denied for user 'monitor'@'es2' (using password: YES) |

| 192.168.56.15 | 3306 | 1565839457815540 | 0                    | Access denied for user 'monitor'@'es2' (using password: YES) |

| 192.168.56.16 | 3306 | 1565839457926355 | 0                    | Access denied for user 'monitor'@'es2' (using password: YES) |

| 192.168.56.15 | 3306 | 1565839467704770 | 0                    | Access denied for user 'monitor'@'es2' (using password: YES) |

| 192.168.56.16 | 3306 | 1565839467824645 | 0                    | Access denied for user 'monitor'@'es2' (using password: YES) |

| 192.168.56.14 | 3306 | 1565839467944884 | 0                    | Access denied for user 'monitor'@'es2' (using password: YES) |

| 192.168.56.14 | 3306 | 1565839477705270 | 0                    | Access denied for user 'monitor'@'es2' (using password: YES) |

| 192.168.56.15 | 3306 | 1565839477807078 | 0                    | Access denied for user 'monitor'@'es2' (using password: YES) |

| 192.168.56.16 | 3306 | 1565839477909188 | 0                    | Access denied for user 'monitor'@'es2' (using password: YES) |

| 192.168.56.15 | 3306 | 1565839487705864 | 0                    | Access denied for user 'monitor'@'es2' (using password: YES) |

| 192.168.56.14 | 3306 | 1565839487836013 | 0                    | Access denied for user 'monitor'@'es2' (using password: YES) |

| 192.168.56.16 | 3306 | 1565839487966243 | 0                    | Access denied for user 'monitor'@'es2' (using password: YES) |

| 192.168.56.14 | 3306 | 1565839497706178 | 0                    | Access denied for user 'monitor'@'es2' (using password: YES) |

| 192.168.56.15 | 3306 | 1565839497808802 | 0                    | Access denied for user 'monitor'@'es2' (using password: YES) |

| 192.168.56.16 | 3306 | 1565839497911180 | 0                    | Access denied for user 'monitor'@'es2' (using password: YES) |

| 192.168.56.14 | 3306 | 1565839507706497 | 0                    | Access denied for user 'monitor'@'es2' (using password: YES) |

| 192.168.56.16 | 3306 | 1565839507808442 | 0                    | Access denied for user 'monitor'@'es2' (using password: YES) |

| 192.168.56.15 | 3306 | 1565839507910607 | 0                    | Access denied for user 'monitor'@'es2' (using password: YES) |

| 192.168.56.16 | 3306 | 1565839517707345 | 0                    | Access denied for user 'monitor'@'es2' (using password: YES) |

| 192.168.56.14 | 3306 | 1565839517815147 | 0                    | Access denied for user 'monitor'@'es2' (using password: YES) |

| 192.168.56.15 | 3306 | 1565839517922987 | 0                    | Access denied for user 'monitor'@'es2' (using password: YES) |

| 192.168.56.15 | 3306 | 1565839523039065 | 232                  | NULL                                                         |

| 192.168.56.14 | 3306 | 1565839523133408 | 1207                 | NULL                                                         |

| 192.168.56.16 | 3306 | 1565839523227794 | 1413                 | NULL                                                         |

| 192.168.56.14 | 3306 | 1565839533039563 | 1542                 | NULL                                                         |

| 192.168.56.16 | 3306 | 1565839533138738 | 1525                 | NULL                                                         |

| 192.168.56.15 | 3306 | 1565839533238017 | 411                  | NULL                                                         |

| 192.168.56.15 | 3306 | 1565839543040819 | 272                  | NULL                                                         |

| 192.168.56.14 | 3306 | 1565839543172288 | 1806                 | NULL                                                         |

| 192.168.56.16 | 3306 | 1565839543303207 | 1774                 | NULL                                                         |

| 192.168.56.16 | 3306 | 1565839553041501 | 1603                 | NULL                                                         |

| 192.168.56.15 | 3306 | 1565839553143450 | 276                  | NULL                                                         |

| 192.168.56.14 | 3306 | 1565839553244967 | 1399                 | NULL                                                         |

| 192.168.56.16 | 3306 | 1565839563042350 | 1510                 | NULL                                                         |

| 192.168.56.14 | 3306 | 1565839563120995 | 1418                 | NULL                                                         |

| 192.168.56.15 | 3306 | 1565839563199256 | 277                  | NULL                                                         |

| 192.168.56.14 | 3306 | 1565839573042777 | 1518                 | NULL                                                         |

| 192.168.56.15 | 3306 | 1565839573159144 | 374                  | NULL                                                         |

| 192.168.56.16 | 3306 | 1565839573275584 | 1672                 | NULL                                                         |

+---------------+------+------------------+----------------------+--------------------------------------------------------------+

63 rows in set (0.00 sec)

admin@127.0.0.1 [(none)] 11:26:21>>>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)] 11:26:37>>>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)] 11:26:51>>>LOAD MYSQL SERVERS TO RUNTIME;

Query OK, 0 rows affected (0.01 sec)

admin@127.0.0.1 [(none)] 11:27:03>>>SAVE MYSQL SERVERS TO DISK;

Query OK, 0 rows affected (0.13 sec)

admin@127.0.0.1 [(none)] 11:27:14>>>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.14 | 3306 | 0         | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |

| 20           | 192.168.56.16 | 3306 | 0         | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |

| 20           | 192.168.56.15 | 3306 | 0         | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |

+--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+

3 rows in set (0.00 sec)

admin@127.0.0.1 [(none)] 11:27:27>>> INSERT INTO mysql_users(username,password,default_hostgroup) VALUES ('orchestrator','123456',10);

Query OK, 1 row affected (0.00 sec)

admin@127.0.0.1 [(none)] 11:28:13>>>SELECT * FROM mysql_users;

+--------------+----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+---------+

| username     | password | active | use_ssl | default_hostgroup | default_schema | schema_locked | transaction_persistent | fast_forward | backend | frontend | max_connections | comment |

+--------------+----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+---------+

| orchestrator | 123456   | 1      | 0       | 10                | NULL           | 0             | 1                      | 0            | 1       | 1        | 10000           |         |

+--------------+----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+---------+

1 row in set (0.00 sec)

admin@127.0.0.1 [(none)] 11:28:31>>>LOAD MYSQL USERS  TO RUNTIME;

Query OK, 0 rows affected (0.00 sec)

admin@127.0.0.1 [(none)] 11:28:44>>>SAVE MYSQL USERS TO DISK;

Query OK, 0 rows affected (0.04 sec)

admin@127.0.0.1 [(none)] 11:28:54>>>exit

Bye

[root@es2 ~]#  mysql -uorchestrator -p123456 -h292.168.56.15  -P6033 -e "SELECT @@server_id;"

mysql: [Warning] Using a password on the command line interface can be insecure.

+-------------+

| @@server_id |

+-------------+

|           1 |

+-------------+

[root@es2 ~]# mysql -u admin -padmin -h 127.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 3

Server version: 5.5.30 (ProxySQL Admin Module)

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

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.

admin@127.0.0.1 [(none)] 11:30:53>>> 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)] 11:30:56>>>LOAD MYSQL QUERY RULES TO RUNTIME;

Query OK, 0 rows affected (0.00 sec)

admin@127.0.0.1 [(none)] 11:31:09>>>>SAVE MYSQL QUERY RULES TO DISK;

ERROR 1045 (28000): ProxySQL Admin Error: near ">": syntax error

admin@127.0.0.1 [(none)] 11:31:19>>>SAVE MYSQL QUERY RULES TO DISK;

Query OK, 0 rows affected (0.03 sec)

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

Bye

[root@es2 ~]#  mysql -uorchestrator -p123456 -h292.168.56.15  -P6033 -e "SELECT @@server_id;"

mysql: [Warning] Using a password on the command line interface can be insecure.

+-------------+

| @@server_id |

+-------------+

|           3 |

+-------------+

[root@es2 ~]#  mysql -uorchestrator -p123456 -h292.168.56.15  -P6033 -e "SELECT @@server_id;"

mysql: [Warning] Using a password on the command line interface can be insecure.

+-------------+

| @@server_id |

+-------------+

|           3 |

+-------------+

[root@es2 ~]#  mysql -uorchestrator -p123456 -h292.168.56.15  -P6033 -e "SELECT @@server_id;"

mysql: [Warning] Using a password on the command line interface can be insecure.

+-------------+

| @@server_id |

+-------------+

|           3 |

+-------------+

[root@es2 ~]#  mysql -uorchestrator -p123456 -h292.168.56.15  -P6033 -e "SELECT @@server_id;"

mysql: [Warning] Using a password on the command line interface can be insecure.

+-------------+

| @@server_id |

+-------------+

|           2 |

+-------------+

[root@es2 ~]#  mysql -uorchestrator -p123456 -h292.168.56.15  -P6033 -e "SELECT @@server_id;"

mysql: [Warning] Using a password on the command line interface can be insecure.

+-------------+

| @@server_id |

+-------------+

|           3 |

+-------------+

[root@es2 ~]#  mysql -uorchestrator -p123456 -h292.168.56.15  -P6033 -e  "BEGIN;SELECT @@server_id;commit;"

mysql: [Warning] Using a password on the command line interface can be insecure.

+-------------+

| @@server_id |

+-------------+

|           1 |

+-------------+

[root@es2 ~]#  mysql -uorchestrator -p123456 -h292.168.56.15  -P6033 -e  "BEGIN;SELECT @@server_id;commit;"

mysql: [Warning] Using a password on the command line interface can be insecure.

+-------------+

| @@server_id |

+-------------+

|           1 |

+-------------+

[root@es2 ~]#  mysql -uorchestrator -p123456 -h292.168.56.15  -P6033 -e  "BEGIN;SELECT @@server_id;commit;"

mysql: [Warning] Using a password on the command line interface can be insecure.

+-------------+

| @@server_id |

+-------------+

|           1 |

+-------------+

[root@es2 ~]#  mysql -uorchestrator -p123456 -h292.168.56.15  -P6033 -e  "BEGIN;SELECT @@server_id;commit;"

mysql: [Warning] Using a password on the command line interface can be insecure.

+-------------+

| @@server_id |

+-------------+

|           1 |

+-------------+

[root@es2 ~]# mysql -u admin -padmin -h 127.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 13

Server version: 5.5.30 (ProxySQL Admin Module)

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

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.

admin@127.0.0.1 [(none)] 11:34: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.16 | 3306 | 0         | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |

| 20           | 192.168.56.14 | 3306 | 0         | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |

| 20           | 192.168.56.15 | 3306 | 0         | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |

| 20           | 192.168.56.16 | 3306 | 0         | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |

+--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+

4 rows in set (0.00 sec)

admin@127.0.0.1 [(none)] 11:34:31>>>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.16 | 3306 | 0         | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |

| 20           | 192.168.56.14 | 3306 | 0         | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |

| 20           | 192.168.56.15 | 3306 | 0         | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |

| 20           | 192.168.56.16 | 3306 | 0         | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |

+--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+

4 rows in set (0.00 sec)

admin@127.0.0.1 [(none)] 11:34:35>>>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.16 | 3306 | 0         | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |

| 20           | 192.168.56.14 | 3306 | 0         | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |

| 20           | 192.168.56.15 | 3306 | 0         | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |

| 20           | 192.168.56.16 | 3306 | 0         | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |

+--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+

4 rows in set (0.00 sec)

admin@127.0.0.1 [(none)] 11:34:36>>>show tables;

+--------------------------------------------+

| tables                                     |

+--------------------------------------------+

| global_variables                           |

| mysql_aws_aurora_hostgroups                |

| mysql_collations                           |

| mysql_galera_hostgroups                    |

| mysql_group_replication_hostgroups         |

| mysql_query_rules                          |

| mysql_query_rules_fast_routing             |

| mysql_replication_hostgroups               |

| mysql_servers                              |

| mysql_users                                |

| proxysql_servers                           |

| runtime_checksums_values                   |

| runtime_global_variables                   |

| runtime_mysql_aws_aurora_hostgroups        |

| runtime_mysql_galera_hostgroups            |

| runtime_mysql_group_replication_hostgroups |

| runtime_mysql_query_rules                  |

| runtime_mysql_query_rules_fast_routing     |

| runtime_mysql_replication_hostgroups       |

| runtime_mysql_servers                      |

| runtime_mysql_users                        |

| runtime_proxysql_servers                   |

| runtime_scheduler                          |

| scheduler                                  |

+--------------------------------------------+

24 rows in set (0.00 sec)

admin@127.0.0.1 [(none)] 11:34:47>>>SELECT * FROM runtime_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.16 | 3306 | 0         | ONLINE  | 1      | 0           | 1000            | 0                   | 0       | 0              |         |

| 20           | 192.168.56.14 | 3306 | 0         | SHUNNED | 1      | 0           | 1000            | 0                   | 0       | 0              |         |

| 20           | 192.168.56.15 | 3306 | 0         | ONLINE  | 1      | 0           | 1000            | 0                   | 0       | 0              |         |

| 20           | 192.168.56.16 | 3306 | 0         | ONLINE  | 1      | 0           | 1000            | 0                   | 0       | 0              |         |

+--------------+---------------+------+-----------+---------+--------+-------------+-----------------+---------------------+---------+----------------+---------+

4 rows in set (0.01 sec)

admin@127.0.0.1 [(none)] 11:35:09>>>SELECT * FROM runtime_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.16 | 3306 | 0         | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |

| 20           | 192.168.56.14 | 3306 | 0         | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |

| 20           | 192.168.56.15 | 3306 | 0         | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |

| 20           | 192.168.56.16 | 3306 | 0         | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |

| 10           | 192.168.56.14 | 3306 | 0         | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |

+--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+

5 rows in set (0.00 sec)

admin@127.0.0.1 [(none)] 11:36:50>>>SELECT * FROM runtime_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.16 | 3306 | 0         | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |

| 20           | 192.168.56.14 | 3306 | 0         | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |

| 20           | 192.168.56.15 | 3306 | 0         | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |

| 20           | 192.168.56.16 | 3306 | 0         | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |

| 10           | 192.168.56.14 | 3306 | 0         | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |

+--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+

5 rows in set (0.01 sec)

admin@127.0.0.1 [(none)] 11:36:55>>>SELECT * FROM runtime_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.16 | 3306 | 0         | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |

| 20           | 192.168.56.14 | 3306 | 0         | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |

| 20           | 192.168.56.15 | 3306 | 0         | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |

| 20           | 192.168.56.16 | 3306 | 0         | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |

| 10           | 192.168.56.14 | 3306 | 0         | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |

+--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+

5 rows in set (0.01 sec)

admin@127.0.0.1 [(none)] 11:36:56>>>SELECT * FROM runtime_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.16 | 3306 | 0         | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |

| 20           | 192.168.56.14 | 3306 | 0         | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |

| 20           | 192.168.56.15 | 3306 | 0         | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |

| 20           | 192.168.56.16 | 3306 | 0         | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |

+--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+

4 rows in set (0.01 sec)

admin@127.0.0.1 [(none)] 11:37:17>>>SELECT * FROM runtime_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.16 | 3306 | 0         | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |

| 20           | 192.168.56.14 | 3306 | 0         | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |

| 20           | 192.168.56.15 | 3306 | 0         | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |

| 20           | 192.168.56.16 | 3306 | 0         | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |

+--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+

4 rows in set (0.00 sec)

admin@127.0.0.1 [(none)] 11:37:18>>>SELECT * FROM runtime_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.16 | 3306 | 0         | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |

| 20           | 192.168.56.14 | 3306 | 0         | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |

| 20           | 192.168.56.15 | 3306 | 0         | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |

| 20           | 192.168.56.16 | 3306 | 0         | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |

+--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+

4 rows in set (0.01 sec)

admin@127.0.0.1 [(none)] 11:37:20>>>SELECT * FROM runtime_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.16 | 3306 | 0         | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |

| 20           | 192.168.56.14 | 3306 | 0         | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |

| 20           | 192.168.56.15 | 3306 | 0         | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |

| 20           | 192.168.56.16 | 3306 | 0         | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |

+--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+

4 rows in set (0.01 sec)

admin@127.0.0.1 [(none)] 11:37:21>>>SELECT * FROM runtime_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.16 | 3306 | 0         | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |

| 20           | 192.168.56.14 | 3306 | 0         | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |

| 20           | 192.168.56.15 | 3306 | 0         | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |

| 20           | 192.168.56.16 | 3306 | 0         | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |

+--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+

4 rows in set (0.00 sec)

admin@127.0.0.1 [(none)] 11:38:22>>>SELECT * FROM runtime_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.16 | 3306 | 0         | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |

| 20           | 192.168.56.14 | 3306 | 0         | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |

| 20           | 192.168.56.15 | 3306 | 0         | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |

| 20           | 192.168.56.16 | 3306 | 0         | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |

+--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+

4 rows in set (0.01 sec)

weixin_39612038
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
Mysql+haproxy+mycat+pxc+zookeeper实现高可用集群
02-15
Mysql+haproxy+mycat+pxc+zookeeper实现高可用集群 本文将详细介绍如何使用Mysql、Haproxy、Mycat、PXC、Zookeeper实现高可用集群，涵盖了整个架构图、应用程序的访问流程、数据的读写分离、负载均衡、数据库节点的...
龙蜥anolis8系统安装mysql5.7
05-08
龙蜥anolis8系统安装mysql5.7
orchestrator+proxysql+mysql5.7GTID主从切换测试过程
08-15 1015
[root@es2~]#cat<<EOF|tee/etc/yum.repos.d/proxysql.repo [proxysql_repo] name=ProxySQLYUMrepository baseurl=https://repo.proxysql.com/ProxySQL/proxysql-2.0.x/centos/\$releaseve...
mysql proxy主从切换_orchestrator+proxysql+mysql5.7GTID主从切换测试过程
weixin_34883242的博客
01-21 521
[root@es2~]#cat<[proxysql_repo]name=ProxySQLYUMrepositorybaseurl=https://repo.proxysql.com/ProxySQL/proxysql-2.0.x/centos/\$releasevergpgcheck=1gpgkey=https://repo.proxysql.com/ProxySQL/repo_...
Mysql5.7+Orch+proxysql+keepalive
u014650965的博客
03-01 598
通过orch、proxysql、proxysql、keepalive实现MySQL高可用及读写分离
MySQL高可用管理工具：orchestrator
baijiu1的博客
04-19 3965
orchestrator是一个MySQL高可用复制拓补的管理和可视化工具，同时也是GitHub官方在使用的一个复制拓补管理工具，它允许： 1）发现： orchestrator主动发现拓补结构并读取基本的MySQL信息，如复制状态和配置。 2）重构： 可以将一个不可用的服务器从拓补结构中剔除，并把数据副本移动到另一个从库下面 3）恢复： 在不可用时，可以选择一个适合的从库提升为主库 ...
mysql proxy安装配置_MySQL Proxy应用入门(1)--安装MySQL Proxy
weixin_34512248的博客
01-19 147
MySQLProxy(MySQL代理)是一个通过MySQL网络协议，提供MySQL服务器与客户端之间连接的应用工具，在基本配置条件下，MySQL代理仅是传递客户端发出的查询请求到MySQL服务器端，而后返回MySQL服务器的响应到客户端。由于MySQL代理使用的MySQL网络协议，因此所有MySQL兼容的客户端(包括mysql命令行、调用mysql命令行的类库、以及支持MySQL网络协议的应用)...
安装LAP+mysql主从库 apache+mysql+proxy主从+PHP+discuz论坛 .docx
10-08
公司运维手册，安装LAP+mysql主从库 apache+mysql+proxy主从+PHP+discuz论坛，废话不多说，上干货！
mysql +mmm + proxy实现读写分离
07-10
mysql +mmm + proxy实现读写分离 ，数据库实现读写分离
amoeba-mysql-1.0.2-BETA.zip_SQL 高并发_mysql-proxy java_代理_分布式java_
09-22
Amoeba属于分布式数据库代理开发框架，专注于分布式数据库 proxy 开发。座落与Client、DB Server(s)之间。对客户 端透明。具有负载均衡、高可用性、sql过滤、读写分离、可路由相关的query到目标数据库、可并发请 求...
orchestrator用作VIP切换的脚本有两个：其一 ----> orch_vip.sh
07-23
用于orchestrator高可用来做 VIP切换的，该脚本略有瑕疵，并没有非常完美。但是生产上是可以用的，不过该脚本没有报警功能，请知悉。
免安装版proxysql+mgr搭建，亲测成功
03-05
文档中写了详细的proxysql搭建过程，绕过很多坑点。希望各位老司机指正。
MGR+Consul+ProxySQL
aohexi1719的博客
08-12 321
--------------------------------------------------------------------------------------------------------------------------------------------CentOS7 配置如下 5.7.22 Group ReplicationMySQL5.7.22安装略...
开源数据库管理软件_跟上开源数据库管理的发展
cumj63710的博客
06-27 240
开源数据库管理软件 开源数据库的世界正在Swift发展。 似乎每天都有开源技术的新版本发布，这可能会使数据库管理员的生活变得更轻松，即使只有他/她知道这一点。 幸运的是，有许多方法可以掌握开源数据库技术的最新发展。 一种方法是下周在爱尔兰都柏林举行的Percona实时开源数据库会议。 我们之前已经介绍了Percona Live，并邀请您回顾一下我们以前的故事 。 从物联网到大数据再到与云的协...
orchestrator+maxscale+mysql5.7GTID主从切换测试过程
08-15 455
1、orchestrator环境请看上一篇：https://blog.51cto.com/860143/24295582、maxscale安装curl-sShttps://downloads.mariadb.com/MariaDB/mariadb_repo_setup|sudobash-s----mariadb-maxscale-version="2....
Mysql高可用—5.7 gtid 主从 + orchestrator 配置及简单测试
翔之天空的专栏
04-21 2091
参考：https://github.com/openark/orchestrator/tree/master/docs 下载地址：https://github.com/openark/orchestrator/releases --1、环境信息： --1-1、版本： 系统版本：CentOS7 Mysql版本：Mysql5.7 orch版本: orchestrator-3.1....
mysql之orchestrator高可用架构-完整版
q936889811的博客
12-20 3130
orchestrator 是MySQL高可用性和复制管理工具。
MySQL 5.7.26部署及授权，主从配置
id_wxq的博客
09-19 400
MySQL 5.7.26部署及授权，主从配置
mysql基于proxysql 实现读写分离
qq_36801585的博客
02-07 235
文章目录ProxySQL 读写分离一、版本介绍二、基础信息介绍2.0 运行原理2.1 ProxySQL组成2.2 启动ProxySQL：2.3 连接ProxySQL2.4 sqlite数据库说明：三、安装四、使用示例1. 搭建主从复制2. 把所有mysql 服务器添加到mysql_servers 表中2、创建并且配置监控账号3、设置分组信息4. 配置发送sql 语句的账号5. 在proxysq...
-130+err_proxy_connection_failed
最新发布
11-07
-130 err_proxy_connection_failed 是一个出现在使用代理服务器时的错误代码。该错误代码表示代理连接失败。 代理服务器用于在客户端与目标服务器之间进行通信，起到中间转发的作用。但是当出现代理连接失败时，...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值