Master -Slave Replication of Mysql Server.

Step 1: Setup Master configuration file properties

Open my.cnf file

^?View Code TEXT

1	vi /etc/my.cnf

.

And comment lines like below

^?View Code TEXT

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)

^?View Code TEXT

1 2 3

log-bin = /var/lib/mysql/mysql-bin.log binlog-do-db=database_name_to_replication server-id=1

.

restart mysql

^?View Code TEXT

1	service mysqld restart

.

Step 2: Setup Master configuration for replication user

logon to mysql server

^?View Code TEXT

1

mysql

.

create an user with “replication slave on” privileges

^?View Code TEXT

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:

^?View Code TEXT

1 2 3 4

USE database_name_to_replication; FLUSH TABLES WITH READ LOCK;   SHOW MASTER STATUS;

.

you will see like this:-

^?View Code TEXT

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

^?View Code TEXT

1	vi /etc/my.cnf

.

And commands lines like below

^?View Code TEXT

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

^?View Code TEXT

1

mysql

.

^?View Code TEXT

1	CREATE DATABASE database_name_to_replication;

.

Restart mysql

^?View Code TEXT

1	service mysqld restart

.

Run below commands:-

^?View Code TETX

1 2 3 4

STOP SLAVE; RESET SLAVE; START SLAVE; SHOW SLAVE STATUS;

.

the output will be somthing like this:-

^?View Code TEXT

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…

^?View Code TEXT

1	LOAD DATA FROM MASTER;

.

or… you can always use below command to load the initial data from master

^?View Code TEXT

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:

^?View Code TEXT

1 2	USE database_name_to_replication; UNLOCK TABLES;