MySQL Master-Master

1. Create config files on both nodes /etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
symbolic-links=0
user=mysql

server-id               = 1  # node 1
#server-id               = 2 # node 2

log-bin                 = mysql-bin.log
binlog-ignore-db        = information_schema
replicate-ignore-db     = information_schema
relay-log               = mysql-relay.log

auto-increment-increment = 2

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

innodb_file_per_table   = 1
2. copy databases to second node
# node1
mysql -u root -p 

# lock all tables
mysql> flush tables with read lock; 
Query OK, 0 rows affected (0.00 sec)

# show status (remember File, Position value)
mysql> show master status; 
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      592 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

# dump databases
mysqldump -u root -p --all-databases --lock-all-tables --events > mysql_dump.sql 

# unlock
mysql> unlock tables; 
Query OK, 0 rows affected (0.00 sec)
mysql> exit
Bye

# transfer the dump to Slave Host
scp mysql_dump.sql node2:/tmp/ 
mysql_dump.sql 100% 515KB 514.7KB/s 00:00

# import dump data sent from Master Host
[[email protected] ~]# mysql -u root -p < /tmp/mysql_dump.sql 

3. configure Master-Master replica
### REPLICA Master-Master

# 1. on both
CREATE USER 'replicator'@'%' IDENTIFIED BY 'replicapassword';
GRANT REPLICATION SLAVE ON *.* TO 'replicator'@'%' IDENTIFIED BY 'replicapassword';

# 2. on node-1
SHOW MASTER STATUS;

# 3. on node-2
CHANGE MASTER TO MASTER_HOST = 'Server A IP Address', MASTER_USER = 'replicator', MASTER_PASSWORD = 'replicapassword', MASTER_LOG_FILE = 'mysql-bin.000001', MASTER_LOG_POS = 107;
START SLAVE;

# 4. repeat #3 on node-1

# 5. check slave status on both
SHOW SLAVE STATUS\G

###
4. Use Keepalived for virtual ip swichover