을 구성하는 방법 MySQL은 다중 마스터 복제
소개 ~
나도 오랫만에 MySQL은 다중 마스터 복제에 대한 글을 쓰기로 계획했다; 마지막으로 지금 시작하십시오!. 제발 “을 구성하는 방법 하나의 마스터”URL과 MySQL이 복제 문서를 참조하십시오 ~ http://www.indiangnu.org/2007/mysql-replication-one-master-multiple-slave/
* 내 모든 MySQL에 다중 마스터 복제 순수에 대한 다음과 같은 두 개의 변수를 기반으로 알려 보자. 그것은 복제 기술을 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
어디로 –
마스터 서버의 N = “니은 번째 번호를 마스터 (- 1 .. 그것을 2 이렇게 계속) 그것을 1 마스터 – 2 계속
우리의 경우 마스터 서버 (2 남 = “총 숫자지만, 높은 그렇게 우리는 새 마스터 서버를 쉽게 추가할 수 있도록이 값을 계속 더 나은)
로그인 노예 업데이 트 = “슬레이브 서버는 바이너리 자체는 마스터 서버에서 수신되는 모든 업데이 트를 로그에 기록하지 않습니다. 이 옵션은 슬레이브 업데이 트를 자사는 SQL 스레드에 의해 자신의 이진 로그에 로그인할 때 수행을 알려줍니다.
**이 MySQL과 최대의 모든 마스터 서버와 슬레이브 서버에서 실행되고 있는지 확인 –
어떻게 설정 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>
* 그래서 우리 모두를 구성하는 다중 마스터 복제 MySQL을 배웠습니다. 즐기십시오!
안부,
Arun Bagul