Test read load balancing
[root@docker35 ~] #for i in $(seq 1 10); do mysql -uwr -plhr -h192168.68.136. -P6033 -e 'select @@server_id; '; done | egrep '[0-9]'
mysql: [Warning] Using a password on the command line interface can be insecure.
573306133
mysql: [Warning] Using a password on the command line interface can be insecure.
573306132
mysql: [Warning] Using a password on the command line interface can be insecure.
573306132
mysql: [Warning] Using a password on the command line interface can be insecure.
573306132
mysql: [Warning] Using a password on the command line interface can be insecure.
573306133
mysql: [Warning] Using a password on the command line interface can be insecure.
573306133
mysql: [Warning] Using a password on the command line interface can be insecure.
573306133
mysql: [Warning] Using a password on the command line interface can be insecure.
573306132
mysql: [Warning] Using a password on the command line interface can be insecure.
573306132
mysql: [Warning] Using a password on the command line interface can be insecure.
573306132
MySQL [(none)]> SELECT * FROM stats.stats_mysql_connection_pool;
+-----------+----------------+----------+--------+----------+----------+--------+---------+-------------+---------+------ -------------+-----------------+-----------------+------------+
| hostgroup | srv_host | srv_port | status | ConnUsed | ConnFree | ConnOK | ConnERR | MaxConnUsed | Queries | Queries_GTID_sync | Bytes_data_sent | Bytes_data_recv | Latency_us |
+-----------+----------------+----------+--------+----------+----------+--------+---------+-------------+---------+------ -------------+-----------------+-----------------+------------+
| 10 | 192.16868.131. | 3306 | ONLINE | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 323 |
| 20 | 192.16868.132. | 3306 | ONLINE | 0 | 1 | 1 | 0 | 1 | 6 | 0 | 108 | 84 | 280 |
| 20 | 192.16868.133. | 3306 | ONLINE | 0 | 1 | 1 | 0 | 1 | 4 | 0 | 72 | 56 | 390 |
+-----------+----------------+----------+--------+----------+----------+--------+---------+-------------+---------+------ -------------+-----------------+-----------------+------------+
3 rows in set (0.06 sec)
-- You can see that the Queries column is allocated to the slave library 132 and 133.
-- Next use mysqlSLAP to do the pressure test
[root@docker35 ~]#
[root@docker35 ~]# mysqlslap -uwr -plhr -h192168.68.136. -P6033 --create-schema=lhrmysqlslap --auto-generate-sql --auto-generate-sql-load-type=read --number-of-queries=100000
mysqlslap: [Warning] Using a password on the command line interface can be insecure.
Benchmark
Average number of seconds to run all queries: 104.757 seconds
Minimum number of seconds to run all queries: 104.757 seconds
Maximum number of seconds to run all queries: 104.757 seconds
Number of clients running queries: 1
Average number of queries per client: 100000
MySQL [(none)]> SELECT * FROM stats_mysql_commands_counters WHERE Total_cnt;
+-----------------+---------------+-----------+-----------+-----------+---------+---------+----------+----------+-------- ---+-----------+--------+--------+---------+----------+
| Command | Total_Time_us | Total_cnt | cnt_100us | cnt_500us | cnt_1ms | cnt_5ms | cnt_10ms | cnt_50ms | cnt_100ms | cnt_500ms | cnt_1s | cnt_5s | cnt_10s | cnt_INFs |
+-----------------+---------------+-----------+-----------+-----------+---------+---------+----------+----------+-------- ---+-----------+--------+--------+---------+----------+
| CREATE_DATABASE | 90942 | 3 | 0 | 0 | 0 | 2 | 0 | 0 | 1 | 0 | 0 | 0 | 0 | 0 |
| CREATE_TABLE | 67954 | 3 | 0 | 0 | 0 | 0 | 0 | 3 | 0 | 0 | 0 | 0 | 0 | 0 |
| INSERT | 11337671 | 113 | 0 | 0 | 0 | 69 | 27 | 14 | 1 | 0 | 1 | 0 | 0 | 1 |
| SELECT | 15299552 | 22293 | 263 | 1588 | 18947 | 1453 | 29 | 10 | 2 | 1 | 0 | 0 | 0 | 0 |
| SHOW | 56308 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | 0 | 0 | 0 | 0 | 0 |
| UNKNOWN | 131355 | 2 | 0 | 0 | 0 | 1 | 0 | 0 | 0 | 1 | 0 | 0 | 0 | 0 |
+-----------------+---------------+-----------+-----------+-----------+---------+---------+----------+----------+-------- ---+-----------+--------+--------+---------+----------+
6 rows in set (0.11 sec)
MySQL [(none)]> SELECT * FROM stats_mysql_commands_counters WHERE Total_cnt;
+-----------------+---------------+-----------+-----------+-----------+---------+---------+----------+----------+-------- ---+-----------+--------+--------+---------+----------+
| Command | Total_Time_us | Total_cnt | cnt_100us | cnt_500us | cnt_1ms | cnt_5ms | cnt_10ms | cnt_50ms | cnt_100ms | cnt_500ms | cnt_1s | cnt_5s | cnt_10s | cnt_INFs |
+-----------------+---------------+-----------+-----------+-----------+---------+---------+----------+----------+-------- ---+-----------+--------+--------+---------+----------+
| CREATE_DATABASE | 90942 | 3 | 0 | 0 | 0 | 2 | 0 | 0 | 1 | 0 | 0 | 0 | 0 | 0 |
| CREATE_TABLE | 67954 | 3 | 0 | 0 | 0 | 0 | 0 | 3 | 0 | 0 | 0 | 0 | 0 | 0 |
| INSERT | 11337671 | 113 | 0 | 0 | 0 | 69 | 27 | 14 | 1 | 0 | 1 | 0 | 0 | 1 |
| SELECT | 16494138 | 24050 | 263 | 1744 | 20488 | 1512 | 29 | 10 | 3 | 1 | 0 | 0 | 0 | 0 |
| SHOW | 56308 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | 0 | 0 | 0 | 0 | 0 |
| UNKNOWN | 131355 | 2 | 0 | 0 | 0 | 1 | 0 | 0 | 0 | 1 | 0 | 0 | 0 | 0 |
+-----------------+---------------+-----------+-----------+-----------+---------+---------+----------+----------+-------- ---+-----------+--------+--------+---------+----------+
6 rows in set (0.07 sec)
MySQL [(none)]> SELECT * FROM stats.stats_mysql_connection_pool;
+-----------+----------------+----------+--------+----------+----------+--------+---------+-------------+---------+------ -------------+-----------------+-----------------+------------+
| hostgroup | srv_host | srv_port | status | ConnUsed | ConnFree | ConnOK | ConnERR | MaxConnUsed | Queries | Queries_GTID_sync | Bytes_data_sent | Bytes_data_recv | Latency_us |
+-----------+----------------+----------+--------+----------+----------+--------+---------+-------------+---------+------ -------------+-----------------+-----------------+------------+
| 10 | 192.16868.131. | 3306 | ONLINE | 0 | 1 | 1 | 0 | 1 | 107 | 0 | 16464 | 92 | 277 |
| 20 | 192.16868.132. | 3306 | ONLINE | 0 | 1 | 1 | 0 | 1 | 24052 | 0 | 768920 | 335907539 | 306 |
| 20 | 192.16868.133. | 3306 | ONLINE | 1 | 0 | 1 | 0 | 1 | 24061 | 0 | 769119 | 335923167 | 381 |
+-----------+----------------+----------+--------+----------+----------+--------+---------+-------------+---------+------ -------------+-----------------+-----------------+------------+
3 rows in set (0.39 sec)
MySQL [(none)]> SELECT hostgroup,schemaname , username , digest , sum_time, count_star, substr(digest_text,1.60) FROM stats_mysql_query_digest where schemaname='lhrmysqlslap' ORDER BY sum_time DESC;
+-----------+--------------+----------+--------------------+----------+------------+------------------------------------- --------------------+
| hostgroup | schemaname | username | digest | sum_time | count_star | substr(digest_text,1.60) |
+-----------+--------------+----------+--------------------+----------+------------+------------------------------------- --------------------+
| 20 | lhrmysqlslap | wr | 0x91B125A563AED6EB | 65166273 | 100000 | SELECT intcol1,charcol1 FROM t1 |
| 10 | lhrmysqlslap | wr | 0xBCFD962F4A5FFA4B | 1134087 | 99 | INSERT INTO t1 VALUES(? ,?)|
| 10 | lhrmysqlslap | wr | 0x50E8C33778819FCD | 23904 | 1 | CREATE TABLE `t1` (intcol1 INT(?) ,charcol1 VARCHAR(?) )|
| 10 | lhrmysqlslap | wr | 0x41B7F05694EF426F | 21351 | 1 | DROP SCHEMA IF EXISTS `lhrmysqlslap` |
+-----------+--------------+----------+--------------------+----------+------------+------------------------------------- --------------------+
4 rows in set (0.08 sec)
Copy the codeAs you can see, the load is allocated to 132 and 133, respectively.
Test read/write separation
-- Test read/write separation
mysql -uwr -plhr -h192168.66.35. -P26033
create database test_proxysql;
use test_proxysql;
create table test_tables(name varchar(20),age int(4));
insert into test_tables values('lhr'.'33');
select * from test_tables;
select * from stats_mysql_query_digest;
MySQL [(none)]> SELECT * FROM stats.stats_mysql_connection_pool;
+-----------+----------------+----------+--------+----------+----------+--------+---------+-------------+---------+------ -------------+-----------------+-----------------+------------+
| hostgroup | srv_host | srv_port | status | ConnUsed | ConnFree | ConnOK | ConnERR | MaxConnUsed | Queries | Queries_GTID_sync | Bytes_data_sent | Bytes_data_recv | Latency_us |
+-----------+----------------+----------+--------+----------+----------+--------+---------+-------------+---------+------ -------------+-----------------+-----------------+------------+
| 10 | 192.16868.131. | 3306 | ONLINE | 0 | 1 | 1 | 0 | 1 | 5 | 0 | 165 | 92 | 369 |
| 20 | 192.16868.132. | 3306 | ONLINE | 0 | 1 | 1 | 0 | 1 | 8 | 0 | 142 | 130 | 301 |
| 20 | 192.16868.133. | 3306 | ONLINE | 0 | 1 | 1 | 0 | 1 | 5 | 0 | 97 | 67 | 341 |
+-----------+----------------+----------+--------+----------+----------+--------+---------+-------------+---------+------ -------------+-----------------+-----------------+------------+
3 rows in set (0.07 sec)
You can see that the write operation is assigned to host 131.
-- Statistics for other tables
SELECT * FROM stats.stats_mysql_connection_pool;
SELECT * FROM stats_mysql_commands_counters WHERE Total_cnt;
SELECT * FROM stats_mysql_query_digest ORDER BY sum_time DESC;
SELECT hostgroup,schemaname , username , digest , sum_time, count_star, substr(digest_text,1.60) FROM stats_mysql_query_digest where schemaname='lhrmysqlslap' ORDER BY sum_time DESC;
When the stats_mysQL_QUERY_digest_reset table is queried, the system automatically extracts data from the STATs_mysQL_QUERy_digest with truncate effect.
SELECT * FROM stats_mysql_query_digest_reset LIMIT 1;
Use sysbench to continue read-write separation testing
Set global max_connections=1000;
sysbench /usr/share/sysbench/oltp_common.lua --time=300 --mysql-host=192.168.68.136 --mysql-port=6033 --mysql-user=wr --mysql-password= LHR --mysql-db=sbtest --table-size=100000 --tables=20 --threads=100 --events=999999999 prepare
sysbench /usr/share/sysbench/oltp_read_write.lua --time=300 --mysql-host=192.168.68.136 --mysql-port=6033 --mysql-user=wr --mysql-password=lhr --mysql-db=sbtest --table-size=10000 --tables=20 --threads=100 --events=999999999 --report-interval=10 --db-ps-mode=disable --forced-shutdown=1 run
sysbench /usr/share/sysbench/oltp_read_only.lua --time=300 --mysql-host=192.168.68.136 --mysql-port=6033 --mysql-user=wr --mysql-password=lhr --mysql-db=sbtest --table-size=10000 --tables=20 --threads=100 --events=999999999 --report-interval=10 --db-ps-mode=disable --forced-shutdown=1 run
- Query the read/write separation monitoring data
MySQL [(none)]> SELECT hostgroup,schemaname , username , digest , sum_time, count_star, substr(digest_text,1.60) FROM stats_mysql_query_digest where schemaname='sbtest' ORDER BY sum_time DESC;
+-----------+------------+----------+--------------------+----------+------------+--------------------------------------- -----------------------+
| hostgroup | schemaname | username | digest | sum_time | count_star | substr(digest_text,1.60) |
+-----------+------------+----------+--------------------+----------+------------+--------------------------------------- -----------------------+
| 10 | sbtest | wr | 0x76607360EFEAC208 | 84059739 | 37 | INSERT INTO sbtest14(k, c, pad) VALUES(? ,? ,?) . (? ,? ,?) . (?|
| 10 | sbtest | wr | 0x02834C12D1767CBF | 81396186 | 37 | INSERT INTO sbtest19(k, c, pad) VALUES(? ,? ,?) . (? ,? ,?) . (?|
| 10 | sbtest | wr | 0xF175422CAEB5052B | 80172428 | 37 | INSERT INTO sbtest5(k, c, pad) VALUES(? ,? ,?) . (? ,? ,?) . (? .|
| 10 | sbtest | wr | 0xD264943870461B52 | 78250552 | 37 | INSERT INTO sbtest9(k, c, pad) VALUES(? ,? ,?) . (? ,? ,?) . (? .|
| 10 | sbtest | wr | 0x2F9D0B4C12C50457 | 75761155 | 37 | INSERT INTO sbtest13(k, c, pad) VALUES(? ,? ,?) . (? ,? ,?) . (?|
| 10 | sbtest | wr | 0x0482F61CCAD957B8 | 74841767 | 37 | INSERT INTO sbtest16(k, c, pad) VALUES(? ,? ,?) . (? ,? ,?) . (?|
| 10 | sbtest | wr | 0x2BA639A0C593250B | 74744554 | 37 | INSERT INTO sbtest20(k, c, pad) VALUES(? ,? ,?) . (? ,? ,?) . (?|
| 10 | sbtest | wr | 0x131C045B3F7FC633 | 74710835 | 37 | INSERT INTO sbtest12(k, c, pad) VALUES(? ,? ,?) . (? ,? ,?) . (?|
| 10 | sbtest | wr | 0x9677C76C4DF88251 | 74446715 | 37 | INSERT INTO sbtest6(k, c, pad) VALUES(? ,? ,?) . (? ,? ,?) . (? .|
| 10 | sbtest | wr | 0x36760936592E8530 | 74289312 | 37 | INSERT INTO sbtest2(k, c, pad) VALUES(? ,? ,?) . (? ,? ,?) . (? .|
| 10 | sbtest | wr | 0xEFBA5FC0C8412297 | 72425766 | 37 | INSERT INTO sbtest18(k, c, pad) VALUES(? ,? ,?) . (? ,? ,?) . (?|
| 10 | sbtest | wr | 0xDF47484FFE945EDD | 70572836 | 37 | INSERT INTO sbtest8(k, c, pad) VALUES(? ,? ,?) . (? ,? ,?) . (? .|
| 10 | sbtest | wr | 0x27D376AC1710C980 | 66516942 | 37 | INSERT INTO sbtest11(k, c, pad) VALUES(? ,? ,?) . (? ,? ,?) . (?|
| 10 | sbtest | wr | 0x4D433FDEA1B945C1 | 65829143 | 37 | INSERT INTO sbtest10(k, c, pad) VALUES(? ,? ,?) . (? ,? ,?) . (?|
| 10 | sbtest | wr | 0xFF6D9C6F32545951 | 65739929 | 37 | INSERT INTO sbtest1(k, c, pad) VALUES(? ,? ,?) . (? ,? ,?) . (? .|
| 10 | sbtest | wr | 0x772AE6B66160E91E | 65271284 | 37 | INSERT INTO sbtest7(k, c, pad) VALUES(? ,? ,?) . (? ,? ,?) . (? .|
| 10 | sbtest | wr | 0x4F8D29910113CAE6 | 64822306 | 37 | INSERT INTO sbtest15(k, c, pad) VALUES(? ,? ,?) . (? ,? ,?) . (?|
| 10 | sbtest | wr | 0x7502AE74F4B0113E | 64133812 | 37 | INSERT INTO sbtest4(k, c, pad) VALUES(? ,? ,?) . (? ,? ,?) . (? .|
| 10 | sbtest | wr | 0x4B083B064FF4F9D9 | 61931719 | 37 | INSERT INTO sbtest17(k, c, pad) VALUES(? ,? ,?) . (? ,? ,?) . (?|
| 10 | sbtest | wr | 0x3C6D29F861CD6572 | 60387247 | 37 | INSERT INTO sbtest3(k, c, pad) VALUES(? ,? ,?) . (? ,? ,?) . (? .|
| 10 | sbtest | wr | 0x8965AD5701787BC0 | 33638613 | 1 | CREATE INDEX k_3 ON sbtest3(k) |
| 10 | sbtest | wr | 0xB12065B0D79AC0DD | 33288092 | 1 | CREATE INDEX k_17 ON sbtest17(k) |
| 10 | sbtest | wr | 0xC46D61BAA605D7D3 | 32926000 | 1 | CREATE INDEX k_1 ON sbtest1(k) |
| 10 | sbtest | wr | 0x2BF8C8E7084502D5 | 32904517 | 1 | CREATE INDEX k_7 ON sbtest7(k) |
| 10 | sbtest | wr | 0x6F22CFB8FF512B02 | 32625659 | 1 | CREATE INDEX k_4 ON sbtest4(k) |
| 10 | sbtest | wr | 0x14A86D647A425E21 | 31622991 | 1 | CREATE INDEX k_15 ON sbtest15(k) |
| 10 | sbtest | wr | 0xF9D03F580356BB68 | 31573312 | 1 | CREATE INDEX k_11 ON sbtest11(k) |
| 10 | sbtest | wr | 0xA43F49E4ADA080FB | 31346640 | 1 | CREATE INDEX k_10 ON sbtest10(k) |
| 10 | sbtest | wr | 0x922B9C1E888EB4C9 | 29435206 | 1 | CREATE INDEX k_8 ON sbtest8(k) |
| 10 | sbtest | wr | 0x409A0DA0B5B6EEF9 | 28482669 | 1 | CREATE INDEX k_2 ON sbtest2(k) |
| 10 | sbtest | wr | 0xE4300864715B3688 | 28000903 | 1 | CREATE INDEX k_20 ON sbtest20(k) |
| 10 | sbtest | wr | 0x4DE9E56B5EF734F2 | 27918352 | 1 | CREATE INDEX k_6 ON sbtest6(k) |
| 10 | sbtest | wr | 0x82CE0656182236D8 | 27909444 | 1 | CREATE INDEX k_12 ON sbtest12(k) |
| 10 | sbtest | wr | 0xFE8EAD5ACC9FEEDE | 27273704 | 1 | CREATE INDEX k_18 ON sbtest18(k) |
| 10 | sbtest | wr | 0x35A492B2AB47EB41 | 26754664 | 1 | CREATE INDEX k_16 ON sbtest16(k) |
| 10 | sbtest | wr | 0xF5B52253F5260086 | 21401807 | 1 | CREATE INDEX k_9 ON sbtest9(k) |
| 10 | sbtest | wr | 0xA1B769A0F4E9637C | 21271034 | 1 | CREATE INDEX k_13 ON sbtest13(k) |
| 10 | sbtest | wr | 0xFBE6F4A5E871D069 | 21202873 | 1 | CREATE INDEX k_5 ON sbtest5(k) |
| 10 | sbtest | wr | 0x004D21922AA0CC4C | 19075137 | 1 | CREATE INDEX k_19 ON sbtest19(k) |
| 10 | sbtest | wr | 0x6057175824222B09 | 18110480 | 1 | CREATE INDEX k_14 ON sbtest14(k) |
| 10 | sbtest | wr | 0x5143272478FE391F | 5837193 | 1 | INSERT INTO sbtest13(k, c, pad) VALUES(? ,? ,?) . (? ,? ,?) . (?|
| 10 | sbtest | wr | 0xD9E2214392AB9E0D | 2791511 | 1 | INSERT INTO sbtest9(k, c, pad) VALUES(? ,? ,?) . (? ,? ,?) . (? .|
| 10 | sbtest | wr | 0x4AC5841F976F5A1A | 1686763 | 2 | CREATE TABLE sbtest5( id INTEGER NOT NULL AUTO_INCREMENT, k |
| 10 | sbtest | wr | 0x0E320961406063D2 | 1608309 | 1 | INSERT INTO sbtest3(k, c, pad) VALUES(? ,? ,?) . (? ,? ,?) . (? .|
| 10 | sbtest | wr | 0x1886E479A84E6EF3 | 1364378 | 1 | INSERT INTO sbtest19(k, c, pad) VALUES(? ,? ,?) . (? ,? ,?) . (?|
| 10 | sbtest | wr | 0xDC0701A550CF81E1 | 1315637 | 2 | CREATE TABLE sbtest14( id INTEGER NOT NULL AUTO_INCREMENT, k |
| 10 | sbtest | wr | 0xDE42F97C93E70D2F | 1213739 | 2 | CREATE TABLE sbtest9( id INTEGER NOT NULL AUTO_INCREMENT, k |
| 10 | sbtest | wr | 0x69426F34842FCBCB | 1167716 | 1 | INSERT INTO sbtest16(k, c, pad) VALUES(? ,? ,?) . (? ,? ,?) . (?|
| 10 | sbtest | wr | 0x3455DC796FFE13FF | 1096589 | 2 | CREATE TABLE sbtest19( id INTEGER NOT NULL AUTO_INCREMENT, k |
| 10 | sbtest | wr | 0xCB672EA01B2BCC66 | 1059624 | 2 | CREATE TABLE sbtest18( id INTEGER NOT NULL AUTO_INCREMENT, k |
| 10 | sbtest | wr | 0x68A680665F3A4F7F | 1046881 | 1 | INSERT INTO sbtest18(k, c, pad) VALUES(? ,? ,?) . (? ,? ,?) . (?|
| 10 | sbtest | wr | 0x988823E25FA87160 | 1008422 | 2 | CREATE TABLE sbtest7( id INTEGER NOT NULL AUTO_INCREMENT, k |
| 10 | sbtest | wr | 0x630131CEA842636C | 941575 | 1 | INSERT INTO sbtest17(k, c, pad) VALUES(? ,? ,?) . (? ,? ,?) . (?|
| 10 | sbtest | wr | 0xCFABCFBA3338DFE6 | 883756 | 2 | CREATE TABLE sbtest13( id INTEGER NOT NULL AUTO_INCREMENT, k |
| 10 | sbtest | wr | 0x38C679BD1A2B850D | 800858 | 2 | CREATE TABLE sbtest20( id INTEGER NOT NULL AUTO_INCREMENT, k |
| 10 | sbtest | wr | 0xA8C074E066D84361 | 745750 | 2 | CREATE TABLE sbtest2( id INTEGER NOT NULL AUTO_INCREMENT, k |
| 10 | sbtest | wr | 0x644F2A01D1AEE6F0 | 618114 | 1 | INSERT INTO sbtest8(k, c, pad) VALUES(? ,? ,?) . (? ,? ,?) . (? .|
| 10 | sbtest | wr | 0xA6A432C624F814B1 | 588508 | 1 | INSERT INTO sbtest7(k, c, pad) VALUES(? ,? ,?) . (? ,? ,?) . (? .|
| 10 | sbtest | wr | 0xAC072AB4E74DCA04 | 587709 | 1 | INSERT INTO sbtest1(k, c, pad) VALUES(? ,? ,?) . (? ,? ,?) . (? .|
| 10 | sbtest | wr | 0x6A5DF2EE9E492E4E | 544548 | 1 | INSERT INTO sbtest4(k, c, pad) VALUES(? ,? ,?) . (? ,? ,?) . (? .|
| 10 | sbtest | wr | 0xB483ABDF2ACB307D | 523796 | 2 | CREATE TABLE sbtest16( id INTEGER NOT NULL AUTO_INCREMENT, k |
| 10 | sbtest | wr | 0xDD3B26A209175EF7 | 520238 | 1 | INSERT INTO sbtest10(k, c, pad) VALUES(? ,? ,?) . (? ,? ,?) . (?|
| 10 | sbtest | wr | 0x1061831367EE99C7 | 453881 | 1 | INSERT INTO sbtest14(k, c, pad) VALUES(? ,? ,?) . (? ,? ,?) . (?|
| 10 | sbtest | wr | 0x7F93BDE97051D79A | 440020 | 2 | CREATE TABLE sbtest6( id INTEGER NOT NULL AUTO_INCREMENT, k |
| 10 | sbtest | wr | 0x806A9CCB80119BE2 | 417349 | 2 | CREATE TABLE sbtest1( id INTEGER NOT NULL AUTO_INCREMENT, k |
| 10 | sbtest | wr | 0x43CA6E3D0072BCBE | 402073 | 2 | CREATE TABLE sbtest12( id INTEGER NOT NULL AUTO_INCREMENT, k |
| 10 | sbtest | wr | 0xA204975AD230A23B | 400655 | 2 | CREATE TABLE sbtest8( id INTEGER NOT NULL AUTO_INCREMENT, k |
| 10 | sbtest | wr | 0x11E6BAC23207DD78 | 381162 | 1 | INSERT INTO sbtest2(k, c, pad) VALUES(? ,? ,?) . (? ,? ,?) . (? .|
| 10 | sbtest | wr | 0x42B1839D8797EDCB | 379488 | 1 | INSERT INTO sbtest12(k, c, pad) VALUES(? ,? ,?) . (? ,? ,?) . (?|
| 10 | sbtest | wr | 0x583CBA28271C4365 | 357866 | 2 | CREATE TABLE sbtest4( id INTEGER NOT NULL AUTO_INCREMENT, k |
| 10 | sbtest | wr | 0x6ACC6500F6722004 | 340867 | 2 | CREATE TABLE sbtest15( id INTEGER NOT NULL AUTO_INCREMENT, k |
| 10 | sbtest | wr | 0x5408EB0F722B3B6F | 324340 | 2 | CREATE TABLE sbtest10( id INTEGER NOT NULL AUTO_INCREMENT, k |
| 10 | sbtest | wr | 0xCD3FA57950F3E362 | 293837 | 1 | INSERT INTO sbtest5(k, c, pad) VALUES(? ,? ,?) . (? ,? ,?) . (? .|
| 10 | sbtest | wr | 0x4196561D18B78360 | 285839 | 1 | INSERT INTO sbtest20(k, c, pad) VALUES(? ,? ,?) . (? ,? ,?) . (?|
| 10 | sbtest | wr | 0xE7DCF83C81EDEA8D | 265476 | 2 | CREATE TABLE sbtest11( id INTEGER NOT NULL AUTO_INCREMENT, k |
| 10 | sbtest | wr | 0xFA3A3817BE19ABB3 | 250778 | 2 | CREATE TABLE sbtest3( id INTEGER NOT NULL AUTO_INCREMENT, k |
| 10 | sbtest | wr | 0x6B8F384E1250D83C | 247460 | 2 | CREATE TABLE sbtest17( id INTEGER NOT NULL AUTO_INCREMENT, k |
| 10 | sbtest | wr | 0x26E4B187688CC6BE | 218255 | 1 | INSERT INTO sbtest6(k, c, pad) VALUES(? ,? ,?) . (? ,? ,?) . (? .|
| 10 | sbtest | wr | 0x9E18B2E0420BA351 | 200861 | 1 | INSERT INTO sbtest15(k, c, pad) VALUES(? ,? ,?) . (? ,? ,?) . (?|
| 10 | sbtest | wr | 0xAB7D4ACFF578DC61 | 199109 | 1 | INSERT INTO sbtest11(k, c, pad) VALUES(? ,? ,?) . (? ,? ,?) . (?|
+-----------+------------+----------+--------------------+----------+------------+--------------------------------------- -----------------------+
80 rows in set (0.71 sec)
MySQL [(none)]> SELECT * FROM stats_mysql_commands_counters WHERE Total_cnt;
+-----------------+---------------+-----------+-----------+-----------+---------+---------+----------+----------+-------- ---+-----------+--------+--------+---------+----------+
| Command | Total_Time_us | Total_cnt | cnt_100us | cnt_500us | cnt_1ms | cnt_5ms | cnt_10ms | cnt_50ms | cnt_100ms | cnt_500ms | cnt_1s | cnt_5s | cnt_10s | cnt_INFs |
+-----------------+---------------+-----------+-----------+-----------+---------+---------+----------+----------+-------- ---+-----------+--------+--------+---------+----------+
| BEGIN | 2793013652 | 15681 | 95 | 888 | 1864 | 5806 | 2671 | 2882 | 447 | 266 | 33 | 533 | 190 | 6 |
| COMMIT | 4360624460 | 15575 | 3 | 31 | 58 | 191 | 138 | 1031 | 2337 | 9827 | 1369 | 590 | 0 | 0 |
| CREATE_DATABASE | 99871 | 4 | 0 | 0 | 0 | 2 | 1 | 0 | 1 | 0 | 0 | 0 | 0 | 0 |
| CREATE_INDEX | 556762097 | 20 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 20 |
| CREATE_TABLE | 13849772 | 43 | 0 | 0 | 1 | 0 | 0 | 21 | 1 | 11 | 4 | 5 | 0 | 0 |
| DELETE | 603363949 | 14940 | 45 | 197 | 1637 | 5575 | 2424 | 2997 | 771 | 1081 | 136 | 77 | 0 | 0 |
| INSERT | 1734742355 | 15812 | 38 | 339 | 1872 | 6251 | 2484 | 2887 | 586 | 590 | 138 | 605 | 21 | 1 |
| SELECT | 3704563235 | 319925 | 965 | 12937 | 109961 | 96434 | 40564 | 44484 | 7667 | 6460 | 376 | 77 | 0 | 0 |
| UPDATE | 1788596841 | 29900 | 45 | 166 | 2336 | 9589 | 4487 | 6847 | 2123 | 3682 | 445 | 180 | 0 | 0 |
| SHOW | 58391 | 2 | 0 | 0 | 0 | 1 | 0 | 0 | 1 | 0 | 0 | 0 | 0 | 0 |
| UNKNOWN | 152706 | 3 | 0 | 0 | 0 | 1 | 0 | 1 | 0 | 1 | 0 | 0 | 0 | 0 |
+-----------------+---------------+-----------+-----------+-----------+---------+---------+----------+----------+-------- ---+-----------+--------+--------+---------+----------+
11 rows in set (0.11 sec)
Copy the codeRead/write separation of data can be observed.
Six, failover
Check SSH, replication, and MHA status on the Manager node.
docker exec -it MHA-LHR-Monitor-ip134 bash
masterha_check_ssh --conf=/etc/mha/mha.cnf
masterha_check_repl --conf=/etc/mha/mha.cnf
masterha_check_status --conf=/etc/mha/mha.cnf
Start the MHA monitoring process
nohup masterha_manager --conf=/etc/mha/mha.cnf --ignore_last_failover < /dev/null > /usr/local/mha/manager_start.log 2>&1 &
-- Stop the MHA monitoring process
masterha_stop --conf=/etc/mha/mha.cnf
[root@MHA-LHR-Monitor-ip134 /]# masterha_check_status --conf=/etc/mha/mha.cnf
mha (pid:3738) is running(0:PING_OK), master:192.16868.131.
Copy the codeNext, break down the main library and continue observing ProxySQL:
-- Break down the main library
docker stop MHA-LHR-Master1-ip131
Copy the codeThe MHA automatically performed a failover, switched the primary library to 132 and sent an alarm email:
In this case, look at ProxySQL:
MySQL [(none)]> select * from mysql_servers;
+--------------+----------------+------+-----------+--------+--------+-------------+-----------------+------------------- --+---------+----------------+---------+
| hostgroup_id | hostname | port | gtid_port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+----------------+------+-----------+--------+--------+-------------+-----------------+------------------- --+---------+----------------+---------+
| 10 | 192.16868.132. | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | |
| 20 | 192.16868.131. | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | |
| 20 | 192.16868.133. | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | |
| 20 | 192.16868.132. | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | |
+--------------+----------------+------+-----------+--------+--------+-------------+-----------------+------------------- --+---------+----------------+---------+
4 rows in set (0.05 sec)
MySQL [(none)]> 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.16868.132. | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | |
| 20 | 192.16868.131. | 3306 | 0 | SHUNNED | 1 | 0 | 1000 | 0 | 0 | 0 | |
| 20 | 192.16868.133. | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | |
| 20 | 192.16868.132. | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | |
+--------------+----------------+------+-----------+---------+--------+-------------+-----------------+------------------ ---+---------+----------------+---------+
4 rows in set (1.26 sec)
Copy the codeYou can see that the 131 has become SHUNNED, and ProxySQL avoids the host.
The load will be distributed to 132 and 133. The test will not be performed here.
Next, start 131 and join the original master slave environment as a slave library:
- start the 131
docker start MHA-LHR-Master1-ip131
Find the recovered statement in the log file of 134
grep "All other slaves should start replication from here" /usr/local/mha/manager_running.log
-- Execute restore on 131
mysql -uroot -plhr -h192168.68.131. -P3306
CHANGE MASTER TO MASTER_HOST='192.168.68.132',
MASTER_PORT=3306,
MASTER_AUTO_POSITION=1,
MASTER_USER='repl',
MASTER_PASSWORD='lhr';
start slave;
show slave status \G
-- Set read-only
set global read_only=1;
Copy the codeQuery ProxySQL:
MySQL [(none)]> select * from mysql_servers;
+--------------+----------------+------+-----------+--------+--------+-------------+-----------------+------------------- --+---------+----------------+---------+
| hostgroup_id | hostname | port | gtid_port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+----------------+------+-----------+--------+--------+-------------+-----------------+------------------- --+---------+----------------+---------+
| 10 | 192.16868.132. | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | |
| 20 | 192.16868.131. | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | |
| 20 | 192.16868.133. | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | |
| 20 | 192.16868.132. | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | |
+--------------+----------------+------+-----------+--------+--------+-------------+-----------------+------------------- --+---------+----------------+---------+
4 rows in set (0.06 sec)
Copy the codeYou can see that 131 is read-only. If you want 132 to write only, you can delete the related record:
MySQL [(none)]> delete from mysql_servers where hostgroup_id=20 and hostname='192.168.68.132';
Query OK, 1 row affected (0.06 sec)
MySQL [(none)]> load mysql servers to runtime;
Query OK, 0 rows affected (0.68 sec)
MySQL [(none)]> save mysql servers to disk;
Query OK, 0 rows affected (0.10 sec)
MySQL [(none)]> select * from mysql_servers;
+--------------+----------------+------+-----------+--------+--------+-------------+-----------------+------------------- --+---------+----------------+---------+
| hostgroup_id | hostname | port | gtid_port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+----------------+------+-----------+--------+--------+-------------+-----------------+------------------- --+---------+----------------+---------+
| 10 | 192.16868.132. | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | |
| 20 | 192.16868.131. | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | |
| 20 | 192.16868.133. | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | |
+--------------+----------------+------+-----------+--------+--------+-------------+-----------------+------------------- --+---------+----------------+---------+
3 rows in set (0.05 sec)
MySQL [(none)]> 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.16868.132. | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | |
| 20 | 192.16868.133. | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | |
| 20 | 192.16868.131. | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | |
+--------------+----------------+------+-----------+--------+--------+-------------+-----------------+------------------- --+---------+----------------+---------+
3 rows in set (0.94 sec)
Copy the codeYou can see that 132 is the master library, 131 and 133 are the slave libraries. Orchestrator interface:
7. Results of interface monitoring (ProxySQL itself +PMM monitoring)
7.1 Built-in monitoring in ProxySQL
ProxySQL Monitoring result:
7.2 PMM monitoring
Monitoring can be done automatically using ProxySQL itself, or by using the PMM. Percona Monitoring and Management (PMM) is a free, open source platform for managing and Monitoring database performance that runs in a Docker environment. It proactively manages and monitors databases such as MySQL (AWS RDS MySQL, Aurora MySQL, and user-built MySQL instances), MariaDB, MongoDB, and PostgreSQL. It also monitors ProxySQL middleware, and provides various metrics and alarm methods.
The PMM monitors ProxySQL using the following commands:
pmm-admin add proxysql --username=root --password= LHR --host=192.168.66.35 --port=26032 --service-name= proxysqL-192.168.66.35-26032
Copy the codePMM monitoring results:
About Me
● The author of this article: Wheat Seedling, part of the content is arranged from the network, if there is infringement, please contact wheat seedling to delete ● This article in personal wechat public number (DB treasure) has been updated simultaneously ● QQ group number: 230161599, 618766405, wechat group private chat ● Personal QQ number (646634621), wechat account (db_bao), indicate the reason for adding ● Completed in Xi ‘an in March 2021 ● Updated date: March 2021 ● All rights reserved
● Wheat Seedling wechat store: weidian.com/?userid=793… Low wheat seedlings in the publication of the database class series: blog.itpub.net/26736162/vi… Low wheat seedlings OCP, OCM, high availability, the DBA course (Oracle, MySQL and no () : blog.itpub.net/26736162/vi… Low database written interview question and answer: mp.weixin.qq.com/s/Vm5PqNcDc…
Use wechat client to scan the following TWO-DIMENSIONAL code to follow the wechat public account of Wheat Seedling (DBbao) and QQ group (DBA Baodian), add Wheat seedling wechat, learn the most practical database technology.