Como configurar vários master replicação do MySQL
Introdução ~
Eu estava planejando escrever artigo sobre a replicação do MySQL Multi Master desde muito tempo, finalmente começou agora!. Por favor, consulte o artigo sobre “Como configurar a replicação do MySQL com um mestre” URL ~ http://www.indiangnu.org/2007/mysql-replication-one-master-multiple-slave/
* Deixe-me informá-lo de tudo o que Multi master replicação no MySQL é puramente baseado em duas variáveis. Não tem nada a ver com a tecnologia de replicação usado em replicação do 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
Onde —
N => número enésima de servidor principal (master-1 em mantê-1 e no master-2 mantê-la 2 e assim por diante ..)
M => número total de Servidor Mestre (2 no nosso caso, mas melhor para manter este alto valor, para que possamos adicionar novo servidor mestre facilmente)
log-slave-updates => Servidor Slave não registra o seu próprio log binário quaisquer actualizações que são recebidas de um servidor mestre. Esta opção diz ao slave para registrar as atualizações feitas pela thread de SQL para seu próprio log binário.
** Certifique-se que o MySQL está rodando e até em todos os servidores master e slave-servidor
Como configurar Multi Master replicação do 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>
* Portanto, todos nós aprendemos a configurar multi-replicação do MySQL mestre. Aproveite!
Atenciosamente,
Arun Bagul