Requirements
Before starting you need to ensure you have the following:
MySQL 5.6 or later.
Ability to set up the MySQL server to use row-level binary logging.
Provision a database user with appropriate privileges for PassKit to connect with.
Introduction
The PassKit MySQL Data Connector can monitor and record all of the row-level changes in the databases on a MySQL server or HA MySQL cluster. The first time it connects to a MySQL server/cluster, it reads a consistent snapshot of all of the databases it has access to. When that snapshot is complete, the connector continuously reads the changes that were committed to MySQL 5.6 or later and generates corresponding events that are consumed by the PassKit platform.
How it works
MySQL’s binary log, or binlog, records all operations in the same order they are committed by the database, including changes to the schemas of tables or changes to data stored within the tables. MySQL uses its binlog for replication and recovery.
The PassKit MySQL Data Connector reads MySQL’s binary log to understand what, and in what order, data has changed. It then produces a change event for every row-level insert, update, and delete into the PassKit platform. PassKit processes the events for tables that it is set up to listen to, and takes appropriate action (i.e. create, update or delete records in the PassKit system).
MySQL is usually set up to purge the binary logs after some period of time. This means that the binary log won’t have the complete history of all changes that have been made to the database. Therefore, when the MySQL connector first connects to a particular MySQL server or cluster, it starts by performing a consistent snapshot of each of the databases. When the connector completes the snapshot, it then starts reading the binlog from the exact point at which the snapshot was made. This way, PassKit starts with a consistent view of all of the data, yet continues reading without having lost any of the changes made while the snapshot was being made.
The connector is also very tolerant of failures. As the connector reads the binlog and produces events, it records the binlog position with each event. If the connector stops for any reason (including communication failures, network problems, or crashes), upon restart it simply continues reading the binlog where it last left off. This includes snapshots: if the snapshot was not completed when the connector is stopped, upon restart it will begin a new snapshot.
Set up
Before the PassKit MySQL Data Connector can be used to monitor the changes committed on a MySQL server, the server must be set up to use row-level binary logging and have a database user with appropriate privileges. If MySQL is configured to use global transaction identifiers (GTIDs), then the connector can more easily reestablish connection should one of the MySQL servers fail.
The following sections outline in more detail how to set up these features in MySQL.
Enabling the binlog
The MySQL server must be configured to use a row-level binary log, which is described in more detail in the MySQL documentation. This is most often done in the MySQL server configuration file, and will look similar to the following fragment:
#server-id = 223344
#log_bin = mysql-bin
#binlog_format = row
#binlog_row_image = full
#expire_logs_days = 10
Where:
the value for server-id must be unique for each server and replication client within the MySQL cluster. When we set up the connector, we’ll also assign the connector a unique server ID.
the value for log_bin is the base name for the sequence of binlog files.
the value for binlog_format must be set to row or ROW.
the value for binlog_row_image must be set to full or FULL.
the value for expire_log_days is the number of days for automatic binary log file removal. The default is 0, which means "no automatic removal," so be sure to set a value that is appropriate for your environment.
Enabling GTIDs (optional)
The MySQL server can be configured to use GTID-based replication. Global transaction identifiers, or GTIDs, were introduced in MySQL 5.6.5, and they uniquely identify a transaction that occurred on a particular server within a cluster. Using GTIDs greatly simplifies replication and makes it possible to easily confirm whether masters and slaves are consistent. Note that if you’re using an earlier version of MySQL, you will not be able to enable GTIDs.
Enabling GTIDs can be done in the MySQL server configuration file, and will look similar to the following fragment:
Gtid_mode = on
Enforce_gtid_consistency = on
where:
the value for gtid_mode specifies the GTID mode of the MySQL server.
the value for enforce_gtid_consistency instructs the server to enforce GTID consistency by allowing execution of only those statements that can be logged in a transactionally safe manner and is required when using GTIDs.
Consult the MySQL documentation for details and specifics about setting up GTIDs.
The PassKit MySQL Data Connector does not require MySQL to use GTIDs and GTID-based replication. Each time the connector starts up, it will automatically detect whether it is enabled and adjust its behavior accordingly.
Create a MySQL user for the connector
A MySQL user must be defined that has all of the following permissions on all of the databases that the connector will monitor:
SELECT - enables the connector to select rows from tables in databases; used only when performing a snapshot.
RELOAD - enables the connector of the FLUSH statement to clear or reload various internal caches, flush tables, or acquire locks; used only when performing a snapshot.
SHOW DATABASES - enables the connector to see database names by issuing the SHOW DATABASE statement; used only when performing a snapshot.
REPLICATION SLAVE - enables the connector to connect to and read the binlog of its MySQL server; always required for the connector.
REPLICATION CLIENT - enables the use of SHOW MASTER STATUS, SHOW SLAVE STATUS, and SHOW BINARY LOGS; always required for the connector.
For example, the following statement grants these permissions for a user passkit that authenticates with the password pk123, connecting from IP 123.123.123.123, where the user is limited to all tables in database mydatabase:
GRANT SELECT, RELOAD, SHOW DATABASES, REPLICATION SLAVE, REPLICATION CLIENT ON mydatabase.* TO 'passkit'@'123.123.123.123' IDENTIFIED BY 'pk123';
The best practice is to have a dedicated database or table that you sync the data into that is required for the PassKit integration. You can then grant the PassKit user access to that database only.
Information to share with PassKit
Once the server is configured for the PassKit Data Connector. Please share the following details with your PassKit technical contact:
Database Hostname: the address of the MySQL server.
Database Port: the port number of the MySQL server.
Database User: the name of the MySQL user that has the required privileges.
Database Password: the password for the MySQL user that has the required privileges.
Server ID: the connector’s identifier that must be unique within the MySQL cluster and similar to MySQL’s server-id configuration property.