Master-Slave Replication in MySQL

langage_mysql

 

 

Replication enables data from one MySQL database server (the master) to be replicated to one or more MySQL database servers (the slaves).

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,a way to analyze it without using the main database, or simply as a means to scale out.

Replication is asynchronous – slaves need not be connected permanently to receive updates from the master. This means that updates can occur over long-distance connections and even over temporary or intermittent connections such as a dial-up service. Depending on the configuration, you can replicate all databases, selected databases, or even selected tables within a database.

This tutorial will cover a very simple example of mysql replication—one master will send information to a single slave. For the process to work you will need two IP addresses: one of the master server and and one of the slave.

This tutorial will use the following IP addresses:
172.16.1.28 – Master Database
172.16.1.17 – Slave Database

1. sudo su

2. apt-get install mysql-client

3. apt-get install mysql-server

 

Configure Master database

4. Open up the mysql configuration file on the master server.

nano /etc/mysql/my.cnf

5. Once inside that file, we need to make a few changes. The changes are :

bind-address              = 172.16.1.28

server-id                     = 1

log_bin                       = /var/log/mysql/mysql-bin.log

binlog_do_db             = newdatabase

6. Now, Refresh MySQL

service mysql restart

7. Open up the MySQL Shell

MySQL -u root –p

8. We need to grant privileges to the slave. You can use this line to name your slave and set up their password. The command should be in this format:

GRANT REPLICATION SLAVE ON *.* TO ‘slave_user’@’%’ IDENTIFIED BY ‘password’;

FLUSH PRIVILEGES;

9. Switch to new database

USE newdatabase;

10. FLUSH TABLES WITH READ LOCK;

11. SHOW MASTER STATUS

Note : Remember File name and position. Here, I’m assuming File- mysql-bin.000001 and Position- 107

12. Exit;

Proceeding the with the database still locked, export your database using mysqldump (make sure you are typing this command in the bash shell, not in MySQL)

13. mysqldump –u root –p –opt newdatabase > newdatabase.sql

14. mysql –u root –p

15. UNLOCK TABLES;

16. QUIT

 

Configure Slave Database

17. sudo su

18. apt-get install mysql-client

19. apt-get install mysql-server

Log into your slave server, open up the MySQL shell and create the new database that you will be replicating from the master (then exit):

20. mysql –u root –p

21. CREATE DATABASE newdatabase;

22. EXIT;

Now we need to configure the slave configuration in the same way as we did the master:

23. nano /etc/mysql/my.cnf

Once inside that file, we need to make a few changes. The changes are :

server_id                    = 2

relay-log                     = /var/log/mysql/mysql-relay-bin.log

log_bin                       = /var/log/mysql/mysql-bin.log

binlog_do_db = newdatabase

Restart MySQL

24. service mysql restart

25. mysql –u root –p

26. CHANGE MASTER TO MASTER_HOST=’172.16.1.28’,MASTER_USER=’slave_user’,MASTER_PASSWORD=’password’,MASTER_LOG_FILE=’mysql-bin.000001’,MASTER_LOG_POS=107;

This command accomplishes several things at the same time:

  1. It designates the current server as the slave of our master server.
  1. It provides the server the correct login credentials
  1. Last of all, it lets the slave server know where to start replicating from; the master log file and log position come from the numbers we wrote down previously.

27. START SLAVE;

28. SHOW SLAVE STATUS\G

If there is an issue in connecting, you can try starting slave with a command to skip over it:

29. STOP SLAVE;

30. SET GLOBAL SQL_SLAVE_SKIP_COUNTER =1;

31.  START SLAVE;

24 Comments

Add a Comment

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