Mysql主从架构
业务发展到一定阶段,单节点数据库无法满足服务需求,这个时候就需要mysql集群技术,而最现在很多公司最常用的就是主从结构,在主从结构中,所有的写入和更新操作都在主库上进行,所有的查询操作都在从库上进行,从库可以有一台或多台,此架构可提高写入速度,尤其明细的提高了读取速度,因为当查询性能很慢时,可通过增加从库的数量来提升读取速度。
主从复制流程
- 主库需开启binlog日志,记录所有修改操作到binlog日志文件中
- 从库的IO进程读取主库的binlog日志并存到该从库的中继日志Relay log中,Relay log日志格式和binlog是完全相同的,可以使用mysqlbinlog来读取Relay log的内容。此时传输过程是这样的,mysql会在从库上开启一个IO线程,从库的io线程会跟主库建立一个连接, 然后在主库中会开启一个binlog dump线程, 从库上io线程通过binlog dump线程来读取主库上的二进制日志的事件,如果从库的io线程追赶上了主库,从库的io线程会进入sleep的状态,直到主库发送信号通知其有新的事件产生时,才会被唤醒。
- 从库的会开启一个sql线程,定时读取Relay log日志,如果发现有更新会立即把更新的内容在本机的数据库上面执行一遍。
配置主从复制步骤
一、配置主从复制参数
二、在master上创建用于复制的账号
三、对master上的数据进行全备
四、对slave上的数据进行初始化
五、启动复制链路
注意:主从数据库需要采用相同的mysql版本,以免因版本差异出现问题
注意:某些参数配置后需要重启服务器才能生效,建议上线之前都把主从复制参数配置好
注意:如果服务要一直提供服务,不能重启服务,配置参数后,还需要使用set global 进行设置
一、配置主从复制参数
配置master服务
编辑/etc/my.cnf文件,修改和添加配置,然后重启服务
注意:如果是mysql5.7及以上版本,还需查看auto.cnf下server-uuid的是否一样,如果一样主从复制会出现问题,需删除掉server-uuid,然后重启服务重新生成server-uuid,auto.cnf存储的路径可能不同,我的路径在/var/lib/mysql/
- 编辑/etc/my.cnf文件
vim /etc/my.cnf
- 修改和添加配置
#每台服务的server_id都不能一样,建议server_id设置成ip的最后一段
server_id = 118
#开启binlog,并存到该目录,需配置属主和属组
log_bin = /var/lib/mysql/binlogs/binlog
#binlog日志文件大小的最大值,当达到设定大小后,会使用新生一个binlog日志
max_binlog_size = 1000M
#以row格式来记录binlog,仅保存记录被修改细节,不记录 SQL 语句上下文相关信息,能非常清晰的记录下每行数据的修改细节,
#不需要记录上下文相关信息,因此不会发生某些特定情况下的 procedure、function、及 trigger 的调用触发无法被正确复制的问题,
#任何情况都可以被复制,且能加快从库重放日志的效率,保证从库数据的一致性。
binlog_format = row
#设置过期时间,超过过期时间会被自动删除,但是有触发条件的,触发条件时:1.binlog大小超过max_binlog_size,
#2.手动执行flush logs,3.重新启动时(MySQL会新生产一个文件用于记录binlog)
expire_logs_days = 7
#sync_binlog=1,表示每次事务提交,MySQL都会把binlog刷下去,是最安全最能保证一致性,但是性能损耗最大
sync_binlog = 1
- 创建配置中的文件夹,并配置属主和属组
mkdir -p /var/lib/mysql/binlogs/
chown -R mysql:mysql /var/lib/mysql/binlogs/
- 重启master服务
service mysqld restart
配置slave服务
编辑/etc/my.cnf文件,修改和添加配置,然后重启服务
注意:如果是mysql5.7及以上版本,还需查看auto.cnf下server-uuid的是否一样,如果一样主从复制会出现问题,需删除掉server-uuid,然后重启服务重新生成server-uuid,auto.cnf存储的路径可能不同,我的路径在/var/lib/mysql/
- 编辑/etc/my.cnf文件
vim /etc/my.cnf
- 修改和添加配置
#每台服务的server_id都不能一样,建议server_id设置成ip的最后一段
server_id = 119
#开启binlog,并存到该目录,需配置属主和属组
log_bin = /var/lib/mysql/binlogs/binlog
#把中继日志relay log存到该目录,需配置属主和属组
relay_log = /var/lib/mysql/relaylogs/relaylog
#使所有的没有server权限的用户不能进行写操作,不论这个用户是否拥有写权限,防止在slave上写数据造成主从数据不一致问题,但是这个配置不能限制拥有sql权限的账号,这时开启super_read_only限制
read_only = on
#在slave重启时,跳过开启复制链路,即启动复制链路,默认是启动的,这时如果有问题,会造成主从复制链路中断,所以需手动启动复制链路
skip_slave_start = on
#值设为on时,从库binlog才会记录主库同步的操作日志
log_slave_updates=on
#master_info_repository和relay_log_info_repository参数表示,把主从复制的信息存到innodb表中,默认是存在文件系统中的,
#一旦slave宕机,可能会出现文件中的信息和实际的信息不一致的情况,存在表中就可以利用innodb的恢复机制(redo log)保证一致性
master_info_repository = TABLE
relay_log_info_repository = TABLE
- 创建配置中的文件夹,并配置属主和属组
mkdir -p /var/lib/mysql/binlogs
mkdir -p /var/lib/mysql/relaylogs
chown -R mysql:mysql /var/lib/mysql/binlogs/
chown -R mysql:mysql /var/lib/mysql/relaylogs/
- 重启slave服务
service mysqld restart
二、在master上创建用于复制的账号
用于IO进程连接mster服务获取binlog日志
#创建用户,%表示在该网段下都能访问
create user 'repluser'@'192.168.3.%' identified by '密码';
#赋权,只赋予replication slave权限就行
grant replication slave on *.* to 'repluser'@'192.168.3.%';
三、对master上的数据进行全备
- 参数解释:
-u:为指定用户名
-p:为指定密码
--single-transaction:Innodb下使用,为在备份之前启动一个事务,来保证数据的一致性,
不过要确保没有其他DDL操作在执行,因为Innodb隔离级别无法对ddl进行隔离
--master-data=1:生成的备份文件中change master不会被注释,记录了binlog文件和日志节点的位置
--routines:备份数据库中存在的存储过程
--triggers:备份数据库中所存在的触发器
--events:备份数据库中的调度事件
- 全备脚本:
mysqldump -uroot -p --master-data=1 --single-transaction --routines --triggers --events --all-databases > allData.sql
四、对slave上的数据进行初始化
- 把master上全量备份的数据复制到slave本地
scp -r root@192.168.3.118:/usr/local/backupData/ /usr/local/backupData/
- 初始化slave
把master的全备数据恢复到slave
mysql -uroot -p < /usr/local/backupData/allData.sql
- 进入mysql,查看是否初始化成功
mysql -uroot -p
show databases;
五、启动复制链路
- 查看change master上的binlog文件和日志节点的位置
more allData.sql
- 配置复制链路
需要先进入slave的mysql命令行,然后执行change master命令,最后启动复制链路
#进入slave的mysql服务
mysql -uroot -p
#master_host为从那台服务复制,即主库的ip
#master_log_file和master_log_pos为告诉slave服务从哪个binlog文件的哪个日志点开始复制
change master to
master_host = '192.168.3.118',
master_user = 'repluser',
master_password = 'Mysql896881831+-*',
master_log_file = 'binlog.000001',
master_log_pos = 154;
#启动复制链路
start slave;
#查看复制链路是否启动成功,IO线程为Yes和SQL线程为Yes即为启动成功
show slave status \G
- 可能出现的问题:IO线程和SQL线程不全是Yes,即为不成功
1.可能是防火墙问题,
可使用firewall-cmd --zone=public --list-ports
查看端口是否开通,
如果未开通防火墙,使用此命令进行开通firewall-cmd --zone=public --add-port=3306/tcp --permanent
,
最后重载防火墙firewall-cmd --reload
2.复制用户创建错误
在创建用户时未指定可访问的网络,只指定了localhost能访问,可通过select host,user from msyql.user
查询
3.配置change master to 时指定的binlog文件和日志节点偏移量不对
六、测试
在主库上插入一条数据
在从库上查询,看是否进行了同步。如下成功查到了主库插入的数据,说明同步成功。