ProxySQL+MGR实现读写分离和主节点故障无感知切换
生活随笔
收集整理的這篇文章主要介紹了
ProxySQL+MGR实现读写分离和主节点故障无感知切换
小編覺得挺不錯的,現在分享給大家,幫大家做個參考.
ProxySQL+MGR實現讀寫分離和主節點故障無感知切換
一、環境準備
-
192.168.153.149 mgr-node1
-
192.168.153.150 mgr-node2
-
192.168.153.151 mgr-node3
-
192.168.153.152 proxysql-node
實驗前關閉防火墻和SELinux
二、MGR(組復制)部署過程
1、四臺機器互相做本地解析
[root@proxysql-node ~]# cat /etc/hosts 127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4 ::1 localhost localhost.localdomain localhost6 localhost6.localdomain6 192.168.153.149 mgr-node1 192.168.153.150 mgr-node2 192.168.153.151 mgr-node3 192.168.153.152 proxysql-node2、四臺機器安裝擴展源
[root@proxysql-node yum.repos.d]# yum -y install epel-release.noarch Loaded plugins: fastestmirror Loading mirror speeds from cached hostfile* base: mirrors.cn99.com* extras: mirrors.huaweicloud.com* updates: mirrors.cn99.com Resolving Dependencies --> Running transaction check ---> Package epel-release.noarch 0:7-11 will be installed --> Finished Dependency Resolution3、三臺數據庫節點下載MySQL數據庫
1、MySQL yum包下載(三臺機器)
[root@mgr-node1 ~]# wget http://repo.mysql.com/mysql57-community-release-el7-10.noarch.rpm --2020-07-18 14:19:46-- http://repo.mysql.com/mysql57-community-release-el7-10.noarch.rpm Resolving repo.mysql.com (repo.mysql.com)... 23.36.53.20 Connecting to repo.mysql.com (repo.mysql.com)|23.36.53.20|:80... connected. HTTP request sent, awaiting response... 200 OK Length: 25548 (25K) [application/x-redhat-package-manager] Saving to: ‘mysql57-community-release-el7-10.noarch.rpm’100%[====================================================================================>] 25,548 132KB/s in 0.2s2020-07-18 14:19:50 (132 KB/s) - ‘mysql57-community-release-el7-10.noarch.rpm’ saved [25548/25548]2、MySQL 軟件源安裝（三臺機器）
[root@mgr-node1 ~]# yum -y install mysql57-community-release-el7-10.noarch.rpm Loaded plugins: fastestmirror Examining mysql57-community-release-el7-10.noarch.rpm: mysql57-community-release-el7-10.noarch Marking mysql57-community-release-el7-10.noarch.rpm to be installed Resolving Dependencies --> Running transaction check ---> Package mysql57-community-release.noarch 0:el7-10 will be installed --> Finished Dependency ResolutionDependencies Resolved==============================================================================================================================Package Arch Version Repository Size ============================================================================================================================== Installing:mysql57-community-release noarch el7-10 /mysql57-community-release-el7-10.noarch 30 kTransaction Summary ============================================================================================================================== Install 1 PackageTotal size: 30 k Installed size: 30 k Downloading packages: Running transaction check Running transaction test Transaction test succeeded Running transactionInstalling : mysql57-community-release-el7-10.noarch 1/1Verifying : mysql57-community-release-el7-10.noarch 1/1Installed:mysql57-community-release.noarch 0:el7-10Complete!3、MySQL 服務安裝(三臺機器)
[root@mgr-node1 ~]# yum install -y mysql-community-server mysql Loaded plugins: fastestmirror Loading mirror speeds from cached hostfile epel/x86_64/metalink | 6.0 kB 00:00:00* base: mirrors.ustc.edu.cn* epel: mirrors.tuna.tsinghua.edu.cn* extras: mirrors.163.com* updates: mirrors.163.com4、啟動mysqld 服務（三臺機器）
[root@mgr-node1 ~]# systemctl start mysqld [root@mgr-node1 ~]# systemctl status mysqld.service ● mysqld.service - MySQL ServerLoaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)Active: active (running) since Sat 2020-07-18 14:39:30 CST; 2min 5s agoDocs: man:mysqld(8)http://dev.mysql.com/doc/refman/en/using-systemd.htmlProcess: 8617 ExecStart=/usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid $MYSQLD_OPTS (code=exited, status=0/SUCCESS)Process: 8567 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)Main PID: 8620 (mysqld)CGroup: /system.slice/mysqld.service└─8620 /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pidJul 18 14:39:26 mgr-node1 systemd[1]: Starting MySQL Server... Jul 18 14:39:30 mgr-node1 systemd[1]: Started MySQL Server.5、設置登陸MySQL數據庫的密碼（三臺機器）
? 由于MySQL從5.7開始不允許首次安裝后使用空密碼進行登錄！為了加強安全性，系統會隨機生成一個密碼以供管理員首次登錄使用，這個密碼記錄在/var/log/mysqld.log文件中，使用下面的命令可以查看此密碼：
[root@mgr-node1 ~]# grep 'temporary password' /var/log/mysqld.log 2020-07-18T06:39:27.392196Z 1 [Note] A temporary password is generated for root@localhost: gKizw,a8!gAU登陸數據庫
[root@mgr-node1 ~]# mysql -uroot -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 Server version: 5.7.31Copyright (c) 2000, 2020, 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.mysql>設置登錄密碼
溫馨提示
mysql5.7通過上面默認安裝后，執行語句可能會報錯：
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements
這個報錯與Mysql 密碼安全策略validate_password_policy的值有關，validate_password_policy可以取0、1、2三個值：
解決辦法：
set global validate_password_policy=0;
set global validate_password_length=1;
4、mgr-node1操作
1、修改my.cnf配置文件
[root@mgr-node1 etc]# cp /etc/my.cnf /etc/my.cnf.bak [root@mgr-node1 etc]# > my.cnf [root@mgr-node1 etc]# cat my.cnf [mysqld] datadir = /var/lib/mysql socket = /var/lib/mysql/mysql.socksymbolic-links = 0log-error = /var/log/mysqld.log pid-file = /var/run/mysqld/mysqld.pid#GTID: server_id = 1 gtid_mode = on enforce_gtid_consistency = onmaster_info_repository=TABLE relay_log_info_repository=TABLE binlog_checksum=NONE#binlog log_bin = mysql-bin log-slave-updates = 1 binlog_format = row sync-master-info = 1 sync_binlog = 1#relay log skip_slave_start = 1transaction_write_set_extraction=XXHASH64 #以便在server收集寫集合的同時將其記錄到二進制日志。寫集合基于每行的主鍵，并且是行更改后的唯一標識此標識將用于檢測沖突。 loose-group_replication_group_name="5db40c3c-180c-11e9-afbf-005056ac6820" #組的名字可以隨便起,但不能用主機的GTID! 所有節點的這個組名必須保持一致！ loose-group_replication_start_on_boot=off #為了避免每次啟動自動引導具有相同名稱的第二個組,所以設置為OFF。 loose-group_replication_local_address= "192.168.153.149:24901" loose-group_replication_group_seeds= "192.168.153.149:24901,192.168.153.150:24901,192.168.153.151:24901" loose-group_replication_bootstrap_group=off loose-group_replication_single_primary_mode=off #關閉單主模式的參數（本例測試時多主模式，所以關閉該項） loose-group_replication_enforce_update_everywhere_checks=on #開啟多主模式的參數 loose-group_replication_ip_whitelist="192.168.153.0/24,127.0.0.1/8" # 允許加入組復制的客戶機來源的ip白名單2、重啟數據庫
[root@mgr-node1 etc]# systemctl restart mysqld [root@mgr-node1 etc]# systemctl status mysqld ● mysqld.service - MySQL ServerLoaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)Active: active (running) since Sat 2020-07-18 15:19:19 CST; 4s agoDocs: man:mysqld(8)http://dev.mysql.com/doc/refman/en/using-systemd.htmlProcess: 8733 ExecStart=/usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid $MYSQLD_OPTS (code=exited, status=0/SUCCESS)Process: 8715 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)Main PID: 8736 (mysqld)CGroup: /system.slice/mysqld.service└─8736 /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pidJul 18 15:19:18 mgr-node1 systemd[1]: Stopped MySQL Server. Jul 18 15:19:19 mgr-node1 systemd[1]: Starting MySQL Server... Jul 18 15:19:19 mgr-node1 systemd[1]: Started MySQL Server.3、登錄數據庫操作
[root@mgr-node1 etc]# mysql -uroot -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 Server version: 5.7.31-log MySQL Community Server (GPL)Copyright (c) 2000, 2020, 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.mysql> SET SQL_LOG_BIN=0; #即不記錄二進制日志 Query OK, 0 rows affected (0.00 sec)mysql> set global validate_password_policy=0; Query OK, 0 rows affected (0.00 sec)mysql> set global validate_password_length=1; Query OK, 0 rows affected (0.00 sec)mysql> GRANT REPLICATION SLAVE ON *.* TO rpl_slave@'%' IDENTIFIED BY '123.com'; Query OK, 0 rows affected, 1 warning (0.00 sec)mysql> FLUSH PRIVILEGES; Query OK, 0 rows affected (0.00 sec)mysql> reset master; Query OK, 0 rows affected (0.00 sec)mysql> SET SQL_LOG_BIN=1; Query OK, 0 rows affected (0.00 sec)mysql> CHANGE MASTER TO MASTER_USER='rpl_slave', MASTER_PASSWORD='123.com' FOR CHANNEL 'group_replication_recovery'; Query OK, 0 rows affected, 2 warnings (0.35 sec)mysql> INSTALL PLUGIN group_replication SONAME 'group_replication.so'; #安裝復制組插件 Query OK, 0 rows affected (0.03 sec)mysql> SHOW PLUGINS; +----------------------------+----------+--------------------+----------------------+---------+ | Name | Status | Type | Library | License | +----------------------------+----------+--------------------+----------------------+---------+ | binlog | ACTIVE | STORAGE ENGINE | NULL | GPL | | mysql_native_password | ACTIVE | AUTHENTICATION | NULL | GPL | | sha256_password | ACTIVE | AUTHENTICATION | NULL | GPL | | CSV | ACTIVE | STORAGE ENGINE | NULL | GPL | | MEMORY | ACTIVE | STORAGE ENGINE | NULL | GPL | | InnoDB | ACTIVE | STORAGE ENGINE | NULL | GPL | | INNODB_TRX | ACTIVE | INFORMATION SCHEMA | NULL | GPL | . . . . | ngram | ACTIVE | FTPARSER | NULL | GPL | | validate_password | ACTIVE | VALIDATE PASSWORD | validate_password.so | GPL | | group_replication | ACTIVE | GROUP REPLICATION | group_replication.so | GPL | +----------------------------+----------+--------------------+----------------------+---------+ 46 rows in set (0.00 sec)mysql> SET GLOBAL group_replication_bootstrap_group=ON; Query OK, 0 rows affected (0.00 sec)mysql> START GROUP_REPLICATION; Query OK, 0 rows affected (2.65 sec)mysql> SET GLOBAL group_replication_bootstrap_group=OFF; Query OK, 0 rows affected (0.00 sec)mysql> SELECT * FROM performance_schema.replication_group_members; +---------------------------+--------------------------------------+-------------+-------------+--------------+ | CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | +---------------------------+--------------------------------------+-------------+-------------+--------------+ | group_replication_applier | 6ceb6753-c8c1-11ea-bb69-000c2939d519 | mgr-node1 | 3306 | ONLINE | +---------------------------+--------------------------------------+-------------+-------------+--------------+ 1 row in set (0.00 sec)比如要保證上面的group_replication_applier的狀態為"ONLINE"才對！創建一個測試庫 mysql> CREATE DATABASE kevin CHARACTER SET utf8 COLLATE utf8_general_ci; Query OK, 1 row affected (0.00 sec)mysql> use kevin; Database changedmysql> create table if not exists haha (id int(10) PRIMARY KEY AUTO_INCREMENT,name varchar(50) NOT NULL); Query OK, 0 rows affected (0.10 sec)mysql> insert into kevin.haha values(1,"wangshibo"),(2,"guohuihui"),(3,"yangyang"),(4,"shikui"); Query OK, 4 rows affected (0.16 sec) Records: 4 Duplicates: 0 Warnings: 0mysql> select * from kevin.haha; +----+-----------+ | id | name | +----+-----------+ | 1 | wangshibo | | 2 | guohuihui | | 3 | yangyang | | 4 | shikui | +----+-----------+ 4 rows in set (0.00 sec)5、mgr-node2操作
1、修改my.cnf配置文件
[root@mgr-node2 ~]# cd /etc/ [root@mgr-node2 etc]# cp my.cnf my.cnf.bak [root@mgr-node2 etc]# > my.cnf [root@mgr-node2 etc]# cat my.cnf [mysqld] datadir = /var/lib/mysql socket = /var/lib/mysql/mysql.socksymbolic-links = 0log-error = /var/log/mysqld.log pid-file = /var/run/mysqld/mysqld.pid#GTID: server_id = 2 gtid_mode = on enforce_gtid_consistency = onmaster_info_repository=TABLE relay_log_info_repository=TABLE binlog_checksum=NONE#binlog log_bin = mysql-bin log-slave-updates = 1 binlog_format = row sync-master-info = 1 sync_binlog = 1#relay log skip_slave_start = 1transaction_write_set_extraction=XXHASH64 loose-group_replication_group_name="5db40c3c-180c-11e9-afbf-005056ac6820" loose-group_replication_start_on_boot=off loose-group_replication_local_address= "192.168.153.150:24901" loose-group_replication_group_seeds= "192.168.153.149::24901,192.168.153.150::24901,192.168.153.151::24901" loose-group_replication_bootstrap_group=off loose-group_replication_single_primary_mode=off loose-group_replication_enforce_update_everywhere_checks=on loose-group_replication_ip_whitelist="192.168.153.0/24,127.0.0.1/8"2、重啟數據庫
[root@mgr-node2 etc]# systemctl restart mysqld [root@mgr-node2 etc]# systemctl status mysqld ● mysqld.service - MySQL ServerLoaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)Active: active (running) since Sat 2020-07-18 15:53:17 CST; 7s agoDocs: man:mysqld(8)http://dev.mysql.com/doc/refman/en/using-systemd.htmlProcess: 23456 ExecStart=/usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid $MYSQLD_OPTS (code=exited, status=0/SUCCESS)Process: 23438 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)Main PID: 23459 (mysqld)CGroup: /system.slice/mysqld.service└─23459 /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pidJul 18 15:53:16 mgr-node2 systemd[1]: Stopped MySQL Server. Jul 18 15:53:16 mgr-node2 systemd[1]: Starting MySQL Server... Jul 18 15:53:17 mgr-node2 systemd[1]: Started MySQL Server.3、登錄數據庫進行操作
[root@mgr-node2 etc]# mysql -uroot -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 Server version: 5.7.31-log MySQL Community Server (GPL)Copyright (c) 2000, 2020, 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.mysql> SET SQL_LOG_BIN=0; Query OK, 0 rows affected (0.00 sec)mysql> set global validate_password_policy=0; Query OK, 0 rows affected (0.00 sec)mysql> set global validate_password_length=1; Query OK, 0 rows affected (0.00 sec)mysql> GRANT REPLICATION SLAVE ON *.* TO rpl_slave@'%' IDENTIFIED BY '123.com'; Query OK, 0 rows affected, 1 warning (0.00 sec)mysql> FLUSH PRIVILEGES; Query OK, 0 rows affected (0.00 sec)mysql> reset master; Query OK, 0 rows affected (0.00 sec)mysql> SET SQL_LOG_BIN=1; Query OK, 0 rows affected (0.00 sec)mysql> CHANGE MASTER TO MASTER_USER='rpl_slave', MASTER_PASSWORD='123.com' FOR CHANNEL 'group_replication_recovery'; Query OK, 0 rows affected, 2 warnings (0.02 sec)mysql> INSTALL PLUGIN group_replication SONAME 'group_replication.so'; Query OK, 0 rows affected (0.02 sec)mysql> SHOW PLUGINS; +----------------------------+----------+--------------------+----------------------+---------+ | Name | Status | Type | Library | License | +----------------------------+----------+--------------------+----------------------+---------+ | binlog | ACTIVE | STORAGE ENGINE | NULL | GPL | | mysql_native_password | ACTIVE | AUTHENTICATION | NULL | GPL | | sha256_password | ACTIVE | AUTHENTICATION | NULL | GPL | | CSV | ACTIVE | STORAGE ENGINE | NULL | GPL | . . . | BLACKHOLE | ACTIVE | STORAGE ENGINE | NULL | GPL | | FEDERATED | DISABLED | STORAGE ENGINE | NULL | GPL | | partition | ACTIVE | STORAGE ENGINE | NULL | GPL | | ngram | ACTIVE | FTPARSER | NULL | GPL | | validate_password | ACTIVE | VALIDATE PASSWORD | validate_password.so | GPL | | group_replication | ACTIVE | GROUP REPLICATION | group_replication.so | GPL | +----------------------------+----------+--------------------+----------------------+---------+ 46 rows in set (0.00 sec)這里只需要執行這一步即可！mysql> START GROUP_REPLICATION; Query OK, 0 rows affected (5.72 sec)查看組內情況，發現MGR-node2已經成功加入這個組內了。 mysql> SELECT * FROM performance_schema.replication_group_members; +---------------------------+--------------------------------------+-------------+-------------+--------------+ | CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | +---------------------------+--------------------------------------+-------------+-------------+--------------+ | group_replication_applier | 6ceb6753-c8c1-11ea-bb69-000c2939d519 | mgr-node1 | 3306 | ONLINE | | group_replication_applier | 6de78dac-c8c5-11ea-a1ae-000c299642d5 | mgr-node2 | 3306 | ONLINE | +---------------------------+--------------------------------------+-------------+-------------+--------------+ 2 rows in set (0.00 sec)查看下，發現已經將MGR-node1節點添加的數據同步過來了 mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | kevin | | mysql | | performance_schema | | sys | +--------------------+ 5 rows in set (0.00 sec)mysql> select * from kevin.haha; +----+-----------+ | id | name | +----+-----------+ | 1 | wangshibo | | 2 | guohuihui | | 3 | yangyang | | 4 | shikui | +----+-----------+ 4 rows in set (0.00 sec)6、mgr-node3操作
1、修改my.cnf 配置文件
[root@mgr-node3 ~]# cd /etc/ [root@mgr-node3 etc]# cp my.cnf my.cnf.bak [root@mgr-node3 etc]# > my.cnf [root@mgr-node3 etc]# cat my.cnf [mysqld] datadir = /var/lib/mysql socket = /var/lib/mysql/mysql.socksymbolic-links = 0log-error = /var/log/mysqld.log pid-file = /var/run/mysqld/mysqld.pid#GTID: server_id = 3 gtid_mode = on enforce_gtid_consistency = onmaster_info_repository=TABLE relay_log_info_repository=TABLE binlog_checksum=NONE#binlog log_bin = mysql-bin log-slave-updates = 1 binlog_format = row sync-master-info = 1 sync_binlog = 1#relay log skip_slave_start = 1transaction_write_set_extraction=XXHASH64 loose-group_replication_group_name="5db40c3c-180c-11e9-afbf-005056ac6820" loose-group_replication_start_on_boot=off loose-group_replication_local_address= "192.168.153.151:24901" loose-group_replication_group_seeds= "192.168.153.149:24901,192.168.153.150:24901,192.168.153.151:24901" loose-group_replication_bootstrap_group=off loose-group_replication_single_primary_mode=off loose-group_replication_enforce_update_everywhere_checks=on loose-group_replication_ip_whitelist="192.168.153.0/24,127.0.0.1/8"2、重啟數據庫
[root@mgr-node3 etc]# systemctl restart mysqld [root@mgr-node3 etc]# systemctl status mysqld ● mysqld.service - MySQL ServerLoaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)Active: active (running) since Sat 2020-07-18 16:35:02 CST; 7s agoDocs: man:mysqld(8)http://dev.mysql.com/doc/refman/en/using-systemd.htmlProcess: 23420 ExecStart=/usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid $MYSQLD_OPTS (code=exited, status=0/SUCCESS)Process: 23402 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)Main PID: 23423 (mysqld)CGroup: /system.slice/mysqld.service└─23423 /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pidJul 18 16:35:01 mgr-node3 systemd[1]: Starting MySQL Server... Jul 18 16:35:02 mgr-node3 systemd[1]: Started MySQL Server.3、查看mysql日志
[root@mgr-node3 etc]# grep password /var/log/mysqld.log 2020-07-18T07:10:43.712239Z 1 [Note] A temporary password is generated for root@localhost: 8C>hj(M9oqY9 2020-07-18T07:11:41.418934Z 2 [Warning] Plugin validate_password reported: 'Effective value of validate_password_length is changed. New value is 4' 2020-07-18T08:34:59.882470Z 0 [Note] Shutting down plugin 'validate_password' 2020-07-18T08:35:01.520836Z 0 [Note] Shutting down plugin 'sha256_password' 2020-07-18T08:35:01.520838Z 0 [Note] Shutting down plugin 'mysql_native_password'4、登錄數據庫操作
[root@mgr-node3 etc]# mysql -uroot -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 Server version: 5.7.31-log MySQL Community Server (GPL)Copyright (c) 2000, 2020, 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.mysql> set global validate_password_policy=0; Query OK, 0 rows affected (0.00 sec)mysql> set global validate_password_length=1; Query OK, 0 rows affected (0.00 sec)mysql> GRANT REPLICATION SLAVE ON *.* TO rpl_slave@'%' IDENTIFIED BY '123.com'; Query OK, 0 rows affected, 1 warning (0.00 sec)mysql> FLUSH PRIVILEGES; Query OK, 0 rows affected (0.00 sec)mysql> reset master; Query OK, 0 rows affected (0.01 sec)mysql> SET SQL_LOG_BIN=1; Query OK, 0 rows affected (0.00 sec)mysql> CHANGE MASTER TO MASTER_USER='rpl_slave', MASTER_PASSWORD='123.com' FOR CHANNEL 'group_replication_recovery'; Query OK, 0 rows affected, 2 warnings (0.01 sec)mysql> INSTALL PLUGIN group_replication SONAME 'group_replication.so'; Query OK, 0 rows affected (0.03 sec)mysql> SHOW PLUGINS; +----------------------------+----------+--------------------+----------------------+---------+ | Name | Status | Type | Library | License | +----------------------------+----------+--------------------+----------------------+---------+ | binlog | ACTIVE | STORAGE ENGINE | NULL | GPL | | mysql_native_password | ACTIVE | AUTHENTICATION | NULL | GPL | | sha256_password | ACTIVE | AUTHENTICATION | NULL | GPL | | CSV | ACTIVE | STORAGE ENGINE | NULL | GPL | . . . | validate_password | ACTIVE | VALIDATE PASSWORD | validate_password.so | GPL | | group_replication | ACTIVE | GROUP REPLICATION | group_replication.so | GPL | +----------------------------+----------+--------------------+----------------------+---------+ 46 rows in set (0.00 sec)這里只需要執行這一步即可！ mysql> START GROUP_REPLICATION; Query OK, 0 rows affected (3.75 sec)查看組內情況，發現MGR-node3已經成功加入這個組內了。 mysql> SELECT * FROM performance_schema.replication_group_members; +---------------------------+--------------------------------------+-------------+-------------+--------------+ | CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | +---------------------------+--------------------------------------+-------------+-------------+--------------+ | group_replication_applier | 6ceb6753-c8c1-11ea-bb69-000c2939d519 | mgr-node1 | 3306 | ONLINE | | group_replication_applier | 6de78dac-c8c5-11ea-a1ae-000c299642d5 | mgr-node2 | 3306 | ONLINE | | group_replication_applier | cb32290b-c8c5-11ea-a447-000c296697cf | mgr-node3 | 3306 | ONLINE | +---------------------------+--------------------------------------+-------------+-------------+--------------+ 3 rows in set (0.00 sec)查看下，發現已經將在其他節點上添加的數據同步過來了 mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | kevin | | mysql | | performance_schema | | sys | +--------------------+ 5 rows in set (0.00 sec)mysql> select * from kevin.haha; +----+-----------+ | id | name | +----+-----------+ | 1 | wangshibo | | 2 | guohuihui | | 3 | yangyang | | 4 | shikui | +----+-----------+ 4 rows in set (0.00 sec)7、組復制數據同步測試
1、在任意一個節點上執行
mysql> SELECT * FROM performance_schema.replication_group_members; +---------------------------+--------------------------------------+-------------+-------------+--------------+ | CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | +---------------------------+--------------------------------------+-------------+-------------+--------------+ | group_replication_applier | 6ceb6753-c8c1-11ea-bb69-000c2939d519 | mgr-node1 | 3306 | ONLINE | | group_replication_applier | 6de78dac-c8c5-11ea-a1ae-000c299642d5 | mgr-node2 | 3306 | ONLINE | | group_replication_applier | cb32290b-c8c5-11ea-a447-000c296697cf | mgr-node3 | 3306 | ONLINE | +---------------------------+--------------------------------------+-------------+-------------+--------------+ 3 rows in set (0.00 sec)mysql> insert into kevin.haha values(11,"beijing"),(12,"shanghai"),(13,"anhui"); Query OK, 3 rows affected (0.00 sec) Records: 3 Duplicates: 0 Warnings: 0mysql> select * from kevin.haha; +----+-----------+ | id | name | +----+-----------+ | 1 | wangshibo | | 2 | guohuihui | | 3 | yangyang | | 4 | shikui | | 11 | beijing | | 12 | shanghai | | 13 | anhui | +----+-----------+ 7 rows in set (0.00 sec)2、在另一臺機器上查看數據庫
mysql> select * from kevin.haha; +----+-----------+ | id | name | +----+-----------+ | 1 | wangshibo | | 2 | guohuihui | | 3 | yangyang | | 4 | shikui | | 11 | beijing | | 12 | shanghai | | 13 | anhui | +----+-----------+ 7 rows in set (0.00 sec)8、組復制故障測試
1、關閉一臺機器的mysql數據庫
[root@mgr-node3 etc]# systemctl stop mysqld [root@mgr-node3 etc]# systemctl status mysqld ● mysqld.service - MySQL ServerLoaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)Active: inactive (dead) since Sat 2020-07-18 16:53:58 CST; 2s agoDocs: man:mysqld(8)http://dev.mysql.com/doc/refman/en/using-systemd.htmlProcess: 23420 ExecStart=/usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid $MYSQLD_OPTS (code=exited, status=0/SUCCESS)Process: 23402 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)Main PID: 23423 (code=exited, status=0/SUCCESS)Jul 18 16:35:01 mgr-node3 systemd[1]: Starting MySQL Server... Jul 18 16:35:02 mgr-node3 systemd[1]: Started MySQL Server. Jul 18 16:53:46 mgr-node3 systemd[1]: Stopping MySQL Server... Jul 18 16:53:58 mgr-node3 systemd[1]: Stopped MySQL Server.2、在另一臺機器數據庫里面查看組的狀態
mysql> SELECT * FROM performance_schema.replication_group_members; +---------------------------+--------------------------------------+-------------+-------------+--------------+ | CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | +---------------------------+--------------------------------------+-------------+-------------+--------------+ | group_replication_applier | 6ceb6753-c8c1-11ea-bb69-000c2939d519 | mgr-node1 | 3306 | ONLINE | | group_replication_applier | 6de78dac-c8c5-11ea-a1ae-000c299642d5 | mgr-node2 | 3306 | ONLINE | +---------------------------+--------------------------------------+-------------+-------------+--------------+ 2 rows in set (0.00 sec)已經自動把down掉的節點剔除組
如果把down掉的節點重啟后 不會自動添加到組里面，需要手動添加.其他節點更新的數據也會及時同步過來！
mysql> START GROUP_REPLICATION; Query OK, 0 rows affected (3.23 sec)mysql> SELECT * FROM performance_schema.replication_group_members; +---------------------------+--------------------------------------+-------------+-------------+--------------+ | CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | +---------------------------+--------------------------------------+-------------+-------------+--------------+ | group_replication_applier | 6ceb6753-c8c1-11ea-bb69-000c2939d519 | mgr-node1 | 3306 | ONLINE | | group_replication_applier | 6de78dac-c8c5-11ea-a1ae-000c299642d5 | mgr-node2 | 3306 | ONLINE | | group_replication_applier | cb32290b-c8c5-11ea-a447-000c296697cf | mgr-node3 | 3306 | ONLINE | +---------------------------+--------------------------------------+-------------+-------------+--------------+ 3 rows in set (0.00 sec)9、三個節點都出現故障解決方法
要是三個節點都發生故障的話，在節點的故障都恢復后，需要手動重新做組復制，操作流程如下：第一個節點 mysql> reset master; mysql> SET SQL_LOG_BIN=1; mysql> CHANGE MASTER TO MASTER_USER='rpl_slave', MASTER_PASSWORD='slave@123' FOR CHANNEL 'group_replication_recovery'; mysql> STOP GROUP_REPLICATION; mysql> SET GLOBAL group_replication_bootstrap_group=ON; mysql> START GROUP_REPLICATION; mysql> SET GLOBAL group_replication_bootstrap_group=OFF; mysql> SELECT * FROM performance_schema.replication_group_members;第二個節點 mysql> reset master; mysql> SET SQL_LOG_BIN=1; mysql> CHANGE MASTER TO MASTER_USER='rpl_slave', MASTER_PASSWORD='slave@123' FOR CHANNEL 'group_replication_recovery'; mysql> START GROUP_REPLICATION; mysql> SELECT * FROM performance_schema.replication_group_members;第三個節點 mysql> reset master; mysql> SET SQL_LOG_BIN=1; mysql> CHANGE MASTER TO MASTER_USER='rpl_slave', MASTER_PASSWORD='slave@123' FOR CHANNEL 'group_replication_recovery'; mysql> START GROUP_REPLICATION; mysql> SELECT * FROM performance_schema.replication_group_members;三、ProxySQL+MGR 配置過程
1、下載mariadb客戶端
[root@proxysql-node ~]# yum install -y mariadb.x86_64 mariadb-libs.x86_64 Loaded plugins: fastestmirror Loading mirror speeds from cached hostfile epel/x86_64/metalink | 6.0 kB 00:00:00* base: mirrors.huaweicloud.com* epel: mirrors.yun-idc.com* extras: mirrors.huaweicloud.com* updates: mirror.bit.edu.cn2、安裝ProxySQL
1、安裝
上傳包 [root@proxysql-node ~]# ls anaconda-ks.cfg proxysql-1.4.8-1-centos7.x86_64.rpm[root@proxysql-node ~]# yum install -y perl-DBI perl-DBD-MySQL Loaded plugins: fastestmirror Loading mirror speeds from cached hostfile* base: mirrors.huaweicloud.com* epel: mirrors.yun-idc.com* extras: mirrors.huaweicloud.com* updates: mirror.bit.edu.cn Resolving Dependencies --> Running transaction check ---> Package perl-DBD-MySQL.x86_64 0:4.023-6.el7 will be installed ---> Package perl-DBI.x86_64 0:1.627-4.el7 will be installed[root@proxysql-node ~]# rpm -ivh proxysql-1.4.8-1-centos7.x86_64.rpm --force Preparing... ################################# [100%] Updating / installing...1:proxysql-1.4.8-1 ################################# [100%]2、proxysql配置文件詳解
[root@proxysql-node ~]# egrep -v "^#|^$" /etc/proxysql.cnf datadir="/var/lib/proxysql" #數據目錄 admin_variables= {admin_credentials="admin:admin" #連接管理端的用戶名與密碼mysql_ifaces="0.0.0.0:6032" #管理端口，用來連接proxysql的管理數據庫! } mysql_variables= {threads=4 #指定轉發端口開啟的線程數量max_connections=2048default_query_delay=0default_query_timeout=36000000have_compress=truepoll_timeout=2000interfaces="0.0.0.0:6033" #指定轉發端口，用于連接后端真實mysql數據庫的，相當于代理作用!default_schema="information_schema"stacksize=1048576server_version="5.5.30" #指定后端mysql的版本connect_timeout_server=3000monitor_username="monitor"monitor_password="monitor"monitor_history=600000monitor_connect_interval=60000monitor_ping_interval=10000monitor_read_only_interval=1500monitor_read_only_timeout=500ping_interval_server_msec=120000ping_timeout_server=500commands_stats=truesessions_sort=trueconnect_retries_on_failure=10 } mysql_servers = ( ) mysql_users: ( ) mysql_query_rules: ( ) scheduler= ( ) mysql_replication_hostgroups= ( )3、啟動服務
[root@proxysql-node ~]# /etc/init.d/proxysql start Starting ProxySQL: DONE! [root@proxysql-node ~]# ss -anput | grep proxy tcp LISTEN 0 128 *:6032 *:* users:(("proxysql",pid=22573,fd=23)) tcp LISTEN 0 128 *:6033 *:* users:(("proxysql",pid=22573,fd=22)) tcp LISTEN 0 128 *:6033 *:* users:(("proxysql",pid=22573,fd=21)) tcp LISTEN 0 128 *:6033 *:* users:(("proxysql",pid=22573,fd=20)) tcp LISTEN 0 128 *:6033 *:* users:(("proxysql",pid=22573,fd=19))4、初始化Proxysql，將之前的proxysql數據都刪除
[root@proxysql-node ~]# mysql -uadmin -padmin -h127.0.0.1 -P6032 Welcome to the MariaDB 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, MariaDB Corporation Ab and others.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.MySQL [(none)]>MySQL [(none)]> show tables; +--------------------------------------------+ | tables | +--------------------------------------------+ | global_variables | | mysql_collations | | 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_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 | +--------------------------------------------+ 20 rows in set (0.00 sec)MySQL [(none)]> delete from scheduler ; Query OK, 0 rows affected (0.00 sec)MySQL [(none)]> delete from mysql_servers; Query OK, 0 rows affected (0.00 sec)MySQL [(none)]> delete from mysql_users; Query OK, 0 rows affected (0.00 sec)MySQL [(none)]> delete from mysql_query_rules; Query OK, 0 rows affected (0.00 sec)MySQL [(none)]> delete from mysql_group_replication_hostgroups ; Query OK, 0 rows affected (0.00 sec)MySQL [(none)]> LOAD MYSQL VARIABLES TO RUNTIME; Query OK, 0 rows affected (0.00 sec)MySQL [(none)]> SAVE MYSQL VARIABLES TO DISK; Query OK, 94 rows affected (0.00 sec)MySQL [(none)]> LOAD MYSQL SERVERS TO RUNTIME; Query OK, 0 rows affected (0.00 sec)MySQL [(none)]> SAVE MYSQL SERVERS TO DISK; Query OK, 0 rows affected (0.10 sec)MySQL [(none)]> LOAD MYSQL USERS TO RUNTIME; Query OK, 0 rows affected (0.00 sec)MySQL [(none)]> SAVE MYSQL USERS TO DISK; Query OK, 0 rows affected (0.00 sec)MySQL [(none)]> LOAD SCHEDULER TO RUNTIME; Query OK, 0 rows affected (0.00 sec)MySQL [(none)]> SAVE SCHEDULER TO DISK; Query OK, 0 rows affected (0.01 sec)MySQL [(none)]> LOAD MYSQL QUERY RULES TO RUNTIME; Query OK, 0 rows affected (0.00 sec)MySQL [(none)]> SAVE MYSQL QUERY RULES TO DISK; Query OK, 0 rows affected (0.00 sec)5、在數據庫端建立proxysql登入需要的賬號（在MGR任意一臺，另外的會同步）
[root@mgr-node1 ~]# mysql -uroot -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 23 Server version: 5.7.31-log MySQL Community Server (GPL)Copyright (c) 2000, 2020, 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.mysql> CREATE USER 'proxysql'@'%' IDENTIFIED BY 'proxysql'; Query OK, 0 rows affected (0.16 sec)mysql> GRANT ALL ON * . * TO 'proxysql'@'%'; Query OK, 0 rows affected (0.00 sec)mysql> create user 'qyuser'@'%' IDENTIFIED BY 'qypass'; Query OK, 0 rows affected (0.01 sec)mysql> GRANT ALL ON * . * TO 'qyuser'@'%'; Query OK, 0 rows affected (0.10 sec)mysql> FLUSH PRIVILEGES; Query OK, 0 rows affected (0.00 sec)6、創建檢查MGR節點狀態的函數和視圖（在三個MGR任意一個節點上操作，會自動同步到其他節點）
[root@mgr-node1 ~]# vim /root/addition_to_sys.sql [root@mgr-node1 ~]# cat /root/addition_to_sys.sql USE sys;DELIMITER $$CREATE FUNCTION IFZERO(a INT, b INT) RETURNS INT DETERMINISTIC RETURN IF(a = 0, b, a)$$CREATE FUNCTION LOCATE2(needle TEXT(10000), haystack TEXT(10000), offset INT) RETURNS INT DETERMINISTIC RETURN IFZERO(LOCATE(needle, haystack, offset), LENGTH(haystack) + 1)$$CREATE FUNCTION GTID_NORMALIZE(g TEXT(10000)) RETURNS TEXT(10000) DETERMINISTIC RETURN GTID_SUBTRACT(g, '')$$CREATE FUNCTION GTID_COUNT(gtid_set TEXT(10000)) RETURNS INT DETERMINISTIC BEGINDECLARE result BIGINT DEFAULT 0;DECLARE colon_pos INT;DECLARE next_dash_pos INT;DECLARE next_colon_pos INT;DECLARE next_comma_pos INT;SET gtid_set = GTID_NORMALIZE(gtid_set);SET colon_pos = LOCATE2(':', gtid_set, 1);WHILE colon_pos != LENGTH(gtid_set) + 1 DOSET next_dash_pos = LOCATE2('-', gtid_set, colon_pos + 1);SET next_colon_pos = LOCATE2(':', gtid_set, colon_pos + 1);SET next_comma_pos = LOCATE2(',', gtid_set, colon_pos + 1);IF next_dash_pos < next_colon_pos AND next_dash_pos < next_comma_pos THENSET result = result +SUBSTR(gtid_set, next_dash_pos + 1,LEAST(next_colon_pos, next_comma_pos) - (next_dash_pos + 1)) -SUBSTR(gtid_set, colon_pos + 1, next_dash_pos - (colon_pos + 1)) + 1;ELSESET result = result + 1;END IF;SET colon_pos = next_colon_pos;END WHILE;RETURN result; END$$CREATE FUNCTION gr_applier_queue_length() RETURNS INT DETERMINISTIC BEGINRETURN (SELECT sys.gtid_count( GTID_SUBTRACT( (SELECT Received_transaction_set FROM performance_schema.replication_connection_status WHERE Channel_name = 'group_replication_applier' ), (SELECT @@global.GTID_EXECUTED) ))); END$$CREATE FUNCTION gr_member_in_primary_partition() RETURNS VARCHAR(3) DETERMINISTIC BEGINRETURN (SELECT IF( MEMBER_STATE='ONLINE' AND ((SELECT COUNT(*) FROM performance_schema.replication_group_members WHERE MEMBER_STATE != 'ONLINE') >= ((SELECT COUNT(*) FROM performance_schema.replication_group_members)/2) = 0), 'YES', 'NO' ) FROM performance_schema.replication_group_members JOIN performance_schema.replication_group_member_stats USING(member_id)); END$$CREATE VIEW gr_member_routing_candidate_status AS SELECT sys.gr_member_in_primary_partition() as viable_candidate, IF( (SELECT (SELECT GROUP_CONCAT(variable_value) FROM performance_schema.global_variables WHERE variable_name IN ('read_only', 'super_read_only')) != 'OFF,OFF'), 'YES', 'NO') as read_only, sys.gr_applier_queue_length() as transactions_behind, Count_Transactions_in_queue as 'transactions_to_cert' from performance_schema.replication_group_member_stats;$$DELIMITER ;7、導入addition_to_sys.sql文件數據
[root@mgr-node1 ~]# mysql -p123.com < /root/addition_to_sys.sql mysql: [Warning] Using a password on the command line interface can be insecure.查看該視圖： [root@mgr-node1 ~]# mysql -uroot -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 25 Server version: 5.7.31-log MySQL Community Server (GPL)Copyright (c) 2000, 2020, 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.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)8、 在proxysql中增加帳號
[root@proxysql-node ~]# mysql -uadmin -padmin -h127.0.0.1 -P6032 Welcome to the MariaDB monitor. Commands end with ; or \g. Your MySQL connection id is 2 Server version: 5.5.30 (ProxySQL Admin Module)Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.MySQL [(none)]> INSERT INTO MySQL_users(username,password,default_hostgroup) VALUES ('proxysql','proxysql',1); Query OK, 1 row affected (0.00 sec)MySQL [(none)]> UPDATE global_variables SET variable_value='proxysql' where variable_name='mysql-monitor_username'; Query OK, 1 row affected (0.00 sec)MySQL [(none)]> UPDATE global_variables SET variable_value='proxysql' where variable_name='mysql-monitor_password'; Query OK, 1 row affected (0.00 sec)MySQL [(none)]> LOAD MYSQL SERVERS TO RUNTIME; Query OK, 0 rows affected (0.00 sec)MySQL [(none)]> SAVE MYSQL SERVERS TO DISK; Query OK, 0 rows affected (0.00 sec)9、登陸測試
如果上面測試登錄時報錯:
[root@proxysql-node ~]# mysql -uproxysql -pproxysql -h 127.0.0.1 -P6033 -e"select @@hostname" ERROR 1045 (28000): ProxySQL Error: Access denied for user 'proxysql'@'127.0.0.1' (using password: YES)但是檢查發現，明明用戶名和密碼已經修改成proxysql:proxysql了 MySQL [(none)]> select * from global_variables; +-----------------------------------------------------+--------------------+ | variable_name | variable_value | +-----------------------------------------------------+--------------------+ . . . | mysql-monitor_username | proxysql | | mysql-monitor_password | proxysql | | mysql-monitor_history | 600000 | | mysql-monitor_connect_interval | 60000 | . . | mysql-commands_stats | true | | mysql-sessions_sort | true | | mysql-connect_retries_on_failure | 10 | +-----------------------------------------------------+--------------------+執行下面操作 將上面對proxysql所有的變更都加載到環境中 MySQL [(none)]> LOAD MYSQL VARIABLES TO RUNTIME; Query OK, 0 rows affected (0.01 sec)MySQL [(none)]> SAVE MYSQL VARIABLES TO DISK; Query OK, 94 rows affected (0.01 sec)MySQL [(none)]> LOAD MYSQL SERVERS TO RUNTIME; Query OK, 0 rows affected (0.01 sec)MySQL [(none)]> SAVE MYSQL SERVERS TO DISK; Query OK, 0 rows affected (0.10 sec)MySQL [(none)]> LOAD MYSQL USERS TO RUNTIME; Query OK, 0 rows affected (0.00 sec)MySQL [(none)]> SAVE MYSQL USERS TO DISK; Query OK, 0 rows affected (0.00 sec)MySQL [(none)]> LOAD SCHEDULER TO RUNTIME; Query OK, 0 rows affected (0.00 sec)MySQL [(none)]> SAVE SCHEDULER TO DISK; Query OK, 0 rows affected (0.00 sec)MySQL [(none)]> LOAD MYSQL QUERY RULES TO RUNTIME; Query OK, 0 rows affected (0.00 sec)MySQL [(none)]> SAVE MYSQL QUERY RULES TO DISK; Query OK, 0 rows affected (0.01 sec)MySQL [(none)]> delete from mysql_servers; Query OK, 0 rows affected (0.00 sec)將后端三個mysql的MGR節點加入到proxysql中MySQL [(none)]> insert into mysql_servers (hostgroup_id, hostname, port) values(1,'192.168.153.149',3306); Query OK, 1 row affected (0.00 sec)MySQL [(none)]> insert into mysql_servers (hostgroup_id, hostname, port) values(1,'192.168.153.150',3306); Query OK, 1 row affected (0.00 sec)MySQL [(none)]> insert into mysql_servers (hostgroup_id, hostname, port) values(1,'192.168.153.151',3306); Query OK, 1 row affected (0.00 sec)MySQL [(none)]> insert into mysql_servers (hostgroup_id, hostname, port) values(2,'192.168.153.149',3306); Query OK, 1 row affected (0.01 sec)MySQL [(none)]> insert into mysql_servers (hostgroup_id, hostname, port) values(2,'192.168.153.150',3306); Query OK, 1 row affected (0.00 sec)MySQL [(none)]> insert into mysql_servers (hostgroup_id, hostname, port) values(2,'192.168.153.151',3306); Query OK, 1 row affected (0.01 sec)查看結果 MySQL [(none)]> select * from mysql_servers ; +--------------+-----------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+ | hostgroup_id | hostname | port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment | +--------------+-----------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+ | 1 | 192.168.153.149 | 3306 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | | | 1 | 192.168.153.150 | 3306 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | | | 1 | 192.168.153.151 | 3306 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | | | 2 | 192.168.153.149 | 3306 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | | | 2 | 192.168.153.150 | 3306 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | | | 2 | 192.168.153.151 | 3306 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | | +--------------+-----------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+ hostgroup_id = 1代表write group，針對我們提出的限制，這個地方只配置了一個節點； hostgroup_id = 2代表read group，包含了MGR的所有節點,目前只是Onlinle的，等配置過scheduler后，status就會有變化 。 對于上面的hostgroup配置，默認所有的寫操作會發送到hostgroup_id為1的online節點，也就是發送到寫節點上。 所有的讀操作，會發送為hostgroup_id為2的online節點。10、驗證proxysql登錄
[root@proxysql-node ~]# mysql -uproxysql -pproxysql -h 127.0.0.1 -P6033 -e"select @@hostname" +------------+ | @@hostname | +------------+ | mgr-node3 | +------------+11、配置scheduler
上傳腳本 [root@proxysql-node ~]# ls anaconda-ks.cfg proxysql-1.4.8-1-centos7.x86_64.rpm proxysql_groupreplication_checker-master.zip將腳本proxysql_groupreplication_checker.sh解壓放到/var/lib/proxysql/下 [root@proxysql-node ~]# cd /var/lib/proxysql/ [root@proxysql-node proxysql]# ls proxysql.db proxysql.log proxysql.pid proxysql_stats.db [root@proxysql-node proxysql]# mv /root/proxysql_groupreplication_checker-master.zip . [root@proxysql-node proxysql]# ls proxysql.db proxysql_groupreplication_checker-master.zip proxysql.log proxysql.pid proxysql_stats.db [root@proxysql-node proxysql]# unzip proxysql_groupreplication_checker-master.zip Archive: proxysql_groupreplication_checker-master.zip 1f34bf1a67da5a26a0c6e16dfb74349871e6470fcreating: proxysql_groupreplication_checker-master/inflating: proxysql_groupreplication_checker-master/README.mdinflating: proxysql_groupreplication_checker-master/README_Chinese.mdinflating: proxysql_groupreplication_checker-master/gr_mw_mode_sw_cheker.shinflating: proxysql_groupreplication_checker-master/gr_sw_mode_checker.shinflating: proxysql_groupreplication_checker-master/proxysql_groupreplication_checker.sh [root@proxysql-node proxysql]# ls proxysql.db proxysql_groupreplication_checker-master.zip proxysql.pid proxysql_groupreplication_checker-master proxysql.log proxysql_stats.db [root@proxysql-node proxysql_groupreplication_checker-master]# mv * /var/lib/proxysql/ [root@proxysql-node proxysql]# chmod a+x /var/lib/proxysql/proxysql_groupreplication_checker.sh [root@proxysql-node proxysql]# ll /var/lib/proxysql/proxysql_groupreplication_checker.sh -rwxr-xr-x 1 root root 6081 Jan 9 2017 /var/lib/proxysql/proxysql_groupreplication_checker.sh然后，在proxysql的scheduler表里面加載如下記錄，然后加載到RUNTIME使其生效，同時還可以持久化到磁盤:[root@proxysql-node proxysql]# mysql -uadmin -padmin -h127.0.0.1 -P6032 Welcome to the MariaDB monitor. Commands end with ; or \g. Your MySQL connection id is 6 Server version: 5.5.30 (ProxySQL Admin Module)Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.MySQL [(none)]> INSERT INTO scheduler(id,interval_ms,filename,arg1,arg2,arg3,arg4, arg5) VALUES (1,'10000','/var/lib/proxysql/ proxysql_groupreplication_checker.sh','1','2','1','0','/var/lib/proxysql/proxysql_groupreplication_checker.log'); Query OK, 1 row affected (0.00 sec)查看結果 MySQL [(none)]> select * from scheduler; +----+--------+-------------+--------------------------------------------------------+------+------+------+------+---------------------------------------------------------+---------+ | id | active | interval_ms | filename | arg1 | arg2 | arg3 | arg4 | arg5 | comment | +----+--------+-------------+--------------------------------------------------------+------+------+------+------+---------------------------------------------------------+---------+ | 1 | 1 | 10000 | /var/lib/proxysql/proxysql_groupreplication_checker.sh | 1 | 2 | 1 | 0 | /var/lib/proxysql/proxysql_groupreplication_checker.log | | +----+--------+-------------+--------------------------------------------------------+------+------+------+------+---------------------------------------------------------+---------+ 1 row in set (0.00 sec)MySQL [(none)]> LOAD SCHEDULER TO RUNTIME; Query OK, 0 rows affected (0.00 sec)MySQL [(none)]> SAVE SCHEDULER TO DISK; Query OK, 0 rows affected (0.01 sec)schedule信息加載后，就會分析當前的環境，mysql_servers中顯示出當前只有192.168.153.149是可以寫的192.168.153.150以及192.168.153.151是用來讀的。 MySQL [(none)]> select * from mysql_servers ; +--------------+-----------------+------+--------------+--------+-------------+-----------------+---------------------+---------+----------------+---------+ | hostgroup_id | hostname | port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment | +--------------+-----------------+------+--------------+--------+-------------+-----------------+---------------------+---------+----------------+---------+ | 1 | 192.168.153.149 | 3306 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | | | 1 | 192.168.153.150 | 3306 | OFFLINE_SOFT | 1 | 0 | 1000 | 0 | 0 | 0 | | | 1 | 192.168.153.151 | 3306 | OFFLINE_SOFT | 1 | 0 | 1000 | 0 | 0 | 0 | | | 2 | 192.168.153.149 | 3306 | OFFLINE_SOFT | 1 | 0 | 1000 | 0 | 0 | 0 | | | 2 | 192.168.153.150 | 3306 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | | | 2 | 192.168.153.151 | 3306 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | | +--------------+-----------------+------+--------------+--------+-------------+-----------------+---------------------+---------+----------------+---------+ 6 rows in set (0.00 sec)各個節點的gr_member_routing_candidate_status視圖也顯示了當前節點是否是正常狀態的， proxysql就是讀取的這個視圖的信息來決定此節點是否可用。 mysql> select * from sys.gr_member_routing_candidate_status\G; *************************** 1. row ***************************viable_candidate: YESread_only: NOtransactions_behind: 0 transactions_to_cert: 0 1 row in set (0.00 sec)ERROR: No query specifiedmysql>12、設置讀寫分離
MySQL [(none)]> insert into mysql_query_rules (active, match_pattern, destination_hostgroup, apply) values (1,"^SELECT",2,1); Query OK, 1 row affected (0.00 sec)MySQL [(none)]> LOAD MYSQL QUERY RULES TO RUNTIME; Query OK, 0 rows affected (0.00 sec)MySQL [(none)]> SAVE MYSQL QUERY RULES TO DISK; Query OK, 0 rows affected (0.01 sec)解釋說明: match_pattern的規則是基于正則表達式的， active表示是否啟用這個sql路由項， match_pattern就是我們正則匹配項， destination_hostgroup表示我們要將該類sql轉發到哪些mysql上面去，這里我們將select轉發到group 2，。 apply為1表示該正則匹配后，將不再接受其他匹配，直接轉發。對于for update需要在gruop1上執行，可以加上規則： MySQL [(none)]> insert into mysql_query_rules(active,match_pattern,destination_hostgroup,apply) values(1,'^SELECT.*FOR UPDATE$',1,1); Query OK, 1 row affected (0.01 sec)在proxysql本機或其他客戶機上檢查下,select 語句,一直連接的是192.168.153.150和192.168.153.151 說明讀寫分離[root@mgr-node3 ~]# mysql -uproxysql -pproxysql -h192.168.153.152 -P6033 -e "select @@hostname" mysql: [Warning] Using a password on the command line interface can be insecure. +------------+ | @@hostname | +------------+ | mgr-node2 | +------------+ mysql: [Warning] Using a password on the command line interface can be insecure. +------------+ | @@hostname | +------------+ | mgr-node2 | +------------+ [root@mgr-node3 ~]# mysql -uproxysql -pproxysql -h192.168.153.152 -P6033 -e "select @@hostname" mysql: [Warning] Using a password on the command line interface can be insecure. +------------+ | @@hostname | +------------+ | mgr-node3 | +------------+ [root@mgr-node3 ~]# mysql -uproxysql -pproxysql -h192.168.153.152 -P6033 -e "select @@hostname" mysql: [Warning] Using a password on the command line interface can be insecure. +------------+ | @@hostname | +------------+ | mgr-node3 | +------------+ [root@mgr-node3 ~]# mysql -uproxysql -pproxysql -h192.168.153.152 -P6033 -e "select @@hostname" mysql: [Warning] Using a password on the command line interface can be insecure. +------------+ | @@hostname | +------------+ | mgr-node2 | +------------+驗證讀寫分離的效果[root@proxysql-node ~]# mysql -uproxysql -pproxysql -h192.168.153.152 -P6033 -e "select @@hostname" +------------+ | @@hostname | +------------+ | mgr-node2 | +------------+ [root@proxysql-node ~]# mysql -uproxysql -pproxysql -h192.168.153.152 -P6033 -e "select * from kevin.haha" +----+-----------+ | id | name | +----+-----------+ | 1 | wangshibo | | 2 | guohuihui | | 3 | yangyang | | 4 | shikui | | 11 | beijing | | 12 | shanghai | | 13 | anhui | +----+-----------+ [root@proxysql-node ~]# mysql -uproxysql -pproxysql -h192.168.153.152 -P6033 -e "delete from kevin.haha where id=1;" [root@proxysql-node ~]# mysql -uproxysql -pproxysql -h192.168.153.152 -P6033 -e "delete from kevin.haha where id=2;" [root@proxysql-node ~]# mysql -uproxysql -pproxysql -h192.168.153.152 -P6033 -e "select * from kevin.haha" +----+----------+ | id | name | +----+----------+ | 3 | yangyang | | 4 | shikui | | 11 | beijing | | 12 | shanghai | | 13 | anhui | +----+----------+[root@proxysql-node ~]# mysql -uproxysql -pproxysql -h192.168.153.152 -P6033 -e 'insert into kevin.haha values(21,"zhongguo"),(22,"xianggang"),(23,"taiwan");' [root@proxysql-node ~]# mysql -uproxysql -pproxysql -h192.168.153.152 -P6033 -e "select * from kevin.haha" +----+-----------+ | id | name | +----+-----------+ | 3 | yangyang | | 4 | shikui | | 11 | beijing | | 12 | shanghai | | 13 | anhui | | 21 | zhongguo | | 22 | xianggang | | 23 | taiwan | +----+-----------+最后在proxysql管理端查看讀寫分離情況[root@proxysql-node ~]# mysql -uadmin -padmin -h 127.0.0.1 -P6032 Welcome to the MariaDB monitor. Commands end with ; or \g. Your MySQL connection id is 1575 Server version: 5.5.30 (ProxySQL Admin Module)Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.MySQL [(none)]> select hostgroup,username,digest_text,count_star from stats_mysql_query_digest; +-----------+----------+------------------------------------------------+------------+ | hostgroup | username | digest_text | count_star | +-----------+----------+------------------------------------------------+------------+ | 1 | proxysql | delete from kevin.haha where id=? | 2 | | 1 | proxysql | insert into kevin.haha values(?,?),(?,?),(?,?) | 1 | | 2 | proxysql | select * from kevin.haha | 3 | | 2 | proxysql | select @@hostname | 5 | | 1 | proxysql | select @@hostname | 1 | | 1 | proxysql | select @@version_comment limit ? | 12 | +-----------+----------+------------------------------------------------+------------+ 6 rows in set (0.00 sec)MySQL [(none)]> select * from mysql_servers; +--------------+-----------------+------+--------------+--------+-------------+-----------------+---------------------+---------+----------------+---------+ | hostgroup_id | hostname | port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment | +--------------+-----------------+------+--------------+--------+-------------+-----------------+---------------------+---------+----------------+---------+ | 1 | 192.168.153.149 | 3306 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | | | 1 | 192.168.153.150 | 3306 | OFFLINE_SOFT | 1 | 0 | 1000 | 0 | 0 | 0 | | | 1 | 192.168.153.151 | 3306 | OFFLINE_SOFT | 1 | 0 | 1000 | 0 | 0 | 0 | | | 2 | 192.168.153.149 | 3306 | OFFLINE_SOFT | 1 | 0 | 1000 | 0 | 0 | 0 | | | 2 | 192.168.153.150 | 3306 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | | | 2 | 192.168.153.151 | 3306 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | | +--------------+-----------------+------+--------------+--------+-------------+-----------------+---------------------+---------+----------------+---------+ 6 rows in set (0.00 sec) 通過上面可以看到： 寫操作都分配到了group1組內，即寫操作分配到192.168.153.149節點上。 讀操作都分配到了group2組內，即讀操作分配到192.168.153.150、192.168.153.151節點上。13、設置故障應用無感應
在上面的讀寫分離規則中，我設置了192.168.153.149為可寫節點，192.168.153.150,192.168.153.151為只讀節點。如果此時192.168.153.149變成只讀模式的話，應用能不能直接連到其它的節點進行寫操作？
1、手動將192.168.153.149變成只讀模式：
mysql> set global read_only=1; Query OK, 0 rows affected (0.00 sec)2、在proxysql節點上查看，mysql_servers的狀態，自動將group1的192.168.153.150改成了online，group2的192.168.153.149,192.168.153.151變成online了，就表示將192.168.153.150變為可寫節點，其它兩個節點變為只讀節點了。
MySQL [(none)]> select * from mysql_servers; +--------------+-----------------+------+--------------+--------+-------------+-----------------+---------------------+---------+----------------+---------+ | hostgroup_id | hostname | port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment | +--------------+-----------------+------+--------------+--------+-------------+-----------------+---------------------+---------+----------------+---------+ | 1 | 192.168.153.149 | 3306 | OFFLINE_SOFT | 1 | 0 | 1000 | 0 | 0 | 0 | | | 1 | 192.168.153.150 | 3306 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | | | 1 | 192.168.153.151 | 3306 | OFFLINE_SOFT | 1 | 0 | 1000 | 0 | 0 | 0 | | | 2 | 192.168.153.149 | 3306 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | | | 2 | 192.168.153.150 | 3306 | OFFLINE_SOFT | 1 | 0 | 1000 | 0 | 0 | 0 | | | 2 | 192.168.153.151 | 3306 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | | +--------------+-----------------+------+--------------+--------+-------------+-----------------+---------------------+---------+----------------+---------+ 6 rows in set (0.00 sec)然后再將將192.168.153.149變為可寫模式后，mysql_servers也恢復過來了。mysql> set global read_only=0; Query OK, 0 rows affected (0.00 sec)然后在proxysql節點查看 MySQL [(none)]> select * from mysql_servers; +--------------+-----------------+------+--------------+--------+-------------+-----------------+---------------------+---------+----------------+---------+ | hostgroup_id | hostname | port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment | +--------------+-----------------+------+--------------+--------+-------------+-----------------+---------------------+---------+----------------+---------+ | 1 | 192.168.153.149 | 3306 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | | | 1 | 192.168.153.150 | 3306 | OFFLINE_SOFT | 1 | 0 | 1000 | 0 | 0 | 0 | | | 1 | 192.168.153.151 | 3306 | OFFLINE_SOFT | 1 | 0 | 1000 | 0 | 0 | 0 | | | 2 | 192.168.153.149 | 3306 | OFFLINE_SOFT | 1 | 0 | 1000 | 0 | 0 | 0 | | | 2 | 192.168.153.150 | 3306 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | | | 2 | 192.168.153.151 | 3306 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | | +--------------+-----------------+------+--------------+--------+-------------+-----------------+---------------------+---------+----------------+---------+ 6 rows in set (0.00 sec)經過測試將192.168.153.149節點停止組復制（stop group_replication）或者該節點宕機(mysql服務掛掉)后，mysql_servers表的信息也會正常的切換新的節點。 待192.168.153.149恢復再加入到組復制后，mysql_servers也會正常的將192.168.153.149改成online狀態。總結
以上是生活随笔為你收集整理的ProxySQL+MGR实现读写分离和主节点故障无感知切换的全部內容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： 莴笋_百度百科
- 下一篇： VTK读取序列DCM格式医学图像