主从复制
一、什么是主从复制
MySQL 主从复制是指数据可以从一个MySQL数据库服务器主节点复制到一个或多个从节点。MySQL 默认采用异步复制方式,这样从节点不用一直访问主服务器来更新自己的数据,数据的更新可以在远程连接上进行,从节点可以复制主数据库中的所有数据库或者特定的数据库,或者特定的表。
主从复制的主要用途
① 读写分离:在开发工作中,有时候会遇见某个sql 语句需要锁表,导致暂时不能使用读的服务,这样就会影响现有业务,使用主从复制,让主库负责写,从库负责读,这样,即使主库出现了锁表的情景,通过读从库也可以保证业务的正常运作。
② 数据实时备份,当系统中某个节点发生故障时,可以方便的故障切换
③ 高可用HA
④ 架构扩展:随着系统中业务访问量的增大,如果是单机部署数据库,就会导致I/O访问频率过高。有了主从复制,增加多个数据存储节点,将负载分布在多个从节点上,降低单机磁盘I/O访问的频率,提高单个机器的I/O性能。
主从复制的规则
①每个slave只能有一个master。(一对一)
②每个slave只能有一个唯一的服务器ID。
③每个master可以有多个slave。(一对多)
在主从复制过程中,最大的问题就是延时。
二、几种主从复制的形式
一主一从
一主多从,提高系统的读性能
一主一从和一主多从是最常见的主从架构,实施起来简单并且有效,不仅可以实现HA,而且还能读写分离,进而提升集群的并发能力。
多主一从 (从5.7开始支持)
多主一从可以将多个mysql数据库备份到一台存储性能比较好的服务器上。
双主复制
即互做主从复制,每个master既是master,又是另外一台服务器的slave。这样任何一方所做的变更,都会通过复制应用到另外一方的数据库中。
级联复制
级联复制模式下,部分slave的数据同步不连接主节点,而是连接从节点。因为如果主节点有太多的从节点,就会损耗一部分性能用于replication,那么我们可以让3~5个从节点连接主节点,其它从节点作为二级或者三级与从节点连接,这样不仅可以缓解主节点的压力,并且对数据一致性没有负面影响。
三、主从复制原理
MySQL主从复制涉及到三个线程,一个运行在主节点(log dump thread),其余两个(I/O thread, SQL thread)运行在从节点,如下图所示:
① 主节点 binary log dump 线程
当从节点连接主节点时,主节点会创建一个log dump 线程,用于发送bin-log的内容。在读取bin-log中的操作时,此线程会对主节点上的bin-log加锁,当读取完成,甚至在发动给从节点之前,锁会被释放。
② 从节点I/O线程
当从节点上执行`start slave`命令之后,从节点会创建一个I/O线程用来连接主节点,请求主库中更新的bin-log。I/O线程接收到主节点binlog dump 进程发来的更新之后,保存在本地relay-log中。
③ 从节点SQL线程
SQL线程负责读取relay log中的内容,解析成具体的操作并执行,最终保证主从数据的一致性。
对于每一个主从连接,都需要三个进程来完成。当主节点有多个从节点时,主节点会为每一个当前连接的从节点建一个binary log dump 进程,而每个从节点都有自己的I/O进程,SQL进程。从节点用两个线程将从主库拉取更新和执行分成独立的任务,这样在执行同步数据任务的时候,不会降低读操作的性能。比如,如果从节点没有运行,此时I/O进程可以很快从主节点获取更新,尽管SQL进程还没有执行。如果在SQL进程执行之前从节点服务停止,至少I/O进程已经从主节点拉取到了最新的变更并且保存在本地relay日志中,当服务再次起来之后,就可以完成数据的同步。
要实施复制,首先必须打开Master 端的binary log(bin-log)功能,否则无法实现。
slave会从master读取binlog来进行数据同步。主要有以下三个步骤:
① master将改变记录到二进制日志(binary log),这些记录过程叫做二进制日志事件(binary log events)。
② slave将master的binary log events拷贝到中继日志(relay log)。
③ slave重做中继日志中的事件,将改变应用到自己的数据库中。MySQL的复制是异步且串行化的。
MySQL 主从复制默认是异步的模式。MySQL增删改操作会全部记录在binary log中,当slave节点连接master时,会主动从master处获取最新的bin log文件。并把bin log中的sql relay。
四、Windows主从配置部署
一主一从
环境介绍及说明
主库所在的操作系统:win7
主库的版本:mysql-5.7.20-winx64.zip
主库的ip地址:127.0.0.1
主库的端口:3310
从库所在的操作系统:win7
从库的版本:mysql-5.7.20-winx64.zip
从库的ip地址:127.0.0.1
从库的端口:3316
注:主库和从库版本可以一致也可以不一致,需要说明一点,如果两者版本不一致,一般主库的版本需要比从库的版本低,这样就可以避免由于版本问题,有些sql不能执行的问题。
由于我下载的mysql都是解压版的,所以只需要把下载好的zip包解压到服务器上即可,就不需要安装了。
主库(master)的安装及配置
① 进入主库mysql-5.7.20-winx64-master目录中,在此目录中新建my.ini文件并添加一下配置。
具体文件内容如下,将里面的路径修改成你自己的主库路径。
#==============================客户端的参数 [client] #MySQL 客户端连接服务器端时使用的端口号,默认的端口号为 3306。如果需要更改端口号的话,可以直接在这里修改。 port = 3310 [mysql] #MySQL 客户端默认的字符集 default-character-set=utf8 #==============================服务端的参数 [mysqld] #====主从复制关键配置 start====== #主库和从库需要不一致,配一个唯一的ID编号,1至32。 手动设定 server_id=1 #二进制文件存放路径,存放在根目录data log-bin=mysql-bin #binlog-do-db=test #需要复制的库,多个库用逗号隔开,如果此项不配置所有主库都参与复制 #binlog-ignore-db=mysql #不需要复制的库,和上项同理 #====主从复制关键配置 end====== #服务器端默认的字符集 character-set-server=utf8 #MySQL 服务器的端口号 port = 3310 #SQL 模式的参数,通过这个参数可以设置检验 SQL 语句的严格程度 sql_mode="STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION" #创建数据表时,默认使用的存储引擎 default_storage_engine=innodb #表示缓存的大小,InnoDB 使用一个缓冲池类保存索引和原始数据。 innodb_buffer_pool_size=1000M #表示日志文件的大小 innodb_log_file_size=50M # 设置mysql的安装目录 basedir=C:\install\mysqlMS\mysql-5.7.20-winx64-master # 设置mysql数据库的数据的存放目录 datadir=C:\install\mysqlMS\mysql-5.7.20-winx64-master\data # 允许同时访问 MySQL 服务器的最大连接数 max_connections=200 # skip_grant_tables [mysql.server] default-character-set=utf8 [mysql_safe] default-character-set=utf8
② cmd进入主库的bin目录中,执行:mysqld --install master --defaults-file="C:\install\mysqlMS\mysql-5.7.20-winx64-master\my.ini"
其中的master为主库mysql的服务名称。
③ 输入 mysqld --initialize-insecure --user=mysql (执行完这条命令后,MySQL会自建一个data文件夹,并且建好默认数据库,登录的用户名为root,密码为空)
④ 启动服务:net start master
提示:
net stop master 停止master服务
sc delete master //删除windows服务(删除之前先停服务)
mysqld --remove //删除mysql服务
tasklist| findstr "mysql" 删除mysql进程
taskkill/f /t /im mysqld.exe 杀死mysql进程
⑤ 修改root用户密码
使用命令 mysql -uroot -p 登录master数据库(默认安装好的mysql的root用户是没有密码的)
登录之后,执行:
use mysql; update user set authentication_string=password('123456') where user='root'; flush privileges;
从库(slave)的安装与配置
① 进入主库mysql-5.7.20-winx64-slave目录中,在此目录中新建my.ini文件并添加一下配置。
#==============================客户端的参数 [client] #MySQL 客户端连接服务器端时使用的端口号,默认的端口号为 3306。如果需要更改端口号的话,可以直接在这里修改。 port = 3316 [mysql] #MySQL 客户端默认的字符集 default-character-set=utf8 #==============================服务端的参数 [mysqld] #====主从复制关键配置 start====== #主库和从库需要不一致 server_id=2 relay-log-index=slave-relay-bin.index relay-log=slave-relay-bin #====主从复制关键配置 end====== #服务器端默认的字符集 character-set-server=utf8 #MySQL 服务器的端口号 port = 3316 #SQL 模式的参数,通过这个参数可以设置检验 SQL 语句的严格程度 sql_mode="STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION" #创建数据表时,默认使用的存储引擎 default_storage_engine=innodb #表示缓存的大小,InnoDB 使用一个缓冲池类保存索引和原始数据。 innodb_buffer_pool_size=1000M #表示日志文件的大小 innodb_log_file_size=50M # 设置mysql的安装目录 basedir=C:\install\mysqlMS\mysql-5.7.20-winx64-slave # 设置mysql数据库的数据的存放目录 datadir=C:\install\mysqlMS\mysql-5.7.20-winx64-slave\data # 允许同时访问 MySQL 服务器的最大连接数 max_connections=200 # skip_grant_tables [mysql.server] default-character-set=utf8 [mysql_safe] default-character-set=utf8
② cmd进入主库的bin目录中,执行:mysqld --install slave --defaults-file="C:\install\mysqlMS\mysql-5.7.20-winx64-slave\my.ini"
其中的slave为从库mysql的服务名称。
③ 初始化:mysqld --initialize-insecure --user=mysql
④ 启动服务:net start slave
⑤ mysql -uroot -p 登录slave数据库,修改root用户密码
use mysql; update user set authentication_string=password('123456') where user='root'; flush privileges;
关联主库(master)与从库(slave)
启动master与slave,分别登录到master和slave的mysql中。
master的mysql 执行命令 show master status查看master的状态。
slave的mysql 执行命令 show slave status查看slave的状态
我们可以发现 ,master的状态下,生成了一个二进制的日志文件,而slave下是空的,所以我们现在就要把主库与从库关联起来。只需要让从库(slave)知道主库(master)的地址就可以了。
① 首先我们需要在主库(master)中创建一个用户用于与从库同步的用户名和密码(这里我创建一个test用户,密码为mysql),并给test用户授权,以用于主库操作从库。
相关命令:
create user test; grant replication slave on *.* to '从库用户名(test)'@'从库主机地址(127.0.0.1)'identified by '密码(123456)'; flush privileges;
② 切换到从库(slave)界面,把主库与从库联系起来。
执行命令:
change master to master_host='127.0.0.1',master_port=3310,master_user='test',master_password='123456',master_log_file='mysql-bin.000001',master_log_pos=988;
参数说明:
- master_host= 这里填你主库的IP。
- master_port=端口号
- master_user=’test’ 刚才我们创建的那个用户。
- master_log_file=’mysql-bin.000001’ 刚才在主库里面 show master status;得到的值了。
- master_log_pos=988
然后执行命令 start slave 开启主从同步。
执行命令查看 slave的状态:
show slave status \G;
出现下图红色部分则开启主从跟踪成功
验证主从同步
进入master和slave并查看他们的数据库。
在主库中创建一个数据库user,看一下从库有没有变化。
五、主从复制的三种模式
主从复制有三种模式,分别是基于SQL语句的复制(statement-based replication, SBR),基于行的复制(row-based replication, RBR),混合模式复制(mixed-based replication, MBR)。对应的,binlog的格式也有三种:STATEMENT,ROW,MIXED
mysql默认的主从复制模式是ROW。
模式划分
STATEMENT模式(SBR)
记录每一条SQL修改
- 每一条会修改数据的sql语句会记录到binlog中。优点是并不需要记录每一条 sql语句和每一行的数据变化,减少了binlog日志量,节约IO,提高性能。
- 缺点是在某些情况下会导致 master-slave中的数据不一致(如sleep()函数, last_insert_id(),以及user-defined functions(udf)等会出现问题)
ROW模式(RBR)
仅记录修改的内容,不记录具体的SQL
- 不记录每条sql语句的上下文信息,仅需记录哪条数据被修改了,修改成什么样了。而且不会出现某些特定情况下的存储过程、或function、或trigger的调用和触发无法被正确复制的问题。
- 缺点是会产生大量的日志,尤其是altertable的时候会让日志暴涨。
MIXED模式(MBR)
- 以上两种模式的混合使用,一般的复制使用STATEMENT模式保存binlog,对于STATEMENT模式无法复制的操作使用ROW模式保存binlog,MySQL会根据执行的SQL语句选择日志保存方式。
建议使用MIXED模式。
如何更改主从复制的模式
查看当前模式
show global variables like 'binlog%';
进入到master服务器的my.cnf文件中,增加一行,设置主从复制模式
重启master服务器的数据库 。