One , Basic environment configuration

1.1 Turn off the firewall and selinux

1.1.1 Turn off the firewall
systemctl stop firewalld.service # Stop firewall systemctl disable firewalld.service
# Do not start
1.1.2 close selinux
sed -i 's/SELINUX=enforcing/SELINUX=disabled/g' /etc/sysconfig/selinux cat
/etc/sysconfig/selinux
1.2 to configure IP Domain name mapping
vim /etc/hosts 127.0.0.1 localhost localhost.localdomain localhost4
localhost4.localdomain4 ::1 localhost localhost.localdomain localhost6
localhost6.localdomain6 192.168.0.118 zj1 192.168.0.119 zj2 192.168.0.120 zj3
1.3 change I/O Scheduling mode
echo deadline > /sys/block/sda/queue/scheduler cat
/sys/block/sda/queue/scheduler
1.4 set up swap Partition size
echo "vm.swappiness = 0" >>/etc/sysctl.conf sysctl -p # adopt sysctl -a see PS
:swappiness Value in 0-100 between ,0 Make the best use of physical memory ,100 Try your best to use it swap partition .
1.5 File system selection

   Best choice of file system xfs, It is more than ext4 More convenient management .

1.6 Operating system restrictions
cat /etc/security/limits.conf ... * soft nproc 65536 * hard nproc 65536 * soft
nofile 65536 * hard nofile 65536 ...
1.7 close numa
# sed -i 's/GRUB_CMDLINE_LINUX.*/GRUB_CMDLINE_LINUX="crashkernel=auto
rd.lvm.lv=centos\/root rd.lvm.lv=centos\/swap rhgb quiet numa=off"/g'
/etc/default/grub # grub2-mkconfig -o /etc/grub2.cfg # cat /etc/grub2.cfg #
reboot # cat /proc/cmdline # dmesg | grep -i numa
Two , Instance installation

2.1 establish MySQL User groups and users
# groupadd mysql # useradd -g mysql mysql -s /sbin/nologin # id mysql
2.2 Create related directory
# mkdir -p /usr/local/mysql/{program,data,conf} # mkdir -p
/usr/local/mysql/data/{data,sock,tmpdir,log,ib,redo,undo,slowlog,binlog,relaylog}
2.3 Unzip the installation package and set permissions
# tar xf mysql-8.0.17-el7-x86_64.tar -C /usr/local/mysql/program/ # cd
/usr/local/mysql/program # tar xf mysql-8.0.17-el7-x86_64.tar.gz # tar xf
mysql-test-8.0.17-el7-x86_64.tar.gz # chown mysql:mysql /usr/local/mysql -R
2.4 Soft link program directory and configure environment variables
ln -s /usr/local/mysql/program/mysql-8.0.17-el7-x86_64
/usr/local/mysql/program/mysql export
PATH=$PATH:/usr/local/mysql/program/mysql/bin/ echo 'export
PATH=$PATH:/usr/local/mysql/program/mysql/bin/' >> /etc/profile #
Check whether the environment variable configuration was successfully added to /etc/profile In the file tail -1 /etc/profile
2.5 Modify configuration file
# Configure according to the actual situation [client] socket=/usr/local/mysql/data/sock/mysql.sock #sock File path
[mysqld] user=mysql server-id=1 basedir=/usr/local/mysql/program/mysql
socket=/usr/local/mysql/data/sock/mysql.sock
pid-file=/usr/local/mysql/data/sock/mysql.pid
datadir=/usr/local/mysql/data/data tmpdir=/usr/local/mysql/data/tmpdir
log-error=/usr/local/mysql/data/log/error.log slow_query_log=1
log_queries_not_using_indexes=1
slow_query_log_file=/usr/local/mysql/data/slowlog/slow-query.log
log-bin=/usr/local/mysql/data/binlog/mysql-bin
relay-log=/usr/local/mysql/data/relaylog/mysql-relay-bin
innodb_data_home_dir=/usr/local/mysql/data/ib
innodb_log_group_home_dir=/usr/local/mysql/data/redo
innodb_undo_directory=/usr/local/mysql/data/undo/ innodb_undo_tablespaces=3
character_set_server=utf8mb4 #MGR Use optimistic lock , So the official website suggests that the isolation level is RC, Reduce lock granularity
transaction_isolation=READ-COMMITTED gtid_mode=on enforce_gtid_consistency=1
binlog_format=row secure_file_priv=/tmp performance_schema=ON
innodb_redo_log_archive_dirs=/usr/local/mysql/data/redo_archive
# Because the clusters will check each other during recovery binlog Data for , Therefore, it is necessary to record the executed data sent by other servers in the cluster binlog, Press GTID To distinguish whether it has been carried out .
log-slave-updates=1 #binlog Verification rules ,5.6 The later version is CRC32, The lower versions are all NONE, however MGR Required NONE
binlog_checksum=NONE # Security considerations ,MGR The cluster requires that the replication mode be changed to slave Record in a table , Otherwise, the report will be wrong
master_info_repository=TABLE relay_log_info_repository=TABLE
# Algorithms for recording transactions , It is recommended to set this parameter on the official website XXHASH64 algorithm transaction_write_set_extraction = XXHASH64
# Equivalent to this GROUP The name of , yes UUID value , It can't be used with others in the cluster GTID Value of UUID Mixed use , available uuidgen To generate
loose-group_replication_group_name = 'de720075-8b42-4833-a4d9-a60e8efb8f5a'
# Do you want to start group replication automatically as the server starts , Direct startup is not recommended , Be afraid of disturbing the accuracy of data during fault recovery
loose-group_replication_start_on_boot = OFF
# local MGR Of IP Address and port ,host:port, yes MGR Port of , Is not the port of the database loose-group_replication_local_address
= '192.168.0.118:33061' # Need to accept this MGR Instance controlled server IP Address and port , yes MGR Port of , Is not the port of the database
loose-group_replication_group_seeds =
'192.168.0.118:33061,192.168.0.119:33061,192.168.0.120:33061'
# Turn on boot mode , Add group members , For first build MGR Or rebuild MGR When using , It only needs to be turned on in one of the cluster
loose-group_replication_bootstrap_group = OFF #IP Address white list , Connections from external hosts will not be allowed , On demand security settings
#loose-group_replication_ip_whitelist = report_host=192.168.0.118
report_port=3306 #ln -s /usr/local/mysql/conf/my.cnf /etc/my.cnf
2.6 Initialize database
mysqld --defaults-file=/etc/my.cnf --initialize-insecure &
--initialize: When this option is initialized, a random is written in the error log root password , Search in the error log after initialization is complete password, The random password is followed by a string of strings , After initialization is completed and started mysqld after , This random password is required for initial login .
--initialize-insecure: This option does not generate a random password when initialized , It's like 5.7 As before, the first login to the database after the initialization is completed root password
2.7 Start the database service
mysqld_safe --defaults-file=/etc/my.cnf & # Order of scanning configuration files , Other locations can also be specified manually
/etc/my.cnf>/etc/mysql/my.cnf>$mysql_home/my.cnf>~/.my.cnf
# Check whether the startup is successful , If it fails , Go to the error log to see the reason ps -ef | grep mysql netstat -anultp | grep Port number
Three , start-up MGR

3.1 start-up MGR Single master mode
# All nodes perform the following steps mysql -S /usr/local/mysql/data/sock/mysql.sock # Login database
mysql>INSTALL PLUGIN group_replication SONAME 'group_replication.so'; # Installing plug-ins
mysql> SET SQL_LOG_BIN=0; # close binlog mysql> CREATE USER repl@'%' IDENTIFIED BY
'repl'; # Create copy account mysql> GRANT REPLICATION SLAVE ON *.* TO repl@'%'; # to grant authorization mysql>
FLUSH PRIVILEGES; # Refresh permissions mysql> SET SQL_LOG_BIN=1; # open binlog mysql> CHANGE
MASTER TO MASTER_USER='repl', MASTER_PASSWORD='repl' FOR CHANNEL
'group_replication_recovery'; # Create replication # start-up MGR, stay 192.168.0.118 On the implementation mysql> SET GLOBAL
group_replication_bootstrap_group=ON; mysql> START GROUP_REPLICATION; mysql>
SET GLOBAL group_replication_bootstrap_group=OFF; # see MGR Group information mysql> SELECT *
FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+---------------+-------------+--------------+-------------+----------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
MEMBER_ROLE | MEMBER_VERSION |
+---------------------------+--------------------------------------+---------------+-------------+--------------+-------------+----------------+
| group_replication_applier | 2a7335b3-c743-11e9-bcbb-000c29586226 |
192.168.0.118 | 3306 | ONLINE | PRIMARY | 8.0.17 |
+---------------------------+--------------------------------------+---------------+-------------+--------------+-------------+----------------+
1 row in set (0.00 sec) # Join other nodes MGR, In slave Library 192.168.0.119,192.168.0.120 On the implementation mysql>
START GROUP_REPLICATION; # see MGR Group information mysql> SELECT * FROM
performance_schema.replication_group_members;
+---------------------------+--------------------------------------+---------------+-------------+--------------+-------------+----------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
MEMBER_ROLE | MEMBER_VERSION |
+---------------------------+--------------------------------------+---------------+-------------+--------------+-------------+----------------+
| group_replication_applier | 2a7335b3-c743-11e9-bcbb-000c29586226 |
192.168.0.118 | 3306 | ONLINE | PRIMARY | 8.0.17 | | group_replication_applier
| 9bff969a-c743-11e9-90c0-000c29a2154f | 192.168.0.119 | 3306 | ONLINE |
SECONDARY | 8.0.17 | | group_replication_applier |
c8dca41c-c744-11e9-8443-000c2943d916 | 192.168.0.120 | 3306 | ONLINE |
SECONDARY | 8.0.17 |
+---------------------------+--------------------------------------+---------------+-------------+--------------+-------------+----------------+
3 rows in set (0.04 sec)
   If an error is reported during startup , It can be referred to performance_schema.replication_connection_status Or log output to solve the problem .
2019-08-26T06:44:59.710514Z 17 [ERROR] [MY-010584] [Repl] Slave I/O for
channel 'group_replication_recovery': error connecting to master
'repl@192.168.0.118:3306' - retry-time: 60 retries: 1 message: Authentication
plugin 'caching_sha2_password' reported error: Authentication requires secure
connection. Error_code: MY-002061 2019-08-26T06:44:59.713143Z 16 [ERROR]
[MY-011582] [Repl] Plugin group_replication reported: 'There was an error when
connecting to the donor server. Please check that group_replication_recovery
channel credentials and all MEMBER_HOST column values of
performance_schema.replication_group_members table are correct and DNS
resolvable.' 2019-08-26T06:44:59.713159Z 16 [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.'
As can be seen from the error report caching_sha2_password Plug in problems , Make changes , Remember to turn off and on before and after the change binlog. # ALTER USER
'repl'@'%' IDENTIFIED WITH mysql_native_password BY 'repl';
3.2 Switch to MGR Multi master mode
# All nodes perform the following steps mysql> stop group_replication;
# Do you want to start single master mode , If activated , This instance is the main database , Provide reading and writing , Other instances are read only , If off It's multi master mode mysql> set global
group_replication_single_primary_mode=OFF; # In multi master mode , Force each instance to check if the operation is allowed , If it's not multi owner , Can be turned off
mysql> set global group_replication_enforce_update_everywhere_checks=ON;
# Only on one node , Any node can mysql> SET GLOBAL group_replication_bootstrap_group=ON;
mysql> START GROUP_REPLICATION; mysql> SET GLOBAL
group_replication_bootstrap_group=OFF; # Execute on the remaining nodes mysql> START GROUP_REPLICATION;
mysql> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+---------------+-------------+--------------+-------------+----------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
MEMBER_ROLE | MEMBER_VERSION |
+---------------------------+--------------------------------------+---------------+-------------+--------------+-------------+----------------+
| group_replication_applier | 2a7335b3-c743-11e9-bcbb-000c29586226 |
192.168.0.118 | 3306 | ONLINE | PRIMARY | 8.0.17 | | group_replication_applier
| 9bff969a-c743-11e9-90c0-000c29a2154f | 192.168.0.119 | 3306 | ONLINE |
PRIMARY | 8.0.17 | | group_replication_applier |
c8dca41c-c744-11e9-8443-000c2943d916 | 192.168.0.120 | 3306 | ONLINE | PRIMARY
| 8.0.17 |
+---------------------------+--------------------------------------+---------------+-------------+--------------+-------------+----------------+
3 rows in set (0.03 sec)
3.3 Switch back to MGR Single master mode
# All nodes perform the following steps mysql> stop group_replication; mysql> set global
group_replication_enforce_update_everywhere_checks=OFF; mysql> set global
group_replication_single_primary_mode=ON; # Master node execution , stay 192.168.0.118 On the implementation SET GLOBAL
group_replication_bootstrap_group=ON; START GROUP_REPLICATION; SET GLOBAL
group_replication_bootstrap_group=OFF; # Execute on the remaining nodes START GROUP_REPLICATION; #
see MGR Group information mysql> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+---------------+-------------+--------------+-------------+----------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
MEMBER_ROLE | MEMBER_VERSION |
+---------------------------+--------------------------------------+---------------+-------------+--------------+-------------+----------------+
| group_replication_applier | 2a7335b3-c743-11e9-bcbb-000c29586226 |
192.168.0.118 | 3306 | ONLINE | PRIMARY | 8.0.17 | | group_replication_applier
| 9bff969a-c743-11e9-90c0-000c29a2154f | 192.168.0.119 | 3306 | ONLINE |
SECONDARY | 8.0.17 | | group_replication_applier |
c8dca41c-c744-11e9-8443-000c2943d916 | 192.168.0.120 | 3306 | ONLINE |
SECONDARY | 8.0.17 |
+---------------------------+--------------------------------------+---------------+-------------+--------------+-------------+----------------+
3 rows in set (0.00 sec)
Four , summary

   In this paper, we introduce the design of the MySQL Single instance installation to MGR Single owner , Multi step configuration and switching ,MGR The related principles and features will be explained in the following articles .

Technology
©2019-2020 Toolsou All rights reserved,
A summary of the computer retest interview questions var a = 0, b = 0; function A(a) {A = function (b)... An interview question about scope 【 Data structure and algorithm 1】 Sparse array 2020—— Net tripod cup part writeupPTA-MOOC《Python Programming Zhejiang University 》 Puzzle A Chapter 2 programming problems python closure uniapp Dynamic routing parameter transfer ( Delivery object ) How to use it Python Batch import Excel Combined use Pandas integration Lambda Expression and functional interface 【Java Details of knowledge points 3】 Serializable and Deserialize