The native replication built into MySQL is a powerful tool to keep an identical copy or copies of a database across multiple servers. MySQL replication is useful if you want to have an exact copy of a live database for backup or application purposes. In the event of a total failure of the primary master database server, the application utilizing the database can be manually pointed to the backup slave database. Replication is also useful to high traffic sites where splitting the database reads across many servers is beneficial for performance.
Here are the steps of how to set up your databases replication. Be aware that variables listed below may be slightly changed across different MySQL versions.
Step 1. Configure the master server
log-bin = /var/lib/mysql/master-bin.log log-bin-index = /var/lib/mysql/master-bin.index.log server-id = 1 expire_logs_days = 7 |
log-bin sets - path and name for your binary logs which are used for replication.
log-bin-index - sets path and name for the index file which contains list of all binary log names
server-id is a uniq server identificator that must be different from all other id’s
expire_logs_days helps to rotate your binary logs
Now, restart your MySQL server by issuing the following command:
service mysqld restart |
Let’s create a user with replication privileges on the master server. Start by logging into MySQL command line:
master> CREATE USER repl_user; master> GRANT REPLICATION SLAVE ON *.* TO repl_user IDENTIFIED BY 'password'; |
Step 2. Configure the slave server
Add or modify the following lines in your my.cnf:
server-id = 2 relay-log = /var/lib/mysql/slave-relay-bin.log relay-log-index = /var/lib/mysql/slave-relay-bin.index.log |
Restart your MySQL server:
service mysqld restart |
Step 3. Connect both servers and start replication
Connect two servers:
slave> CHANGE MASTER TO -> MASTER_HOST = 'master_IP_address', -> MASTER_PORT = 3306, -> MASTER_USER = 'repl_user', -> MASTER_PASSWORD = 'password'; |
Finally, start replication:
slave> START SLAVE; |
That’s all. MySQL replication has been setup and ready for use.
If any questions occur do not hesitate to contact us. Our support engineers will come to your assistance at once!