MGR multi main mode configuration load balancing
Experimental environment information
MySQL master-slave information
| IP | role |
|---|---|
| 188.188.0.68 | Node1 |
| 188.188.0.69 | Node2 |
| 188.188.0.70 | Node3 |
ProxySQL version: 2.0.3-29-g00f26d5
>>>Server and user configuration<<<
1. Add backend database server
Admin> SELECT * FROM mysql_servers;
Empty set (0.00 sec)
Admin> INSERT INTO mysql_servers(hostgroup_id,hostname,port,comment)
-> VALUES
-> (1,'188.188.0.68',3306,'Node1'),
-> (1,'188.188.0.69',3306,'Node2'),
-> (1,'188.188.0.70',3306,'Node3');
Query OK, 3 rows affected (0.00 sec)
Admin> SELECT * FROM mysql_servers;
+--------------+--------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname | port | gtid_port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+--------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| 1 | 188.188.0.68 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | Node1 |
| 1 | 188.188.0.69 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | Node2 |
| 1 | 188.188.0.70 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | Node3 |
+--------------+--------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
3 rows in set (0.00 sec)2. Configure monitoring
1) Create monitoring dependency view in MySQL
Since the monitoring of MGR depends on the sys.gr? Member? Routing? Candidate? Status view, which is not provided by the system, it needs to be established manually.
Related script address: Add to sys.sql script under https://github.com/lefred/mysql/gr/routing/check/.
Log in to the MGR master node and execute the script in the sys Library:
# mysql -uroot -p -S /data/database-3306/mysql-3306.sock sys < /root/mysql_gr_routing_check-master/addition_to_sys.sql
View results
mysql> show tables from sys like 'gr%'; +------------------------------------+ | Tables_in_sys (gr%) | +------------------------------------+ | gr_member_routing_candidate_status | +------------------------------------+ 1 row in set (0.00 sec)
The view has been created.
2) Users required by MYSQL after adding ProXYSQL monitoring (the user needs to be created in MySQL Server):
Create settings monitoring user in MySQL:
mysql> GRANT select on sys.gr_member_routing_candidate_status TO 'psql_monitor'@'188.188.0.%' IDENTIFIED BY '987654'; mysql> show grants for 'psql_monitor'@'188.188.0.%' ; +--------------------------------------------------------------------------------------------+ | Grants for psql_monitor@188.188.0.% | +--------------------------------------------------------------------------------------------+ | GRANT PROCESS, REPLICATION CLIENT ON *.* TO 'psql_monitor'@'188.188.0.%' | | GRANT SELECT ON `sys`.`gr_member_routing_candidate_status` TO 'psql_monitor'@'188.188.0.%' | +--------------------------------------------------------------------------------------------+ 2 rows in set (0.00 sec) mysql> flush privileges; Query OK, 0 rows affected (0.00 sec)
To view configuring users in ProxySQL:
Admin> select * from global_variables where variable_name='mysql-monitor_username'; +------------------------+----------------+ | variable_name | variable_value | +------------------------+----------------+ | mysql-monitor_username | monitor | +------------------------+----------------+ 1 row in set (0.00 sec) Admin> select * from global_variables where variable_name='mysql-monitor_password'; +------------------------+----------------+ | variable_name | variable_value | +------------------------+----------------+ | mysql-monitor_password | monitor | +------------------------+----------------+ 1 row in set (0.00 sec)
Configure monitoring users:
Admin> UPDATE global_variables SET variable_value='psql_monitor' WHERE variable_name='mysql-monitor_username'; Query OK, 1 row affected (0.00 sec) Admin> UPDATE global_variables SET variable_value='987654' WHERE variable_name='mysql-monitor_password'; Query OK, 1 row affected (0.01 sec) Admin> select * from global_variables where variable_name='mysql-monitor_username'; +------------------------+----------------+ | variable_name | variable_value | +------------------------+----------------+ | mysql-monitor_username | psql_monitor | +------------------------+----------------+ 1 row in set (0.00 sec) Admin> select * from global_variables where variable_name='mysql-monitor_password'; +------------------------+----------------+ | variable_name | variable_value | +------------------------+----------------+ | mysql-monitor_password | 987654 | +------------------------+----------------+ 1 row in set (0.00 sec)
3) Modify monitoring interval
Admin> SELECT * FROM global_variables WHERE variable_name IN ('mysql-monitor_connect_interval','mysql-monitor_ping_interval','mysql-monitor_read_only_interval');
+----------------------------------+----------------+
| variable_name | variable_value |
+----------------------------------+----------------+
| mysql-monitor_connect_interval | 60000 |
| mysql-monitor_ping_interval | 10000 |
| mysql-monitor_read_only_interval | 1500 |
+----------------------------------+----------------+
3 rows in set (0.00 sec)
Admin> UPDATE global_variables SET variable_value='2000' WHERE variable_name IN ('mysql-monitor_connect_interval','mysql-monitor_ping_interval','mysql-monitor_read_only_interval');
Query OK, 3 rows affected (0.00 sec)4) Validation and persistence of configuration
Load configuration to RUNTIME layer:
Admin> LOAD MYSQL VARIABLES TO RUNTIME; Query OK, 0 rows affected (0.00 sec)
Persistent configuration to DISK layer:
Admin> SAVE MYSQL VARIABLES TO DISK; Query OK, 110 rows affected (0.02 sec)
3. Back end MySQL service health detection
1) To view the backend connection status:
Admin> SELECT * FROM monitor.mysql_server_connect_log ORDER BY time_start_us DESC LIMIT 10; +--------------+------+------------------+-------------------------+---------------+ | hostname | port | time_start_us | connect_success_time_us | connect_error | +--------------+------+------------------+-------------------------+---------------+ | 188.188.0.70 | 3306 | 1556620581265582 | 387 | NULL | | 188.188.0.69 | 3306 | 1556620581239483 | 413 | NULL | | 188.188.0.68 | 3306 | 1556620581213375 | 432 | NULL | | 188.188.0.70 | 3306 | 1556620579255264 | 381 | NULL | | 188.188.0.68 | 3306 | 1556620579234288 | 426 | NULL | | 188.188.0.69 | 3306 | 1556620579213311 | 492 | NULL | | 188.188.0.70 | 3306 | 1556620577259808 | 443 | NULL | | 188.188.0.68 | 3306 | 1556620577236530 | 437 | NULL | | 188.188.0.69 | 3306 | 1556620577213253 | 500 | NULL | | 188.188.0.70 | 3306 | 1556620575249404 | 468 | NULL | +--------------+------+------------------+-------------------------+---------------+ 10 rows in set (0.00 sec)
To view the backend survival status:
Admin> SELECT * FROM monitor.mysql_server_ping_log ORDER BY time_start_us DESC LIMIT 10; +--------------+------+------------------+----------------------+------------+ | hostname | port | time_start_us | ping_success_time_us | ping_error | +--------------+------+------------------+----------------------+------------+ | 188.188.0.70 | 3306 | 1556620607647791 | 85 | NULL | | 188.188.0.68 | 3306 | 1556620607631491 | 80 | NULL | | 188.188.0.69 | 3306 | 1556620607615190 | 89 | NULL | | 188.188.0.70 | 3306 | 1556620605653805 | 83 | NULL | | 188.188.0.69 | 3306 | 1556620605634458 | 85 | NULL | | 188.188.0.68 | 3306 | 1556620605615106 | 88 | NULL | | 188.188.0.70 | 3306 | 1556620603663711 | 84 | NULL | | 188.188.0.68 | 3306 | 1556620603639370 | 93 | NULL | | 188.188.0.69 | 3306 | 1556620603615027 | 94 | NULL | | 188.188.0.69 | 3306 | 1556620601666891 | 81 | NULL | +--------------+------+------------------+----------------------+------------+ 10 rows in set (0.00 sec)
2) Validation and persistence of configuration
After confirming that the backend service is normal, it can be loaded into the RUNTIME layer to enable and persist to the DISK layer:
Admin> SELECT * FROM runtime_mysql_servers; Empty set (0.00 sec) Admin> SELECT * FROM disk.mysql_servers; Empty set (0.00 sec)
Load configuration to RUNTIME layer:
Admin> LOAD MYSQL SERVERS TO RUNTIME; Query OK, 0 rows affected (0.01 sec)
Persistent configuration to DISK layer:
Admin> SAVE MYSQL SERVERS TO DISK; Query OK, 0 rows affected (0.03 sec) Admin> 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 | +--------------+--------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+ | 1 | 188.188.0.70 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | Node3 | | 1 | 188.188.0.69 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | Node2 | | 1 | 188.188.0.68 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | Node1 | +--------------+--------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+ 3 rows in set (0.00 sec) Admin> SELECT * FROM disk.mysql_servers; +--------------+--------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+ | hostgroup_id | hostname | port | gtid_port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment | +--------------+--------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+ | 1 | 188.188.0.68 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | Node1 | | 1 | 188.188.0.69 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | Node2 | | 1 | 188.188.0.70 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | Node3 | +--------------+--------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+ 3 rows in set (0.00 sec)
4. MySQL multi main architecture information configuration
1) Add multi master configuration
Admin> SELECT * FROM mysql_group_replication_hostgroups; Empty set (0.00 sec
Insert read-write, read-only group configuration information:
Admin> INSERT INTO mysql_group_replication_hostgroups(writer_hostgroup,backup_writer_hostgroup,reader_hostgroup,offline_hostgroup,active,max_writers,comment) VALUES (1,2,4,6,1,3,'MGR_MM'); Query OK, 1 row affected (0.00 sec)
explain:
The write group number is 1, the group of other writeable nodes is 2 (because MGR can be multi primary), the read-only group is 4, and the offline group is 6;
For the above servers, if you want to provide read and write functions at the same time, you can set the writer ﹣ is ﹣ also ﹣ reader to 1;
Admin> select * from mysql_group_replication_hostgroups; +------------------+-------------------------+------------------+-------------------+--------+-------------+-----------------------+-------------------------+---------+ | writer_hostgroup | backup_writer_hostgroup | reader_hostgroup | offline_hostgroup | active | max_writers | writer_is_also_reader | max_transactions_behind | comment | +------------------+-------------------------+------------------+-------------------+--------+-------------+-----------------------+-------------------------+---------+ | 1 | 2 | 4 | 6 | 1 | 3 | 0 | 0 | MGR_MM | +------------------+-------------------------+------------------+-------------------+--------+-------------+-----------------------+-------------------------+---------+ 1 row in set (0.00 sec)
2) Configuration takes effect
Load the modified configuration into the RUNTIME layer:
Admin> LOAD MYSQL SERVERS TO RUNTIME; Query OK, 0 rows affected (0.00 sec)
To view MySQL groups:
Admin> 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 | +--------------+--------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+ | 1 | 188.188.0.69 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | Node2 | | 1 | 188.188.0.70 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | Node3 | | 1 | 188.188.0.68 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | Node1 | +--------------+--------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+ 3 rows in set (0.00 sec)
As you can see, all three nodes are group 1, because we set max_writers to 3, which means that at most three nodes are required to provide write services.
be careful:
If the number of nodes exceeds the setting of max_writers, the extra nodes will be put into group 2. If the group number is 1 and there is no change for a long time, and no other errors can be found, please execute on any MySQL node:
mysql> stop group_replication;
Manually create an exception to trigger the update of the server status by ProxySQL, and then check the runtime ﹣ MySQL ﹣ servers to identify it. After identifying it, add the node again.
mysql> start group_replication;
3) View MySQL status
Admin> SELECT * FROM monitor.mysql_server_group_replication_log ORDER BY time_start_us DESC LIMIT 10; +--------------+------+------------------+-----------------+------------------+-----------+---------------------+-------+ | hostname | port | time_start_us | success_time_us | viable_candidate | read_only | transactions_behind | error | +--------------+------+------------------+-----------------+------------------+-----------+---------------------+-------+ | 188.188.0.70 | 3306 | 1558246065466522 | 1948 | YES | NO | 0 | NULL | | 188.188.0.69 | 3306 | 1558246065466059 | 1907 | YES | NO | 0 | NULL | | 188.188.0.68 | 3306 | 1558246065465594 | 1850 | YES | NO | 0 | NULL | | 188.188.0.70 | 3306 | 1558246060466448 | 1874 | YES | NO | 0 | NULL | | 188.188.0.69 | 3306 | 1558246060465986 | 1896 | YES | NO | 0 | NULL | | 188.188.0.68 | 3306 | 1558246060465515 | 1834 | YES | NO | 0 | NULL | | 188.188.0.70 | 3306 | 1558246055466430 | 2342 | YES | NO | 0 | NULL | | 188.188.0.69 | 3306 | 1558246055465968 | 1927 | YES | NO | 0 | NULL | | 188.188.0.68 | 3306 | 1558246055465503 | 1882 | YES | NO | 0 | NULL | | 188.188.0.70 | 3306 | 1558246050466312 | 1895 | YES | NO | 0 | NULL | +--------------+------+------------------+-----------------+------------------+-----------+---------------------+-------+ 10 rows in set (0.00 sec)
The attributes of all three nodes have been recognized, and there is no error message.
4) Save configuration
Admin> SAVE MYSQL SERVERS TO DISK; Query OK, 0 rows affected (0.03 sec) Admin> SELECT * FROM disk.mysql_servers; +--------------+--------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+ | hostgroup_id | hostname | port | gtid_port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment | +--------------+--------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+ | 1 | 188.188.0.68 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | Node1 | | 1 | 188.188.0.69 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | Node2 | | 1 | 188.188.0.70 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | Node3 | +--------------+--------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+ 3 rows in set (0.00 sec)
5. Set MySQL users (used to connect to the backend)
1) Create a business account in the backend MySQL
Admin> SELECT * FROM mysql_users; Empty set (0.00 sec)
--Create a business library and read / write account on the back-end MySQL:
mysql> CREATE DATABASE test; Query OK, 1 row affected (0.01 sec) mysql> GRANT select,insert,update,delete ON test.* TO 'test_rw'@'188.188.0.%' IDENTIFIED BY '123456'; Query OK, 0 rows affected, 1 warning (0.00 sec)
2) Add business account to ProxySQL
--Add the business account on the back-end Mysql to MySQL users
Admin> INSERT INTO mysql_users(username,password,default_hostgroup,comment) VALUES ('test_rw','123456',1,'Business read-write account');
Query OK, 1 row affected (0.00 sec)
Admin> SELECT * FROM mysql_users;
+----------+----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+--------------------+
| username | password | active | use_ssl | default_hostgroup | default_schema | schema_locked | transaction_persistent | fast_forward | backend | frontend | max_connections | comment |
+----------+----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+--------------------+
| test_rw | 123456 | 1 | 0 | 1 | NULL | 0 | 1 | 0 | 1 | 1 | 10000 | Business read-write account |
+----------+----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+--------------------+
1 row in set (0.00 sec)3) Effectiveness of configuration
Again, load the configuration into the runtime for it to take effect.
Admin> LOAD MYSQL USERS TO RUNTIME; Query OK, 0 rows affected (0.00 sec) Admin> SELECT * FROM runtime_mysql_users; +----------+-------------------------------------------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+--------------------+ | username | password | active | use_ssl | default_hostgroup | default_schema | schema_locked | transaction_persistent | fast_forward | backend | frontend | max_connections | comment | +----------+-------------------------------------------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+--------------------+ | test_rw | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | 1 | 0 | 1 | | 0 | 1 | 0 | 0 | 1 | 10000 | Business read-write account | | test_rw | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | 1 | 0 | 1 | | 0 | 1 | 0 | 1 | 0 | 10000 | Business read-write account | +----------+-------------------------------------------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+--------------------+ 2 rows in set (0.00 sec)
Each user added has a pair of records, because each user has a user record in the front end and the back end;
4) Encrypted MEMORY layer user password
Obtain the password of the user after encryption from the RUNTIME layer, and update the MEMORY layer:
Admin> SELECT * FROM mysql_users; +----------+----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+--------------------+ | username | password | active | use_ssl | default_hostgroup | default_schema | schema_locked | transaction_persistent | fast_forward | backend | frontend | max_connections | comment | +----------+----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+--------------------+ | test_rw | 123456 | 1 | 0 | 1 | NULL | 0 | 1 | 0 | 1 | 1 | 10000 | Business read-write account | +----------+----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+--------------------+ 1 row in set (0.00 sec) Admin> SAVE MYSQL USERS FROM RUNTIME; Query OK, 0 rows affected (0.00 sec) Admin> SELECT * FROM mysql_users; +----------+-------------------------------------------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+--------------------+ | username | password | active | use_ssl | default_hostgroup | default_schema | schema_locked | transaction_persistent | fast_forward | backend | frontend | max_connections | comment | +----------+-------------------------------------------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+--------------------+ | test_rw | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | 1 | 0 | 1 | | 0 | 1 | 0 | 1 | 1 | 10000 | Business read-write account | +----------+-------------------------------------------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+--------------------+ 1 row in set (0.00 sec)
5) Configuration persistence
Persist user information to DISK layer:
Admin> SELECT * FROM disk.mysql_users; Empty set (0.00 sec) Admin> SAVE MYSQL USERS TO DISK; Query OK, 0 rows affected (0.01 sec) Admin> SELECT * FROM disk.mysql_users; +----------+-------------------------------------------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+--------------------+ | username | password | active | use_ssl | default_hostgroup | default_schema | schema_locked | transaction_persistent | fast_forward | backend | frontend | max_connections | comment | +----------+-------------------------------------------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+--------------------+ | test_rw | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | 1 | 0 | 1 | | 0 | 1 | 0 | 1 | 1 | 10000 | Business read-write account | +----------+-------------------------------------------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+--------------------+ 1 row in set (0.00 sec)
6. Business account availability verification
At this point, after the user is added, you can use the client to connect to ProxySQL to try to connect.
Admin> select * from stats.stats_mysql_query_digest ; Empty set (0.01 sec)
No SQL statistics in ProxySQL;
[root@localhost ~]# mysql -u test_rw -p123456 -h 188.188.0.71 -P6033 -e "SELECT 1" mysql: [Warning] Using a password on the command line interface can be insecure. +---+ | 1 | +---+ | 1 | +---+
Note: the default connection port is 6033, not 6032(ProxySQL management port) or 3306(MySQL port).
[root@localhost ~]# mysql -u test_rw -p123456 -h 188.188.0.71 -P6033 -e "SELECT @@port" mysql: [Warning] Using a password on the command line interface can be insecure. +--------+ | @@port | +--------+ | 3306 | +--------+
The port of the backend MySQL service is queried here.
[root@localhost ~]# mysql -u test_rw -p123456 -h 188.188.0.71 -P6033 -e "delete from test.t1;" [root@localhost ~]# mysql -u test_rw -p123456 -h 188.188.0.71 -P6033 -e "insert into test.t1 values(1,'a');" [root@localhost ~]# mysql -u test_rw -p123456 -h 188.188.0.71 -P6033 -e "update test.t1 set name='b' where id=1;" [root@localhost ~]# mysql -u test_rw -p123456 -h 188.188.0.71 -P6033 -e "SELECT * from test.t1;" mysql: [Warning] Using a password on the command line interface can be insecure. +----+------+ | id | name | +----+------+ | 1 | b | +----+------+
All the above additions, deletions, modifications and queries have been carried out normally.
Admin> select * from stats.stats_mysql_query_digest ; +-----------+--------------------+----------+----------------+--------------------+--------------------------------------+------------+------------+------------+----------+----------+----------+ | hostgroup | schemaname | username | client_address | digest | digest_text | count_star | first_seen | last_seen | sum_time | min_time | max_time | +-----------+--------------------+----------+----------------+--------------------+--------------------------------------+------------+------------+------------+----------+----------+----------+ | 1 | information_schema | test_rw | | 0x79197FCA4792FA4C | update test.t1 set name=? where id=? | 2 | 1558252176 | 1558252177 | 4774 | 800 | 3974 | | 1 | information_schema | test_rw | | 0x1FAA742A35A78D70 | delete from test.t1 | 1 | 1558252124 | 1558252124 | 3833 | 3833 | 3833 | | 1 | information_schema | test_rw | | 0x72F235F8DDF4234E | insert into test.t1 values(?,?) | 3 | 1558251902 | 1558252156 | 14638 | 811 | 10222 | | 1 | information_schema | test_rw | | 0x38B78BFFAF23614A | SELECT * from test.t1 | 14 | 1558251448 | 1558252227 | 3253 | 149 | 318 | | 1 | information_schema | test_rw | | 0x5804011D7FE81F57 | insert into test.t1(name) values(?) | 4 | 1558251747 | 1558251882 | 8270 | 719 | 3928 | | 1 | information_schema | test_rw | | 0x831B091BA90D80E5 | SELECT @@port | 1 | 1558251443 | 1558251443 | 246 | 246 | 246 | | 1 | information_schema | test_rw | | 0x226CD90D52A2BA0B | select @@version_comment limit ? | 25 | 1558251443 | 1558252227 | 0 | 0 | 0 | +-----------+--------------------+----------+----------------+--------------------+--------------------------------------+------------+------------+------------+----------+----------+----------+ 7 rows in set (0.00 sec)
So far, the simplest server and user configuration has been completed!!!
In addition, you can also assign weights to node servers to control the pressure of each server.
>>>System parameter configuration<<<
Modify auto submit parameters:
Admin> show variables like '%auto%'; +---------------------------------------+-------+ | Variable_name | Value | +---------------------------------------+-------+ | mysql-forward_autocommit | false | | mysql-enforce_autocommit_on_reads | false | | mysql-autocommit_false_not_reusable | false | | mysql-autocommit_false_is_transaction | false | | mysql-auto_increment_delay_multiplex | 5 | +---------------------------------------+-------+ 5 rows in set (0.01 sec)
Modify the following 2 parameters:
1)
Admin> set mysql-forward_autocommit='true';
When true, the SET autocommit = 0 command is forwarded to the backend on MySQL.
2)
Admin> set mysql-autocommit_false_not_reusable = 'true';
When set to true, the connection with autocommit = 0 is not reused and is destroyed when the connection returns to the connection pool
Make configuration effective
Admin> save mysql variables to disk;
Make configuration persistent
Admin> load mysql variables to run;
>>>Pressure test<<<
Multiple main pressure tests can be conducted for MGR, but attention shall be paid to:
Because ProxySQL monitors the master-slave status by establishing a status view of sys.gr'member'routing'candidate'status on the back-end mysql, the query of this view is actually the summary of query results of multiple system tables. Therefore, accessing this view when MySQL is busy will cause the monitor to get information timeout and fail, and then misjudge the host state and modify the state of runtime ﹣ MySQL ﹣ servers, resulting in the disconnection of the connection back-end MGR thread. Therefore, it is better to replace this view with a table with the same name and write the server status in it, so that the timeout exception of getting content during the access of the monitor program will not be obtained, and the back-end connection thread will not be killed.
For MGR multi-mode pressure measurement, the following ideas can be referred to:
Due to the limitation and deficiency of MGR in the support of large transactions and the detection of transaction conflicts, it is impossible to test MGR directly in parallel. Oracle's official multi primary mode test is to test different test libraries on each node, that is to say, it can avoid the problem that the tools cannot be tested in parallel, and at the same time, it can reduce the possibility of conflict. Remember, multi primary mode must avoid the scene of hot data conflict.
For example, there are three nodes in the MGR cluster, A, B and C respectively. Then, at least three libraries need to be built during the pressure test. In this way, each tpcc pressure test uses one thread to test one library and starts multiple tpcc to test at the same time.
You can test this:
Node A:
./tpcc_start -h188.188.0.68 -P3306 -uroot -p '123456' -d tpcc1 -w 5 -c 1 -r 10 -l 30 -i 10 -f tpcc_mysql.log -t tpcc_mysql.rtx .....
Node B:
./tpcc_start -h188.188.0.69 -P3306 -uroot -p '123456' -d tpcc2 -w 5 -c 1 -r 10 -l 30 -i 10 -f tpcc_mysql.log -t tpcc_mysql.rtx .....
Node C:
./tpcc_start -h188.188.0.70 -P3306 -uroot -p '123456' -d tpcc3 -w 5 -c 1 -r 10 -l 30 -i 10 -f tpcc_mysql.log -t tpcc_mysql.rtx .....
However, the multi primary mode compression test will not have a good result, because the hot spots are too centralized, which will lead to many submission failures, and may lead to performance degradation.
If sysbench is used for pressure testing, the same idea can only be used in the way of multi library + single thread + multi test terminal.
~
~
complete!