MySQL master-slave replication and primary master replication

1. Introduction

As one of the most widely used databases in the world, MySQL is one of the reasons why it is free. But what is not to be overlooked is that its own function is indeed very powerful. With the development of technology, in the actual production environment, the single MySQL database server can not meet the actual demand. At this point the database cluster is a good solution to this problem. With MySQL distributed cluster, you can build a high-concurrency, load-balanced cluster Server (this blog is not involved for the time being). Prior to this we had to ensure that the data in each MySQL server was synchronized. Data synchronization we can easily complete with MySQL internal configuration, mainly with master-slave replication and primary master replication.

Back to top 2, environmental instructions

Two Linux virtual hosts

Linux version CentOS6.6, MySQL 5.5

ip:192.168.95.11, 192.168.95.12

Back to top 3, master-slave copy back to top 3.1, MySQL

is already installed and has no data.

Back to top 3.2, configuration file

MySQL configuration files in general Linux are all in/ETC/MY.CNF (the configuration file in Windows is Mysql.ini)

Log-bin=mysql-bin Open Binary Log

Note: The binary log must be turned on because the synchronization of the data is essentially the other MySQL database server executes the binary log of this data change again on this computer.

192.168.95.11 Primary database server

192.168.95.12 from the database server

Back to top 3.3, start building master-slave replication

The first step:

Create a MySQL user in 192.168.95.11 that can log on in a 192.168.95.12 host

Users: mysql12

Password: mysql12

Mysql>grant REPLICATION SLAVE on * * to ' mysql12 ' @ ' 192.168.95.12 ' identified by ' mysql12 ';

Mysql>flush privileges;

Step Two:

View 192.168.95.11MySQL server binary file name and location

Mysql>show MASTER STATUS;

Step Three:

Tell the binary file name and location

Execute in 192.168.95.12:

Mysql>change MASTER to

>master_host= ' 192.168.95.11 ',

>master_user= ' mysql12 ',

>master_password= ' mysql12 ',

>master_log_file= ' mysql-bin.000048 ',

>MASTER_LOG_POS=432;

Complete master-Slave replication configuration

Back to top 3.4, test master-slave replication

In the 192.168.95.12

Mysql>slave START; #开启复制

Mysql>show SLAVE status\g #查看主从复制是否配置成功

When you see Slave_io_running:yes and Slave_sql_running:yes, it shows the state is normal.

Actual test:

--Login 192.168.95.11 main MySQL

Mysql>show DATABASES;

--Landing 192.168.95.12 from MySQL

Mysql>show DATABASES;

-----------------------------------------------------

192.168.95.11 Main MySQL operation:

Mysql>create database AA;

Mysql>use AA;

Mysql>create table tab1 (id int auto_increment,name varchar), primary key (ID));

Mysql>show databases;

Mysql>show tables;

192.168.95.12 from MySQL operation:

Mysql>show databases;

Mysql>show tables;

From the above two results graph can be learned that the two hosts to achieve data synchronization. The configuration of master-slave replication is so simple.

Back to top 4, MySQL master copy back to top 4.1, principle of implementation

Primary master replication can change the data within both MySQL hosts, and the other host will make changes accordingly. Smart you may have thought of how it should be done. Yes, it would be good to combine two master-slave replication organically together. But in the configuration, we need to pay attention to some problems, for example, primary key duplication, Server-id can not be repeated and so on.

Back to top 4.2, configuration file

--192.168.95.11

Server-id=11 #任意自然数n, just ensure that the two MySQL hosts do not repeat.

Log-bin=mysql-bin #开启二进制日志

auto_increment_increment=2 #步进值auto_imcrement. Normally there are N main MySQL to fill n

Auto_increment_offset=1 #起始值. Generally fill the nth master MySQL. This is the first master MySQL

Binlog-ignore=mysql #忽略mysql库 "I don't usually write."

Binlog-ignore=information_schema #忽略information_schema库 "I don't usually write."

Replicate-do-db=aa #要同步的数据库, all libraries by default

--192.168.95.12

Server-id=12

Log-bin=mysql-bin

auto_increment_increment=2

auto_increment_offset=2

Replicate-do-db=aa

Restart MySQL after configuration.

back to top 4.3, start building primary master replication

Because primary master replication is a combination of two master-slave replication, I then proceed to the above master-slave replication and then configure.

The first step:

Create a MySQL user in 192.168.95.12 that can log on in a 192.168.95.11 host

Users: Mysql11

Password: mysql11

Mysql>grant REPLICATION SLAVE on * * to ' mysql11 ' @ ' 192.168.95.11 ' identified by ' mysql11 ';

Mysql>flush privileges;

Step Two:

Viewing binary log names and locations in 192.168.95.12

Mysql>show Master status;

Step Three:

Tell the binary file name and location

Execute in 192.168.95.11:

Mysql>change MASTER to

Master_host= ' 192.168.95.12 ',

Master_user= ' Mysql11 ',

Master_password= ' Mysql11 ',

Master_log_file= ' mysql-bin.000084 ',

master_log_pos=107;

Completing the primary master replication configuration

Back to top 4.4, test Master master replication

Open slave start respectively;

Mysql>show SLAVE status\g #查看主从复制是否配置成功

192.168.95.11

192.168.95.12

When you see Slave_io_running:yes and Slave_sql_running:yes, it shows the state is normal.

Test:

--192.168.95.11

Mysql>use AA;

Mysql>select*from Tab1;

TAB1 No data

--192.168.95.12

Mysql>use AA;

Mysql>select*from Tab1;

TAB1 No data

--192.168.95.11 Inserting data

Mysql>insert into TAB1 (name) value (' 11 '), (' 11 '), (' 11 ');

--192.168.95.12 Inserting data

Mysql>insert into TAB1 (name) value (' 22 '), (' 22 '), (' 22 ');

View data:

Two host data results as well!

Primary master replication Configuration succeeded!

Back to top 5, precautions

1, the primary master replication configuration file Auto_increment_increment and Auto_increment_offset can only guarantee that the primary key is not duplicated, but does not guarantee the primary key order.

2, when the configuration is complete slave_io_running, slave_sql_running is not all Yes, show Slave status\g information, there are errors, can be corrected according to the error prompt.

3, slave_io_running, slave_sql_running not all is yes, most of the problems are caused by the data is not unified.

Common error points:

1, both databases exist DB database, and the first MySQL DB has tab1, the second MySQL DB does not have TAB1, that certainly cannot succeed.

2, has obtained the data binary log name and the location, but also carries on the data operation, causes the POS to change. The previous POS was used when configuring change master.

3, stop slave, data changes, and then start slave. Error.

Ultimate Correction: Re-run the change master again.

(These are some of their own ideas, if there is insufficient or wrong place please point out)

MySQL master-slave replication and primary master replication