MySQL Replication Setup
--
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…
