基于MySQL innodb cluster和MySQL router的高可用与读写分离

1.背景

2016年12月12日MySQL5.7.17版本GA了。其中最重要的一个新特性是,官方的高可用架构组复制(Group Replication)。
2017年4月份,基于组复制的一套完整的官方的MySQL高可用解决方案诞生了,这就是这篇文章要介绍的MySQL innodb
cluster。
运维同事搭建MySQL数据库高可用时又多了一套架构可以选择了。
而本文章,我们会更深入一些,带大家从零开始搭建完整的基于MySQL innodb cluster和MySQL router的高可用与读写分离的架构。

注意,文中实现的架构基于MGR的单主架构,而非多主架构。多主架构有一些坑,暂不推荐多主。文中单主架构采用的是最简单的三台mysqld服务器实现,读写分离为主提供写,两个从提供读。

2.架构图

3.基础环境

3.1 软硬件准备

三台数据库服务器:
192.168.199.121
192.168.199.122
192.168.199.123
一台应用服务器
192.168.199.198

规格:
系统:centos 7.5  
MySQL:8.0.13 二进制包  
MySQL shell: 8.0.13 二进制包  
MySQL router: 8.0.13 二进制包  

3.2 四台机hosts 修改

vi /etc/hosts
192.168.199.121 192-168-199-121
192.168.199.122 192-168-199-122
192.168.199.123 192-168-199-123

#应用服务器所在的host关系可以不配置进去,但应用服务器也要修改hosts文件

3.3 关闭防火墙和SELINUX

#关闭selinux
#selinux配置文件修改
vim /etc/selinux/config
SELINUX=disabled

#临时在线关闭selinux
setenforce 0

#关闭防火墙
systemctl stop firewalld
systemctl disable firewalld

4.开始搭建

4.1 MySQL组复制的搭建

#下载MySQL 8.0.13二进制安装包
wget https://dev.mysql.com/get/Downloads/MySQL-8.0/mysql-8.0.13-linux-glibc2.12-x86_64.tar.xz
#解压安装二进制程序
mkdir -p /opt/mysql-8.0
tar Jxvf mysql-8.0.13-linux-glibc2.12-x86_64.tar.xz -C /opt/mysql-8.0/ && cd  /opt/mysql-8.0/mysql-8.0.13-linux-glibc2.12-x86_64 && mv *  .. && ln -s /opt/mysql-8.0/ /usr/local/mysql
#添加mysql组和mysql用户
groupadd mysql 
useradd -M -g mysql -s /sbin/nologin -d /usr/local/mysql mysql
#创建MySQL数据目录
mkdir -p /database/mysql/data/3306
chown mysql:mysql /database/mysql/data/3306 -R

#修改加入环境变量
echo  'export PATH=/usr/local/mysql/bin/:$PATH' >>/etc/profile
source /etc/profile
#编辑配置文件 vi /etc/my.cnf
# “#”号右边的是MySQL官方默认值
[mysql]
auto-rehash
socket                              =/tmp/mysql.sock                #   /tmp/mysql.sock


[mysqld]
####: for global
user                                =mysql                          #	mysql
basedir                             =/usr/local/mysql/              #	/usr/local/mysql/
datadir                             =/database/mysql/data/3306      #	/usr/local/mysql/data
server_id                           =647                            #	0
port                                =3306                           #	3306
character_set_server                =utf8                           #	latin1
log_bin_trust_function_creators     =on                             #   0
max_prepared_stmt_count             =1048576
log_timestamps                      =system                         #	utc
socket                              =/tmp/mysql.sock                #	/tmp/mysql.sock
read_only                           =0                              #	off
skip_name_resolve                   =1                              #   0
auto_increment_increment            =1                              #	1
auto_increment_offset               =1                              #	1
lower_case_table_names              =1                              #	0
secure_file_priv                    =                               #	null
open_files_limit                    =65536                          #   1024
max_connections                     =1000
thread_cache_size                   =256                              #   9
table_open_cache                    =4096                           #   2000
table_definition_cache              =2000                           #   1400
table_open_cache_instances          =32                             #   16

####: for binlog
binlog_format                       =row                          #	row
log_bin                             =mysql-bin                      #	off
binlog_rows_query_log_events        =on                             #	off
log_slave_updates                   =on                             #	off
expire_logs_days                    =7                              #	0
binlog_cache_size                   =65536                          #	65536(64k)
binlog_checksum                     =none                           #	CRC32
sync_binlog                         =1                              #	1
slave-preserve-commit-order         =ON                             #   

####: for error-log
log_error                           =err.log                        #	/usr/local/mysql/data/localhost.localdomain.err

general_log                         =off                            #   off
general_log_file                    =general.log                    #   hostname.log

####: for slow query log
slow_query_log                      =on                             #    off
slow_query_log_file                 =slow.log                       #    hostname.log
log_queries_not_using_indexes       =on                             #    off
long_query_time                     =10.000000                       #    10.000000

####: for gtid
gtid_executed_compression_period    =1000                          #	1000
gtid_mode                           =on                            #	off
enforce_gtid_consistency            =on                            #	off


####: for replication
skip_slave_start                    =0                              #	
master_info_repository              =table                         #	file
relay_log_info_repository           =table                         #	file
slave_parallel_type                 =logical_clock                 #    database | LOGICAL_CLOCK
slave_parallel_workers              =2                             #    0
rpl_semi_sync_master_enabled        =1                             #    0
rpl_semi_sync_slave_enabled         =1                             #    0
rpl_semi_sync_master_timeout        =1000                          #    1000(1 second)
plugin_load_add                     =semisync_master.so            #
plugin_load_add                     =semisync_slave.so             #
binlog_group_commit_sync_delay      =500                          #    500(0.05%秒)、默认值0
binlog_group_commit_sync_no_delay_count = 13                        #    0


####: for innodb
default_storage_engine                          =innodb                     #	innodb
default_tmp_storage_engine                      =innodb                     #	innodb
innodb_data_file_path                           =ibdata1:64M:autoextend     #	ibdata1:12M:autoextend
innodb_temp_data_file_path                      =ibtmp1:12M:autoextend      #	ibtmp1:12M:autoextend
innodb_buffer_pool_filename                     =ib_buffer_pool             #	ib_buffer_pool
innodb_log_group_home_dir                       =./                         #	./
innodb_log_files_in_group                       =16                          #	2
innodb_log_file_size                            =256M                        #	50331648(48M)
innodb_file_per_table                           =on                         #	on
innodb_online_alter_log_max_size                =128M                  #   134217728(128M)
innodb_open_files                               =65535                       #   2000
innodb_page_size                                =16k                        #	16384(16k)
innodb_thread_concurrency                       =0                          #	0
innodb_read_io_threads                          =4                          #	4
innodb_write_io_threads                         =4                          #	4
innodb_purge_threads                            =4                          #	4(垃圾回收)
innodb_page_cleaners                            =4                          #   4(刷新lru脏页)
innodb_print_all_deadlocks                      =on                         #	off
innodb_deadlock_detect                          =on                         #	on
innodb_lock_wait_timeout                        =50                         #	50
innodb_spin_wait_delay                          =6                          #	6
innodb_autoinc_lock_mode                        =2                          #	1
innodb_io_capacity                              =200                        #   200
innodb_io_capacity_max                          =2000                       #   2000
#--------Persistent Optimizer Statistics
innodb_stats_auto_recalc                        =on                         #   on
innodb_stats_persistent                         =on                         #	on
innodb_stats_persistent_sample_pages            =20                         #	20
innodb_buffer_pool_instances                    =2
innodb_adaptive_hash_index                      =on                         #	on
innodb_change_buffering                         =all                        #	all
innodb_change_buffer_max_size                   =25                         #	25
innodb_flush_neighbors                          =1                          #	1
#innodb_flush_method                             =                           #	
innodb_doublewrite                              =on                         #	on
innodb_log_buffer_size                          =64M                        #	16777216(16M)
innodb_flush_log_at_timeout                     =1                          #	1
innodb_flush_log_at_trx_commit                  =1                          #	1
innodb_buffer_pool_size                         =2176M                  #	134217728(128M)
autocommit                                      =1                          #	1
#--------innodb scan resistant
innodb_old_blocks_pct                           =37                         #    37
innodb_old_blocks_time                          =1000                       #    1000
#--------innodb read ahead
innodb_read_ahead_threshold                     =56                         #    56 (0..64)
innodb_random_read_ahead                        =OFF                        #    OFF
#--------innodb buffer pool state
innodb_buffer_pool_dump_pct                     =25                         #    25 
innodb_buffer_pool_dump_at_shutdown             =ON                         #    ON
innodb_buffer_pool_load_at_startup              =ON                         #    ON




####  for performance_schema
performance_schema                                                      =on    #    on
performance_schema_consumer_global_instrumentation                      =on    #    on
performance_schema_consumer_thread_instrumentation                      =on    #    on
performance_schema_consumer_events_stages_current                       =on    #    off
performance_schema_consumer_events_stages_history                       =on    #    off
performance_schema_consumer_events_stages_history_long                  =off   #    off
performance_schema_consumer_statements_digest                           =on    #    on
performance_schema_consumer_events_statements_current                   =on    #    on
performance_schema_consumer_events_statements_history                   =on    #    on
performance_schema_consumer_events_statements_history_long              =off   #    off
performance_schema_consumer_events_waits_current                        =on    #    off
performance_schema_consumer_events_waits_history                        =on    #    off
performance_schema_consumer_events_waits_history_long                   =off   #    off
performance-schema-instrument                                           ='memory/%=COUNTED'

要注意组复制的my.cnf以下配置是必须要修改/增加的,三台server分别如下

#组复制前提配置
#192.168.199.121
server_id=121  #三台server分别配置121、122、123
gtid_mode=ON
enforce_gtid_consistency=ON
binlog_checksum=NONE

#组复制配置
loose-group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"
loose-group_replication_start_on_boot=off
loose-group_replication_local_address= "192.168.199.121:33061"
loose-group_replication_group_seeds= "192.168.199.121:33061,192.168.199.122:33062,192.168.199.123:33063"
loose-group_replication_bootstrap_group=off
#组复制前提配置
#192.168.199.122
server_id=122  #三台server分别配置121、122、123
gtid_mode=ON
enforce_gtid_consistency=ON
binlog_checksum=NONE

#组复制配置
loose-group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"
loose-group_replication_start_on_boot=off
loose-group_replication_local_address= "192.168.199.122:33062"
loose-group_replication_group_seeds= "192.168.199.121:33061,192.168.199.122:33062,192.168.199.123:33063"
loose-group_replication_bootstrap_group=off
#组复制前提配置
#192.168.199.123
server_id=123  #三台server分别配置121、122、123
gtid_mode=ON
enforce_gtid_consistency=ON
binlog_checksum=NONE

#组复制配置
loose-group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"
loose-group_replication_start_on_boot=off
loose-group_replication_local_address= "192.168.199.123:33063"
loose-group_replication_group_seeds= "192.168.199.121:33061,192.168.199.122:33062,192.168.199.123:33063"
loose-group_replication_bootstrap_group=off
#无密码初始化
mysqld --defaults-file=/etc/my.cnf --initialize-insecure 
#启动数据库
mysqld --defaults-file=/etc/my.cnf &

#当然也可以用以下做成服务的方式启动,这样有mysqld_safe守护进程
cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld
service mysqld start
#建立组复制
# 192.168.199.121、192.168.199.122、192.168.199.123 同操作
# 直接mysql即可无密码登录
mysql

# 以下是SQL
SET SQL_LOG_BIN=0;
CREATE USER rpl_user@'%' IDENTIFIED WITH 'mysql_native_password' BY 'password';
GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%';
CREATE USER root@'%' IDENTIFIED BY 'root';
GRANT ALL on *.* to root@'%' with grant option;
FLUSH PRIVILEGES;
SET SQL_LOG_BIN=1;

INSTALL PLUGIN group_replication SONAME 'group_replication.so';

SHOW PLUGINS;

CHANGE MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='password'  FOR CHANNEL 'group_replication_recovery';
# 192.168.199.121 SQL操作
SET GLOBAL group_replication_bootstrap_group=ON;
START GROUP_REPLICATION;
SET GLOBAL group_replication_bootstrap_group=OFF;
# 其他机器,即192.168.199.122、192.168.199.123只需要SQL操作
START GROUP_REPLICATION;

验证

SELECT * FROM performance_schema.replication_group_members;

192.168.199.121PRIMARY库上测试一下

#92.168.199.121PRIMARY库上测试一下
CREATE DATABASE test;
USE test;
CREATE TABLE t1 (c1 INT PRIMARY KEY, c2 TEXT NOT NULL);
INSERT INTO t1 VALUES (1, 'Luis');

#三台都执行验证一下gtid是否增加
show master status\G
SELECT * FROM performance_schema.replication_group_members;

于是组复制搭建完毕。

4.2 MySQL Shell的部署

MySQL Shell,它是一个支持MySQL服务器开发和管理的交互式JavaScript,Python或SQL接口。MySQL Shell是一个可以单独安装的组件,是安装和管理MySQL innodb cluster必须安装的组件,以下文中将用js模式操作。

192.168.199.121PRIMARY库上部署mysql-shell,或三台MGR主机都部署mysql-shell

#下载mysql-shell二进制安装包
wget https://dev.mysql.com/get/Downloads/MySQL-Shell/mysql-shell-8.0.13-linux-glibc2.12-x86-64bit.tar.gz
mkdir -p /opt/mysql-8.0-shell
tar -zxvf mysql-shell-8.0.13-linux-glibc2.12-x86-64bit.tar.gz -C /opt/mysql-8.0-shell && cd /opt/mysql-8.0-shell/mysql-shell-8.0.13-linux-glibc2.12-x86-64bit && mv * .. && ln -s /opt/mysql-8.0-shell/ /usr/local/mysqlshell

#加入环境变量并使其生效
echo  'export PATH=/usr/local/mysqlshell/bin/:$PATH' >>/etc/profile
source /etc/profile

4.3 使用mysql-shell创建MySQL innodb cluster

mysqlshell登录PRIMARY节点

mysqlsh --uri root@127.0.0.1:3306

用已有的MGR创建innodb cluster

 MySQL  127.0.0.1:3306 ssl  JS > var fandercluster = dba.createCluster('fandercluster')
You are connected to an instance that belongs to an unmanaged replication group.
Do you want to setup an InnoDB cluster based on this replication group? [Y/n]: y
A new InnoDB cluster will be created based on the existing replication group on instance 'root@127.0.0.1:3306'.

Creating InnoDB cluster 'fandercluster' on 'root@127.0.0.1:3306'...
Adding Seed Instance...
Adding Instance '192-168-199-122:3306'...
Adding Instance '192-168-199-123:3306'...

Cluster successfully created based on existing replication group.

查看innodb cluster状况

 MySQL  127.0.0.1:3306 ssl  JS > fandercluster.status()
{
    "clusterName": "fandercluster", 
    "defaultReplicaSet": {
        "name": "default", 
        "primary": "192-168-199-121:3306", 
        "ssl": "DISABLED", 
        "status": "OK", 
        "statusText": "Cluster is ONLINE and can tolerate up to ONE failure.", 
        "topology": {
            "192-168-199-121:3306": {
                "address": "192-168-199-121:3306", 
                "mode": "R/W", 
                "readReplicas": {}, 
                "role": "HA", 
                "status": "ONLINE"
            }, 
            "192-168-199-122:3306": {
                "address": "192-168-199-122:3306", 
                "mode": "R/O", 
                "readReplicas": {}, 
                "role": "HA", 
                "status": "ONLINE"
            }, 
            "192-168-199-123:3306": {
                "address": "192-168-199-123:3306", 
                "mode": "R/O", 
                "readReplicas": {}, 
                "role": "HA", 
                "status": "ONLINE"
            }
        }
    }, 
    "groupInformationSourceMember": "mysql://root@127.0.0.1:3306"
}

可以看出121是R/W模式,是PRIMARY节点。122和123是R/O模式,是SECONDARY节点。MGR状态良好。
这样,MySQL innodb cluster就部署好了。

4.4 MySQL Router部署

MySQL路由器实际上是InnoDB Cluster的一部分,是轻量级中间件,可在应用程序和后端MySQL服务器之间提供透明路由。它可用于各种用例,例如通过有效地将数据库流量路由到适当的后端MySQL服务器来提供高可用性和可伸缩性。

有多少台应用服务器就要部署多少个mysql-router,这是官方推荐的架构。我们只有一台应用服务器192.168.199.198,我们部署一个mysql-router。

#下载mysql-router二进制安装包
wget https://dev.mysql.com/get/Downloads/MySQL-Router/mysql-router-8.0.13-linux-glibc2.12-x86_64.tar.xz
mkdir -p /opt/mysql-8.0-router
tar -Jxvf mysql-router-8.0.13-linux-glibc2.12-x86_64.tar.xz -C /opt/mysql-8.0-router && cd /opt/mysql-8.0-router/mysql-router-8.0.13-linux-glibc2.12-x86_64 && mv * .. && ln -s /opt/mysql-8.0-router/ /usr/local/mysqlrouter

#加入环境变量并使其生效
echo  'export PATH=/usr/local/mysqlrouter/bin/:$PATH' >>/etc/profile
source /etc/profile

初始化mysql-router

#IP地址填PRIMARY节点即可

[root@localhost ~]# mysqlrouter --bootstrap root@192.168.199.121:3306 --user=root
Please enter MySQL password for root: 

Bootstrapping system MySQL Router instance...
Checking for old Router accounts
Creating account mysql_router1_klh7m3xmmru0@'%'
MySQL Router  has now been configured for the InnoDB cluster 'fandercluster'.

The following connection information can be used to connect to the cluster after MySQL Router has been started with generated configuration..

Classic MySQL protocol connections to cluster 'fandercluster':
- Read/Write Connections: localhost:6446
- Read/Only Connections: localhost:6447
X protocol connections to cluster 'fandercluster':
- Read/Write Connections: localhost:64460
- Read/Only Connections: localhost:64470

初始化后mysql-router自动生成一个默认的配置文件,在安装路径

[root@localhost mysqlrouter]# pwd
/usr/local/mysqlrouter
[root@localhost mysqlrouter]# ll mysqlrouter.conf 
-rw------- 1 root root 1222 Nov 30 14:34 mysqlrouter.conf

我们稍微修改一下配置文件
vi mysqlrouter.conf

# File automatically generated during MySQL Router bootstrap
[DEFAULT]
name=system
user=root
keyring_path=/opt/mysql-8.0-router/data/keyring
master_key_path=/opt/mysql-8.0-router/bin/.././mysqlrouter.key
connect_timeout=30
read_timeout=30

[logger]
level = INFO

[metadata_cache:fandercluster]
router_id=1
bootstrap_server_addresses=mysql://192-168-199-122:3306,mysql://192-168-199-123:3306,mysql://192-168-199-121:3306
user=mysql_router1_klh7m3xmmru0
metadata_cluster=fandercluster
ttl=0.5

[routing:fandercluster_default_rw]
bind_address=0.0.0.0
bind_port=6446
destinations=metadata-cache://fandercluster/default?role=PRIMARY
routing_strategy=round-robin
protocol=classic

[routing:fandercluster_default_ro]
bind_address=0.0.0.0
bind_port=6447
destinations=metadata-cache://fandercluster/default?role=SECONDARY
#routing_strategy=round-robin
routing_strategy=round-robin-with-fallback
protocol=classic

[routing:fandercluster_default_x_rw]
bind_address=0.0.0.0
bind_port=64460
destinations=metadata-cache://fandercluster/default?role=PRIMARY
routing_strategy=round-robin
protocol=x

[routing:fandercluster_default_x_ro]
bind_address=0.0.0.0
bind_port=64470
destinations=metadata-cache://fandercluster/default?role=SECONDARY
routing_strategy=round-robin
protocol=x

注释的地方即为我修改的地方。可以看出配置文件配置了PRIMARY节点是读写模式的,SECONDARY是只读模式。

[routing:fandercluster_default_ro]
bind_address=0.0.0.0
bind_port=6447
destinations=metadata-cache://fandercluster/default?role=SECONDARY
#routing_strategy=round-robin
routing_strategy=round-robin-with-fallback
protocol=classic

在这里,routing_strategy=round-robin,读请求是在122、123两个只读服务器上轮训的。但123服务器挂了后,依然会在122和123轮训,这个用户体验就很差了,因为此时访问123会报错。而且当122、123两个数据库都挂的情况下,只读请求无法打回到121上。而routing_strategy=round-robin-with-fallback算法解决上面的问题。

启动mysqlrouter

mysqlrouter &

这样,我们就部署好mysql router了,注意mysql router的路由模式是port base的,它提供了一个写端口和一个读端口来实现读写分离。

5.MySQL innodb cluster读写分离测试

5.1 测试写端口

[root@localhost mysqlrouter]# for ((i=0;i<=5;i++));do mysql -uroot -proot -P6446 -h127.0.0.1 -e "select @@server_id";done;
mysql: [Warning] Using a password on the command line interface can be insecure.
+-------------+
| @@server_id |
+-------------+
|         121 |
+-------------+
mysql: [Warning] Using a password on the command line interface can be insecure.
+-------------+
| @@server_id |
+-------------+
|         121 |
+-------------+
mysql: [Warning] Using a password on the command line interface can be insecure.
+-------------+
| @@server_id |
+-------------+
|         121 |
+-------------+
mysql: [Warning] Using a password on the command line interface can be insecure.
+-------------+
| @@server_id |
+-------------+
|         121 |
+-------------+
mysql: [Warning] Using a password on the command line interface can be insecure.
+-------------+
| @@server_id |
+-------------+
|         121 |
+-------------+
mysql: [Warning] Using a password on the command line interface can be insecure.
+-------------+
| @@server_id |
+-------------+
|         121 |
+-------------+

测试符合预期

5.2测试读端口

[root@localhost mysqlrouter]# for ((i=0;i<=5;i++));do mysql -uroot -proot -P6447 -h127.0.0.1 -e "select @@server_id";done; 
mysql: [Warning] Using a password on the command line interface can be insecure.
+-------------+
| @@server_id |
+-------------+
|         122 |
+-------------+
mysql: [Warning] Using a password on the command line interface can be insecure.
+-------------+
| @@server_id |
+-------------+
|         123 |
+-------------+
mysql: [Warning] Using a password on the command line interface can be insecure.
+-------------+
| @@server_id |
+-------------+
|         122 |
+-------------+
mysql: [Warning] Using a password on the command line interface can be insecure.
+-------------+
| @@server_id |
+-------------+
|         123 |
+-------------+
mysql: [Warning] Using a password on the command line interface can be insecure.
+-------------+
| @@server_id |
+-------------+
|         122 |
+-------------+
mysql: [Warning] Using a password on the command line interface can be insecure.
+-------------+
| @@server_id |
+-------------+
|         123 |
+-------------+

读端口在两台SECONDARY节点上轮训,符合预期

5.3模拟SECONDARY节点挂了一台,读路由情况

[root@192-168-199-123 ~]# service mysqld stop
Shutting down MySQL............. SUCCESS! 
[root@localhost mysqlrouter]# for ((i=0;i<=5;i++));do mysql -uroot -proot -P6447 -h127.0.0.1 -e "select @@server_id";done;
mysql: [Warning] Using a password on the command line interface can be insecure.
+-------------+
| @@server_id |
+-------------+
|         122 |
+-------------+
mysql: [Warning] Using a password on the command line interface can be insecure.
+-------------+
| @@server_id |
+-------------+
|         122 |
+-------------+
mysql: [Warning] Using a password on the command line interface can be insecure.
+-------------+
| @@server_id |
+-------------+
|         122 |
+-------------+
mysql: [Warning] Using a password on the command line interface can be insecure.
+-------------+
| @@server_id |
+-------------+
|         122 |
+-------------+
mysql: [Warning] Using a password on the command line interface can be insecure.
+-------------+
| @@server_id |
+-------------+
|         122 |
+-------------+
mysql: [Warning] Using a password on the command line interface can be insecure.
+-------------+
| @@server_id |
+-------------+
|         122 |
+-------------+

123服务器挂了,读端口只路由到122服务上,测试符合预期。

5.4模拟SECONDARY节点两台都挂了

[root@192-168-199-122 ~]# service mysqld stop
Shutting down MySQL............. SUCCESS! 
[root@192-168-199-123 ~]# service mysqld stop
Shutting down MySQL............. SUCCESS! 
[root@localhost mysqlrouter]# for ((i=0;i<=5;i++));do mysql -uroot -proot -P6447 -h127.0.0.1 -e "select @@server_id";done;
mysql: [Warning] Using a password on the command line interface can be insecure.
+-------------+
| @@server_id |
+-------------+
|         121 |
+-------------+
mysql: [Warning] Using a password on the command line interface can be insecure.
+-------------+
| @@server_id |
+-------------+
|         121 |
+-------------+
mysql: [Warning] Using a password on the command line interface can be insecure.
+-------------+
| @@server_id |
+-------------+
|         121 |
+-------------+
mysql: [Warning] Using a password on the command line interface can be insecure.
+-------------+
| @@server_id |
+-------------+
|         121 |
+-------------+
mysql: [Warning] Using a password on the command line interface can be insecure.
+-------------+
| @@server_id |
+-------------+
|         121 |
+-------------+
mysql: [Warning] Using a password on the command line interface can be insecure.
+-------------+
| @@server_id |
+-------------+
|         121 |
+-------------+

所有SECONDARY服务器挂了,读端口路由到PRIMARY节点服务器121上,测试符合预期。(上面提到的round-robin-with-fallback算法)

5.5模拟PRIMARY-SECONDARY切换了,现在122是PRIMARY节点了,其他为SECONDARY节点。

mysql> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-----------------+-------------+--------------+-------------+----------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST     | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |
+---------------------------+--------------------------------------+-----------------+-------------+--------------+-------------+----------------+
| group_replication_applier | 1e24f619-f44f-11e8-a0ec-000c29cde11e | 192-168-199-122 |        3306 | ONLINE       | PRIMARY     | 8.0.13         |
| group_replication_applier | 26602f27-f44f-11e8-a732-000c290c24bc | 192-168-199-123 |        3306 | ONLINE       | SECONDARY   | 8.0.13         |
| group_replication_applier | b0b6f17f-f44e-11e8-b0e2-000c2983982f | 192-168-199-121 |        3306 | ONLINE       | SECONDARY   | 8.0.12         |
+---------------------------+--------------------------------------+-----------------+-------------+--------------+-------------+----------------+
3 rows in set (0.00 sec)
[root@localhost mysqlrouter]# for ((i=0;i<=5;i++));do mysql -uroot -proot -P6446 -h127.0.0.1 -e "select @@server_id";done;
mysql: [Warning] Using a password on the command line interface can be insecure.
+-------------+
| @@server_id |
+-------------+
|         122 |
+-------------+
mysql: [Warning] Using a password on the command line interface can be insecure.
+-------------+
| @@server_id |
+-------------+
|         122 |
+-------------+
mysql: [Warning] Using a password on the command line interface can be insecure.
+-------------+
| @@server_id |
+-------------+
|         122 |
+-------------+
mysql: [Warning] Using a password on the command line interface can be insecure.
+-------------+
| @@server_id |
+-------------+
|         122 |
+-------------+
mysql: [Warning] Using a password on the command line interface can be insecure.
+-------------+
| @@server_id |
+-------------+
|         122 |
+-------------+
mysql: [Warning] Using a password on the command line interface can be insecure.
+-------------+
| @@server_id |
+-------------+
|         122 |
+-------------+
[root@localhost mysqlrouter]# for ((i=0;i<=5;i++));do mysql -uroot -proot -P6447 -h127.0.0.1 -e "select @@server_id";done;
mysql: [Warning] Using a password on the command line interface can be insecure.
+-------------+
| @@server_id |
+-------------+
|         123 |
+-------------+
mysql: [Warning] Using a password on the command line interface can be insecure.
+-------------+
| @@server_id |
+-------------+
|         121 |
+-------------+
mysql: [Warning] Using a password on the command line interface can be insecure.
+-------------+
| @@server_id |
+-------------+
|         123 |
+-------------+
mysql: [Warning] Using a password on the command line interface can be insecure.
+-------------+
| @@server_id |
+-------------+
|         121 |
+-------------+
mysql: [Warning] Using a password on the command line interface can be insecure.
+-------------+
| @@server_id |
+-------------+
|         123 |
+-------------+
mysql: [Warning] Using a password on the command line interface can be insecure.
+-------------+
| @@server_id |
+-------------+
|         121 |
+-------------+

写端口路由到PRIMARY服务器122上,读端口路由到SECONDARY服务器121和123上,测试符合预期。

6.Troubleshoot

6.1 组复制无法online,复制报2061错误

参考这里

#原因是没有使用SSL时,你创建复制密码时要指明mysql_native_password
CREATE USER rpl_user@'%' IDENTIFIED WITH 'mysql_native_password' BY 'password';

6.2 mysqlrouter初始化时报1044 mysql error

[root@localhost mysqlrouter]# mysqlrouter --bootstrap root@192.168.199.121:3306 --user=root
Please enter MySQL password for root: 

Bootstrapping system MySQL Router instance...
Checking for old Router accounts
Creating account mysql_router1_xi3qiedz3zuw@'%'
Executing statements failed with: 'Error creating MySQL account for router: Error executing MySQL query: Access denied for user 'root'@'%' to database 'mysql_innodb_cluster_metadata' (1044)' (1044), trying to connect to another node
Error: Error creating MySQL account for router: Error executing MySQL query: Access denied for user 'root'@'%' to database 'mysql_innodb_cluster_metadata' (1044)


Error: It appears that a router instance named 'system' has been previously configured in this host. If that instance no longer exists, use the --force option to overwrite it.

解决: 初始化时--user=root这个用户必须要有授权权限(with grant option)

GRANT ALL on *.* to root@'%' with grant option;

7.总结

MGR架构可以实现高可用,但实现failover,则需要安装InnoDB Cluster了。MySQL shell和MySQL Router是InnoDB Cluster集群的一部分。MySQL shell用于安装和管理InnoDB Cluster集群,InnoDB Cluster集群通过新建的原数据库MySQL_innodb_cluster_metadata管理MGR集群状态,MySQL Router通过周期性的访问Cluster创建的MySQL_innodb_cluster_metadata库中的元数据获取集群成员信息,再通过performance_schema的系统表获取可连接实例及其状态来实现failover和读负载均衡、读写分离的路由。
MySQL Router非常轻量级,性能损耗小于1%,官方建议每台应用上部署一个mysqlrouter节点,优点是节省网络带宽。缺点是mysqlrouter太轻量级了,只能提供简单的基于端口的读写路由(Port based routing)和基于权重轮询的负载均衡(Weighted Balance),不支持基于SQL的读写分离(Query based routing)和空闲优先的负载均衡(Application Layer balancer)。

posted on 2018-12-05 15:47  fanderchan  阅读(1909)  评论(0编辑  收藏  举报

导航