MySQL Master-Slave Replication: Setting Up
Key Takeaways
- MySQL replication can solve various problems including scale-out solutions, data security, analytics, long-distance data distribution, and taking backups from slaves instead of the master.
- After replication, daily backups and reports can be obtained from the Slave, reducing load on the Master machine. If the Slave node is down, replication is resumed from the point it paused when the node is live again.
- Setting up replication involves allowing remote connections, setting bind-address, enabling binary logging, establishing a unique server ID, and creating a user for replication on the Master. On the Slave, MySQL needs to be installed, bind-address and binary logging set, and a unique server ID established.
- MySQL replication can be used for load balancing by distributing read queries across multiple slave servers. However, write queries must still be executed on the master server. Ensuring data consistency in MySQL replication involves executing all write queries on the master server and regularly checking the replication status.
Why would you want to do that
Replication of MySQL can be a solution to various problems that you might want to solve:- Scale-out solutions
- Data security
- Analytics
- Long-distance data distribution
- Backup taken from slaves, rather than from master
Where have we been?
Before implementing replication, we had a configuration such as the following:
Where are we after replication?
After MySQL replication, we are now in the following picture:
How did we do that?
Here is the process that we followed to carry out the replication:Assumptions
- Both Master and Slave are debian squeeze machines.
- Master has static IP: 10.100.10.80.
- Slave has static IP: 10.100.10.103.
- Master and Slave are on the same LAN.
- Master has MySQL installed and you have the root password to connect to it from ‘localhost’. MySQL does not allow remote connections by default, and that was our case too.
- Slave does not have MySQL installed yet.
- Master debian allows incoming connections on 3306 port, the standard MySQL listening port.
PHASE 1
Overview
In the first phase of the work, the overview of what we did is as follows: 1) On Master- Allow remote connections
- Set bind-address
- Enable binary logging
- Establish a unique server ID
- Restart server
- Create a user for replication
- Install MySQL
- Stop MySQL
- Set bind-address
- Enable binary logging
- Set unique server ID
Detailed Actions
On Master
1) Allow remote connections Since you are about to change the bind address on your MySQL server, you are essentially allowing access to your MySQL server from “remote” machines. If you do not do that, after the change of the bind address and restarting of your MySQL server, users from remote or local machines will not be able to connect to your server. All the remote and local machine IPs need to be granted access for the users that access MySQL from these machines. The following mysql commands (which can be issued using mysql shell)mysql> grant all on *.* to bar@’10.100.10.55’ identified by ‘bar_password’; mysql> flush privileges;allow the user ‘bar’ to connect to this MySQL server and to all databases from the machine with IP ’10.100.10.55’. This is necessary even if the user connects from the machine that MySQL server is installed. Hence, connect to your MySQL server using mysql and set these commands accordingly before proceeding to the following. Don’t forget that this is true for the ‘root’ user too, even if he connects from the local machine. 2) Set bind-address to the IP of the machine. This is done inside the
my.cnf
file. bind-address
is a configuration parameter of the section [mysqld]
. Set it to the IP of your Master machine. In our case, it was 10.100.10.80. Note that if you have enabled skip-networking
you need to disable it.
3) Set binary log file pattern
The parameter is called log_bin
and it should exist in the [mysqld]
section. Note that the full path to the binary log file should be given. Example: /var/log/mysql/mysql-bin.log
.
Important gotcha: make sure that the folder/disk has enough space to write the binary logs there.
4) Set server id
This is the Master replication server ID. It has to be unique among all the ids used in the whole replication setup. For example, set this to “100” and make sure that you set “101” on your Slave (see later).
The parameter is server-id
and should be set in the section [mysqld]
.
5) innodb_flush_log_at_trx_commit
and sync_binlog
These should be set to “1”, both of them. According to MySQL documentation for the greatest possible durability and consistency in a replication setup using InnoDB with transactions … you have to do this. More on this you can find here
.
These parameters are set in section [mysqld]
.
6) RESTART MySQL Server
Having done all the necessary changes on the configuration file of MySQL Server and having granted access to all existing users to be able to connect using their IP, you can restart your MySQL Server.
7) Create user for replication
You need to create a user that will have the right to work as the replication/slave client. The following commands:
mysql> create user ‘repl’@’10.100.10.103’ identified by ‘slavepassword’; mysql> grant all on *.* to ‘repl’@’10.100.10.103’; mysql> flush privileges;are creating the user ‘repl’ and give him the right to do replication from the slave machine with IP 10.100.10.103 using the specified password.
On Slave
1) Install MySQL Server On Slave machine you need to install MySQL Server. 2) Stop MySQL server You do not need to have it running, for the time being. Proceed with changing MySQL configuration parameters to support Slave replication. 3)bind-address
Set bind-address
to the value of the IP of your Slave machine. In our case was 10.100.100.103. The parameter should be set in the [mysqld]
section.
4) log_bin
Set the full path to the binary log file. For example: /var/mysql/log/mysql-bin.log
. The parameter is set in the [mysqld]
section of the my.cnf
file.
Important gotcha:: make sure that your binary log folder has lots of space. You will need it.
5) server-id
You need to give the server ID for the Slave node for this replication setup. Put for example “101”. It has to be different from the server IDs of the Master and other Slave nodes in your configuration.
Do not start Slave MySQL server. You still do not need to do that.
You are done for now. Phase 1 is finished.
In Phase 2, we will start replication.
Frequently Asked Questions on MySQL Master-Slave Replication
What is the significance of the server-id in MySQL replication?
The server-id is a unique identifier for each server in a MySQL replication setup. It is crucial because it allows the master server to differentiate between multiple slave servers. If two servers have the same server-id, it can lead to data inconsistency and replication errors. Therefore, it’s essential to ensure that each server in your replication setup has a unique server-id.
How can I check if MySQL replication is working correctly?
You can check the status of MySQL replication by running the ‘SHOW SLAVE STATUS’ command on the slave server. This command provides detailed information about the replication status, including whether it’s running, the last error message, and the master log file position. If the ‘Slave_IO_Running’ and ‘Slave_SQL_Running’ values are both ‘Yes’, it means the replication is working correctly.
What is the role of the binary log in MySQL replication?
The binary log plays a crucial role in MySQL replication. It records all changes to the database, including data modifications (insert, update, delete) and schema changes (create, alter, drop). The master server uses the binary log to transmit these changes to the slave servers, ensuring that they have the same data as the master.
How can I resolve replication errors in MySQL?
Resolving replication errors in MySQL depends on the specific error message. However, common solutions include checking the network connectivity between the master and slave servers, ensuring that the master and slave servers have unique server-ids, and verifying that the binary log file and position specified in the ‘CHANGE MASTER TO’ command are correct. If the error persists, you may need to consult the MySQL documentation or seek help from a MySQL expert.
Can I use MySQL replication for load balancing?
Yes, MySQL replication can be used for load balancing. By distributing read queries across multiple slave servers, you can reduce the load on the master server and improve the overall performance of your database system. However, keep in mind that write queries must still be executed on the master server.
What is the difference between synchronous and asynchronous replication in MySQL?
In synchronous replication, the master server waits for confirmation from the slave server before committing a transaction. This ensures data consistency but can impact performance. On the other hand, in asynchronous replication, the master server doesn’t wait for confirmation from the slave server, leading to better performance but a risk of data inconsistency if the master server fails before the slave server has replicated the changes.
How can I set up multiple slave servers in MySQL replication?
To set up multiple slave servers in MySQL replication, you need to configure each slave server with a unique server-id and specify the master server’s details using the ‘CHANGE MASTER TO’ command. Then, start the replication process on each slave server using the ‘START SLAVE’ command. Remember to ensure that the binary log file and position are correct on each slave server.
Can I pause and resume MySQL replication?
Yes, you can pause and resume MySQL replication using the ‘STOP SLAVE’ and ‘START SLAVE’ commands, respectively. This can be useful when you need to perform maintenance tasks on the slave server or troubleshoot replication issues.
What is the impact of MySQL replication on database performance?
MySQL replication can have both positive and negative impacts on database performance. On the positive side, it can improve read performance by distributing read queries across multiple slave servers. However, it can also increase the load on the master server due to the overhead of managing the binary log and transmitting changes to the slave servers.
How can I ensure data consistency in MySQL replication?
Ensuring data consistency in MySQL replication involves several strategies. First, make sure that all write queries are executed on the master server. Second, regularly check the replication status using the ‘SHOW SLAVE STATUS’ command. If there are any replication errors, resolve them promptly to prevent data inconsistency. Finally, consider using tools like pt-table-checksum and pt-table-sync from the Percona Toolkit to verify and correct data consistency.
Currently, spending most of the day as CTO for Book&Table. Otherwise, full stack engineer for Fraudpointer and Wyngle. Also, blogging here and teaching computer programming.