从做系统网站的收藏怎么找回/茶叶网络推广方案
在连接的实例上创建mysql.mysql_innodb_cluster_metadata存储元数据信息
验证配置信息
将此节点注册成seed节点
创建必要的管理账号
启动 Group Replication
mysql-js> \c root@10.186.23.95 #必须连接某个数据节点
Creating a Session to 'root@10.186.23.95'
Enter password:
Classic Session successfully established. No default schema selected.
mysql-js> dba.createCluster('mycluster')
A new InnoDB cluster will be created on instance 'root@10.186.23.95:3306'.
Creating InnoDB cluster 'mycluster' on 'root@10.186.23.95:3306'...
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-js> var cluster=dba.getCluster('mycluster')
mysql-js> cluster.addInstance('root@10.186.23.96: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 'root@10.186.23.96:3306':
Adding instance to the cluster ...
The instance 'root@10.186.23.96:3306' was successfully added to the cluster.
mysql-js> cluster.addInstance('root@10.186.23.94: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 'root@10.186.23.94:3306':
Adding instance to the cluster ...
The instance 'root@10.186.23.94:3306' was successfully added to the cluster.
mysql-js> cluster.status()
{
"clusterName": "mycluster",
"defaultReplicaSet": {
"name": "default",
"primary": "10.186.23.95:3306",
"status": "OK",
"statusText": "Cluster is ONLINE and can tolerate up to ONE failure.",
"topology": {
"10.186.23.94:3306": {
"address": "10.186.23.94:3306",
"mode": "R/O",
"readReplicas": {},
"role": "HA",
"status": "ONLINE"
},
"10.186.23.95:3306": {
"address": "10.186.23.95:3306",
"mode": "R/W",
"readReplicas": {},
"role": "HA",
"status": "ONLINE"
},
"10.186.23.96:3306": {
"address": "10.186.23.96:3306",
"mode": "R/O",
"readReplicas": {},
"role": "HA",
"status": "ONLINE"
}
}
}
}
安装配置MySQL Router
[root@10-186-23-97 ~]# yum install -y mysql-router
[root@10-186-23-97 ~]# mysqlrouter --bootstrap root@10.186.23.94:3306 --user=mysqlrouter
Please enter MySQL password for root:
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
[root@10-186-23-97 ~]# mysqlsh --uri root@localhost:6446
Creating a Session to 'root@localhost:6446'
Enter password:
Classic Session successfully established. No default schema selected.
Welcome to MySQL Shell 1.0.9
Copyright (c) 2016, 2017, 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', '\h' or '\?' for help, type '\quit' or '\q' to exit.
Currently in JavaScript mode. Use \sql to switch to SQL mode and execute queries.
mysql-js> \sql
Switching to SQL mode... Commands end with ;
mysql-sql> select @@hostname;
+----------------------------+
| @@hostname |
+----------------------------+
| 10-186-23-94.actionsky.com |
+----------------------------+
1 row in set (0.00 sec)
可能遇到的问题
节点gtid 异常
如果节点在加入集群前，执行了写操作，加入集群时会报错
Please provide the password for 'root@10.186.23.96:3306':
Adding instance to the cluster ...
Cluster.addInstance: 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: '10.186.23.96@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)
节点的错误日志中会出现
2017-05-09T06:49:57.301003Z 0 [ERROR] Plugin group_replication reported: 'This member has more executed transactions than those present in the group. Local transactions: 605da5eb-347d-11e7-b68b-bef8d5ac5be4:1,
cf2fe6ca-3460-11e7-aab3-bef8d5ac5be4:1-7 > Group transactions: 8399a91c-3483-11e7-b68b-bef8d5ac5be4:1-5,
cf2fe6ca-3460-11e7-aab3-bef8d5ac5be4:1-15'
解决办法，登录到此节点执行reset master
python版本异常
第一次安装时没有检查python版本，配置实例时出错，但未输出具体错误。
mysql-py> dba.configure_local_instance('root@localhost:3306')
Please provide the password for 'root@localhost:3306':
Detecting the configuration file...
Found configuration file at standard location: /etc/my.cnf
Do you want to modify this file? [Y|n]: Y
Validating instance...
The issues above can be fixed dynamically to get the server ready for InnoDB Cluster.
{
"errors": [],
"restart_required": false,
"status": "error"
}
后来通过查看mysqlsh的日志，发现错误原因
less ~/.mysqlsh/mysqlsh.log
...
2017-05-09 04:24:27: Error: DBA: mysqlprovision exited with error code (1) : ERROR: The __main__ gadget requires Python version 2.7.0 or higher and lower than 4.0.0. The version of Python detected was 2.6.6. You may need to install or redirect the execution of this utility to an environment that includes a compatible Python version.
...
重启节点后需要手动重新加入集群
mysql-js> var cluster = dba.getCluster()
mysql-js> cluster.status()
{
...
"10.186.23.96:3306": {
"address": "10.186.23.96:3306",
"mode": "R/O",
"readReplicas": {},
"role": "HA",
"status": "(MISSING)"
}
}
}
}
mysql-js> cluster.rejoinInstance('root@10.186.23.96:3306')
mysql-js> cluster.status()
{
...
"10.186.23.96:3306": {
"address": "10.186.23.96:3306",
"mode": "R/O",
"readReplicas": {},
"role": "HA",
"status": "ONLINE"
}
}
}
}
集群所有节点发生重启
当集群的所有节点都offline，直接获取集群信息失败，如何重新恢复集群
mysql-js> var cluster=dba.getCluster('mycluster')
Dba.getCluster: This function is not available through a session to a standalone instance (RuntimeError)
执行rebootClusterFromCompleteOutage命令，可恢复集群
mysql-js> dba.rebootClusterFromCompleteOutage('mycluster')
Reconfiguring the cluster 'mycluster' from complete outage...
The instance '10.186.23.96:3306' was part of the cluster configuration.
Would you like to rejoin it to the cluster? [y|N]: y
The instance '10.186.23.94:3306' was part of the cluster configuration.
Would you like to rejoin it to the cluster? [y|N]: y
The cluster was successfully rebooted.
脑裂场景
当集群中有部分节点出现UNREACHABLE状态，此时集群无法做出决策，，会出现以下局面，此时只剩下一个活跃节点，此节点只能提供查询，无法写入，执行写入操作会hang住。
修复这种状态，需要执行forceQuorumUsingPartitionOf指定当前活跃节点(如果是多个则选择primary node)，此时活跃节点可以提供读写操作，然后将其他节点加入此集群。
节点有哪状态
ONLINE - 节点状态正常。
OFFLINE - 实例在运行，但没有加入任何Cluster。
RECOVERING - 实例已加入Cluster，正在同步数据。
ERROR - 同步数据发生异常。
UNREACHABLE - 与其他节点通讯中断，可能是网络问题，可能是节点crash。
MISSING 节点已加入集群，但未启动group replication
集群有哪些状态
OK – 所有节点处于online状态，有冗余节点。
OK_PARTIAL – 有节点不可用，但仍有冗余节点。
OK_NO_TOLERANCE – 有足够的online节点，但没有冗余，例如：两个节点的Cluster，其中一个挂了，集群就不可用了。
NO_QUORUM – 有节点处于online状态，但达不到法定节点数，此状态下Cluster无法写入，只能读取。
UNKNOWN – 不是online或recovering状态，尝试连接其他实例查看状态。
UNAVAILABLE – 组内节点全是offline状态，但实例在运行，可能实例刚重启还没加入Cluster。
最后附一张集群状态图，restoreFromCompleteOutage在GA版本改成rebootClusterFromCompleteOutage。
请点击输入图片描述
请点击输入图片描述