Twisted IT paired

Sources:
https://www.packtpub.com/books/content/setting-mysql-replication-high-availability
https://www.packtpub.com/books/content/installing-and-managing-multi-master-replication-managermmm-mysql-high-availability
https://capttofu.livejournal.com/1752.html

Using Master<->Master replication is good backup solution, but is not good enough if we want to offload queries from master.

Thus we can create:
Master - Master
| |
Slave-Slave Slave-Slave

1. Setup both masters.
Tweak some options in my.cnf (on all masters!):
server-id = 1
log-slave-updates
log-bin = /var/log/mysql/bin.log
log-bin-index = /usr/local/mysql/var/log-bin.index
log-error = /usr/local/mysql/var/error.log
expire_logs_days = 10
max_binlog_size = 200M

WARNING: log-slave-updates is crucial!!! If not set slaves on second node won't get updated and vice versa if pushed from first master.

2. Add MySQL Users:
mysql> grant replication slave on . to 'replication'@'10.0.0.%' identified by 'pass';
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

3. Dump all DBs from master. SCP dump on slave and import it. This way we will have 1:1 dbs on both nodes. Note that you may set password for debian-sys-maint user in /etc/mysql/debian.cnf

On master:
$> mysqldump --delete-master-logs --master-data --lock-all-tables --all-databases --hex-blob -u root -p > dumpall.sql
$> bzip2 dumpall.sql
$> scp dumpall.sql.bz2 root@slave:

NOTICE: --delete-master-logs clears all master logs BEFORE this dump. If you have other slaves syncin' or need earlier binlogs remove this option!

On slave:
$> bunzip2 dumpall.sql.bz2
$> mysql -uroot -p mysql < dumpall.sql

check BIN_LOG and POSITION:

$> grep BIN_LOG dumpall.sql

now login in mysql and change master to:

mysql> change master to master_host = '10.0.0.1', master_user='replication', master_password='pass', master_log_file='node1-binary.000001', master_log_pos=1;
mysql> start slave;

Check if 2nd Master slave is running. Check seconds behind. Should be 0 and Error_* too. Usually this means everything is OK.
mysql> show slave status\G
mysq> show master status;

Now do the same thing on 1st Master. Just use second master bin log and position.

mysql> change master to master_host = '10.0.0.2', master_user='replication', master_password='pass', master_log_file='node1-binary.000001', master_log_pos=1;
mysql> start slave;

Check if 1st Master slave is running. Check seconds behind. Should be 0 and Error_* too. Usually this means everything is OK.
mysql> show slave status\G

Now test create/insert/update/delete.
First on 1st master create table. Insert a record. Check on 2nd master if table is there and has record.
On second master insert second record. Check on 1st if there are 2 records.

4. Create Read-Only Slaves connected to the 1st master and on 2nd:

Simply do same setup as above. Dump DB. populate, then change master to BUT WATCH OUT for the binlog/position!

When done settiing up and slave status shows 0 TEST!

First create table on 1st master, insert 1 record.
Then Check on all slaves connected to 1st master.
After Check all slaves connected to 2nd master!
All MUST have table+record.
After that test to insert second row on 2nd slave.
Then Check on all slaves connected to 1st master.
After Check all slaves connected to 2nd master!


I think that's all!
Happy replicating.