MySQL as a Source Database

Home / Repstance User Guide / DATABASE CONFIGURATION COMMANDS / Prepare Source Database / MySQL as Source

Note – MySQL is supported as a Source Database since version 6, by Repstance Advanced Edition only (see Supported Database Requirements).

These parameters are used by prepare, validate and remove with database=source dbtype=mysql (see Prepare Source Database, Validate Database and Remove Repstance Objects from Database). The login that is given in user needs the privileges that are described in Database Privileges. MySQL 8.0 or later is supported. MariaDB is not supported as a Source Database.

Requirements of the Server

The Capture Process reads the binary log of the server. validate checks the settings below, and the Capture Process checks them again every time it starts:

Setting	Value	Remark
version	8.0 or later	MariaDB is refused.
log_bin	ON	On Amazon RDS the binary log is on when the backup retention period of the instance is 1 day or more.
binlog_format	ROW	The binary log holds the changed rows, not the statements.
binlog_row_image	FULL	The binary log holds all the columns of a row before and after a change.
binlog_row_metadata	FULL	The binary log holds the names of the columns and the other metadata of the tables.

The settings can be read with:

show variables where variable_name in ('log_bin', 'binlog_format', 'binlog_row_image', 'binlog_row_metadata');

The Capture Process continues from the position where it stopped, so the server has to keep the files of the binary log that it has not read yet while the Capture Process is stopped. On Amazon RDS set the retention of the binary log, for example to 24 hours:

call mysql.rds_set_configuration('binlog retention hours', 24);

Parameters

Parameter	Description
database	source. Required.
dbtype	mysql. Required.
generatesql	1 makes prepare return the SQL script and change nothing. The default is 0: prepare creates the objects. validate does not use the parameter, and remove removes the objects whatever its value.
server	The host name or the IP address of the MySQL server. Required.
port	The TCP port of the MySQL server, usually 3306. Required.
user	The login that Repstance connects with. Required. For prepare it needs the right to create the database repi.
password	The password of the login. Required.
dbname	The database to connect to. It is optional. The Repstance objects are always in the database repi, whatever dbname is. In MySQL a database is what other systems call a schema: the first part of a mask schema.table of the Capture Process is the name of the database.

The parameters of the other Source Databases that MySQL has no use for, for example housekeeping of SQL Server, are refused.

Examples

Check the server, the login and the Repstance objects:

repcli validate database=source dbtype=mysql server=mysql-src.example.com port=3306 dbname=sales user=repuser password=Passw0rd123

The answer depends on what is missing. The objects of Repstance do not exist yet:

[Invalid]: Database is not prepared for capture: [Schema objects do not exist: 1]

The server is not set up for the capture:

[Invalid]: the server needs binlog_format=ROW, binlog_row_image=FULL

The login cannot read the binary log:

[Invalid]: the login needs the privileges REPLICATION SLAVE and REPLICATION CLIENT to read the binary log

Everything is in place:

[Valid]: Database SALES is valid

Prepare the database:

repcli prepare database=source dbtype=mysql server=mysql-src.example.com port=3306 dbname=sales user=repuser password=Passw0rd123
[Completed]: Database [mysql-src.example.com:3306,sales] has been prepared.

Return the script instead of running it. It is short; an administrator runs it with the MySQL client:

repcli prepare database=source dbtype=mysql server=mysql-src.example.com port=3306 user=repuser password=Passw0rd123 generatesql=1
[Completed]:
---- SQL ----
create database if not exists repi;
create table if not exists repi.ld_capture (
  id int auto_increment primary key,
  capture_id int not null,
  schema_name varchar(255) not null,
...

Remove the objects:

repcli remove database=source dbtype=mysql server=mysql-src.example.com port=3306 dbname=sales user=repuser password=Passw0rd123
[Completed]: Capture objects have been removed [mysql-src.example.com:3306,sales]

A required parameter that is missing, a parameter that MySQL does not have, and a server that cannot be reached:

repcli prepare database=source dbtype=mysql server=mysql-src.example.com
[Failed]: Missed port parameter
  port                   TCP port of the MySQL server. [1-65535; required]

repcli prepare database=source dbtype=mysql server=mysql-src.example.com port=3306 user=repuser password=Passw0rd123 housekeeping=1
[Failed]: An invalid parameter [housekeeping] has been supplied. It is a parameter of mssql, not of mysql.
Valid parameters for 'prepare database=source', database type mysql:
  ...

repcli validate database=source dbtype=mysql server=127.0.0.1 port=1 user=repuser password=Passw0rd123
[Invalid]: dial tcp 127.0.0.1:1: connect: connection refused

What the Commands Do

prepare creates the database repi with two tables: ld_capture, the list of the tables of the Initial Load, and ValidateTables, the tasks of the Data Comparison. The command can be run again: what exists is left as it is. The tables that are replicated are not changed, and the command creates no login and grants no privileges (see Database Privileges). With generatesql=1 no connection is made.
validate connects to the server, checks the settings of the table above, checks that the login has the privileges REPLICATION SLAVE and REPLICATION CLIENT, and checks that the table repi.ld_capture exists. The privileges are read with show grants. The check does not fail when it cannot tell, for example when the privileges come from roles; the Capture Process then reports the error of the server. When repi.ValidateTables does not exist the result is valid and carries a warning: only the Data Comparison needs the table.
remove drops the tables repi.ld_capture and repi.ValidateTables and then the database repi, unless something else is in it (the objects of an Apply Process of the same server stay). The tables that are replicated are not touched. Stop the Capture Process first. Removing a Capture Process (see Remove Process) deletes only its own rows in repi.ld_capture.

The messages of validate and what to do about them are listed in Capture and Source Databases. The parameters of the Capture Process are described in MySQL Capture Parameters.

Web UI

MySQL is offered as a Source in Repstance Advanced Edition only. Click “Add Capture” on the dashboard, enter the id and the name of the process and click the logo of MySQL. The form “Database Settings” has the fields “Server”, “Port” (3306 is entered), “Username”, “Password” and “Database Name” (the list shows the databases of the server). The settings of SQL Server (“Housekeeping”, “Secondary” and “Full DDL Capture”) do not apply to MySQL. “Test connection” is validate. The buttons “Prepare for Replication” and “Show Prepare SQL” appear after it and work as described in Prepare Source Database.

Oracle as Source

PostgreSQL as Source