MySQL复制

  1. 云栖社区>
  2. 博客>
  3. 正文

MySQL复制

技术小阿哥 2017-11-20 12:42:00 浏览1248
展开阅读全文

复制解决的问题

概述:你的网站访问量非常大,对系统的稳定性非常高,那么可以使用mysql功能的复制功能,复制是指将主要的数据库的DDL和DML操作通过二进制日志传到复制服务器(也叫从服务器)上然后在从服务器上对这些日志重新执行(也叫重做),从而使得从服务器和主服务器的数据保持同步,mysql复制的优点包括以下三个方面

1. 如果主服务器出现问题,可以快速切换到从服务器提供服务

2. 可以在从服务器上执行查询操作,降低主服务器的访问压力

3. 可以在从服务器上执行备份,以避免备份期间影响主服务器的服务

缺点:由于MySQLl实现的是异步的复制,所以在主从服务器之间存在一定的差距,所以对实时性要求高的需要从主服务器数据库获得,对一般只有更新不频繁的数据或对实时性要求不高的数据可以通过从服务器查询,解决这个办法的方法可以使用

复制模式

MySQL复制是异步的,它将数据从一个主机复制到一个或多个MySQL从机。另外,MySQL复制支持多种日志类型,针对各种不同类型的数据库更新来优化数据库性能。

1. 基于行的复制 - 复制单独的行,而不是SQL语句,这种方式最适合更新只影响少数行的情况。这种情况使用的锁较少,使数据库的并发性更高。

2. 基于语句复制 - 复制SQL语句,而不是实际的数据被更新。 适用于影响多行的更新操作,基于语句的复制导致较少的数据被记录在日志中。

3. 混合模式复制 - MySQL可以根据事件动态改变上面讨论的复制模式。

练习1:配置mySQL复制

本练习将会克隆出两个mySQL服务器SQL1 223和mySQL2 224。然后配置这两个mySQL实现所有数据库(除了mysql数据库和information_schame数据库)复制。SQL1的数据会自动复制到mySQL2服务器。

1. 准备实验环境

从现有mySQL克隆mySQL从。关闭现有安装了mySQL的Ubuntu,更改显示名,做快照。

clip_image002 clip_image003

创建快照

clip_image004

clip_image005

点中mySQL克隆模板,点击“Clone”

clip_image006

clip_image007

clip_image008

指定虚拟机存放位置

clip_image009

点击“确定”。可以看到克隆的虚拟机mySQL2 224

clip_image010

启动mySQL2 224,更改服务器名称和IP地址为mySQL2 和192.168.80.224,重启mySQL2后做快照。

clip_image011

2. 使用putty连接主mySQL

如果连接不上,需要关闭Ubuntu的防火墙,运行ufw disable

clip_image012

3. 在主服务器上创建复制账户

在主服务器上创建复制帐号,授予相应的权限。注意,复制帐号的口令最好不要超过6位,而且不要带"#"等特殊字符。

mysql> grant replication slave on *.* to 'repl'@'192.168.80.%' identified by 'a1!';

Query OK, 0 rows affected (0.00 sec)

mysql>exit

clip_image013

4. 更改mySQL1服务器mySQL配置文件

server-id = 1

log-bin=mysql-bin

binlog-ignore-db=mysql

binlog-ignore-db=information_schema

clip_image014

各个配置选项的说明如下:

server-id = 1

服务器ID号,整数值,保证唯一标识一台服务器就可以

log-bin=mysql-bin

打开二进制日志

binlog-ignore-db=mysql

忽略mysql数据库复制

binlog-ignore-db=information_schema

忽略information_schema数据库复制

5. 在mySQL2配置如下

server-id =2

master-host=192.168.80.223

master-user= repl

master-password=a1!

relay-log=relay-bin

relay-log-index=relay-bin

replicate-ignore-db=mysql

replicate-ignore-db=information_schema

clip_image015

6. 在SQL1上备份所有数据库

在查看SQL1上的现有数据库,查看现有数据库。记下这些数据库。

clip_image016

备份所有数据库

root@mySQL1:~# mysqldump -u root -p --all-database > all.sql

Enter password:

root@mySQL1:~# ls

clip_image017

删除SQL1上用户数据库

clip_image018

7. 删除SQL2上所有用户数据库

clip_image019

8. 先重启SQL1上的mysql服务,再重启SQL2上的mysql服务

重启完后SQL1和SQL2就有复制关系了。

clip_image020

clip_image021

9. 从SQL1还原备份的数据库

注意;现在在SQL1上的所有操作都会在SQL2上执行一遍。

在SQL1上还原数据库,在SQL2上也会创建还原的数据库。

root@mySQL1:~# mysql -u root -p < all.sql

Enter password:

clip_image022

查看SQL1上还原的数据库。

clip_image023

10. 在SQL2上查看数据库

可以看到两个数据库students和testDB已经出现在SQL2

clip_image024

练习2:查看复制状态

使用mySQL manager工具连接到SQL2状态

show slave status

clip_image025

clip_image026

Show slave status

clip_image027

可以看到配置的主mySQL是哪个服务器,复制账号,只要Slave_IO_Running和Slave_SQL_Running是yes说明配置成功。

Slave_IO_running负责从主服务器上读取BINLOG日志并写入从服务器的中继日志中。

Slave_SQL_running此进程负责读取并且执行中继日志中的BINLOG日志。

clip_image028

可以看到不参与复制的数据库“mysql”和“information_schema”。

clip_image029

练习3:验证mySQL复制

主mySQL的数据变化会自动复制到从mySQL,从mySQL的数据库变化不会复制到主mySQL。下面就来验证。

1. 在SQL1上创建新的数据库和表 插入数据

clip_image030

2. 在SQL2上查看数据库是否复制过来

查看从SQL1 中刚刚复制过来的schoolDB2数据库。

clip_image031

再查看schoolDB2数据库中的表,可以看到数据也复制过来了。

clip_image032

练习4:验证单向复制

3. 在SQL2上,插入一条记录。

clip_image033

在SQL1上查看,可以看到,SQL2上的记录不会复制到SQL1.

clip_image034

在SQL1上插入一条记录。

clip_image035

在SQL1上的students数据库的s表插入一条记录。

clip_image036

在SQL2上查看,可以看到schoolDB2数据库t1表中的记录复制过来了

clip_image037

查看数据库students中s表中记录,发现也复制过来。

clip_image038

结论:mySQL复制主数据库只能向从数据库复制,并且可同时对多个数据库进行数据同步。
练习5:禁用特定数据库复制

本练习禁止students复制到SQL2。

4. 在SQL1中更改配置文件,增加

binlog-ignore-db=students

##指定不写binlog的数据库,不同步系统库

binlog-ignore-db = mysql

binlog-ignore-db = information_schema

##指定写二进制的数据库,多个要多行

replicate-do-db = DB_Test

##指定跨库更新也同步的数据库。和replicate-do-db相同多行

replicate-wild-do-table=DB\_Test.%

备注::关于过滤复制对象的问题,但是如果不是特殊需要,不要使用复制过滤,因为同样有几率在一些事件触发的时候导致一些错误。介绍几种过滤参数。

binlog_do_db 只复制指定的数据库(主服务器配置文件里设置)

binlog_ignore_db 不复制指定的数据库(主服务器配置文件里设置)

replicate_ignore_table 不复制指定的表(从服务器配置文件里设置)

replicate_wild_ignore_table

使用wild匹配来不复制的指定表(从服务器配置文件里设置),比如参数设为abc.%,表示不复制abc的所有表。

clip_image039

5. 重启SQL1的mysql服务

clip_image040

6. 在SQL1上student2数据库的s表插入一条记录

clip_image041

7. 在SQL2上验证,可以看到students数据库的s表中新增加的数据没有复制过来。

clip_image042

练习6:暂停从mySQL从主mySQL复制

本练习将会配置从mySQL暂时停止从主mySQL复制。

8. 你只需要在从mySQL运行

Stop slave

clip_image043

9. 在主mySQL插入记录

clip_image044

10. 在从mySQL查看,发现数据没有复制过来

clip_image045

11. 在从mySQL上运行slave服务,可以看到数据复制过来

clip_image046

 



本文转自 onesthan 51CTO博客,原文链接:http://blog.51cto.com/91xueit/1138103,如需转载请自行联系原作者

网友评论

登录后评论
0/500
评论
技术小阿哥
+ 关注