MySQL 5.7 GTID Replication

Install MySQL 5.7 Server on CentOS 7
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('[email protected]');

# save your creds to .my.cnf
cat << EOF >> ~/.my.cnf
[client]
user=root
[email protected]
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]
datadir=/var/lib/mysql

socket=/var/lib/mysql/mysql.sock
symbolic-links=0
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

skip_name_resolve
skip-external-locking
open_files_limit       = 65535
character-set-server   = utf8mb4
collation-server       = utf8mb4_general_ci
lower_case_table_names = 1
#performance_schema     = 0
#explicit_defaults_for_timestamp

## REPLICATION
##

server-id = 1                 # uniq for every server

auto-increment-offset     = 1 # for node 1
#auto-increment-offset     = 2 # for node 2
auto-increment-increment  = 2

master-info-repository    = TABLE
relay-log-info-repository = TABLE
binlog-format   = ROW
binlog-checksum = crc32
relay-log = /var/lib/mysql/mysql-relaylog
log-bin   = /var/lib/mysql/mysql-binlog
gtid-mode = ON
enforce-gtid-consistency = ON
binlog-ignore-db = sys
binlog-ignore-db = performance_schema
binlog-ignore-db = information_schema
expire_logs_days = 7
max_binlog_size  = 1G
sync_binlog      = 1
slave-skip-errors      = ALL
slave_parallel_workers = 2 # = CPU cores

## TWEAKS
##

innodb_buffer_pool_size     = 1G       # (adjust value here, 50%-70% of total RAM)
innodb_log_file_size        = 256M     # 128M – 2G (does not need to be larger than buffer pool)
innodb_checksum_algorithm   = crc32    # available since MySQL 5.6
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_flush_log_at_trx_commit = 2     # 2 = fast, no flush no sync, 1 = reliability
innodb_flush_log_at_timeout    = 1800

query_cache_type=OFF
query_cache_size=0
#table_open_cache_instances=16

## 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

create a replication user on both nodes
CREATE USER 'replication'@'%' IDENTIFIED BY '[email protected]';
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='[email protected]', 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='[email protected]', 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 [email protected]"%" IDENTIFIED BY '[email protected]' WITH GRANT OPTION;
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.