Configureren multi meester MySQL replicatie
Inleiding ~
Ik was van plan om te schrijven artikel over Multi Master MySQL replicatie sinds lange tijd eindelijk begonnen nu!. Raadpleeg het artikel over de “How to configure MySQL replicatie met een Master” URL ~ http://www.indiangnu.org/2007/mysql-replication-one-master-multiple-slave/
* Laat ik u meedelen dat alle Multi Master replicatie in MySQL is puur gebaseerd op de volgende twee variabelen. Het heeft niets te maken met replicatie technologie die wordt gebruikt in MySQL replicatie ….
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
Waar —
N => n aantal master server (op master-1 houdt het 1 en op master-2 houdt het 2 en zo verder ..)
M => Totaal aantal Master Server (2 in ons geval maar beter om deze waarde te hoog te houden zodat we nieuwe master server kunt toevoegen gemakkelijk)
log-slaaf-updates => Slave server niet inloggen om zijn eigen binaire log alle updates die worden ontvangen van een master server. Deze optie geeft de slaaf om de updates door haar SQL draad om haar eigen binaire log log.
** Zorg ervoor dat MySQL draait en tot op alle servers en master-slave-server
Hoe te opstelling Multi Master MySQL replicatie? —
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>
* Dus we allemaal geleerd voor het configureren van multi-master MySQL replicatie. Enjoy!
Groeten,
Arun Bagul