Kunaljain's Weblog

October 29, 2007

Implementing Replication

Filed under: MySql — Kunal Jain @ 16:47

There are only few steps to implement replication. The first step is to set up a user account to use only for replication

GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'repluser'@'slave_host' IDENTIFIED BY 'password'

In this sql statement the user account repluser is granted only what need for replication the host name is the HostName or IP Address of Slave Server. You can enter the statement on the slave server but with the Master HostName or IP Address.

****Configuring The Master Serve****

Open the file my.cnf which is usually located in /etc/my.cnf and add the following lines to the configuration file

log-bin = <path for Log_File>

binlog-do-db = db_test

server-id = 1

****db_test is the name of the Database we replicate on slave server

****Restart MySql

/etc/init.d/mysql restart

****Now on MySql shell check the Master Status

mysql>SHOW MASTER STATUS\G;

****SHOW MASTER STATUS shows output like

*************************** 1. row ***************************
File: log-bin.000002
Position: 79
Binlog_Do_DB: db_test
Binlog_Ignore_DB:
1 row in set (0.00 sec)

mysql>quit;

****Take the backup of your DataBase. On the shell prompt type

#mysql[data-dir]/bin/mysqldump -u <UserName> -p<Password> <Database Name> > backup.sql

and copy the backup.sql to the slave server with the following command

#scp backup.sql slave@hostname:~/backupfiles/

with this command backup.sql file is copied to backupfiles folder on slave server

****Configuring the slave server

After creating the user for replication as said above create the database in which you load the backup file of the master serve so we firstly create the database. On MySql prompt type

mysql>create database db_test

****Remember First db_test is on the Master Server and here we created a new Database. You can give it any name Same name helps in remembering which database is replicating.

mysql>show databases;

show you the list of all the databases.

mysql>quit;

Now on shell prompt open my.cnf file and add the following changes

server-id = 2

save the file and load the database of Master Server in the newly created Database on slave server with the help of following command

#mysql[data-dir]/bin/mysql -u <UserName> -p<password> <Database Name> < path to backup.sql file

****In this command Database Name is db_test which we created on slave server

****Now execute the following statement on the slave servers with the actual values

mysql> CHANGE MASTER TO MASTER_HOST = 'Master_HostName/IP Address',
-> MASTER_USER = 'Replicant UserName',
-> MASTER_PASSWORD = 'Password',
-> MASTER_LOG_FILE = 'Master Log File Name' ,
->  MASTER_LOG_POS = 'Master Log  Position';

NOTE:- Master_HostName -> is the Master Server IP Address or HostName

Master_user -> Replicant User Name which is repluser in my case

Master_password -> Password of the replicant user(repluser) which is password in my case

Master_Log_File -> show master status command gives you the Log File name

Master_Log_Pos -> show master status command gives you the Log Position

Now Restart your Mysql

#/etc/init.d/mysql restart

and also start slave by typing the following command on mysql prompt

mysql>START SLAVE;

****You can check the status of slave server by typing the following command on slave server

mysql> show slave status;

If this configuration doesn’t work then add the followling lines in my.cnf file on slave server with actual values

server-id = 2

master-host=master_host_name/master_IP_Address

master-user = repluser

master-password = password

master-connect-retry = 100

replicate-do-db = db_test

log-bin = /var/log/mysql/lob-bin.log

log-bin-index = /var/log/mysql/log-bin.index

log-error = /var/log/mysql/log-error.log

relay-log = /var/log/mysql/relay-log.log

relay-log-info-file = /var/log/mysql/relay-log.info

relay-log-index = /var/log/mysql/relay-log.index

****Now again restart your Mysql and also slave Server the check the salve status by issued the show slave status command

Advertisements

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: