【MySQL】数据库中间件--ProxySQL+MGR-创新互联
[root@wallet01 ~]# mysql -uroot -p < addition_to_sys.sql
Enter password:
[root@wallet01 ~]# mysql -uroot -p
Enter password:
mysql> select * from sys.gr_member_routing_candidate_status;
+------------------+-----------+---------------------+----------------------+
| viable_candidate | read_only | transactions_behind | transactions_to_cert |
+------------------+-----------+---------------------+----------------------+
| YES | NO | 0 | 0 |
+------------------+-----------+---------------------+----------------------+
1 row in set (0.00 sec)
mysql> create user monitor@'192.168.40.%' identified by 'monitor@2019';
Query OK, 0 rows affected (0.11 sec)
mysql> grant select on sys.* to monitor@'192.168.40.%';
Query OK, 0 rows affected (0.04 sec)
[root@wallet02 ~]# mysql -uroot -p
Enter password:
mysql> select * from sys.gr_member_routing_candidate_status;
+------------------+-----------+---------------------+----------------------+
| viable_candidate | read_only | transactions_behind | transactions_to_cert |
+------------------+-----------+---------------------+----------------------+
| YES | YES | 0 | 0 |
+------------------+-----------+---------------------+----------------------+
1 row in set (0.00 sec)
[root@wallet03 ~]# mysql -uroot -p
Enter password:
mysql> select * from sys.gr_member_routing_candidate_status;
+------------------+-----------+---------------------+----------------------+
| viable_candidate | read_only | transactions_behind | transactions_to_cert |
+------------------+-----------+---------------------+----------------------+
| YES | YES | 0 | 0 |
+------------------+-----------+---------------------+----------------------+
1 row in set (0.00 sec)
[root@wallet04 ~]# cat <
insert into mysql_servers(hostgroup_id,hostname,port)
values(10,'192.168.40.34',3306),
(10,'192.168.40.35',3306),
(10,'192.168.40.36',3306);
Query OK, 3 rows affected (0.00 sec)
Admin>load mysql servers to runtime;
Query OK, 0 rows affected (0.01 sec)
Admin>save mysql servers to disk;
Query OK, 0 rows affected (0.42 sec)
Admin>select hostgroup_id,hostname,port,status,weight from mysql_servers;
+--------------+---------------+------+--------+--------+
| hostgroup_id | hostname | port | status | weight |
+--------------+---------------+------+--------+--------+
| 10 | 192.168.40.34 | 3306 | ONLINE | 1 |
| 10 | 192.168.40.35 | 3306 | ONLINE | 1 |
| 10 | 192.168.40.36 | 3306 | ONLINE | 1 |
+--------------+---------------+------+--------+--------+
3 rows in set (0.00 sec)
Admin>set mysql-monitor_username='monitor';
Query OK, 1 row affected (0.00 sec)
Admin>set mysql-monitor_password='monitor@2019';
Query OK, 1 row affected (0.00 sec)
Admin>load mysql variables to runtime;
Query OK, 0 rows affected (0.00 sec)
Admin>save mysql variables to disk;
Query OK, 136 rows affected (0.06 sec)
Admin>insert into mysql_group_replication_hostgroups(writer_hostgroup,backup_writer_hostgroup,reader_hostgroup,offline_hostgroup,active,max_writers,writer_is_also_reader,max_transactions_behind)
values(10,20,30,40,1,1,0,0);
Query OK, 1 row affected (0.00 sec)
Admin>load mysql servers to runtime;
Query OK, 0 rows affected (0.01 sec)
Admin>save mysql servers to disk;
Query OK, 0 rows affected (0.49 sec)
Admin>select * from mysql_group_replication_hostgroups\G
*************************** 1. row ***************************
writer_hostgroup: 10
backup_writer_hostgroup: 20
reader_hostgroup: 30
offline_hostgroup: 40
active: 1
max_writers: 1
writer_is_also_reader: 0
max_transactions_behind: 0
comment: NULL
1 row in set (0.00 sec)
Admin>select hostgroup_id, hostname, port,status from runtime_mysql_servers;
+--------------+---------------+------+--------+
| hostgroup_id | hostname | port | status |
+--------------+---------------+------+--------+
| 10 | 192.168.40.34 | 3306 | ONLINE |
| 30 | 192.168.40.35 | 3306 | ONLINE |
| 30 | 192.168.40.36 | 3306 | ONLINE |
+--------------+---------------+------+--------+
3 rows in set (0.00 sec)
Admin>insert into mysql_users(username,password,default_hostgroup,transaction_persistent)
values('sakila','sakila@2019',10,1);
Query OK, 1 row affected (0.00 sec)
Admin>load mysql users to runtime;
Query OK, 0 rows affected (0.00 sec)
Admin>save mysql users to disk;
Query OK, 0 rows affected (0.14 sec)
Admin>insert into mysql_query_rules(rule_id,active,match_digest,destination_hostgroup,apply)
values(1,1,'^SELECT.*FOR UPDATE$',10,1),(2,1,'^SELECT',30,1);
Query OK, 2 rows affected (0.00 sec)
Admin>load mysql query rules to runtime;
Query OK, 0 rows affected (0.00 sec)
Admin>save mysql query rules to disk;
Query OK, 0 rows affected (0.20 sec)
Admin>select hostgroup,digest_text from stats_mysql_query_digest;
+-----------+------------------------------------------------------------------------------------+
| hostgroup | digest_text |
+-----------+------------------------------------------------------------------------------------+
| 10 | DELETE FROM `rental` WHERE (`rental_id`=?) |
| 30 | SELECT * FROM `store` LIMIT ?, ? |
| 10 | SHOW COLUMNS FROM `sakila`.`customer` |
| 10 | SHOW COLUMNS FROM `sakila`.`actor` |
| 30 | SELECT * FROM `country` WHERE (`country_id`=?) LIMIT ? |
| 10 | UPDATE `country` SET `last_update`=? WHERE (`country_id`=?) |
| 30 | SELECT * FROM `country` LIMIT ?, ? |
创新互联建站是一家专注于网站制作、网站建设与策划设计,铁山港网站建设哪家好?创新互联建站做网站,专注于网站建设十余年,网设计领域的专业建站公司;建站业务涵盖:铁山港等地区。铁山港做网站价格咨询:028-86922220另外有需要云服务器可以了解下创新互联cdcxhl.cn，海内外云服务器15元起步，三天无理由+7*72小时售后在线，公司持有idc许可证，提供“云服务器、裸金属服务器、高防服务器、香港服务器、美国服务器、虚拟主机、免备案服务器”等云主机租用服务以及企业上云的综合解决方案，具有“安全稳定、简单易用、服务可用性高、性价比高”等特点与优势，专为企业上云打造定制，能够满足用户丰富、多元化的应用场景需求。
本文标题：【MySQL】数据库中间件--ProxySQL+MGR-创新互联
文章转载：http://kswjz.com/article/jgpjd.html
扫二维码与项目经理沟通
我们在微信上24小时期待你的声音
解答本文疑问/技术咨询/运营咨询/技术建议/互联网交流
