Setup Mysql Replication

Setup Primary Server
Append following following in Primary Server my.cnf

[mysqld]
log-bin=/var/log/mysql/binlog.log
server-id=1

Create log directory
mkdir /var/log/mysql/
chown mysql:mysql /var/log/mysql
/etc/init.d/mysql restart

Backup desired database

mysql -u root -p
or if plesk installed
mysql -u admin -p`cat /etc/psa/.psa.shadow`

Create
GRANT REPLICATION SLAVE ON *.* TO ‘replicate_user’@’%’ IDENTIFIED BY ‘dbpass’;
FLUSH PRIVILEGES;

FLUSH TABLES WITH READ LOCK;

Open another ssh session
mkdir /home/anuj/dbbackup
mysql -p

SHOW MASTER STATUS;
(and copy the status)

+—————+———-+————–+——————+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+—————+———-+————–+——————+
| binlog.000002 |  7506314 |              |                  |
+—————+———-+————–+——————+

mysqldump  -v -u admin -p`cat /etc/psa/.psa.shadow` –-opt dbname –-lock-all-tables -–add-drop-table > dbname_db.sql

UNLOCK TABLES;

login to slave server
edit my.cnf and append fowllowing
server-id=2
log-bin=/var/log/mysql/binlog.log

master-host=1.2.3.4
master-user=replicate_user
master-password=dbpass
master-connect-retry=60
replicate-do-db=dbname

create log directory
mkdir /var/log/mysql/
chown -R mysql: /var/log/mysql/

copy db from master to slave
scp dbname_db.sql ducati@4.3.2.1:/home/ducati

cd /home/ducati
mysql -p
CREATE DATABASE dbname;
\. dbname_db.sql

stop slave;

CHANGE MASTER TO MASTER_HOST=’1.2.3.4′, MASTER_USER=’replicate_user’, MASTER_PASSWORD=’dbpass’, MASTER_LOG_FILE=’binlog.000002′, MASTER_LOG_POS=7506314;

start slve;

show slave status\G;

Verify replication status
Slave_IO_Running: Yes
Slave_SQL_Running: Yes

If both are Yes then replicatio is up and running

Verify replications with creating test table in primary server database
mysql -p
use dbname;
creat test table to verify replication
create table zanujtest(testname SMALLINT UNSIGNED ZEROFILL NOT NULL );

Go to slave mysql and check whether the table is updated or not

Revert change in mysql primary
drop table zanujtest;