Setup MySQL master-master replication (Windows and Linux)

Ngày 20 tháng 5 năm 2013 Trương Chương Dương

Add firewall rules:

  1. iptables -A INPUT -p tcp -s 192.168.14.13 --sport 3306 -d 192.168.14.3 --dport 3306 -m state --state NEW,ESTABLISHED -j ACCEPT
  2. iptables -A OUTPUT -p tcp -s 192.168.14.3 --sport 3306 -d 192.168.14.13 --dport 3306 -m state --state ESTABLISHED -j ACCEPT


The next step is to configure both mysql using the file my.cnf (usualy on /etc in GNU/Linux) :

on master1 (192.168.14.13), under the mysqld section we add the following :

server-id=1
master-host=192.168.14.3
master-user=repl
master-password=repl
master-port=3306
log-bin=MASTER1

binlog-do-db=replication_db
replicate-do-db=replication_db

binlog-do-db=replication_db2
replicate-do-db=replication_db2

auto_increment_increment      = 10
auto_increment_offset         = 1

and on master2 (192.168.14.3), under the same section :

server-id=2
master-host=192.168.14.13
master-user=repl
master-password=repl
master-port=3306
log-bin=MASTER2

binlog-do-db=replication_db
replicate-do-db=replication_db

binlog-do-db=replication_db2
replicate-do-db=replication_db2

auto_increment_increment      = 10
auto_increment_offset         = 2

explanations of these settings :

  • server-id : this is an integer id helping to identify the server (must be unique in your replication farm!)
  • master-host : specifies the ip/hostname of the MySQL acting as master for the current server
  • master-user : specifies the user used to make the connection to the master
  • master-password : is the user's password
  • master-port : specifies on which port the master is listening
  • log-bin : needed to start the binary logging process
  • binlog-do-db : specifies on which databases the binary logging must be active (only those databases will be in the binary log)
  • replicate-do-db : which database must be replicated by the server as slave.
  • auto_increment_increment : controls the increment between successive AUTO_INCREMENT values.
  • auto_increment_offset : determines the starting point for AUTO_INCREMENT column values.

The last two options are needed to prevent key collisions when using AUTO_INCREMENT columns, otherwise multiple masters may attempt to use the same AUTO_INCREMENT value when inserting rows.

optionally two extra options can be added :

  • show-slave-auth-info : Display slave usernames and passwords in the output of SHOW SLAVE HOSTS on the master server.
  • slave_compressed_protocol={0|1} : this option enable the use of compression for the slave/master protocol if both the slave and the master support it. The default is 0 (no compression). This accelerates the replication process

Creating the replication user on both nodes :

on master1 :

mysql> grant replication slave, replication client on *.* to repl@"192.168.14.3" identified by "repl";
Query OK, 0 rows affected (0.00 sec)
mysql> show grants for repl@"192.168.14.3"; 
+--+
| Grants for repl@192.168.14.3                                                                                          |
+--+
| GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'repl'@'192.168.14.3' IDENTIFIED BY PASSWORD '5ec3db8f603fcb03' | 
+--+
1 row in set (0.00 sec)

on master2 :

mysql> grant replication slave, replication client on *.* to repl@"192.168.14.13" identified by "repl";
Query OK, 0 rows affected (0.00 sec)
mysql> show grants for repl@"192.168.14.13";
+---+
| Grants for repl@192.168.14.13                                                                                          |
+---+
| GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'repl'@'192.168.14.13' IDENTIFIED BY PASSWORD '5ec3db8f603fcb03' | 
+---+
1 row in set (0.00 sec)

Now, restart your both mysql server.

On master1, run this:
mysql>stop slave;
mysql>show master status;
*************************** 1. row ***************************           
       File: MASTER1.000001
       Position: 98
   Binlog_Do_DB: replication

Binlog_Ignore_DB:
1 row in set (0.00 sec)

On master2, run this:
mysql>stop slave;
mysql>show master status;
*************************** 1. row ***************************           
       File: MASTER2.000001
       Position: 87
   Binlog_Do_DB: replication

Binlog_Ignore_DB:
1 row in set (0.00 sec)

mysql>CHANGE MASTER TO

MASTER_LOG_FILE=’MASTER1.000001’, -- The file you got when ran show master status on master1

MASTER_LOG_POS=98; -- the pos you got when ran show master status on master1
mysql>START SLAVE;
 
On master1, run this:
mysql>CHANGE MASTER TO

MASTER_LOG_FILE=’MASTER2.000001’, -- The file you got when ran show master status on master2

MASTER_LOG_POS=87; -- the pos you got when ran show master status on master2
mysql>START SLAVE;

That all, now every change on a server will also apply to both.
 

For MySQL running on Windows comment out those lines:

On Server 1:
[mysqld]

############ Replication #####################
server-id=1
#master-host=IP Server-2 xxx.xxx.xxx.xxx
#master-user=repl
#master-password=repl
#master-port=3306
log-bin=server-bin
binlog-do-db=DB_name
binlog-ignore-db=mysql
binlog-ignore-db=test
replicate-do-db=DB_name
auto_increment_increment = 1
auto_increment_offset = 1
show-slave-auth-info

On Server 2:
[mysqld]

############ Replication #####################
server-id=2
#master-host=IP Server-1 xxx.xxx.xxx.xxx
#master-user=repl
#master-password=repl
#master-port=3312
log-bin=server-bin
binlog-do-db=DB_name
binlog-ignore-db=mysql
binlog-ignore-db=test
replicate-do-db=DB_name
auto_increment_increment = 1
auto_increment_offset = 2
show-slave-auth-info

After restarted you mysql, please run these command:
On master1, at the Command Prompt I entered the following commands:

mysql>CHANGE MASTER TO MASTER_HOST=’IP of SERVER2’,
MASTER_PORT=3306, # No quotes!
MASTER_USER=’repl’,
MASTER_PASSWORD=’repl’;

On master1, at the Command Prompt I entered the following commands:

mysql>CHANGE MASTER TO MASTER_HOST=’IP of SERVER1’,
MASTER_PORT=3312, # No quotes!
MASTER_USER=’repl’,
MASTER_PASSWORD=’repl’;

 

Đang tải dữ liệu...
web site traffic statistics