InnoDBCluster \color{green}{InnoDBCluster Cluster introduction}InnoDBCluster introduction from the original official document
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.
InnoDB Cluster is a highly available solution for MySQL, including MySQL, MySQL shell and MySQL Router. It provides read/write separation, backup and disaster recovery, etc. For details, please refer to the official document InnoDB Cluster.
- Mysql – 5.7.21 – Linux – glibc2.12 – x86_64. Tar. Gz
Configure user groups and create log folders
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 logCopy the codeModify the configuration file vi /etc/my. CNF (The server_id of each cluster node must be unique)
[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 = MINIMALCopy the codeInitializing the database
./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
Copy the codeNote: initialize database, use the cat/usr/local/mysql/log/mysqld log to get the initial password: : 2018-04-19T07:35:20.481280z 1 [Note] A temporary password is generated for root@localhost: Qp, el.ypx24p
Add mysql environment variables at the bottom of /etc/profile and change the database password
export MYSQL_HOME=/usr/local/mysql
export PATH=${MYSQL_HOME}/bin:$PATH
Copy the code- Mysql – the shell – 1.0.11-1. El7. X86_64. RPM
RPM - the ivh mysql - the shell - 1.0.11-1. El7. X86_64. RPMCopy the code- Mysql – the router – 2.1.6-1. El7. X86_64. RPM
RPM - the ivh mysql - the router - 2.1.6-1. El7. X86_64. RPMCopy the codeCluster building \ color {green} {cluster set up} cluster structures, use the three set up a mysql instance InnoDB cluster of xx. Xx. Xx. 01:33 06 x.. X x x x. 02:33 06 x.. X x x x. 03:33 06
- Check whether the status of each node meets the requirements
[. Root @ f x x x x x x. 01 ~] # mysqlsh mysql - js > dba. CheckInstanceConfiguration (" root @ xx. Xx. Xx. 01:33 06 ") both Please dojo.provide the Password for '××.××.××.01:33 ': Validating Instance... The instance '××.××.××.01:3306' is valid for Cluster usage {"status": "OK"}Copy the codeRepeat the above for the other two instances.
- Create the cluster
[. Root @ x x x x x x. 01 ~] # mysqlsh mysql - js > shell. Connect (' root @ xx. Xx. Xx. 01:33 06 ') mysql - js > var cluster = dba.createCluster('myCluster')Copy the codeAdd instance
Mysql - js > cluster. AddInstance (' root @ xx. Xx. Xx. 02:33 06) mysql - js > cluster. The addInstance (' root @ f x. Xx. Xx. 03:33 06 ')Copy the codeViewing Cluster Status
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" : "... X x x x x x 01:33 06 ", "mode" : "R/W", "readReplicas" : {}, "role" : "HA", "status" : "ONLINE"}, "forming87:3306" : {" address ":"... X x x x x x 02:33 06 ", "mode" : "R/O", "readReplicas" : {}, "role" : "HA," "status" : "ONLINE"}, "forming92:3306" : {" address ":"... X x x x x x 03:33 06 ", "mode" : "R/O", "readReplicas" : {}, "role": "HA", "status": "ONLINE" } } } }Copy the code- Configure the MySQL Router
[root@ * *. * *. * *.01~]# mysqlRouter --bootstrap root@ * *. * *. * * 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 anyway: spreads over gse-backed loans up to/etc/mysqlrouter/mysqlrouter. Conf. Bak [root @ xx. Xx. Xx. 01 ~] # systemctl start mysqlrouter.serviceCopy the code- The test link
#mysql -u root -h ××.××.××.01 -p 6446 -p
- Matters needing attention
- Each table must have a primary key;
- If the following error occurs during cluster establishment
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 (RuntimeErrCopy the codeThe following measures can be taken
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;Copy the code- Cluster commands
Var cluster = dba.getCluster('iCluster') // Rejoin the cluster.rejoinInstance("root@hostname:3306") // Check the node configuration instance, Prior to joining is used to cluster the dba. CheckInstanceConfiguration (" root @ the hostname: 3306 ") / / node in the cluster state Cluster. CheckInstanceState (" root @ the hostname: 3306 ") / / delete cluster. The cluster dissolve ({force: True}) // Delete cluster.removeInstance("root@hostname:3306") // Forcibly delete a node Cluster.removeinstance ('root@hostname:3306',{force:true}) // Add node cluster.addinstance ("root@hostname:3306")Copy the code- Cluster and node status
Cluster status OK: All nodes are online and redundant nodes exist. OK_PARTIAL: a node is unavailable but there are still redundant nodes. OK_NO_TOLERANCE: there are enough online nodes, but no redundancy, for example: a Cluster of two nodes, if one of them dies, the Cluster becomes unavailable; NO_QUORUM: a node is online, but the number of nodes is less than the statutory number. In this state, the Cluster cannot be written, but can only be read. UNKNOWN: is not the online or recovering state, and attempts to connect other instances to check the state; UNAVAILABLE: All nodes in the group are in offline state, but the instance is running. The instance may have just been restarted and has not been added to the Cluster.
Node status ONLINE: The node status is normal. OFFLINE: The instance is running but has not been added to any Cluster. RECOVERING: Instance joins Cluster and synchronizing data; ERROR: Data synchronization is abnormal. UNREACHABLE: Communication with other nodes is interrupted, which may be a network problem or a node crash. MISSING: A node is added to the cluster, but Group Replication is not started.