The following is the example setup:
- MySQL 5.6.29
- Oracle GoldenGate 12.2
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.
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;
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)
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 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;
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
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)
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.