Securing MySQL replication using SSH is a great way to ensure that your data is transmitted securely between the master and slave servers. Below are the steps to set up MySQL replication over SSH on an Ubuntu 24.04 or newer system.

Prerequisites

1. Two Ubuntu servers: One will be the MySQL master and the other the slave.
2. MySQL installed: Make sure MySQL is installed and running on both servers.
3. SSH access: You should have SSH access to both servers.
4. MySQL user: Create a MySQL user on the master with replication privileges.

Step 1: Configure MySQL on the Master Server

1. Edit MySQL Configuration:
   Open the MySQL configuration file on the master server, usually located at /etc/mysql/mysql.conf.d/mysqld.cnf or /etc/my.cnf.

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

Look for the [mysqld] section and add or modify the following lines:

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

• server-id: A unique identifier for the master server.
• log_bin: The path for the binary log file.

1. Create a Replication User:
   Log in to the MySQL shell and create a user for replication:

   mysql -u root -p

Then run:

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

3. Get the Binary Log Coordinates:
   Still in the MySQL shell, run:

   FLUSH TABLES WITH READ LOCK;
   SHOW MASTER STATUS;

Note the File and Position values, as you will need them later. Leave this window open to maintain the read lock.

Step 2: Configure MySQL on the Slave Server

1. Edit MySQL Configuration:
   Open the MySQL configuration file on the slave server:

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

Add or modify the following lines:

   server-id = 2

• Ensure server-id is different from that of the master.

1. Restart MySQL: Restart the MySQL service on the slave server:

   sudo systemctl restart mysql

Step 3: Set Up SSH Tunneling

1. Create an SSH Key Pair (if not already done):
   On the slave server, generate an SSH key pair to allow passwordless login to the master server:

   ssh-keygen -t rsa

Follow the prompts to create the key pair. By default, it will be created in ~/.ssh/id_rsa.

2. Copy the SSH Public Key to the Master:
   Copy the public key to the master server:

   ssh-copy-id user@master-server-ip

Replace user with your username and master-server-ip with the IP address of your master server.

3. Test SSH Connection:
   Make sure you can SSH into the master server without a password:

   ssh user@master-server-ip

Step 4: Configure the Slave to Connect to the Master via SSH

1. Create an SSH Tunnel:
   On the slave server, create an SSH tunnel to the master server. You can do this by running:

   ssh -f -N -L 3307:localhost:3306 user@master-server-ip

This command forwards port 3306 of the master server to port 3307 on the slave server.

2. Configure the Slave to Use the Tunnel:
   Now, connect to the MySQL shell on the slave server and configure the slave to use the SSH tunnel:

   mysql -u root -p

Run the following command using the File and Position values obtained earlier:

   CHANGE MASTER TO
       MASTER_HOST='127.0.0.1',
       MASTER_PORT=3307,
       MASTER_USER='replicator',
       MASTER_PASSWORD='your_password',
       MASTER_LOG_FILE='mysql-bin.000001',  -- replace with your File value
       MASTER_LOG_POS=12345;                 -- replace with your Position value

3. Start the Slave:
   Start the replication process:

   START SLAVE;

4. Verify Replication:
   Check the status of the slave to ensure replication is working:

   SHOW SLAVE STATUS\G

Look for Slave_IO_Running and Slave_SQL_Running both set to “Yes”.

Step 5: Testing and Verification

1. Test Data Replication:
   Insert data into the master database and check if it replicates to the slave.
2. Monitor Logs:
   Monitor MySQL logs for any errors.

Conclusion

You have now set up MySQL replication over SSH on Ubuntu 24.04 or newer. This method encrypts the data during transit, ensuring a more secure replication environment. Always keep your systems updated and monitor replication status regularly for any issues.