Data Definition Language (DDL) statements (operations) are used to define MySQL database structures or schema. You can use these DDL statements for data replication between MySQL source and target databases. MySQL DDL specifics are found in the MySQL documentation at https://dev.mysql.com/doc/.
Topics:
Parent topic: Using Oracle GoldenGate with MySQL
The prerequisites for configuring DDL replication are as follows:
DDL replication is supported for MySQL 5.7.10 and greater.
Bidirectional filtering for DDL replication is not supported.
Remote capture implementation doesn’t support DDL replication.
Oracle GoldenGate DDL replication uses two plug-ins as a shared library, ddl_rewriter
and ddl_metadata
, which must be installed on your MySQL server before Oracle GoldenGate replication starts.
The standalone application, Oracle GoldenGate metadata_server
, must be running to capture the DDL metadata.
The history
table under the new oggddl
database (oggddl.history
). This metadata history table is used to store and retrieve the DDL metadata history. The history table records must be ignored from being logged into the binary log so you must specify binlog-ignore-db=oggddl
in the my.cnf
file.
You should not manually drop the oggddl
database or the history
table because all DDL statements that run after this event will be lost.
You should not stop the metadata_server
during DDL capture as all the DDL statements that run after this event will be lost.
You should not manually remove the ddl_rewriter
and the ddl_metadata
plugins during DDL capture because all DDL statements that run after this event will be lost.
DDL executed within the stored procedure is not supported. For example , a DDL executed as in the following is not supported.
CREATE PROCEDURE atssrc.generate_data() BEGIN DECLARE i INT DEFAULT 0; WHILE i < 800 DO SET i = i + 1; IF (i = 100) then alter table atssrc.`ddl6` add col2 DATE after id; ELSEIF (i = 200) then alter table atssrc.`ddl6` add col3 DATETIME after datetime; ELSEIF (i = 300) then alter table atssrc.`ddl6` add `col4` timestamp NULL DEFAULT NULL after channel; ELSEIF (i = 400) then alter table atssrc.`ddl6` add col5 YEAR after value; END IF; END WHILE; END$$ DELIMITER ; call atssrc.generate_data();
By design, the heartbeat DDLs are ignored by the capture and you should create the heartbeat tables manually at the target.
Parent topic: Using DDL Replication
To install DDL replication, you run the installation script that is provided with Oracle GoldenGate as the replication user. This user must have Create
, Insert
,Select
, Delete
, Drop
, and Truncate
database privileges. Additionally, this user must have write permission to copy the Oracle GoldenGate plugin in the MySQL plugin directory. For example, the MySQL plugin are typically in /usr/lib64/mysql/plugin/
.
The installation script options are install
, uninstall
, start
, stop
, and restart
.
The command to install DDL replication uses the install option, user id, password, and port number respectively:
bash-3.2$ ./ddl_install.sh install-option user-id password port-number
For example:
bash-3.2$ ./ddl_install.sh install root welcome 3306
The DDL replication installation script completes the following tasks:
ddl_rewriter
, ddl_metadata
plugins and the metadata_server
files exist. If these files are not found, then an error message appears and the installation exits.metadata_server
if it is running.oggddl.history
table if it exists.metadata_server
as a daemon process.ddl_rewriter
and ddl_metadata
plugins.Parent topic: Using DDL Replication
You can use the following options with the metadata server:
You must have the Oracle GoldenGate metadata_server
running to capture the DDL metadata.
Run the install script with start
option to start the metadata server.
Run the install script with stop
option to stop the metadata server.
Run the install script with restart
option to stop the running metadata server and start again.
Oracle GoldenGate DDL replication uses two plugins as a shared library, ddl_rewriter
and ddl_metadata
, both of which must be installed on your MySQL server before Oracle GoldenGate replication starts.
The oggddl.history
metadata history table is used to store and retrieve the DDL metadata history.
There is a single history table and metadata server for each MySQL server. If you want to issue and capture DDLs from multiple instances of an Extract process on the same database server at the same time, there is a possibility of conflict between accessing and populating the metadata history table. Oracle recommends that you do not run and capture DDLs using multiple Extract instances on the same MySQL server.
Parent topic: Using DDL Replication
The following options are supported for MySQL DDL replication:
Option | Description |
---|---|
|
Include create table. |
|
Include tables under the |
|
Exclude all the tables under the |
|
Include DDL that contains this string. |
|
Excludes DDL that contains this string. |
|
MySQL DDL uses this option and should be used as the default for Oracle GoldenGate MySQL DDL replication. |
|
Default option. |
For a full list of options, see DDL in Reference for Oracle GoldenGate.
Using DDL Statements and Options
INCLUDE
(default) means include all objects that fit the rest of the description. EXCLUDE
means to omit items that fit the description. Exclude rules take precedence over include rules.
OPTYPE
specifies the types of operations to be included or excluded. You can use CREATE
and ALTER
. Multiple OPTYPE
can be specified using parentheses. For example, optype (create, alter)
. The asterisk (*) wildcard can be specified to indicate all operation types, and this is the default.
OBJTYPE
specifies the TABLE
operations to include or exclude. The wildcard can be specified to indicate all object types, and this is the default.
OBJNAME
specifies the actual object names to include or exclude. For example, eric.*
. Wildcards are specified as in other cases where multiple tables are specified. The default is *
.
String
indicates that the rule is true if any of the strings in stringspec
are present (or false if excludestring
is specified and the stringspec
is present). If multiple string
entries are made, at least one entry in each stringspec
must be present to make the rule evaluate true.
ddlops string (“a”, “b”), string (“c”) evaluates true if string “a” OR “b” is present, AND string “c” is present
local
is specified if you want the rule to apply only to the current Extract trail (the Extract trail to which the rule applies must precede this ddlops
specification).
The semicolon is required to terminate the parameter entry.
For example:
ddl optype (create, drop), objname (eric.*); ddl exclude objname (eric.tab*); exttrail a; exttrail b; ddl optype (create), objname (joe.*), string (“abc”, “xyz”) local; ddl optype (alter), objtype (index);
In this preceding example, the exttrail a
gets creates and drops for all objects that belong to eric
, except for objects that start with tab
, exttrail a
also gets all alter index statements, unless the index name begins with tab
(the rule is global even though it’s included in exttrail b
). exttrail b
gets the same objects as a
, and it also gets all creates for objects that belong to joe
when the string abc
or xyz
is present in the DDL text. The ddlops.c
module stores all DDL operation parameters and executes related rules.
Additionally, you can use the DDLOPTIONS
parameter to configure aspects of DDL processing other than filtering and string substitution. You can use multiple DDLOPTIONS
statements and Oracle recommends using one. If you are using multiple DDLOPTIONS
statements, then make each of them unique so that one does not override the other. Multiple DDLOPTIONS
statements are executed in the order listed in the parameter file.
See DDL and DDLOPTIONS.
Parent topic: Using DDL Replication
DDL replication relies on a metadata history table and the metadata plugin and server. To troubleshoot when DDL replication is enabled, the history table contents and the metadata plugin server logs are required.
You can use the mysqldump
command to generate the history table dump using one of the following examples:
mysqldump [options] database [tables] mysqldump [options] --databases [options] DB1 [DB2 DB3...] mysqldump [options] --all-databases [options]
For example, bash-3.2$ mysqldump -uroot -pwelcome oggddl history > outfile
The metadata plugins and server logs are located in the MySQL and Oracle GoldenGate installation directories respectively.
If you find an error in the log files, you need to ensure that the metadata server is running.
Parent topic: Using DDL Replication
If you no longer want to capture the DDL events, then you can use the same install script and select the uninstall
option to disable the DDL setup. Also, any Extract with DDL parameters should be removed or disabled. If you want to capture the DDL again, you can run the install script again. You should take care when multiple instances of the capture process is running on the same instance of your MySQL server. The DDL setup should not be disturbed or uninstalled when multiple capture processes are running and when at most one capture is designed to capture the DDL statement.
Use the installation script with the uninstall
option to uninstall DDL Replication. For example:
bash-3.2$ ./ddl_install.sh uninstall root welcome 3306
The script performs the following tasks:
ddl_rewriter
and ddl_metadata
plugins.oggddl.history
table if exists.metadata_server
if it is running.Parent topic: Using DDL Replication