How to configure MySQL native replication for high availability

Setting up MySQL native replication allows data from one server (master) to be copied to one or more servers (slaves). This process ensures data consistency across multiple nodes. It provides redundancy and supports scaling of read-heavy applications by offloading reads to slave servers.

Replication is controlled by the master server, which writes changes to binary logs. The slave servers connect to the master and apply these changes to their own data. This method ensures that all changes made on the master are replicated to the slaves.

The following steps outline how to set up MySQL master-slave replication. The configuration includes setting up the master and slave servers, enabling replication, and verifying the replication status.

1. Install MySQL on the master and slave nodes.

   $ sudo apt-get install mysql-server 

2. Enable binary logging and set the server-id on the master server.

   [mysqld]
   server-id=1
   log_bin=mysql-bin 

3. Restart the MySQL service.

   $ sudo systemctl restart mysql 

4. Create a user with replication privileges on the master server.

   CREATE USER 'repl_user'@'%' IDENTIFIED BY 'password';
   GRANT REPLICATION SLAVE ON *.* TO 'repl_user'@'%';
   FLUSH PRIVILEGES; 

5. Get the current binary log file and position from the master.

   SHOW MASTER STATUS;
   +------------------+----------+--------------+------------------+
   | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
   +------------------+----------+--------------+------------------+
   | mysql-bin.000001 | 154      |              |                  |
   +------------------+----------+--------------+------------------+ 

6. Set a unique server-id on the slave server. Connect the slave to the master using the binary log information.

   [mysqld]
   server-id=2
   relay-log=relay-bin 

7. Restart the MySQL service on the slave.

   $ sudo systemctl restart mysql 

8. Configure replication on the slave.

   CHANGE MASTER TO MASTER_HOST='master_ip', MASTER_USER='repl_user', MASTER_PASSWORD='password', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=154;
   START SLAVE; 

9. Start the replication process on the slave server and check its status.

   SHOW SLAVE STATUS\G; 

   Ensure that Slave_IO_RunningYes.

10. Insert data into the master database and verify it appears on the slave server.

    USE your_database;
    INSERT INTO your_table (column1, column2) VALUES ('value1', 'value2'); 

11. Check the data on the slave.

    SELECT * FROM your_table;