December 2009 – Page 3 – IndianGNU.org

Bagaimana mengkonfigurasi MySQL multi master replikasi

By Arun Bagul | December 9, 2009 | Comments 2 comments

Pendahuluan ~

Saya berencana untuk menulis artikel tentang Multi Master MySQL replikasi sejak lama; Akhirnya mulai sekarang!. Silakan merujuk artikel tentang “Bagaimana mengkonfigurasi MySQL replikasi dengan satu Master” URL ~ http://www.indiangnu.org/2007/mysql-replication-one-master-multiple-slave/

* Biarkan saya memberitahu Anda semua bahwa Multi Master replikasi di MySQL adalah murni didasarkan pada dua variabel berikut. Itu tidak ada hubungannya dengan teknologi yang digunakan replikasi replikasi di MySQL ….

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>

** 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>

* Master-2 Server =>

Set following variables…

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

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>

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

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

Mana —
N => n jumlah server master (di master-1 tetap 1 dan pada master-2 tetap 2 dan seterusnya ..)
M => Total jumlah Master Server (2 dalam kasus kami, tetapi lebih baik untuk menjaga nilai ini tinggi sehingga kita dapat menambahkan server master baru dengan mudah)

log-budak-update => slave server tidak log untuk log biner sendiri setiap update yang diterima dari server Master. Opsi ini menceritakan budak untuk log pembaruan yang dilakukan oleh SQL thread ke biner sendiri log.

** Pastikan bahwa MySQL berjalan dan sampai pada semua server master dan slave server –

Cara men-setup replikasi MySQL Multi Master? —

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> 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

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

* Master-2

** 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

** Add following on master-2

** 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>

* Jadi kita semua belajar untuk mengkonfigurasi MySQL multi-master replikasi. Nikmatilah!!

Salam,
Arun Bagul

كيفية تكوين متعدد سيد ماي المتماثل

By Arun Bagul | December 9, 2009 | Comments 0 Comment

مقدمة ~

كنت أخطط لكتابة مقال عن النسخ المتعددة ماجستير الخلية منذ وقت طويل ، وأخيرا بدأت الآن!. يرجى الرجوع إلى مقالة حول “كيفية تكوين الخلية المتماثل مع رئيسي واحد” عنوان ~ http://www.indiangnu.org/2007/mysql-replication-one-master-multiple-slave/

* واسمحوا لي أن أحيطكم علما بأن جميع النسخ المتعددة ماجستير في الخلية هو محض يستند التالية اثنين من المتغيرات. انه ليس له اي علاقة مع التكنولوجيا المستخدمة في النسخ المتماثل… ماي.

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>

** Create Table ~

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

** Add Record now ~

mysql>

* Master-2 Server =>

Set following variables…

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

mysql>

** Create Table ~

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

** Add Record now ~

mysql>

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

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

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> 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

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

##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

##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]

[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

* Master-2

** 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

** Add following on master-2

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

mysql>

* ولذا فإننا تعلمنا جميعا لتكوين متعدد الماجستير ماي تكرارها. يتمتع!

تمنيات ،
Arun Bagul

Cómo configurar la replicación de varios maestros de MySQL

By Arun Bagul | December 9, 2009 | Comments 0 Comment

Introducción ~

Pensaba escribir el artículo sobre la replicación Multi Master MySQL desde hace mucho tiempo, y finalmente empezar ahora mismo!. Por favor, consulte el artículo sobre “Cómo configurar la replicación de MySQL con un Maestro” URL ~ http://www.indiangnu.org/2007/mysql-replication-one-master-multiple-slave/

* Quisiera informar a todos ustedes que varios maestros de replicación en MySQL se basa puramente en las siguientes dos variables. No tiene nada que ver con la tecnología de replicación utilizan en la replicación de MySQL ….

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>

** Create Table ~

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

** Add Record now ~

mysql>

* Master-2 Server =>

Set following variables…

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

mysql>

** Create Table ~

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

** Add Record now ~

mysql>

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

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

mysql>

** This is how Multi master replication works….

auto_increment_offset=Nth master server
auto_increment_increment=M

Donde —
N => número n de servidor maestro (en master-1 mantenga 1 y en master-2 mantenga el 2, etc ..)
M => Número total de Master Server (2 en nuestro caso, pero mejor para mantener este alto valor para que podamos añadir fácilmente nuevas servidor maestro)

log-slave-updates => servidor esclavo no registra a su propio registro binario las actualizaciones que se reciben de un servidor maestro. Esta opción le dice al esclavo en el registro de las actualizaciones realizadas por el flujo SQL en el log binario del esclavo.

** Asegúrese de que MySQL se está ejecutando y hasta en todos los servidores maestro y servidor de esclavos

¿Cómo configurar múltiples replicación de 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> 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

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

##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

##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]

[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

* Master-2

** 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

** Add following on master-2

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

mysql>

* Así que todos aprendimos a varios configurar la replicación MySQL maestro. Disfrute!

Recuerdos,
Arun Bagul

Как настроить несколько мастер репликации MySQL

By Arun Bagul | December 9, 2009 | Comments 0 Comment

Введение ~

Я собирался написать статью о Multi репликации MySQL так долго, наконец начали сейчас!. См. статью на тему “Как настроить репликации MySQL с одним ведущим” URL ~ http://www.indiangnu.org/2007/mysql-replication-one-master-multiple-slave/

* Позвольте мне сообщить вам все, что многолетние репликации в MySQL основывается исключительно на следующие две переменные. Она не имеет ничего общего с репликацией технологии, используемые в репликации MySQL ….

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>

** Create Table ~

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

** Add Record now ~

mysql>

* Master-2 Server =>

Set following variables…

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

mysql>

** Create Table ~

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

** Add Record now ~

mysql>

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

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

mysql>

** This is how Multi master replication works….

auto_increment_offset=Nth master server
auto_increment_increment=M

Где —
N => NTH ряд мастер-сервера (на мастер-1 Keep It 1 и 2 мастер-Keep It 2 и так далее ..)
M => целый ряд мастер-сервер (2 в нашем случае, но лучше держать это значение высокой, так что мы можем добавить новый мастер-сервера легко)

Log-славянский Обновления => Славянский сервер не войти в свой собственный бинарный журнал любые обновления, которые были получены от главного сервера. Эта опция говорит раб журнала обновлений исполнении своих SQL поток своих собственных бинарных журналов.

** Убедитесь, что MySQL работает и до мастера на все серверы и серверы-славянский

Как настроить Multi Master 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> 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

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

##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

##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]

[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

* Master-2

** 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

** Add following on master-2

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

mysql>

* Таким образом, мы все узнали, настроить несколько репликации MySQL. Наслаждайтесь!

Привет,
Arun Bagul

を構成する方法マルチマスターのMySQLレプリケーション

By Arun Bagul | December 9, 2009 | Comments 0 Comment

はじめに〜

私は長い間、マルチマスタのMySQLレプリケーションで記事を書くことを計画していた。最後に、今すぐ開始！。してください”を構成する方法1つのマスター”のURLでのMySQLのレプリケーションは、記事参照してください〜 http://www.indiangnu.org/2007/mysql-replication-one-master-multiple-slave/

*私のすべては、MySQLのマルチマスタのレプリケーションは純粋に2つの以下の変数に基づいていますことをお知らせしましょう。これはレプリケーションテクノロジー、MySQLのレプリケーションで使用されるとは何の関係もない…。

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>

** Create Table ~

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

** Add Record now ~

mysql>

* Master-2 Server =>

Set following variables…

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

mysql>

** Create Table ~

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

** Add Record now ~

mysql>

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

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

mysql>

** This is how Multi master replication works….

auto_increment_offset=Nth master server
auto_increment_increment=M

どこ –
マスタサーバのN =”n番目の番号マスタ（上の1 ..上に2などを保持）が1とマスタ- 2上の維持
当社の場合、マスターサーバ（2のM =”の合計数が高いので、簡単、新しいマスタサーバーを追加することができますがこの値を維持する方）

ログ- slave – updatesを=”スレーブサーバは、バイナリ、独自にマスターサーバーから受信されたすべての更新ログに記録されません。このオプションは、スレーブの更新プログラムは、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> 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

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

##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

##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]

[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

* Master-2

** 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

** Add following on master-2

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

mysql>

*だから、すべての設定、マルチマスタのMySQLのレプリケーションを学びました。をお楽しみください！

よろしく、
Arun Bagul

Come configurare multi master replica MySQL

By Arun Bagul | December 9, 2009 | Comments 0 Comment

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>

** 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>

** Create Table ~

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

** Add Record now ~

mysql>

* Master-2 Server =>

Set following variables…

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

mysql>

** Create Table ~

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

** Add Record now ~

mysql>

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

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

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> 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

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

##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

##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]

[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

* Master-2

** 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

** Add following on master-2

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

mysql>

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

Saluti,
Arun Bagul

Wie konfiguriert man Multi-Master-Replikation MySQL

By Arun Bagul | December 9, 2009 | Comments 0 Comment

Einführung ~

Ich hatte geplant, Artikel auf Multi-Master-Replikation MySQL seit langer Zeit schreiben, schließlich begann jetzt!. Verwenden Sie hierzu den Artikel “How to configure MySQL-Replikation mit einem Master” URL ~ http://www.indiangnu.org/2007/mysql-replication-one-master-multiple-slave/

* Lassen Sie mich zu informieren, dass Sie alle Multi-Master-Replikation in MySQL ist lediglich auf die beiden folgenden Variablen. Es hat nichts mit Replikations-Technologie in die MySQL-Replikation verwendet tun ….

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>

** Create Table ~

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

** Add Record now ~

mysql>

* Master-2 Server =>

Set following variables…

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

mysql>

** Create Table ~

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

** Add Record now ~

mysql>

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

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

mysql>

** This is how Multi master replication works….

auto_increment_offset=Nth master server
auto_increment_increment=M

Wo —
N => n-te Zahl der Master-Server (auf Master-1 keep it 1 und Master-2 keep it 2 und so weiter ..)
M => Anzahl der Master-Server (2 in unserem Fall aber besser, diesen Wert so hoch zu halten, dass wir neue Master-Server hinzufügen können)

log-slave-updates => Slave-Server nicht anmelden, um sein eigenes Binärlog alle Aktualisierungen, die von einem Master-Server empfangen werden. Diese Option weist den Slave die Updates von der SQL-Thread auf seine eigenen Binärlog zu schreiben.

** Stellen Sie sicher, dass MySQL läuft und sich auf allen Servern und Master-Slave-Server –

How to setup Multi-Master-Replikation 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> 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

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

##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

##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]

[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

* Master-2

** 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

** Add following on master-2

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

mysql>

* Also haben wir alle gelernt, zu konfigurieren, Multi-Master-Replikation MySQL. Viel Spaß!

Grüße,
Arun Bagul

Comment faire pour configurer plusieurs maître de réplication MySQL

By Arun Bagul | December 9, 2009 | Comments 0 Comment

Introduction ~

J’avais l’intention d’écrire l’article sur Multi réplication MySQL maître depuis longtemps, a finalement commencé maintenant!. S’il vous plaît consulter l’article sur le «Comment faire pour configurer la réplication MySQL avec un Maître” URL ~ http://www.indiangnu.org/2007/mysql-replication-one-master-multiple-slave/

* Permettez-moi de vous informer que la réplication Multi Master en MySQL est purement basé sur la suite de deux variables. Cela n’a rien à voir avec la technologie de réplication utilisée dans la réplication MySQL ….

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>

** Create Table ~

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

** Add Record now ~

mysql>

* Master-2 Server =>

Set following variables…

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

mysql>

** Create Table ~

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

** Add Record now ~

mysql>

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

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

mysql>

** This is how Multi master replication works….

auto_increment_offset=Nth master server
auto_increment_increment=M

Où —
N => nième nombre de serveur maître (sur le maître-1 garder 1 et sur le maître-2 maintenir 2 et ainsi de suite ..)
M => Nombre total de Master Server (2 dans notre cas, mais il vaut mieux laisser cette valeur élevée afin que nous puissions ajouter facilement de nouveaux serveur maître)

mises à jour log-slave-=> serveur esclave ne vous connectez pas à son propre log binaire les mises à jour qui sont reçus à partir d’un serveur maître. Cette option indique à l’esclave va enregistrer les modifications effectuées par son thread SQL à ses propres logs binaires.

** Assurez-vous que MySQL est en cours d’exécution et sur tous les serveurs et le serveur maître-esclave

Comment faire pour une configuration multi-maître de réplication 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> 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

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

##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

##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]

[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

* Master-2

** 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

** Add following on master-2

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

mysql>

* Ainsi, nous avons tous appris à plusieurs configurer la réplication MySQL maître. Enjoy!

Observe,
Arun Bagul

Configureren multi meester MySQL replicatie

By Arun Bagul | December 9, 2009 | Comments 0 Comment

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>

** 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>

** Create Table ~

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

** Add Record now ~

mysql>

* Master-2 Server =>

Set following variables…

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

mysql>

** Create Table ~

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

** Add Record now ~

mysql>

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

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

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> 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

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

##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

##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]

[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

* Master-2

** 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

** Add following on master-2

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

mysql>

* Dus we allemaal geleerd voor het configureren van multi-master MySQL replicatie. Enjoy!

Groeten,
Arun Bagul

如何配置MySQL的多主复制

By Arun Bagul | December 9, 2009 | Comments 0 Comment

介绍〜

我正打算写多条主MySQL的复制很长时间以来，终于开始了！。请参阅“的文章：如何配置MySQL复制一个法师”网址〜 http://www.indiangnu.org/2007/mysql-replication-one-master-multiple-slave/

*请让我告诉大家，在MySQL中的多主复制纯粹是基于以下两个变量。它没有任何关系，在MySQL的复制使用复制技术…。

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>

** Create Table ~

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

** Add Record now ~

mysql>

* Master-2 Server =>

Set following variables…

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

mysql>

** Create Table ~

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

** Add Record now ~

mysql>

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

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

mysql>

** This is how Multi master replication works….

auto_increment_offset=Nth master server
auto_increment_increment=M

凡 –
ñ =“的主服务器的第n位（硕士- 1保持1和掌握- 2保持2等等..）
m等于“总数的主服务器（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> 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

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

##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

##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]

[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

* Master-2

** 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

** Add following on master-2

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

mysql>

*所以，我们都学会了配置多主MySQL的复制。享受！

关心，
Arun Bagul