mysql主从复制维护_MySQL主从复制详解

一、MySQL 主从复制原理

1、简介

MySql自带有主从复制功能，该功能是构建大型应用、高性能应用的基础。这种机制是指：将某一台主机(master)上的MySQL数据复制到多台其他主机上(slave)，并重新执行一遍来实现。复制过程中一个Mysql实例充当主库(master)，其他Mysql实例充当分库(slave)。

2、复制架构图

主库将更新写入binlog(二进制日志)，并维护文件的一个索引以跟踪日志循环。如图：

从库生成两个线程，一个I/O线程，一个SQL线程。I/O线程去请求主库的binlog，　并将得到的binlog日志写到relay log(中继日志)文件中；

主库会生成一个log dump线程，用来给从库I/O线程传binlog；SQL线程会读取relay log文件中的日志，并解析成具体操作，来实现主从的操作一致，而最终数据一致。

3、MySQL支持的复制类型

3.1 基于语句的复制。在主服务器上执行的 SQL 语句，在从服务器上执行同样的语句。配置：binlog_format = 'STATEMENT';

3.2 基于行的复制。把改变的内容复制过去，而不是把命令在从服务器上执行一遍，从 MySQL 5.0开始支持。配置： binlog_format = 'ROW';

3.3 混合类型的复制。默认采用基于语句的复制，一旦发现基于语句的无法精确的复制时，就会采用基于行的复制。配置：binlog_format = 'MIXED';

4、解决的问题

数据分布

负载平衡

备份

高可用性和容错行

5、复制的常用拓扑结构

复制的拓扑结构有以下一些基本原则：

1> 每个Slave只能有一个Master

2>每个Slave只能有一个唯一的服务器ID

3>每个Master可以有很多Slave

4>如果设置了log_slave_updates，Slave可以是其他Slave的Master，从而扩散Master的更新。

MySQL不支持多主服务器复制，即一个Slave可以有多个Master。

常用拓扑结构：

5.1 一主多复制架构

由一个Master和多个Slave组成复制系统，Slave之间不通信。

在实际场景中，MySQL复制大部分都是一主多复制这种架构。在Master读取请求压力非常大的场景下，把大量对实时性要求不是特别高的读取通过负载均衡到多个从Slave上，降低主库的读取压力。在Master宕机时，可以把一个Slave切换为主库继续提供服务。

问题建议：

1> 当Slave增加到一定数量时，Slave对Master的负载及网络带宽会成为一个严重的问题。

2> 不同的Slave扮演不同的角色(例如使用不同的索引，或者不同的存储引擎)。

3> 用一个Slave作为备用Master，只进行复制。

4> 用一个远程Slave，用于灾难恢复。

5.2 多级复制架构

一主多从的架构能够解决大部分请求压力特别大的场景需求，但随着从库的增加，会影响到Master的I/O和网络压力，而使用多级复制架构就可以解决一主多从的这个问题。但同时要注意的是，多级复制场景下主库的数据是经历了多次才到达Salve，期间的延时也会比一主多从的复制要大。

问题建议：

1> 会根据层级的多少增加复制的延时。

2> 这种方案可以与第三方软件结合使用，如：Slave + LVS + Keepalived实现高可用

5.3 双主复制/Dual Master架构

如果写压力比较大，或者DBA做维护需要主从切换，通过双主复制/Dual Master架构可以避免重复搭主从库的麻烦。

问题建议：

1> 最大的问题就是更新冲突

2> 可以采用MySQL Cluster，以及将Cluster和Replication结合起来，可以建立强大的高性能数据平台。

二、MySQL主从配置

1、基础环境配置

数据库版本：mysql 5.1.73(Slave版本 >= Master版本)

IP地址：192.168.1.100 (Master)、192.168.1.101(Slave)

2、Master服务器配置

2.1(关掉新主库的只读属性)

mysql>set global read_only=0;

mysql>flush privileges;

2.2 开启读写属性

mysql>set global read_only=1;

mysql>flush privileges;

2.3 基于行的复制。把改变的内容复制过去，而不是把命令在从服务器上执行一遍，从 MySQL 5.0开始支持

mysql> SET SESSION binlog_format = 'ROW';

mysql> SET GLOBAL binlog_format = 'ROW';

2.4 在 Master 的数据库中建立一个复制账户，每个 Slave 使用该账户连接 Master 进行复制，需要 replication slave 和 replication client 权限，Master 的连接信息会存储在文本文件 master.info 文件中。(master.info文件在 Slave 的数据目录中)

说明：创建了一个用户名为 replication 的用户，密码为 123456 ,只允许在 192.168.1.101 这个 Slave 上登录。

mysql>grant replication slave on replication clinet on *.* to 'replication'@'192.168.1.101(slave IP)' identified by '123456';

mysql>flush privileges;

2.5 修改Master MySQL的数据库配置文件，默认是/etc/my.cnf

# 开启二进制日志，并指定文件所在目录，并在服务器上创建目录和授权

log-bin=/var/log/mysql/master-bin

#增加配置

binlog_format=mixed

2.6 取消server-id注释，不能和Slave一致

server-id = 1

2.7 在[mysqld]下面添加

binlog-do-db=db_test  #要同步的数据库，如果有多个，则需要多写几行。

binlog-ignore-db=db_ignore_test #不需要同步的数据库，如果多个，则需要多写几行。

2.8 重启MySQL服务

2.9 查看Master状态

show master status;

File字段和Position字段要注意，在配置Slave时，需要指定相关值。

File

Position

Binlog_Do_DB

Binlog_Ignore_DB

master-bin.000208

1562

db_test

db_ignore_test

3、Slave服务器配置

3.1 取消server-id注释，不能和Slave一致

server-id = 101

3.2 添加中继日志，创建日志目录，授权

relay-log=/var/log/mysql

3.3 重启mysql服务

3.4 查看中继日志状态

mysql>show globalvariables like '%relay%';

3.5 连接Master服务器

mysql>change master to master_host='192.168.1.100',master_user='replication',master_password='123456',master_log_file='master-bin.000208',master_log_pos=1562;

选项：

master_host：Master 服务器IP

master_user：Master 服务器授权用户，也就是 Master 前面创建的那个用户

master_password：Master 服务器授权用户对应的密码

master_log_file：Master binlog 文件名，对应查询Master服务器状态时，File字段

master_log_pos：Master binlog 文件中的 Postion 值，对应查询Master服务器状态时，Position字段

更多的选项可以看:http://dev.mysql.com/doc/refman/5.7/en/change-master-to.html

#其中master_log_file、master_log_pos和File、Position不对应会提示1263错误，如：Slave I/O: Got fatal error 1236 from master when reading data from binary log: 'Could not find first log file name in binary log index file', Internal MariaDB error code: 1236

解决方案是：在Master服务器上，查看Master状态，根据查看结果中的File字段和Position字段。在Slave中执行，

stop slave;

change master to master_log_file='master-bin.000208',master_log_pos=1562;

start slave;

3.6 查看主从状态

show slave status;

如果Last_SQL_Error没有错误提示以及Slave中的Exec_Master_Log_Pos值和Master中的show master status中的Position值一样，这样的话，MySQL主从复制应该是成功的。

4、测试

在Master数据库中执行sql语句操作，观察Slave是否同步，如果同步则说明配置成功。

5、注意

1> 主库和从库数据库名称必须相同

2> 主库和从库的复制可以精确到表，但是在需要改主库或者从库的数据库结构时需要立刻重启slave

3> 不能在MySQL配置文件里直接写入master的配置信息，需要用change master命令来完成。

4> 指定replicate_do_db必须在my.cnf里配置，不能用change master命令来完成；

5> 如果不及时清理，日积月累二进制日志文件可能会把磁盘空间占满，可以在配置文件里加上expire_logs_days=7，只保留最近7天的日志，建议当slave不再使用时，通过reset slave来取消relaylog；

6>写一个监控脚本，用来监控 Slave 中的两个"yes"，如果只有一个"yes"或者零个，就表明主从有问题

收集的几个相关知识文章：

http://hzcsky.blog.51cto.com/1560073/479476/

http://storysky.blog.51cto.com/628458/259280

http://outofmemory.cn/code-snippet/3177/mysql-zhucong-library-clock-error-%EF%BC%9A-1062-Error-Duplicate-entry-1438019-for-key-PRIMARY-on-quer

http://www.lookingss.org/index.php/archives/3.html

http://369258.blog.51cto.com/359258/1345239

http://www.cnblogs.com/gomysql/p/3662264.html

http://tiany.blog.51cto.com/513694/173526

http://www.cnblogs.com/chenpingzhao/p/5060874.html