• Home
    • 2018
    • 2017
  • Oracle GoldenGate
    • Core Platform
    • Cloud Service
    • For Oracle DB
    • For Big Data
    • For MySQL
    • For DB2 z/OS
    • For DB2 LUW
    • For DB2 i
    • For Informix
    • For SQL Server
    • For Teradata
    • For AWS Aurora
    • Foundation Suite
    • Use Cases
  • Data Management
    • General
    • Big Data
    • Oracle Database
    • MySQL
    • MS SQL Server
    • IBM DB2
    • IBM Informix
    • Teradata
    • SAP ASE
  • Data Science
  • IT
    • IT Management
    • IT Technology
  • Reading
  • About
Jinyu's Blog
About Data, Database, Integration and IT 

Why do I get the "Failed to Connect to MySQL Database Error"?

2/21/2016

0 Comments

 
Details: When connecting to MySQL database in GGCS, I get the following error message:
GGSCI> dblogin sourcedb ggdemo@localhost, userid root, password xxx
2016-08-06 07:53:25  WARNING OGG-00769  MySQL Login failed: . SQL error (2002). Can't connect to local MySQL 
server through socket '/tmp/mysql.sock' (111).
ERROR: Failed to connect to MySQL database engine for HOST localhost, DATABASE mysql, USER root, PORT 3306.
Answer: You can see that the MySQL configuration file path is different from the configured one /var/lib/mysql/mysql.sock. Set up the following environment variables before starting GGSCI. 
export MYSQL_HOME=/var/lib/mysql
export MYSQL_UNIX_PORT=/var/lib/mysql/mysql.sock
Then, the connection should be successful. 
GGSCI (ip-172-30-3-204) 1> dblogin sourcedb ggdemo@localhost, userid root, password xxx
Successfully logged into database.
0 Comments

How to Get Started with GoldenGate for MySQL?

2/18/2016

0 Comments

 
The reading list including the selected online documents and the Oracle Knowledge Base articles. 
  1. How to Replicate Data Between Oracle and MySQL Database? (Doc ID 1605674.1)
  2. Goldengate MySQL Extract Does Not Capture Any Data When Mysqld Parameter Binlog_format is Not Set to Row (Doc ID 1566099.1)
  3. Does GoldenGate Support MySQL Remote? (Doc ID 1525296.1)
  4. Oracle GoldenGate Database Profile check script for MySQL Database (Doc ID 1501176.1)
  5. Supporting Transactions In MySQL By Using InnoDB Storage Engine (Doc ID 966149.1)
  6. OGG MYSQL Replicat is using non CDR columns in the WHERE clause for updates (Doc ID 1904130.1)
  7. What Is The Difference Between DYNSQL And NODYNSQL (Doc ID 965382.1)
  8. Why Do I Have To Enclose Names In Quotes When Mapping From A Case-sensitive Database? (Doc ID 972891.1)
  9. OGG Tool To Debug A Replicat : Showsyntax View replicat syntax, nobinarychars, nodynsql (Doc ID 966984.1)
  10. Using GETTRUNCATES To Replicat Purgedata From Tandem To Open System (Doc ID 966093.1)
0 Comments

How to replicat MySQL multi-byte/Unicode data using Oracle GoldenGate?

2/10/2016

0 Comments

 
Created: 1/21/2016 Last Updated: 8/6/2016
Oracle GoldenGate for MySQL supports replicating multi-byte characters either in metadata such as the table names or in any data columns. Let's do a simple test on this

The following is the example setup:
  • MySQL 5.6.29
  • Oracle GoldenGate 12.2
To simplify the test, Oracle GoldenGate is configured to replicate data from one table to the other in the same MySQL database. 

Step 1: Create Tables with Multi-byte Characters in MySQL
First, let's create a two tables in MySQL databases for the source and target. Note that the tables are defined with multi-byte character set utf8mb4. ​
What is the UTF8MB4 support in MySQL?
MySQL has limitation of only supporting characters from the basic multilingual plane (0x0000 - 0xFFFF). This restriction is removed in MySQL 5.5.3 and later version.
drop table tweet_tbl;
drop table tweet_target_tbl;

create table tweet_tbl(
id int primary key,
username char(8),
content varchar(25) ,
update_time timestamp);

create table tweet_target_tbl(
id int primary key,
username char(8),
content varchar(25) ,
update_time timestamp)
DEFAULT CHARACTER SET utf8mb4;
Step 2: Confirming the Default Database and Table Encoding
Next, we check the tables encoding information.

How to change the default character of a MySQL Database?
Please change the setting in the [mysqld] section in the my.ini or my.cof file and restart the database, i.e. "character-set-server=utf8".

mysql> select default_character_set_name from information_schema.schemata s 
where schema_name='mysql';
+----------------------------+
| default_character_set_name |
+----------------------------+
| latin1                              |
+----------------------------+
1 row in set (0.00 sec)

mysql>  select T.table_name, ccsa.character_set_name
      FROM information_schema.TABLES T,
           information_schema.COLLATION_CHARACTER_SET_APPLICABILITY ccsa
      WHERE ccsa.collation_name = T.table_collation
            AND T.table_schema = "ggdemo"
            AND T.table_name in ( "tweet_tbl", "tweet_target_tbl");
+------------------+--------------------+
| table_name       | character_set_name |
+------------------+--------------------+
| tweet_target_tbl | latin1            |
| tweet_tbl        | utf8mb4            |
+------------------+--------------------+
2 rows in set (0.00 sec)
, Step 3: Setting up the Oracle GoldenGate Replication. 
In this example, we skip the data pump component in Oracle GoldenGate.  

Extract Parmeter setup (extuni.prm):  
extract extuni
SETEVN (MYSQL_HOME='/var/lib/mysql')
SETENV (MYSQL_UNIX_PORT='/var/lib/mysql/mysql.sock')
sourcedb ggdemo,userid root,password mysql
reportcount every 60 seconds, rate
exttrail ./dirdat/tt
TRANLOGOPTIONS ALTLOGDEST /var/lib/mysql/bin-log.index 
table ggdemo.tweet_tbl;
Replicat param (repuni.prm):
 replicat repuni
 SETENV (MYSQL_HOME='/var/lib/mysql')
 SETENV (MYSQL_UNIX_PORT='/var/lib/mysql/mysql.sock')
 targetdb ggdemo userid root password mysql SESSIONCHARSET utf8mb4
 map ggdemo.tweet_tbl, target ggdemo.tweet_target_tbl;
GGSCI Command (rununi.oby):
add extract extuni, tranlog, begin now 
add exttrail ./dirdat/tt, extract extuni, megabytes 5 
start extract extuni
info extuni 

dblogin sourcedb ggdemo, userid root, password mysql 
add checkpointtable ggdemo.tweetchkpt_tbl

add replicat repuni, exttrail ./dirdat/tt, checkpointtable ggdemo.tweetchkpt_tbl

start repuni
info repuni
​Step 4: Run an example replication ​
mysql> insert into tweet_tbl values(1, 'jiwang', 0xF0A0B296, sysdate());
Query OK, 1 row affected (0.00 sec)

mysql> select hex(tt.content) as source_content, hex(tt.content) as target_content
from tweet_tbl ts, tweet_target_tbl tt;
+----------------+----------------+
| source_content | target_content |
+----------------+----------------+
| F0A0B296       | F0A0B296       |
+----------------+----------------+
1 row in set (0.01 sec)
The replication is done successfully. ​
Why I get the "OGG-00768 Failed to Map database character to ULibCharSet. SQL error (0)." error?
For Oracle GoldenGate 11.2.0.7, if the database is set to be in character set utf8mb4, you will get the "OGG-00768 Failed to Map database character to ULibCharSet. SQL error (0)." error. This is a bug.
0 Comments

    GoldenGate for MySQL

    Oracle GoldenGate for MySQL replicates DML operations for MySQL databases. Here, I discuss Oracle GoldenGate for MySQL, MySQL database and MySQL replication.

    ​Top Posts
    • Summary
    • MySQL 5.7 Support
    • Veridata Support 
    • How to replicat MySQL RDS Database  
    • How to enable binary logging for MySQL
    Resources
    • Cheat sheet: GoldenGate for MySQL
    • Documentation: ​GoldenGate for MySQL Installation and Setup Guide (12.2)
    Buzzwords
    Heterogeneity Security High Performance ​Real-Time ​DML Globalization

    Archives

    February 2018
    January 2018
    July 2017
    April 2017
    January 2017
    September 2016
    August 2016
    March 2016
    February 2016
    January 2016
    December 2015
    November 2015
    October 2015
    September 2015
    August 2015
    June 2015

    Categories

    All
    Amazon AWS
    Feature Inquiry
    GoldenGate
    How To
    MySQL
    Release
    Troubleshooting

    RSS Feed

Copyright © 2010-2027 Jinyu Wang.