Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
– 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.
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 .
1 2 3 | # Replication server-id = 1 log_bin = /var/log/mysql/mysql-bin.log |
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
1 | $ >sudo service mysql restart |
3- create a replication user
1 | $ > mysql -u root -p |
1 2 3 | mysql> GRANT REPLICATION SLAVE ON *.* TO '<strong>slave_user</strong>'@'<strong>192.168.147.16</strong>' IDENTIFIED BY 'password'; mysql> FLUSH PRIVILEGES; |
4- get the position of the binary log
1 | mysql > SHOW MASTER STATUS; |
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.
1 2 3 4 5 6 7 8 9 | $> sudo service mysql stop $> innobackupex –apply-log –redo-only /var/db_backups/full $> innobackupex –copy-back /var/db_backups/full $> sudo chown -R mysql:mysql /var/lib/mysql $> sudo service mysql start |
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
1 2 3 4 | # Replication server-id = 2 log_bin = /var/log/mysql/mysql-bin.log relay-log = /var/log/mysql/mysql-relay-bin.log |
2- restart the slave
1 | sudo service mysql restart |
3- setup replication on slave
1 2 3 | $> mysql -u root -p mysql> CHANGE MASTER TO MASTER_HOST='192.168.147.12', MASTER_USER='slave_user', MASTER_PASSWORD='password', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=107; |
4- start replication and check the status
mysql> START SLAVE
you should check that everything went OK with :
1 2 3 4 5 6 | SHOW SLAVE STATUS \G ... Slave_IO_Running: Yes Slave_SQL_Running: Yes ... ... |
