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/mysqllog_bin=mysql-binserver_id=10
- 配置备库服务器
修改两台备库服务器的/etc/my.cnf配置文件,指定一个唯一的服务器ID。配置i完成后重启备库服务器。
-- 对于备库服务器,必须配置的变量只有server_id-- log_bin=mysql-binserver_id=11-- relay_log=/var/lib/mysql/mysql-relay-bin-- log_slave_updates=1-- read_only=1
-- 对于备库服务器,必须配置的变量只有server_id-- log_bin=mysql-binserver_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: NULLCommand: Connect Time: 542 -- 闲置时间 State: Waiting for master to send event Info: NULL*************************** 4. row *************************** Id: 8 User: system user Host: db: NULLCommand: 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: NULLCommand: Binlog Dump Time: 703 State: Master has sent all binlog to slave; waiting for more updates Info: NULL ...省略
最后,使用show databases、show tables、select等命令或语句查看数据等是否已经同步过来。
检查数据复制结果
可以看到,数据已经复制成功。
到此,基本的主备复制已经完成。后续可以以此为基础开展扩展、备份、恢复的工作,实现系统的高可用。
主备复制延迟原因及解决办法
- 主备复制延迟原因
一般情况下,一个服务器开放N个链接给客户端来连接的, 这样就会有大并发的更新操作,但是备库服务器的里面读取binlog 的线程仅有一个,当某个SQL在备库服务器上执行的时间稍长或者由于某个SQL要进行锁表就会导致,主库服务器的SQL大量积压,未被同步到备库服务器里。这就导致了主备不一致, 也就是主备延迟。
- 解决办法
主备同步延迟是没办法完全避免的, 因为所有的SQL必须都要在备库服务器里面执行一遍,但是主库服务器如果不断的有更新操作源源不断的写入, 那么一旦有延迟产生, 那么延迟加重的可能性就会越来越大。以下是一些可以做的缓解的措施:
1.减少日志的累积,提高同步频率
因为主服务器要负责更新操作, 对安全性的要求比备库服务器高, 所以有些设置可以修改,比如
sync_binlog=1innodb_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. 增加备库服务器,这个目的还是分散读的压力, 从而降低服务器负载。