Mysql master-slave configuration (dual-Master Model)

I. Implementation conditions of A and B master models: 1. enable binary log 2. enable relay log 3. solve the Problem of Automatically increasing columns. If the column number Automatically increasing on server A has A value of 35, it is not synchronized to server B yet.

I. Implementation conditions of A and B master models: 1. enable binary log 2. enable relay log 3. solve the Problem of Automatically increasing columns. If the column number Automatically increasing on server A has A value of 35, it is not synchronized to server B yet.

I. Implementation conditions of A and B master models:

1. Enable binary log

2. Enable relay logs

3. Solve the Problem of automatic column Growth

If the column number that automatically grows on server A has A 35, it is not synchronized to server B yet, and A piece of data is inserted on server B. The number is also 35. When the 35-to-B servers of A are synchronized, data will inevitably be lost.

Solution:

The automatic growth of rows inserted on server A is an odd number, and the automatic growth of server B is an even number. This solves the problem of automatic growth.

Assume that A is A database in A production environment. Now I want to add server B to implement the dual-master model.

2. steps:

1. Create an account with the copy permission on server A and server B

2. modify the configuration file (enable binary logs and relay logs) on server A and server B)

3. Import the data files on server A to server B.

Note: When importing data, disable the binary log of server B.

4. Let B first become slave, and then let A become slave

5. Test

3. Start Configuration

1. Create an authorized user

Mysql> grant replication slave on *. * to 'slave '@' 192. 168.2.96 'identified by '20140901'; Query OK, 0 rows affected (12345 sec) # A server mysql> grant replication slave on *. * to 'slave '@' 192. 168.2.93 'identified by '123'; Query OK, 0 rows affected (12345 sec) # server B

2. Edit the configuration file

[Root @ oracle ~] # Vim/etc/my. cnflog-bin = mysql-bin # enable the binary log server-id = 1relay-log = mysql-relay-bin # enable the relay log log_slave_updates = on # record time from the server to the binary log auto_increment_increment = 2 # auto_increment_offset = 1 # start value of automatic growth # configuration of server A [root @ node2 ~] # Vim/etc/my. cnfserver-id = 2 # log-bin = mysql-bin # log_slave_updates = onauto_increment_increment = 2auto_increment_offset = 2 # configure Server B, disable binary logs first

Restart service

[Root @ oracle ~] # Service mysqld restartShutting down MySQL... [OK] Starting MySQL. [OK]

Create a test table

Mysql> select * from info; + ----- + ------- + ----- + | sid | name | age | + ----- + ------- + ----- + | 1 | zhang | 23 | 2 | li | 12 | 3 | cheng | 34 | 4 | wang | 22 | 5 | chen | 44 | + ----- + ------- + ----- + 5 rows in set (0.00 sec) # create A test table on server

3. Import the table to server B.

[Root @ oracle ~] # Mysqldump -- databases data -- lock-all-tables -- master-data = 2>/root/dump. SQL [root @ oracle ~] # Scp/root/dump. SQL root@192.168.2.96:/root/# A server dump + copy [root @ node2 ~] # Mysql <dump. SQL # executed on server B

4. Let server B first become slave

Mysql> change master to master_host = '2017. 168.2.93 ', master_user = 'slave', master_password = '000000', master_port = 12345, MASTER_LOG_FILE = 'mysql-bin.000007', MASTER_LOG_POS = 3306; # MASTER_LOG_FILE and MASTER_LOG_POS at dump. [root @ node2 ~] records in SQL # Vim/etc/my. cnf # validate the parameter just commented out log-bin = mysql-binlog_slave_updates = on [root @ node2 ~] # Service mysqld restartShutting down MySQL... [OK] Starting MySQL. [OK]

Let server A become slave again

Mysql> show master status; + metric + ---------- + -------------- + ---------------- + metric + | File | Position | Binlog_Do_DB | metric | usage | + ------------------ + ---------- + -------------- + metric + mysql-bin.000001 | 120 | | + ------------------ + ---------- + -------------- + -------------------- + ------------------- + 1 row in set (0.00 sec) # view binary log information on B mysql> change master to master_host = '2017. 168.2.96 ', master_user = 'slave', master_password = '000000', master_port = 12345, MASTER_LOG_FILE = 'mysql-bin.000001 ', MASTER_LOG_POS = 3306; mysql> start slave;

View the status of server A and server B:

Mysql> show slave status \ G; ***************************** 1. row ************************** Slave_IO_State: Waiting for master to send eventMaster_Host: 192.168.2.96Master _ User: slaveMaster_Port: 3306Connect_Retry: 60Master_Log_File: mysql-bin.000001Read_Master_Log_Pos: 120Relay_Log_File: mysql-relay-bin.000002Relay_Log_Pos: mysql-bin.000001Slave_IO_Running: YesSlave_ SQL _Running: Yes # status on A server mysql> show slave status \ G; * *************************** 1. row ************************** Slave_IO_State: Waiting for master to send eventMaster_Host: 192.168.2.93Master _ User: slaveMaster_Port: 3306Connect_Retry: 60Master_Log_File: mysql-bin.000008Read_Master_Log_Pos: 120Relay_Log_File: mysql-relay-bin.000003Relay_Log_Pos: mysql-bin.000008Slave_IO_Running: YesSlave_ SQL _Running: Yes # view status on B server

5. test:

Mysql> insert into info (name, age) values ('sun', 25), ('ding', 29 ); # insert 2 rows of data on server A mysql> select * from info; + ----- + ------- + ----- + | sid | name | age | + ----- + ------- + ----- + | 1 | zhang | 23 | 2 | li | 12 | 3 | cheng | 34 | 4 | wang | 22 | 5 | chen | 44 | 7 | sun | 25 | 9 | ding | 29 | + ----- + ------- + ----- + 7 rows in set (0.00 sec) # view results on server B

Mysql> insert into info (name, age) values ('bb ', 33), ('bc', 21 ); # insert two rows of data on server B mysql> select * from info; + ----- + ------- + ----- + | sid | name | age | + ----- + ------- + ----- + | 1 | zhang | 23 | 2 | li | 12 | 3 | cheng | 34 | 4 | wang | 22 | 5 | chen | 44 | 7 | sun | 25 | 9 | ding | 29 | 10 | BB | 33 | 12 | BC | 21 | + ----- + ------- + ----- + 9 rows in set (0.00 sec) # view on server

In this way, the dual-master model has been implemented.

This article is from the "My favorite technology" blog. Please keep this source