MySQL 8.0.18 Mgr building and switching function

Time:2020-5-18

1、 System installation package


yum -y install make gcc-c++ cmake bison-devel ncurses-devel readline-devel libaio-devel perl libaio wget lrzsz vim libnuma* bzip2 xz

2、 Turn off firewall and SELinux


sed -i 's/SELINUX=enforcing/SELINUX=disabled/' /etc/selinux/config
setenforce 0

/etc/init.d/iptables stop

echo "/etc/init.d/iptables stop">>/etc/rc.local

3、 Modify system limit parameters


cat >> /etc/security/limits.conf << EOF
#
###custom
#
* soft nofile 20480
* hard nofile 65535
* soft nproc 20480
* hard nproc 65535
EOF

4、 Configure each hosts host resolution


cat >> /etc/hosts <<"EOF"

10.10.146.28 bj-db-m1 
10.10.1.139 bj-db-m2 
10.10.173.84 bj-db-m3 

EOF

5、 Modify kernel parameters

cat >>/etc/sysctl.conf <<"EOF"
vm.swappiness=0
#Increase the number of queues supported by TCP
net.ipv4.tcp_max_syn_backlog = 65535
#Reduce resource recovery when disconnected
net.ipv4.tcp_max_tw_buckets = 8000
net.ipv4.tcp_tw_reuse = 1
net.ipv4.tcp_tw_recycle = 1
net.ipv4.tcp_fin_timeout = 10
#Change the local port range
net.ipv4.ip_local_port_range = 1024 65535
#Allow more connections to queue
net.ipv4.tcp_max_syn_backlog = 4096 
#For a local only database server
net.ipv4.tcp_fin_timeout = 30
#Port listening queue
net.core.somaxconn=65535
#Rate of data received
net.core.netdev_max_backlog=65535
net.core.wmem_default=87380
net.core.wmem_max=16777216
net.core.rmem_default=87380
net.core.rmem_max=16777216
EOF

sysctl -p

6、 Download the installation package

wget https://dev.mysql.com/get/Downloads/MySQL-8.0/mysql-8.0.18-linux-glibc2.12-x86_64.tar.xz
#Unzip the installation package
tar -xJf mysql-8.0.18-linux-glibc2.12-x86_64.tar.xz
#Enter the directory and make a soft connection for later upgrade
cd /usr/local/
ln -s /opt/mysql-8.0.18-linux-glibc2.12-x86_64 mysql
#Create user
groupadd mysql
useradd -g mysql mysql -d /home/mysql -s /sbin/nologin

#Create the corresponding directory

mkdir -p /data/mysql/mysql_3306/{logs,tmp,undolog}

7、 Create my.cnf configuration file

7-1. First configuration

#The first
if [ -f /etc/my.cnf ]; then
 mv /etc/my.cnf /etc/my.cnf.`date +%Y%m%d%H%m`.bak
fi 
# node1
cat >/data/mysql/mysql_3306/my_3306.cnf <<"EOF"
[client]
port = 3306
socket = /data/mysql/mysql_3306/tmp/mysql_3306.sock

[mysql]
prompt="\[email protected]\h \R:\m:\s [\d]> "
no-auto-rehash

[mysqld]
user = mysql
port = 3306
admin_address = 127.0.0.1
basedir = /usr/local/mysql
datadir = /data/mysql/mysql_3306/data
socket = /data/mysql/mysql_3306/tmp/mysql_3306.sock
pid-file = mysql_3306.pid
character-set-server = utf8mb4
skip_name_resolve = 1

#replicate-wild-ignore-table=mysql.%
#replicate-wild-ignore-table=test.%
#replicate-wild-ignore-table=information_schema.%

# Two-Master configure
#server-1 
#auto-increment-offset = 1
#auto-increment-increment = 2 

#server-2    
#auto-increment-offset = 2
#auto-increment-increment = 2


# semi sync replication settings #
#plugin_dir = /usr/local/mysql/lib/mysql/plugin
#plugin_load = "validate_password.so;rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"
plugin_ Dir = / usr / local / MySQL / lib / plugin - path to the official version
Plugin ﹐ load = "RPL ﹐ semi ﹐ sync ﹐ master = semisync ﹐ so; RPL ﹐ semi ﹐ sync ﹐ slave = semisync ﹐ slave. So" ﹐ official version path

slave_parallel_workers = 4
slave_parallel_type = LOGICAL_CLOCK
slave_preserve_commit_order = 1

open_files_limit = 65535
back_log = 1024
max_connections = 1024
max_connect_errors = 1000000
table_open_cache = 1024
table_definition_cache = 1024
table_open_cache_instances = 64
thread_stack = 512K
external-locking = FALSE
max_allowed_packet = 32M
sort_buffer_size = 4M
join_buffer_size = 4M
thread_cache_size = 1536
interactive_timeout = 600
wait_timeout = 600
tmp_table_size = 32M
max_heap_table_size = 32M
slow_query_log = 1
log_timestamps = SYSTEM
slow_query_log_file = /data/mysql/mysql_3306/logs/slow.log
log-error = /data/mysql/mysql_3306/logs/error.log
long_query_time = 0.1
log_queries_not_using_indexes =1
log_throttle_queries_not_using_indexes = 60
min_examined_row_limit = 100
log_slow_admin_statements = 1
log_slow_slave_statements = 1
server-id = 1423306
log-bin = /data/mysql/mysql_3306/logs/mysql-bin
sync_binlog = 1
binlog_cache_size = 4M
max_binlog_cache_size = 2G
max_binlog_size = 1G
binlog_expire_logs_seconds=2592000 
master_info_repository = TABLE
relay_log_info_repository = TABLE
gtid_mode = on
enforce_gtid_consistency = 1
binlog_checksum=NONE
log_slave_updates
slave-rows-search-algorithms = 'INDEX_SCAN,HASH_SCAN'
binlog_format = row
binlog_row_image=FULL

relay_log_recovery = 1
relay-log-purge = 1
key_buffer_size = 32M
read_buffer_size = 8M
read_rnd_buffer_size = 4M
bulk_insert_buffer_size = 64M
myisam_sort_buffer_size = 128M
myisam_max_sort_file_size = 10G
myisam_repair_threads = 1
lock_wait_timeout = 3600
explicit_defaults_for_timestamp = 1
innodb_thread_concurrency = 0
innodb_sync_spin_loops = 100
innodb_spin_wait_delay = 30

#transaction_isolation = REPEATABLE-READ
transaction_isolation = READ-COMMITTED
#innodb_additional_mem_pool_size = 16M
innodb_buffer_pool_size = 2867M
innodb_buffer_pool_instances = 4
innodb_buffer_pool_load_at_startup = 1
innodb_buffer_pool_dump_at_shutdown = 1
innodb_data_file_path = ibdata1:1G:autoextend
innodb_flush_log_at_trx_commit = 1
innodb_log_buffer_size = 32M
innodb_log_file_size = 2G
innodb_log_files_in_group = 3
innodb_max_undo_log_size = 4G
innodb_undo_directory = /data/mysql/mysql_3306/undolog

#Adjust appropriately according to your server's IOPs capability
#It can be adjusted to 10000 - 20000 if it is equipped with ordinary SSD disk
#If the high-end PCIe SSD card is configured, it can be adjusted higher, such as 50000 - 80000
innodb_io_capacity = 4000
innodb_io_capacity_max = 8000
innodb_flush_sync = 0
innodb_flush_neighbors = 0
innodb_write_io_threads = 8
innodb_read_io_threads = 8
innodb_purge_threads = 4
innodb_page_cleaners = 4
innodb_open_files = 65535
innodb_max_dirty_pages_pct = 50
innodb_flush_method = O_DIRECT
innodb_lru_scan_depth = 4000
innodb_checksum_algorithm = crc32
innodb_lock_wait_timeout = 10
innodb_rollback_on_timeout = 1
innodb_print_all_deadlocks = 1
innodb_file_per_table = 1
innodb_online_alter_log_max_size = 4G
innodb_stats_on_metadata = 0

# some var for MySQL 8
log_error_verbosity = 3
innodb_print_ddl_logs = 1
binlog_expire_logs_seconds = 2592000
#innodb_dedicated_server = 0

innodb_status_file = 1
#Note: when InnoDB? Status? Output & InnoDB? Status? Output? Locks are enabled, the log error file may grow faster
innodb_status_output = 0
innodb_status_output_locks = 0

#performance_schema
performance_schema = 1
performance_schema_instrument = '%memory%=on'
performance_schema_instrument = '%lock%=on'

#innodb monitor
innodb_monitor_enable="module_innodb"
innodb_monitor_enable="module_server"
innodb_monitor_enable="module_dml"
innodb_monitor_enable="module_ddl"
innodb_monitor_enable="module_trx"
innodb_monitor_enable="module_os"
innodb_monitor_enable="module_purge"
innodb_monitor_enable="module_log"
innodb_monitor_enable="module_lock"
innodb_monitor_enable="module_buffer"
innodb_monitor_enable="module_index"
innodb_monitor_enable="module_ibuf_system"
innodb_monitor_enable="module_buffer_page"
innodb_monitor_enable="module_adaptive_hash"

#MGR
#The loose prefix in the GR configuration item base indicates that if the group replication plugin does not load MySQL server, Meiji maintenance will be started
transaction_ write_ set_ Extraction = xxhash64 ා get write set for each transaction, and get hash value with xxhash64 algorithm
Loose group ABCD replication ABCD group ABCD name = "58f6e65e-9309-11e9-9d88-525400184a0a" ාgroup name, which can be generated with select uuid()
Loose group replication start on boot = off do not automatically start group replication when mysqld starts
loose-group_ replication_ local_ address = " 10.10.146.28:33006 "ා the IP address and port of this node. Note that the port is the communication port between members of the group, not the external service port of MySQL
Loose group replication group seeds = "10.10.146.28:33006, 10.10.1.139:33006, 10.10.173.84:33006" ා IP and port number of the seed node. When new members join the cluster, they need to contact the seed node. The node that starts the cluster does not use this option
Close group? Replication? Bootstrap? Group = off? Close. If it is turned on, it will cause brain crack? Whether to start the cluster? Note that this option can only be used for one node at any time. Generally, it is used when starting the cluster. You need to turn off this option after starting

Loose group? Replication? Member? Weight = 50? Weight selection


[mysqldump]
quick
max_allowed_packet = 32M

[mysqld_safe]
#malloc-lib=/usr/local/mysql/lib/jmalloc.so 
nice=-19
open-files-limit=65535

EOF

7-2. Second configuration

#Second
if [ -f /etc/my.cnf ]; then
 mv /etc/my.cnf /etc/my.cnf.`date +%Y%m%d%H%m`.bak
fi 
# node1
cat >/data/mysql/mysql_3306/my_3306.cnf <<"EOF"
[client]
port = 3306
socket = /data/mysql/mysql_3306/tmp/mysql_3306.sock

[mysql]
prompt="\[email protected]\h \R:\m:\s [\d]> "
no-auto-rehash

[mysqld]
user = mysql
port = 3306
admin_address = 127.0.0.1
basedir = /usr/local/mysql
datadir = /data/mysql/mysql_3306/data
socket = /data/mysql/mysql_3306/tmp/mysql_3306.sock
pid-file = mysql_3306.pid
character-set-server = utf8mb4
skip_name_resolve = 1

#replicate-wild-ignore-table=mysql.%
#replicate-wild-ignore-table=test.%
#replicate-wild-ignore-table=information_schema.%

# Two-Master configure
#server-1 
#auto-increment-offset = 1
#auto-increment-increment = 2 

#server-2    
#auto-increment-offset = 2
#auto-increment-increment = 2


# semi sync replication settings #
#plugin_dir = /usr/local/mysql/lib/mysql/plugin
#plugin_load = "validate_password.so;rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"
plugin_ Dir = / usr / local / MySQL / lib / plugin - path to the official version
Plugin ﹐ load = "RPL ﹐ semi ﹐ sync ﹐ master = semisync ﹐ so; RPL ﹐ semi ﹐ sync ﹐ slave = semisync ﹐ slave. So" ﹐ official version path

slave_parallel_workers = 4
slave_parallel_type = LOGICAL_CLOCK
slave_preserve_commit_order = 1

open_files_limit = 65535
back_log = 1024
max_connections = 1024
max_connect_errors = 1000000
table_open_cache = 1024
table_definition_cache = 1024
table_open_cache_instances = 64
thread_stack = 512K
external-locking = FALSE
max_allowed_packet = 32M
sort_buffer_size = 4M
join_buffer_size = 4M
thread_cache_size = 1536
interactive_timeout = 600
wait_timeout = 600
tmp_table_size = 32M
max_heap_table_size = 32M
slow_query_log = 1
log_timestamps = SYSTEM
slow_query_log_file = /data/mysql/mysql_3306/logs/slow.log
log-error = /data/mysql/mysql_3306/logs/error.log
long_query_time = 0.1
log_queries_not_using_indexes =1
log_throttle_queries_not_using_indexes = 60
min_examined_row_limit = 100
log_slow_admin_statements = 1
log_slow_slave_statements = 1
server-id = 1433306
log-bin = /data/mysql/mysql_3306/logs/mysql-bin
sync_binlog = 1
binlog_cache_size = 4M
max_binlog_cache_size = 2G
max_binlog_size = 1G
binlog_expire_logs_seconds=2592000 
master_info_repository = TABLE
relay_log_info_repository = TABLE
gtid_mode = on
enforce_gtid_consistency = 1
binlog_checksum=NONE
log_slave_updates
slave-rows-search-algorithms = 'INDEX_SCAN,HASH_SCAN'
binlog_format = row
binlog_row_image=FULL

relay_log_recovery = 1
relay-log-purge = 1
key_buffer_size = 32M
read_buffer_size = 8M
read_rnd_buffer_size = 4M
bulk_insert_buffer_size = 64M
myisam_sort_buffer_size = 128M
myisam_max_sort_file_size = 10G
myisam_repair_threads = 1
lock_wait_timeout = 3600
explicit_defaults_for_timestamp = 1
innodb_thread_concurrency = 0
innodb_sync_spin_loops = 100
innodb_spin_wait_delay = 30

#transaction_isolation = REPEATABLE-READ
transaction_isolation = READ-COMMITTED
#innodb_additional_mem_pool_size = 16M
innodb_buffer_pool_size = 2867M
innodb_buffer_pool_instances = 4
innodb_buffer_pool_load_at_startup = 1
innodb_buffer_pool_dump_at_shutdown = 1
innodb_data_file_path = ibdata1:1G:autoextend
innodb_flush_log_at_trx_commit = 1
innodb_log_buffer_size = 32M
innodb_log_file_size = 2G
innodb_log_files_in_group = 3
innodb_max_undo_log_size = 4G
innodb_undo_directory = /data/mysql/mysql_3306/undolog

#Adjust appropriately according to your server's IOPs capability
#It can be adjusted to 10000 - 20000 if it is equipped with ordinary SSD disk
#If the high-end PCIe SSD card is configured, it can be adjusted higher, such as 50000 - 80000
innodb_io_capacity = 4000
innodb_io_capacity_max = 8000
innodb_flush_sync = 0
innodb_flush_neighbors = 0
innodb_write_io_threads = 8
innodb_read_io_threads = 8
innodb_purge_threads = 4
innodb_page_cleaners = 4
innodb_open_files = 65535
innodb_max_dirty_pages_pct = 50
innodb_flush_method = O_DIRECT
innodb_lru_scan_depth = 4000
innodb_checksum_algorithm = crc32
innodb_lock_wait_timeout = 10
innodb_rollback_on_timeout = 1
innodb_print_all_deadlocks = 1
innodb_file_per_table = 1
innodb_online_alter_log_max_size = 4G
innodb_stats_on_metadata = 0

# some var for MySQL 8
log_error_verbosity = 3
innodb_print_ddl_logs = 1
binlog_expire_logs_seconds = 2592000
#innodb_dedicated_server = 0

innodb_status_file = 1
#Note: when InnoDB? Status? Output & InnoDB? Status? Output? Locks are enabled, the log error file may grow faster
innodb_status_output = 0
innodb_status_output_locks = 0

#performance_schema
performance_schema = 1
performance_schema_instrument = '%memory%=on'
performance_schema_instrument = '%lock%=on'

#innodb monitor
innodb_monitor_enable="module_innodb"
innodb_monitor_enable="module_server"
innodb_monitor_enable="module_dml"
innodb_monitor_enable="module_ddl"
innodb_monitor_enable="module_trx"
innodb_monitor_enable="module_os"
innodb_monitor_enable="module_purge"
innodb_monitor_enable="module_log"
innodb_monitor_enable="module_lock"
innodb_monitor_enable="module_buffer"
innodb_monitor_enable="module_index"
innodb_monitor_enable="module_ibuf_system"
innodb_monitor_enable="module_buffer_page"
innodb_monitor_enable="module_adaptive_hash"

#MGR
#The loose prefix in the GR configuration item base indicates that if the group replication plugin does not load MySQL server, Meiji maintenance will be started
transaction_ write_ set_ Extraction = xxhash64 ා get write set for each transaction, and get hash value with xxhash64 algorithm
Loose group ABCD replication ABCD group ABCD name = "58f6e65e-9309-11e9-9d88-525400184a0a" ාgroup name, which can be generated with select uuid()
Loose group replication start on boot = off do not automatically start group replication when mysqld starts
Lose group replication local address = "10.10.1.139:33006" the IP address and port of this node. Note that the port is the communication port between members in the group, not the external service port of MySQL
Loose group replication group seeds = "10.10.146.28:33006, 10.10.1.139:33006, 10.10.173.84:33006" ා IP and port number of the seed node. When new members join the cluster, they need to contact the seed node. The node that starts the cluster does not use this option
Close group? Replication? Bootstrap? Group = off? Close. If it is turned on, it will cause brain crack? Whether to start the cluster? Note that this option can only be used for one node at any time. Generally, it is used when starting the cluster. You need to turn off this option after starting

Loose group? Replication? Member? Weight = 50? Weight selection


[mysqldump]
quick
max_allowed_packet = 32M

[mysqld_safe]
#malloc-lib=/usr/local/mysql/lib/jmalloc.so 
nice=-19
open-files-limit=65535

EOF

7-3. Configuration of the third set

#The third
if [ -f /etc/my.cnf ]; then
 mv /etc/my.cnf /etc/my.cnf.`date +%Y%m%d%H%m`.bak
fi 
# node1
cat >/data/mysql/mysql_3306/my_3306.cnf <<"EOF"
[client]
port = 3306
socket = /data/mysql/mysql_3306/tmp/mysql_3306.sock

[mysql]
prompt="\[email protected]\h \R:\m:\s [\d]> "
no-auto-rehash

[mysqld]
user = mysql
port = 3306
admin_address = 127.0.0.1
basedir = /usr/local/mysql
datadir = /data/mysql/mysql_3306/data
socket = /data/mysql/mysql_3306/tmp/mysql_3306.sock
pid-file = mysql_3306.pid
character-set-server = utf8mb4
skip_name_resolve = 1

#replicate-wild-ignore-table=mysql.%
#replicate-wild-ignore-table=test.%
#replicate-wild-ignore-table=information_schema.%

# Two-Master configure
#server-1 
#auto-increment-offset = 1
#auto-increment-increment = 2 

#server-2    
#auto-increment-offset = 2
#auto-increment-increment = 2


# semi sync replication settings #
#plugin_dir = /usr/local/mysql/lib/mysql/plugin
#plugin_load = "validate_password.so;rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"
Plugin? Dir = / usr / local / MySQL / lib / plugin? Path to the official version
Plugin ﹐ load = "RPL ﹐ semi ﹐ sync ﹐ master = semisync ﹐ so; RPL ﹐ semi ﹐ sync ﹐ slave = semisync ﹐ slave. So" ﹐ official version path

slave_parallel_workers = 4
slave_parallel_type = LOGICAL_CLOCK
slave_preserve_commit_order = 1

open_files_limit = 65535
back_log = 1024
max_connections = 1024
max_connect_errors = 1000000
table_open_cache = 1024
table_definition_cache = 1024
table_open_cache_instances = 64
thread_stack = 512K
external-locking = FALSE
max_allowed_packet = 32M
sort_buffer_size = 4M
join_buffer_size = 4M
thread_cache_size = 1536
interactive_timeout = 600
wait_timeout = 600
tmp_table_size = 32M
max_heap_table_size = 32M
slow_query_log = 1
log_timestamps = SYSTEM
slow_query_log_file = /data/mysql/mysql_3306/logs/slow.log
log-error = /data/mysql/mysql_3306/logs/error.log
long_query_time = 0.1
log_queries_not_using_indexes =1
log_throttle_queries_not_using_indexes = 60
min_examined_row_limit = 100
log_slow_admin_statements = 1
log_slow_slave_statements = 1
server-id = 1443306
log-bin = /data/mysql/mysql_3306/logs/mysql-bin
sync_binlog = 1
binlog_cache_size = 4M
max_binlog_cache_size = 2G
max_binlog_size = 1G
binlog_expire_logs_seconds=2592000 
master_info_repository = TABLE
relay_log_info_repository = TABLE
gtid_mode = on
enforce_gtid_consistency = 1
binlog_checksum=NONE
log_slave_updates
slave-rows-search-algorithms = 'INDEX_SCAN,HASH_SCAN'
binlog_format = row
binlog_row_image=FULL

relay_log_recovery = 1
relay-log-purge = 1
key_buffer_size = 32M
read_buffer_size = 8M
read_rnd_buffer_size = 4M
bulk_insert_buffer_size = 64M
myisam_sort_buffer_size = 128M
myisam_max_sort_file_size = 10G
myisam_repair_threads = 1
lock_wait_timeout = 3600
explicit_defaults_for_timestamp = 1
innodb_thread_concurrency = 0
innodb_sync_spin_loops = 100
innodb_spin_wait_delay = 30

#transaction_isolation = REPEATABLE-READ
transaction_isolation = READ-COMMITTED
#innodb_additional_mem_pool_size = 16M
innodb_buffer_pool_size = 2867M
innodb_buffer_pool_instances = 4
innodb_buffer_pool_load_at_startup = 1
innodb_buffer_pool_dump_at_shutdown = 1
innodb_data_file_path = ibdata1:1G:autoextend
innodb_flush_log_at_trx_commit = 1
innodb_log_buffer_size = 32M
innodb_log_file_size = 2G
innodb_log_files_in_group = 3
innodb_max_undo_log_size = 4G
innodb_undo_directory = /data/mysql/mysql_3306/undolog

#Adjust appropriately according to your server's IOPs capability
#It can be adjusted to 10000 - 20000 if it is equipped with ordinary SSD disk
#If the high-end PCIe SSD card is configured, it can be adjusted higher, such as 50000 - 80000
innodb_io_capacity = 4000
innodb_io_capacity_max = 8000
innodb_flush_sync = 0
innodb_flush_neighbors = 0
innodb_write_io_threads = 8
innodb_read_io_threads = 8
innodb_purge_threads = 4
innodb_page_cleaners = 4
innodb_open_files = 65535
innodb_max_dirty_pages_pct = 50
innodb_flush_method = O_DIRECT
innodb_lru_scan_depth = 4000
innodb_checksum_algorithm = crc32
innodb_lock_wait_timeout = 10
innodb_rollback_on_timeout = 1
innodb_print_all_deadlocks = 1
innodb_file_per_table = 1
innodb_online_alter_log_max_size = 4G
innodb_stats_on_metadata = 0

# some var for MySQL 8
log_error_verbosity = 3
innodb_print_ddl_logs = 1
binlog_expire_logs_seconds = 2592000
#innodb_dedicated_server = 0

innodb_status_file = 1
#Note: enable InnoDB_ status_ output & innodb_ status_ output_ After locks, the log error file may grow faster
innodb_status_output = 0
innodb_status_output_locks = 0

#performance_schema
performance_schema = 1
performance_schema_instrument = '%memory%=on'
performance_schema_instrument = '%lock%=on'

#innodb monitor
innodb_monitor_enable="module_innodb"
innodb_monitor_enable="module_server"
innodb_monitor_enable="module_dml"
innodb_monitor_enable="module_ddl"
innodb_monitor_enable="module_trx"
innodb_monitor_enable="module_os"
innodb_monitor_enable="module_purge"
innodb_monitor_enable="module_log"
innodb_monitor_enable="module_lock"
innodb_monitor_enable="module_buffer"
innodb_monitor_enable="module_index"
innodb_monitor_enable="module_ibuf_system"
innodb_monitor_enable="module_buffer_page"
innodb_monitor_enable="module_adaptive_hash"

#MGR
#The loose prefix in the GR configuration item base indicates that if the group replication plugin does not load MySQL server, Meiji maintenance will be started
Transaction > write > set > extraction = xxhash64 > get write set for each transaction and get hash value with xxhash64 algorithm
Loose group ABCD replication ABCD group ABCD name = "58f6e65e-9309-11e9-9d88-525400184a0a" ාgroup name, which can be generated with select uuid()
Loose group replication start on boot = off do not automatically start group replication when mysqld starts
Lose group replication local address = "10.10.173.84:33006" the IP address and port of this node. Note that the port is the communication port between members in the group, not the external service port of MySQL
Loose group replication group seeds = "10.10.146.28:33006, 10.10.1.139:33006, 10.10.173.84:33006" ා IP and port number of the seed node. When new members join the cluster, they need to contact the seed node. The node that starts the cluster does not use this option
loose-group_ replication_ bootstrap_ Group = off ා turn off. If it is turned on, it will cause brain crack. Whether to start the cluster or not. Note that this option can only be used for one node at any time. Usually, it is used when starting the cluster. You need to turn off this option after starting

Loose group? Replication? Member? Weight = 50? Weight selection


[mysqldump]
quick
max_allowed_packet = 32M

[mysqld_safe]
#malloc-lib=/usr/local/mysql/lib/jmalloc.so 
nice=-19
open-files-limit=65535

EOF

8、 Modify permissions, initialize and start

chown -R mysql.mysql /data/mysql/mysql_3306
chown -R mysql.mysql /usr/local/mysql/

#Initialization
# /usr/local/mysql/bin/mysqld --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysql --initialize-insecure
#The official recommendation is to use -- initialize, which will generate temporary passwords that are difficult to enter in the error log. The password free method I use here.
/usr/local/mysql/bin/mysqld --defaults-file=/data/mysql/mysql_3306/my_3306.cnf --initialize-insecure --user=mysql &

#Start database
/usr/local/mysql/bin/mysqld_safe --defaults-file=/data/mysql/mysql_3306/my_3306.cnf &

9、 View log

#9. View log
# tail -f /data/mysql/mysql_3306/logs/error.log

10、 First landing

#10. First landing
/usr/local/mysql/bin/mysql -S /data/mysql/mysql_3306/tmp/mysql_3306.sock

11、 Change password

#Change password method

set sql_log_bin = 0;

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'GJjumB6g4FcwdF3R6AZc' PASSWORD EXPIRE NEVER ;
create user 'root'@'127.0.0.1' identified WITH mysql_native_password by 'GJjumB6g4FcwdF3R6AZc' PASSWORD EXPIRE NEVER ;
grant all privileges on *.* to 'root'@'127.0.0.1' with grant option;

create user 'admin_m'@'127.0.0.1' identified WITH mysql_native_password by 'fcfmTbRw1tz2x5L5GvjJ' PASSWORD EXPIRE NEVER ; 
grant all privileges on *.* to 'admin_m'@'127.0.0.1' with grant option;

create user 'admin_m'@'%' identified WITH mysql_native_password by 'fcfmTbRw1tz2x5L5GvjJ' PASSWORD EXPIRE NEVER ; 
grant all privileges on *.* to 'admin_m'@'%' with grant option;

create user 'test_w'@'%' identified with mysql_native_password by 'EeCrfUDO6wRzn72BBQ52' PASSWORD EXPIRE NEVER ;
grant insert,delete,update,select on db144.* to 'test_w'@'%' ;

create user 'test_r'@'%' identified with mysql_native_password by 'EeCrfUDO6wRzn72BBQ52' PASSWORD EXPIRE NEVER ;
grant insert,delete,update,select on db144.* to 'test_r'@'%' ;

create user 'repl'@'%' IDENTIFIED with mysql_native_password by 'replpfhOTnWffQdQL3F3' ;
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%' ;
set sql_log_bin = 1;

12、 Shortcut settings

Shortcuts


ln -s /usr/local/mysql/lib/libmysqlclient.so /usr/lib/
ln -s /usr/local/mysql/lib/libmysqlclient.so.21 /usr/lib/libmysqlclient.so.21
ln -s /usr/local/mysql/lib/libmysqlclient.so /usr/lib64/
ln -s /usr/local/mysql/lib/libmysqlclient.so.21 /usr/lib64/libmysqlclient.so.21


ln -s /data/mysql/mysql_3306/tmp/mysql_3306.sock /tmp/mysql.sock
ln -s /usr/local/mysql/bin/* /usr/bin/

cat >>~/.bashrc <<"EOF"
##########


alias mysql.3306.start="/usr/local/mysql/bin/mysqld_safe --defaults-file=/data/mysql/mysql_3306/my_3306.cnf &"
alias mysql.3306.stop="/usr/local/mysql/bin/mysqladmin -h127.0.0.1 -P 3306 -uroot -p'GJjumB6g4FcwdF3R6AZc' shutdown &"
alias mysql.3306.login="/usr/local/mysql/bin/mysql -h127.0.0.1 -P 3306 -uroot -p'GJjumB6g4FcwdF3R6AZc'"


##########
EOF


source /root/.bash_profile

cat >>/etc/ld.so.conf <<"EOF"
/usr/local/mysql/lib
EOF

ldconfig 
mysql.3306.login

13、 Mgr configuration

13-1. First configuration

#First Mgr configuration:
#Step 1: create users for replication

set sql_log_bin=0;
create user 'repuser'@'%' identified by 'JhXpMK44ju8Vp5bxvO2N';

grant replication slave,replication client on *.* to 'repuser'@'%';

create user 'repuser'@'127.0.0.1' identified by 'JhXpMK44ju8Vp5bxvO2N';

grant replication slave,replication client on *.* to 'repuser'@'127.0.0.1';

create user 'repuser'@'localhost' identified by 'JhXpMK44ju8Vp5bxvO2N';

grant replication slave,replication client on *.* to 'repuser'@'localhost';

set sql_log_bin=1;

#Step 2: configure users for replication

change master to master_user='repuser',master_password='JhXpMK44ju8Vp5bxvO2N' for channel 'group_replication_recovery';

#Step 3: install the MySQL group replication plug-in


#Note: if you write and write plugin "load =" group "replication = group" replication. So "in my.cnf, you can do nothing

install plugin group_replication soname 'group_replication.so';

#Check whether the installation is successful through show plugins
show plugins;

#Step 4: build a group (the official point is to initialize a replication group

set global group_replication_bootstrap_group=on;
start group_replication;
set global group_replication_bootstrap_group=off;

select * from performance_schema.replication_group_members;

13-2. Configuration of the second and the third

##########################################################################
#Mgr configures other slave nodes
#Execute in all MySQL from the host
#Step 1: create users for replication

set sql_log_bin=0;
create user 'repuser'@'%' identified by 'JhXpMK44ju8Vp5bxvO2N';

grant replication slave,replication client on *.* to 'repuser'@'%';

create user 'repuser'@'127.0.0.1' identified by 'JhXpMK44ju8Vp5bxvO2N';

grant replication slave,replication client on *.* to 'repuser'@'127.0.0.1';

create user 'repuser'@'localhost' identified by 'JhXpMK44ju8Vp5bxvO2N';

grant replication slave,replication client on *.* to 'repuser'@'localhost';

set sql_log_bin=1;

#Step 2: configure users for replication

change master to master_user='repuser',master_password='JhXpMK44ju8Vp5bxvO2N' for channel 'group_replication_recovery';

#Step 3: install the MySQL group replication plug-in


#Note: if you write and write plugin "load =" group "replication = group" replication. So "in my.cnf, you can do nothing

install plugin group_replication soname 'group_replication.so';

#Check whether the installation is successful through show plugins
show plugins;

#Step 4: join the replication group created previously

start group_replication;
select * from performance_schema.replication_group_members;
#########################################################################################

#Check status
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 | d955da6d-0048-11ea-b7b4-525400f4342d | bj-db-m1 | 3306 | ONLINE | PRIMARY | 8.0.18  |
| group_replication_applier | e050c34f-0048-11ea-917d-52540021fab9 | bj-db-m3 | 3306 | ONLINE | SECONDARY | 8.0.18  |
| group_replication_applier | e6c56347-0048-11ea-9e8b-5254007c241f | bj-db-m2 | 3306 | ONLINE | SECONDARY | 8.0.18  |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
3 rows in set (0.00 sec)

14、 Single master to multi master

######################################################################
#Single master to multi master
#The Mgr switch mode needs to restart group replication, so you need to turn off group replication on all nodes first,
#Set group_ replication_ single_ primary_ Mode = off, and then start group replication.
1) Stop group replication (on all Mgr nodes):
stop group_replication; 
set global group_replication_single_primary_mode=OFF;
set global group_replication_enforce_update_everywhere_checks=ON;

2) Select any Mgr node to execute (for example, select mgr-node1 node here):
set global group_replication_recovery_get_public_key=1;
SET GLOBAL group_replication_bootstrap_group=ON;
START GROUP_REPLICATION;
SET GLOBAL group_replication_bootstrap_group=OFF;

3) Then execute on the other Mgr nodes (in this case, mgr-node2 and mgr-node3 nodes):
set global group_replication_recovery_get_public_key=1;
START GROUP_REPLICATION;

4) View Mgr group information (can be viewed on any Mgr node)
SELECT * FROM performance_schema.replication_group_members;


#It can be seen that the status of all Mgr nodes is online and the roles are primary. The Mgr multi master mode is successfully built.

##########################################################################

15、 Multi master switch receipt master

##########################################################################
#Multi master switch back to single master mode
1) Stop group replication (on all Mgr nodes):
stop group_replication;
set global group_replication_enforce_update_everywhere_checks=OFF;
set global group_replication_single_primary_mode=ON;

2) Select a node as the master node and execute on the master node (here, select mgr-node1 node as the master node)
SET GLOBAL group_replication_bootstrap_group=ON;
START GROUP_REPLICATION;
SET GLOBAL group_replication_bootstrap_group=OFF;
 
3) In other remaining nodes, i.e. from the library node (in this case, from the library node refers to mgr-node2 and mgr-node3):
START GROUP_REPLICATION;

4) View Mgr group information (can be viewed on any Mgr node)
SELECT * FROM performance_schema.replication_group_members;
##########################################################################

16、 Fault precautions

#Trouble points:
#In single master mode, recover mgr-node1 node. After recovery, manually activate the group replication function of this node
#If a node fails, it needs to be rejoined to the Mgr cluster after recovery. The correct way is to:
STOP GROUP_REPLICATION;
START GROUP_REPLICATION;

#If a node hangs, the other nodes continue to synchronize
#When the failed node recovers, you only need to activate the group replication function ("start group [replication;") of the node manually,
#You can join the Mgr group replication cluster normally and automatically synchronize the data of other nodes


#If I / O replication is abnormal
#After confirming that the data is correct
#Find the gtid of the main library
mysql> show global variables like '%gtid%' ;
+----------------------------------------------+-------------------------------------------------------+
| Variable_name    | Value       |
+----------------------------------------------+-------------------------------------------------------+
| binlog_gtid_simple_recovery   | ON       |
| enforce_gtid_consistency   | ON       |
| group_replication_gtid_assignment_block_size | 1000000      |
| gtid_executed    | 58f6e65e-9309-11e9-9d88-525400184a0a:1-946050:1000003 |
| gtid_executed_compression_period  | 1000       |
| gtid_mode     | ON       |
| gtid_owned     |       |
| gtid_purged     |       |
| session_track_gtids    | OFF       |
+----------------------------------------------+-------------------------------------------------------+
rows in set (0.00 sec)

#Operate from the library in case of failure
stop GROUP_REPLICATION;
reset master;
set global gtid_purged='58f6e65e-9309-11e9-9d88-525400184a0a:1-946055:1000003';
START GROUP_REPLICATION;

#Add whitelist segment
stop group_replication;
set global group_replication_ip_whitelist="127.0.0.1/32,172.16.60.0/24,172.16.50.0/24,172.16.51.0/24";
start group_replication;
show variables like "group_replication_ip_whitelist";

#Be sure to close group replication and execute "stop group" before configuring the white list_ replication;"

summary

The above is the MySQL 8.0.18 Mgr building and switching function that Xiaobian introduced to you, hoping to help you!

Recommended Today

Promise. All() gets multiple asynchronous requests in the project

Promise. All() is simply understood as:Multiple promise instances can be combined into a new instance, and the combined promise will become full only when all instances are full. If any instance is rejected, the combined promise will become rejected。 1 const p1 = new Promise((resolve, reject) => { 2 resolve(‘success’) 3 }) 4 5 const […]