25/07/2021

MySQL Master-Slave Replication On Same Server But Different Instances

Master-Slave Replication On Same Server

To install MySQL see Install MySQL 8.0 on CentOS 8
I have use 2 different instances of MySQL running on different ports namely 3306 and 3307.
To create 2 instances see Create 2nd MySQL Instance
1. Add the following line to the first configuration file that is, mysql-server.cnf file :

log-bin=mysql-bin

The config file should look more or less like this:

2. Connect to the MySQL console of the Master Server to create a user for replication:

mysql -h 127.0.0.1 -P 3306 -u root -p

I am using 3306 since it is the port on which my Master Server is running.
Lauch the following from the console:

create user [email protected]’%’ identified with mysql_native_password by ‘replipassword’;
grant replication slave on *.* to [email protected]’%’;
flush privileges;

3. Restart MySQL:

systemctl restart mysqld

4. Connect to the console and run the following to get the Status of your server:

show master status;


5. Now connect to the second instance which will be the Slave:

mysql -h 127.0.0.1 -P 3307 -u root -p

Run the following command:

change master to master_host=’127.0.0.1′, master_user=’repliuser’, master_password=’replipassword’, master_log_file=’the output_of_step_4′, master_log_pos=”the output_of_step_4″;
start slave;

6. To get the status mo the slave run:

show slave status\G;

Leave a Reply

Your email address will not be published.