欢迎投稿

今日深度:

RDS永利ag手机版|首页与自建库的gtid主从同步,什么是主从永利ag手机版|首页

RDS永利ag手机版|首页与自建库的gtid主从同步,什么是主从永利ag手机版|首页


场景:永利ag手机版|首页用的是和通永利ag手机版|首页的RDS,有个实时数据分析项目需要做永利ag手机版|首页主从,开始的时候购买了和通永利ag手机版|首页RDS的只读实例,但在测试的过程中,发现该只读实例不能满足项目需求。因为实时数据分析项目需要从库的条件有:
1、从库需要有开启和停止主从的权限;
2、从库需要支持replication级联同步。
经咨询和通永利ag手机版|首页工程师,只能通过DTS进行数据同步,并且不支持级联同步。所以只好自己搭建MYSQL主从去满足项目了。

一、在centos7上部署MySQL永利ag手机版|首页
1、从MYSQL官网下载安装文件
wget http://dev.mysql.com/get/mysql-community-release-el7-5.noarch.rpm
2、卸载mariadb
rpm -qa | grep mariadb #查询已安装的mariadb rpm
rpm -e --nodeps mariadb-libs-xxxxx.x86_64 #强制卸载
3、安装MySQL
rpm -ivh mysql-community-release-el7-5.noarch.rpm
yum install mysql-community-server

4、启动MySQL
systemctl start mysqld.service

5、停止MySQL
systemctl stop mysqld.service

二、修改从库配置文件/etc/my.cnf
character-set-server=utf8
datadir = /var/lib/mysql
socket = /var/lib/mysql/mysql.sock

wait_timeout=2880000
interactive_timeout = 2880000
max_allowed_packet = 100M

innodb_checksum_algorithm=innodb
innodb_data_file_path=ibdata1:200M:autoextend
innodb_log_files_in_group=2
innodb_log_file_size=524288000
innodb_undo_directory=.
innodb_undo_tablespaces=0

server-id = 10000
gtid_mode = on

enforce_gtid_consistency = 1
master-info-repository = file
relay-log-info_repository = file
log-bin = mysqlslave-bin
relay-log = mysqlslave-relay-bin
expire_logs_days = 30
log-slave-updates = 1
binlog_format = row

replicate-do-db = db1
replicate-do-db = db2
replicate-do-db = db3

replicate-ignore-db = mysql
replicate-ignore-db = information_schema
replicate-ignore-db = performance_schema
slave-skip-errors = 1007,1008,1053,1062,1213,1158,1159

三、重启从库
systemctl restart mysqld.service

四、从RDS的永利ag手机版|首页导数据出来
mysqldump -u db1_user -pdb1_password -hmasterhost.mysql.rds.aliyuncs.com> /tmp/db1.sql
mysqldump -u db2_user -pdb2_password -hmasterhost.mysql.rds.aliyuncs.com> /tmp/db2.sql
mysqldump -u db3_user -pdb3_password -hmasterhost.mysql.rds.aliyuncs.com> /tmp/db3.sql

五、主库查询master?gtid_purged
mysql>show global variables like '%gtid%';
找到gtid_purged的值,第七步要用到

六、将RDS导出的永利ag手机版|首页,导入到从库
mysql -uroot -p root_password < /tmp/db1.sql
mysql -uroot -p root_password < /tmp/db2.sql
mysql -uroot -p root_password < /tmp/db3.sql

七、进入从库,设置同步信息
mysql>reset master;

mysql>set @@global.gtid_purged='主库的GITD值';

mysql>change master to master_host = 'masterhost.mysql.rds.aliyuncs.com', master_port = 3306, master_user = 'repl', master_password='repl_password', master_auto_position = 1;

mysql>start slave;

八、查询返回信息
mysql>show slave status G;

Slave_IO_Running: Yes
Slave_SQL_Running: Yes
两个都是yes,表示同步成功。

www.htsjk.Com true http://www.htsjk.com/mariadb/37715.html NewsArticle RDS永利ag手机版|首页与自建库的gtid主从同步,什么是主从永利ag手机版|首页 场景:永利ag手机版|首页用的是和通永利ag手机版|首页的RDS,有个实时数据分析项目需要做永利ag手机版|首页主从,开始的时候购买了和通永利ag手机版|首页RDS的只读实例,但...
评论暂时关闭