Derwynd’s Weblog

A) Master –> Slave

MASTER
-=-=-=-

mysql> GRANT REPLICATION SLAVE ON *.* TO ’slave_user’@'%’ IDENTIFIED BY ’slavepass’;
mysql> flush privileges;
mysql> CREATE DATABASE exampledb;

In my.cnf of Master

[mysqld]
log-bin=mysql-bin
server-id=1
max_allowed_packet=16M
binlog-do-db=exampledb

dump the database and scp to slave
mysqldump -u root –opt exampledb > /tmp/exampledb.sql

Slave
-=-=-=-

mysql> CREATE DATABASE exampledb;

Import database
mysql -u root exampledb < /tmp/exampledb.sql

In my.cnf of Slave
[mysqld]
server-id=2
master-host     =   192.168.50.81 #Master IP
master-user     =  slave_user
master-password =   slavepass
master-connect-retry= 60
max_allowed_packet=16M
replicate-do-db=exampledb
log-warnings

On Both servers restart the mysql

Test by on Master

mysql> show master status;
+——————+———-+————–+——————+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+——————+———-+————–+——————+
| mysql-bin.000002 |      270 | exampledb    |                  |
+——————+———-+————–+——————+
1 row in set (0.00 sec)

Test by on Slave
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.50.81
Master_User: slave_user
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000002
Read_Master_Log_Pos: 270
Relay_Log_File: mysqlslav-relay-bin.000006
Relay_Log_Pos: 407
Relay_Master_Log_File: mysql-bin.000002
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: exampledb
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 270
Relay_Log_Space: 407
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
1 row in set (0.00 sec)

On Master
mysql> use exampledb;
mysql> CREATE TABLE test ( `field1` varchar(8) NOT NULL default ”, `field2` tinyint(4) unsigned default NULL );

On Slave
mysql> use exampledb;
mysql> show tables;
+———————+
| Tables_in_exampledb |
+———————+
| test                |
+———————+
1 row in set (0.00 sec)
B) MASTER <—-> Master

On the
server
Under the mysqld section add
[mysqld]

# let’s make it so auto increment columns behave by having different increments on both servers
auto_increment_increment=2
auto_increment_offset=1
# Replication Master Server
# binary logging is required for replication
log-bin=master1-bin
binlog-ignore-db=mysql
binlog-ignore-db=test
# required unique id between 1 and 2^32 – 1
server-id = 1
#following is the slave settings so this server can connect to master2
master-host = 192.168.50.82
master-user = slave_user
master-password = slavepass
master-port = 3306

mysql> GRANT REPLICATION SLAVE ON *.* TO ’slave_user’@'192.168.50.82′ IDENTIFIED BY ’slavepass’;
mysql> flush privileges;

On the 192.168.50.82 server
Under the mysqld section add
[mysqld]

# let’s make it so auto increment columns behave by having different increments on both servers
auto_increment_increment=2
auto_increment_offset=2
# Replication Master Server
# binary logging is required for replication
log-bin=master2-bin
binlog-ignore-db=mysql
binlog-ignore-db=test
# required unique id between 1 and 2^32 – 1
server-id = 2
#following is the slave settings so this server can connect to master2
master-host = 192.168.50.81
master-user = slave_user
master-password = slavepass
master-port = 3306

mysql> GRANT REPLICATION SLAVE ON *.* TO ’slave_user’@'192.168.50.81′ IDENTIFIED BY ’slavepass’;
mysql> flush privileges;

Restart Mysql on both servers;
Check by creating database on one server
ie

on 192.168.50.81

mysql> create database exampledb;
mysql> show databases;
+——————–+
| Database           |
+——————–+
| information_schema |
| exampledb          |
| mysql              |
| test               |
+——————–+

on 192.168.50.82
mysql> show databases;
+——————–+
| Database           |
+——————–+
| information_schema |
| exampledb          |
| mysql              |
| test               |
+——————–+

mysql> use exampledb;
mysql> CREATE TABLE test ( `field1` varchar(8) NOT NULL default ”, `field2` tinyint(4) unsigned default NULL );

on 192.168.50.81
mysql> show tables;
+———————+
| Tables_in_exampledb |
+———————+
| test                |
+———————+
1 row in set (0.00 sec)

Restarting Replication if broken

Get latest mysql dump file from master and replicate on slave

on the master run
show master status;

Take the File and position values and run on slave

  stop slave;
  CHANGE MASTER TO MASTER_HOST='192.168.50.81',
  MASTER_USER='slave_user',
  MASTER_PASSWORD='password',
  MASTER_LOG_FILE='mail2-bin.00002',
  MASTER_LOG_POS=37;
  start slave;

Replace the filename and position with actual ones

restart mysql on slave

Multiple Replication

To set up replication like

 A --> B --> c

Follow the same steps to set up replication on the C server from B
Edit the my.cnf file on B server and add to what is already present

log-warnings
log-bin=mysql-bin
max_allowed_packet=16M
binlog-do-db=mail
log-slave-updates
sync-binlog=1
relay-log=<hostname>-relay-bin

log-slave-updates -- This is for the slave to write to it's log file so that C server can read the logs

On server C along with the master slave configuration add this

log-warnings
log-bin=mysql-bin
max_allowed_packet=16M
binlog-do-db=mail
sync-binlog=1
relay-log=<hostname>-relay-bin
slave_net_timeout=30
master-connect-retry=10
slave-skip-errors

slave_net_timeout -- The default for the slave_net_timeout setting is 3600,
which is 60 minutes. I've set this to 30.
It also removes a step in resuming replication of "STOP SLAVE; START SLAVE" 

master-connect-retry -- The number of seconds the slave thread will sleep before retrying
to connect to the master in case the master goes down or the connection is lost. Default is 60.