Mysql主从/主主同步手册

Mysql主从复制概述

MySQL数据库自身提供的主从复制功能可以方便的实现数据的多处自动备份，实现数据库的拓展。多个数据备份不仅可以加强数据的安全性，通过实现读写分离还能进一步提升数据库的负载性能。

下图就描述了一个多个数据库间主从复制与读写分离的模型：

在一主多从的数据库体系中，多个从服务器采用异步的方式更新主数据库的变化，业务服务器在执行写或者相关修改数据库的操作是在主服务器上进行的，读操作则是在各从服务器上进行。

Mysql支持哪些复制

• 基于语句的复制：在主服务器上执行的sql语句，在从服务器上执行同样的语句。mysql默认采用基于语句的复制，效率比较高。一旦发现没法精确复制时，会自动选择基于行的复制。
• 基于行的复制：把改变的内容复制过去，而不是把命令在从服务器上执行一遍。从mysql 5.0开始支持
• 混合类型的复制：默认采用基于语句的复制，一旦发现基于语句的无法精确复制时，就会采用基于行的复制。

主从复制原理       

    当Master上的数据发生改变时，则将其改变写入二进制binlog日志中；Slave服务器会在一定时间间隔内对Master二进制日志进行探测其是否发生改变，如果发生改变，则开始一个I/O Thread请求Master二进制事件，同时主节点为每个I/O线程启动一个dump线程，用于向其发送二进制事件，并保存至从节点本地的中继日志中，从节点将启动SQL线程从中继日志中读取二进制日志，在本地重放，使得其数据和主节点的保持一致，最后I/O Thread和SQL Thread将进入睡眠状态，等待下一次被唤醒。

注意事项：

• master将操作语句记录到binlog日志中，然后授予slave远程连接的权限（master一定要开启binlog二进制日志功能；通常为了数据安全考虑，slave也开启binlog功能）。

• slave开启两个线程：IO线程和SQL线程。其中：IO线程负责读取master的binlog内容到中继日志relay log里；SQL线程负责从relay log日志里读出binlog内容，并更新到slave的数据库里，这样就能保证slave数据和 master数据保持一致了。

• Mysql复制至少需要两个Mysql的服务，当然Mysql服务可以分布在不同的服务器上，也可以在一台服务器上启动多个服务。

• Mysql复制最好确保master和slave服务器上的Mysql版本相同（如果不能满足版本一致，那么要保证master主节点的版本低于slave从节点的版本）

• master和slave两节点间时间需同步

Mysql主主同步

      主主同步事实上就是在主从的基础上，将原先的从机当主机，主机当从机再配置一遍主从同步  。 只不过MySQL的配置文件有些地方需要注意一下。

主从复制需要进行的配置

•    主服务器：
  • 开启二进制日志
  • 配置唯一的server-id
  • 获得master二进制日志文件名及位置
  • 创建一个用于slave和master通信的用户账号
• 从服务器：
  • 配置唯一的server-id
  • 使用master分配的用户账号读取master二进制日志
  • 启用slave服务

配置文件注意事项

[mysqld]
server-id                      = 1                     #[必须]服务器唯一ID，每台服务器需不同
log-bin                        = /home/mysql/mysql-bin #[必须]启用二进制文件
binlog_format                  = mixed                 #[不是必须]二进制文件启用混合模式
expire-logs-days               = 14                    #[不是必须]二进制文件过期时间，单位是天
sync-binlog                    = 1                     #[不是必须]当每进行1次事务提交之后，MySQL将进行一次磁盘同步指令来将binlog_cache中的数据强制写入磁盘
# MASTER DB #
binlog-do-db                   = test,androidpnserver   #[不是必须]只将对应的数据库变动写入二进制文件。如果有多个数据库可用逗号分隔,或者使用多个binlog-do-db选项
binlog-ignore-db               = mysql,information_schema #[必须]不需要记录二进制日志的数据库。如果有多个数据库可用逗号分隔，或者使用多个binlog-do-db选项。一般为了保证主主同步不冲突，会忽略mysql数据库。
auto-increment-increment       = 10                       #[必须]
auto-increment-offset          = 1                        #[必须]
#做主主备份的时候，因为每台数据库服务器都可能在同一个表中插入数据，如果表有一个自动增长的主键，那么就会在多服务器上出现主键冲突。
#解决这个问题的办法就是让每个数据库的自增主键不连续。上面两项说的是，假设需要将来可能需要10台服务器做备份，将auto-increment-increment设为10。而auto-increment-offset=1表示这台服务器的序号。从1开始，不超过auto-increment-increment。
# SLAVE DB #
replicate-do-db                = test,androidpnserver    #[不是必须]只同步对应的数据库。如果有多个数据库可用逗号分隔，或者使用多个replicate-do-db选项
replicate-ignore-db            = mysql,information_schema #[必须]不需要同步的数据库。如果有多个数据库可用逗号分隔，或者使用多个replicate-ignore-db选项。一般为了保证主主同步不冲突，会不同步mysql数据库。
relay_log                      = /home/mysql/relay-bin    #[不是必须]开启中继日志，复制线程先把远程的变化复制到中继日志中，再执行。
log-slave-updates              = ON          #[必须]中继日志执行之后将变化写入自己的二进制文件
slave-skip-errors              = all         #[不是必须]跳过所有的sql语句失败

主服务器配置

修改mysql配置
vi /etc/my.cnf

创建用于同步数据的账号

grant replication slave on *.* to 'sync'@' 172.16.210.60' identified by '<YourPassword>';    #IP地址可以指定固定的IP，提高安全性。若用'%'则表示所有的IP均可
flush privileges;

重启mysql

service mysqld restart

查看主服务器状态

指定同步位置

CHANGE MASTER TO MASTER_HOST='172.16.210.60',MASTER_USER='sync',MASTER_PASSWORD='<YourPassword>',MASTER_LOG_FILE='mysql-bin.000004',MASTER_LOG_POS=6412;

启动I/O 线程和SQL线程

start slave

#不带参数表示同时启动IO线程和SQL线程

start slave sql_thread;
start slave io_thread;

#也可以通过制定参数，分别启动IO线程和SQL线程

查看从服务状态

从服务器配置

修改mysql配置

注意红色圈出的部分与主服务器不一致

创建用于同步数据的账号

grant replication slave on *.* to 'sync'@' 172.16.10.120' identified by '<YourPassword>';    #IP地址可以指定固定的IP，提高安全性。若用'%'则表示所有的IP均可
flush privileges;

重启mysql

service mysqld restart

查看主服务器状态

指定同步位置

CHANGE MASTER TO MASTER_HOST='172.16.10.120',MASTER_USER='sync',MASTER_PASSWORD='<YourPassword>',MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=1971;

 启动I/O 线程和SQL线程

start slave

#不带参数表示同时启动IO线程和SQL线程

start slave sql_thread;
start slave io_thread;

#也可以通过制定参数，分别启动IO线程和SQL线程

查看从服务状态

到此为止主主同步配置完成，接下来就可以进行测试了。在其中任何一台服务器做数据的增删改、另一台服务器也都有相应的体现。

下一篇文章将会介绍，当某一台服务器发生故障时，如何实现故障转移，达到高可用的目的。