Come configurare multi master replica MySQL

Come configurare multi master replica MySQL

Introduzione ~

Avevo intenzione di scrivere l’articolo su Multi Master replica di MySQL da tempo, finalmente iniziata ora!. Si prega di consultare l’articolo on “Come configurare la replica di MySQL con un Master” URL ~ http://www.indiangnu.org/2007/mysql-replication-one-master-multiple-slave/

* Vorrei informare tutti voi che Multi replica Master in MySQL è puramente basato sulle seguenti due variabili. Non ha nulla a che fare con la tecnologia di replica utilizzato nella replica MySQL ….

mysql> show variables like ‘%increment_%’;
+——————————-+——-+
| Variable_name                 | Value |
+——————————-+——-+
| auto_increment_increment      | 1     |
| auto_increment_offset         | 1     |
+——————————-+——-+
2 rows in set (0.00 sec)

mysql>

** Requirements ~

a) Master Hosts (2 master in my case) ~
master-1 => 10.66.66.194
master-2 => 10.66.90.135
b) Replication Slave (1 slave) ~
Slave => 10.66.75.137

c) MySQL server (with replication support)

** Let us understand how it works ?

* Master-1 Server =>

Set following variables…

mysql> set auto_increment_increment=5;
mysql> set auto_increment_offset=1;

mysql> show variables like ‘%increment_%’;
+————————–+——-+
| Variable_name            | Value |
+————————–+——-+
| auto_increment_increment | 2     |
| auto_increment_offset    | 1     |
+————————–+——-+
2 rows in set (0.00 sec)

mysql>

** Create Table ~

mysql> create table class ( rollno INT(5) NOT NULL PRIMARY KEY AUTO_INCREMENT , name VARCHAR(30) );

** Add Record now ~

mysql> INSERT INTO class VALUES (”,’Arun Bagul’);
mysql> INSERT INTO class VALUES (”,’Ravi Bhure’);
mysql> INSERT INTO class VALUES (”,’Karthik Appigita’);
mysql> INSERT INTO class VALUES (”,’Ameya Pandit’);

mysql> SELECT * FROM class;
+——–+——————+
| rollno | name             |
+——–+——————+
|      1 | Arun Bagul       |
|      3 | Ravi Bhure       |
|      5 | Karthik Appigita |
|      7 | Ameya Pandit     |
+——–+——————+
4 rows in set (0.00 sec)

mysql>

* Master-2 Server =>

Set following variables…

mysql> set auto_increment_increment=2;
mysql> set auto_increment_offset=2;

mysql> show variables like ‘%increment_%’;
+————————–+——-+
| Variable_name            | Value |
+————————–+——-+
| auto_increment_increment | 2     |
| auto_increment_offset    | 2     |
+————————–+——-+
2 rows in set (0.00 sec)

mysql>

** Create Table ~

mysql> create table class ( rollno INT(5) NOT NULL PRIMARY KEY AUTO_INCREMENT , name VARCHAR(30) );

** Add Record now ~

mysql> INSERT INTO class VALUES (”,’Nilkanth Parab’);
mysql> INSERT INTO class VALUES (”,’Nishit Shah’);
mysql> INSERT INTO class VALUES (”,’Ram Krishna’);
mysql> INSERT INTO class VALUES (”,’Suhail Thakur’);

mysql> SELECT * FROM class;
+——–+——————+
| rollno | name             |
+——–+——————+
|      2 | Nilkanth Parab   |
|      4 | Nishit Shah      |
|      6 | Ram Krishna      |
|      8 | Suhail Thakur    |
+——–+——————+
4 rows in set (0.00 sec)

mysql>

** What is the importance of “auto_increment_increment” and “auto_increment_offset” ~

mysql> desc class;
+——–+————-+——+—–+———+—————-+
| Field  | Type        | Null | Key | Default | Extra          |
+——–+————-+——+—–+———+—————-+
| rollno | int(5)      | NO   | PRI | NULL    | auto_increment |
| name   | varchar(30) | YES  |     | NULL    |                |
+——–+————-+——+—–+———+—————-+

auto_increment_offset    => This is BASE value for column with “auto_increment” attribute (please refer the above example)
auto_increment_increment => This is the increment value for column with “auto_increment” attribute

** If you combine the both tables (master-1 and master-2) the final table will look like this ~

mysql> SELECT * FROM class;
+——–+——————+
| rollno | name             |
+——–+——————+
|      1 | Arun Bagul       |
|      2 | Nilkanth Parab   |
|      3 | Ravi Bhure       |
|      4 | Nishit Shah      |
|      5 | Karthik Appigita |
|      6 | Ram Krishna      |
|      7 | Ameya Pandit     |
|      8 | Suhail Thakur    |
+——–+——————+
8 rows in set (0.00 sec)

mysql>

** This is how Multi master replication works….

auto_increment_offset=Nth master server
auto_increment_increment=M

Nei casi in cui —
N => n-esimo numero di server master (il master-1 tenerlo 1 e il master-2 tenerlo 2 e così via ..)
M => Numero totale di Master Server (2 nel nostro caso, ma meglio per mantenere questo valore in modo che possiamo aggiungere facilmente nuovi server master)

log-slave-aggiornamenti => Server Slave non accede al proprio registro binario tutti gli aggiornamenti che vengono ricevuti da un server master. Questa opzione dice lo schiavo per accedere gli aggiornamenti eseguiti dai suoi thread SQL ai propri log binario.

** Assicurarsi che MySQL è in esecuzione e su tutti i server master e slave-server

Come configurare Multi Master replica MySQL? —

Step 1] Create Database/Tables on  Master  & Slave Servers –

You can create DB on all master & slave server or  create on one server and export that DB on  rest of all servers…

Master-1 => Create DB and Table

mysql> create database student;

mysql> use student;

mysql> create table class ( rollno INT(5) NOT NULL PRIMARY KEY AUTO_INCREMENT , name VARCHAR(30) );

mysql> show tables;
+——————-+
| Tables_in_student |
+——————-+
| class             |
+——————-+
1 row in set (0.00 sec)

mysql> desc class;
+——–+————-+——+—–+———+—————-+
| Field  | Type        | Null | Key | Default | Extra          |
+——–+————-+——+—–+———+—————-+
| rollno | int(5)      | NO   | PRI | NULL    | auto_increment |
| name   | varchar(30) | YES  |     | NULL    |                |
+——–+————-+——+—–+———+—————-+
2 rows in set (0.00 sec)

mysql> SELECT * FROM class;
Empty set (0.00 sec)
mysql>

* Now take dump of “student” DB and export it on all master and Slave server…

[root@master-1~]# mysqldump -u root -p  -d student > /home/arunsb/student.sql

* SCP the dump file on master-2 and slave server ~
[root@master-1~]# scp /home/arunsb/student.sql arunsb@10.66.90.135:/tmp/student.sql
[root@master-1~]# scp /home/arunsb/student.sql arunsb@10.66.75.137:/tmp/student.sql

Login on master-2 and slave ~

mysql> create database student;

[root@master-2~]# mysql -u root -p student < /tmp/student.sql
Enter password:
[root@master-2~]#

[root@master-2~]# mysql -u root -p
Enter password:

mysql> use student

mysql> SELECT * FROM class;
Empty set (0.00 sec)
mysql>

** Please repeat the same steps on Slave server as well…

Step 2] Update “my.cnf” config file on master-1,master-2 and slave server –

[root@master-1~]# cat /etc/my.cnf

###########################
##MySQL replication setting

#Master setting(1)
server-id = 1
log-bin = /var/log/mysql/binary/mysql-bin.log
binlog-do-db = student
binlog-ignore-db = mysql
#log = /var/log/mysql/mysql.log
auto_increment_offset=1
auto_increment_increment=5
log-slave-updates

##slave setting
master-port=3306
master-host=10.66.90.135
master-user=replication
master-password=mypwd
master-connect-retry=60
replicate-do-db=student
###########################

[root@master-1~]#

[root@master-2~]# cat /etc/mysql/my.cnf

###########################
##MySQL replication setting

#Master setting(2)
server-id = 2
log-bin = /var/log/mysql/binary/mysql-bin.log
binlog-do-db=student
binlog-ignore-db = mysql
log = /var/log/mysql/mysql.log
auto_increment_offset=2
auto_increment_increment=5
log-slave-updates

##slave setting
master-port=3306
master-host=10.66.66.194
master-user=replication
master-password=mypwd
master-connect-retry=60
replicate-do-db=student
###########################

[root@master-2~]#

* please create directory for binary log and set permission…

[root@master-1~]# mkdir -p /var/log/mysql/binary/
[root@master-1~]# chown mysql:adm  /var/log/mysql/ /var/log/mysql/binary/

[root@master-2~]# mkdir -p /var/log/mysql/binary/
[root@master-2~]# chown mysql:adm  /var/log/mysql/ /var/log/mysql/binary/

** MySQL Replication Slave ~

[root@slave~]# cat  /etc/my.cnf

[mysqld]

########################################
##slave setting
server-id=4
master-port=3306
master-host=10.66.90.135
master-user=replication
master-password=mypwd
master-connect-retry=60
replicate-do-db=student
########################################

[root@slave~]#

Step 3] Give Replication permission on both masters ~

** Master (1 & 2) ~

mysql> GRANT REPLICATION SLAVE ON *.* TO ‘replication’@’10.66.%.%’ IDENTIFIED BY ‘mypwd’;
Query OK, 0 rows affected (0.00 sec)

mysql>

Step 4] Restart MySQL on both master as well as replication slave server ~

** Please verify setting on master-1 and master-2 server…

* Master-1

mysql> show variables like ‘%increment_%’;
+————————–+——-+
| Variable_name            | Value |
+————————–+——-+
| auto_increment_increment | 5     |
| auto_increment_offset    | 1     |
+————————–+——-+
2 rows in set (0.00 sec)

* Master-2

mysql> show variables like ‘%increment_%’;
+————————–+——-+
| Variable_name            | Value |
+————————–+——-+
| auto_increment_increment | 5     |
| auto_increment_offset    | 2     |
+————————–+——-+
2 rows in set (0.00 sec)

** Please verify ‘master’ & ‘slave’ status on both masters(1 & 2) and slave –

mysql> show master status;
mysql> show slave status;

** Multi Master replication is started…

Step 5] Add few records on Master-1 & Master-2 server at same time ~

Add records on both master server at same time and check master and replication slave status as shown above….

** Add following on master-1

mysql> INSERT INTO class VALUES (”,’Arun Bagul’);
mysql> INSERT INTO class VALUES (”,’Ravi Bhure’);
mysql> INSERT INTO class VALUES (”,’Karthik Appigita’);
mysql> INSERT INTO class VALUES (”,’Ameya Pandit’);

** Add following on master-2

mysql> INSERT INTO class VALUES (”,’Nilkanth Parab’);
mysql> INSERT INTO class VALUES (”,’Nishit Shah’);
mysql> INSERT INTO class VALUES (”,’Ram Krishna’);
mysql> INSERT INTO class VALUES (”,’Suhail Thakur’);

** Please verify the numbers of records on both masters and slave….

mysql> SELECT * FROM class;
+——–+——————+
| rollno | name             |
+——–+——————+
|      1 | Arun Bagul       |
|      2 | Nilkanth Parab   |
|      6 | Ravi Bhure       |
|     11 | Karthik Appigita |
|     16 | Ameya Pandit     |
|     17 | Nishit Shah      |
|     22 | Ram Krishna      |
|     27 | Suhail Thakur    |
+——–+——————+
8 rows in set (0.00 sec)

mysql>

* Così abbiamo tutti imparato a configurare multi-master replica di MySQL. Enjoy!

Saluti,
Arun Bagul

Similar Posts:

Be Sociable, Share!

Lascia un Commento

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