Setup a simple MySQL Master Slave Replication

– Requirements

This topic assumes that you have user with sudo privileges and have MySQL installed on both server(Master & Slave). If you do not have MySQL server installed, you can install it with this topic . 

My Master IP address is 192.168.147.12, and my slave is at 192.168.147.16. You must also make sure you have super privileges on both database to create user, start and stop MySQL Server and Replication

 

– About MySQL replication

MySQL replication is a process that allows you to easily maintain multiple copies of a MySQL data by having them copied automatically from a master to a slave database. This can helpful for many reasons including facilating a backup for the data, although it can also be used for other purposes such as for failover, or analyzing data on the slave in order not to overload the master.

mysql_replication_topology_threads

As the master-slave replication is a one-way replication (from master to slave), only the master database is used for the write operations on the slave.

 

– Configure the Master

1- Open up the mysql configuration file on the master server and add 3 lines below .

Explanation : 

server-id number must be unique and cannot match any other server-id in your replication group.

log_bin : is the binary log path, where the slave is going to copy all of the changes that occurs

2- restart MySQL server

3- create a replication user

4- get the position of the binary log

5- Full backup of the database

If your data-set is too large size, mysqldump will take a long time to finish your logical backup and the restore on the slave will also take a very long time. I recommend to use a binary backup. in the example below i used percona xtrabackup and i streamed the binary backup directly on the slave via ssh. Please follow this article to installed percona xtrabackup on your Master server and see also how to connect via ssh without password  before run below command :

sudo innobackupex –user=root –password=password –no-lock –history –stream=xbstream ./ | ssh [email protected] “xbstream -x -C /var/db_backups/full/”

However if your data-set is small you can procceed with the mysqldump command below to stream and load the backup directly into the slave server:

mysqldump -u root -p  | mysql -u root -p -h 192.168.147.16

– Configure the Slave

If you stream the Full backup with xtrabackup tool you have to make sure the data directory on the slave is empty, prepare the binary backup by applying the redo log and restore the backup while the server is stopped.

However, if you generated your backup with mysqldump please skip step above

1– Setup the slave by adding lines below at the end of my.cnf file 

2- restart the slave

3- setup replication on slave

4- start replication and check the status

mysql> START SLAVE

you should check that everything went OK with :

 

Mikael HOUNDEGNON
Author: Mikael HOUNDEGNON

My name is Mikael HOUNDEGNON. I am an experienced MySQL DBA/Developer based in the greater Chicago area. You can find out more about me here. I blog here mostly about things I don’t want to forget ? most likely, MySQL Tips. My specialties : MySQL Replication (Master Slave, MultiMaster, Fail over, etc) MySQL Backups MySQL Query Optimization MySQL Performance Tuning MySQL Stored Procedures Storage Engine Tuning Do you have an interesting project idea? Or you just want to chat? Get in touch!

Leave a Reply

Your email address will not be published. Required fields are marked *