Thursday, April 23, 2015

Mysql Master-Master Replication

This site[1] though me how to do the replication.

[1]
https://www.digitalocean.com/community/tutorials/how-to-set-up-mysql-master-master-replication

Steps
MasterA (192.168.56.110)
MasterB (192.168.56.111)
1
# yum install mysql-server mysql-client vim ntpdate
# yum install mysql-server mysql-client vim ntpdate
2
# ntpdate ntp.org
# ntpdate ntp.org
3
# service mysqld restart
# service mysqld restart
4
# /usr/bin/mysqladmin -u root password 'mapass'
# /usr/bin/mysqladmin -u root password 'mbpass'
5
# vim /etc/my.cnf
#--------------------------------------
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

server-id              = 1
log_bin                = /var/log/mysql/mysql-bin.log
binlog_do_db           = example
bind-address           = 192.168.56.110

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
#--------------------------------------
# mkdir -p /var/log/mysql/
# chown mysql /var/log/mysql/
# service mysqld restart
# vim /etc/my.cnf
#--------------------------------------
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

server-id              = 2
log_bin                = /var/log/mysql/mysql-bin.log
binlog_do_db           = example
bind-address           = 192.168.56.111

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
#--------------------------------------
# mkdir -p /var/log/mysql/
# chown mysql /var/log/mysql/
# service mysqld restart
6
# netstat -antlp | grep 3306
tcp 0 0 192.168.56.110:3306 0.0.0.0:* LISTEN      1655/mysqld
# netstat -antlp | grep 3306
tcp 0 0 192.168.56.111:3306 0.0.0.0:* LISTEN      1558/mysqld
7
# mysql -u root –p
Mapass

8
mysql> show databases;

mysql> create user 'replicator'@'%' identified by 'rapass';
Query OK, 0 rows affected (0.00 sec)

mysql> grant replication slave on *.* to 'replicator'@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> show master status \G;
            File: mysql-bin.000001
        Position: 341
mysql>

9
We have to use the mysql-bin.000001 and position of 341 over there in masterB server.
mysql> show databases;

mysql> create user 'replicator'@'%' identified by 'rbpass';
Query OK, 0 rows affected (0.00 sec)

mysql> grant replication slave on *.* to 'replicator'@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> show master status \G;
            File: mysql-bin.000001
        Position: 341

mysql> slave stop;

mysql> CHANGE MASTER TO MASTER_HOST = '192.168.56.110', MASTER_USER = 'replicator', MASTER_PASSWORD = 'rapass', MASTER_LOG_FILE = 'mysql-bin.000001', MASTER_LOG_POS = 341;

mysql > slave start;
10
mysql> slave stop;

mysql> CHANGE MASTER TO MASTER_HOST = '192.168.56.111', MASTER_USER = 'replicator', MASTER_PASSWORD = 'rbpass', MASTER_LOG_FILE = 'mysql-bin.000001', MASTER_LOG_POS = 341;

mysql > slave start;

11
Mysql > \! iptables -I INPUT 1 -p tcp --dport 3306 -j ACCEPT
Mysql > \! /sbin/service iptables save
Mysql > \! iptables -I INPUT 1 -p tcp --dport 3306 -j ACCEPT
Mysql > \! /sbin/service iptables save
12
Mysql > create database example;


13

This should create a DB called example here too.
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| example            |
| mysql              |
| test               |
+--------------------+
4 rows in set (0.00 sec)
14
mysql> create table example.Bala (`id` varchar(10));

15
mysql> show tables in example;
+-------------------+
| Tables_in_example |
+-------------------+
| Bala              |
+-------------------+
1 row in set (0.00 sec)
mysql> show tables in example;
+-------------------+
| Tables_in_example |
+-------------------+
| Bala              |
+-------------------+
1 row in set (0.00 sec)
16
The following command will takeout Bala table from example DB on both the servers.
mysql> DROP tables example.Bala;
Query OK, 0 rows affected (0.00 sec)