orchestrator+proxysql+mysql5.7GTID主從切換測(cè)試過程

[root@es2?~]#?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

[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)

分享名稱：orchestrator+proxysql+mysql5.7GTID主從切換測(cè)試過程
URL地址：http://muchs.cn/article4/jeppoe.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供網(wǎng)站內(nèi)鏈、企業(yè)建站、Google、App設(shè)計(jì)、做網(wǎng)站、外貿(mào)建站

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請(qǐng)盡快告知，我們將會(huì)在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng)，如需處理請(qǐng)聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時(shí)需注明來源： 創(chuàng)新互聯(lián)