配置mysql主从复制(一主一从)
MySQL版本:5.7,Linux版本:CentOS 7.4
MySQL主从复制简介
MySQL主从复制的目的是实现数据库冗余备份,将master数据库的数据定时同步到slave库中,一旦master数据库宕机,可以将Web应用数据库配置快速切换到slave数据库,确保Web应用有较高的可用性。
MySQL主从同步是一个异步复制的过程,要实现复制,受限需要在master上开启bin-log日志功能,bin-log日志用于记录在master库执行的增删改更新操作的sql语句,整个过程需要开启3个线程,分别是master开启I/O线程,slave开启I/O线程和SQL线程。
主从复制大致流程
- slave上执行slave start,slave I/O线程会通过在master创建的授权用户连接到master,并请求master从指定的文件和位置发送bin-log日志内容。
- master接收到slave的IO线程请求后,master的IO线程根据slave发送的指定bin-log日志position点之后的内容,然后返回给slave的IO线程。
- 返回的信息中除了bin-log日志内容外,还有master最新的bin-log文件名以及在bin-log中的下一个指定更新position点。
- slave的IO线程接收到信息后,将接收到的日志内容依次添加到slave端的relay-log文件的末端,并将读取到的master端的bin-log文件名和position点记录到master.info文件中,以便在下一次读取的时候能告知master从相应的bin-log文件名及最后一个position点开始发起请求
- slave SQL线程检测到relay-log中内容有更新,会立刻解析relay-log的内容,将解析后的SQL语句在slave中执行,执行成功后主库和从库数据一致。
准备
- 主节点:192.168.66.11
- 从节点:192.168.66.10
- 待同步的数据库:ipcc
配置主节点
-
vim /etc/my.cnf
-
在
[mysqld]
字段中添加:
log-bin=master-bin
binlog-format=ROW
server-id=1
- 重启mysqld:
systemctl restart mysqld
- 登入mysql,创建用于同步的账户:
create user ‘tongbu‘@‘%‘ identified by ‘123456‘;
- 授权:
grant replication slave on *.* to ‘tongbu‘@‘%‘ identified by ‘123456‘;
- 查看master状态:
show master status;
- 注意file字段和position字段
配置从节点
-
vim /etc/my.cnf
-
在[mysqld]字段中添加:
server-id=2
log-bin=mysql-bin
binlog-format=ROW
- 重启mysql
- 登入mysql,输入
change master to master_host=‘192.168.66.11‘,master_user=‘tongbu‘,master_password=‘123456‘,master_log_file=‘master-bin.000001‘,master_log_pos=154;
start slave;
-
show slave status\G
- 注意看 Slave_IO_Running和Slave_SQL_Running是否为yes
测试
在主库创建一个数据库:create database test123;
在数据库中新建一个表:create table t0(id int,name varchar(20));
查看从库有没有新建的表
只同步部分库或表
方式一:限制master端
在/etc/my.cnf中添加:binlog-do-db=ipcc
(多个数据库用逗号隔开,不加这一行默认同步所有库)
方式二:限制slave端
在/etc/my.cnf中添加:replicate-do-db=ipcc
指定需要同步的表:replicate-do-table=ipcc.cdr
解决主从复制问题
如果发生网络异常导致主从同步错误,或者在已有数据基础上搭建主从复制,需要解决数据不一致问题。
如果主从数据差别不大时的不同步问题
- master端执行:
flush tables with read lock;
,将数据库设置为全局读锁。 - slave端:1、停止IO及SQL线程,
stop slave;
2、将同步错误的SQL跳过一次,set global sql_slave_skip_counter=1;
3、启动slave,start slave;
- master端执行:
unlock tables;
解锁。
如果主从数据差别很大时的不同步问题
- master端执行:
flush tables with read lock;
,将数据库设置为全局读锁。 - 使用mysqldump、mysqlpump、xtrabackup等工具对master数据库进行完整备份
- 将完整数据导入到从库
- 重新配置主从关系
- master端执行:
unlock tables;
解锁。
主键冲突,错误代码1062
情况:从库上执行show slave status\G
,报错last_error:1062
,sql线程已停止工作。
原因:从库上执行了写操作,然后在主库上执行了相同的SQL语句,主键冲突,主从复制状态就会报错。
解决:
- 利用perconna-toolkit工具中的py-slave-restart命令在从库跳过错误(因为主从库有相同的数据)。
主库更新数据,从库找不到而报错,错误代码1032
在从库执行delete删除操作,再在主库执行更新操作,由于从库已经没有该数据,导致主从数据不一致。
解决方法:在从库执行show slave status
,根据错误信息所知道的binlog文件和position号,在主库上通过mysqlbinlog命令查找在主库执行的哪条SQL语句导致的主从报错。把从库上丢失的这条数据补上,然后执行跳过错误。如果从库丢失了很多数据,需要考虑重新配置主从环境。