Derwynd's Weblog

MYSQL REPLICATION

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.

November 10, 2008 - Posted by derwynd | Uncategorized