MYSQL MASTER-SLAVE REPLICATION

MySQL Master-Slave Replication on CentOS / RHEL

 This was using MySQL 5.0 and CentOS 5.3, but this should work for most semi-recent versions of both.In this setup, transactions are mirrored to the slave server as they happen on the master.
In this tutorial I’ll use the following setup:

Master Server: 10.1.100.1
Slave Server: 10.2.200.2
MySQL Data path: /var/lib/mysql
MySQL slave user named slave_user

[Master]

First, edit the master server MySQL config file. Add/Replace the following lines

vim /etc/my.cnf

# [mysqld] section
# Start Modification
# First line is probably already there
datadir = /var/lib/mysql
server-id = 1
relay-log = /var/lib/mysql/mysql-relay-bin
relay-log-index = /var/lib/mysql/var/mysql-relay-bin.index
log-error = /var/lib/mysql/mysql.err
master-info-file = /var/lib/mysql/mysql-master.info
relay-log-info-file = /var/lib/mysql/mysql-relay-log.info
log-bin = /var/lib/mysql/mysql-bin
# Stop Modification

Restart MySQL to load the changes

1	service mysqld restart

[Slave]

Now we’ll do about the same thing on the slave server

1	vim /etc/my.cnf

# [mysqld] section
# Start Modification
# First line is probably already there
datadir = /var/lib/mysql
server-id = 2
relay-log = /var/lib/mysql/mysql-relay-bin
relay-log-index = /var/lib/mysql/mysql-relay-bin.index
log-error = /var/lib/mysql/var/mysql.err
master-info-file = /var/lib/mysql/mysql-master.info
relay-log-info-file = /var/lib/mysql/mysql-relay-log.info
# Stop Modification

Restart MySQL to load the changes

1	service mysqld restart

[Master]

Now we need to tell MySQL where we are replicating to and what user we will do it with.

mysql -u root -p mysql> STOP SLAVE; mysql> GRANT REPLICATION SLAVE ON *.* TO 'slave_user'@'%' IDENTIFIED BY 'slave_password'; mysql> FLUSH PRIVILEGES;

Now we will test that this side of the replication is working and get the location that we will start the replication from.

mysql> USE Any_database_name; mysql> FLUSH TABLES WITH READ LOCK;mysql> SHOW MASTER STATUS; +------------------+----------+--------------+------------------+ | File             | POSITION | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql-bin.000001 |   451228 |              |                  | +------------------+----------+--------------+------------------+ 1 ROW IN SET (0.00 sec)

Write down the File, Position number, as this is where we will start the replication from.
Here it is a good idea to do a dump of your master database(s) and pipe it into your slave server.

mysqldump -u root --all-databases --single-transaction --master-data=1 > /home/MasterSnapshot.sql

[Slave]

First grab your SQL dump file from the master server. You can use whatever method you would like to transfer the file. SCP example:

scp root@10.1.100.1:/path/to/MasterSnapshot.sql root@10.2.200.2:/home/MasterSnapshot.sql

Import the SQL file into MySQL

mysql -u root -p < /home/MasterSnapshot.sql

Now we’ll set the slave to read from the master server, starting at the record position we wrote down earlier. Make sure you use the MASTER_LOG_FILE and MASTER_LOG_POS from a few steps back.

mysql> CHANGE MASTER TO MASTER_HOST='10.1.100.1', MASTER_USER='slave_user',  MASTER_PASSWORD='slave_password', MASTER_LOG_FILE='mysql-bin.000001',   MASTER_LOG_POS=451228; mysql> START SLAVE; mysql> SHOW SLAVE STATUS\G;

Make sure that from the resulting output you have the following:
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
If you don’t, check the MySQL error log to find out what is causing the problem

AFTER DOING THIS IF WE WANT TO KEEP DELAY FROM MASTER TO SLAVE WE SHOULD USE MAAKIT :

#rpm -Uvh http://download.fedora.redhat.com/pub/epel/6/i386/epel-release-6-5.noarch.rpm

#yum install maatkit 

#mk-slave-delay --delay 1m --interval 15s --run-time 10m slavehost