MySQL - Master to Passive Master Replication

This replication is only working behind HAproxy!

The manual needs to be followed word by word...
as usual ... no warranties ...

Set-up:

on both server simultaneously

install MySQL directly from download.mysql.com
copy a my.conf from gitlab in place
create the following:
mkdir -p /data/mysql
mkdir -p /data/log
chmod 750 /data/mysql /data/log
chown -R mysql:mysql /data/mysql /data/log
mkdir -p /var/run/mysqld
chown mysql:mysql /var/run/mysqld
mkdir -p /var/log/mysqld
chown mysql:mysql /var/log/mysqld
ln -s  /var/run/mysqld/mysql.sock /var/lib/mysql/mysql.sock
run as root:   mysql_install_db
check this is empty:   /var/log/mysqld/mysql.err
run as root:   systemctl start mysqld
check the logs in  /var/log/mysqld  are identical
run as root:   mysql -p   (no password)
set-up the main database:   use mysql;
then run this script:
use mysql;
delete from user where user='';
delete from user where user='root' and host not in ('localhost');
update user set password=PASSWORD("xxx") where user='root';
drop database IF EXISTS test;
delete from db where db='test' or db='test\\_%';
create user 'addi'@'%' identified by 'xxx';
create user 'iang'@'%' identified by 'xxx';
create user 'nagios'@'%' identified by 'read_only';
grant ALL on *.* to 'addi'@'%';
grant ALL on *.* to 'iang'@'%';
grant SUPER on *.* to 'addi'@'%';
grant SUPER on *.* to 'iang'@'%';
grant GRANT OPTION on *.* to 'addi'@'%';
grant GRANT OPTION on *.* to 'iang'@'%';
grant SELECT on *.* to 'nagios'@'%';
grant PROCESS on *.* to 'nagios'@'%';
grant REPLICATION CLIENT on *.* to 'nagios'@'%';
GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'replicator'@'%' IDENTIFIED BY 'xxx';
flush privileges;
select user,host,password from user;

now to the wicket-tricky stuff
replication set-up
check that this is identical on both servers:  
show variables where Variable_Name IN ("server_id", "log_bin", "relay_log", "log_slave_updates");
show master status;
log in from A to B:   mysql -h db-02 -u replicator -p****
log in from B to A:   mysql -h db-01 -u replicator -p****
fix it or restart if something does not work !!!

add the replication details:
CHANGE MASTER TO MASTER_HOST='<the other server>',
-> MASTER_USER='replicator',
-> MASTER_PASSWORD='***',
-> MASTER_LOG_FILE='mysql-bin.<file from master status>',
-> MASTER_LOG_POS=<pos from master status>;
on second server first:
SLAVE START;
SHOW SLAVE STATUS\G
ensure NO errors !!!

on first server:
SLAVE START;
SHOW SLAVE STATUS\G
ensure NO errors !!!

Checking the Replication.

from the first server:
create this:
create database important;
use important;
create table stuff(
id int(16) auto_increment,
details varchar(200),
PRIMARY KEY (id));
now do this:   INSERT INTO important.stuff SET details='Gift from A to B';

on the second server:
check this:
select * from important.stuff\G
you see:   'Gift from A to B

now do this on the second server:
INSERT INTO important.stuff SET details='Gift from B to A';

back on the first server:
check this:
select * from important.stuff\G
you see the 2nd record:   'Gift from B to A


The Replication is working successfully if you haven't seen any errors  !!!


enjoy life !!!
Addi