14 Using DDL Replication
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:
- Plug-in Based DDL Configuration Prerequisites and Considerations
- Installing DDL Replication
- Using the Metadata Server
- Using DDL Filtering for Replication
- Troubleshooting Plug-in Based DDL Replication
- Uninstalling Plug-In Based DDL Replication
Parent topic: Using Oracle GoldenGate for MySQL
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
andddl_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 newoggddl
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 specifybinlog-ignore-db=oggddl
in themy.cnf
file. -
You should not manually drop the
oggddl
database or thehistory
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 theddl_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 themetadata_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
andddl_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
andddl_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 |
---|---|
|
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 useCREATE
andALTER
. MultipleOPTYPE
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 theTABLE
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 instringspec
are present (or false ifexcludestring
is specified and thestringspec
is present). If multiplestring
entries are made, at least one entry in eachstringspec
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 thisddlops
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 toeric
, except for objects that start withtab
,exttrail a
also gets all alter index statements, unless the index name begins withtab
(the rule is global even though it’s included inexttrail b
).exttrail b
gets the same objects asa
, and it also gets all creates for objects that belong tojoe
when the stringabc
orxyz
is present in the DDL text. Theddlops.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
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
andddl_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