mysql5.7主从复制

1. 简介

　　随着技术的发展，在实际的生产环境中，由单台MySQL数据库服务器不能满足实际的需求。此时数据库集群就很好的解决了这个问题。采用MySQL分布式集群，能够搭建一个高并发、负载均衡的集群服务器。在此之前我们必须要保证每台MySQL服务器里的数据同步。数据同步我们可以通过MySQL内部配置就可以轻松完成，主要有主从复制和主主复制。
　　MySQL数据库自身提供的主从复制功能可以方便的实现数据的多处自动备份，实现数据库的拓展。多个数据备份不仅可以加强数据的安全性，通过实现读写分离还能进一步提升数据库的负载性能。

         在一主多从的数据库体系中，多个从服务器采用异步的方式更新主数据库的变化，业务服务器在执行写或者相关修改数据库的操作是在主服务器上进行的，读操作则是在各从服务器上进行。如果配置了多个从服务器或者多个主服务器又涉及到相应的负载均衡问题，关于负载均衡具体的技术细节还没有研究过，今天就先简单的实现一主一从的主从复制功能。

1 为什么要做主从复制？

• 读写分离。在业务复杂的系统中，有这么一个情景，有一句sql语句需要锁表，导致暂时不能使用读的服务，那么就很影响运行中的业务，使用主从复制，让主库负责写，从库负责读，这样，即使主库出现了锁表的情景，通过读从库也可以保证业务的正常运作。
• 做数据的热备
• 架构的扩展。业务量越来越大，I/O访问频率过高，单机无法满足，此时做多库的存储，降低磁盘I/O访问的频率，提高单个机器的I/O性能。

 

Mysql复制过程分成三步：

1：master将改变记录到二进制日志（binary log）,这些记录过程叫做二进制日志时间。

2：slave将master的binary log events 拷贝到它的中继日志（relay log）。

3：salve 重做中继日志中的事件，将改变应用到自己的数据库中，Mysql复制是异步且串行化的。

 

复制的基本原则：

1：每个slave只有一个master,每个slave只能有唯一的一个服务器id,每个master可以有多个slave。

2：mysql的版本一致，且相互之间网络互通（ping）

 

 

mysql一主一从配置步骤：

1：编辑主节点配置文件

vi  /etc/my.cnf

 

2：启用二进制日志文件

在【mysqld】下加入如下内容：

# 二进制日志存放位置
log-bin = 你的路径/mysql-bin
# 主服务器唯一id
server-id =1
# mysql错误日志存放位置
log-err = 你的路径/mysql-err
# 主机读写都OK
read-only=0
#设置不需要复制的库（可选）
#binlog-ignore-db=mysql
#设置需要复制的库（可选）
#binlog-do-db= 需要复制的库名

其他可选参数

binlog_format           = MIXED                 //binlog日志格式

expire_logs_days     = 7                //binlog过期清理时间

max_binlog_size      100m                    //binlog每个日志文件大小

 

重启mysql服务

systemctl restart mysqld.service

 

3:进入mysql查看二进制日志是否开启

mysql -uroot -p

命令： SHOW GLOBAL VARIABLES LIKE '%log%';

 

查看主节点二进制日志列表:     SHOW MASTER LOGS;

 

4、在主机上建立账户并授权slave

GRANT REPLICATION SLAVE ON *.* TO 'yourUser'@'192.168.131.141' IDENTIFIED BY 'yourPassword';

flush privileges;

--查询master的状态
show master status\G

 

 查询master的状态:   show master status\G;

 记录上图结果中File和Position的值。

 

5：编辑从节点配置文件

vi  /etc/my.cnf

开启中继日志, 在【mysqld】下加入如下内容：

relay-log=relay-log
relay-log-index=relay-log.index
server-id=2

重启mysql服务：

systemctl restart mysqld.service

 

6、查看从节点日志信息

SHOW GLOBAL VARIABLES LIKE '%log%';

 

7、在从节点配置访问主节点的参数信息
添加 主节点主机，访问主节点的用户名及密码，主节点二进制文件信息。
注意：主节点的二进制文件一定要是二进制列表中的最后一个二进制文件。

CHANGE MASTER TO master_host = 'your master ip',
 master_user = 'yourUser',
 master_password = 'yourPassword',
 master_log_file = 'mysql-bin.0000001',
 master_log_pos = 120;

 

 

 

8、查看从节点的状态信息

SHOW SLAVE STATUS\G;

因为没有启动 从节点的复制线程，IO线程 和 SQL 线程都为NO.

 

 启动复制线程

//开启复制
start slave;

再次查看从节点状态信息

 

测试主从复制是否成功

在Master中创建数据库并创建数据表并插入一条数据

在Slave中查询这条数据

 

常用语句

show master status: 查看master的状态，尤其是当前的日志及位置
show slave status 查看slave的状态
reset slave 重置slave状态
start slave 启动slave状态
stop slave 暂停slave状态

 

主从复制架构中应注意的问题：

从节点要设置某些限定使得它不能进行写操作,才能保证复制当中的数据一致。

1：限制从服务器为只读
在从服务器上设置：
read_only = ON,但是此限制对拥有SUPER权限 的用户均无效。
阻止所有用户：
mysq>FLUSH TABLES WITH READ LOCK;

2:如何保证主从复制时的事物安全？
1：在主节点设置参数
sync_binlog=1： Mysql开启bin-log日志使用bin-log时，默认情况下，并不是每次执行写入就与硬盘同步，这样在服务器崩溃时，就可能导致bin-log最后的语句丢失。可以通过这个参数来调节，sync_binlog=N，使执行N次写入后，与硬盘同步。1是最安全的，但是也是最慢的。
如果用到innode 存储引擎:
innodb_flush_logs_at_trx_commit=ON（刷写日志：在事务提交时，要将内存中跟事务相关的数据立即刷写到事务日志中去。）
innodb_support_xa=ON (分布式事务：基于它来做两段式提交功能)
sync_master_info=1：每次给从节点dump一些事件信息之后，主节点的master info 信息会立即同步到磁盘上。让从服务器中的 master_info 及时更新。

 

 

2：在每个slave节点
skip_slave_start =ON (跳过自动启动，使用手动启动。)
relay_log也会在内从中先缓存，然后在同步到relay_log中去，可以使用下面参数使其立即同步。
sync_relay_log =1 ，默认为10000，即每10000次sync_relay_log事件会刷新到磁盘。为0则表示不刷新，交由OS的cache控制。
sync_relay_log_info= 1   每间隔多少事务刷新relay-log.info，如果是table（innodb）设置无效，每个事务都会更新