We have 2 servers, one of which is a Master and the other which is a Slave. We tell the Master that it should keep a log of every action performed on it. We tell the slave server that it should look at this log on the Master and whenever something new happens, it should do the same thing.
You should follow the instructions below with two console windows open - one for the Master and one for the Slave.
Step 1:
First of all, we need to create a user on the Master server that the Slave will connect as. Say 'slave_user'. Log into mysql as root and create the user:
-- ================================================================================
GRANT REPLICATION SLAVE ON *.* TO 'slave_user'@'%' IDENTIFIED BY 'your_password';
FLUSH PRIVILEGES;
-- ================================================================================
Step 2:
Now, we should edit the my.cnf file (usually in /etc/my.cnf), in the [mysqld] section and tell MySQL that it's going to be a Master:
-- ================================================================================
log-bin = /home/mysql/logs/mysql-bin.log
binlog-do-db=replication_test_master
server-id=1
-- ================================================================================
The first line tells MySQL to start writing a log, and tells it where to write the log. Make sure this directory is empty of all replication logs, especially if you're starting again after replication has already been used.
The second line chooses the database to write the log for. You should change this to your database. The third line gives the server an ID (to distinguish it from the Slave).
You should also make sure skip-networking has not been enabled.
Step 3:
You should now restart the Master:
-- ================================================================================
/etc/init.d/mysql restart
-- ================================================================================
Step 4:
Again, we should change the /etc/my.cnf of the Slave server, in the [mysqld] section:
-- ================================================================================
server-id=2
master-host=10.90.50.62
master-connect-retry=60
master-user=slave_user
master-password=nn@123
replicate-do-db=replication_test_master
relay-log = /var/lib/mysql/slave-relay.log
relay-log-index = /var/lib/mysql/slave-relay-log.index
-- ================================================================================
Line 1 gives the Slave its unique ID. Line 2, tells the Slave the I.P address of the Master server - so you need to change the I.P here.
The remaining lines set a retry limit, and tell the Slave the user, password and database it needs to replicate. We also tell the slave what to use as its relay log. It's best to set this directly, or MySQL will create the name from the hostname and should you change hostname, replication will fail.
You should also make sure skip-networking has not been enabled.
Step 5:
You should now restart the Slave:
-- ================================================================================
/etc/init.d/mysql restart
-- ================================================================================
===============================
Getting the data onto the Slave
===============================
On the Master...
I'm assuming you have a live Master server, and an as yet empty Slave server. This stage depends on whether data is constantly being added to the Master. If so, we will have to prevent all database access on the Master so nothing can be added. This means your server will hang during the next step. If no data is being added to the server, you can skip this step. On the Master server, log into MySQL and do the following:
Step 6:
mysql -u root -p (log into MySQL)
-- ================================================================================
FLUSH TABLES WITH READ LOCK;
-- ================================================================================
Step 7:
Now we will use mysqldump to get the data out. So, still on the Master server:
-- ================================================================================
mysqldump replication_test_master -u root -p > /var/tmp/replication_test_master.sql;
gzip /var/tmp/replication_test_master.sql;
-- ================================================================================
Make sure you change my_database to your database name, and my_home_dir to the name of your home directory (or another directory of your choosing). You wll now have a file called database.sql.gz in your home directory. This is a gziped copy of your database.
On the Slave...
Step 8:
Now we need to copy over the gzipped file. On the Slave run the following:
-- ================================================================================
scp root@10.90.50.62:/var/tmp/replication_test_master.sql.gz /var/tmp/
-- ================================================================================
Make sure 128.0.0.1 is the I.P of the Master. This will copy the file from the Master and put it in your home directory on the Slave. Now we just need to import into MySQL:
Step 9:
mysql -u root -p (log into MySQL)
-- ================================================================================
CREATE DATABASE replication_test_master;
-- ================================================================================
Step 10:
-- ================================================================================
gunzip /var/tmp/replication_test_master.sql.gz
mysql -u root -p replication_test_master </var/tmp/replication_test_master.sql
-- ================================================================================
Lets ready...
On the Master...
Now we're ready to kick things off. We need to find the position the Master is at in the logs. So, log into MySQL and run the following:
Step 11:
mysql -u root -p (log into MySQL)
-- ================================================================================
SHOW MASTER STATUS;
-- ================================================================================
+------------------+----------+-------------------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+-------------------------+------------------+
| mysql-bin.000002 | 106 | replication_test_master | |
+------------------+----------+-------------------------+------------------+
Keep that on-screen.
On the Slave...
Step 12:
Log into MySQL and do the following:
mysql -u root -p (log into MySQL)
-- ================================================================================
slave stop;
CHANGE MASTER TO MASTER_HOST='10.90.50.62', MASTER_USER='slave_user', MASTER_PASSWORD='nn@123', MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=106;
slave start;
-- ================================================================================
First we stop the Slave. Then we tell it exactly where to look in the Master log file. We use the values for our previous SHOW MASTER STATUS; command on the Master. You should change 128.0.0.1 to the I.P of the Master, and change the user and password accordingly.
The Slave will now be waiting. So all that's left is to...
Back on the Master...
Step 13:
We should already be logged into MySQL, so all you have to do is:
-- ================================================================================
unlock tables;
-- ================================================================================
To release the tables from lock. Note you only have to do this if you previously ran FLUSH TABLES WITH READ LOCK;
=============================
Checking Replication is Working
=============================
Step 14:
On the master server, issue the following command to see the master status:
mysql -u root -p (log into MySQL)
-- ================================================================================
SHOW MASTER STATUS;
-- ================================================================================
+------------------+----------+-------------------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+-------------------------+------------------+
| mysql-bin.000002 | 106 | replication_test_master | |
+------------------+----------+-------------------------+------------------+
Step 15:
On the backup server, issue the following command to see the slave status:
-- ================================================================================
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.90.50.62
Master_User: slave_user
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000002
Read_Master_Log_Pos: 823
Relay_Log_File: slave-relay.000002
Relay_Log_Pos: 968
Relay_Master_Log_File: mysql-bin.000002
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: replication_test_master
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: 823
Relay_Log_Space: 1119
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
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
1 row in set (0.00 sec)
-- ================================================================================
==================
Test replication:
==================
Make changes on master tables and see that changes should reflect in slave as well.
lets test it on table "category_master_list_new"
Step 16:
Getting count of records in table category_master_list_new on master
(root@localhost) Wed Oct 27 13:00:42 2010 [replication_test_master]>select count(1) from category_master_list_new;
+----------+
| count(1) |
+----------+
| 8969 |
+----------+
1 row in set (0.00 sec)
Step 17:
Getting count of records in table category_master_list_new on slave
mysql> select count(1) from category_master_list_new;
+----------+
| count(1) |
+----------+
| 8969 |
+----------+
1 row in set (0.00 sec)
Step 18:
Now i am inserting two records in category_master_list_new on master
(root@localhost) Wed Oct 27 13:24:36 2010 [replication_test_master]>insert into category_master_list_new(tagid,tagname,parent_id,is_primary,is_secondary) select tagid,tagname,parent_id,is_primary,is_secondary from category_master_list_new limit 2;
Query OK, 2 rows affected, 0 warning (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
Step 19:
Lets check the count of table category_master_list_new on master and slave
On Master
(root@localhost) Wed Oct 27 13:28:04 2010 [replication_test_master]>select count(1) from category_master_list_new;
+----------+
| count(1) |
+----------+
| 8971 |
+----------+
1 row in set (0.00 sec)
Step 20:
On Slave
mysql> select count(1) from category_master_list_new;
+----------+
| count(1) |
+----------+
| 8971 |
+----------+
1 row in set (0.00 sec)
See the two record we have inserted in master is been reflected on slave also.
Yay!!!! its working fine.
You should follow the instructions below with two console windows open - one for the Master and one for the Slave.
Step 1:
First of all, we need to create a user on the Master server that the Slave will connect as. Say 'slave_user'. Log into mysql as root and create the user:
-- ================================================================================
GRANT REPLICATION SLAVE ON *.* TO 'slave_user'@'%' IDENTIFIED BY 'your_password';
FLUSH PRIVILEGES;
-- ================================================================================
Step 2:
Now, we should edit the my.cnf file (usually in /etc/my.cnf), in the [mysqld] section and tell MySQL that it's going to be a Master:
-- ================================================================================
log-bin = /home/mysql/logs/mysql-bin.log
binlog-do-db=replication_test_master
server-id=1
-- ================================================================================
The first line tells MySQL to start writing a log, and tells it where to write the log. Make sure this directory is empty of all replication logs, especially if you're starting again after replication has already been used.
The second line chooses the database to write the log for. You should change this to your database. The third line gives the server an ID (to distinguish it from the Slave).
You should also make sure skip-networking has not been enabled.
Step 3:
You should now restart the Master:
-- ================================================================================
/etc/init.d/mysql restart
-- ================================================================================
Step 4:
Again, we should change the /etc/my.cnf of the Slave server, in the [mysqld] section:
-- ================================================================================
server-id=2
master-host=10.90.50.62
master-connect-retry=60
master-user=slave_user
master-password=nn@123
replicate-do-db=replication_test_master
relay-log = /var/lib/mysql/slave-relay.log
relay-log-index = /var/lib/mysql/slave-relay-log.index
-- ================================================================================
Line 1 gives the Slave its unique ID. Line 2, tells the Slave the I.P address of the Master server - so you need to change the I.P here.
The remaining lines set a retry limit, and tell the Slave the user, password and database it needs to replicate. We also tell the slave what to use as its relay log. It's best to set this directly, or MySQL will create the name from the hostname and should you change hostname, replication will fail.
You should also make sure skip-networking has not been enabled.
Step 5:
You should now restart the Slave:
-- ================================================================================
/etc/init.d/mysql restart
-- ================================================================================
===============================
Getting the data onto the Slave
===============================
On the Master...
I'm assuming you have a live Master server, and an as yet empty Slave server. This stage depends on whether data is constantly being added to the Master. If so, we will have to prevent all database access on the Master so nothing can be added. This means your server will hang during the next step. If no data is being added to the server, you can skip this step. On the Master server, log into MySQL and do the following:
Step 6:
mysql -u root -p (log into MySQL)
-- ================================================================================
FLUSH TABLES WITH READ LOCK;
-- ================================================================================
Step 7:
Now we will use mysqldump to get the data out. So, still on the Master server:
-- ================================================================================
mysqldump replication_test_master -u root -p > /var/tmp/replication_test_master.sql;
gzip /var/tmp/replication_test_master.sql;
-- ================================================================================
Make sure you change my_database to your database name, and my_home_dir to the name of your home directory (or another directory of your choosing). You wll now have a file called database.sql.gz in your home directory. This is a gziped copy of your database.
On the Slave...
Step 8:
Now we need to copy over the gzipped file. On the Slave run the following:
-- ================================================================================
scp root@10.90.50.62:/var/tmp/replication_test_master.sql.gz /var/tmp/
-- ================================================================================
Make sure 128.0.0.1 is the I.P of the Master. This will copy the file from the Master and put it in your home directory on the Slave. Now we just need to import into MySQL:
Step 9:
mysql -u root -p (log into MySQL)
-- ================================================================================
CREATE DATABASE replication_test_master;
-- ================================================================================
Step 10:
-- ================================================================================
gunzip /var/tmp/replication_test_master.sql.gz
mysql -u root -p replication_test_master </var/tmp/replication_test_master.sql
-- ================================================================================
Lets ready...
On the Master...
Now we're ready to kick things off. We need to find the position the Master is at in the logs. So, log into MySQL and run the following:
Step 11:
mysql -u root -p (log into MySQL)
-- ================================================================================
SHOW MASTER STATUS;
-- ================================================================================
+------------------+----------+-------------------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+-------------------------+------------------+
| mysql-bin.000002 | 106 | replication_test_master | |
+------------------+----------+-------------------------+------------------+
Keep that on-screen.
On the Slave...
Step 12:
Log into MySQL and do the following:
mysql -u root -p (log into MySQL)
-- ================================================================================
slave stop;
CHANGE MASTER TO MASTER_HOST='10.90.50.62', MASTER_USER='slave_user', MASTER_PASSWORD='nn@123', MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=106;
slave start;
-- ================================================================================
First we stop the Slave. Then we tell it exactly where to look in the Master log file. We use the values for our previous SHOW MASTER STATUS; command on the Master. You should change 128.0.0.1 to the I.P of the Master, and change the user and password accordingly.
The Slave will now be waiting. So all that's left is to...
Back on the Master...
Step 13:
We should already be logged into MySQL, so all you have to do is:
-- ================================================================================
unlock tables;
-- ================================================================================
To release the tables from lock. Note you only have to do this if you previously ran FLUSH TABLES WITH READ LOCK;
=============================
Checking Replication is Working
=============================
Step 14:
On the master server, issue the following command to see the master status:
mysql -u root -p (log into MySQL)
-- ================================================================================
SHOW MASTER STATUS;
-- ================================================================================
+------------------+----------+-------------------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+-------------------------+------------------+
| mysql-bin.000002 | 106 | replication_test_master | |
+------------------+----------+-------------------------+------------------+
Step 15:
On the backup server, issue the following command to see the slave status:
-- ================================================================================
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.90.50.62
Master_User: slave_user
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000002
Read_Master_Log_Pos: 823
Relay_Log_File: slave-relay.000002
Relay_Log_Pos: 968
Relay_Master_Log_File: mysql-bin.000002
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: replication_test_master
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: 823
Relay_Log_Space: 1119
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
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
1 row in set (0.00 sec)
-- ================================================================================
==================
Test replication:
==================
Make changes on master tables and see that changes should reflect in slave as well.
lets test it on table "category_master_list_new"
Step 16:
Getting count of records in table category_master_list_new on master
(root@localhost) Wed Oct 27 13:00:42 2010 [replication_test_master]>select count(1) from category_master_list_new;
+----------+
| count(1) |
+----------+
| 8969 |
+----------+
1 row in set (0.00 sec)
Step 17:
Getting count of records in table category_master_list_new on slave
mysql> select count(1) from category_master_list_new;
+----------+
| count(1) |
+----------+
| 8969 |
+----------+
1 row in set (0.00 sec)
Step 18:
Now i am inserting two records in category_master_list_new on master
(root@localhost) Wed Oct 27 13:24:36 2010 [replication_test_master]>insert into category_master_list_new(tagid,tagname,parent_id,is_primary,is_secondary) select tagid,tagname,parent_id,is_primary,is_secondary from category_master_list_new limit 2;
Query OK, 2 rows affected, 0 warning (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
Step 19:
Lets check the count of table category_master_list_new on master and slave
On Master
(root@localhost) Wed Oct 27 13:28:04 2010 [replication_test_master]>select count(1) from category_master_list_new;
+----------+
| count(1) |
+----------+
| 8971 |
+----------+
1 row in set (0.00 sec)
Step 20:
On Slave
mysql> select count(1) from category_master_list_new;
+----------+
| count(1) |
+----------+
| 8971 |
+----------+
1 row in set (0.00 sec)
See the two record we have inserted in master is been reflected on slave also.
Yay!!!! its working fine.
Posts
