Primary master configuration in MySQL passive mode

MySQL architecture is the simplest to use the most is the master-slave, the main master and so on, the master-slave has a switching problem, from the library is not writable, in the main library under certain circumstances, the switch is very troublesome, here can use the main main mode.

But the Lord also has a problem, that is, both sides of the simultaneous writing may conflict, the primary key conflict, although can be used to solve the odd and even primary key, or the program to do, but add trouble, here with the passive mode of the master is more convenient.

Two server settings

1. Two servers are set up on binary logs and relay logs:

#给服务器命名一个id

server_id=140

#声明二进制日志的文件为mysql-bin.xxx

Log-bin=mysql-bin

#二进制日志的格式: Mixed/row/statement

Binlog_format=mixed

#主主复制时都需要配置relay-log

Relay-log=mysql-relay

2. To deal with the empowerment and to set against each other as their own master

20:grant replication Slave on * * to ' repl ' @ ' 10.0.67.19 ' identified by ' 123456 ';

19:change Master to master_host= ' 10.0.67.20 ', master_user= ' repl ', master_password= ' 123456 ', master_port=3306, Master_ Log_file= ' mysql-bin.000004 ', master_log_pos=1318;

19:grant replication Slave on * * to ' repl ' @ ' 10.0.67.20 ' identified by ' 123456 ';

20:change Master to master_host= ' 10.0.67.19 ', master_user= ' repl ', master_password= ' 123456 ', master_port=3306, Master_ Log_file= ' mysql-bin.000004 ', master_log_pos=1318;

3. Turn on Slave:

Start slave

4. View slave status

Show Slave Status\g

Primary master replication in passive mode:

1. Concept: Refers to 2 server status, but one of them is read-only, and the business also only write a certain 1 servers;

2. Benefits: If the server for the write fails, can quickly switch to from the server, or for maintenance purposes, the write function to another server is also more convenient;

3. Implementation: Add the following configuration to the my.cnf of the read-only server: Read-only=on;

Sync Conflict Issues:

Extract:

1. Master replication must be aware of the problems to avoid, synchronization conflicts, for example:

CREATE TABLE Stu (

ID int primary KEY auto_increment.

)......

2 MySQL status is equal, if 2 requests to reach 2 servers at the same time, the requested a node Stu ID is 1, the requested B node Stu ID is 1, this time there is a synchronization conflict problem;

2. Solution:

Let the primary key of a server grow by odd number, and the primary key of B server grows by even;

A server:

Set global auto_increment_increment = 2; Increments per increment

Set global auto_increment_offset = 1; Initial self-increment number

Set session auto_increment_increment = 2;

Set session Auto_increment_offset = 1;

B Server:

Set global auto_increment_increment = 2;

Set global auto_increment_offset = 2;

Set session auto_increment_increment=2;

Set session Auto_increment_offset = 2;

Note: auto-increment-increment and Auto-increment-offset to write to the configuration file, to prevent the next restart after the failure;

Note: If you need to add servers later, there is a limit to this approach.

Method: We can solve the business logic, for example, in Oracle has sequence sequence, sequence each access to generate increment/decrement of data, in the case of Redis, we can build a global:userid, each time php before inserting MySQL, first incr- >global:userid, get a non-repeating userid;

Primary master configuration in MySQL passive mode