Primary master replication for MySQL cluster (ii)

Described in front of the master-slave replication, this article I will introduce is the main master replication, in fact, listen to the name can be known, the main master replication is actually two servers between the main node and slave nodes. Next I will give you a detailed introduction, how to configure Master Master replication!

One, the master-slave replication problem 1.1, from the node occupied the primary node's self-increment ID

Environment:

Master node: zyhserver1=1.0.0.3

From node: udzyh1=1.0.0.5

The first step: we create a database db_love_1 in the master node, creating a table tb_love (with ID increment and name attribute).

int primary key auto_increment, name varchar (());

The second step: Add a piece of data to the master node, and we can see the data in both of them.

Insert into Tb_love (name) VALUES ('zhangsan');

Step three: If we are adding a piece of data from a node

Insert into Tb_love (name) VALUES ('Lisi');

In the From node: in the master node:

This is natural because we are master-slave replication, only the primary node writes the data to be synchronized to the slave node, and the data from the node is not synchronized with the primary node. Because the slave node does not have a binary log file, and the master node does not have a relay log file, the corresponding function is completed.

Fourth Step: If we insert a piece of data in the master node

Insert into Tb_love (name) VALUES ('wangwu');

In the master node: in the slave node:

Analysis: At this point we will find that the slave node does not update the WANGWU of the master node because the location of the ID 2 from the node is already occupied, and then we look at the state from the node:

　　　　　　

Here we can see that the IO thread from the node is open, and the SQL thread is closed. That leads to a broken master-slave relationship, so how do we recover it?

We first stop slave from the node, then perform the reset slave and then re-make the change from the node to the master to.

1.2, pre-master relationship before the premise of the establishment

In fact, before building a master-slave relationship, we need to ensure that two points:

1) One is that the structure of the database and the table is the same, that is, what database and table in the master node should have the database and table from the node.

(If there is a database in the master node that is not in the node, then when we delete the database, there will be no error from the node)

2) The second is to ensure the master-slave node: The database primary key self-increment of the same step, but the self-increment start position is inconsistent.

(a self-increment starting from 1, the resulting primary key is: 1,3,5,7,9. Another starting from 2, the generated primary key is: 2,4,6,810)

If it is a dual-master, there is no need to set, but if it is master-slave mode and the primary and slave nodes can insert data, so the data inserted from the node cannot be synchronized to the master node.　　If the primary node then inserts data with the same ID, the error occurs when synchronizing to the slave node.　　　　How do you set it up? Temporary settings:

The primary node of the MySQL terminal  executes:　　　　set auto_increment_increment=2  set auto_increment_offset=1  Execution from the node's MySQL terminal    :　　set auto_increment_increment=2    set auto_increment _offset=2　　　　

Permanent settings, if the MySQL service is restarted or reset:

The primary node of the MySQL terminal   executes: Set Global auto_increment_increment=2  setglobal auto_increment_offset= 1 from the node's MySQL terminal execution  :　　 Set Global auto_increment_increment=2  setglobal auto_ increment_offset=2　　　　　

1.3, in the construction of MySQL cluster master-slave replication encountered problems

1) View the status of the slave appears to be

　　　　

Check to see if the host (preferably using IP), port, user, password, filen, Pos are correct when you change.

There is no real user zyh created. If not, see if the two servers can ping.

2) Master node can ping the slave node and vice versa

Because we have two virtual machines installed in VMware, one with the bridge mode and one with the NAT mode, so

It's useful to change the bridging mode to NAT mode.

1.4, understand the content of Binary-log file acquisition

After the Slave IO thread receives the information, it writes the received log content to the end of the Relaylog file (mysql-relay-bin.xxxxxx) on the Slave side and reads the Bin-log of the master end The file name and location are recorded in the Master-info file

Slave's SQL thread detects that the content in the Relay log is newly added, it immediately resolves the contents of the log file into those executable query statements that were executed at the Master end , and executes the query itself.

Analysis: How did the slave IO thread read the SQL statement? In fact, it does not directly obtain the SQL statement written to the master node. Instead, the results of data changes (such as INSERT, delete, modify operations) are queried (analyzed) in the master node

To generate a SQL statement to the binary log file, so why do we specify the query statement in the master node, and the node does not do the query operation.

Second, the primary master replication

In fact, we learned the master-slave replication, the main master copy understanding is quite simple. is not the configuration from the node, the node plus the master node in the master node!

2.1. Master-Master Replication understanding

1) Authorized account on slave node
2) in the master node slave configuration, the original slave as master to connect

　　　

2.2. Primary master replication process

Environment:

Ubuntu Server Edition: 1.0.0.3==server1 (Master node)

Ubuntu Desktop Edition (two units): 1.0.0.5=udzyh1 (from node)

1) In fact, our initial configuration (in the Mysqld.cnf file) is server1-->udzyh1:

In the master node:

server-id= One log-bin=mysql-bin- one binlog-FORMAT=RPW

In the From node:

server-id=relay-log=mysql-relay-

2) We need to add the configuration from the node to the master node and the configuration of the master node to the slave node

In the main node plus:

relay-log=mysql-relay-　　

On the Slave node, add:

lob-bin=mysql-bin-  binlog-format=row

　　　　When we restart the service, we can generate the trunk log file under/var/lib/mysql, and the binary log file will be generated from the node . We are still in the configuration file

Add skip-name-resolve reverse Domain name parsing off, can speed up the run (just shut down MySQL)

3) connection

Run in UDZYH1: Grant replication Slave,replication Client on * * to ' zyh ' @ '% ' identified by ' 123456 ';(create a user on the master node)

Then run the MySQL terminal in Server1:

　　　　

4) then turn on master-slave replication in Server1

Start slave

Note: There are two common operations

inch ' mysql-bin-11.0000001 ' \g

Third, the master replication of MySQL cluster in-depth discussion 3.1, resolve the primary key conflict problem

1) If the two nodes are simple, you can make the first node ID increment step to 2 starting point of 1, let the second node ID increment step to 2 starting point 2
Set Session/set global auto_increment_increment=2
Set Session/set global Auto_increment_offset=1

2) Use primary key generator or primary key server

3.2. Passive primary master replication for Mysql cluster

Both servers are master but one is a read-only server and cannot be inserted to modify data.
Add Read-only=1 to the my.conf configuration file for the read-only server (this option can be ignore for users with super privileges), primarily to back up the master server

　　

Note: But we generally do not do this, we will do it through Mysql-proxy (explained later)

3.3. How nodes are deployed

　　

Cannot allow one slave node to replicate multiple master nodes

　　　　

Primary master replication for MySQL cluster (ii)