如何理解ogg12 mysql to oracle单向同步
发布时间：2021-12-20 10:29:38 所属栏目：移动 来源：互联网
导读：本篇文章为大家展示了如何理解ogg12 mysql to oracle单向同步，内容简明扼要并且容易理解，绝对能使你眼前一亮，通过这篇文章的详细介绍希望你能有所收获。 环境： 192.168.91.137 (Mysql) 192.168.91.138 ( Oracle ) 版本： 操作系统：centOS 6.5 Mysql: 5
|
本篇文章为大家展示了如何理解ogg12 mysql to oracle单向同步，内容简明扼要并且容易理解，绝对能使你眼前一亮，通过这篇文章的详细介绍希望你能有所收获。 环境： 192.168.91.137 (Mysql) —> 192.168.91.138 ( Oracle ) 版本： 操作系统：centOS 6.5 Mysql: 5.7.17 Oracle： 11.2.0.4 goldengate: 12.2.0.1 for oracle 12.2.0.1 for mysql 一、源端安装配置【Mysql】 1、vim /etc/profile export MYSQL_HOME=/usr/local/mysql export PATH=$PATH:$MYSQL_HOME/bin source /etc/profile 2、添加一个mysql组 groupadd -g 1000 mysql passwd mysql 3、添加一个用户 useradd -u 1000 -g mysql mysql passwd mysql 4、解压缩下载的包 cd /usr/local/ tar -xzvf /usr/local/mysql-5.7.17-linux-glibc2.5-x86_64.tar.gz tar -xzvf /usr/local/mysql-test-5.7.17-linux-glibc2.5-x86_64.tar.gz 5、然后 mv 解压后的包,重命名mysql mv mysql-5.7.17-linux-glibc2.5-x86_64 mysql 6、进入mysql包中， 给这个包授权 给mysql chown -R mysql:mysql /usr/local/mysql chmod -R 755 /usr/local/mysql 7、进入mysql文件名 basedir 为mysql 的路径， datadir 为mysql的 data 包，里面存放着mysql自己的包， 如user [root@ogg1 local]# /usr/local/mysql/bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data 2017-04-07T02:50:57.483315Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details). 2017-04-07T02:51:00.090256Z 0 [Warning] InnoDB: New log files created, LSN=45790 2017-04-07T02:51:00.505229Z 0 [Warning] InnoDB: Creating foreign key constraint system tables. 2017-04-07T02:51:00.708487Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: 08775cba-1b3d-11e7-8520-000c29439f01. 2017-04-07T02:51:00.733003Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened. 2017-04-07T02:51:00.735871Z 1 [Note] A temporary password is generated for root@localhost: EdgZSS;6=hqD 注：此处需要注意记录生成的临时密码，如上文： 8、安装SSL [root@ogg1 local]# /usr/local/mysql/bin/mysql_ssl_rsa_setup --datadir=/usr/local/mysql/data Generating a 2048 bit RSA private key .......................................................................................+++ ...............................................+++ writing new private key to 'ca-key.pem' ----- Generating a 2048 bit RSA private key ....................................+++ .........+++ writing new private key to 'server-key.pem' ----- Generating a 2048 bit RSA private key ................................................................................................................................................................+++ ..............................................................................+++ writing new private key to 'client-key.pem' 9、进入support-files，拷贝参数文件 [root@ogg1 local]# cp /usr/local/mysql/support-files/my-default.cnf /etc/my.cnf [root@ogg1 local]# cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysql 10、修改配置文件中的内容 [root@ogg1 local]# vim /etc/init.d/mysql basedir=/usr/local/mysql datadir=/usr/local/mysql/data [root@ogg1 local]# vim /etc/my.cnf basedir=/usr/local/mysql datadir=/usr/local/mysql/data port=3306 socket=/tmp/mysql.sock character-set-server=utf8 log_timestamps=system user=mysql binlog_format=row server-id=1 log-bin=mysql-bin 注：如果vim /etc/my.cnf 只配置 log-bin=mysql-bin，没配置server-id=1。会报如下错误 Starting MySQL. ERROR! The server quit without updating PID file (/usr/local/mysql/data/ogg1.pid). 11、启动mysql [root@ogg1 local]# /usr/local/mysql/bin/mysqld_safe --user=mysql & 12、另开个窗口，输入临时密码:EdgZSS;6=hqD (见第七步操作最后) [root@ogg1 ~]# mysql -uroot -p Enter password: Welcome to the MySQL monitor. Commands end with ; or g. Your MySQL connection id is 3 Server version: 5.7.17 Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or 'h' for help. Type 'c' to clear the current input statement. 13、修改密码 mysql> set password=password('manager2017'); Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> flush privileges; Query OK, 0 rows affected (0.00 sec) 14、查看设置的参数是否生效： mysql> show master status; +------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000001 | 154 | | | | +------------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec) mysql> show variables like '%log_bin%'; +---------------------------------+---------------------------------------+ | Variable_name | Value | +---------------------------------+---------------------------------------+ | log_bin | ON | | log_bin_basename | /usr/local/mysql/data/mysql-bin | | log_bin_index | /usr/local/mysql/data/mysql-bin.index | | log_bin_trust_function_creators | OFF | | log_bin_use_v1_row_events | OFF | | sql_log_bin | ON | +---------------------------------+---------------------------------------+ 6 rows in set (0.01 sec) mysql> show variables like '%binlog%'; +-----------------------------------------+----------------------+ | Variable_name | Value | +-----------------------------------------+----------------------+ | binlog_cache_size | 32768 | | binlog_checksum | CRC32 | | binlog_direct_non_transactional_updates | OFF | | binlog_error_action | ABORT_SERVER | | binlog_format | ROW | | binlog_group_commit_sync_delay | 0 | | binlog_group_commit_sync_no_delay_count | 0 | | binlog_gtid_simple_recovery | ON | | binlog_max_flush_queue_time | 0 | | binlog_order_commits | ON | | binlog_row_image | FULL | | binlog_rows_query_log_events | OFF | | binlog_stmt_cache_size | 32768 | | innodb_api_enable_binlog | OFF | | innodb_locks_unsafe_for_binlog | OFF | | log_statements_unsafe_for_binlog | ON | | max_binlog_cache_size | 18446744073709547520 | | max_binlog_size | 1073741824 | | max_binlog_stmt_cache_size | 18446744073709547520 | | sync_binlog | 1 | +-----------------------------------------+----------------------+ 20 rows in set (0.01 sec) mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | +--------------------+ mysql> create database test; Query OK, 1 row affected (0.07 sec) mysql> create table t1 (name char(10)); Query OK, 0 rows affected (0.13 sec) mysql> show table status from test like 't1'; +-------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+ | Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment | +-------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+ | t1 | InnoDB | 10 | Dynamic | 0 | 0 | 16384 | 0 | 0 | 0 | NULL | 2017-04-07 14:56:21 | NULL | NULL | utf8_general_ci | NULL | | | +-------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+ 1 row in set (0.00 sec) 二、安装mysql版本的goldengate 下载相应的OGG软件。http://www.oracle.com/technetwork/middleware/goldengate/downloads/index.html 源端用mysql用户安装gg： 1、设置环境变量 vi .bash_profile export GG_HOME=/home/mysql/ggs export MYSQL_HOME=/usr/local/mysql export LD_LIBRARY_PATH=$MYSQL_HOME/lib:/usr/lib:/usr/local/lib export PATH=$GG_HOME:$PATH source ~/.bash_profile [mysql@ogg1]$ unzip ggs_Linux_x64_MySQL_64bit.zip [mysql@ogg1]$ mkdir -p ./ggs [mysql@ogg1]$ tar -xvf ggs_Linux_x64_MySQL_64bit.tar -C ./ggs [mysql@ogg1]$ cd ./ggs 2、配置gg参数 [mysql@ogg1 ggs]$ ./ggsci Oracle GoldenGate Command Interpreter for MySQL Version 12.2.0.1.1 OGGCORE_12.2.0.1.0_PLATFORMS_151211.1401 Linux, x64, 64bit (optimized), MySQL Enterprise on Dec 11 2015 16:23:51 Operating system character set identified as UTF-8. Copyright (C) 1995, 2015, Oracle and/or its affiliates. All rights reserved. GGSCI (ogg1) 1> create subdirs Creating subdirectories under current directory /home/mysql/ggs Parameter files /home/mysql/ggs/dirprm: created Report files /home/mysql/ggs/dirrpt: created Checkpoint files /home/mysql/ggs/dirchk: created Process status files /home/mysql/ggs/dirpcs: created SQL script files /home/mysql/ggs/dirsql: created Database definitions files /home/mysql/ggs/dirdef: created Extract data files /home/mysql/ggs/dirdat: created Temporary files /home/mysql/ggs/dirtmp: created Credential store files /home/mysql/ggs/dircrd: created Masterkey wallet files /home/mysql/ggs/dirwlt: created Dump files /home/mysql/ggs/dirdmp: created GGSCI (ogg1) 2> edit params mgr GGSCI (ogg1) 3> view params mgr PORT 7809 autorestart extract *,waitminutes 2,resetminutes 5 GGSCI (ogg1) 4> start mgr Manager started. GGSCI (ogg1) 5> info mgr Manager is running (IP port ogg1.7809, Process ID 11623). 3、配置extract进程 GGSCI (ogg1) 6> edit params extr01 GGSCI (ogg1) 7> view params extr01 extract extr01 setenv (MYSQL_HOME="/usr/local/mysql") tranlogoptions altlogdest /usr/local/mysql/data/mysql-bin.index sourcedb test@localhost:3306,userid root,password manager2017 exttrail ./dirdat/e2 dynamicresolution gettruncates table test.t1; GGSCI (ogg1) 8> add extract extr01,tranlog,begin now EXTRACT added. GGSCI (ogg1) 9> add exttrail ./dirdat/e2,extract extr01 EXTTRAIL added. 4、配置推送进程 GGSCI (ogg1) 10> edit params pump01 GGSCI (ogg1) 11> view params pump01 extract pump01 rmthost 192.168.91.138,mgrport 7809 rmttrail /u01/app/goldengate/dirdat/e2 passthru gettruncates table test.t1; GGSCI (ogg1) 12> add extract pump01,exttrailsource ./dirdat/e2 EXTRACT added. GGSCI (ogg1) 13> add rmttrail /u01/app/goldengate/dirdat/e2,extract pump01 RMTTRAIL added. 5、配置defgen (异构数据库需要)： GGSCI (ogg1) 14> edit params defgen GGSCI (ogg1) 15> view params defgen defsfile /home/mysql/ggs/dirdef/defgen.prm sourcedb test@localhost:3306, userid root,password manager2017 table test.t1; [mysql@ogg1 ggs]$ ./defgen paramfile ./dirprm/defgen.prm *********************************************************************** Oracle GoldenGate Table Definition Generator for MySQL Version 12.2.0.1.1 OGGCORE_12.2.0.1.0_PLATFORMS_151211.1401 Linux, x64, 64bit (optimized), MySQL Enterprise on Dec 11 2015 17:21:39 Copyright (C) 1995, 2015, Oracle and/or its affiliates. All rights reserved. Starting at 2017-04-07 15:28:50 *********************************************************************** Operating System Version: Linux Version #1 SMP Fri Nov 22 03:15:09 UTC 2013, Release 2.6.32-431.el6.x86_64 Node: ogg1 Machine: x86_64 soft limit hard limit Address Space Size : unlimited unlimited Heap Size : unlimited unlimited File Size : unlimited unlimited CPU Time : unlimited unlimited Process id: 11704 *********************************************************************** ** Running with the following parameters ** *********************************************************************** defsfile /home/mysql/ggs/dirdef/defgen.prm sourcedb test@localhost:3306, userid root,password *** table test.t1; Retrieving definition for test.t1. Definitions generated for 1 table in /home/mysql/ggs/dirdef/defgen.prm. 三、安装配置目标端GG【Oracle】 1、安装oracle数据库软件实例，此处略过。 2、安装OGG软件 下载相应的OGG软件。http://www.oracle.com/technetwork/middleware/goldengate/downloads/index.html [root@ogg2 ~]# su - root [root@ogg2 ~]# mkdir -p /u01/app/goldengate [root@ogg2 ~]# chown -R oracle:oinstall /u01/app/goldengate [root@ogg2 ~]# su - oracle [oracle@ogg2 ~]$ ll unzip fbo_ggs_Linux_x64_shiphome.zip [oracle@ogg2 ~]$ cd /home/oracle/fbo_ggs_Linux_x64_shiphome/Disk1 [oracle@ogg2 Disk1]$ sed -i "s/INSTALL_OPTION=/INSTALL_OPTION=ORA11g/" /home/oracle/fbo_ggs_Linux_x64_shiphome/Disk1/response/oggcore.rsp [oracle@ogg2 Disk1]$ sed -i "s|SOFTWARE_LOCATION=|SOFTWARE_LOCATION=/u01/app/goldengate|" /home/oracle/fbo_ggs_Linux_x64_shiphome/Disk1/response/oggcore.rsp [oracle@ogg2 Disk1]$ sed -i "s/START_MANAGER=/START_MANAGER=false/" /home/oracle/fbo_ggs_Linux_x64_shiphome/Disk1/response/oggcore.rsp [oracle@ogg2 Disk1]$ ./runInstaller -silent -responseFile /home/oracle/fbo_ggs_Linux_x64_shiphome/Disk1/response/oggcore.rsp 3、配置环境变量 3.1 配置oracle用户的环境变量 [oracle@ogg2 Disk1]$ vi ~/.bash_profile export ORACLE_BASE=/u01/app/oracle export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/dbhome_1 export ORACLE_SID=burton export PATH=$PATH:$ORACLE_HOME/bin:$HOME/bin:$ORACLE_HOME/jdk/bin export PATH=$ORACLE_HOME/bin:/usr/sbin:$GG_HOME:$PATH export TNS_ADMIN=$ORACLE_HOME/network/admin export NLS_LANG=AMERICAN_AMERICA.AL32UTF8 export NLS_DATE_FORMAT="YYYY-MM-DD HH24:MI:SS" export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/usr/lib:/usr/local/lib export GG_HOME=/u01/app/goldengate umask 022 3.2 修改数据库参数 [oracle@ogg2 Disk1]$ source ~/.bash_profile [oracle@ogg2 Disk1]$ mkdir -p /u01/app/archivelog [oracle@ogg2 Disk1]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.4.0 Production on Fri Apr 7 15:36:30 2017 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> alter system set log_archive_dest_1='LOCATION=/u01/app/archivelog' scope=spfile; System altered. SQL> alter system set log_archive_format='burton_%t_%s_%r.arc' scope=spfile; System altered. SQL> shutdown immediate SQL> startup mount SQL> alter database archivelog; SQL> alter database open; SQL> archive log list; SQL> alter database add supplemental log data; SQL> select supplemental_log_data_min from v$database; SUPPLEME -------- YES SQL> alter database force logging; SQL> create tablespace tbs_ogg datafile '/u01/app/oracle/oradata/burton/tbs_ogg.dat' size 200M autoextend on next 5M; Tablespace created. SQL> create user ogg identified by ogg default tablespace tbs_ogg temporary tablespace temp account unlock; User created. SQL> alter system set enable_goldengate_replication = true scope=both; System altered. SQL> alter user ogg quota unlimited on tbs_ogg; User altered. SQL> grant connect,resource,dba to ogg; Grant succeeded. SQL> grant execute on utl_file to ogg; Grant succeeded. 3.3 创建测试用户 SQL> create tablespace TESTTBS01 datafile '/u01/app/oracle/oradata/burton/testtbs01.dat' size 100M autoextend on next 5M; Tablespace created. SQL> create user test identified by test2017 default tablespace TESTTBS01 temporary tablespace temp; User created. SQL> grant connect,resource,select_catalog_role to test; Grant succeeded. SQL> conn test/test2017 Connected. SQL> create table t1 (name char(10)); Table created. 4、OGG配置： 4.1 配置GG必要的路径和配置mgr参数 [oracle@ogg2 ~]$ cd $GG_HOME [oracle@ogg2 goldengate]$ ./ggsci Oracle GoldenGate Command Interpreter for Oracle Version 12.2.0.1.1 OGGCORE_12.2.0.1.0_PLATFORMS_151211.1401_FBO Linux, x64, 64bit (optimized), Oracle 11g on Dec 12 2015 00:54:38 Operating system character set identified as UTF-8. Copyright (C) 1995, 2015, Oracle and/or its affiliates. All rights reserved. GGSCI (ogg2) 1> create subdirs Creating subdirectories under current directory /u01/app/goldengate Parameter files /u01/app/goldengate/dirprm: created Report files /u01/app/goldengate/dirrpt: created Checkpoint files /u01/app/goldengate/dirchk: created Process status files /u01/app/goldengate/dirpcs: created SQL script files /u01/app/goldengate/dirsql: created Database definitions files /u01/app/goldengate/dirdef: created Extract data files /u01/app/goldengate/dirdat: created Temporary files /u01/app/goldengate/dirtmp: created Credential store files /u01/app/goldengate/dircrd: created Masterkey wallet files /u01/app/goldengate/dirwlt: created Dump files /u01/app/goldengate/dirdmp: created GGSCI (ogg2) 2> edit params mgr GGSCI (ogg2) 3> view params mgr PORT 7809 autorestart extract *,waitminutes 2,resetminutes 5 ACCESSRULE, PROG REPLICAT, IPADDR 192.168.91.137, ALLOW // 只在目标端配置，IP地址是对端IP GGSCI (ogg2) 4> start mgr 4.2 配置GLOBALS： GGSCI (ogg2) 5> edit params ./GLOBALS GGSCI (ogg2) 6> view params ./GLOBALS ggschema ogg checkpointtable ogg.checkpoint GGSCI (ogg2) 7> dblogin userid ogg, password ogg Successfully logged into database. GGSCI (ogg2) 8> add trandata test.t1 GGSCI (ogg2 as ogg@burton) 9> add checkpointtable ogg.checkpoint Successfully created checkpoint table ogg.checkpoint. GGSCI (ogg2 as ogg@burton) 10> edit params repl01 GGSCI (ogg2 as ogg@burton) 11> view params repl01 replicat repl01 sourcedefs /u01/app/goldengate/dirdef/defgen.prm userid ogg,password ogg reperror default,discard discardfile /u01/app/goldengate/dirrpt/repl01.dsc,append,megabytes 50 dynamicresolution map test.t1, target test.t1; GGSCI (ogg2 as ogg@burton) 12> dblogin userid ogg,password ogg Successfully logged into database. GGSCI (ogg2 as ogg@burton) 13> add replicat repl01,exttrail /u01/app/goldengate/dirdat/e2,checkpointtable ogg.checkpoint REPLICAT added. 四、启动OGG 1、源端： 拷贝defgen.prm到目标端: [mysql@mysql2 ggs]$ scp -r /home/mysql/ggs/dirdef/defgen.prm oracle@192.168.91.138:/u01/app/goldengate/dirdef/ 2、启动extract和pump进程 GGSCI (ogg1) 1> start extract extr01 Sending START request to MANAGER ... EXTRACT EXTR01 starting GGSCI (ogg1) 2> start extract pump01 Sending START request to MANAGER ... EXTRACT PUMP01 starting GGSCI (ogg1) 3> info all Program Status Group Lag at Chkpt Time Since Chkpt MANAGER RUNNING EXTRACT RUNNING EXTR01 00:00:00 00:00:06 EXTRACT RUNNING PUMP01 00:00:00 00:00:01 3、目标端 GGSCI (ogg2) 1> start replicat repl01 Sending START request to MANAGER ... REPLICAT REPL01 starting GGSCI (ogg2) 2> info all Program Status Group Lag at Chkpt Time Since Chkpt MANAGER RUNNING REPLICAT RUNNING REPL01 00:00:00 00:00:04 五、验证数据 1、源端 [root@ogg1 Desktop]# mysql -uroot -p Enter password: Welcome to the MySQL monitor. Commands end with ; or g. Your MySQL connection id is 6 Server version: 5.7.17-log MySQL Community Server (GPL) Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or 'h' for help. Type 'c' to clear the current input statement. mysql> use test Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> insert into t1 values ('abcd'); Query OK, 1 row affected (0.00 sec) 2、目标端 [oracle@ogg2 ~]$ sqlplus test/test2017 SQL*Plus: Release 11.2.0.4.0 Production on Mon Apr 10 10:19:36 2017 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> select * from t1; NAME ---------- abcd 注：异构数据库只支持DML操作。 上述内容就是如何理解ogg12 mysql to oracle单向同步，你们学到知识或技能了吗？如果还想学到更多技能或者丰富自己的知识储备，欢迎关注亿速云行业资讯频道。 （编辑：PHP编程网 - 襄阳站长网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
站长推荐