Goldengate之Mysql到Mysql的同步
使用Goldengate完成Mysql到Mysql的数据同步,源库的o2m-mc库同步到目标库的gmdc库,表名GMQDSJSP1、初始化mysql1)目标库创建数据库mysql> create database gmdc;2)导入数据 将源库表GMQDSJSP数据导出,然后导入到目标库中mysqldump -uroot -p123456--single-transac
·
使用Goldengate完成Mysql到Mysql的数据同步,源库的o2m-mc库同步到目标库的gmdc库,表名GMQDSJSP
1、初始化
1)目标库创建数据库
mysql> create database gmdc;2)导入数据
将源库表GMQDSJSP数据导出,然后导入到目标库中
mysqldump -uroot -p123456 --single-transaction --flush-logs --master-data=2 -B o2m-mc --tables GMQDSJSP > /tmp/GMQDSJSP20151021.sql
mysql -uroot -p123456 gmdc < /tmp/GMQDSJSP20151021.sql
2、源库
1)修改参数
在/etc/my.cnf的[mysqld]下添加如下参数
binlog_format=row
重启
service mysqld restat
2)创建用户并授权
3)解压安装创建相应目录mysql> GRANT ALL PRIVILEGES ON `o2m-mc`.* TO 'ogg'@localhost IDENTIFIED BY 'ogg; mysql> GRANT ALL PRIVILEGES ON `o2m-mc`.* TO 'ogg'@'%'IDENTIFIED BY 'ogg;
unzip ggs_121210_Linux_x64_MySQL_64bit.zip -d /home/goldengate
cd /home/goldengate
tar xvf ggs_Linux_x64_MySQL_64bit.tar
./ggsci
GGSCI (hadooptest05) 1> create subdirs
Creating subdirectories under current directory /home/goldengate
Parameter files /home/goldengate/dirprm: already exists
Report files /home/goldengate/dirrpt: created
Checkpoint files /home/goldengate/dirchk: created
Process status files /home/goldengate/dirpcs: created
SQL script files /home/goldengate/dirsql: created
Database definitions files /home/goldengate/dirdef: created
Extract data files /home/goldengate/dirdat: created
Temporary files /home/goldengate/dirtmp: created
Credential store files /home/goldengate/dircrd: created
Masterkey wallet files /home/goldengate/dirwlt: created
Dump files /home/goldengate/dirdmp: created
4)配置管理进程
GGSCI (hadooptest05) 2> edit params mgr
port 7809
dynamicportlist 7840-7939
autorestart er *, retries 5, waitminutes 3
purgeoldextracts /home/goldengate/dirdat/*,usecheckpoints, minkeepdays 2
GGSCI (hadooptest05) 3> start mgr
Manager started.
5)配置抽取进程
GGSCI (hadooptest05) 4> edit param mce1
extract mce1
sourcedb o2m-mc@192.168.7.221:3306 userid ogg password ogg
exttrail /home/goldengate/dirdat/me
discardfile /home/goldengate/dirrpt/mce1.dsc,append
TranLogOptions AltLogDest /home/mariadb/data/binlogs.index
table o2m-mc.GMQDSJSP;
GGSCI (hadooptest05) 5> add extract mce1,tranlog,begin now
GGSCI (hadooptest05) 6> add exttrail /home/goldengate/dirdat/me,extract mce1
GGSCI (hadooptest05) 7> start mce1
6)配置传递进程
GGSCI (hadooptest05) 8> edit param mcp1 extract mcp1 passthru sourcedb o2m-mc@192.168.7.221:3306 userid ogg password ogg rmthost 192.168.7.221,mgrport 7809,compress rmttrail /home/goldengate/dirdat/mp dynamicresolution numfiles 3000 table o2m-mc.GMQDSJSP; GGSCI (hadooptest05) 9> add extract mcp1,exttrailsource /home/goldengate/dirdat/me GGSCI (hadooptest05) 10> add rmttrail /home/goldengate/dirdat/mp,extract mcp1 GGSCI (hadooptest05) 11> start mc1
7)创建表的定义文件
#创建参数文件 GGSCI (hadooptest05) 10> edit param defgen defsfile ./dirdef/gmqdsjsjp.def sourcedb o2m-mc@192.168.7.221:3306 userid ogg, password ogg table o2m-mc.GMQDSJSP; #生成表定义文件 ./defgen paramfile ./dirprm/defgen.prm #传至目标库目录 scp /home/mysql/goldengate/dirdef/gmqdsjsjp.def 192.168.7.221:/home/goldengate/dirdef/
3、目标库
1)创建用户
2)同样解压安装并创建目录mysql> GRANT ALL PRIVILEGES ON `gmdc`.* TO 'ogg'@localhost IDENTIFIED BY 'ogg'; mysql> GRANT ALL PRIVILEGES ON `gmdc`.* TO 'ogg'@'%' IDENTIFIED BY 'ogg';
unzip ggs_121210_Linux_x64_MySQL_64bit.zip -d /home/goldengate cd /home/goldengate tar xvf ggs_Linux_x64_MySQL_64bit.tar ./ggsci GGSCI (db) 1> create subdirs Creating subdirectories under current directory /home/goldengate Parameter files /home/goldengate/dirprm: already exists Report files /home/goldengate/dirrpt: created Checkpoint files /home/goldengate/dirchk: created Process status files /home/goldengate/dirpcs: created SQL script files /home/goldengate/dirsql: created Database definitions files /home/goldengate/dirdef: created Extract data files /home/goldengate/dirdat: created Temporary files /home/goldengate/dirtmp: created Credential store files /home/goldengate/dircrd: created Masterkey wallet files /home/goldengate/dirwlt: created Dump files /home/goldengate/dirdmp: created
3)配置管理进程
GGSCI (db) 3> edit params mgr port 7809 dynamicportlist 7840-7939 autorestart er *, retries 5, waitminutes 3 purgeoldextracts /home/goldengate/dirdat/*,usecheckpoints, minkeepdays 2 GGSCI (db) 4> start mgr Manager started.
4)添加检查点表
5)配置应用进程GGSCI (db) 5> dblogin sourcedb gmdc@192.168.7.221:3306 userid ogg password ogg GGSCI (db DBLOGIN as ogg) 6> add checkpointtable gmdc.checkpoint GGSCI (db DBLOGIN as ogg) 7> edit params ./GLOBALS checkpointtable gmdc.checkpoint
GGSCI (db) 1> edit params mcr1 replicat mcr1 targetdb gmdc@192.168.7.221:3306 userid ogg password ogg handlecollisions sourcedefs /home/goldengate/dirdef/gmqdsjsp.def discardfile /home/goldengate/dirrpt/mcr1.dsc,purge map o2m-mc.GMQDSJSP, target gmdc.GMQDSJSP; GGSCI (db) 2> add replicat mcr1,exttrail /home/goldengate/dirdat/mp,checkpointtable gmdc.checkpoint GGSCI (db) 3> start dcr1
4、测试数据同步
注意:mysql中表的大小注意区分
更多推荐
已为社区贡献3条内容
所有评论(0)