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
Role | IP |
Master | 192.168.1.1 |
Slave | 192.168.1.2 |
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:
File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
HOSTNAME-FF73873-bin.000009 | 704 |
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:
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! 🏆🎉
Howdy aree using WordPress for your site platform?
I’m new to thhe blog world but I’m trying to get
started and set upp my own. Do you require any coding expertise to make
your own blog? Any help would be really appreciated!
Yep, hosted on WordPress and no coding experience needed to get started with the basics!