Document reference Address: http://blog.csdn.net/u010587433/article/details/49305019
Demand:
Using goldengate to complete MySQL-to-MySQL data synchronization, the source library's O2M-MC library synchronizes to the GMDC library of the target library, the table name gmqdsjsp, in other words, the synchronization of data between tables between different databases
Experiment Preparation:
System: CentOS 7.2
MySQL version: 5.7-12
MYSQL-1:192.168.68.13 mysql-2:192.168.68.14
1. Initialization
1) Target Library CREATE database
mysql> CREATE DATABASE GMDC;
2) Import data
Export the Source Library table gmqdsjsp data and import it into the target library
[SQL]View PlainCopy
- mysqldump-uroot-p123456 --single-transaction--flush-logs--master-data=2-b o2m-mc--tables GMQDSJSP >/tmp/GMQ Dsjsp20151021.sql
- mysql-uroot-p123456 GMDC </tmp/gmqdsjsp20151021.sql
2. Source Library
1) Modify the parameters
Under/etc/my.cnf [mysqld], add the following parameters
[SQL]View PlainCopy
- Binlog_format=row
- Log-bin = Mysql-bin #开启二进制日志
Restart
[SQL]View PlainCopy
- Service Mysqld REStat
2) Create user and authorize
[SQL]View PlainCopy
- Mysql> GRANT all privileges in ' O2M-MC '. * to ' ogg ' @localhost identified by ' Ogg;
- Mysql> GRANT All privileges the ' o2m-mc '. * to ' ogg ' @'% ' identified by ' Ogg;
3) Unzip the installation to create the appropriate directory
[SQL]View PlainCopy
- 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) configuration Management process
[SQL]View PlainCopy
- 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) Configuring the extraction process
[SQL]View PlainCopy
- Ggsci (hadooptest05) 4> edit param Mce1
- Extract Mce1
- sourcedb [email protected]: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) Configuring the delivery process
[SQL]View PlainCopy
- Ggsci (hadooptest05) 8> edit param MCP1
- Extract MCP1
- PassThru
- sourcedb [email protected]: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) Create a definition file for the table
[SQL]View PlainCopy
- #创建参数文件
- Ggsci (hadooptest05) 10> edit param Defgen
- Defsfile./dirdef/gmqdsjsjp.def
- sourcedb [email protected]: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. Target Library
1) Create user
[SQL]View PlainCopy
- Mysql> GRANT all privileges in ' GMDC '. * to ' ogg ' @localhost identified by ' Ogg ';
- Mysql> GRANT all privileges in ' GMDC '. * to ' ogg ' @'% ' identified by ' Ogg ' ;
2) also unzip the installation and create the directory
[SQL]View PlainCopy
- 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) configuration Management process
[SQL]View PlainCopy
- 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) Add Checkpoint table
[SQL]View PlainCopy
- GGSCI (db) 5> dblogin sourcedb [email protected]: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) Configure the application process
[SQL]View PlainCopy
- GGSCI (db) 1> edit params MCR1
- Replicat MCR1
- targetdb [email protected]: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. Test Data synchronization
Note: The size of the table in MySQL is distinguished
Info MGR//view startup information
Goldengate complete mysql-to-MySQL data synchronization
