MySQL 8.0.12 InnoDB Cluster 单主机搭建

77 阅读 0 评论 51 点赞
我是靠谱客的博主含糊草丛，最近开发中收集的这篇文章主要介绍MySQL 8.0.12 InnoDB Cluster 单主机搭建，觉得挺不错的，现在分享给大家，希望可以做个参考。
概述

1.准备工作：
1.0 下载RPM包安装：
# rpm -ivh mysql-router-8.0.12-1.el7.x86_64.rpm
# rpm -ivh mysql-shell-8.0.12-1.el7.x86_64.rpm 

# rpm -qa | grep -i mysql
mysql-router-8.0.12-1.el7.x86_64
mysql-shell-8.0.12-1.el7.x86_64

1.1 关闭SELinux
# cat /etc/sysconfig/selinux  | grep -v ^#

SELINUX=disabled
SELINUXTYPE=targeted 
## getenforce 
Disabled
1.2 关闭防火墙：
systemctl stop firewalld
systemctl disable firewalld

1.3 设置IP和主机名的映射(关键步骤)
# cat /etc/hosts
127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4
::1         localhost localhost.localdomain localhost6 localhost6.localdomain6
192.168.0.151 node1

1.4 系统优化：
cat>>/etc/sysctl.conf <<EOF
fs.aio-max-nr = 1048576
fs.file-max = 681574400
kernel.shmmax = 137438953472 
kernel.shmmni = 4096
kernel.sem = 250 32000 100 200
net.ipv4.ip_local_port_range = 9000 65000
net.core.rmem_default = 262144
net.core.rmem_max = 4194304
net.core.wmem_default = 262144
net.core.wmem_max = 1048586
EOF

cat>>/etc/security/limits.conf <<EOF
* soft nproc 65536
* hard nproc 65536
* soft nofile 65536
* hard nofile 65536
EOF

2.准备每个实例的配置文件：
-- 实例1：
slave-parallel-type=LOGICAL_CLOCK
slave-preserve-commit-order=ON
master_info_repository = TABLE
relay_log_info_repository = TABLE

#GR
#plugin-load=group_replication.so
server_id=8010
gtid_mode=ON
enforce_gtid_consistency=ON
binlog_checksum=NONE
transaction_write_set_extraction=XXHASH64
loose-group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"
loose-group_replication_start_on_boot=off
loose-group_replication_local_address= "192.168.0.151:24901"
loose-group_replication_group_seeds= "192.168.0.151:24901,192.168.0.151:24902,192.168.0.151:24903"
loose-group_replication_bootstrap_group=off
loose-group_replication_ip_whitelist="192.168.0.0/16"


-- 实例2：
slave-parallel-type=LOGICAL_CLOCK
slave-preserve-commit-order=ON
master_info_repository = TABLE
relay_log_info_repository = TABLE

#GR
#plugin-load=group_replication.so
server_id=8011
gtid_mode=ON
enforce_gtid_consistency=ON
binlog_checksum=NONE
transaction_write_set_extraction=XXHASH64
loose-group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"
loose-group_replication_start_on_boot=off
loose-group_replication_local_address= "192.168.0.151:24902"
loose-group_replication_group_seeds= "192.168.0.151:24901,192.168.0.151:24902,192.168.0.151:24903"
loose-group_replication_bootstrap_group=off
loose-group_replication_ip_whitelist="192.168.0.0/16"

-- 实例3：
slave-parallel-type=LOGICAL_CLOCK
slave-preserve-commit-order=ON
master_info_repository = TABLE
relay_log_info_repository = TABLE

#GR
#plugin-load=group_replication.so
server_id=8012
gtid_mode=ON
enforce_gtid_consistency=ON
binlog_checksum=NONE
transaction_write_set_extraction=XXHASH64
loose-group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"
loose-group_replication_start_on_boot=off
loose-group_replication_local_address= "192.168.0.151:24903"
loose-group_replication_group_seeds= "192.168.0.151:24901,192.168.0.151:24902,192.168.0.151:24903"
loose-group_replication_bootstrap_group=off
loose-group_replication_ip_whitelist="192.168.0.0/16"

4.创建和启动实例：
参考：
https://mp.csdn.net/postedit/82765213

5.创建用户：
mysql> select host,user from mysql.user;
+-----------+------------------+
| host      | user             |
+-----------+------------------+
| localhost | mysql.infoschema |
| localhost | mysql.session    |
| localhost | mysql.sys        |
| localhost | root             |
+-----------+------------------+
4 rows in set (0.00 sec)

mysql> set sql_log_bin=0;  
Query OK, 0 rows affected (0.00 sec)

mysql> create user root@'%' identified by 'root';
Query OK, 0 rows affected (0.01 sec)

mysql> GRANT all on *.* TO root@'%' WITH GRANT OPTION;
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP,RELOAD,SHUTDOWN,PROCESS,FILE,REFERENCES,INDEX,ALTER,SHOW DATABASES,SUPER,CREATE TEMPORARY TABLES,LOCK TABLES,EXECUTE,REPLICATION SLAVE,REPLICATION CLIENT,CREATE VIEW,SHOW VIEW,CREATE ROUTINE,ALTER ROUTINE,CREATE USER,EVENT,TRIGGER,CREATE TABLESPACE,CREATE ROLE,DROP ROLE ON *.* TO root@'%' WITH GRANT OPTION;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> GRANT BACKUP_ADMIN,BINLOG_ADMIN,CONNECTION_ADMIN,ENCRYPTION_KEY_ADMIN,GROUP_REPLICATION_ADMIN,PERSIST_RO_VARIABLES_ADMIN,REPLICATION_SLAVE_ADMIN,RESOURCE_GROUP_ADMIN,RESOURCE_GROUP_USER,ROLE_ADMIN,SET_USER_ID,SYSTEM_VARIABLES_ADMIN,XA_RECOVER_ADMIN ON *.* TO root@'%' WITH GRANT OPTION;
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> set sql_log_bin=1;
Query OK, 0 rows affected (0.00 sec)

mysql> select host,user from mysql.user;
+-----------+------------------+
| host      | user             |
+-----------+------------------+
| %         | root             |
| localhost | mysql.infoschema |
| localhost | mysql.session    |
| localhost | mysql.sys        |
| localhost | root             |
+-----------+------------------+
5 rows in set (0.00 sec)

-- 创建用户和授权的语句：
set sql_log_bin=0;  
create user root@'%' identified by 'root';
GRANT all on *.* TO root@'%' WITH GRANT OPTION;
GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP,RELOAD,SHUTDOWN,PROCESS,FILE,REFERENCES,INDEX,ALTER,SHOW DATABASES,SUPER,CREATE TEMPORARY TABLES,LOCK TABLES,EXECUTE,REPLICATION SLAVE,REPLICATION CLIENT,CREATE VIEW,SHOW VIEW,CREATE ROUTINE,ALTER ROUTINE,CREATE USER,EVENT,TRIGGER,CREATE TABLESPACE,CREATE ROLE,DROP ROLE ON *.* TO root@'%' WITH GRANT OPTION;
GRANT BACKUP_ADMIN,BINLOG_ADMIN,CONNECTION_ADMIN,ENCRYPTION_KEY_ADMIN,GROUP_REPLICATION_ADMIN,PERSIST_RO_VARIABLES_ADMIN,REPLICATION_SLAVE_ADMIN,RESOURCE_GROUP_ADMIN,RESOURCE_GROUP_USER,ROLE_ADMIN,SET_USER_ID,SYSTEM_VARIABLES_ADMIN,XA_RECOVER_ADMIN ON *.* TO root@'%' WITH GRANT OPTION;
flush privileges;
set sql_log_bin=1;

6.检查配置：
# rpm -ql mysql-shell
/usr/bin/mysql-secret-store-login-path
/usr/bin/mysqlsh

默认使用JS检查每个实例的配置信息。

# mysqlsh
MySQL Shell 8.0.12

Copyright (c) 2016, 2018, 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 '?' for help; 'quit' to exit.


 MySQL  JS >  dba.checkInstanceConfiguration('root@node1:8010')
Please provide the password for 'root@node1:8010': ****
Save password for 'root@node1:8010'? [Y]es/[N]o/Ne[v]er (default No): Y
Validating local MySQL instance listening at port 8010 for use in an InnoDB cluster...

This instance reports its own address as node1.example.com
Clients and other cluster members will communicate with it through this address by default. If this is not correct, the report_host MySQL system variable should be changed.

Checking whether existing tables comply with Group Replication requirements...
No incompatible tables detected

Checking instance configuration...
Instance configuration is compatible with InnoDB cluster

The instance 'node1:8010' is valid for InnoDB cluster usage.

{
    "status": "ok"
}

 MySQL  JS > 

执行 dba.checkInstanceConfiguration，当输出 "status": "ok"的时候，表示该服务器检查通过。
依次检查后面的2个实例：
 MySQL  JS >  dba.checkInstanceConfiguration('root@node1:8011')
 MySQL  JS >  dba.checkInstanceConfiguration('root@node1:8012')

--退出：
 MySQL  JS > quit
Bye!

7.创建组复制：

[root@node1 ~]#  mysqlsh --uri root@node1:8010
Creating a session to 'root@node1:8010'
Fetching schema names for autocompletion... Press ^C to stop.
Your MySQL connection id is 9
Server version: 8.0.12 MySQL Community Server - GPL
No default schema selected; type use <schema> to set one.
MySQL Shell 8.0.12

Copyright (c) 2016, 2018, 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 '?' for help; 'quit' to exit.


 MySQL  node1:8010 ssl  JS > var cluster = dba.createCluster('InnoDBCluster')
A new InnoDB cluster will be created on instance 'root@node1:8010'.

Validating instance at node1:8010...

This instance reports its own address as node1

Instance configuration is suitable.
Creating InnoDB cluster 'InnoDBCluster' on 'root@node1:8010'...
Adding Seed Instance...

Cluster successfully created. Use Cluster.addInstance() to add MySQL instances.
At least 3 instances are needed for the cluster to be able to withstand up to
one server failure.


 MySQL  node1:8010 ssl  JS > cluster.addInstance('root@node1:8011') 
A new instance will be added to the InnoDB cluster. Depending on the amount of
data on the cluster this might take from a few seconds to several hours.

Adding instance to the cluster ...

Validating instance at node1:8011...

This instance reports its own address as node1

Instance configuration is suitable.
The instance 'root@node1:8011' was successfully added to the cluster.

MySQL  node1:8010 ssl  JS > cluster.addInstance('root@node1:8012')
A new instance will be added to the InnoDB cluster. Depending on the amount of
data on the cluster this might take from a few seconds to several hours.

Adding instance to the cluster ...

Validating instance at node1:8012...

This instance reports its own address as node1

Instance configuration is suitable.
The instance 'root@node1:8012' was successfully added to the cluster.


--上述操作步骤：
 mysqlsh --uri root@node1:8010 
  var cluster = dba.createCluster('InnoDBCluster')
  cluster.addInstance('root@node1:8011') 
  cluster.addInstance('root@node1:8012')
--集群状态检查：
MySQL  node1:8010 ssl  JS > 
 MySQL  node1:8010 ssl  JS > cluster.status()
{
    "clusterName": "InnoDBCluster", 
    "defaultReplicaSet": {
        "name": "default", 
        "primary": "node1:8010", 
        "ssl": "REQUIRED", 
        "status": "OK", 
        "statusText": "Cluster is ONLINE and can tolerate up to ONE failure.", 
        "topology": {
            "node1:8010": {
                "address": "node1:8010", 
                "mode": "R/W", 
                "readReplicas": {}, 
                "role": "HA", 
                "status": "ONLINE"
            }, 
            "node1:8011": {
                "address": "node1:8011", 
                "mode": "R/O", 
                "readReplicas": {}, 
                "role": "HA", 
                "status": "ONLINE"
            }, 
            "node1:8012": {
                "address": "node1:8012", 
                "mode": "R/O", 
                "readReplicas": {}, 
                "role": "HA", 
                "status": "ONLINE"
            }
        }
    }, 
    "groupInformationSourceMember": "mysql://root@node1:8010"
}


8.MySQL-router配置：
# rpm -ql mysql-router | grep -E 'etc|var'
/etc/mysqlrouter
/etc/mysqlrouter/mysqlrouter.conf
/var/log/mysqlrouter
/var/run/mysqlrouter

# id mysqlrouter 
uid=998(mysqlrouter) gid=996(mysqlrouter) groups=996(mysqlrouter)


--创建mysqlrouter的数据存储：
#mkdir -p /data/mysql80/mysqlrouter
--启动mysqlrouter：
# mysqlrouter --bootstrap root@node1:8010 --user mysqlrouter   --directory /data/mysql80/mysqlrouter --user=root --conf-use-sockets --force  
Please enter MySQL password for root: 

Bootstrapping MySQL Router instance at '/data/mysql80/mysqlrouter'...
Checking for old Router accounts
Creating account mysql_router3_0c9aklqsov5w@'%'
MySQL Router  has now been configured for the InnoDB cluster 'InnoDBCluster'.

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

Classic MySQL protocol connections to cluster 'InnoDBCluster':
- Read/Write Connections: localhost:6446
- Read/Write Connections: /data/mysql80/mysqlrouter/mysql.sock
- Read/Only Connections: localhost:6447
- Read/Only Connections: /data/mysql80/mysqlrouter/mysqlro.sock

X protocol connections to cluster 'InnoDBCluster':
- Read/Write Connections: localhost:64460
- Read/Write Connections: /data/mysql80/mysqlrouter/mysqlx.sock
- Read/Only Connections: localhost:64470
- Read/Only Connections: /data/mysql80/mysqlrouter/mysqlxro.sock

注释：
读写连接：localhost:6446

只读连接：localhost:6447

--启动mysqlrouter服务：
chown -R mysqlrouter:mysqlrouter /var/log/mysqlrouter/
 chmod -R 775 /var/log/mysqlrouter/
# systemctl start mysqlrouter                           
# systemctl status mysqlrouter                          
● mysqlrouter.service - MySQL Router
   Loaded: loaded (/usr/lib/systemd/system/mysqlrouter.service; disabled; vendor preset: disabled)
   Active: active (running) since Wed 2018-09-19 00:22:17 CST; 3s ago
 Main PID: 1442 (main)
   CGroup: /system.slice/mysqlrouter.service
           └─1442 /usr/bin/mysqlrouter -c /etc/mysqlrouter/mysqlrouter.conf

Sep 19 00:22:17 node1 systemd[1]: Started MySQL Router.
Sep 19 00:22:17 node1 systemd[1]: Starting MySQL Router...
注释：若是二进制安装启动的脚本为 myrouter/start.sh

端口检查：
# netstat -anpt |grep mysql


9.登录查看：
#mysql -h 192.168.0.151 -uroot -proot -P6446
可以读写操作
#mysql -h 192.168.0.151 -uroot -proot -P6447
只读操作。

# mysql -p -S /tmp/mysql8010.sock
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 57
Server version: 8.0.12 MySQL Community Server - GPL

Copyright (c) 2000, 2018, 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 host,user from mysql.user;
+-----------+----------------------------------+
| host      | user                             |
+-----------+----------------------------------+
| %         | mysql_innodb_cluster_r0429496332 |
| %         | mysql_innodb_cluster_r0429501613 |
| %         | mysql_innodb_cluster_r0429503138 |
| %         | mysql_innodb_cluster_r0429564188 |
| %         | mysql_router3_0c9aklqsov5w       |
| %         | root                             |
| localhost | mysql.infoschema                 |
| localhost | mysql.session                    |
| localhost | mysql.sys                        |
| localhost | mysql_innodb_cluster_r0429496332 |
| localhost | mysql_innodb_cluster_r0429501613 |
| localhost | mysql_innodb_cluster_r0429503138 |
| localhost | mysql_innodb_cluster_r0429564188 |
| localhost | mysql_innodb_cluster_r0430389165 |
| localhost | mysql_innodb_cluster_r0430437090 |
| localhost | root                             |
+-----------+----------------------------------+
16 rows in set (0.00 sec)

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 | 917b67ed-bb5c-11e8-8172-000c29e8fbcb | node1       |        8012 | ONLINE       | SECONDARY   | 8.0.12         |
| group_replication_applier | aa46fae7-bb4b-11e8-bdf0-000c29e8fbcb | node1       |        8010 | ONLINE       | PRIMARY     | 8.0.12         |
| group_replication_applier | c03dfcd2-bb4b-11e8-af98-000c29e8fbcb | node1       |        8011 | ONLINE       | SECONDARY   | 8.0.12         |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
3 rows in set (0.00 sec)

可以看到8010实例为主节点。



--部署过程中的报错信息：
报错信息1：
注意：
主机名要和hostname配置一致。否则报错：
 MySQL  node1:8010 ssl  JS > var cluster = dba.createCluster('InnoDBCluster')
A new InnoDB cluster will be created on instance 'root@node1:8010'.

Validating instance at node1:8010...

This instance reports its own address as node1.example.com

Instance configuration is suitable.
Creating InnoDB cluster 'InnoDBCluster' on 'root@node1:8010'...
Dba.createCluster: ERROR: An error was found trying to install the group_replication plugin: Query failed. MySQL Error (1126): ClassicSession.query: Can't open shared library '/lib/plugin/group_replication.so' (errno: 0 /lib/plugin/group_replication.so: cannot open shared object file: No such file or directory)
ERROR: Error starting cluster: The group_replication plugin could not be loaded in the server 'node1:8010': 'node1:8010' - Query failed. MySQL Error (1126): ClassicSession.query: Can't open shared library '/lib/plugin/group_replication.so' (errno: 0 /lib/plugin/group_replication.so: cannot open shared object file: No such file or directory). Query: INSTALL PLUGIN group_replication SONAME 'group_replication.so' (RuntimeError)

# cat /etc/hostname 
node1
# cat /etc/hosts
127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4
::1         localhost localhost.localdomain localhost6 localhost6.localdomain6
192.168.0.151 node1
这2个地方的配置信息要一致。


报错信息2：
 MySQL  node1:8010 ssl  JS >   cluster.addInstance('root@node1:8012')
A new instance will be added to the InnoDB cluster. Depending on the amount of
data on the cluster this might take from a few seconds to several hours.

Adding instance to the cluster ...

Validating instance at node1:8012...

This instance reports its own address as node1

Instance configuration is suitable.
Cluster.addInstance: ERROR: 
Group Replication join failed.
ERROR: Group Replication plugin failed to start. Server error log contains the following errors: 
 2018-09-18T15:57:32.610202Z 0 [ERROR] [MY-011526] [Repl] Plugin group_replication reported: 'This member has more executed transactions than those present in the group. Local transactions: d48ab83a-bb4b-11e8-8d2d-000c29e8fbcb:1-4 > Group transactions: 6320665a-bb5b-11e8-9d6c-000c29e8fbcb:1-16,
2018-09-18T15:57:32.610276Z 0 [ERROR] [MY-011522] [Repl] Plugin group_replication reported: 'The member contains transactions not present in the group. The member will now exit the group.'

ERROR: Error joining instance to cluster: 'node1:8012' - Query failed. MySQL Error (3092): ClassicSession.query: The server is not configured properly to be an active member of the group. Please see more details on error log.. Query: START group_replication: MySQL Error (3092): ClassicSession.query: The server is not configured properly to be an active member of the group. Please see more details on error log. (RuntimeError)

注意：创建用户需要先关闭binlog再打开，否则报错。
示例如下：
set sql_log_bin=0;  
create user root@'%' identified by 'root';
GRANT all on *.* TO root@'%' WITH GRANT OPTION;
GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP,RELOAD,SHUTDOWN,PROCESS,FILE,REFERENCES,INDEX,ALTER,SHOW DATABASES,SUPER,CREATE TEMPORARY TABLES,LOCK TABLES,EXECUTE,REPLICATION SLAVE,REPLICATION CLIENT,CREATE VIEW,SHOW VIEW,CREATE ROUTINE,ALTER ROUTINE,CREATE USER,EVENT,TRIGGER,CREATE TABLESPACE,CREATE ROLE,DROP ROLE ON *.* TO root@'%' WITH GRANT OPTION;
GRANT BACKUP_ADMIN,BINLOG_ADMIN,CONNECTION_ADMIN,ENCRYPTION_KEY_ADMIN,GROUP_REPLICATION_ADMIN,PERSIST_RO_VARIABLES_ADMIN,REPLICATION_SLAVE_ADMIN,RESOURCE_GROUP_ADMIN,RESOURCE_GROUP_USER,ROLE_ADMIN,SET_USER_ID,SYSTEM_VARIABLES_ADMIN,XA_RECOVER_ADMIN ON *.* TO root@'%' WITH GRANT OPTION;
flush privileges;
set sql_log_bin=1;


组复制的一些限制：
组复制的限制
事物锁缺失问题：
组复制建议，事物隔离级别，read commit
序列化隔离级别：多主模式不支持
并发DDL和DML: 多主模式下，不支持 一边对一个表进行DDL,另一边进行更新，这样对于DDL在其他实例上操作有未检出的风险
外键级联约束：多主模式下，多级外键依赖对引起多级操作， 因此可能导致未知冲突，建议打开 group_replication_enforce_update_everywhere_checks=ON
大事物，超过5秒未提交，会导致组通信失败，
多主模式下：select * for update 会导致 死锁。因为这个锁并非全组共享。
部分复制不支持：组复制下，设置部分复制，会过滤事物，导致组事物不一致。
Mysql 8.0.11 group_replication_enforce_update_everywhere_checks=ON 多主模式下不支持。
停止复制的情况下，某个节点执行命令后再启动，会因为本地有私有事物，无法加入集群。需要全局 reset master 重新开始集群复制。