Skip to content

MySQL Replication Made Easy

March 23, 2010

MySQL Replication can be done in many ways such as master-master, master-slave, etc. Today i will talk about Master-Slave replication in which one MySQL instance serves as Master database and second instance serve as the secondary database.

Master-Slave replication can be used in many ways, following are few advantages of using Master-Slave MySQL replication

  1. Master server will be used to perform all the CRUD operation while slave can be used to run heavy queries, thus distributing the server load.
  2. Slave pull all the information from server and serves as the mirror of master so its a real-time backup of your database.
  3. You can test your applications with the production data and without connecting to your master/primary database, connect it to slave/secondary database instead.

Lets start with the setup of Master instance.

Setting Master MySQL Instance

Use the following steps to setup your Master MySQL instance.

  1. Stop mysql server if it is running
  2. Edit the my.cnf and enter the following replication properties
  3. Start the mysql server instance
  4. Read the mysql.err file to see if there was any error during startup
  5. Get master status and note down the required properties
  6. Create replication user

Master Server Properties

#replication master-server configuration

server-id=1

relay-log=/var/lib/mysql/logs/mysql-relay-bin

relay-log-index=/var/lib/mysql/logs/mysql-relay-bin.index

log-error=/var/lib/mysql/logs/mysql.err

master-info-file=/var/lib/mysql/logs/mysql-master.info

relay-log-info-file=/var/lib/mysql/logs/mysql-relay-log.info

log-bin=/var/lib/mysql/logs/bin-logs

binlog-do-db=bisdb

binlog-ignore-db=test

# end replication

Following table explains all of these properties:

Once you have updated your my.cnf file with the above properties. You are done with the master configurations, restart mysql server instance and view the error.log file which you specified in the above properties. See, if there is any error in the error.log or not. I am assuming you did everything right and there was not any error.

Following are few useful notes to read before we move onto next step.

Note: If you omit server-id (or set it explicitly to its default value of 0), a master refuses connections from all slaves.

Note: For the greatest possible durability and consistency in a replication setup using InnoDB with transactions, you should use innodb_flush_log_at_trx_commit=1 and sync_binlog=1 in the master my.cnf file.

Note: Ensure that the skip-networking option is not enabled on your replication master. If networking has been disabled, your slave will not able to communicate with the master and replication will fail.

Now login to mysql console.

mysql> show server status;

+—————–+———–+————–+——————+

| File            | Position  | Binlog_Do_DB | Binlog_Ignore_DB |

+—————–+———–+————–+——————+

| bin-logs.000003 | 106 | bisdb        | test             |

+—————–+———–+————–+——————+

1 row in set (0.00 sec)

As you can see that bin-log file is bin-logs.000003 and position is 106, write down both of these values as you will need them to configure slave box. (Use the values that comes on your box)

You will need to create a user with the replication privileges and that user will be used to pull the data from master box.

mysql> create user slave_user;

mysql> grant replication slave on *.* to slave_user@’slave-box-ip’ identified by ‘slaveuser’;

Okay, Now you are done with the master configuration, time to configure your slave box. (Don’t forget to use some strict password for slave user)

Setting Slave MySQL Instance

Use the following steps to setup your Slave MySQL instance.

  1. Stop mysql server if it is running
  2. Edit the my.cnf and enter the following replication properties
  3. Start the mysql server instance
  4. Read the mysql.err file to see if there was any error during startup
  5. Set the binary log position on slave
  6. Check slave status

Slave Server Properties

#replication slave-server configuration

server-id=2

master-host=192.168.168.8

master-user=slave_user

master-password=slaveuser

master-connect-retry=60

replicate-do-db=bisdb

replicate-ignore-db=test

log-error=/var/lib/mysql/logs/mysql.err

relay-log=/var/lib/mysql/logs/mysql-relay-bin

relay-log-index=/var/lib/mysql/logs/mysql-relay-bin.index

slave_exec_mode=IDEMPOTENT

slave-skip-errors=1062,1060,1050,1396,1146

# end replication

Most of the properties are self explanatory so i am going to skip them except last two. 1) slave_exec_mode is used to supress the duplicate key errors. 2) slave-skip-errors is used to continue replication when these errors occur. By default mysql replication stops when there is any error during the replication process. If the error occured contained in the given list of error codes then replication will continue otherwise will halt.

Now restart mysql server and view the mysql.err file to see if there is any error in your configuration or it is started normally. Lets assume All is Well! and login to mysql to set the binary log position.

mysql> CHANGE MASTER TO MASTER_HOST=’192.168.168.8′, MASTER_USER=’slave_user’, MASTER_PASSWORD=’slaveuser’, MASTER_LOG_FILE=’bin-logs.000003′, MASTER_LOG_POS=106;

mysql> show slave status\G

It would show you that its connecting to master for reading the new events. You can test your replication by making some changes in the database which you used in replicate-do-db and any changes made in this database on master box should be replicated to the slave box. Whereas any change made in the ignore-db should not be replicated.

Conclusion

Mysql replication is a very good feature and can be used in many scenarios and the most common ones are backup, performance, etc. In this article i talked about the basic replication which you will use when you are setting up a new environment. But in case of running mysql server instance you might want to make few more changes in the replication process to copy the master data to the slave box.

Advertisements
Leave a Comment

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s

%d bloggers like this: