Config Mysql 5.5 Replication on Ubuntu 12.04

###MYSQL replication configuration

Prerequisite

sudo apt-get install pv

IP address:
node-1: 11.11.1.27
node-2: 11.11.1.29

MYSQL configuration path/file: /etc/mysql/my.cfg
MYSQL plugins path: /usr/lib/mysql/plugin/

1. Update /etc/mysql/my.cfg

https://gist.github.com/lifuzu/6702922

2. Install semisynchronous replication plugins

mysql -u root -p
INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
quit;
sudo service mysql restart

3. Check these plugins:

SHOW VARIABLES LIKE 'rpl_semi_sync%';
SHOW STATUS LIKE 'Rpl_semi_sync%';

We should make sure the value "Rpl_semi_sync_master_status" and "Rpl_semi_sync_slave_status" should be 'ON'.

4. Lock the master DB, dump it, unlock it

FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000002 |      107 |              |                  |
+------------------+----------+--------------+------------------+

mysqldump --extended-insert=FALSE --complete-insert=TRUE --order-by-primary -u root -p <database> > ~/<database>_09232013.sql

UNLOCK TABLES;

5. Import the DB to slave

gzip <database>_09232013.sql
scp <database>_09232013.sql.gz username@11.11.1.29:~/downloads/<database>_09232013.sql.gz

SWITCH OVER TO DRM-2:

node-2$ gunzip <database>_09232013.sql.gz
#node-2$ mysqladmin -uroot -p create my_database
node-2$ pv <database>_09232013.sql | mysql -uroot -p <database>

6. Grant replication permissions

node-1> GRANT REPLICATION SLAVE ON *.* TO 'replication'@'11.11.1.29' IDENTIFIED BY '<PASSWORD>';
node-1> FLUSH PRIVILEGES;
node-2> GRANT REPLICATION SLAVE ON *.* TO 'replication'@'11.11.1.27' IDENTIFIED BY '<PASSWORD>';
node-2> FLUSH PRIVILEGES;

# REVOKE REPLICATION SLAVE ON *.* FROM 'replication'@'11.11.1.29';
# REVOKE REPLICATION SLAVE ON *.* FROM 'replication'@'11.11.1.27';

7. Check the replication user

node-1> mysql -ureplication -p -h 11.11.1.29 -P 3306
node-2> mysql -ureplication -p -h 11.11.1.27 -P 3306

it is OK if it logins successfully

8. Slave Database B to A

node-2> CHANGE MASTER TO MASTER_HOST='11.11.1.27', MASTER_USER='replication', MASTER_PASSWORD='<PASSWORD>', master_log_file='mysql-bin.000002', master_log_pos=107;

node-2> START SLAVE;
node-2> SHOW SLAVE STATUS\G

9. Check the slave status We should make sure both "Slave_IO_Running" and "Slave_SQL_Running" are 'Yes', and no error in 'Last_IO_Errno' and 'Last_SQL_Error'.

10. Slave Database A to B

node-2> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000002 |      717 |              |                  |
+------------------+----------+--------------+------------------+
node-1> CHANGE MASTER TO MASTER_HOST='11.11.1.27', MASTER_USER='replication', MASTER_PASSWORD='<PASSWORD>', master_log_file='mysql-bin.000002', master_log_pos=717;

node-1> START SLAVE;
node-1> SHOW SLAVE STATUS\G

11. Check the slave staus We should make sure both "Slave_IO_Running" and "Slave_SQL_Running" are 'Yes', and no error in 'Last_IO_Errno' and 'Last_SQL_Error'.

REFERENCES:
0. http://dev.mysql.com/doc/refman/5.0/en/replication-howto.html

1. http://dev.mysql.com/doc/refman/5.5/en/replication-semisync-installation.html
2. http://brendanschwartz.com/post/12702901390/mysql-master-master-replication (ssh tunnel configuration)
3. http://erlycoder.com/43/mysql-master-slave-and-master-master-replication-step-by-step-configuration-instructions- (iptable)
4. http://scale-out-blog.blogspot.com/2012/04/if-you-must-deploy-multi-master.html
5. http://dev.mysql.com/doc/refman/5.5/en/mysql-cluster-replication-multi-master.html