Setting Up MySQL Master-Slave Replication on ServerStadium VMs

MySQL Master-Slave replication involves one database server (the Master) sending updates to one or more database servers (the Slaves). This guide will walk you through setting up this configuration using ServerStadium VM instances.

Prerequisites

• A ServerStadium account and at least two VM instances. Set these up through ServerStadium Cloud Services.
• Basic knowledge of MySQL and Linux server management.

Step 1: Setting Up the MySQL Master Server

1. Install MySQL: On your first VM (the Master), install MySQL:

   sudo apt update
   sudo apt install mysql-server

2. Configure MySQL for Replication: Open the MySQL configuration file:
   

   sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

   
   Add or modify the following lines:
   

   server-id = 1
   log_bin = /var/log/mysql/mysql-bin.log
   binlog_do_db = my_database

   
   Replace my_database with the name of your database. Save and exit the file.
3. Restart MySQL:
   

   sudo systemctl restart mysql

4. Create a Replication User:

   Access MySQL:
   

   mysql -u root -p

   
   Then run:
   

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

   
   Replace 'password' with a strong password.
5. Lock the Database for Export:

   In the MySQL prompt, run:
   

   FLUSH TABLES WITH READ LOCK;
   SHOW MASTER STATUS;

   
   Note the file name and position. You will need these for setting up the Slave.

Step 2: Setting Up the MySQL Slave Server

1. Install MySQL on the Slave Server:

   Repeat the same installation process on your second VM (the Slave).

2. Configure the Slave Server:

   Open the MySQL configuration:
   

   sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

   
   Add or modify:
   

   server-id = 2
   relay_log = /var/log/mysql/mysql-relay-bin.log
   log_bin = /var/log/mysql/mysql-bin.log
   binlog_do_db = my_database

   
   Restart MySQL:
   

   sudo systemctl restart mysql

Step 3: Synchronize the Databases

1. Export the Database from the Master:

   On the Master server, export your database:
   

   mysqldump -u root -p –opt my_database > my_database.sql

2. Transfer the Database Dump to the Slave Server:

   Use scp or a similar tool to transfer my_database.sql to the Slave server.

3. Import the Database on the Slave Server:

   On the Slave server, import the database:
   

   mysql -u root -p my_database < my_database.sql

Step 4: Establish Replication

1. Configure the Slave Server:

   On the Slave server, open the MySQL prompt:
   

   mysql -u root -p

   
   Run:
   

   STOP SLAVE;
   CHANGE MASTER TO MASTER_HOST='master_ip', MASTER_USER='replicator', MASTER_PASSWORD='password', MASTER_LOG_FILE='recorded_log_file_name', MASTER_LOG_POS=recorded_log_position;
   START SLAVE;

   
   Replace master_ip, recorded_log_file_name, recorded_log_position, and password with the appropriate values.
2. Verify Replication Status:

   Still in the MySQL prompt on the Slave, check the replication status:
   

   <span class="hljs-keyword">SHOW</span> SLAVE STATUS\G

   
   Look for Slave_IO_Running and Slave_SQL_Running both being set to Yes.

Conclusion

You’ve successfully set up MySQL Master-Slave replication across ServerStadium VMs. This configuration ensures that your databases are synchronized, providing a foundation for high availability and data redundancy.

For more resources or assistance, visit our knowledge base or contact our support team.