How to create Replication on Mysql: Step by step
To created replication (master-slave) on ServerX slave-server (suppose that its IP is 192.168.1.4) and ServerY master-Server (suppose that its IP is 192.168.1.135), below is the procedure step by step:
- @ServerY (Master): add this to my.cnf:
server-id = 1 log_bin = /var/log/mysql/mysql-bin.log expire_logs_days = 10 max_binlog_size = 100M #For the greatest possible durability and consistency in a replication #setup using InnoDB with transactions innodb_flush_log_at_trx_commit =1 sync_binlog =1
- @ServerX(Slave) add this
server-id = 2 log_bin = /var/log/mysql/mysql-bin.log log-slave-updates =yes #set log-slave-update IF you want to chain replication servers, #otherwise just drop it
- create slave users on ServerY
mysql> grant replication slave on *.* to 'slave'@'192.168.1.4' IDENTIFIED BY 'Mypass'; - copy DB from ServerY to ServerX
- run mysqladmin -uroot -pth3gat3sql shutdown
- run tar cf /tmp/MyDB.tar ./MyDB
- @ServerX run
scp Username@192.168.1.35:/tmp/MyDB.tar /var/lib/mysql
tar -xvf MyDB.tar - @ServerY:
mysql>FLUSH TABLES WITH READ LOCK;
mysql> show master status;
Save the result of show master status - @ServerX, Run run this query:
CHANGE MASTER TO
MASTER_HOST='192.168.1.135',
MASTER_USER='slave',
MASTER_PASSWORD='MyDB',
MASTER_LOG_FILE='mysql-bin.000003',
MASTER_LOG_POS=106;
the values of MASTER_LOG_FILE and MASTER_LOG_POS are those came from show master status on ServerY. - now to be sure that your replication is fine
@ServerX:
show slave status
Slave_IO_Running and Slave_SQL_Running MUST be Yes for both.



