Master Slave Replication Setup in MySQL

MySQL Servers for replication setup, one is a Master and the other is a Slave.
Master database keep record of all log then slave read the log created in Master and applied on mysql database.

Following are the steps to configure replication on Master & slave server:

  1. Master: Configure Master User and permissions:
    Create a Master user which is used by slave to create connection or replicate the changes
CREATE USER 'user_name'@'%' IDENTIFIED BY 'user_password';
GRANT REPLICATION SLAVE ON *.* TO 'user_name'@'%';
FLUSH PRIVILEGES;

2. Master: Edit configuration file my.ini or my.cnf section [mysqld]

erver-id = 1   -- assign id to server
log-bin = mysql-bin.log  -- start write log in file
binlog-do-db = your_database --database name for which replication established

3. On Slave: Edit the my.ini or my.cnf file at slave side.

server-id = 2
master-host = master_ip_address
master-connect-retry = 60
master-user = user_name
master-password = user_password
replicate-do-db = your_database
relay-log = slave-relay.log
relay-log-index = slave-relay-log.index

Note:
first line is used to assign an ID to this MySQL server.
second line is the I.P address of the Master server.
third line is used to set a retry limit in seconds.
username and password to the Slave, by using which it connect the Master.

4. Master: Flush the Master tables to take consistent backup.

--flush for consistent backup
FLUSH TABLES WITH READ LOCK;
--take data backup of the Master by using mysqldump
mysqldump your_database -u root -p > D://Backup/backup.sql;

5. Slave: Create database if not exists:

CREATE DATABASE `your_database`;

6. Slave: import backup to the slave mysql server.

mysql -u root -p your_database < D://Backup/backup.sql
--->Change `your_database` and backup directory according to your setup

7. Master: find the log file name and log position in the Master.

SHOW MASTER STATUS;

+------------------+----------+-----------------+------------------+
| File             | Position | Binlog_Do_DB    | Binlog_Ignore_DB |
+------------------+----------+-----------------+------------------+
| mysql-bin.000001 | 130      | your_database   |                  |
+------------------+----------+-----------------+------------------+

8. Slave: Run the following commands:

SLAVE STOP;
CHANGE MASTER TO MASTER_HOST='master_ip_address', MASTER_USER='user_name',
MASTER_PASSWORD='user_password', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=130;
SLAVE START;

9. Slave: check the slave status.

SHOW SLAVE STATUS;

10. Master: release the tables from lock by run the following.

UNLOCK TABLES;

--Note: Locked with this command at step 4 for consistent backup:
--FLUSH TABLES WITH READ LOCK;

Error Handling in Master Slave configuration:

  1. To skip just N query that is hanging the slave, use the following syntax

SET GLOBAL sql_slave_skip_counter = N;

--Example skip one error of hanging query: set as 1 value.
STOP SLAVE;
SET GLOBAL sql_slave_skip_counter=1;
START SLAVE;

2. you might want to skip all duplicate errors you might be getting

1062 | Error 'Duplicate entry 'xyz' for key 1' on query

Edit the my.cnf file and set following parameter with error number:

slave-skip-errors = 1062
syntax: 
slave-skip-errors=[err_code1,err_code2,...|all]
slave-skip-errors=1062,1053
slave-skip-errors=all
slave-skip-errors=ddl_exist_errors

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.