Requirements for MySQL and MariaDB

From HVR
Jump to: navigation, search

  Since    v5.2.3/15  
This section describes the requirements, access privileges, and other features of HVR when using MySQL/MariaDB/Aurora MySQL for replication. For information about the capabilities supported by HVR on MySQL, see Capabilities for MySQL. For information about the capabilities supported by HVR on MariaDB, see Capabilities for MariaDB. For information about the capabilities supported by HVR on Aurora MySQL, see Capabilities for Aurora MySQL.

For information about compatibility and supported versions of MySQL/MariaDB with HVR platforms, see Platform Compatibility Matrix.

MySQL/MariaDB/Aurora
Capture Hub Integrate
Icon-Yes.png Icon-Yes.png Icon-Yes.png

Location Connection

This section lists and describes the connection details required for creating MySQL/MariaDB/Aurora MySQL location in HVR. HVR connects to the MySQL/MariaDB/Aurora MySQL server using the TCP protocol.

SC-Hvr-Location MySQL.png
Field Description
Node The hostname or IP-address of the machine on which the MySQL/MariaDB server is running.
  Example: 192.168.127.129
Port The TCP port on which the MySQL/MariaDB/Aurora MySQL server is expecting connections.
  Example: 3306
Database The name of the MySQL/MariaDB/Aurora MySQL database.
  Example: mytestdb
User The username to connect HVR to MySQL/MariaDB/Aurora MySQL Database.
  Example: hvruser
Password The password of the User to connect HVR to MySQL/MariaDB/Aurora MySQL Database.


HUB

HVR allows you to create hub database in MySQL/MariaDB/Aurora MySQL. The hub database is a small database which HVR uses to control its replication activities. This database stores HVR catalog tables that hold all specifications of replication such as the names of the replicated databases, the list of replicated tables, and the replication direction.

Grants for Hub

To capture changes from source database or to integrate changes into target database the HVR hub database User (for example, hvruser) requires the following grants:

  • Permission to create and drop HVR catalog tables.

Capture

  Since    v5.3.1/13  
HVR supports capturing changes from MySQL database (includes regular MySQL, Amazon RDS for MySQL, and Aurora MySQL). The default value for Capture/LogReadMethod is SQL.

Note: From HVR 5.3.1/13 to HVR 5.3.1/20, capturing changes from MySQL using the DIRECT connection method is not available. Because of this behavior, Capture/LogReadMethod is not available for MySQL.

Grants for Capture

To capture changes from MySQL the User (for example, hvruser) requires the following grants:

grant replication client on *.* to 'hvruser'@'%' ;
grant replication slave on *.* to 'hvruser'@'%' ;
grant select on *.* to 'hvruser'@'%' ;

Binary Logging

In MySQL, the transaction updates are recorded in the binary logs. For HVR to capture changes, the binary logging should be configured in MySQL database. MySQL allows you to define system variables (parameters) at server level (global) and at session level. The configuration for binary logging should be strictly defined as mentioned in this section. Defining parameters not mentioned in this section can lead to HVR not capturing changes. For more information about binary logging, search for "binary logging" in MySQL Documentation.

Note: If binary logging is not enabled in MySQL, an error like the following is displayed in HVR: "hvrinit: F_JD0AC8: The 'SHOW MASTER STATUS' command returned no results. Please check that the binary logging is enabled for the source database. F_JR0015: The previous error was detected during generation of objects for channel hvr_demo51. It is possible that objects have been left incomplete.".

Binary Logging for Regular MySQL

The following parameters should be defined in MySQL configuration file my.cnf (Unix) or my.ini (Windows):

  • log_bin=ON - to enable binary logging in MySQL.
  • binlog_format=ROW - to set the binary logging format.
  • binlog_row_image=full or binlog_row_image=noblob - to determine how row images are written to the binary log.
  • Note: binlog_row_image=minimal is not supported in HVR.

Binary Logging for Amazon RDS for MySQL and Aurora MySQL

This section provides information required for configuring binary logging in Amazon RDS for MySQL and Aurora MySQL database.

  1. To enable binary logging, perform the steps mentioned in https://aws.amazon.com/premiumsupport/knowledge-center/enable-binary-logging-aurora/ . While performing the steps to enable binary logging, the following parameters should be defined:
    • binlog_format=ROW - to set the binary logging format.
    • binlog_checksum=CRC32 or binlog_checksum=NONE - to enable or disable writing a checksum for each event in the binary log.
  2. For Aurora MySQL, the cluster should be restarted after enabling the binary logging. The replication will begin only after restarting the cluster.

Refresh and Integrate

HVR supports refresh and integrate for MySQL database (includes regular MySQL, Amazon RDS for MySQL, and Aurora MySQL).

Grants for Integrate and Refresh Target

To integrate changes into MySQL the User (for example, hvruser) requires the following grants:

  • Permission to read and change replicated tables.
  • grant select, insert, update, delete on tbl to hvruser
    
  • Permission to create and drop HVR state tables.

Grants for Bulk Load

The two options available to use bulk load during Refresh or Integrate in MySQL/MariaDB are:

  1. Direct loading by the MySQL/MariaDB server. The following conditions should be satisfied to use this option:
    • The User should have FILE permission.
    • The system variable (of MySQL/MariaDB server) secure_file_priv should be set to "" (blank).
  2. Initial loading by the MySQL/MariaDB client followed by MySQL/MariaDB server. The following condition should be satisfied to use this option:
    • The system variable (of MySQL/MariaDB client and server) local_infile should be enabled.

Grants for HVR Compare and HVR Refresh(Source Database)

To perform HVR Compare or HVR Refresh(in Source Database), the User (for example, hvruser) requires the following grant to read replicated tables:

grant select on tbl to hvruser