Mysql高可用解决方案之InnoDB Cluster

来自原官方文档的自我简介

MySQL InnoDB cluster provides a complete high availability solution for MySQL. MySQL Shell includes AdminAPI which enables you to easily configure and administer a group of at least three MySQL server instances to function as an InnoDB cluster. Each MySQL server instance runs MySQL Group Replication, which provides the mechanism to replicate data within InnoDB clusters, with built-in failover. AdminAPI removes the need to work directly with Group Replication in InnoDB clusters, but for more information see Chapter 18, Group Replication which explains the details. MySQL Router can automatically configure itself based on the cluster you deploy, connecting client applications transparently to the server instances. In the event of an unexpected failure of a server instance the cluster reconfigures automatically. In the default single-primary mode, an InnoDB cluster has a single read-write server instance - the primary. Multiple secondary server instances are replicas of the primary. If the primary fails, a secondary is automatically promoted to the role of primary. MySQL Router detects this and forwards client applications to the new primary. Advanced users can also configure a cluster to have multiple-primaries.

简单地说就是官方提供的MySQL高可用的解决方案，包括MySQL、MySQL shell和MySQL Router，提供读写分离，备份容灾等，详情请查看官方文档InnoDB Cluster。

InnoDB Cluster

• mysql-5.7.21-linux-glibc2.12-x86_64.tar.gz
  配置用户组和创建日志文件夹

cd /usr/local/
tar -xzvf mysql-5.7.21-linux-glibc2.12-x86_64.tar.gz
mv mysql-5.7.21-linux-glibc2.12-x86_64 mysql
groupadd mysql
useradd -r -g mysql mysql
chown -R mysql:mysql mysql
cd /usr/local/mysql/
mkdir log
chown -R mysql:mysql log

修改配置文件
vi /etc/my.cnf (集群各节点 server_id需不重复)

[mysqld]
datadir=/data/mysql_data
basedir=/usr/local/mysql
socket=/tmp/mysql.sock
log-error=/usr/local/mysql/log/mysqld.log
symbolic-links=0

log_slave_updates = ON
server_id = 106206331
relay_log_info_repository = TABLE
master_info_repository = TABLE
transaction_write_set_extraction = XXHASH64
binlog_format = ROW
disabled_storage_engines = MyISAM,BLACKHOLE,FEDERATED,CSV,ARCHIVE
report_port = 3306
binlog_checksum = NONE
enforce_gtid_consistency = ON
log_bin
gtid_mode = ON
sql_mode = STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
max_connections=5000
wait_timeout=31536000
interactive_timeout=31536000
innodb_buffer_pool_size=1073741824
key_buffer_size=419430400
innodb_flush_log_at_trx_commit=2
binlog_row_image = MINIMAL

初始化数据库

./bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysql_data
chown -R mysql:mysql /data/mysql_data
cp support-files/mysql.server /etc/init.d/mysql
chkconfig --add mysql
chkconfig --list mysql
/etc/init.d/mysql start

注：初始化数据库之后，用cat /usr/local/mysql/log/mysqld.log 获取初始密码：例：2018-04-19T07:35:20.481280Z 1 [Note] A temporary password is generated for root@localhost: Qp,eL.ypX24p

修改/etc/profile文件，底部增加mysql环境变量，最后还需要修改数据库密码

export MYSQL_HOME=/usr/local/mysql
export PATH=${MYSQL_HOME}/bin:$PATH

• mysql-shell-1.0.11-1.el7.x86_64.rpm

rpm -ivh mysql-shell-1.0.11-1.el7.x86_64.rpm

• mysql-router-2.1.6-1.el7.x86_64.rpm

rpm -ivh mysql-router-2.1.6-1.el7.x86_64.rpm

用一下三个mysql实例搭建一个InnoDB集群
××.××.××.01:3306
××.××.××.02:3306
××.××.××.03:3306

• 检查各节点状态是否满足条件

[root@f××.××.××.01~]# mysqlsh
mysql-js> dba.checkInstanceConfiguration("root@××.××.××.01:3306")
Please provide the password for '××.××.××.01:3306':
Validating instance...

The instance '××.××.××.01:3306' is valid for Cluster usage
{
    "status": "ok"
}

其他两个实例重复以上操作。

• 创建集群

[root@××.××.××.01~]# mysqlsh
mysql-js> shell.connect('root@××.××.××.01:3306')
mysql-js> var cluster = dba.createCluster('myCluster')

添加实例

mysql-js> cluster.addInstance('root@××.××.××.02:3306)
mysql-js> cluster.addInstance('root@f××.××.××.03:3306')

查看集群状态

mysql-js>cluster.status()
{
  "clusterName": "myCluster",
  "defaultReplicaSet": {
  "name": "default",
  "primary": "××.××.××.01:3306",
  "status": "OK",
  "statusText": "Cluster is ONLINE and can tolerate up to ONE failure.",
  "topology": {
    "forming52:3306": {
      "address": "××.××.××.01:3306",
      "mode": "R/W",
      "readReplicas": {},
      "role": "HA",
      "status": "ONLINE"
    },
    "forming87:3306": {
      "address": "××.××.××.02:3306",
      "mode": "R/O",
      "readReplicas": {},
      "role": "HA",
      "status": "ONLINE"
    },
    "forming92:3306": {
      "address": "××.××.××.03:3306",
      "mode": "R/O",
      "readReplicas": {},
      "role": "HA",
      "status": "ONLINE"
    }
  }
 }
}

• 配置MySQL Router

[root@××.××.××.01~]# mysqlrouter --bootstrap root@××.××.××.01:3306 --user=mysqlrouter
Please enter MySQL password for root:
WARNING: The MySQL server does not have SSL configured and metadata used by the router may be transmitted unencrypted.

Bootstrapping system MySQL Router instance...
MySQL Router  has now been configured for the InnoDB cluster 'myCluster'.

The following connection information can be used to connect to the cluster.

Classic MySQL protocol connections to cluster 'myCluster':
- Read/Write Connections: localhost:6446
- Read/Only Connections: localhost:6447

X protocol connections to cluster 'myCluster':
- Read/Write Connections: localhost:64460
- Read/Only Connections: localhost:64470

Existing configurations backed up to /etc/mysqlrouter/mysqlrouter.conf.bak

[root@××.××.××.01~]# systemctl start mysqlrouter.service

• 测试链接
  mysql-shell连接：#mysqlsh --uri root@××.××.××.01:6446
  mysql连接：#mysql -u root -h ××.××.××.01 -P 6446 -p

• 注意事项

1. 每个表必须有主键；
2. 集群搭建过程中如果出现如下报错

Cluster.addInstance: WARNING: Unable to verify server_id of ××.××.××.01:3306
WARNING: Not running locally on the server and can not access its error log.
ERROR:
Group Replication join failed.
ERROR: Error joining instance to cluster: '××.××.××.01:3306' - Query failed. MySQL Error (3092): The server is not configureroperly to be an active member of the group. Please see more details on error log.. Query: START group_replication (RuntimeErr

可以采用如下措施

mysql> install plugin group_replication soname 'group_replication.so'; ##安装插件
mysql> set global group_replication_allow_local_disjoint_gtids_join=ON;
mysql> START GROUP_REPLICATION;
mysql> select * from performance_schema.replication_group_members;

3. cluster相关命令

 // 获取当前集群
var cluster = dba.getCluster('iCluster')
// 重新加入节点
cluster.rejoinInstance("root@hostname:3306") 
// 检查节点配置实例，用于加入cluster之前
dba.checkInstanceConfiguration("root@hostname:3306") 
//检查cluster里节点状态
cluster.checkInstanceState("root@hostname:3306") 
 // 删除集群
cluster.dissolve({force：true})
// 删除节点
cluster.removeInstance("root@hostname:3306") 
 // 强制删除节点
cluster.removeInstance('root@hostname:3306',{force:true})
 // 增加节点
cluster.addInstance("root@hostname:3306")

4. 集群和节点状态说明

集群状态
OK：所有节点处于online状态，有冗余节点；
OK_PARTIAL：有节点不可用，但仍有冗余节点；
OK_NO_TOLERANCE：有足够的online节点，但没有冗余，例如：两个节点的Cluster，其中一个挂了，集群就不可用了；
NO_QUORUM：有节点处于online状态，但达不到法定节点数，此状态下Cluster无法写入，只能读取；
UNKNOWN：不是online或recovering状态，尝试连接其他实例查看状态；
UNAVAILABLE：组内节点全是offline状态，但实例在运行，可能实例刚重启还没加入Cluster。

节点状态
ONLINE：节点状态正常；
OFFLINE：实例在运行，但没有加入任何Cluster；
RECOVERING：实例已加入Cluster，正在同步数据；
ERROR：同步数据发生异常；
UNREACHABLE：与其他节点通讯中断，可能是网络问题，可能是节点crash；
MISSING：节点已加入集群，但未启动group replication。