MySQL主从复制与主主复制
WilMarshbur
7年前
<h3>1、简介</h3> <p>MySQL作为世界上使用最为广泛的数据库之一，免费是其原因之一。但不可忽略的是它本身的功能的确很强大。随着技术的发展，在实际的生产环境中，由单台MySQL数据库服务器不能满足实际的需求。此时数据库集群就很好的解决了这个问题了。采用MySQL分布式集群，能够搭建一个高并发、负载均衡的集群服务器（这篇博客暂时不涉及）。在此之前我们必须要保证每台MySQL服务器里的数据同步。数据同步我们可以通过MySQL内部配置就可以轻松完成，主要有主从复制和主主复制。</p> <h3>2、环境说明</h3> <p>两台linux虚拟主机</p> <p>Linux版本CentOS6.6、MySQL 5.5</p> <p>ip：192.168.95.11、192.168.95.12</p> <h3>3、主从复制</h3> <p>3.1、MySQL</p> <p>已经安装好，并且没有任何数据</p> <p>3.2、配置文件</p> <p>一般Linux中的MySQL配置文件都在/etc/my.cnf(windows中的配置文件为mysql.ini)</p> <p>log-bin=mysql-bin 开启二进制日志</p> <p>注意：二进制日志必须开启，因为数据的同步实质上就是其他的MySQL数据库服务器将这个数据变更的二进制日志在本机上再执行一遍。</p> <p>192.168.95.11 为主数据库服务器</p> <p>192.168.95.12 为从数据库服务器</p> <p>3.3、开始构建主从复制</p> <p>第一步：</p> <p>在192.168.95.11中创建一个192.168.95.12主机中可以登录的MySQL用户</p> <p>用户：mysql12</p> <p>密码：mysql12</p> <p>mysql>GRANT REPLICATION SLAVE ON *.* TO ‘mysql12’@’192.168.95.12’ IDENTIFIED BY ‘mysql12’;</p> <p>mysql>FLUSH PRIVILEGES;</p> <p>第二步：</p> <p>查看192.168.95.11MySQL服务器二进制文件名与位置</p> <p>mysql>SHOW MASTER STATUS;</p> <p style="text-align:center"><img src="https://simg.open-open.com/show/325feb5bc63a3ca4262155d26734df9a.jpg"></p> <p>第三步：</p> <p>告知二进制文件名与位置</p> <p>在192.168.95.12中执行：</p> <p>mysql>CHANGE MASTER TO</p> <p>>MASTER_HOST=’192.168.95.11’,</p> <p>>MASTER_USER=’mysql12’,</p> <p>>MASTER_PASSWORD=’mysql12’,</p> <p>>MASTER_LOG_FILE=’mysql-bin.000048’,</p> <p>>MASTER_LOG_POS=432;</p> <p style="text-align:center"><img src="https://simg.open-open.com/show/de0cfbda2ca7f78fe654a80e94c34ee2.jpg"></p> <p>完成主从复制配置</p> <p>3.4、测试主从复制</p> <p>在192.168.95.12中</p> <p>mysql>SLAVE START; #开启复制</p> <p>mysql>SHOW SLAVE STATUS\G #查看主从复制是否配置成功</p> <p style="text-align:center"><img src="https://simg.open-open.com/show/90a70bc35ed59df9e2db1680aa07de20.jpg"></p> <p>当看到Slave_IO_Running: YES、Slave_SQL_Running: YES才表明状态正常</p> <p>实际测试：</p> <p>--登陆192.168.95.11主MySQL</p> <p>mysql>SHOW DATABASES;</p> <p style="text-align:center"><img src="https://simg.open-open.com/show/9e29a7b805e530c3823cbfcd897dd294.jpg"></p> <p>--登陆192.168.95.12从MySQL</p> <p>mysql>SHOW DATABASES;</p> <p style="text-align:center"><img src="https://simg.open-open.com/show/9e29a7b805e530c3823cbfcd897dd294.jpg"></p> <p>-----------------------------------------------------</p> <p>192.168.95.11主MySQL操作：</p> <p>mysql>create database aa;</p> <p>mysql>use aa;</p> <p>mysql>create table tab1(id int auto_increment,name varchar(10),primary key(id));</p> <p>mysql>show databases;</p> <p>mysql>show tables;</p> <p style="text-align:center"><img src="https://simg.open-open.com/show/dd134de91e17ea2490b6f6d6525f5194.jpg"></p> <p>192.168.95.12从MySQL操作：</p> <p>mysql>show databases;</p> <p>mysql>show tables;</p> <p style="text-align:center"><img src="https://simg.open-open.com/show/145374b3f8f033b5d15bb10900bee34b.jpg"></p> <p>由上面两个结果图可得知，两主机达到了数据同步。主从复制的配置就是如此的简单。</p> <h3>4、MySql主主复制</h3> <p>4.1、实现原理</p> <p>主主复制即在两台MySQL主机内都可以变更数据，而且另外一台主机也会做出相应的变更。聪明的你也许已经想到该怎么实现了。对，就是将两个主从复制有机合并起来就好了。只不过在配置的时候我们需要注意一些问题，例如，主键重复，server-id不能重复等等。</p> <p>4.2、配置文件</p> <p>--192.168.95.11</p> <p>server-id=11 #任意自然数n，只要保证两台MySQL主机不重复就可以了。</p> <p>log-bin=mysql-bin #开启二进制日志</p> <p>auto_increment_increment=2 #步进值auto_imcrement。一般有n台主MySQL就填n</p> <p>auto_increment_offset=1 #起始值。一般填第n台主MySQL。此时为第一台主MySQL</p> <p>binlog-ignore=mysql #忽略mysql库【我一般都不写】</p> <p>binlog-ignore=information_schema #忽略information_schema库【我一般都不写】</p> <p>replicate-do-db=aa #要同步的数据库，默认所有库</p> <p>--192.168.95.12</p> <p>server-id=12</p> <p>log-bin=mysql-bin</p> <p>auto_increment_increment=2</p> <p>auto_increment_offset=2</p> <p>replicate-do-db=aa</p> <p>配置好后重启MySQL</p> <p>4.3、开始构建主主复制</p> <p>因为主主复制是两个主从复制组合一起，所以我就接着上面主从复制接着配置。</p> <p>第一步：</p> <p>在192.168.95.12中创建一个192.168.95.11主机中可以登录的MySQL用户</p> <p>用户：mysql11</p> <p>密码：mysql11</p> <p>mysql>GRANT REPLICATION SLAVE ON *.* TO ‘mysql11’@’192.168.95.12’ IDENTIFIED BY ‘mysql11’;</p> <p>mysql>FLUSH PRIVILEGES;</p> <p>第二步：</p> <p>在192.168.95.12查看二进制日志名和位置</p> <p>mysql>show master status;</p> <p style="text-align:center"><img src="https://simg.open-open.com/show/aab4722c7a744c7afebbd458a07737f8.jpg"></p> <p>第三步：</p> <p>告知二进制文件名与位置</p> <p>在192.168.95.11中执行：</p> <p>mysql>CHANGE MASTER TO</p> <p>MASTER_HOST=’192.168.95.12’,</p> <p>MASTER_USER=’mysql11’,</p> <p>MASTER_PASSWORD=’mysql11’,</p> <p>MASTER_LOG_FILE=’mysql-bin.000084’,</p> <p>MASTER_LOG_POS=107;</p> <p style="text-align:center"><img src="https://simg.open-open.com/show/86e219e5d3e1eb2386e742eee80b11dd.jpg"></p> <p>完成主主复制配置</p> <p>4.4、测试主主复制</p> <p>分别开启slave start；</p> <p>mysql>SHOW SLAVE STATUS\G #查看主从复制是否配置成功</p> <p>192.168.95.11</p> <p style="text-align:center"><img src="https://simg.open-open.com/show/b5fa6190255db509f44dad2cff2ea5c1.jpg"></p> <p>192.168.95.12</p> <p style="text-align:center"><img src="https://simg.open-open.com/show/8cb4f8e2a46c4a71291744892165b2d1.jpg"></p> <p>当看到Slave_IO_Running: YES、Slave_SQL_Running: YES才表明状态正常</p> <p>测试：</p> <p>--192.168.95.11</p> <p>mysql>use aa;</p> <p>mysql>select*from tab1;</p> <p>tab1无数据</p> <p>--192.168.95.12</p> <p>mysql>use aa;</p> <p>mysql>select*from tab1;</p> <p>tab1无数据</p> <p>--192.168.95.11插入数据</p> <p>mysql>insert into tab1 (name) value(‘11’),(‘11’),(‘11’);</p> <p>--192.168.95.12插入数据</p> <p>mysql>insert into tab1 (name) value(‘22’),(‘22’),(‘22’);</p> <p>查看数据：</p> <p>两个主机数据结果一样！</p> <p style="text-align:center"><img src="https://simg.open-open.com/show/e5a635b0bbf87cd5c5b5b293f785da6c.jpg"></p> <p>主主复制配置成功！</p> <h3>5、注意事项</h3> <p>1、主主复制配置文件中auto_increment_increment和auto_increment_offset只能保证主键不重复，却不能保证主键有序。</p> <p>2、当配置完成Slave_IO_Running、Slave_SQL_Running不全为YES时，show slave status\G信息中有错误提示，可根据错误提示进行更正。</p> <p>3、Slave_IO_Running、Slave_SQL_Running不全为YES时，大多数问题都是数据不统一导致。</p> <p>常见出错点：</p> <p>1、两台数据库都存在db数据库，而第一台MySQL db中有tab1，第二台MySQL db中没有tab1，那肯定不能成功。</p> <p>2、已经获取了数据的二进制日志名和位置，又进行了数据操作，导致POS发生变更。在配置CHANGE MASTER时还是用到之前的POS。</p> <p>3、stop slave后，数据变更，再start slave。出错。</p> <p>终极更正法：重新执行一遍CHANGE MASTER就好了。</p> <p>（以上是自己的一些见解，若有不足或者错误的地方请各位指出）</p> <p> </p> <p> </p> <p>来自：http://www.cnblogs.com/phpstudy2015-6/p/6485819.html</p> <p> </p>