Using DDL Replication

Plug-in Based DDL Configuration Prerequisites and Considerations

This is an older approach to performing DDL Replication. The prerequisites for configuring DDL replication are as follows:

DDL replication is supported for MySQL 5.7.
Remote capture for MySQL 5.7 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 table DDLs are ignored by the capture and you should create the heartbeat tables manually at the target.

Parent topic: Using DDL Replication

Installing 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:

Ensures that you have a supported MySQL server version installed. DDL replication is supported for MySQL 5.7.10 and greater.
Locates the MySQL plugin directory.
Ensures that the 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.
Ensures that the plugins are already installed. If installed, the script exits with a message requesting you to uninstall first and then reinstall.
Stops the metadata_server if it is running.
Deletes the oggddl.history table if it exists.
Starts the metadata_server as a daemon process.
Installs the ddl_rewriter and ddl_metadata plugins.

Parent topic: Using DDL Replication

Using the Metadata Server

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

Using DDL Filtering for Replication

The following options are supported for MySQL DDL replication:

Option	Description
`DDL INCLUDE OPTYPE CREATE OBJTYPE TABLE;`	Include create table.
`DDL INCLUDE OBJNAME ggvam.*`	Include tables under the `ggvam`database.
`DDL EXCLUDE OBJNAME ggvam.emp*;`	Exclude all the tables under the `ggvam` database and table name starting with the `emp`wildcard.
`DDL INCLUDE INSTR ‘XYZ’`	Include DDL that contains this string.
`DDL EXCLUDE INSTR ‘WHY’`	Excludes DDL that contains this string.
`DDL INCLUDE MAPPED`	MySQL DDL uses this option and should be used as the default for Oracle GoldenGate MySQL DDL replication. `DDL INCLUDE ALL` and `DDL` are not supported.
`DDL EXCLUDE ALL`	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.
For example:
```
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 abcor 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

Troubleshooting Plug-in Based DDL Replication

Plug-in based 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

Uninstalling Plug-In Based 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:

Uninstalls the ddl_rewriter and ddl_metadata plugins.
Deletes the oggddl.history table if exists.
Removes the plugins from MySQL plugin directory.
Stops the metadata_server if it is running.

Parent topic: Using DDL Replication