mysql主从复制,从0到1搭建配置
一、mysql主从复制概念
主从复制(也称AB复制)允许将来自一个MySQL数据库服务器(主服务器)的数据复制到一个或多个MySQL数据库服务器(从服务器)。
补充:
双主复制,也就是互做主从复制,每个master既是master,又是另外一台服务器的slave。这样任何一方所做的变更,都会通过复制应用到另外一方的数据库中。
二、三种复制模式
语句复制模式(Statement Based Replication,SBR):基于实际执行的SQL语句的复制模式,该复制模式简单实现了数据同步,但在执行跨库更新等SQL语句时容易出现主从库的数据不一致问题。
记录复制模式(Row Based Replication,RBR):基于修改的行的复制模式。不再简单记录SQL语句的执行顺序,而是逐行记录存储引擎的数据是如何变更的,主从库的数据一致性保障得到大幅度提升,是当前各生产环境常用的一种复制模式,该方式更安全。
混合复制模式(Mixed Based Replication):简称MBR,根据SQL语句的不同来判断是否需要使用row格式,当出现可能造成主从库数据不一致的SQL语句时(例如:用户自定义函数、跨库SQL语句等),binlog自动转为row格式记录。
三、主从复制搭建
1、mysql5.7安装
在实际生产环境中,如果使用的内网,则无法上网下载资源。则需要提前准备好mysql5.7的安装包。
准备好主机,从机两台服务器
服务器1:centos7 64位(one):192.168.60.130 主机 master
服务器2:centos7 64位(two):192.168.60.131 从机 slave
然后分别安装在需要搭建主从复制的主机,从机上面,并且使用ps -ef|grep mysql查询mysql进程,如图,则表示安装成功。
2、分别配置mysql配置文件
先在master节点中编辑mysql的配置文件 my.cnf,新增以下内容:
[mysqld]
## 同一局域网内注意要唯一
server-id = 1
## 开启二进制日志功能,可以随意取(关键)
log-bin=mysql-bin
## 需要备份的数据库
binlog-do-db = testdata
binlog-do-db = student
binlog-do-db = xxl_job
binlog-do-db = solver
## 复制过滤:不需要备份的数据库,不输出(mysql库一般不同步)
binlog-ignore-db = mysql
## 主从复制的格式(mixed,statement,row,默认格式是statement)
binlog_format=mixed
然后再slave节点中编辑mysql的配置文件my.cnf,新增以下内容:
[mysqld]
## 同一局域网内注意要唯一
server-id = 2
## 开启二进制日志功能,可以随意取(关键)
log-bin=mysql-bin
## 需要备份的数据库
binlog-do-db = testdata
binlog-do-db = student
binlog-do-db = xxl_job
binlog-do-db = solver
## 复制过滤:不需要备份的数据库,不输出(mysql库一般不同步)
binlog-ignore-db = mysql
## 主从复制的格式(mixed,statement,row,默认格式是statement)
binlog_format=mixed
补充:
在mysql主主同步时(两台机器互相同步数据),需要设置
auto_increment_increment = 2
auto_increment_offset = 1 或 2
这样才能避免两台服务器同时做更新时自增字段的值之间的冲突。
auto_increment_increment是自增的步长,value为1代表每次增加 1,auto_increment_offset是自增的偏移量,也就是自增开始,value为1代表从1开始增加。
3、重启mysql数据库
主机和从机编辑完配置文件之后,需要进行重启
4、在master服务器授权slave服务器可以同步的权限(master节点执行)
4.1进入mysql控制台
命令:mysql -u root -p
4.2授予slave可以同步master的权限
命令:grant replication slave,replication client on *.* to 'root'@'slave服务的ip' identified by 'slave服务器的密码';
4.3刷新权限配置
命令:flush privileges;
4.4查看mysql现在有哪些用户及其对应的ip权限
命令:select user,host from mysql.user;
这一条为接下来进行主从复制的用户与对应slave ip
4.5查询master服务的binlog文件名和位置(master节点执行)
命令:show master status;
参数解释:
参数名称 | 解释 |
---|---|
File(重要) | 由SQL线程执行的包含多数近期事件的主服务器二进制日志文件的名称 |
Position(重要) | 在当前的主服务器二进制日志中,I/O线程已经读取的位置 |
Binlog_Do-DB | 需要做同步的数据库 |
Binlog-Ignore_DB | 不需要做同步的数据库 |
Executed_gtid_Set | 从库自身已经执行的事务编号 |
5、slave进行关联master节点(slave节点执行)
命令:change master to master_host='192.168.60.130', master_user='root', master_password='root', master_port=3306,master_log_file='mysql-bin.000002',master_log_pos=4093;
master_host: master的ip地址
master_user: 步骤4.2授予权限的用户
master_password: 步骤4.2授予权限的用户所对应的密码
master_port: master中mysql开放的端口
master_log_file: 步骤4.5查询出来的file
master_log_pos: 步骤4.5查询出来的Position
6、开启主从复制(slave执行)
命令:start slave;
7、查询同步状态
命令:show slave status\G;
Slave_IO_Running 和 Slave_SQL_Running 都为yes,则表示主从同步成功。
show slave status\G; 命令主要参数详解:
参数 | 解释 |
---|---|
Slave_IO_State | 线程准备就绪,等待主服务器的事件 |
Master_Host | 主服务的ip地址 |
Master_User | 被用于链接主服务器的当前用户 |
Master_Port | 当前主服务器端口 |
Connect_Retry | Master-connect-retry选项的当前值 |
Master_Log_File | I/O线程当前正在读取的主服务器二进制日志文件名称 |
Read_Master_Log_Pos | 在当前的主服务器二进制日志中,I/O线程已经读取的位置 |
Relay_Log_File | SQL线程当前正在读取和执行的中继日志文件的名称 |
Relay_Log_Pos | 在当前的中继日志中,SQL线程已读取和执行的位置 |
Slave_IO_Running | I/O线程是否被启动并成功地连接到主服务器上 |
Slave_SQL_Running | SQL线程是否被启动 |
Last_xx_Errno,Last_xx_Erro | 被多数最近被执行的查询返回的错误数量和错误消息 |
四、双主复制
双主复制:则是将原来的主机当做从机,从机当做主机,从第4步到第7步再执行一次