MySQL主从复制与主主复制

共计 4245 个字符，预计需要花费 11 分钟才能阅读完成。

1、简介

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

2、环境说明

     两台 Linux 虚拟主机

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

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

            mysql>FLUSH PRIVILEGES;

            第二步：

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

            mysql>SHOW MASTER STATUS;

            第三步：

            告知二进制文件名与位置

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

            完成主从复制配置

      3.4、测试主从复制

            在 192.168.95.12 中

            mysql>SLAVE START;  #开启复制

            mysql>SHOW SLAVE STATUS\G  #查看主从复制是否配置成功

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

            实际测试：

            – 登陆 192.168.95.11 主 MySQL

            mysql>SHOW DATABASES;

            – 登陆 192.168.95.12 从 MySQL

            mysql>SHOW DATABASES;

            —————————————————–

            192.168.95.11 主 MySQL 操作：

            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 从 MySQL 操作：

            mysql>show databases;

            mysql>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

           mysql>GRANT REPLICATION SLAVE ON *.* TO‘mysql11’@’192.168.95.12’IDENTIFIED BY‘mysql11’;

           mysql>FLUSH PRIVILEGES;

           第二步：

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

           mysql>show master status;

           第三步：

           告知二进制文件名与位置

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

           完成主主复制配置

    4.4、测试主主复制

           分别开启 slave start；

           mysql>SHOW SLAVE STATUS\G  #查看主从复制是否配置成功

           192.168.95.11

           192.168.95.12

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

           测试：

           –192.168.95.11

           mysql>use aa;

           mysql>select*from tab1;

           tab1 无数据

           –192.168.95.12

           mysql>use aa;

           mysql>select*from tab1;

           tab1 无数据

           –192.168.95.11 插入数据

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

           –192.168.95.12 插入数据

           mysql>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 status\G 信息中有错误提示，可根据错误提示进行更正。

     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 就好了。

本文永久更新链接地址 ：http://www.linuxidc.com/Linux/2017-04/142624.htm