https://dev.mysql.com/doc/refman/5.7/en/mysql-innodb-cluster-working-with-production-deployment.html
https://dev.mysql.com/doc/refman/5.7/en/mysql-innodb-cluster-limitations.html
install (3 nodes)
yum install https://dev.mysql.com/get/mysql57-community-release-el7-11.noarch.rpm -y
yum install mysql-community-server -y
yum install mysql-shell -y
yum install mysql-router -y
[xzx@cloud4ourself-kube1 ~]$ mysql --version
mysql Ver 14.14 Distrib 5.7.18, for Linux (x86_64) using EditLine wrapper
[xzx@cloud4ourself-kube1 ~]$ mysqlrouter --version
MySQL Router v2.1.3 on Linux (64-bit) (GPL community edition)
[xzx@cloud4ourself-kube1 ~]$ mysqlsh --version
MySQL Shell Version 1.0.9
configure (3 nodes)
service mysqld start
grep 'temporary password' /var/log/mysqld.log
mysql -uroot -p
mysql> set global validate_password_policy = 0;
mysql> set password=PASSWORD('myrootpwd');
mysql> set global validate_password_policy = 0;
CREATE USER myuser IDENTIFIED BY 'new_password';
GRANT RELOAD, SHUTDOWN, PROCESS, FILE, SUPER, REPLICATION SLAVE, REPLICATION CLIENT, CREATE USER ON *.* TO myuser;
GRANT SELECT ON performance_schema.* TO myuser;
GRANT SELECT, INSERT, UPDATE, DELETE ON mysql.* TO myuser;
GRANT ALL PRIVILEGES ON mysql_innodb_cluster_metadata.* TO myuser WITH GRANT OPTION;
GRANT REPLICATION SLAVE ON *.* TO myuser WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON *.* TO myuser;
/etc/my.cnf (3 nodes )
server_id = 1 # 2 , 3
enforce_gtid_consistency = ON
gtid_mode = ON
log_bin = 1
log_slave_updates = ON
master_info_repository = TABLE
relay_log_info_repository = TABLE
transaction_write_set_extraction = XXHASH64
binlog_checksum = NONE
validate_password_policy = 0
cluster
mysqlsh --log-level=DEBUG3
mysql-js> dba.checkInstanceConfiguration('myuser@10.9.5.91:3306')
mysql-js> dba.checkInstanceConfiguration('myuser@10.9.5.90:3306')
mysql-js> dba.checkInstanceConfiguration('myuser@10.9.5.71:3306')
mysql-js> shell.connect('myuser@10.9.5.91:3306')
mysql-js> var cluster = dba.createCluster('myCluster')
mysql-js> cluster.checkInstanceState('myuser@10.9.5.91:3306')
mysql-js> cluster.checkInstanceState('myuser@10.9.5.90:3306')
mysql-js> cluster.checkInstanceState('myuser@10.9.5.71:3306')
mysql-js> cluster.checkInstanceState('myuser@10.9.5.90:3306')
Please provide the password for 'myuser@10.9.5.90:3306':
Analyzing the instance replication state...
The instance '10.9.5.90:3306' is valid for the cluster.
The instance is new to Group Replication.
{
"reason": "new",
"state": "ok"
}
mysql-js> cluster.checkInstanceState('myuser@10.9.5.71:3306')
Please provide the password for 'myuser@10.9.5.71:3306':
Analyzing the instance replication state...
The instance '10.9.5.71:3306' is valid for the cluster.
The instance is new to Group Replication.
{
"reason": "new",
"state": "ok"
}
cluster.status()
cluster.addInstance('myuser@10.9.5.90:3306')
cluster.addInstance('myuser@10.9.5.71:3306')
mysql-js> cluster.status()
{
"clusterName": "myCluster",
"defaultReplicaSet": {
"name": "default",
"primary": "10.9.5.91:3306",
"status": "OK",
"statusText": "Cluster is ONLINE and can tolerate up to ONE failure.",
"topology": {
"10.9.5.71:3306": {
"address": "10.9.5.71:3306",
"mode": "R/O",
"readReplicas": {},
"role": "HA",
"status": "ONLINE"
},
"10.9.5.90:3306": {
"address": "10.9.5.90:3306",
"mode": "R/O",
"readReplicas": {},
"role": "HA",
"status": "ONLINE"
},
"10.9.5.91:3306": {
"address": "10.9.5.91:3306",
"mode": "R/W",
"readReplicas": {},
"role": "HA",
"status": "ONLINE"
}
}
}
}
mysql router
[root@cloud4ourself-kube1 ~]# mysqlrouter --bootstrap myuser@10.9.5.91:3306 --user=root
Please enter MySQL password for myuser:
Reconfiguring 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@cloud4ourself-kube1 ~]# mysqlrouter &
[1] 7524
error
mysql-js> cluster.addInstance('myuser@10.9.5.90:3306')
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.
Please provide the password for 'myuser@10.9.5.90:3306':
Adding instance to the cluster ...
Cluster.addInstance: ERROR:
Group Replication join failed.
ERROR: Group Replication plugin failed to start. Server error log contains the following errors:
2017-05-04T09:08:59.075925Z 0 [ERROR] Plugin group_replication reported: 'This member has more executed transactions than those present in the group. Local transactions: cff92742-3097-11e7-a452-fa163ecea498:1-2 > Group transactions: 7738d877-30a8-11e7-a7c6-fa163e72ab1f:1-17,
2017-05-04T09:08:59.075977Z 0 [ERROR] 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: '10.9.5.90@3306' - Query failed. 3092 (HY000): 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 (RuntimeError)
fix
mysql> set global group_replication_allow_local_disjoint_gtids_join=ON;
Query OK, 0 rows affected (0.01 sec)
貌似 cluster.rejoinInstance('myuser@10.9.5.71:3306') 此方法存在bug，其使用root去连目标数据库，因此建议还是使用root用户做操作。
test
- create table
mysql-js> \sql
Switching to SQL mode... Commands end with ;
mysql-sql> create database b;
Query OK, 1 row affected (0.08 sec)
mysql-sql> use b;
Query OK, 0 rows affected (0.00 sec)
mysql-sql> create table a (c int);
Query OK, 0 rows affected (0.29 sec)
mysql-sql> insert into a values(1);
ERROR: 3098 (HY000): The table does not comply with the requirements by an external plugin.
mysql-sql> alter table a add column a_id int(4);
Query OK, 0 rows affected (0.81 sec)
mysql-sql> alter table a add primary key pk (a_id);
Query OK, 0 rows affected (0.30 sec)
mysql-sql> insert into a values (1,2);
Query OK, 1 row affected (0.06 sec)
- check mysql slave nodes
mysql> select * from b.a;
+------+------+
| c | a_id |
+------+------+
| 1 | 2 |
+------+------+
1 row in set (0.00 sec)
- stop one of mysql slave nodes
[root@cloud4ourself-kube3 ~]# service mysqld stop
Redirecting to /bin/systemctl stop mysqld.service
- check cluster status
"topology": {
"10.9.5.71:3306": {
"address": "10.9.5.71:3306",
"mode": "R/O",
"readReplicas": {},
"role": "HA",
"status": "(MISSING)"
},
"10.9.5.90:3306": {
"address": "10.9.5.90:3306",
"mode": "R/O",
"readReplicas": {},
"role": "HA",
"status": "ONLINE"
},
"10.9.5.91:3306": {
"address": "10.9.5.91:3306",
"mode": "R/W",
"readReplicas": {},
"role": "HA",
"status": "ONLINE"
}
}
- insert one recode
mysql-sql> insert into a values (2,3);
Query OK, 1 row affected (0.05 sec)
# and the online slave node
mysql> select * from b.a;
+------+------+
| c | a_id |
+------+------+
| 1 | 2 |
| 2 | 3 |
+------+------+
2 rows in set (0.00 sec)
- start 10.9.5.71:3306 and check the table
mysql> select * from b.a;
+------+------+
| c | a_id |
+------+------+
| 1 | 2 |
+------+------+
1 row in set (0.00 sec)
- rejoininstance 10.9.5.71:3306
mysql-sql> \js
Switching to JavaScript mode...
mysql-js> cluster.rejoinInstance('root@10.9.5.71:3306')
Rejoining the instance to the InnoDB cluster. Depending on the original
problem that made the instance unavailable, the rejoin operation might not be
successful and further manual steps will be needed to fix the underlying
problem.
Please monitor the output of the rejoin operation and take necessary action if
the instance cannot rejoin.
Please provide the password for 'root@10.9.5.71:3306':
Rejoining instance to the cluster ...
The instance 'root@10.9.5.71:3306' was successfully rejoined on the cluster.
The instance '10.9.5.71:3306' was successfully added to the MySQL Cluster.
- check 10.9.5.71:3306 again
mysql> select * from b.a;
+------+------+
| c | a_id |
+------+------+
| 1 | 2 |
| 2 | 3 |
+------+------+
2 rows in set (0.00 sec)
