GTID复制实验

基于GTID的复制环境搭建

基础环境
操作系统: CentOS 7.2
MySQL版本: mysql-5.7.14-linux-glibc2.5-x86_64
主库IP: 172.16.123.171
从库IP: 172.16.123.172
端口: 3306

1 主从库核心配置
gtid-mode = on
enforce_gtid_consistency = 1

server-id = 3306171
binlog_format = row
log-bin=/data/mysql/mysql3306/logs/mysql-bin

skip_slave_start = 1 #禁止mysql服务启动时启动主从关系

2 主库创建复制用的账号
create user 'repl'@172.16.123.%' identified by 'repl4slave';
grant replication slave on *.* to 'repl'@'172.16.123.%';

3 主库
root@localhost [(none)]>create database db1;
root@localhost [(none)]>use db1;
root@localhost [db1]>create table tb1(id int(11) not null auto_increment, name varchar(10) not null default '', primary key(id));
root@localhost [db1]>insert into tb1(name) values('张三'),('李四');

mysqldump -S /tmp/mysql.sock -uroot -p --master-data=2 --single-transaction -A > db1-20161004_gtid.sql
Warning: A partial dump from a server that has GTIDs will by default include the GTIDs of all transactions, even those that changed suppressed parts of the database. If you don't want to restore GTIDs, pass --set-gtid-purged=OFF. To make a complete dump, pass --all-databases --triggers --routines --events.
以上警告说是备份包含GTID, 备份文件中可以看到SET @@GLOBAL.GTID_PURGED='76814042-89dc-11e6-85e3-00155d016707:1';
如果是部分备份,不需要GTID的时候,可以加上--set-gtid-purged=OFF

scp db1-20161004_gtid.sql root@172.16.123.172:/root

4 从库
mysql -S /tmp/mysql.sock -uroot -p < ~/db1-20161004_gtid.sql
CHANGE MASTER TO
MASTER_HOST='172.16.123.171',
MASTER_USER='repl',
MASTER_PASSWORD='repl4slave',
MASTER_PORT=3306,
MASTER_AUTO_POSITION=1;

start slave;
show slave status\G
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Executed_Gtid_Set: 76814042-89dc-11e6-85e3-00155d016707:1-7
测试
主库插入一条数据
insert into db1.tb1(name) values('王伍');
主库和从库执行show master status;查看Executed_Gtid_Set是否一致

如果导入备份数据提示错误ERROR 1840 (HY000) at line 24: @@GLOBAL.GTID_PURGED can only be set when @@GLOBAL.GTID_EXECUTED is empty.
解决办法reset master;

暂无评论

发送评论 编辑评论


				
|´・ω・)ノ
ヾ(≧∇≦*)ゝ
(☆ω☆)
(╯‵□′)╯︵┴─┴
 ̄﹃ ̄
(/ω\)
∠( ᐛ 」∠)_
(๑•̀ㅁ•́ฅ)
→_→
୧(๑•̀⌄•́๑)૭
٩(ˊᗜˋ*)و
(ノ°ο°)ノ
(´இ皿இ`)
⌇●﹏●⌇
(ฅ´ω`ฅ)
(╯°A°)╯︵○○○
φ( ̄∇ ̄o)
ヾ(´・ ・`。)ノ"
( ง ᵒ̌皿ᵒ̌)ง⁼³₌₃
(ó﹏ò。)
Σ(っ °Д °;)っ
( ,,´・ω・)ノ"(´っω・`。)
╮(╯▽╰)╭
o(*////▽////*)q
>﹏<
( ๑´•ω•) "(ㆆᴗㆆ)
😂
😀
😅
😊
🙂
🙃
😌
😍
😘
😜
😝
😏
😒
🙄
😳
😡
😔
😫
😱
😭
💩
👻
🙌
🖕
👍
👫
👬
👭
🌚
🌝
🙈
💊
😶
🙏
🍦
🍉
😣
Source: github.com/k4yt3x/flowerhd
颜文字
Emoji
小恐龙
花!
上一篇
下一篇