MySQL Data Replication in Master-Slave Mode

MySQL Data Replication in Master-Slave Mode

Data replication in MySQL is a simple mechanism that allows keeping identical, current tables in multiple locations without affecting the performance of the MySQL server. A particularly useful feature is that MySQL servers can be located in various locations—such as in different countries.

Let’s delve a little deeper into the scheme. Suppose we have a dedicated server or SSD VDS where our main (master) MySQL server is located. We want to implement a scheme with 1-2 additional secondary (slave) MySQL servers to solve the following tasks:

• Increase data availability—even if something happens to the master server, an up-to-date copy of the data will be accessible without any loss.
• Avoid overloading the master server with “heavy” operations—for example, backups of tables can be made from the slave without decreasing the performance of the master server.
• Use additional tools (like mysql-proxy) for reading data on slave servers while writing occurs on the master, creating a powerful scalable and distributed cluster.

Regarding configurations, slaves do not need to be as performant as masters. We successfully use setups where dedicated servers or high-performance VDS serve as masters, while slaves are mid-range VDS.

To avoid potential incompatibility, we recommend using the same version of the MySQL server on all nodes.

Configuration Steps

First, note that the file paths are specified for CentOS 6; there may be minor changes on other OS.

1. Master Server Configuration
   On the master, specify the server ID and enable logging in the mysqld section of /etc/my.cnf:

   server-id=1
   binlog-format = mixed
   log-bin=mysql-bin
   innodb_flush_log_at_trx_commit=1
   sync_binlog=1
   

   Then restart the MySQL server:

   service mysqld restart
   

2. Create Slave User
   Create a user for the slave to authenticate on the master server:

   CREATE USER 'slave101'@'2.2.2.2';  
   GRANT REPLICATION SLAVE ON *.* TO 'slave101'@'2.2.2.2' IDENTIFIED BY 'passw0rd';  
   FLUSH PRIVILEGES;
   

   Replace 2.2.2.2 with the IP address of the slave server and set a more secure password than ‘passw0rd’.

3. Create a Full Database Dump
   Create a full dump of all databases:

   mysqldump --skip-lock-tables --single-transaction --flush-logs --hex-blob --master-data=2 -A > ~/master.sql
   

   View the beginning of the resulting file to note the values for MASTER_LOG_FILE and MASTER_LOG_POS:

   head -n80 ~/master.sql | grep MASTER_LOG
   

4. Transfer Dump to Slave Server
   Use SCP, FTP, or Rsync to transfer the dump to the slave server. While the dump is being copied, make changes to my.cnf on the slave server:

   server-id = 101
   binlog-format = mixed
   log_bin = mysql-bin
   relay-log = mysql-relay-bin
   log-slave-updates = 1
   read-only = 1
   

   Restart the MySQL server on the slave, then load the data copy from the dump:

   mysql -u root -p < ~/master.sql
   

5. Start Data Replication
   To begin data replication, execute the following SQL command in the MySQL console on the slave:

   CHANGE MASTER TO  
   MASTER_HOST='1.1.1.1',  
   MASTER_USER='slave101',  
   MASTER_PASSWORD='passw0rd',  
   MASTER_LOG_FILE='XXX',  
   MASTER_LOG_POS=YYY;  
   START SLAVE;
   

   Replace 1.1.1.1 with the IP address of the master server, use the created password, and replace XXX and YYY with the log file name and position.

6. Check Replication Status
   The setup is now complete, and you can check the synchronization status with:

   SHOW SLAVE STATUS \G
   

   Check that Last_Error is empty and that Read_Master_Log_Pos is increasing as the master server is active.

In conclusion, data replication does not replace the need for backups. If a table is deleted or altered due to error, changes are reflected on slave servers almost instantly, making recovery impossible. Always ensure backups are made, which can be done from any slave without compromising master performance.

P.S. While preparing this note, I referred to a valuable post: Setting up MySQL replication without the downtime by Bryan Kennedy.