Shulou(Shulou.com)06/03 Report--

1. Introduction

MySQL is one of the most widely used databases in the world, and free is one of the reasons. However, it cannot be ignored that its own functions are indeed very powerful. With the development of technology, in the actual production environment, a single MySQL database server can not meet the actual needs. Database clustering solves this problem well. MySQL distributed cluster can be used to build a cluster server with high concurrency and Load Balancer (this blog does not cover it for the time being). Before that, we must ensure that the data in each MySQL server is synchronized. Data synchronization can be easily accomplished through MySQL internal configuration, mainly master-slave replication and master-master replication.

back to top

2. Environmental Description

Two Linux virtual hosts

Linux versions CentOS 6.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

It's installed and there's no data.

back to top

3.2 configuration file

MySQL configuration files in Linux are generally located in/etc/my.cnf(mysql.ini configuration file in Windows)

log-bin=mysql-bin Open binary log

Note: Binary logging must be turned on, because synchronization of data essentially means that other MySQL database servers perform binary logging of data changes locally.

192.168.95.11 is the main database server

192.168.95.12 for slave database server

back to top

3.3 Start building master-slave replication

Step 1:

Create a MySQL user in 192.168.95.11 who can log in to the 192.168.95.12 host

User: mysql12

Password: mysql12

mysql>GRANT REPLICATION SLAVE ON *.* TO 'mysql12'@'192.168.95.12' IDENTIFIED BY 'mysql12';

mysql>FLUSH PRIVILEGES;

Step 2:

View 192.168.95.11 MySQL Server Binary File Names and Locations

mysql>SHOW MASTER STATUS;

Step 3:

Tells binary file name and location

Executed 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 copy

In 192.168.95.12

mysql>SLAVE START; #Start replication

mysql>SHOW SLAVE STATUS\G #Check whether master-slave replication is configured successfully

Slave_IO_Running: YES, Slave_SQL_Running: YES

Actual testing:

--Login 192.168.95.11 Master MySQL

mysql>SHOW DATABASES;

--login 192.168.95.12 from MySQL

mysql>SHOW DATABASES;

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

192.168.95.11 Main MySQL operations:

mysql>create database aa;

mysql>use aa;

mysql>create table tab1(id int auto_increment,name varchar(10),primary key(id));

mysql>show databases;

mysql>show tables;

192.168.95.12 Operation from MySQL:

mysql>show databases;

mysql>show tables;

As can be seen from the above two results, the two hosts have achieved data synchronization. The configuration of master-slave replication is as simple as that.

back to top

4. MySql Master Master Copy

back to top

4.1, realization principle

Master replication means that data can be changed in both MySQL hosts, and the other host will also make corresponding changes. You may be smart enough to figure out how to do it. Right, that is, it is good to organically combine the two master-slave copies. However, we need to pay attention to some problems when configuring, such as duplicate primary keys, server-id cannot be duplicated, etc.

back to top

4.2 configuration file

--192.168.95.11

server-id=11 #Any natural number n, as long as the two MySQL hosts do not duplicate it.

log-bin=mysql-bin #Open binary log

auto_increment_increment=2 #step value auto_increment. There are usually n master MySQL, so fill in n

auto_increment_offset=1 #starting value. Generally fill in the nth main MySQL. This is the first MySQL.

binlog-ignore=mysql #ignore mysql library [I usually don't write]

binlog-ignore=information_schema #Ignore the information_schema library

replicate-do-db=aa #databases to synchronize, default all libraries

--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 master master replication

Because master-master replication is a combination of two master-slave replicas, I followed the master-slave replication above and then configured it.

Step 1:

Create a MySQL user in 192.168.95.12 who can log in to the 192.168.95.11 host

User: mysql11

Password: mysql11

mysql>GRANT REPLICATION SLAVE ON *.* TO 'mysql11'@'192.168.95.11' IDENTIFIED BY 'mysql11';

mysql>FLUSH PRIVILEGES;

Step 2:

View binary log names and locations at 192.168.95.12

mysql>show master status;

Step 3:

Tells binary file name and location

Executed 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;

Complete the master replication configuration

back to top

4.4 Test Master Master Copy

Open slave start separately;

mysql>SHOW SLAVE STATUS\G #Check whether master-slave replication is configured successfully

192.168.95.11

192.168.95.12

Slave_IO_Running: YES, Slave_SQL_Running: YES

Testing:

--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 Insert data

mysql>insert into tab1 (name) value('11'),('11'),('11');

--192.168.95.12 Insert data

mysql>insert into tab1 (name) value('22'),('22'),('22');

View data:

The data from both hosts is the same!

Master copy configuration successful!

back to top

5. Precautions

1. Auto_increment_increment and auto_increment_offset in the master replication configuration file can only ensure that the primary keys are not duplicated, but cannot ensure that the primary keys are in order.

2. When Slave_IO_Running and Slave_SQL_Running are not all YES, there is an error prompt in the show slave status\G message, which can be corrected according to the error prompt.

When Slave_IO_Running and Slave_SQL_Running are not all YES, most problems are caused by inconsistent data.

Common mistakes:

DB database exists in both databases, but tab1 exists in the first MySQL DB and tab1 does not exist in the second MySQL DB.

2. The binary log name and location of the data have been obtained, and data operations have been performed, resulting in changes in POS. Change MASTER is configured using the previous POS.

After stopping slave, the data changes and then starts slave. Error.

The ultimate correction method: re-execute the CHANGE MASTER.

(The above are some of my own opinions. If there are deficiencies or mistakes, please point out them.)

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.