Bobby's Blog

CentOS MySQL Master-Slave Replication – Step by Step

CentOS MySQL Master-Slave Replication - Step by Step

MySQL replication is a process that enables data from one MySQL database server (the master) to be copied automatically to one or more MySQL database servers (the slaves).

I would assume that you have MySQL installed on both servers, so we would get straight into the Master-Slave setup.

I would use the following two servers: – Master – Slave

1. We are going to start with the master:

– SSH to the master server
– Edit the /etc/my.cnf file

Add the following entries under [mysqld] section and don’t forget to replace the database name with database name that you would like to replicate on Slave.

– Restart mysql

– Create the replication user (change the Pass-Goes-Here part with your new pass):
– Note this goes on the slave, so that the master could then connect to the slave server

New we have to export the database that we would like to replicate.

– Open a second SSH session

In the first session run:

You would see something like:

Take a note of the the File (mysql-bin.000003) and Position (11128001) numbers

While the tables have been locked with the read lock – go ahead and export the database in question via the Second SSH session without closing the first one!

Once that has been done unlock the tables via the first ssh session

Now we are ready to start with the slave setup. Go ahead and copy the dump from the master to the slave.

Then SSH to the slave server.

– Edit the my.cnf:

Under the [mysqld] section add the following and update the details accordingly:

Now import the dump file that we exported in earlier command and restart the MySQL service.

Note you might need to create the database before importing the data:

Login into MySQL as root user and stop the slave:

Then in order to start the replication run the following and make sure th update the details accordingly:

Then when you run the show slave status command you should see something like this:

To fully test the replication you can try creating a table on the master and then check if it was successfully replicated over to the slave.

This is pretty much it. 🙂

About the author


Linux System Administrator