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.
Sorry, the comment form is closed at this time.
-
Recent
- Set Time Zone + Fedora 18
- Fedora 17 + Tata Photon (Huawei)
- Fedora 17: Install OpenSSH Server
- Change the default keyring password
- Error: Could not stat() command file ‘/usr/local/nagios/var/rw/nagios.cmd’!
- Gnone 3.X adding personal launcher
- Gnome 3.x Change Panel
- KDE remove autostart programs
- GPS on Linux
- Debian Squeeze change root password
- Download YouTube Videos on Linux
- Pidgin + The certificate for omega.contacts.msn.com could not be validated
-
Links
-
Archives
- April 2013 (1)
- September 2012 (2)
- August 2012 (2)
- July 2012 (2)
- November 2011 (1)
- July 2011 (1)
- April 2011 (1)
- January 2011 (1)
- November 2010 (1)
- September 2010 (2)
- July 2010 (3)
- March 2010 (1)
-
Categories
-
RSS
Entries RSS
Comments RSS