Howdy all!

Today I’m going to walk through a setup of MySQL replication. In this example, I’ll have 1x master and 1x slave server. Depending on how you feel about those name designations, feel free to call it master and replica instead…

Okay, lets crack on!

This will be our setup

First things first, you’ll obviously need to have MySQL installed on both systems. This can be downloaded from the MySQL website. I’ll be using version 8.0.28.

Master

Once installed, head over to the master server and take a copy of the my.ini file located in C:\ProgramData\MySQL\MySQL Server 8.0. Once you have a copy, edit the original my.ini file and make the following changes:

• server-id=1
• bind-address=0.0.0.0
• log-bin=mysql-bin
• default_time_zone=’+00:00′

These can be added anywhere to the file, but I recommend adding them right after the [mysqld] declaration.

You’ll probably have to save the file onto your desktop and drag it back into the above location.

Once saved, restart the MySQL service on the master server

Once the MySQL service has restarted, we need to create the required replication user. This is the user that our slave server will use to read the master database. We’ll do this by opening the MySQL Command Line Client.

First, lets check if there is already a user we could use for replication:

SELECT host, user FROM mysql.user;

If no users look suitable, then we need to create one:

CREATE USER 'replication'@'%' IDENTIFIED BY 'password';

The ‘%’ block states that the user can connect from any system. To have better security, you could lock this down to only the slave server.

Lets confirm that the user was created succesfully:

SELECT host, user FROM mysql.user WHERE user='replication';

Now, we can grant this user to have replication permissions to the master database:

GRANT REPLICATION SLAVE ON *.* TO 'replication'@'%';

The *.* simply means that permissions are granted to every single database and tables on the master server, but you could specify them better for increase security or versatility

Now lets flush the tables with read locks and show the master status:

FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;

Finally, lets backup the current databases so that they can be restored on the slave server. To backup the MySQL data, use the below command located in C:\Program Files\MySQL\MySQL Server 8.0\bin\:

mysqldump.exe -u USERNAME -p DATABASENAME > c:\temp\mysqldump.sql

Now the backup is complete, you can unlock the tables using:

UNLOCK TABLES;

And copy the MySQL dump to the slave server.

Slave

On the slave server, take a copy of the my.ini file located in C:\ProgramData\MySQL\MySQL Server 8.0. Once you have a copy, edit the original my.ini file and make the following changes:

• server-id=2
• bind-address=0.0.0.0
• log-bin=mysql-bin
• default_time_zone=’+00:00′

These can be added anywhere to the file, but I recommend adding them right after the [mysqld] declaration.

You’ll probably have the same the file onto your desktop and drag it back into the above location.

Now, restart the MySQL service

Open the MySQL Command Line Client and check the current databases on the system using:

SHOW DATABASES;

Once confirmed, we can import the mysql dump file from the master server using the mysql.exe location in C:\Program Files\MySQL\MySQL Server 8.0\bin\:

mysql.exe -u USERNAME -p DATABASENAME < c:\temp\mysqldump.sql

Once the import is complete, stop the slave server:

STOP SLAVE;

Master

Head back to the master server and grab the log file and log position using:

SHOW MASTER STATUS;

For example, the output might look like this:

Slave

Back to the slave server, open the MySQL Command Line Client and run the following command to setup the slave instance:

CHANGE MASTER TO 
MASTER_HOST='192.168.1.1', 
MASTER_USER='replication', 
MASTER_PASSWORD='password', 
MASTER_LOG_FILE='HOSTNAME-FF73873-bin.000009', 
MASTER_LOG_POS=704,
GET_MASTER_PUBLIC_KEY=1;

I’ve put each config entry onto a separate line to make it easy to follow. The final GET_MASTER_PUBLIC_KEY=1 is required otherwise you will get the below error:

Authentication plugin ‘caching_sha2_password’ reported error: Authentication requires secure connection

Now you can start the slave instance again:

START SLAVE;

Check the slave instance status using:

SHOW SLAVE STATUS \G

Test

To test that replication is setup and working, create a new table on the master database and it should replicate almost instantly to the slave server.

Create a new table on the master server:

CREATE TABLE zzTest (testID int);

On the slave server, check for the table using:

SHOW TABLES

If the newly created table shows up, everything is working!

Congrats on setting this up! Nice and easy once you’ve done it a couple (hundred) times!

Enjoy! 🏆🎉