MySql5.7主从同步配置(gtid模式)
mysql数据库主从同步配置是开发中比较常见的需求,在MySql里,主从同步主要有两种,bin-log和gtid。下面就主要总结一下使用gtid模式配置主从同步的过程。
1. 概念
全局事务标识:global transaction identifiers;
GTID是一个事务一一对应,并且全局唯一ID;
一个GTID在一个服务器上只执行一次,避免重复执行导致数据混乱或者主从不一致;
GTID用来代替传统复制方法,不再使用MASTER_LOG_FILE+MASTER_LOG_POS开启复制。而是使用MASTER_AUTO_POSTION=1的方式开始复制;
MySQL-5.6.5开始支持的,MySQL-5.6.10后开始完善;
在传统的slave端,binlog是不用开启的,但是在GTID中slave端的binlog是必须开启的,目的是记录执行过的GTID(强制)。
GTID = source_id:transaction_id
source_id: 用于鉴别原服务器,即mysql服务器唯一的的server_uuid,由于GTID会传递到slave,所以也可以理解为源ID。
transaction_id: 为当前服务器上已提交事务的一个序列号,通常从1开始自增长的序列,一个数值对应一个事务。
示例:3E11FA47-71CA-11E1-9E33-C80AA9429562:23
前面的一串为服务器的server_uuid,即3E11FA47-71CA-11E1-9E33-C80AA9429562,后面的23为transaction_id
工作原理:
master更新数据时,会在事务前产生GTID,一同记录到binlog日志中。
slave端的i/o 线程将变更的binlog,写入到本地的relay log中。
sql线程从relay log中获取GTID,然后对比slave端的binlog是否有记录。
如果有记录,说明该GTID的事务已经执行,slave会忽略。
如果没有记录,slave就会从relay log中执行该GTID的事务,并记录到binlog。
在解析过程中会判断是否有主键,如果没有就用二级索引,如果没有就用全部扫描。
优点:
更简单的实现failover,不用以前那样在需要找log_file和log_pos;
更简单的搭建主从复制;
比传统的复制更加安全;
GTID是连续的没有空洞的,保证数据的一致性,零丢失。
GTID限制:
不能CREATE TABLE … SELECT 复制表结构,复制数据
使用GTID复制模式时,不支持create temporary table 和 drop temporary table。但是在autocommit=1的情况下可以创建临时表,Master端创建临时表不产生GTID信息,所以不会同步到slave,但是在删除临时表的时候会产生GTID会导致,主从中断.
在一个事务中或语句中同时更新事务和非事务表,比如在一个事务中更新 innodb表和myisam表。要更新的所有非事务表是临时表除外
2. 配置过程
1. 安装mysql5.7
这里需要说明gtid在mysql5.6之后才有,但是mysql5.6上gtid默认是不可用的,而在mysql5.7上是默认可用的。
2. 数据手工同步
目的:自动同步前,保证两个数据库的数据相同。
1)主数据库导出要备份的数据
2)从数据库导入主数据库的备份数据
3. 修改主数据库配置文件
sudo vi /etc/my.cnf,添加以下配置,重启mysql。
[mysqld]
#表名存储在磁盘是小写的,但是比较的时候是不区分大小写
lower_case_table_names=1
###主从同步-主###
#设置成IP的最后一位即可
server_id=0001
#打开gtid模式
gtid_mode=on
#开启gtid的一些安全限制,开启后执行限制语法将会报错
enforce_gtid_consistency=true
#允许下端接入slave,A->B->C,其中B是A的从服务器,同时B又是C的主服务器,那么B服务器除了需要打开log-bin之外,还需要打开log-slave-updates选项
log_slave_updates=on
#二进制日志文件名称
log_bin=mysql-bin
#mysql复制模式,三种:SBR(基于sql语句复制),RBR(基于行的复制),MBR(混合模式复制,可靠性更高)
binlog_format=MIXED
#二进制日志自动删除的天数
expire_logs_days=7
#就是启动slave从库的时候,复制线程不会随着mysql进程启动而开启,进而导致破坏了从库.如果你需要启动就需要手动执行start slave命令
skip_slave_start=ON
#主从复制中的跳过错误处理参数
slave_skip_errors=1062,1053
#中继日志.
relay_log=relay_log
master_info_repository=TABLE
relay_log_info_repository=TABLE
#主库
# 需要同步的数据库,如果复制多个数据库,重复设置这个选项即可
binlog-do-db=mt
# 不需要同步的数据库
binlog-ignore-db = mysql
binlog-ignore-db = information_schema
binlog-ignore-db = performance_schema
binlog-ignore-db = sys
重启主库服务器
4. 修改从数据库配置文件
sudo vi /etc/my.cnf, 添加以下配置,重启mysql。
[mysqld]
#表名存储在磁盘是小写的,但是比较的时候是不区分大小写
lower_case_table_names=1
###主从同步-从###
#设置成IP的最后一位即可
server_id=0002
#打开gtid模式
gtid_mode=on
#开启gtid的一些安全限制,开启后执行限制语法将会报错
enforce_gtid_consistency=true
#允许下端接入slave,A->B->C,其中B是A的从服务器,同时B又是C的主服务器,那么B服务器除了需要打开log-bin之外,还需要打开log-slave-updates选项
log_slave_updates=on
#二进制日志文件名称
log_bin=mysql-bin
#mysql复制模式,三种:SBR(基于sql语句复制),RBR(基于行的复制),MBR(混合模式复制,可靠性更高)
binlog_format=MIXED
#二进制日志自动删除的天数
expire_logs_days=7
#就是启动slave从库的时候,复制线程不会随着mysql进程启动而开启,进而导致破坏了从库.如果你需要启动就需要手动执行start slave命令
skip_slave_start=ON
#主从复制中的跳过错误处理参数
slave_skip_errors=1062,1053
#中继日志.
relay_log=relay_log
master_info_repository=TABLE
relay_log_info_repository=TABLE
#从库
# 需要同步的数据库,如果复制多个数据库,重复设置这个选项即可
replicate-do-db=mt
# 不需要同步的数据库
replicate-ignore-db = mysql
replicate-ignore-db = information_schema
replicate-ignore-db = performance_schema
replicate-ignore-db = sys
重启从库服务器
5. 主库授权复制用户
set global validate_password_policy=0;
set global validate_password_length=1;
grant replication slave on *.* to 'root'@'%' identified by 'trnuser123';
flush privileges;
#显示主服务器的状态信息,并且找到File 和 Position 的值记录下来;
show master status;
6. 同步数据
1)锁住主库表flush tables with read lock;
2)备份主库中的所有数据到从库中,保持当前主库和从库数据一致
3)解锁主库表unlock tables;
7. 从数据库开启同步。
#重启从数据库,设置登录主数据库的账号和密码等信息,然后启动slave
change master to master_host='${主库的实际IP地址}',master_user='root',master_password='trnuser123', master_auto_position=1;
start slave;
show slave status;
配置完之后,通过查看slave的状态,可以看是否配置成功。同时可以在主库进行一些操作,提交一些事务(insert,update),之后数据就会自动同步到从库。
8. 验证主从同步成功与否
1)查询从服务器
在从服务器上输入命令:
show slave status;
如果输出Slave_IO_Running 和 Slave_SQL_Running都是Yes,就说明配置成功了
如果其中一项不为 Yes,查看 Last_IO_Errno 错误码和错误信息,或者查看 MySQL 日志信息并查找对应问题
2)添加一个表和数据后,验证一下是否同步到从服务器。
给已经运行的GTID的master端添加一个新的slave
在mysqldump备份的时候指定--master-data=2(来保存binlog的文件号和位置的命令)。一个脚本供参考:
DB_USER="root"
DB_PASS="123456!"
DB_HOST="localhost"
DB_NAME="newerp"
BIN_DIR="/usr/bin"
BCK_DIR="/data"
DATE=date +%Y-%m-%d_%H-%M-%S
mysqldump -u$DB_USER -p$DB_PASS -h$DB_HOST $DB_NAME > $BCK_DIR/$DB_NAME.$DATE.sql
#例如: mysqldump -u"root" -p"123456" -h"127.0.0.1" "mt" > SQL_BACK.sql
使用mysqldump的命令在dump文件里可以看到下面两个信息:
SET @@SESSION.SQL_LOG_BIN=0;
SET @@GLOBAL.GTID_PURGED='7800a22c-95ae-11e4-983d-080027de205a:1-8';
将备份还原到slave后,使用change master to命令挂载master端。
mysql -uroot -p < /root/sql_back.sql
同步操作SQL语句
查看主库状态
show processlist;
show master status;
查看从库状态
show processlist;
show slave status;
停止同步
在主从服务器都执行下面指令
STOP SLAVE;
STOP SLAVE io_thread;
RESET SLAVE all;
RESET MASTER;
————————————————
参考:http://www.taodudu.cc/news/show-5947880.html?action=onClick
版权声明:本文为CSDN博主「爱游泳的老白」的原创文章,遵循CC 4.0 BY-SA版权协议,转载请附上原文出处链接及本声明。
原文链接:https://blog.csdn.net/wjw465150/article/details/127043721