كيفية تكوين متعدد سيد ماي المتماثل
مقدمة ~
كنت أخطط لكتابة مقال عن النسخ المتعددة ماجستير الخلية منذ وقت طويل ، وأخيرا بدأت الآن!. يرجى الرجوع إلى مقالة حول “كيفية تكوين الخلية المتماثل مع رئيسي واحد” عنوان ~ http://www.indiangnu.org/2007/mysql-replication-one-master-multiple-slave/
* واسمحوا لي أن أحيطكم علما بأن جميع النسخ المتعددة ماجستير في الخلية هو محض يستند التالية اثنين من المتغيرات. انه ليس له اي علاقة مع التكنولوجيا المستخدمة في النسخ المتماثل… ماي.
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
10
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
حيث —
ع => أقصى عدد من الخادم الرئيسي (على الماجستير – 1 يبقيه (1) وعلى الماجستير – 2 يبقيه 2) ، وهلم جرا…)
م => العدد الكلي للماجستير خادم (2 في حالتنا ولكن الأفضل أن تبقي هذه القيمة العالية حتى نتمكن من إضافة جديدة الملقم الرئيسي بسهولة)
سجل العبيد التحديثات => خادم الرقيق لا السجل الخاص به ثنائي تسجيل أية تحديثات التي يتم تلقيها من ملقم ماجستير. هذا الخيار يروي الرقيق لتسجيل الدخول على التحديثات التي يقوم بها والخيط مزود إلى سجل ثنائي الخاصة.
** تأكد من أن الخلية يشغل وحتى على جميع الخوادم الرئيسية والخادم الرقيق
كيفية الإعداد المتعددة ماجستير ماي تكرارها؟ —
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>
* ولذا فإننا تعلمنا جميعا لتكوين متعدد الماجستير ماي تكرارها. يتمتع!
تمنيات ،
Arun Bagul