Manish Agrawal

My Experiments with Technology..

  Home  |   Contact  |   Syndication    |   Login
  102 Posts | 2 Stories | 66 Comments | 12 Trackbacks

News


live stats

Domain Name Free Service
Get a free domain name like www.YourName.co.nr 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.


Google




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.

Article Categories

Archives

Post Categories

Image Galleries

Interesting Blogs

Interesting Links

Mobile

SharePoint

Travel Domain

 

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.

[mysqld]

log-bin=mysql-bin

server-id=1

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&

 

 

5.    mysql> FLUSH TABLES WITH READ LOCK;

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:

mysql> UNLOCK TABLES;

 

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

[mysqld]

server-id=2

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;

 

 

 

 

Note:

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

mysql> STOP SLAVE

 

 

Refer following links for more information on MySQL DB Replication:

http://dev.mysql.com/doc/refman/5.0/en/replication-options.html

http://crazytoon.com/2008/04/21/mysql-replication-replicate-by-choice/

http://dev.mysql.com/doc/refman/5.0/en/mysqldump.html

 

posted on Tuesday, April 27, 2010 2:23 AM

Feedback

# re: Steps for MySQL DB Replication 4/28/2010 1:07 AM neways
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.


Post A Comment
Title:
Name:
Email:
Comment:
Verification: