Steps for MySQL DB Replication

Posted by Manish Agrawal on Geeks with Blogs See other posts from Geeks with Blogs or by Manish Agrawal
Published on Tue, 27 Apr 2010 07:23:01 GMT Indexed on 2010/04/27 7:34 UTC
Read the original article Hit count: 339

Filed under:


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 [email protected]<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:


© Geeks with Blogs or respective owner