Goldengate完成Mysql到Mysql的数据同步
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了Goldengate完成Mysql到Mysql的数据同步,小编现在分享给大家,供广大互联网技能从业者学习和参考。文章包含5971字,纯文字阅读大概需要9分钟。
内容图文
- 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]下添加如下参数
[sql] view plain copy- binlog_format=row
- log-bin = mysql-bin #开启二进制日志
重启
[sql] view plain copy- service mysqld restat
2)创建用户并授权
[sql] view plain copy- 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;
3)解压安装创建相应目录
[sql] view plain copy- 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)配置管理进程
[sql] view plain copy- 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)配置抽取进程
[sql] view plain copy- 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)配置传递进程
[sql] view plain copy- GGSCI (hadooptest05) 8> edit param mcp1
- extract mcp1
- passthru
- sourcedb o2m-mc@192.168.68.13:3306 userid ogg password ogg
- rmthost 192.168.68.14,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 mcp1
7)创建表的定义文件
[sql] view plain copy- #创建参数文件
- GGSCI (hadooptest05) 10> edit param defgen
- defsfile ./dirdef/gmqdsjsjp.def
- sourcedb o2m-mc@192.168.68.14:3306 userid ogg, password ogg
- table o2m-mc.GMQDSJSP;
- #生成表定义文件
- ./defgen paramfile ./dirprm/defgen.prm
- #传至目标库目录
- scp /home/mysql/goldengate/dirdef/gmqdsjsjp.def 192.168.68.14:/home/goldengate/dirdef/
3、目标库
1)创建用户
[sql] view plain copy- mysql> GRANT ALL PRIVILEGES ON `gmdc`.* TO ‘ogg‘@localhost IDENTIFIED BY ‘ogg‘;
- mysql> GRANT ALL PRIVILEGES ON `gmdc`.* TO ‘ogg‘@‘%‘ IDENTIFIED BY ‘ogg‘;
2)同样解压安装并创建目录
[sql] view plain copy- 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)配置管理进程
[sql] view plain copy- 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)添加检查点表
[sql] view plain copy- GGSCI (db) 5> dblogin sourcedb gmdc@192.168.68.14: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
5)配置应用进程
[sql] view plain copy- GGSCI (db) 1> edit params mcr1
- replicat mcr1
- targetdb gmdc@192.168.68.14: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 mcr1
4、测试数据同步
注意:mysql中表的大小注意区分
info mgr //查看启动信息
Goldengate完成Mysql到Mysql的数据同步
标签:ack single 准备 dmp 目录 too handle localhost 开启
本文系统来源:http://www.cnblogs.com/tianfen/p/7779835.html
内容总结
以上是互联网集市为您收集整理的Goldengate完成Mysql到Mysql的数据同步全部内容,希望文章能够帮你解决Goldengate完成Mysql到Mysql的数据同步所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错,欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明:本文内容由互联网用户自发贡献,该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 gblab@vip.qq.com 举报,一经查实,本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。