Goldengate certification for MySQL (2023)

Goldengate certification for MySQL (1)

Oracle Database Tips by Donald BurlesonJune 10, 2015


This is an excerpt from the book book Oracle GoldenGate 12c: A Hands-on Guide to Data Replication & Integration using Oracle & SQL Server.

Certification of Oracle GoldenGate and MySQL

Using certified software releases is essential. Certification ensures the combination of the installed Oracle software has been tested, issues are discovered, and fixes & workarounds are developed by Oracle Corporation. However, Oracle GoldenGate 12c for MySQL is certified with MySQL enterprise edition only. From MySQL command-line, execute the SHOW VARIABLES with the characters pattern '%version%' to display matching MySQL system variables related to softwareversion details. Use of the wildcards characters ('%' and '_') and WHERE clause limits the returned system variables values.

mysql> SHOW VARIABLES LIKE '%version%';

+-------------------------+-----------------------------------------------+

| Variable_Name| Value |

+-------------------------+-----------------------------------------------+

| protocol_version| 10|

| version| 5.0.95-log |

| version_bdb| Sleepycat Software: Berkeley DB 4.1.24:|

|| (December 16, 2015)|

| version_comment| Source distribution|

| version_compile_machine | x86_64|

| version_compile_os| redhat-linux-gn|

+-------------------------+-----------------------------------------------+

6 rows in set (0.01 sec)

At the time of writing the book, Oracle GoldenGate 12c (12.1.2.0) is certified with MySQL database version 5.5 and 5.6, and available for x86-64 platforms only. For the current certification matrix of Oracle GoldenGate and MySQL database, visit Oracle support services site - http://support.oracle.com.

Installation and Setup of MySQL

Since Oracle Linux Release 5 is bundled with MySQL, it is important to certifythe pre-installed version of MySQL with Oracle GoldenGate for MySQL.Let us quickly start and verify the pre-installed Oracle MySQL version.

$ su -

Password:

# service mysqld start

Starting MySQL:[ OK]

# mysql

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 2

Server version: 5.0.77 Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> SELECT VERSION();

+-----------+

| VERSION() |

+-----------+

| 5.0.77 |

+-----------+

1 row in set (0.00 sec)

mysql>

For an unsupported release, an upgrade or de-install and install of MySQL is required. Typically, MySQL installation is performed using either the RedHat Linux Operating System package manager (rpm) or the Yum utility. The next section illustrates MySQL de-installation using 'rpm' and the installation using the Yum utility, which installs the certified Oracle MySQL release 5.6.19-2.

What is Yum?

Yum stands for Yellowdog Update Modified. It is a package manager for automating the installation of Linux Operating System packages (RPMs). The Yum utility usesa repository to resolve RPM conflicts and dependences. Before using the Yum utility to install MySQL, download the latest version of the MySQL Yum repository, which references the latest version of MySQL distribution packages. The repository is downloadable from the Oracle MySQL developers' site http://dev.mysql.com/downloads/repo.

The following are the basic options of the Yum utility:

* Installing distribution packages specifying the 'install' option followed bythe repository definition. The flag [-y] enables a silent install session.

#yum install <distribution packages reference> [ -y ]

* Remove distribution packages by specifying the 'remove' option followedby the repository definition. The flag [-y] enables silent de-install session.

#yum remove <distribution packages reference > [ -y ]

* Query the existing installation for finding packages based on keywords or specific package name.

#yum search <package keyword>

Other Yum utility options include update, check update, provides, and local install. For software like MySQL running on Linux, it is highly recommended to employ the Yum utility to install software.

De-Installation of MySQL

Since the pre-shipped with Oracle Linux is not certified with Oracle GoldenGate 12 (12.1.2.0), perform the steps below to de-install the existing MySQL software using the 'rpm' command.

* Begin by querying the existing MySQL installed packages, then remove each software package individually as shown next. Do not remove the ODBC package as it is required by Oracle GoldenGate for MySQL.

[root@source-mysql1 ~]# rpm -qa | grep '^mysql-'

mysql-5.0.77-3.el5

mysql-connector-odbc-3.51.26r1127-1.el5

mysql-bench-5.0.77-3.el5

mysql-server-5.0.77-3.el5

mysql-devel-5.0.77-3.el5

mysql-5.0.77-3.el5

mysql-devel-5.0.77-3.el5

* Next, de-install MySQL software. Forced de-install is performed by supplying the two options '-allmatches' and '-nodeps'. The option '-allmatches' removes all versions of the packages with matching name - avoiding returning an error in case multiple matching packages exist. To avoid dependencies check, use the option '-nodeps'.

[root@source-mysql1 ~]# rpm -e --allmatches mysql-5.0.77-3.el5 --nodeps

[root@source-mysql1 ~]# rpm -e --allmatches mysql-bench-5.0.77-3.el5 --nodeps

[root@source-mysql1 ~]# rpm -e --allmatches mysql-server-5.0.77-3.el5 --nodeps

[root@source-mysql1 ~]# rpm -e --allmatches mysql-devel-5.0.77-3.el5 --nodeps

[root@source-mysql1 ~]#

Repeat the de-installation steps on the four Oracle MySQL systems.

Installation of Oracle MySQL

The installation of MySQL is easily and quickly performed using the Yum utilityby following the steps below:

* Begin by installing the latest MySQL repository package. The package is downloadable from http://dev.mysql.com/downloads/repo site.

[root@source-mysql1 rpm]# rpm -Uhv mysql-community-release-el5-5.noarch.rpm

* Use the Yum utility to install Oracle MySQL server. The server must be ableto connect to the repository to resolve packages dependencies and installthe required software packages.

[root@source-mysql1 rpm]# yum install mysql mysql-server -y

* Query the installed MySQL packages details. This step can also be performed using the Yum utility.

[root@source-mysql1 rpm]# rpm -qa | grep '^mysql-'

mysql-5.6.19-2.el5

mysql-connector-odbc-3.51.26r1127-1.el5

mysql-community-client-5.6.19-2.el5

mysql-community-release-el5-5

mysql-community-server-5.6.19-2.el5

mysql-community-common-5.6.19-2.el5

mysql-community-libs-5.6.19-2.el5

mysql-5.6.19-2.el5

mysql-community-libs-compat-5.6.19-2.el5

[root@source-mysql2 rpm]#

* Start MySQL Linux services

[root@source-mysql1 rpm]# /sbin/service mysqld start

* Secure the installation by providing password for 'root' MySQL user.

root@source-mysql1 rpm]# /usr/bin/mysql_secure_installation

* Connect to MySQL server, create and query the list of databases.

[root@source-mysql1 rpm]# mysql -u root -p

Enter password:

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 13

Server version: 5.6.19 MySQL Community Server (GPL)

Copyright (c) 2000, 2015, 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> create database SDB2;

Query OK, 1 row affected (0.00 sec)

mysql> show databases;

+--------------------+

| Database|

+--------------------+

| information_schema |

| SDB2|

| mysql|

| performance_schema |

+--------------------+

4 rows in set (0.02 sec)

mysql>

Repeat the installation steps across the four Oracle MySQL systems.

MySQL Post-Installation

Perform the following steps to enable Oracle GoldenGate 12c to perform captures from MySQL transaction logs. Otherwise, Oracle GoldenGate trails are shipped excluding the database transactions.

* Change the Operating System group for the Operating System user 'mysql'to match the group for the Oracle GoldenGate user. Both users assignedthe 'oinstall' group.

root@source-mysql1 rpm]# usermod -G oinstall mysql

* Modify the 'umask' environment variable to enable read permission for MySQL transaction log files. Add the environment variables MYSQL_HOME and LD_LIBRARY_PATH to .bash_profile shell startup file.

umask 077

MYSQL_HOME=/usr/bin; export MYSQL_HOME

LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/u01/app/ggs/mysql; export LD_LIBRARY_PATH

* Prepare MySQL parameter file /etc/my.cnf. This file overrides parameter files found elsewhere. Notice the format for the parameter 'log-bin', it is fully qualified as log-bin=/var/lib/mysql/<hostname>-bin.

[mysqld]

#

datadir=/var/lib/mysql

socket=/var/lib/mysql/mysql.sock

user=mysql

log-bin=/var/lib/mysql/localhost-bin

max_binlog_size=4096

binlog_format=row

#

# Disabling symbolic-links is recommended to prevent assorted security

# risks

symbolic-links=0

#

# Recommended in standard MySQL setup

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

#

[mysqld_safe]

log-error=/var/log/mysqld.log

pid-file=/var/run/mysqld/mysqld.pid

* Setup the 'socket' parameter to 'socket=/tmp/mysql.sock'. This is done creating the directory /tmp/mysql.sock or by creating a soft link to /tmp directory.This step is required because Oracle GoldenGate requires the parameter 'socket' to be located on /tmp/mysql.sock.

[root@source-mysql1 rpm]# ln -s /var/lib/mysql/mysql.sock /tmp

* Create the ODBC initialization parameter file - /usr/local/etc/odbc.ini.Add the parameters required by Oracle MySQL ODBC connector.

[root@source-mysql1 etc]# pwd

/usr/local/etc

[ODBC 3.51 Data Source]

SDB2 = MyODBC 3.51 Driver DSN

[SDB1]

Driver = /usr/lib/libmyodbc3.so

Description = Connector/ODBC 3.51 Driver DSN

Server = localhost

Port = 3306

User = root

Password = oracle

Database = SDB1

Option = 3

Socket = /tmp/mysql.sock

Since most of the steps above are Oracle GoldenGate specific, do not proceed with the installation of Oracle GoldenGate 12c prior to successfully completing these steps. Stop and start the mysqld server and attempt to connect to MySQL. This will verify connections are affected by the new or modified parameters. After Oracle GoldenGate 12c for MySQL is installed, test the database connection from the GGSCI command-line by executing DBLOGIN. The SOURCEDB option refersto <db name>@<hostname>.

GGSCI (source-mysql1) 15> DBLOGIN SOURCEDB SDB1@source-mysql1, USERID root, PASSWORD oracle

Successfully logged into database.

GGSCI (source-mysql1) 16>

Goldengate certification for MySQL (2)

Top Articles
Latest Posts
Article information

Author: Frankie Dare

Last Updated: 12/14/2022

Views: 5726

Rating: 4.2 / 5 (53 voted)

Reviews: 92% of readers found this page helpful

Author information

Name: Frankie Dare

Birthday: 2000-01-27

Address: Suite 313 45115 Caridad Freeway, Port Barabaraville, MS 66713

Phone: +3769542039359

Job: Sales Manager

Hobby: Baton twirling, Stand-up comedy, Leather crafting, Rugby, tabletop games, Jigsaw puzzles, Air sports

Introduction: My name is Frankie Dare, I am a funny, beautiful, proud, fair, pleasant, cheerful, enthusiastic person who loves writing and wants to share my knowledge and understanding with you.