Geeks With Blogs

live stats

Domain Name Free Service
Get a free domain name like with the following features included: free URL redirection with cloaking, path forwarding, all meta-tags supported, kill-frame feature, NO forced ADS at all, and more.


All content © Manish Agrawal
The content on this site represents my own personal opinions and thoughts at the time of posting, and does not reflect those of my employer's in any way.
Disclaimer:- All postings in this blog is provided "AS IS" with no warranties, and confers no rights.
Manish Agrawal My Experiments with Technology..


Following are the steps for MySQL Replication implementation on Linux machine:

Pre-implementation steps for DB Replication:


1.    Identify the databases to be replicated

2.    Identify the tables to be ignored during replication per database for example log tables

3.  Carefully identify and replace the variables and paths(locations) mentioned (in bold) in the commands given below with appropriate values

4.  Schedule the maintenance activity in odd hours as these activities will affect all the databases on Master database server




Implementation steps for DB Replication:



1.    Configure the /etc/my.cnf file on Master database server to enable Binary logging, setting of server id and configuring of dbnames for which logging should be done.




binlog-do-db = dbname


Note: You can specify multiple DB in binlog-do-db by using comma separated dbname values like: dbname1, dbname2, …, dbnameN


2.    On Master database, Grant Replication Slave Privileges, by executing following command on mysql prompt

mysql> GRANT REPLICATION SLAVE ON *.* TO slaveuser@<hostname> identified by ‘slavepassword’;


3.    Stop the Master & Slave database by giving the command

     mysqladmin shutdown


4.    Start the Master database by giving the command

     /usr/local/mysql-5.0.22/bin/mysqld_safe --user=user&




Note: Leave the client (putty session) from which you issued the FLUSH TABLES statement running, so that the read lock remains in effect. If you exit the client, the lock is released.

6.    mysql > SHOW MASTER STATUS;


         | File          | Position | Binlog_Do_DB | Binlog_Ignore_DB |


         | mysql-bin.003 | 117       | dbname       |                  |


Note: Note this information as this will be required while starting of Slave and replication in later steps


7.    Take MySQL dump by giving the following command, In another session window (putty window) run the following command:

mysqldump –u user --ignore-table=dbname.tbl_name -–ignore-table=dbname.tbl_name2 --master-data dbname > dbname_dump.db

Note: When choosing databases to include in the dump, remember that you will need to filter out databases on each slave that you do not want to include in the replication process.



8.    Unlock the tables on Master by giving following command:



9.    Copy the dump file to Slave DB server


10.  Startup the Slave by using option --skip-slave

     /usr/local/mysql-5.0.22/bin/mysqld_safe --user=user --skip-slave&


11.  Restore the dump file on Slave DB server

     mysql –u user dbname < dbname_dump.db


12.  Stop the Slave database by giving the command

     mysqladmin shutdown


13.  Configure the /etc/my.cnf file on the Slave database server



replicate-ignore-table = dbname.tablename


14.  Start the Slave Mysql Server with 'replicate-do-db=DB name' option.

     /usr/local/mysql-5.0.22/bin/mysqld_safe --user=user --replicate-do-db=dbname --skip-slave


15.  Configure the settings at Slave server for Master host name, log filename and position within the log file as shown in Step 6 above

Use Change Master statement in the MySQL session

mysql> CHANGE MASTER TO MASTER_HOST='<master_host_name>', MASTER_USER='<replication_user_name>', MASTER_PASSWORD='<replication_password>', MASTER_LOG_FILE='<recorded_log_file_name>', MASTER_LOG_POS=<recorded_log_position>;

16.  On Slave Servers mysql prompt give the following command:

a.     mysql > START SLAVE;

b.    mysql > SHOW SLAVE STATUS;






To stop slave for backup or any other activity you can use the following command on the Slave Servers mysql prompt:




Refer following links for more information on MySQL DB Replication:


Posted on Tuesday, April 27, 2010 2:23 AM Technology Articles | Back to top

Comments on this post: Steps for MySQL DB Replication

# re: Steps for MySQL DB Replication
Requesting Gravatar...
Your blog provided us with valuable information to work with.Each & every tips of your post are awesome.Thanks a lot for sharing.Keep blogging.
Left by neways on Apr 28, 2010 1:07 AM

Your comment:
 (will show your gravatar)

Copyright © Manish Agrawal | Powered by: