REPLICATION – MYSQL (ENG)

REPLICATION – MYSQL (ENG)

In this article we will implement the replication of a database on 2 MySQL servers.

PREREQUISITES

  1. two debian machines

 

Before doing anything, let’s start by updating our 2 machines.

apt-get update && apt-get upgrade

Then we will install mysql-server on both machines.

apt-get install -y mysql-server

 

MASTER-SLAVE REPLICATION

Master-slave replication is a one-way replication. that is, only the master server can add, delete, modify the contents of the database to replicate. While the slave server, will only copy the database of the master server and it will serve as backup server in case of failure on the master server.

For master-slave replication, we must define which server will be master and which server will be slave. For my situation I choose « ServerBBD1 » which will be master and « ServerBDD2 » which will be slave.


Configuration of ServeurBDD1

To begin, we connect to the MySQL console.

mysql -u root -p

Then we create a database.

create database replication;

Then exit the console with the exit command.


Then we must access the /etc/mysql/my.cnf configuration file and change theses lines in the file to allow replication of the database.

nano /etc/mysql/my.cnf

5)my.cnf bind address

6)my.cnf serv id etc

Once the changes are made, we restart the mysql service.

service mysql restart

Now we reconnect back to the MySQL console.

mysql -u root -p

We assign the replication privileges to the user ‘repuser’@’Ip-slave-server’ which will be created following this command:

grant replication slave on *.* to 'repuser'@'192.168.1.107' identified by 'password';v
flush privileges;

We will need the folder name and position following the command:

show master status;

note the content of « File » and « Position ».

 

Exporting the database

We will now export the database of theserver « ServerBBD1 » to the 2nd server « ServerBDD2 ».

To do that we will first pass our database read-only.

use replication;
flush tables with read lock;

Then we leave the mysql console and create a dump of the database in order to export it to the slave server.

mysqldump -u root -p replication > replication.sql

And once the export is done, we can go back to the mysql console and remove the read only on the database.

use replication;
unlock tables;

Now we will send the dump that we created on the slave server thanks to this command:

scp replication.sql root@192.168.109.107:/root/

Configuration of ServerBDD2

On the serverBDD2, we create the same database as on the serverBDD1.

create database replication;

Then we import the dump that we recovered previously on our new database.

mysql -u root -p replication < replication.sql

Exit the mysql console and configure the /etc/mysql/my.cnf file as on the serverBDD1 but change the server ID to 2.

nano /etc/mysql/my.cnf

5)my.cnf bind address

16) edit my.cnf bdd2

And finally we restart the service mysql.

service mysql restart

We will now indicate the master server to the DB2 server.

Reconnect to the mysql console and enter these commands.

stop slave;
change master to master_host='192.168.109.106',master_user='repuser', master_password='password', master_log_file='mysql-bin.000001', master_log_pos=341;
start slave;

 


To know if the replication works, we will first look at the slave status on the serverBDD2.

show slave status\G

20) show slave status

If the command returns you « Waiting for master to send event » in the first line, it is that everything works correctly.

If it sends you another message than this one, it is that the replication does not work.


Finally, we will test the replication by creating a table on the serverBDD1 and check if the serverBDD2 has the same table.

21) test

Let’s see now on the serverBDD2.

22) resultat test

Everything is working!

 

MASTER-MASTER REPLICATION

Master-master replication is a two-way replication. that is, both servers will be able to add, delete, modify the contents of the database to replicate.


To make a master-master replication, all you have to do is make a master-slave replication on both servers. that is, both servers will be both master and slave.

So we will do again what we did previously but this time with the server DBD1 as a slave and the server DBD2 as master, while keeping the configuration made previously.

 

Configuration of ServerBDD2

We just have to enter this command

grant replication slave on *.* to 'repuser'@'192.168.109.106' identified by 'password';

then

flush privileges;

We will need the folder name and position following the command:

show master status;

24) show master status bdd2.PNG

Configuration of ServeurBDD1

Now we indicate the server master to the serveurBDD1.

stop slave;
change master to master_host = '192.168.109.107', master_user = 'repuser', master_password = 'password', master_log_file = 'mysql-bin.000003', master_log_pos = 526;
start slave;

Normally if everything is correct, type the command

show slave status\G

25) show slave status

And if the command returns you « Waiting for master to send event » it is that everything is good.


We will now create a table « rep » with the serverBDD2 (which originally was the slave server and could not create, delete, edit, etc. ..) on the database « replication ».

use replication;
create table rep ( ID INT NOT NULL AUTO_INCREMENT, TEST VARCHAR(100) NOT NULL, PRIMARY key ( ID ));

Now we look at the tables to see if the table is in the replication database.

show tables;

27) show tables.PNG

We can see that we created a table.


Now we go to the server DBD1 to see if we have the new table and if we can delete it.

28) drop table.PNG

We can see that we have the new table and we managed to delete it.

 

 

SOURCES:

http://denisrosenkranz.com/tuto-replication-master-slave-avec-mysql-sous-debian-8/

http://www.responsive-mind.fr/replication-mysql-master-master/

Publicités

Laisser un commentaire

Entrez vos coordonnées ci-dessous ou cliquez sur une icône pour vous connecter:

Logo WordPress.com

Vous commentez à l'aide de votre compte WordPress.com. Déconnexion / Changer )

Image Twitter

Vous commentez à l'aide de votre compte Twitter. Déconnexion / Changer )

Photo Facebook

Vous commentez à l'aide de votre compte Facebook. Déconnexion / Changer )

Photo Google+

Vous commentez à l'aide de votre compte Google+. Déconnexion / Changer )

Connexion à %s