This article will help you to setup master-slave replication between MySQL servers. We are using MySQL 5.5 on CentOS 6.3. I hope it will help you to setup replication.
Setup Details:
Master Server: 192.168.1.10 Slave Server: 192.168.1.20 Database: mydb
If you are using different – different versions of MySQL on Master and Slave use this link to check compatibility.
1. Setup MySQL Master Server
- Create an mysql account on Master server with REPLICATION SLAVE privileges to which replication client will connect to master.
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl_user'@'192.168.1.20' IDENTIFIED BY 'secretpassword'; mysql> FLUSH PRIVILEGES;
mysql> use mydb; mysql> FLUSH TABLES WITH READ LOCK; mysql> exit;
# vim /etc/my.cnf
[mysqld] log-bin=mysql-bin binlog-do-db=mydb server-id=1 innodb_flush_log_at_trx_commit=1 sync_binlog=1
# service mysqld restart
mysql > SHOW MASTER STATUS; +------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql-bin.000002 | 107 | mydb | | +------------------+----------+--------------+------------------+
The above output is showing that the current binary file is using mysql-bin.000002 and offset value is 107. Note down these values to use on slave server.
# mysqldump -u root -p mydb > mydb.sql # scp mydb.sql 192.168.1.20:/opt/
mysql> UNLOCK TABLES;
2. Setup MySQL Slave Server
# vim /etc/my.cnf
[mysqld] server-id=2 replicate-do-db=mydb
server-id always be an non zero numeric value. These value will never be similar with other master and slave servers.
# /etc/init.d/mysqld restart
# mysql -u root -p mydb < mydb.sql
mysql> CHANGE MASTER TO MASTER_HOST='192.168.1.10', -> MASTER_USER='repl_user', -> MASTER_PASSWORD='secretpassword', -> MASTER_LOG_FILE='mysql-bin.000002', -> MASTER_LOG_POS=107;
mysql> SLAVE START;
mysql> show slave status G
*************************** 1. row *************************** Slave_IO_State: Master_Host: 192.168.1.15 Master_User: repl_user Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000002 Read_Master_Log_Pos: 107 Relay_Log_File: mysqld-relay-bin.000001 Relay_Log_Pos: 4 Relay_Master_Log_File: mysql-bin.000002 Slave_IO_Running: No Slave_SQL_Running: No Replicate_Do_DB: mydb Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 107 Relay_Log_Space: 107 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: NULL Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 1 1 row in set (0.00 sec) mysql>
MySQL Master-slave Replication has been configured successfully on your system and in working mode. Thanks for using this tutorial, Kindly do not forgot to share it with your friends.
7 Comments
Don’t forget to write firewall-cmd statements which allows 3306 port and mysql service.
gotta try this
This is what i was looking for. Thank you so much for sharing this. Very helpful.
hi all,
i have followed all the steps mentioned above.
But did not work.
it is not useful …
Hi Sunny,
What issue are you facing here…?
have you added port n service in firewall??
Hi Rahual,
Above your all steps are ok if database does not using triggers if database are using triggers then it is not successfully. so please add all steps for with triggers replication.
Short Example:-
if database are using triggers then would be configure row-based replication.
mysqldump -u root –skip-triggers database > database.sql
master server : my.cnf
[mysqld]
binlog_format = row
mysql> show variables like “binlog_format”;
…………………………………………………………………………………
Slave server:- my.cnf
[mysqld]
binlog_format = row
mysql> show variables like “binlog_format”;