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

            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.11’ 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.cnblogs.com/phpstudy2015-6/p/6485819.html#_label2

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏散尽浮华

分布式监控系统Zabbix-3.0.3-完整安装记录(1)

分布式监控系统Zabbix-3.0.3的安装记录 环境说明 zabbix-server:192.168.1.30     #zabbix的服务端(若要监控本机,...

25010
来自专栏数据和云

深入原理:Consistent Reads 与 buffer cache

黄玮(Fuyuncat) 资深Oracle DBA,个人网www.HelloDBA.com,致力于数据库底层技术的研究,其作品获得广大同行的高度评价. 编辑手记...

3657
来自专栏同步博客

MySQL主从复制与主主复制

      MySQL作为世界上使用最为广泛的数据库之一,免费是其原因之一。但不可忽略的是它本身的功能的确很强大。随着技术的发展,在实际的生产环境中,由单台My...

952
来自专栏运维小白

19.5 忘记Admin密码如何做

忘记Admin密码如何做 进入mysql命令行,选择zabbix库 mysql -uroot -p zabbix update users set passwd...

1928
来自专栏杨建荣的学习笔记

MySQL断电恢复的一点简单分析

今天有个网友问我一个MySQL的恢复问题。提供的截图如下。 ? 对于这个问题,在一些断电的场景下还是可能出现的。我首先是要确认是否为线上业务还是测试环境...

49710
来自专栏文渊之博

孤立的SQL用户

问题 最近公司很多数据库在上云,也有一部分在下云。这期间出现了很多问题,其中一个比较恶心的问题就是“孤立用户”。当数据库备份还原以后用以前的用户发现不能登录。一...

3407
来自专栏企鹅号快讯

Access+Mssql+Mysql 手工注入基本流程

0x01 Access 注入 ? 判断表名:adminand (select count(*) from admin)>0判断字段名:username,pass...

2185
来自专栏耕耘实录

CentOS7.2下安装MySQL-5.7.18RPM Bundle版(适用于最新版5.7.20)

版权声明:本文为耕耘实录原创文章,各大自媒体平台同步更新。欢迎转载,转载请注明出处,谢谢。

1094
来自专栏张戈的专栏

MySQL命令行工具:percona-toolkit安装使用初探

导读:percona-toolkit 源自 Maatkit 和 Aspersa 工具,这两个工具是管理 mysql 的最有名的工具,现在 Maatkit 工具已...

3937
来自专栏乐沙弥的世界

使用mysqldump导出数据库

    mysqldump是mysql用于转存储数据库的客户端程序。它主要产生一系列的SQL语句,可以封装到文件,该文件包含有所有重建您的数据库所需要的SQL命...

1842

扫码关注云+社区

领取腾讯云代金券