如何配置MySQL的多主复制
介绍〜
我正打算写多条主MySQL的复制很长时间以来，终于开始了！。请参阅“的文章：如何配置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保持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> 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