MySQL 8.0.11 INNODB cluster Operations Management Handbook II--Cluster construction

MySQL 8.0.11 InnoDB cluster high Availability cluster Deployment Operations Management Handbook Two cluster construction author Fanglian basic Environment

System: CentOS 7.5
mysql:8.0.11 Binary Package
mysqlshell:8.0.11 RPM Package
Mysql router:8.0.11 Binary Package

Architecture:

192.168.181.101 myrouter1 keepalived, Mysql-shell, Mysql-router, mysql-client

192.168.181.102 Myrouter2 keepalived, Mysql-shell, Mysql-router, mysql-client

192.168.181.103 mysql3 MySQL Server, Mysql-shell

192.168.181.104 mysql4 MySQL Server, Mysql-shell

192.168.181.105 mysql5 MySQL Server, Mysql-shell

Preparation: Modify the/etc/hosts (5 nodes to do), and the host name is the same

192.168.181.101 MYSQL1
192.168.181.102 MYSQL2
192.168.181.103 Mysql3
192.168.181.104 MYSQL4
192.168.181.105 YSQL5

Package dependencies

Yum-y install gcc glibc libaio libstdc++ libstdc libncurses ld-linux

Firewall and SELinux shutdown:

(1), close SELinux
Setenforce 0
Modify/etc/selinux/config

                        vim /etc/selinux/config                        SELINUX=disabled

(2), shut down the firewall

                systemctl stop firewalld                        systemctl disable firewalld

Sysctl.conf Optimization:

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

Limit optimization:

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

cat>>/etc/pam.d/login <<EOFsession required /lib/security/pam_limits.sosession required pam_limits.soEOF

cat>>/etc/profile<<EOFif [ $USER = "mysql" ]; thenulimit -u 16384 -n 65536fiEOF

Source/etc/profile

Installing the MySQL Software (SQL node) environment variables

useradd mysqlcat >>/home/mysql/.bash_profile<<EOFexport PATH=/usr/local/mysql/bin:/usr/local/mysql/lib:$PATHEOF

Install MySQL software (SQL node):

cd /datatar -xzvf mysql-8.0.11-el7-x86_64.tar.gz  -C /usr/localmv /usr/local/mysql-8.0.11-el7-x86_64 /usr/local/mysqlchown -R  mysql.mysql  /usr/local/mysql

Initialize MySQL (one master node)

mkdir -p /data/mysql_3306_test/{data,log,binlog,conf,tmp}chown -R mysql.mysql /data/mysql_3306_test

Parameter file (self-optimizing)

Su–mysql
Vim/data/mysql_3306_test/conf/my.cnf

[mysqld]lower_case_table_names = 1user = mysqlserver_id = 1104 #各个实例全局唯一port = 3310enforce_gtid_consistency = Ongtid_mode = O                         Nbinlog_checksum = Nonedefault_authentication_plugin = Mysql_native_password #为了兼容以前的驱动 datadir =/data/mysql_3310_test/datapid-file =/data/mysql_3310_test/tmp/mysqld.pidsock ET =/data/mysql_3310_test/tmp/mysqld.socktmpdir =/data/mysql_3310_test /tmp/skip-name-resolve = Ontable_open_cache = 2000################ #innodb #################### # # # #innodb_data_home_dir =/data/mysql_3310_test/datainnodb_data_file_path = ibdata1:512m;ibdata2:512 M:autoextendinnodb_buffer_pool_size = 2000minnodb_flush_log_at_trx_commit = 1innodb_io_capacity = 600innodb_lock_ Wait_timeout = 120innodb_log_buffEr_size = 8minnodb_log_file_size = 200minnodb_log_files_in_group = 3innodb_max_dirty_pages_pct = 85innodb_read_io_ Threads = 8innodb_write_io_threads = 8innodb_thread_concurrency = 32innodb_file_per_tableinnodb_rollback_on_ Timeoutinnodb_undo_directory =/data/mysql_3310_test/datainnodb_log_group_home_dir =/data/mysql_3310_test /data################## #session ########################## #join_buffer_size = 8mkey_buffer_size = 256mbulk_insert_ Buffer_size = 8mmax_heap_table_size = 96mtmp_table_size = 96mread_buffer_size = 8msort_buffer_size = 2Mmax_allowed_ Packet = 64mread_rnd_buffer_size = 32m########### #log set################## #log-error =/data/mysql_                   3310_test/log/mysqld.errlog-bin =/data/mysql_3310_test/binlog/binloglog_bin_index =/data/mysql_3310_test/binlog/binlog.indexmax_binlog_size = 500mslow_query_log_file =/dat                  A/mysql_3310_test/log/slow.logslow_query_log= 1long_query_time = 10log_queries_not_using_indexes = Onlog_throttle_queries_not_using_indexes = 10log _slow_admin_statements = Onlog_output = File,tablemaster_info_file =/data/mysql _3310_test/binlog/master.info######################### #mgr set############################# #mysqlx_port = 33101 # To set, because the Group replication protocol is the port to go, multi-instance to adjust the Mysqlx_socket=/data/mysql_3310_test/tmp/mysqlx.sockloose-group_replication_group_ Name= "AAAAAAAA-AAAA-AAAA-AAAA-AAAAAAAAAAAA" Loose-group_replication_start_on_boot=offloose-group_replication_ local_address= "mysql4:33101" loose-group_replication_group_seeds= "mysql3:33101,mysql4:33101,mysql5:33101" loose-group_replication_bootstrap_group= offloose-group_replication_ip_whitelist= "192.168.181.0/24" Loose-group_ Replication_single_primary_mode = on #我们在次都要单主模式云因看后面loose-group_replication_auto_increment_increment=1 #这个默认是7, Our single main mode is set to 1

Single master mode and multi-master mode note

Multi-Master mode:
Loose-group_replication_single_primary_mode = Off

Operation Flow: Business-side Connection IP processing, group members, and then actively exit group (exit All), close the Group_replication_single_primary_mode parameter, Start the server in Group one by one

Set Global Group_replication_single_primary_mode=off

Initialize the deployment:

mysqld   --defaults-file=/data/mysql_3306_test/conf/my.cnf  --initialize-insecure  --user=mysqlmysqld   --defaults-file=/data/mysql_3310_test/conf/my.cnf  --initialize-insecure  --user=mysql

Set the password to start first:

mysqladmin  --defaults-file=my.cnf   password  跟密码进入数据库mysql -uroot -p -S /data/mysql_3306_test/tmp/mysqld.sock

Create a local user

Create user [email protected] ' 127.0.0.1 ' identified by ' password '; GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW Databa SES, 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 ' @ ' 12 7.0.0.1 ' with GRANT OPTION; Grant PROXY on "@" to "root" @ ' 127.0.0.1 ' 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 ' @ ' 127.0.0.1 ' with GRANT option;create user [email protected] ' 192.168.181.% ' identified by ' password '; GRANT all on * * to [email protected] ' 192.168.181.% ' with GRANT OPTION; GRANT SELECT, INSERT, UpdatE, DELETE, create, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, create Temporar Y TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, create VIEW, SHOW view, create ROUTINE, ALTER Routi NE, create USER, EVENT, TRIGGER, create tablespace, create role, DROP role on *. * to ' root ' @ ' 192.168.181.% ' with GRANT OPT ION; 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 [email protected] ' 192.168.181.% ' with GRANT OPTION;

Copy the instance to another node:

Close the main library node
Packaged
Passed
Extract

Installing the MySQL Shell route

RPM-IVH mysql-shell-8.0.11-1.el7.x86_64.rpm
RPM-IVH mysql-router-8.0.11-1.el7.x86_64

Configuring the Cluster

Using the MySQL command
(1), check and configure instances (per MySQL node)
Mysqlsh--log-level=debug3 Log Location ~/.mysqlsh/mysqlsh.log
Check instance

dba.configureLocalInstance(‘[email protected]:3306‘);dba.chekInstanceConfiguration(‘[email protected]:3306‘)

(2), create cluster cluster (confirm that each instance of SQL node completes the above instance configuration and verify success)
Execute the following command on any of the MySQL instance nodes:

mysqlshshell.connect(‘[email protected]:3306‘)var cluster = dba.createCluster(‘qwCluster‘);

If you create a successful output, there will be a similar "Cluster successfully created" in the information. The statement

Adding two additional nodes to a cluster cluster

cluster.addInstance(‘[email protected]:3306‘);cluster.addInstance(‘[email protected]:3306‘);

Cluster validation

Mysql.shell View

Cluster.status ()

Navacat connection MySQL View

SELECT @ @hostname

Problem multi-instance environment do not use port 3306

In a multi-instance environment, an instance uses the default port of 3306, which causes frequent misoperation. Remember

Recommended ports for this environment
3300 3310 3320 ... 3390

Deploy up to 10 instances of a single host

How to reset the lab environment

2. Master node:

   dba.dropMetadataSchema()   mysqlshell 清空集群mysql> stop group_replication;mysql> reset master;               （清空日志，确保和从库的表没有冲突奥，）mysql> reset slave

4. Other nodes (main cleanup and master-slave information to ensure that the main library and the table from the library do not conflict)

     mysql> stop group_replication;mysql> Reset master;mysql> Reset slave  

   hostname is inconsistent with name in/etc/hosts

   [REPL] slave I/O for Channel ' group_replication_recovery ': Error Connecting to master ' [email protected]:3306 '-retry-time:60 retries:1, error_code:my-002005
   [Error] [MY-0115 [REPL] Plugin Group_replication reported: ' There was a error when connecting to the donor server. Please check this group_replication_recovery channel credentials and all Member_host column values of PERFORMANCE_SCHEMA.R Eplication_group_members table is correct and DNS resolvable. '
   [ERROR] [MY-011583] [REPL] Plugin Group_replication reported: ' For details please check Performance_ Schema.replication_connection_status table and error log messages of Slave I/O for Channel Group_replication_recovery. '

This problem has depressed me for a long time,
Hostname is
Mysql4
MySQL5
Mysql6
And I was in the/etc/hosts.
192.168.181.103 MYSQL-1
192.168.181.103 Mysql-2
192.168.181.103 mysql-3

The log application card for the main library cannot be applied to the slave library at a location

[ERROR] [MY-010586] [REPL] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "Slave START". We stopped at log ' binlog.000007 ' position 151

[ERROR] [MY-010584] [REPL] Slave SQL for Channel ' group_replication_applier ': Error executing row event: ' Unknown database ' Mysql_innodb_cluster_met Adata ', error_code:my-001049

Rebuild Master:
MySQL>reset master

Restrictions on group replication

1. The problem of lock missing things:
   Group replication recommendations, things isolation level, read commit
2. Serialization Isolation Level: Multi-master mode not supported
3. Concurrent DDL and DML: in multi-master mode, the DDL on one table is not supported and the other side is updated so that there is no risk of detection of DDL operations on other instances
4. FOREIGN key cascading constraints: Multi-master mode, multi-level foreign key dependent pairs cause multi-level operations, which can cause unknown conflicts, it is recommended to open Group_replication_enforce_update_everywhere_checks=on
5. Big things, more than 5 seconds uncommitted, can cause group communication to fail,
6. Multi-Master mode: the SELECT * for update causes a deadlock. Because this lock is not shared across the group.
7. Partial replication does not support: Under Group replication, set partial replication, which filters things and causes inconsistencies in group things.
8. Mysql 8.0.11 Group_replication_enforce_update_everywhere_checks=on is not supported in multi-master mode.
9. In the case of stopping replication, a node executes a command and then starts, because there is a local private thing that cannot join the cluster. Global Reset Master is required to restart cluster replication.

MySQL 8.0.11 INNODB cluster Operations Management Handbook II--Cluster construction