MySQL主从复制与主主复制

1、简介

      MySQL作为世界上使用最为广泛的数据库之一，免费是其原因之一。但不可忽略的是它本身的功能的确很强大。随着技术的发展，在实际的生产环境中，由单台MySQL数据库服务器不能满足实际的需求。此时数据库集群就很好的解决了这个问题了。采用MySQL分布式集群，能够搭建一个高并发、负载均衡的集群服务器（这篇博客暂时不涉及）。在此之前我们必须要保证每台MySQL服务器里的数据同步。数据同步我们可以通过MySQL内部配置就可以轻松完成，主要有主从复制和主主复制。

回到顶部

2、环境说明

     两台linux虚拟主机

     Linux版本CentOS6.6、MySQL 5.5

     ip：192.168.95.11、192.168.95.12

回到顶部

3、主从复制

回到顶部

     3.1、MySQL

            已经安装好，并且没有任何数据

回到顶部

     3.2、配置文件

            一般Linux中的MySQL配置文件都在/etc/my.cnf(windows中的配置文件为mysql.ini)

            log-bin=mysql-bin 开启二进制日志

           注意：二进制日志必须开启，因为数据的同步实质上就是其他的MySQL数据库服务器将这个数据变更的二进制日志在本机上再执行一遍。

           192.168.95.11  为主数据库服务器

           192.168.95.12  为从数据库服务器

回到顶部

    3.3、开始构建主从复制

            第一步：

            在192.168.95.11中创建一个192.168.95.12主机中可以登录的MySQL用户

            用户：mysql12

            密码：mysql12

            mysqlgt;GRANT REPLICATION SLAVE ON *.* TO ‘mysql12’@’192.168.95.12’ IDENTIFIED BY ‘mysql12’;

            mysqlgt;FLUSH PRIVILEGES;

            第二步：

            查看192.168.95.11MySQL服务器二进制文件名与位置

            mysqlgt;SHOW MASTER STATUS;

            第三步：

            告知二进制文件名与位置

            在192.168.95.12中执行：

            mysqlgt;CHANGE MASTER TO

                     gt;MASTER_HOST=’192.168.95.11’,

                     gt;MASTER_USER=’mysql12’,

                     gt;MASTER_PASSWORD=’mysql12’,

                     gt;MASTER_LOG_FILE=’mysql-bin.000048’,

                     gt;MASTER_LOG_POS=432;

            完成主从复制配置

回到顶部

      3.4、测试主从复制

            在192.168.95.12中

            mysqlgt;SLAVE START;   #开启复制

            mysqlgt;SHOW SLAVE STATUSG   #查看主从复制是否配置成功

            当看到Slave_IO_Running: YES、Slave_SQL_Running: YES才表明状态正常

            实际测试：

            --登陆192.168.95.11主MySQL

            mysqlgt;SHOW DATABASES;

            --登陆192.168.95.12从MySQL

            mysqlgt;SHOW DATABASES;

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

            192.168.95.11主MySQL操作：

            mysqlgt;create database aa;

            mysqlgt;use aa;

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

            mysqlgt;show databases;

            mysqlgt;show tables;

            192.168.95.12从MySQL操作：

            mysqlgt;show databases;

            mysqlgt;show tables;

            由上面两个结果图可得知，两主机达到了数据同步。主从复制的配置就是如此的简单。

回到顶部

4、MySql主主复制

回到顶部

     4.1、实现原理

            主主复制即在两台MySQL主机内都可以变更数据，而且另外一台主机也会做出相应的变更。聪明的你也许已经想到该怎么实现了。对，就是将两个主从复制有机合并起来就好了。只不过在配置的时候我们需要注意一些问题，例如，主键重复，server-id不能重复等等。

回到顶部

     4.2、配置文件

            --192.168.95.11

            server-id=11   #任意自然数n，只要保证两台MySQL主机不重复就可以了。

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

           auto_increment_increment=2   #步进值auto_imcrement。一般有n台主MySQL就填n

           auto_increment_offset=1   #起始值。一般填第n台主MySQL。此时为第一台主MySQL

           binlog-ignore=mysql   #忽略mysql库【我一般都不写】

           binlog-ignore=information_schema   #忽略information_schema库【我一般都不写】

           replicate-do-db=aa   #要同步的数据库，默认所有库

           --192.168.95.12

           server-id=12

           log-bin=mysql-bin

           auto_increment_increment=2

           auto_increment_offset=2

           replicate-do-db=aa

           配置好后重启MySQL

回到顶部

    4.3、开始构建主主复制

           因为主主复制是两个主从复制组合一起，所以我就接着上面主从复制接着配置。

           第一步：

           在192.168.95.12中创建一个192.168.95.11主机中可以登录的MySQL用户

           用户：mysql11

           密码：mysql11

           mysqlgt;GRANT REPLICATION SLAVE ON *.* TO ‘mysql11’@’192.168.95.11’ IDENTIFIED BY ‘mysql11’;

           mysqlgt;FLUSH PRIVILEGES;

           第二步：

           在192.168.95.12查看二进制日志名和位置

           mysqlgt;show master status;

           第三步：

           告知二进制文件名与位置

           在192.168.95.11中执行：

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

           完成主主复制配置

回到顶部

    4.4、测试主主复制

           分别开启slave start；

           mysqlgt;SHOW SLAVE STATUSG   #查看主从复制是否配置成功

           192.168.95.11

           192.168.95.12

           当看到Slave_IO_Running: YES、Slave_SQL_Running: YES才表明状态正常

           测试：

           --192.168.95.11

           mysqlgt;use aa;

           mysqlgt;select*from tab1;

           tab1无数据

           --192.168.95.12

           mysqlgt;use aa;

           mysqlgt;select*from tab1;

           tab1无数据

           --192.168.95.11插入数据

           mysqlgt;insert into tab1 (name) value(‘11’),(‘11’),(‘11’);

           --192.168.95.12插入数据

           mysqlgt;insert into tab1 (name) value(‘22’),(‘22’),(‘22’);

           查看数据：

           两个主机数据结果一样！

           主主复制配置成功！

回到顶部

5、注意事项

     1、主主复制配置文件中auto_increment_increment和auto_increment_offset只能保证主键不重复，却不能保证主键有序。

     2、当配置完成Slave_IO_Running、Slave_SQL_Running不全为YES时，show slave statusG信息中有错误提示，可根据错误提示进行更正。

     3、Slave_IO_Running、Slave_SQL_Running不全为YES时，大多数问题都是数据不统一导致。

     常见出错点：

     1、两台数据库都存在db数据库，而第一台MySQL db中有tab1，第二台MySQL db中没有tab1，那肯定不能成功。

     2、已经获取了数据的二进制日志名和位置，又进行了数据操作，导致POS发生变更。在配置CHANGE MASTER时还是用到之前的POS。

     3、stop slave后，数据变更，再start slave。出错。

     终极更正法：重新执行一遍CHANGE MASTER就好了。

（以上是自己的一些见解，若有不足或者错误的地方请各位指出）

1, introduction

As one of the most widely used databases in the world, MySQL is free. But it can't be ignored that its function is very powerful. With the development of technology, in the actual production environment, a single MySQL database server can not meet the actual needs. At this time, the database cluster solves this problem very well. With MySQL distributed cluster, you can build a cluster server with high concurrency and load balance (not involved in this blog for the time being). Before that, we must ensure the data synchronization in each MySQL server. Data synchronization can be easily completed through MySQL internal configuration, mainly including master-slave replication and master-slave replication.

Back to the top

2. Environmental description

Two Linux virtual hosts

Linux version CentOS 6.6, MySQL 5.5

     ip：192.168.95.11、192.168.95.12

Back to the top

3. Master-slave replication

Back to the top

     3.1、MySQL

It's installed and there's no data

Back to the top

3.2 configuration file

In general, MySQL configuration files in Linux are in / etc / my.cnf (mysql.ini in Windows)

Log bin = MySQL bin enable binary log

Note: binary log must be enabled, because data synchronization is essentially another MySQL database server that executes the binary log of data change on the local machine again.

192.168.95.11 main database server

192.168.95.12 is the slave database server

Back to the top

3.3 start to build 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

            mysqlgt;GRANT REPLICATION SLAVE ON *.* TO ‘mysql12’@’192.168.95.12’ IDENTIFIED BY ‘mysql12’;

            mysqlgt;FLUSH PRIVILEGES;

Step 2:

View the binary file name and location of 192.168.95.11 MySQL server

            mysqlgt;SHOW MASTER STATUS;

Step 3:

Tell binary name and location

In 192.168.95.12:

            mysqlgt;CHANGE MASTER TO

                     gt;MASTER_HOST=’192.168.95.11’,

                     gt;MASTER_USER=’mysql12’,

                     gt;MASTER_PASSWORD=’mysql12’,

                     gt;MASTER_LOG_FILE=’mysql-bin.000048’,

                     gt;MASTER_LOG_POS=432;

Complete master-slave replication configuration

Back to the top

3.4 test master-slave replication

In 192.168.95.12

MySQL gt; slave start; ා enable replication

MySQL gt; show slave status g ා check whether the master-slave replication is configured successfully

When you see slave IO running: Yes, slave SQL running: Yes, the status is normal

Actual test:

-- log in to 192.168.95.11 main MySQL

            mysqlgt;SHOW DATABASES;

-- log in to 192.168.95.12 and log in from mysql

            mysqlgt;SHOW DATABASES;

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

192.168.95.11 main MySQL operation:

            mysqlgt;create database aa;

            mysqlgt;use aa;

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

            mysqlgt;show databases;

            mysqlgt;show tables;

192.168.95.12 operation from MySQL:

            mysqlgt;show databases;

            mysqlgt;show tables;

From the above two results, we can see that the two hosts have achieved data synchronization. The configuration of master-slave replication is so simple.

Back to the top

4. MySQL primary primary replication

Back to the top

4.1 implementation principle

The primary and primary replication can change data in both MySQL hosts, and the other host will make corresponding changes. Smart you may have thought about how to achieve it. Yes, just combine the two master-slave replication. However, we need to pay attention to some problems during configuration, such as duplicate primary key, server ID, etc.

Back to the top

4.2 configuration file

            --192.168.95.11

Server id = 11 ා any natural number n, as long as the two MySQL hosts are not repeated.

Log bin = MySQL Bin - enable binary log

Auto increment = 2 step value auto increment. Generally, if there are n main mysql, fill in n

Auto increment offset = 1 initial value. Generally fill in the nth main mysql. This is the first main MySQL

Binlog ignore = MySQL ා ignore MySQL database [I don't usually write it]

Binlog ignore = information ﹐ schema ﹐ ignore the information ﹐ schema library [I don't usually write it]

Replicate do DB = AA ා database to be synchronized, all databases 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 the top

4.3. Start to build primary primary replication

Since the primary and secondary replication is a combination of two primary and secondary replication, I will continue the above primary and secondary replication and then configure.

Step 1:

In 192.168.95.12, create a MySQL user that can log in to the 192.168.95.11 host

User: mysql11

Password: mysql11

           mysqlgt;GRANT REPLICATION SLAVE ON *.* TO ‘mysql11’@’192.168.95.11’ IDENTIFIED BY ‘mysql11’;

           mysqlgt;FLUSH PRIVILEGES;

Step 2:

Check binary log name and location at 192.168.95.12

           mysqlgt;show master status;

Step 3:

Tell binary name and location

In 192.168.95.11:

           mysqlgt;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 primary primary replication configuration

Back to the top

4.4 test primary primary replication

Start slave start respectively;

MySQL gt; show slave status g ා check whether the master-slave replication is configured successfully

           192.168.95.11

           192.168.95.12

When you see slave IO running: Yes, slave SQL running: Yes, the status is normal

Test:

           --192.168.95.11

           mysqlgt;use aa;

           mysqlgt;select*from tab1;

Tab1 no data

           --192.168.95.12

           mysqlgt;use aa;

           mysqlgt;select*from tab1;

Tab1 no data

-- 192.168.95.11 insert data

           mysqlgt;insert into tab1 (name) value(‘11’),(‘11’),(‘11’);

-- 192.168.95.12 insert data

           mysqlgt;insert into tab1 (name) value(‘22’),(‘22’),(‘22’);

View data:

The data results of the two hosts are the same!

Primary primary replication configuration succeeded!

Back to the top

5. Precautions

1. Auto increment and auto increment offset in the primary and primary replication configuration file can only ensure that the primary key is not duplicate, but not orderly.

2. When the 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.

3. When slave IO running and slave SQL running are not all yes, most problems are caused by data inconsistency.

Common mistakes:

1. Both databases have DB databases, but the first MySQL DB has tab1, and the second MySQL DB does not have tab1, which is certainly not successful.

2. The binary log name and location of the data have been obtained, and the data operation has been carried out, resulting in the change of POS. The previous POS is still used when configuring change master.

3. After stop slave, the data changes, and then start slave. Mistakes.

The ultimate correction: just execute change master again.

(the above are some of my own opinions. If there are any shortcomings or mistakes, please point out.)

--------------------------------------------------------------
主机参考，收集国内外VPS，VPS测评，主机测评，云服务器，虚拟主机，独立服务器，国内外服务器，高性价比建站主机相关优惠信息@zhujicankao.com
详细介绍和测评国外VPS主机,云服务器,国外服务器,国外主机的相关优惠信息,商家背景,网络带宽等等,也是目前国内最好的主机云服务器VPS参考测评资讯优惠信息分享平台

这几篇文章你可能也喜欢：

• 搬瓦工VPS，限量款套餐补货，美国CN2 GIA/日本软银/荷兰AS9929，1Gbps超大高端网络线路，KVM虚拟架构，1核1G内存1Gbps带宽，80美元/年，机房可随意切换
• CloudCone，美国Win系统VPS云服务器少量补货，美国洛杉矶MC机房，KVM虚拟架构，3核4G内存1Gbps带宽，17.49美元/月
• LOCVPS，双12特惠，香港免备案VPS云服务器终身65折，香港葵湾机房，CN2+BGP/CN2网络，KVM虚拟架构，1核2G内存2Mbps带宽不限流量，29.25元/月
• 恒创科技，全场3折优惠起，免备案香港VPS云服务器低至28元/月，香港/美国，新增多款特价服务器，1核1G内存5Mbps带宽不限流量，292元/年
• 萤光云，香港VPS云服务器测评报告，回国优化线路，香港VPS测评，萤光云VPS好不好？萤光云香港VPS云服务器值得购买吗？

本文由主机参考刊发，转载请注明：MySQL主从复制与主主复制 https://zhujicankao.com/12763.html