MySQL 5.7 GTID Replication

Install MySQL 5.7 Server on CentOS 7
sysctl vm.swappiness=1
echo vm.swappiness=1 >> /etc/sysctl.conf

rpm -ivh https://dev.mysql.com/get/mysql57-community-release-el7-11.noarch.rpm
yum install -y mysql-community-client mysql-community-server

# start
systemctl start mysqld

# get temporary root password
grep -i temporary /var/log/mysqld.log

# login
mysql -uroot -p

# change default password
SET PASSWORD FOR 'root'@'localhost' = PASSWORD('P@ssw0rd');

# save your creds to .my.cnf
cat << EOF >> ~/.my.cnf
[client]
user=root
password=P@ssw0rd
EOF

Fisrt read this:
https://dev.mysql.com/doc/refman/5.7/en/optimization.html
https://github.com/major/MySQLTuner-perl
22TB migration https://thoughts.t37.net/how-we-upgraded-a-22tb-mysql-cluster-from-5-6-to-5-7-in-9-months-cc41b391895d

/etc/my.cnf
[mysqld]
user=mysql
basedir=/usr/
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
pid-file=/var/lib/mysql/mysql.pid
port=3306

## Logs
log_error=/var/log/mysql/mysqld.log
log_warnings=2
# log_output = FILE
## Slow logging
slow_query_log_file=/var/log/mysql/mysql-slow.log
long_query_time=2
slow_query_log=OFF
log_queries_not_using_indexes=OFF
log_slow_admin_statements=ON

memlock = 0
sysdate_is_now=1
max_connections=500
symbolic-links=0
skip_name_resolve
skip-external-locking
open_files_limit       = 65535
lower_case_table_names = 1

## CHARACTER SET
#collation_server = utf8_unicode_ci
#init_connect = 'SET NAMES utf8'
#character_set_server = utf8


## REPLICATION
##
server-id   = 1                 # uniq for every server
report_host = 10.10.10.12       # ip of other master node

log_bin             = binlog
log_slave_updates   = 1
binlog-format       = ROW
relay-log           = relaylog
gtid-mode           = ON
enforce-gtid-consistency = ON
expire_logs_days    = 7
max_binlog_size     = 1G
read_only           = ON
sync_binlog         = 1
#slave-skip-errors  = ALL
slave_parallel_workers  = 2      # = ( cores - 1 )
ignore-db-dir    = lost+found

## Semi-sync replication settings
loose_rpl_semi_sync_master_enabled=ON
loose_rpl_semi_sync_master_timeout=1000
loose_rpl_semi_sync_slave_enabled=ON

## Replication safety (from 5.6 and onwards)
master_info_repository=TABLE
relay_log_info_repository=TABLE
relay_log_recovery=ON

## INNODB
##
default_storage_engine      = innodb
innodb_autoinc_lock_mode    = 2
innodb_buffer_pool_instances= 1
innodb_buffer_pool_size     = 1G       # (adjust value here, 50%-70% of total RAM)
innodb_log_files_in_group   = 2
innodb_log_file_size        = 512M     # 128M – 2G (does not need to be larger than buffer pool)
innodb_log_buffer_size      = 64M
innodb_checksum_algorithm   = crc32    # available since MySQL 5.6
innodb_data_file_path       = ibdata1:100M:autoextend
innodb_file_per_table       = 1
innodb_autoinc_lock_mode    = 2        # 1 (deafult) = safe for replication, 2 = fastest but not safe
innodb_flush_method         = O_DIRECT # avoid double buffering
#innodb_flush_method         = O_DSYNC  # 2 times faster than O_DIRECT but not safe
innodb_rollback_on_timeout  = ON
innodb_flush_log_at_trx_commit = 2     # 2 = fast, no flush no sync, 1 = reliability
innodb_flush_log_at_timeout    = 1800
innodb_stats_on_metadata    = 0        # avoid statistics update when doing e.g show tables
innodb_thread_concurrency   = 64
innodb_doublewrite          = 1
## for RAID10 with 8 disks:
innodb_read_io_threads=8
innodb_write_io_threads=4
## for SSD:
#innodb_read_io_threads=32
#innodb_write_io_threads=16

## OTHER TWEAKS
##
performance_schema = ON
performance-schema-max-mutex-classes = 0
performance-schema-max-mutex-instances = 0
tmp_table_size      = 64M
max_heap_table_size = 64M
max_allowed_packet  = 512M
# sort_buffer_size = 256K
# read_buffer_size = 256K
# read_rnd_buffer_size = 512K
# myisam_sort_buffer_size = 8M
thread_cache_size=512
query_cache_type = 0
query_cache_size = 0
table_open_cache=1024

[MYSQL]
socket=/var/lib/mysql/mysql.sock
# default_character_set = utf8

[client]
socket=/var/lib/mysql/mysql.sock
# default_character_set = utf8

[mysqldump]
socket=/var/lib/mysql/mysql.sock
max_allowed_packet = 512M
# default_character_set = utf8
user=backupuser
password=j63umETdF56QNkx7

[xtrabackup]
user=backupuser
password=j63umETdF56QNkx7

[MYSQLD_SAFE]
pid-file=/var/lib/mysql/mysql.pid
basedir=/usr/
# datadir = /var/lib/mysql

create a replication user on both nodes
CREATE USER 'replication'@'%' IDENTIFIED BY 'P@ssw0rd';
GRANT REPLICATION SLAVE ON *.* TO 'replication'@'%';
start replication
# on node 1 (10.10.10.12 - node-2 ip)
CHANGE MASTER TO MASTER_HOST='10.10.10.12', MASTER_USER='replication', MASTER_PORT=3306, MASTER_PASSWORD='P@ssw0rd', MASTER_AUTO_POSITION=1;
START SLAVE;

# on node 2 (10.10.10.11 - node-1 ip)
CHANGE MASTER TO MASTER_HOST='10.10.10.11', MASTER_USER='replication', MASTER_PORT=3306, MASTER_PASSWORD='P@ssw0rd', MASTER_AUTO_POSITION=1;
START SLAVE;
check slave status on both
SHOW SLAVE STATUS\G
add some remote admin users
mysql
> GRANT ALL PRIVILEGES ON *.* TO admin@"%" IDENTIFIED BY 'P@ssw0rd' WITH GRANT OPTION;
READ-ONLY SWITCH (switch it with keepalived or cluster)
mysql> FLUSH TABLES WITH READ LOCK;
mysql> SET GLOBAL read_only = ON;

mysql> SET GLOBAL read_only = OFF;
mysql> UNLOCK TABLES;
Slave recovery
# on master
mysql
> reset master;
> exit
mysqldump --all-databases --single-transaction --set-gtid-purged=OFF --triggers --routines > dump.sql
scp dump.sql 10.10.10.12:/

# on slave
mysql
> stop slave;
> stop slave for channel "master1";
> reset slave;
> set global relay_log_purge=1;
> flush logs;
> purge binary logs;
> source /dump.sql;
> start slave;

> show slave status\G
# ensure everything works:
#   Slave_IO_Running: Yes
#   Slave_SQL_Running: Yes

Now use Keepalived for virtual ip switching.