To Gain Knowledge and to become a one, who all wants to become

Dreams are not those which are seen during sleep ,but Dreams are those which do not let you sleep,so see the dreams
and work hard to make them true.

Friday, September 17, 2010

Master -Slave Replication of Mysql Server.

Step 1: Setup Master configuration file properties

Open my.cnf file
1
vi /etc/my.cnf

.

And comment lines like below
1
2
#skip-networking
 #bind-address = 127.0.0.1

.

we have to tell MySQL for which database it should write logs (these logs are used by the slave to see what has changed on the master)
1
2
3
log-bin = /var/lib/mysql/mysql-bin.log
 binlog-do-db=database_name_to_replication
 server-id=1

.

restart mysql
1
service mysqld restart

.

Step 2: Setup Master configuration for replication user

logon to mysql server
1
mysql

.

create an user with “replication slave on” privileges
1
2
3
4
5
GRANT REPLICATION SLAVE ON *.* to 'replication_user'@'%' IDENTIFIED BY 'password';
FLUSH PRIVILEGES;
GRANT RELOAD ON *.* TO 'replication_user'@'%'
GRANT SUPER ON *.* TO 'replication_user'@'%'
FLUSH PRIVILEGES;

.

Flush tables in our database:
1
2
3
4
USE database_name_to_replication;
FLUSH TABLES WITH READ LOCK;
 
SHOW MASTER STATUS;

.

you will see like this:-
1
2
3
4
5
6
7
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000010 |      106 | db to replicate| mysql            | 
+------------------+----------+--------------+------------------+
1 row in set (0.01 sec)
==============================================

.

Step 3: Setup Slave configuration file

Open my.cnf file
1
vi /etc/my.cnf

.

And commands lines like below
1
2
3
4
5
6
7
server-id=2
 master-host = host_to_master_server
 master-user = replication_user
 master-password = password
 master-port = 3306
 master-connect-retry=60
 replicate-do-db=database_name_to_replication

.

Connect to MySql
1
mysql

.

1
CREATE DATABASE database_name_to_replication;

.

Restart mysql
1
service mysqld restart

.

Run below commands:-
1
2
3
4
STOP SLAVE;
RESET SLAVE;
START SLAVE;
SHOW SLAVE STATUS;

.

the output will be somthing like this:-
1
2
3
4
5
6
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.16.4
Master_User: replication_user
Master_Port: 3306
Connect_Retry: 60
.............

.

now we should load data from our master…
1
LOAD DATA FROM MASTER;

.

or… you can always use below command to load the initial data from master
1
mysql -usome_user_name -psome_password -Ddatabase_name_to_replication < dump_from_master_db.sql

.

after our data is moved to slave server… we can unlock our tables.
Log in to MySQL sever (master) and:
1
2
USE database_name_to_replication;
UNLOCK TABLES;

No comments:

Post a Comment