{"id":525,"date":"2019-08-28T14:20:58","date_gmt":"2019-08-28T06:20:58","guid":{"rendered":"https:\/\/www.linuxdevops.cn\/?p=525"},"modified":"2020-12-14T12:02:52","modified_gmt":"2020-12-14T04:02:52","slug":"pyszcm1mzfzgzqhgkyhjdj","status":"publish","type":"post","link":"https:\/\/www.linuxdevops.cn\/2019\/08\/pyszcm1mzfzgzqhgkyhjdj\/","title":{"rendered":"MySQL8+MGR\u7ec4\u590d\u5236\u914d\u7f6e\u793a\u4f8b"},"content":{"rendered":"\n
\u53c2\u8003\u8d44\u6599\uff1a
https:\/\/github.com\/sysown\/proxysql\/wiki<\/a> #GitHub\u5b98\u65b9
https:\/\/github.com\/malongshuai\/proxysql\/wiki<\/a> #\u5927\u4f6c\u4e2d\u6587\u7ffb\u8bd1\u7248\n\nhttps:\/\/www.mysql.com\/<\/a> #Mysql \u5b98\u7f51
MySQL Group Replication\uff1aProxySQL\u4e2d\u7684\u539f\u751f\u652f\u6301
https:\/\/lefred.be\/content\/mysql-group-replication-native-support-in-proxysql\/<\/a>
MySQL MGR\u5b9e\u73b0\u5206\u6790 - \u6210\u5458\u7ba1\u7406\u4e0e\u6545\u969c\u6062\u590d\u5b9e\u73b0
https:\/\/www.cnblogs.com\/zyfd\/p\/9816571.html<\/a><\/p>\u76f8\u5173\u8d44\u6599\u53ef\u4ee5\u4e86\u89e3\u4e0b\u5927\u6982\u6d41\u7a0b\uff0c\u5b9e\u73b0\u539f\u7406\u3002proxysql \u548cMySQL\u5b89\u88c5 \u4e0a\u9762\u8d44\u6599\u4e2d\u6709<\/cite><\/blockquote>\n\n\n\u73af\u5883\u51c6\u5907\uff1a\n 192.168.17.133 mysql-master\n 192.168.17.134 mysql-slave134\n 192.168.17.135 mysql-slave135\n vim \/etc\/hosts #\u914d\u7f6eDNS\u89e3\u6790\n vim \/etc\/hostname #\u8bbe\u7f6e\u4e3b\u673a\u540d<\/pre>\n\n\n<\/p>\n\n\n
MySQL\u914d\u7f6e <\/p>\n\n\n
vim \/etc\/my.cnf \u52a0\u5165\u4ee5\u4e0b\u914d\u7f6e\n #MGR \u8bbe\u7f6e\n server_id=133\n gtid_mode=ON\n enforce_gtid_consistency=ON\n master_info_repository=TABLE\n relay_log_info_repository=TABLE\n binlog_checksum=NONE\n log_slave_updates=ON\n log_bin=binlog\n binlog_format=ROW\n transaction_write_set_extraction=XXHASH64 ##\u6307\u793aserver\u5fc5\u987b\u4e3a\u6bcf\u4e2a\u4e8b\u52a1\u6536\u96c6\u5199\u96c6\u5408\uff0c\u5e76\u4f7f\u7528XXHASH64\u54c8\u5e0c\u7b97\u6cd5\u5c06\u5176\u7f16\u7801\u4e3a\u6563\u5217\n loose-group_replication_group_name=\"aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa\"\n loose-group_replication_start_on_boot=off ##\u6307\u793a\u63d2\u4ef6\u5728server\u542f\u52a8\u65f6\u4e0d\u81ea\u52a8\u542f\u52a8\u7ec4\u590d\u5236\n loose-group_replication_local_address= \"192.168.17.133:33061\" #\u6bcf\u4e2a\u8282\u70b9\u6b64\u5904\u4e3a\u672c\u673a\u7684IP\u53ca\u7aef\u53e3\n loose-group_replication_group_seeds= \"192.168.17.133:33061,192.168.17.134:33061,192.168.17.135:33061\" #MGR\u7ec4\u7684\u6240\u6709IP\u53ca\u7aef\u53e3\n loose-group_replication_bootstrap_group=off ##\u914d\u7f6e\u662f\u5426\u81ea\u52a8\u5f15\u5bfc\u7ec4\n loose-group_replication_ip_whitelist=\"127.0.0.1,192.168.17.0\/24\" ##\u7528\u6237\u767d\u540d\u5355\n loose-group_replication_enforce_update_everywhere_checks=ON ##\u591a\u4e3b\u6a21\u5f0f\u4e0b\u4e3a\u591a\u4e3b\u66f4\u65b0\u542f\u7528\u6216\u7981\u7528\u4e25\u683c\u4e00\u81f4\u6027\u68c0\u67e5\n loose-group_replication_single_primary_mode=OFF ##\u8bbe\u7f6e\u7ec4\u81ea\u52a8\u9009\u62e9\u4e00\u4e2aserver\u6765\u5904\u7406\u8bfb\/\u5199\u5de5\u4f5c\n group_replication_member_weight=100 #\u6743\u91cd\uff0c\u5982\u679c\u591a\u4e2a\u670d\u52a1\u5668\u6743\u91cd\u76f8\u540c\u4f1a\u6839\u636eserver_uuid\u6309\u5b57\u5178\u987a\u5e8f\u548c\u9009\u62e9\u7b2c\u4e00\u4e2a\u3002\n loose-group_replication_enforce_update_everywhere_checks=OFF ##\u5355\u4e3b\u6a21\u5f0f\u4e0b\u4e00\u81f4\u6027\u68c0\u67e5\u53ef\u4ee5\u5173\u95ed\n loose-group_replication_single_primary_mode=ON ##\u5355\u4e3b\u6a21\u5f0f<\/pre>\n\n\n\u767b\u5f55mysql \u4e3b\u670d\u52a1\u5668\u8bbe\u7f6e<\/p>\n\n\n
show databases; \n SET SQL_LOG_BIN=0; #\u7981\u7528\u4e8c\u8fdb\u5236\u65e5\u81f3\n set global validate_password.policy=0; \n CREATE USER replication@'%' IDENTIFIED BY 'wangfeng'; #\u521b\u5efa\u7528\u6237\n GRANT REPLICATION SLAVE ON .<\/em> TO replication@'%'; #\u52a0\u7528\u6237\u6743\u9650\n FLUSH PRIVILEGES; #\u5237\u65b0\u6570\u636e\n SET SQL_LOG_BIN=1; #\u542f\u7528\u4e8c\u8fdb\u5236\u65e5\u81f3\n CHANGE MASTER TO MASTER_USER='replication', MASTER_PASSWORD='wangfeng' FOR CHANNEL 'group_replication_recovery'; #\u5f53\u914d\u7f6e\u4e86\u7528\u6237\uff0c\u4f7f\u7528CHANGE MASTER TO\u8bed\u53e5\u5c06\u670d\u52a1\u5668\u4e3a\u4e0b\u4e00\u6b21\u9700\u8981\u4ece\u5176\u4ed6\u6210\u5458\u6062\u590d\u72b6\u6001\u65f6\u4f7fSET SQL_LOG_BIN=0;\u7528group_replication_recovery\u590d\u5236\u901a\u9053\u7684\u7ed9\u5b9a\u51ed\u8bc1\uff0c\u53d1\u51fa\u4ee5\u4e0b\u547d\u4ee4\uff0c\u7528\u521b\u5efa\u7528\u6237\u65f6\u76f4\u63a5\u4f7f\u7528\u7684\u503c\u66ff\u6362replication@\u548c\u5bc6\u7801\n INSTALL PLUGIN group_replication SONAME 'group_replication.so'; #\u5b89\u88c5\u7ec4\u63d2\u4ef6\n SHOW PLUGINS; \n SET GLOBAL group_replication_bootstrap_group=ON; #\u5355\u4e3b\u6a21\u5f0f\u4e0b\uff0cmaster\u5f00\u542f\u58f0\u660e\u521d\u59cb\u5316group (\u53ea\u9700\u8981\u7b2c\u4e00\u53f0master\uff09\n START GROUP_REPLICATION; #\u6253\u5f00\u7ec4\u590d\u5236\n SET GLOBAL group_replication_bootstrap_group=OFF; \n SELECT * FROM performance_schema.replication_group_members; #\u67e5\u770b\u7ec4\u6210\u5458\u72b6\u6001<\/pre>\n\n\n\u4ece\u670d\u52a1\u5668\u8bbe\u7f6e<\/p>\n\n\n
show databases;
SET SQL_LOG_BIN=0; #\u7981\u7528\u4e8c\u8fdb\u5236\u65e5\u81f3
set global validate_password.policy=0;
SHOW VARIABLES LIKE 'validate_password%';
CREATE USER replication@'%' IDENTIFIED BY 'wangfeng'; #\u521b\u5efa\u7528\u6237
GRANT REPLICATION SLAVE ON .<\/em> TO replication@'%'; #\u52a0\u7528\u6237\u6743\u9650
FLUSH PRIVILEGES; #\u5237\u65b0\u6570\u636e
SET SQL_LOG_BIN=1; #\u542f\u7528\u4e8c\u8fdb\u5236\u65e5\u81f3
CHANGE MASTER TO MASTER_USER='replication', MASTER_PASSWORD='wangfeng' FOR CHANNEL 'group_replication_recovery'; #\u5f53\u914d\u7f6e\u4e86\u7528\u6237\uff0c\u4f7f\u7528CHANGE MASTER TO\u8bed\u53e5\u5c06\u670d\u52a1\u5668\u4e3a\u4e0b\u4e00\u6b21\u9700\u8981\u4ece\u5176\u4ed6\u6210\u5458\u6062\u590d\u72b6\u6001\u65f6\u4f7fSET SQL_LOG_BIN=0;\u7528group_replication_recovery\u590d\u5236\u901a\u9053\u7684\u7ed9\u5b9a\u51ed\u8bc1\uff0c\u53d1\u51fa\u4ee5\u4e0b\u547d\u4ee4\uff0c\u7528\u521b\u5efa\u7528\u6237\u65f6\u76f4\u63a5\u4f7f\u7528\u7684\u503c\u66ff\u6362rpl_user\u548c\u5bc6\u7801
INSTALL PLUGIN group_replication SONAME 'group_replication.so'; #\u5b89\u88c5\u7ec4\u63d2\u4ef6
SHOW PLUGINS;
START GROUP_REPLICATION; #\u6253\u5f00\u7ec4\u590d\u5236
SELECT * FROM performance_schema.replication_group_members;<\/pre>\n","protected":false},"excerpt":{"rendered":"\u53c2\u8003\u8d44\u6599\uff1a https:\/\/github.com\/sysown\/proxysql\/wiki #GitHub\u5b98\u65b9<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[17,18,15],"tags":[],"yoast_head":"\n
MySQL8+MGR\u7ec4\u590d\u5236\u914d\u7f6e\u793a\u4f8b - Linux\u81ea\u52a8\u5316\u8fd0\u7ef4<\/title>\n\n\n\n\n\n\n\n\n\n\n\n\n