mysql主从复制实践（MySQL进阶之搭建主从复制集群）

mysql主从复制实践（MySQL进阶之搭建主从复制集群）比如读密集型应用的优化，需要读写分离的场景，将数据复制到多个服务器上，分担原来服务器读操作的压力。2.负载均衡下面是复制比较常见的用途1.数据分布主要用在一些需要在不同的地理位置分布数据备份的场景，比如不同数据中心来备份数据。

MySQL内建的复制功能是构建基于mysql的大规模、高性能应用的基础，这类应用一般使用水平扩展的架构。复制功能不仅有利于构建高性能的应用，同时也是高可用性、可扩展性、灾难恢复、备份以及数据仓库等工作的基础。通常，可扩展性和高可用性通常都是相关联的。

复制概述

复制解决的基本问题是让一台服务器的数据与其它服务器保持同步。一台主库的数据可以同步到多台备库上，备库本身也可以配置成另外一台服务器的主库。主库和备库之间可以有多种不同的组合方式。

通过复制可以将读操作指向备库来获得更好的读扩展，但对于写操作，除非设计得当，否则并不合适通过复制来扩展些操作。在一主多备的架构中，写操作会被执行多次，整个系统的性能取决于写入最慢的那台服务器。

• 复制方式

MySQL支持两种复制方式，基于行和基于语句的复制。基于语句的复制也称为逻辑复制。这两种方式都是通过在主库上记录二进制日志(binlog)、在备库重复日志的方式来实现异步的数据复制。

• 复制解决的问题

下面是复制比较常见的用途

1.数据分布

主要用在一些需要在不同的地理位置分布数据备份的场景，比如不同数据中心来备份数据。

2.负载均衡

比如读密集型应用的优化，需要读写分离的场景，将数据复制到多个服务器上，分担原来服务器读操作的压力。

3.备份

复制可以作为备份的一项技术补充。

4.高可用性和故障切换

通过数据复制，将数据同步到多态服务器上，在主机机出现单点故障时，可以即时切换到其它的服务器上。

5.MySQL升级测试

这种做法比较普遍，使用一个更高版本的MySQL做为备库，保证在升级全部实例前，查询能够在备库按照预期进行。

• 复制的工作流程

MySQL通过以下3个步骤复制数据：

1.在主库上开启二进制日志binlog，把数据变更记录到日志中（这些变更记录被称为二进制日志事件）

2.备库将主库上的二进制日志复制到自己的中继日志relaylog中

3.备库读取中继日志中的事件，将其重放到备库数据之上

步骤1中，主库将二进制日志事件写入binlog中，步骤2中，备库会启动1个I/O线程，连接到主库，主库会启动1个二进制转储线程将二进制日志传输到给备库的I/O线程进行保存。步骤3中，备库另外启动1个SQL线程，读取中继日志，将日志事件在备库中执行，从而完成备库数据的更新。整个过程中，会启动3个线程专门用于数据的复制。

搭建一主多备集群

假设主库服务器IP为192.168.8.125，然后配置两个备库服务器，IP分别是192.168.8.128，192.168.8.132。分别在三台服务器上安装好MySQL数据库。然后按步骤一步一步搭建。

• 创建复制账号

分别在三台服务上使用root用户或有权限的用户登录mysql命令行客户端，执行如下命令创建专门用户复制数据的账号同时授权

GRANT REPLICATION SLAVE REPLICATION CLIENT ON *.* TO repl@'192.168.8.%' IDENTIFIED BY '123456'; FLUSH PRIVILEGES;

可能需要关闭防火墙或开放对应的端口号，如果登录连接不上的话

sudo systemctl stop iptables（需要安装iptables服务） sudo systemctl stop firewalld（默认） -- sudo systemctl disable firewalld.service（设置开启不启动）

• 配置主库服务器

修改/etc/my.cnf配置文件，开启二进制日志，并指定一个唯一的服务器ID，配置完成后，重启主库服务器

-- 开启并指定二级制日志文件的前缀名，日志保存在数据文件相同目录下，比如/var/lib/mysql log_bin=mysql-bin server_id=10

• 配置备库服务器

修改两台备库服务器的/etc/my.cnf配置文件，指定一个唯一的服务器ID。配置i完成后重启备库服务器。

-- 对于备库服务器，必须配置的变量只有server_id -- log_bin=mysql-bin server_id=11 -- relay_log=/var/lib/mysql/mysql-relay-bin -- log_slave_updates=1 -- read_only=1

-- 对于备库服务器，必须配置的变量只有server_id -- log_bin=mysql-bin server_id=12 -- relay_log=/var/lib/mysql/mysql-relay-bin -- log_slave_updates=1 -- read_only=1

• 启动复制

主库中直接用root先创建新的数据库和表，并插入新的数据

create database if not exists books default charset utf8 collate utf8_general_ci; use books; create table tuser( id int primary key loginname varchar(100) name varchar(100) age int sex char(1) dep int address varchar(100) ); create table tdep( id int primary key name varchar(100) ); insert into tdep values (1 '技术部'); insert into tuser (id loginname name age sex dep address) values(1 'chengshangqian' '九五' 36 'M' 1 '广东广州越秀登峰街道狮带岗小区');

完毕，确认一下二进制文件是否已经在主库创建、具体的日志文件名等信息

show master status;

登录到备库服务器（root用户），输入change master命令指定要复制的主库

-- 查看当前数据库信息，此时还没有看到books库 mysql> show databases; ...省略输出 -- 指向主库 mysql> change master to master_host='192.168.8.125' -> master_user='repl' -> master_password='123456' -> master_log_file='mysql-bin.000001' -> master_log_pos=0;

其中，命令中涉及的参数为含义具体如下

master_host 主库服务器主机IP

master_port 主库服务器主机端口号

master_user 主库服务器上要复制的数据库用户

master_password 主库服务器上要复制的数据库用户密码

master_log_file 主库服务器上的二进制日志文件

master_log_pos 读取二进制日志的起始读取位置，0表示从日志的开头读取

可以通过show slave status语句检查复制是否正确执行

mysql> show slave status \G *************************** 1. row *************************** Slave_IO_State: Master_Host: 192.168.8.125 Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000001 Read_Master_Log_Pos: 4 Relay_Log_File: mysql-relay-bin.000001 Relay_Log_Pos: 4 Relay_Master_Log_File: mysql-bin.000001 Slave_IO_Running: No Slave_SQL_Running: No ...省略 Seconds_Behind_Master: NULL ...省略 Master_TLS_Version:

Slave_IO_State、Slave_IO_Running、Slave_SQL_Running这三列显示当前备库复制尚未运行。

接着执行以下命令启动复制

mysql> start slave;

正确执行后，再次使用show slave status命令检查复制的情况

mysql> show slave status \G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.8.125 Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000001 Read_Master_Log_Pos: 1463 Relay_Log_File: mysql-relay-bin.000002 Relay_Log_Pos: 1676 Relay_Master_Log_File: mysql-bin.000001 Slave_IO_Running: Yes Slave_SQL_Running: Yes ...省略 Seconds_Behind_Master: 0 ...省略 Master_TLS_Version:

可以通过show processlist命令查看线程列表，可以发现在备库中，会有两个连接到主库的线程（id为7和8，Command类型为Connect的线程）在处理复制的工作

mysql> show processlist \G; ...省略 *************************** 3. row *************************** Id: 7 User: system user Host: db: NULL Command: Connect Time: 542 -- 闲置时间 State: Waiting for master to send event Info: NULL *************************** 4. row *************************** Id: 8 User: system user Host: db: NULL Command: Connect Time: 2239 -- 闲置时间 State: Slave has read all relay log; waiting for more updates Info: NULL

以上的操作都是在备库上执行的。也可以登录主库，查看线程列表，可以看到由备库I/O线程向主库发起的连接而启动的二进制转储线程（id为6，Command为Binlog Dump的线程）。

mysql> show processlist \G *************************** 1. row *************************** Id: 6 User: repl Host: 192.168.8.132:39852 db: NULL Command: Binlog Dump Time: 703 State: Master has sent all binlog to slave; waiting for more updates Info: NULL ...省略

最后，使用show databases、show tables、select等命令或语句查看数据等是否已经同步过来。

检查数据复制结果

可以看到，数据已经复制成功。

到此，基本的主备复制已经完成。后续可以以此为基础开展扩展、备份、恢复的工作，实现系统的高可用。

主备复制延迟原因及解决办法

• 主备复制延迟原因

一般情况下，一个服务器开放Ｎ个链接给客户端来连接的， 这样就会有大并发的更新操作，但是备库服务器的里面读取binlog 的线程仅有一个，当某个SQL在备库服务器上执行的时间稍长或者由于某个SQL要进行锁表就会导致，主库服务器的SQL大量积压，未被同步到备库服务器里。这就导致了主备不一致， 也就是主备延迟。

• 解决办法

主备同步延迟是没办法完全避免的， 因为所有的SQL必须都要在备库服务器里面执行一遍，但是主库服务器如果不断的有更新操作源源不断的写入， 那么一旦有延迟产生， 那么延迟加重的可能性就会越来越大。以下是一些可以做的缓解的措施：

1.减少日志的累积，提高同步频率

因为主服务器要负责更新操作， 对安全性的要求比备库服务器高， 所以有些设置可以修改，比如

sync_binlog=1 innodb_flush_log_at_trx_commit = 1

而备库服务器则不需要这么高的数据安全，完全可以将sync_binlog设置为0或者关闭binlog，innodb_flushlog，innodb_flush_log_at_trx_commit 也 可以设置为0来提高sql的执行效率，这个能很大程度上提高效率。另外就是使用比主库服务器更好的硬件设备作为备库服务器。

2. 把一台备库服务器当作为备份使用，不提供查询， 其负载下来了， 执行relay log里面的SQL效率自然就高

3. 增加备库服务器，这个目的还是分散读的压力， 从而降低服务器负载。