mysql主从数据库实现_MySQL主从数据库原理及实现

MySQL主从数据库原理及实现

MySQL的主从同步架构是当前比较流行的一种数据库架构，利用MySQL的主从配置，可以实现读写分离，减轻主数据库的访问压力，提升网站性能。

主从原理：

1. Master记录二进制日志，在每个事务更新数据完成之前，master在二日志记录这些改变。MySQL将事务串行的写入二进制日志，即使事务中的语句都是交叉执行的。在事件写入二进制日志完成后，master通知存储引擎提交事务。

2. slave开始一个I/O线程，该线程在Master上建立一个普通链接，然后开始binlog dump process。Binlog dump process从master的二进制日志中读取事件，如果已经跟上master，它会睡眠并等待master产生新的事件。I/O线程将这些事件写入中继日志。

3. SQL线程从中继日志读取事件，并重放其中的事件而更新slave的数据，使其与master中的数据一致。只要该线程与I/O线程保持一致，中继日志通常会位于OS的缓存中，所以中继日志的开销很小。

注意：

在master中也有一个工作线程：和其它MySQL的连接一样，slave在master中打开一个连接也会使得master开始一个线程。复制过程有一个很重要的限制——复制在slave上是串行化的，也就是说master上的并行更新操作不能在slave上并行操作。

主从配置

主机A: 192.168.1.100

从机B:192.168.1.101

1、先登录主机 A

mysql>GRANT REPLICATION SLAVE ON *.* TO ‘backup’@’192.168.1.101‘ IDENTIFIED BY ‘123456’;

赋予从机权限，有多台丛机，就执行多次

backup是授权账号，123456是密码

2、 打开主机A的my.cnf，输入

server-id               = 1    #主机标示，整数

log_bin                 = /var/log/mysql/mysql-bin.log   #确保此文件可写

read-only              =0  #主机，读写都可以

binlog-do-db         =test   #需要备份数据，多个写多行

binlog-ignore-db    =mysql #不需要备份的数据库，多个写多行

3、打开从机B的my.cnf，输入

server-id               = 2

log_bin                 = /var/log/mysql/mysql-bin.log

master-host     =192.168.1.100

master-user     =backup

master-pass     =123456

master-port     =3306

master-connect-retry=60 #如果从服务器发现主服务器断掉，重新连接的时间差(秒)

replicate-do-db =test #只复制某个库

replicate-ignore-db=mysql #不复制某个库

4、同步数据库

不用太费事，只把主从库都启动即可自动同步，如果不嫌麻烦的话可以把主库的内容导出成SQL，然后在从库中运行一遍

5、先重启主机A的mysql，再重启从机B的mysql

6、验证

在主机A中，mysql>show master status\G;

在从机B中，mysql>show slave status\G;

Slave_IO_Running 和 Slave_SQL_Running, 必须都为 Yes

添加新slave服务器

假如master已经运行很久了，想对新安装的slave进行数据同步，甚至它没有master的数据。

此时，有几种方法可以使slave从另一个服务开始，例如，从master拷贝数据，从另一个slave克隆，从最近的备份开始一个slave。Slave与master同步时，需要三样东西：

1. master的某个时刻的数据快照；

2. master当前的日志文件、以及生成快照时的字节偏移。这两个值可以叫做日志文件坐标(log file coordinate)，因为它们确定了一个二进制日志的位置，你可以用SHOW MASTER STATUS命令找到日志文件的坐标；

3. master的二进制日志文件。

主从运维

1. 确保从库的两个线程都是正常运行的

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

如可监控和报警：

编写一shell脚本，用nagios监控slave的两个yes(Slave_IO及Slave_SQL进程)，如发现只有一个或零个yes，就表明主从有问题了，发短信警报吧。

2. 出现“log event entry exceeded max_allowed_pack”错误

如果在应用中使用大的BLOB列或CLOB列或者长字符串，那么在从服务器上回复时，可能会出现“log event entry exceeded max_allowed_pack”的错误，这是因为含有大文本的记录无法通过网络进行传输而导致的。

show variables LIKE '%max_all%'

Variable_name Value

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

max_allowed_packet 1048576

(1 row(s) affected)

解决方法：

同时在my.ini或my.cnf文件里设置max_allowed_packet=16M，数据库重启之后该参数将有效

set @@global.max_allowed_packet=16777216;

3. 多主复制时自增长冲突

大多数情况下使用一台主服务器对一台或者多台从服务器，但是在某些情况下可能会存在多个服务器配置为复制主服务器，使用auto_increment时应采取特殊步骤以防止键值冲突，否则插入时多个主服务器会试图使用相同的auto_increment值

解决方法：

在这里我们在A,B上加入参数，以实现奇偶插入

A：my.cnf上加入参数

auto_increment_offset = 1

auto_increment_increment = 2

这样A的auto_increment字段产生的数值是：1, 3, 5, 7, …等奇数ID了

B：my.cnf上加入参数

auto_increment_offset = 2

auto_increment_increment = 2

这样B的auto_increment字段产生的数值是：2, 4, 6, 8, …等偶数ID了

提示：

一般不建议使用双主或多主，因为这样会带来意想不到的冲突状况，就像SQLSERVER的对等复制，虽然有很多冲突检测措施。但是有时候冲突是不可预料的，出现冲突DBA要排查，维护成本较高，我们生产环境里是没有使用双主和多主，主要使用的是一主多从或一主一从

参考：

http://blog.csdn.net/hguisu/article/details/7325124/

http://369369.blog.51cto.com/319630/790921/

https://my.oschina.net/u/730579/blog/632790

http://www.cnblogs.com/xiao-yu/archive/2011/06/14/2080842.html