搭建MySQL5.6主从复制

本文档详述了MySQL 5.6版本的主从复制配置过程,包括设置主从复制的方式、主节点和从节点的配置、日志类型、数据同步用户创建、数据初始化以及主从同步状态检查。主从复制通过二进制日志实现,确保数据一致性。
摘要由CSDN通过智能技术生成

概要:

MySQL版本:5.6

主节点ip:192.168.100.128

从节点ip:192.168.100.129

主从复制方式:基于日志(binlog)

MySQL主从复制(也称A/B复制)的原理

(1) Master将数据改变记录到二进制日志(binary log)中,也就是配置文件log-bin指定的文件,这些记录叫做二进制日志事件(binary log events)

(2) Slave通过I/O线程读取Master中的binary log events并写入到它的中继日志(relay log)

(3) Slave重做中继日志中的事件,把中继日志中的事件信息一条一条的在本地执行一次,完成数据在本地的存储,从而实现将改变反映到它自己的数据(数据重放)

mysql单向一主一从配置:

master:192.168.100.128  slave:192.168.100.129

Master端的配置:

1、修改Master的配置文件/etc/my.cnf

[root@localhost ~]# vi /etc/my.cnf


## 在 [mysqld] 中增加以下配置项
## 设置server_id,一般设置为IP
server_id=128
## 复制过滤:需要备份的数据库,输出binlog
#binlog-do-db=mydb
## 复制过滤:不需要备份的数据库,不输出(mysql库一般不同步)
binlog-ignore-db=mysql
## 开启二进制日志功能,可以随便取,最好有含义
log-bin=edu-mysql-bin
## 为每个session 分配的内存,在事务过程中用来存储二进制日志的缓存
binlog_cache_size=1M
## 主从复制的格式(mixed,statement,row,默认格式是statement)
binlog_format=mixed
## 二进制日志自动删除/过期的天数。默认值为0,表示不自动删除。
expire_logs_days=7
## 跳过主从复制中遇到的所有错误或指定类型的错误,避免slave端复制中断。
## 如:1062错误是指一些主键重复,1032错误是因为主从数据库数据不一致
slave_skip_errors=1062
## 如果需要同步函数或者存储过程
log_bin_trust_function_creators=true

2、MySQL对于二进制日志 (binlog)的复制类型
(1) 基于语句的复制:在Master上执行的SQL语句,在Slave上执行同样的语句。MySQL默认采用基于语句的复制,效率比较高。一旦发现没法精确复制时,会自动选着基于行的复制。   
(2) 基于行的复制:把改变的内容复制到Slave,而不是把命令在Slave上执行一遍。从MySQL5.0开始支持。
(3) 混合类型的复制:默认采用基于语句的复制,一旦发现基于语句的无法精确的复制时,就会采用基于行的复制。

3、启动/重启Master数据库服务,登录数据库,创建数据同步用户,并授予相应的权限

[root@localhost ~]# service mysql restart

##创建数据同步用户repl,并授予相应的权限


mysql> grant replication slave, replication client on *.* to 'repl'@'192.168.100.129' identified by '123456';
## 刷新授权表信息
mysql> flush privileges;、
## 查看MySQL现在有哪些用户及对应的IP权限


mysql> select user,host from mysql.user;

## 查看position号,记下file名称和position号(从机上需要用到这个position号和现在的日志文件)


mysql> show master status;

Master重启后会修改mysql-bin文件名(序号加1)
 
4、创建mydb库、表,并写入一定量的数据,用于模拟现有的业务系统数据库


create database if not exists mydb default charset utf8 collate utf8_general_ci;
use mydb;
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
  `Id` int(11) NOT NULL AUTO_INCREMENT,
  `user_ame` varchar(255) NOT NULL DEFAULT '' COMMENT '用户名',
  `pwd` varchar(255) NOT NULL DEFAULT '' COMMENT '密码',
  PRIMARY KEY (`Id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 COMMENT='用户信息表';
INSERT INTO `user` (user_name, pwd) VALUES ('yzr','123456'),('test','123456'),('you','654321');
 
5、为保证Master和Slave的数据一致,我们采用主备份,从还原来实现初始数据一致
## 先临时锁表


mysql> flush tables with read lock;
## 这里我们实行全库备份,在实际中,我们可能只同步某一个库,那也可以只备份一个库

[root@localhost ~]# mysqldump -p3306 -uroot -p --add-drop-table mydb > /tmp/master-mydb.sql;

## 解锁表
mysql> unlock tables;

## 注意:实际生产环境中大数据量(超2G数据)的备份,建议不要使用mysqldump进行比分,因为会非常慢。此时推荐使用 XtraBackup 进行备份。
 
将Master上备份的数据远程传送到Slave上,以用于Slave配置时恢复数据

[root@localhost ~]# scp /tmp/master-mydb.sql root@192.168.100.129:/tmp/

Slave端配置:

1、修改/etc/my.cnf文件:

[root@localhost ~]# vi /etc/my.cnf


## 在 [mysqld] 中增加以下配置项
## 设置server_id,一般设置为IP
server_id=129
## 复制过滤:需要备份的数据库,输出binlog
#binlog-do-db=mydb
##复制过滤:不需要备份的数据库,不输出(mysql库一般不同步)
binlog-ignore-db=mysql
## 开启二进制日志,以备Slave作为其它Slave的Master时使用
log-bin=edu-mysql-slave1-bin
## 为每个session 分配的内存,在事务过程中用来存储二进制日志的缓存
binlog_cache_size = 1M
## 主从复制的格式(mixed,statement,row,默认格式是statement)
binlog_format=mixed
## 二进制日志自动删除/过期的天数。默认值为0,表示不自动删除。
expire_logs_days=7
## 跳过主从复制中遇到的所有错误或指定类型的错误,避免slave端复制中断。
## 如:1062错误是指一些主键重复,1032错误是因为主从数据库数据不一致
slave_skip_errors=1062
## relay_log配置中继日志
relay_log=edu-mysql-relay-bin
## log_slave_updates表示slave将复制事件写进自己的二进制日志
log_slave_updates=1
## 防止改变数据(除了特殊的线程)
#read_only=1

2、保存后重启MySQL服务,还原备份数据

[root@edu-mysql-02 ~]# service mysql restart

Slave上创建相同库:
create database if not exists mydb default charset utf8 collate utf8_general_ci;
use mydb;
导入数据

[root@localhost ~]# mysql -uroot -p mydb < /tmp/master-mydb.sql

3、登录Slave数据库,添加相关参数(Master的IP、端口、同步用户、密码、position号、读取哪个日志文件)

在master那边,执行:
flush logs;
show master status;记下File, Position。
在slave端,执行:
stop slave;
mysql> change master to master_host='192.168.100.128', master_user='repl', master_password='123456', master_port=3306, master_log_file='edu-mysql-bin.000001', master_log_pos=429, master_connect_retry=30;

此处的master_log_file的值为上面查询的file值,master_log_pos为Position值。
上面执行的命令的解释:
master_host='192.168.100.128'                  ## Master的IP地址
master_user='repl'                             ## 用于同步数据的用户(在Master中授权的用户)
master_password='123456'     ## 同步数据用户的密码
master_port=3306                              ## Master数据库服务的端口
master_log_file='edu-mysql-bin.000001'        ##指定Slave从哪个日志文件开始读复制数据(可在Master上使用show master status查看到日志文件名)
master_log_pos=429                         ## 从哪个POSITION号开始读
master_connect_retry=30  ##当重新建立主从连接时,如果连接建立失败,间隔多久后重试。单位为秒,默认设置为60秒,同步延迟调优参数。
 
## 查看主从同步状态
mysql> show slave status\G;
可看到Slave_IO_State为空, Slave_IO_Running和Slave_SQL_Running是No,表明Slave还没有开始复制过程。

##保证各个db的uuid不一样
找到每个实例下data文件夹下的auto.cnf文件,修改里面的uuid值,保证各个db的uuid不一样,重启db即可

## 开启主从同步
mysql> start slave;
## 再查看主从同步状态
mysql> show slave status\G;


主要看以下两个参数,这两个参数如果是Yes就表示主从同步正常
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
由截图中的主从同步状态信息可以看出,我们配置的主从同步是正常的。
 
可查看master和slave上线程的状态。在master上,可以看到slave的I/O线程创建的连接:
Master : mysql> show processlist\G;
1.row 为处理slave的I/O线程的连接。
 2.row 为处理MySQL客户端连接线程。
 3.row 为处理本地命令行的线程。
 
Slave : mysql> show processlist\G;
1.row 为I/O线程状态。
2.row 为SQL线程状态。
3.row 为处理本地命令行的线程。
 
主从数据复制同步测试
(1) 在Master中的mydb库上变更数据的同步测试;
mysql> INSERT INTO `user` VALUES (4,'同步测试1','123456'),(5,'同步测试2','123456');
Master中添加完之后,登录Slave中查看数据是否已同步。
 
(2) 在Master上新建一个youdb库
mysql> create database if not exists youdb default charset utf8 collate utf8_general_ci;
在Slave中查看数据库
mysql> show databases;

重置主从同步设置

如果遇到同步出错,可在Slave上重置主从复制设置:
(1) mysql> reset slave;
(2) mysql> change master to master_host='192.168.100.128',
master_user='repl',
master_password='123456',
master_port=3306,
master_log_file='edu-mysql-bin.00000x',  
master_log_pos=xx,
master_connect_retry=30;
(master_log_file和master_log_pos要在Master中用show master status 命令查看)
 
注意:如果在Slave没做只读控制的情况下,千万不要在Slave中手动插入数据,那样数据就会不一致,主从就会断开,就需要重新配置了。

主从配置需要注意的点

(1)主从服务器操作系统版本和位数一致;

(2) MasterSlave数据库的版本要一致

(3) MasterSlave数据库中的数据要一致

(4) Master开启二进制日志,MasterSlaveserver_id在局域网内必须唯一

  • 1
    点赞
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值