The following deployment uses InnoDB Cluster. Each server instance runs MySQL Group Replication (i.e. redundant Replication mechanism with failover built in). The MGR has two modes, one is single-primary. One is multi-primary, which means single or multi-primary. Note: Multi-primary, where all nodes are Primary and can be read and written at the same time, seems to be better, but because of the complexity of multi-primary, there are some limitations to the use of multi-primary. For example, Foreign Keys with Cascading Constraints are not supported.
1 Environment Preparation
Four servers of Centos7 version are prepared to deploy innodb Cluster multi-node cluster environment (at least three servers are required), among which:
1) Host-192-169-106-11, host-192-169-106-12, host-192-169-106-13 as cluster node server, 2) host-192-169-106-11 as the management node server, which is responsible for creating the cluster, As a route of cluster, this node needs to be installed with mysql-shell and mysql-router 3) The Python version of all nodes must be later than 2.7Copy the codeIP address Host name Role Software 192.169.106.11 Host-192-169-106-11 Management node 1+ Cluster node 1 Mysql5.7, mysql-shell, Mysql -route 192.169.106.12 host-192-169-106-12 cluster node 2 Mysql5.7, Mysql -shell 192.169.106.13 Host-192-169-106-13 cluster node 3Copy the code[root@host-192-169-106-11 ~]# cat /etc/redhat-release
CentOS Linux release 7.5.1804 (Core)
[root@host-192-169-106-11 ~]# python -VPython 2.7.5 Configuring the /etc/hosts host mapping for each node to facilitate connection through the hostname of the node this step is important. Otherwise, synchronization may not be possible because the database needs to be synchronized according to member_host. The default is localhost, so you can't communicate!! . 192.169.106.11 Host-192-169-106-11 192.169.106.12 Host-192-169-106-12 192.169.106.13 Host-192-169-106-13 Perform the following optimization configuration on all nodes [root@host-192-169-106-11 ~]# 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 [root@host-192-169-106-11 ~]# sysctl -p
[root@host-192-169-106-11 ~]# cat>>/etc/security/limits.conf <<EOF
> mysql soft nproc 65536
> mysql hard nproc 65536
> mysql soft nofile 65536
> mysql hard nofile 65536
> EOF
[root@host-192-169-106-11 ~]# cat>>/etc/pam.d/login <<EOF
> session required /lib/security/pam_limits.so
> session required pam_limits.so
> EOF
[root@host-192-169-106-11 ~]# cat>>/etc/profile<<EOF
> if [ $USER = "mysql" ]; then
> ulimit -u 16384 -n 65536
> fi
> EOF
[root@host-192-169-106-11 ~]# source /etc/profile
Copy the code2. Install mysql shell and mysql-route on the management node
[root@host-192-169-106-11 src]# ll21648-rw-r --r-- 1 root root 15526654 8月 28 09:52 mysql-router-2.1.4-linux-glibc2.12-x86-64bit. Tar. gz-rw-r --r-- 1 Root root 6635831 8月 28 09:52 mysql-shell-1.0.9-linux-glibc2.12-x86-64bit. Tar. gz [root@host-192-169-106-11 SRC]# tar - ZVXF mysql - the shell - 1.0.9 - Linux - glibc2.12 - x86-64 - bit. Tar. Gz
[root@host-192-169-106-11 src]# tar - ZVXF mysql - the router - 2.1.4 - Linux - glibc2.12 - x86-64 - bit. Tar. Gz
[root@host-192-169-106-11 src]# mysql - the router - mv 2.1.4 - Linux - glibc2.12 - x86-64 - bit mysql - the router
[root@host-192-169-106-11 src]# mv mysql - the shell - 1.0.9 - Linux - glibc2.12 - x86-64 - bit mysql - shell
[root@host-192-169-106-11 src]# mv mysql-router /usr/local/
[root@host-192-169-106-11 src]# mv mysql-shell /usr/local/
[root@host-192-169-106-11 local]# vim /etc/profile.export PATH=$PATH:/usr/local/mysql-shell/bin/:/usr/local/mysql-route/bin/
[root@host-192-169-106-11 local]# source /etc/profile
[root@host-192-169-106-11 ~]# mysqlprovision --versionMysqlprovision version 2.0.0 [root@host-192-169-106-11 ~]# mysqlsh --version
MySQL Shell Version 1.0.9
[root@host-192-169-106-11 ~]# mysqlrouter --version
MySQL Router v2.1.4 on Linux (64-bit) (GPL community edition)
Copy the code3. Install and deploy Mysql5.7 and mysql-shell on three cluster nodes
3.1 Installing mysql-shell (Same operation for three Nodes)
[root@host-192-169-106-11 ~]# cd /usr/local/src/
[root@host-192-169-106-11 src]# ll mysql - the shell - 1.0.9 - Linux - glibc2.12 - x86-64 - bit. Tar. Gz-rw-r--r-- 1 root root 6635831 Mar 22 2017 mysql-shell-1.0.9-linux-glibc2.12-x86-64bit. Tar. gz [root@host-192-169-106-11 src]# tar - ZVXF mysql - the shell - 1.0.9 - Linux - glibc2.12 - x86-64 - bit. Tar. Gz
[root@host-192-169-106-11 src]# mv mysql - the shell - 1.0.9 - Linux - glibc2.12 - x86-64 - bit mysql - shell
[root@host-192-169-106-11 src]# mv mysql-shell /usr/local/
[root@host-192-169-106-11 src]# echo "export PATH=$PATH:/usr/local/mysql-shell/bin/" >> /etc/profile
[root@host-192-169-106-11 src]# source /etc/profile
[root@host-192-169-106-11 ~]# mysqlprovision --version Mysqlprovision version 2.0.0 [root@host-192-169-106-11 ~]# mysqlsh --version
MySQL Shell Version 1.0.9
Copy the code3.2 Installing mysql5.7 (Do the same for three nodes)
1) Uninstall mysql and mariadb-lib [root@host-192-169-106-11 SRC]# /bin/rpm -e $(/bin/rpm -qa | grep mysql|xargs) --nodeps
[root@host-192-169-106-11 src]# /bin/rpm -e $(/bin/rpm -qa | grep mariadb|xargs) --nodeps2) download mysql5.7.21 RPM installation package download address: http://ftp.ntu.edu.tw/MySQL/Downloads/MySQL-5.7/ [root @ host - 192-169-106-11 SRC]# tar - VXF mysql - 5.7.27-1. El7. X86_64. RPM - bundle. The tar
[root@host-192-169-106-11 src]# ll1 root root 5310556640 8月 28 12:05 mysql-5.7.27-1.el7.x86_64. RPM -bundle.tar -rw-r--r-- 1 7155 31415 25365436 6月 12 14:42 mysql-community-client-5.7.27-1.el7.x86_64. RPM -rw-r--r-- 1 7155 31415 281248 6月 12 14:42 Mysql -community-common-5.7.27-1.el7.x86_64. RPM -rw-r--r-- 1 7155 31415 3833396 6月 12 14:42 RPM -rw-r--r-- 1 7155 31415 47074656 6月 12 14:42 RPM -rw-r--r-- 1 7155 31415 24079736 6月 12 14:42 RPM -rw-r--r--. 1 7155 31415 129991352 6月 12 14:42 RPM -rw-r--r-- 1 7155 31415 2272032 6月 12 14:42 Mysql -community-libs-5.7.27-1.el7.x86_64. RPM -rw-r--r-- 1 7155 31415 2116432 6月 12 14:42 RPM -rw-r--r-- 1 7155 31415 173500088 6月 12 14:43 RPM -rw-r--r-- 1 7155 31415 122530756 6月 12 14:43 RPM -rw-r--r-- 1 root root 15526654 8月 28 09:52 Mysql -router-2.1.4-linux-glibc2.12-x86-64bit. Tar. gz -rw-r--r-- 1 root root 6635831 August 28 09:52 Mysql > install mysql-shell-1.0.9-linux-glibc2.12-x86-64bit. Tar. gz mysql > install mysql-shell-1.0.9-linux-glibc2.12-x86-64bit.RPM --force # RPM -ivh mysql-community-common-5.7.27-1.el7.x86_64
[root@host-192-169-106-11 src]# RPM -ivh mysql-community-libs-5.7.27-1.el7.x86_64. RPM --force # RPM -ivh mysql-community-libs-5.7.27-1.el7.x86_64
[root@host-192-169-106-11 src]RPM --force # RPM -ivh mysql-community-client-5.7.27-1.el7.x86_64
[root@host-192-169-106-11 src]RPM --force # RPM -ivh mysql-community-server-5.7.27-1.el7.x86_64= = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = [root@kevin ~] : [root@kevin ~] : [root@kevin ~] : [root@kevin ~] : [root@kevin ~] : [root@kevin ~]RPM --force # RPM -ivh mysql-community-server-5.7.21-1.el7.x86_64Warning: mysql-community-server-5.7.27-1.el7.x86_64. RPM: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY error: Failed dependencies: Libaio.so.1()(64bit) is needed by mysql-community-server-5.7.27-1.el7.x86_64 libaio.so.1(libaio_0.1)(64bit) is needed by Mysql -community-server-5.7.27-1.el7.x86_64 libaio.so.1(libaio_0.4)(64bit) is needed by Ql-community-server-5.7.27-1.el7.x86_64 Net-tools is needed by ql-community-server-5.7.27-1.el7.x86_64 Install libaio-0.3.107-10.el6.x86_64. RPM [root@host-192-169-106-11 SRC]Wget # http://mirror.centos.org/centos/6/os/x86_64/Packages/libaio-0.3.107-10.el6.x86_64.rpm
[root@host-192-169-106-11 src]# RPM -ivh libaio-0.3.107-10.el6.x86_64. RPM --forceInstall Net-Tools [root@host-192-169-106-11 SRC]# yum install net-tools = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = RPM is used to install mysql. The installation path is as follows: Database directory /var/lib/mysql.configuration file /usr/share/mysql(mysql.server command and configuration file) /etc/my. CNF command /usr/bin(mysqladmin mysqldump command and other commands) Mysql > initialize mysql database /etc/rc.d/init.d/ To initialize, run the following command [root@host-192-169-106-11 SRC]# mysql_install_db --datadir=/var/lib/mysql // Datadir must be specified, the ~/. Mysql_secret password file will be generated
[root@host-192-169-106-11 src]# mysqld --initialize --user= mysqld -- mysqld -- mysqld -- mysqld -- mysqld -- mysqld -- mysqld -- mysqld -- mysqld -- mysqld -- mysqld If you are running as mysql, you can remove the --user option.4) Change the user and group of the mysql database directory and start the mysql database [root@host-192-169-106-11 SRC]# chown mysql:mysql /var/lib/mysql -R
[root@host-192-169-106-11 src]# systemctl start mysqld.service
[root@host-192-169-106-11 src]# systemctl enable mysqld.serviceMysql > initialize; mysql > initialize; mysql > initialize; mysql > initialize; It will generate a password for root and mark it as expired, and you'll need to set a new password once you're logged in. Using --initialize-insecure will not generate a password for root if you're not insecure mode. Initialize generates a root password in thelogAt the end of the file is the following"F; HNq*thK2hb"Is the generated root password [root@host-192-169-106-11 SRC]# cat /var/log/mysqld.log|grep 'A temporary password'. T05:57:021884z 1 [Note] A temporary password is generatedforroot@localhost: F; HNq*thK2hb [root@kevin ~]# mysql -uroot -p'F; HNq*thK2hb'
mysql> set password=password('sagis@123');
mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@The '%' IDENTIFIED BY 'sagis@123' WITH GRANT OPTION;
mysql> flush privileges;
Copy the code3.3 configure my CNF
CNF of host-192-169-106-11 [root@host-192-169-106-11 ~]# cp /etc/my.cnf /etc/my.cnf.bak
[root@host-192-169-106-11 ~]# >/etc/my.cnf
[root@host-192-169-106-11 ~]# vim /etc/my.cnf
[mysqld]
datadir = /var/lib/mysql
socket = /var/lib/mysql/mysql.sock
port=23306
symbolic-links = 0
log-error = /var/log/mysqld.log
pid-file = /var/run/mysqld/mysqld.pid
# Copy framework
server_id=2
gtid_mode=ON
enforce_gtid_consistency=ON
binlog_checksum=NONE
log_bin=binlog
log_slave_updates=ON
binlog_format=ROW
master_info_repository=TABLE
relay_log_info_repository=TABLE
Group replication Settings
The server must collect the write set for each transaction and encode it as a hash using the XXHASH64 hash algorithm
transaction_write_set_extraction=XXHASH64
# tell plugin to join or create group name, UUID
loose-group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"
Set to OFF to avoid automatically booting a second group with the same name each time the server is started.
loose-group_replication_start_on_boot=off
# tell the plugin to use the IP address and port 24901 to receive incoming connections from other members of the group
loose-group_replication_local_address="192.169.106.12:24902"
Start group server, seed server, join group should connect these IP and port; Other servers can be added to the group only with the consent of the group members
loose-group_replication_group_seeds="192.169.106.11:24901192169 106.12:24902192169 106.13:24903"
loose-group_replication_bootstrap_group=off
loose-group_replication_ip_whitelist="192.169.106.13 192.169.106.11, 192.169.106.12,"
# Use MGR's single master modeloose-group_replication_single_primary_mode = on disabled_storage_engines = MyISAM,BLACKHOLE,FEDERATED,CSV,ARCHIVE Report_port =23306 After the previous configuration, copy the /etc/my. CNF file of db-node01 to the other two nodes [root@host-192-169-106-11 ~]# rsync-e "ssh-p60202" -avpgolr /etc/my.cnf [email protected]:/etc/
[root@host-192-169-106-11 ~]# rsync-e "ssh-p60203" -avpgolr /etc/my.cnf [email protected]:/etc/The three cluster nodes have the same parameters except server_id and loose-group_replication_local_address. So when the copy is done, After modifying parameters server_id and loose-group_replication_local_address of the /etc/my. CNF file on host-192-169-106-12 and host-192-169-106-13 respectively, To restart the databases of the three nodes in turn, install the MGR plug-in and set up the replication account (for all MGR nodes) [root@host-192-169-106-11 ~]# systemctl restart mysqld
Copy the code4. Create Innodb Cluster
4.1 Create cluster on host-192-169-106-11; use shell on host-192-169-106-11 to connect to mysql
[root@host-192-169-106-11 ~]# mysqlshWelcome to MySQL Shell 1.0.9...................The password is also required to execute the configuration command
# and then need to input the MySQL configuration file path, this example, the path is/usr/local/data/s1 / s1. The CNF
Select * from user root where user root is authorized
mysql-js> shell.connect('[email protected]:13306')
Creating a session to '[email protected]:13306'
Please provide the password for '[email protected]:13306': *********
Save password for '[email protected]:13306'? [Y]es/[N]o/Ne[v]er (default No): yes
Fetching schema names for autocompletion... Press ^C to stop.
Your MySQL connection id is 1424
Server version: 5.7.27-log MySQL Community Server (GPL)
No default schema selected; type \use <schema> to setOne. < 13306 > ClassicSession: [email protected]: MySQL 192.169.106.11: JS > 13306 dba. ConfigureLocalInstance (); Configuringlocal MySQL instance listening at port 13306 for use in an InnoDB cluster...
This instance reports its own address as host-192-169-106-11:13306
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.
The instance '192.169.106.11:13306' is valid for InnoDB cluster usage.
The instance '192.169.106.11:13306' is already ready for InnoDB cluster usage.
Create a cluster and name it 'myCluster'MySQL 192.169.106.11:13306 JS > var cluster = dba.createcluster ('myCluster');
A new InnoDB cluster will be created on instance '192.169.106.11:13306'Validating instance at 192.169.106.11:13306... This instance reports its own address as host-192-169-106-11:13306 Adding Seed Instance... Cluster successfully created. Use Cluster.addInstance() to add MySQL instances. At least 3 instances are neededforthe cluster to be able to withstand up to one server failure. If the preceding information is created successfully, a statement similar to Cluster successfully created is displayedCheck the cluster status
mysql-js> cluster.status();
{
"clusterName": "myCluster"."defaultReplicaSet": {
"name": "default"."primary": "192.169.106.11:3306"."ssl": "DISABLED"."status": "OK"."statusText": "Cluster is ONLINE and can tolerate up to ONE failure."."topology": {
"192.169.106.11:3306": {
"address": "192.169.106.11:3306"."mode": "R/W"."readReplicas": {},
"role": "HA"."status": "ONLINE"
}
}
}
mysql-js> dba.getCluster();
<Cluster:myCluster>
Copy the code4.2 Adding node Host-192-169-106-12 to cluster myCluster
Host-192-169-106-12 local mysql-shell [root@host-192-169-106-12 ~]# mysqlsh. mysql-js> shell.connect('[email protected]:3306')
Creating a session to '[email protected]:3306'
Please provide the password for '[email protected]:3306': *********
Save password for '[email protected]:3306'? [Y]es/[N]o/Ne[v]er (default No): yes
Fetching schema names for autocompletion... Press ^C to stop.
Your MySQL connection id is 1424
Server version: 5.7.27-log MySQL Community Server (GPL)
No default schema selected; type \use <schema> to setOne. < 3306 > ClassicSession: [email protected]: MySQL 192.169.106.12: JS > 3306 dba. ConfigureLocalInstance (); Configuringlocal MySQL instance listening at port 3306 for use in an InnoDB cluster...
This instance reports its own address as host-192-169-106-12:3306
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.
The instance '192.169.106.12:3306' is valid for InnoDB cluster usage.
The instance '192.169.106.12:3306' is already ready forInnoDB cluster Usage. Then modify my.cnf, add configuration item: [root@host-192-169-106-12 ~]# vim /etc/my.cnf. Loose-group_replication_allow_local_disjoint_gtids_join =on Restart mysql service [root@host-192-169-106-12 ~]# systemctl restart mysqldThen add 192.169.106.12 to the mysql-shell of host-192-169-106-11"myCluster"Mysql-js > cluster.addinstance (host = 192-169-106-11)'[email protected]: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 '[email protected]:3306':
Adding instance to the cluster ...
The instance '[email protected]:3306'was successfully added to the cluster. The preceding information indicates that node 192.169.106.12 has been added successfully"myCluster"Mysql-js > cluster.status(); {"clusterName": "myCluster"."defaultReplicaSet": {
"name": "default"."primary": "192.169.106.11:3306"."ssl": "DISABLED"."status": "OK"."statusText": "Cluster is ONLINE and can tolerate up to ONE failure."."topology": {
"192.169.106.11:3306": {
"address": "192.169.106.11:3306"."mode": "R/W"."readReplicas": {},
"role": "HA"."status": "ONLINE"
},
"192.169.106.12:3306": {
"address": "192.169.106.12:3306"."mode": "R/O"."readReplicas": {},
"role": "HA"."status": "ONLINE"}}}} Also, after the above operation, the mysql-shell terminal window of 192.169.106.11 node should not be closed and remain in the cluster state, which will be used to add 192.169.106.13 node to the cluster (as mentioned in the following common commands).Copy the code4.3 Adding node Host-192-169-106-13 to cluster myCluster
Host-192-169-106-13 local mysql-shell [root@host-192-169-106-13 ~]# mysqlsh. mysql-js> shell.connect('[email protected]:3306')
Creating a session to '[email protected]:3306'
Please provide the password for '[email protected]:3306': *********
Save password for '[email protected]:3306'? [Y]es/[N]o/Ne[v]er (default No): yes
Fetching schema names for autocompletion... Press ^C to stop.
Your MySQL connection id is 1424
Server version: 5.7.27-log MySQL Community Server (GPL)
No default schema selected; type \use <schema> to setOne. < 3306 > ClassicSession: [email protected]: MySQL 192.169.106.13: JS > 3306 dba. ConfigureLocalInstance (); Configuringlocal MySQL instance listening at port 3306 for use in an InnoDB cluster...
This instance reports its own address as host-192-169-106-13:3306
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.
The instance '192.169.106.13:3306' is valid for InnoDB cluster usage.
The instance '192.169.106.13:3306' is already ready forInnoDB cluster Usage. Then modify my.cnf, add configuration item: [root@host-192-169-106-13 ~]# vim /etc/my.cnf. Loose-group_replication_allow_local_disjoint_gtids_join =on Restart mysql service [root@host-192-169-106-13 ~]# systemctl restart mysqldThen add 192.169.106.13 to the mysql-shell of host-192-169-106-11"myCluster"Mysql-js > cluster.addinstance (host = 192-169-106-11)'[email protected]: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 '[email protected]:3306':
Adding instance to the cluster ...
The instance '[email protected]:3306'was successfully added to the cluster. The preceding information indicates that node 192.169.106.13 has been added successfully"myCluster"Mysql-js > cluster.status(); {"clusterName": "myCluster"."defaultReplicaSet": {
"name": "default"."primary": "192.169.106.11:3306"."ssl": "DISABLED"."status": "OK"."statusText": "Cluster is ONLINE and can tolerate up to ONE failure."."topology": {
"192.169.106.11:3306": {
"address": "192.169.106.11:3306"."mode": "R/W"."readReplicas": {},
"role": "HA"."status": "ONLINE"
},
"192.169.106.12:3306": {
"address": "192.169.106.12:3306"."mode": "R/O"."readReplicas": {},
"role": "HA"."status": "ONLINE"
},
"192.169.106.13:3306": {
"address": "192.169.106.13:3306"."mode": "R/O"."readReplicas": {},
"role": "HA"."status": "ONLINE"}},"topologyMode": "Single-Primary"
},
"groupInformationSourceMember": "192.169.106.11:3306"} Based on cluster information, node 192.169.106.11 is the primary node and has R/W read/write permission. The other two nodes are secondary nodes and have R/O read-only permissionCopy the code5. Start the route of the management node
Go to the mysql-router installation directory on the 192.169.106.11 management node and configure and start the router [root@host-192-169-106-11 ~]/bin/ mysqlRouter --bootstrap [email protected]:3306 -d myrouter --user=root
Please enter MySQL password for root:
Bootstrapping MySQL Router instance at /root/myrouter...
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/write port
- Read/Only Connections: localhost:6447 Read-only port
X protocol connections to cluster 'myCluster': - Read/Write Connections: localhost:64460 - Read/Only Connections: [root@host-192-169-106-11 ~] localhost:64470 localhost:64470# ls /root/myrouter/
data log mysqlrouter.conf mysqlrouter.key run start.sh stop.sh
[root@host-192-169-106-11 ~]# cat/root/myrouter/mysqlrouter. Conf # can modify the configuration file, also can not modify the defaultBy default, after connecting to mysql through route, port 6446 can be read and write. Port 6447 can be read only. Then start mysqlroute [root@host-192-169-106-11 ~]# /root/myrouter/start.sh
PID 28505 written to /root/myrouter/mysqlrouter.pid
[root@host-192-169-106-11 ~]# ps -ef|grep myroute
[root@host-192-169-106-11 ~]# ps -ef|grep myroute
root 7827 7755 0 10:49 pts/0 00:00:00 grep --color=auto myroute
root 28505 1 0 03:55 ? 00:00:55 /usr/local/mysql-router/bin/mysqlrouter -c /usr/local/mysql-router/myrouter/mysqlrouter.conf
[root@host-192-169-106-11 ~]# netstat -tunlp|grep 28505TCP 0 0 0.0.0.00:64460 0.0.0.0:* LISTEN 28505/ mysqlRouter TCP 0 0 0.0.0.00:6446 0.0.0.0:* LISTEN 28505/mysqlrouter TCP 0 0 0.0.0.00:6447 0.0.0.0:* LISTEN 28505/ mysqlRouter TCP 0 0 0.0.0.00:64470 0.0.0.0:* LISTEN 28505/ mysqlRouter This allows you to connect to the router using the MySQL client. A) Management node local mysql-shell connection: [root@host-192-169-106-11 ~]# mysqlsh --uri root@localhost:6446B) Management node local mysql connection: [root@host-192-169-106-11 ~]# mysql -u root -h 127.0.0.1 -P 6446 -pC) Connect to mysql by route [root@host-192-169-106-11 ~]# mysql -u root -h 192.169.106.11 -P 6446 -pExample Test cluster node data synchronization. Select host-192-169-106-13 as remote client to connect router [root@host-192-169-106-13 ~]# mysql -u root -h 192.169.106.13 -P 6446 -pEnter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1054 Server version: 5.7.25- Log MySQL Community Server (GPL) Copyright (C) 2000, 2019, 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> show databases;
+-------------------------------+
| Database |
+-------------------------------+
| information_schema |
| mysql |
| mysql_innodb_cluster_metadata |
| performance_schema |
| sys |
+-------------------------------+
5 rows in set(0.00 SEC) Mysql > CREATE DATABASEtest CHARACTER SET utf8 COLLATE utf8_general_ci;
ERROR 1044 (42000): Access denied for user 'root'@The '%' to database 'test'This is because'root@%'Mysql > select host,user from mysql.user; +-----------+----------------------------------+ | host | user | +-----------+----------------------------------+ | % | mysql_innodb_cluster_rp496261783 | | % | mysql_innodb_cluster_rp496457975 | | % | mysql_innodb_cluster_rp496569258 | | % | mysql_innodb_cluster_rp496629685 | | % | mysql_router1_olzau3ltjqzx | | % | root | | localhost | mysql.session | | localhost | mysql.sys | | localhost | root | +-----------+----------------------------------+ 9 rowsin set (0.00 sec)
mysql> show grants for root@The '%'; +----------------------------------------------------------------------------------------------------------------------- ------------------+ | Grantsforroot@% | +----------------------------------------------------------------------------------------------------------------------- ------------------+ | GRANT RELOAD, SHUTDOWN, PROCESS, FILE, SUPER, REPLICATION SLAVE, REPLICATION CLIENT, CREATE USER ON *.* TO'root'@The '%' WITH GRANT OPTION |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `mysql`.* TO 'root'@The '%' WITH GRANT OPTION |
| GRANT SELECT ON `performance_schema`.* TO 'root'@The '%' WITH GRANT OPTION |
| GRANT ALL PRIVILEGES ON `mysql_innodb_cluster_metadata`.* TO 'root'@The '%'WITH GRANT OPTION | +----------------------------------------------------------------------------------------------------------------------- ------------------+ 4 rowsin set(0.00 SEC) Log in to the master database and create a user with administrative rights [root@host-192-169-106-11 ~]# mysql -psagis@123. mysql>set global validate_password_policy=0;
Query OK, 0 rows affected (0.00 sec)
mysql> setglobal validate_password_length=1; Query OK, 0 rows affected (0.00 SEC) mysql> grant all on *.* to HJH @The '%' identified by "hjh@123"with grant option; Query OK, 0 rows affected, 1 warning (0.05 SEC) Then remotely log in to the router using the new account created above [root@host-192-169-106-13 ~]# mysql -u bobo -h 192.169.106.13 -P 6446 -p. mysql> show grantsfor hjh@The '%';
+-------------------------------------------------------------+
| Grants for hjh@% |
+-------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'hjh'@The '%' WITH GRANT OPTION |
+-------------------------------------------------------------+
1 row in set(0.00 SEC) Test test librarytest
mysql> CREATE DATABASE test CHARACTER SET utf8 COLLATE utf8_general_ci;
Query OK, 1 row affected (0.06 sec)
mysql> use test;
Database changed
mysql> create table if not exists test(id int(10) PRIMARY KEY AUTO_INCREMENT,name varchar(50) NOT NULL); Query OK, 0 rows affected (0.22 SEC) mysql> insert into Kevin. Haha values(1,"hejianhui");
Query OK, 1 rows affected (0.13 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> select * from test.test;
+----+-----------+
| id | name |
+----+-----------+
| 1 | hejianhui |
+----+-----------+
4 rows in set(0.00 SEC) Log in to the mysql database on each of the three cluster nodes to discover the test librarytestSynchronization has been completed. Data for write operations is written to nodes 192.169.106.11 and then synchronized to read-only nodes 192.169.106.12 and 192.169.106.13. Note: The route connected to port 6446 is available for read and write operations. However, after connecting with port 6447, only read-only operation can be performed. It can be executed after login" select @@hostname"Check which node to log in to. [root@host-192-169-106-13 ~]# mysql -u hjh -h 192.169.106.13 -P 6447 -p. mysql> select * from kevin.haha; +----+-----------+ | id | name | +----+-----------+ | 1 | hejianhui | +----+-----------+ 1 rowsin set (0.00 sec)
mysql> delete from test.test where id=1;
ERROR 1290 (HY000): The MySQL server is running with the --super-read-only option so it cannot execute this statement
Copy the codeIn addition, keepalived can be used to achieve high availability of InnoDB Cluster, that is, two DB-route management nodes, fail-aware switching through VIP resources. 3 cluster nodes (install mysql, mysql-shell), 2 route management nodes (install keepalived, mysql-shell, mysql-route, mysql-client)
6. InnoDB Cluster Daily maintenance commands
6.1 After configuring each node, you can check whether each node in the cluster is available before creating a cluster
dba.checkInstanceConfiguration("[email protected]:3306");
Copy the code6.2 For example, during Innodb Cluster creation, how can I obtain and check the cluster status if I log in to mysqlSH (remotely from a client or locally from any node) again
mysql-js> shell.connect("[email protected]:3306");
Please provide the password for '[email protected]:3306':
Creating a Session to '[email protected]:3306'Classic Session successfully established. No default schema selected. Mysql-js > cluster.status(); ReferenceError: cluster is not defined ReferenceError: cluster is not defined If the above method is used, an error will be reported that the cluster is not defined. In this case, you need to execute the following statement to check the cluster status!!!!! mysql-js> cluster.status(); ReferenceError: cluster is not defined mysql-js> cluster= db.getcluster (); <Cluster:myCluster> mysql-js> cluster.status(); MySQL 172.16.60.214:6446 SSL JS > dba.getcluster (); < Cluster: myCluster > = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = : a) the dba. GetCluster ();# View the created cluster
b) cluster=dba.getCluster(); Get the current cluster
c) cluster.status(); # Check cluster status
Copy the code6.3 InnoDB Cluster Command help for Cluster maintenance
dba.help();
Copy the code6.4 Important commands for daily use (mysqlsh JS syntax)
dba.checkInstanceConfiguration("root@hostname:3306") Check the node configuration instance before joining the cluster
dba.rebootClusterFromCompleteOutage('myCluster'); # to restart
dba.dropMetadataSchema(); # delete schema
var cluster = dba.getCluster('myCluster') Get the current cluster
cluster.checkInstanceState("root@hostname:3306") Check the node status in the cluster
cluster.rejoinInstance("root@hostname:3306") # rejoin the node. When I test it locally, I find that rejoin is invalid all the timeAddcluster. Dissolve ({force:true}) # delete cluster
cluster.addInstance("root@hostname:3306") # add node
cluster.removeInstance("root@hostname:3306") # delete node
cluster.removeInstance('root@host:3306',{force:true}) # Forcibly delete a node
cluster.dissolve({force:true}) # Disband the cluster
cluster.describe(); # Cluster descriptionCluster node status - ONLINE: The instance is ONLINE and participatingin the cluster.
- OFFLINE: The instance has lost connection to the other instances.
- RECOVERING: The instance is attempting to synchronize with the cluster by retrieving transactions it needs before it can become an ONLINE member.
- UNREACHABLE: The instance has lost communication with the cluster.
- ERROR: The instance has encountered an error during the recovery phase or while applying a transaction
Copy the code