B.3. MySQL Database Setup

For replication between MySQL hosts, you must configure each MySQL database server to support the required user names and core MySQL configuration.

Note

Native MySQL replication should not be running when you install Continuent Tungsten™. The replication service will be completely handled by Continuent Tungsten™, and the normal replication, management and monitoring techniques will not provide you with the information you need.

B.3.1. MySQL Version Support

Database Version Support Status Notes
MySQL 5.0, 5.1, 5.5, 5.6, 5.7 Primary platform Statement and row based replication is supported. MyISAM and InnoDB table types are fully supported; MyISAM tables may introduce replication errors during failover scenarios. The JSON datatype is not supported.
Percona 5.5, 5.6, 5.7 Primary platform Statement and row based replication is supported. MyISAM and InnoDB table types are fully supported; MyISAM tables may introduce replication errors during failover scenarios. The JSON datatype is not supported.
MariaDB 5.5, 10.0 Primary platform  

B.3.2. MySQL Configuration

Each MySQL Server should be configured identically within the system. Although binary logging must be enabled on each host, replication should not be configured, since Tungsten Replicator will be handling that process.

The configured tungsten user must be able to read the MySQL configuration file (for installation) and the binary logs. Either the tungsten user should be a member of the appropriate group (i.e. mysql), or the permissions altered accordingly.

Important

Parsing of mysqld_multi configuration files is not currently supported. To use a mysqld_multi installation, copy the relevant portion of the configuration file to a separate file to be used during installation.

To setup your MySQL servers, you need to do the following:

  • Configure your my.cnf settings. The following changes should be made to the [mysqld] section of your my.cnf file:

    • By default, MySQL is configured only to listen on the localhost address (127.0.0.1). The bind-address parameter should be checked to ensure that it is either set to a valid value, or commented to allow listening on all available network interfaces:

      #bind-address = 127.0.0.1
    • Specify the server id

      Each server must have a unique server id:

      server-id = 1
    • (Optional) Reconfigure the default MySQL TCP/IP port

      Change the listening port to 13306. The Tungsten Connector will listen on the normal port 3306 for MySQL connections and send them to the database using port 13306.

      port = 13306

      If you are not using Tungsten Connector, the setting can remain at the default of 3306.

    • Ensure that the maximum number of open files matches the configuration of the database user. This was configured earlier at 65535 files.

      open_files_limit = 65535
    • Enable binary logs

      Tungsten Replicator operates by reading the binary logs on each machine, so logging must be enabled:

      log-bin = mysql-bin
    • Set the sync_binlog parameter to 1 (one).

      Note

      In MySQL 5.7, the default value is 1.

      The MySQL sync_binlog parameter sets the frequency at which the binary log is flushed to disk. A value of zero indicates that the binary log should not be synchronized to disk, which implies that only standard operating system flushing of writes will occur. A value greater than one configures the binary log to be flushed only after sync_binlog events have been written. This can introduce a delay into writing information to the binary log, and therefore replication, but also opens the system to potential data loss if the binary log has not been flushed when a fatal system error occurs.

      Setting a value of value 1 (one) will synchronize the binary log on disk after each event has been written.

      sync_binlog = 1
    • Increase MySQL protocol packet sizes

      The replicator can apply statements up to the maximum size of a single transaction, so the maximum allowed protocol packet size must be increase to support this:

      max_allowed_packet = 52m
    • Configure InnoDB as the default storage engine

      Continuent Tungsten needs to use a transaction safe storage engine to ensure the validity of the database. The InnoDB storage engine also provides automatic recovery in the event of a failure. Using MyISAM can lead to table corruption, and in the event of a switchover or failure, and inconsistent state of the database, making it difficult to recover or restart replication effectively.

      InnoDB should therefore be the default storage engine for all tables, and any existing tables should be converted to InnoDB before deploying Continuent Tungsten.

      default-storage-engine = InnoDB
    • Configure InnoDB Settings

      Tungsten Replicator creates tables and must use InnoDB tables to store the status information for replication configuration and application:

      The MySQL option innodb_flush_log_at_trx_commit configures how InnoDB writes and confirms writes to disk during a transaction. The available values are:

      • A value of 0 (zero) provides the best performance, but it does so at the potential risk of losing information in the event of a system or hardware failure. For use with Continuent Tungsten™ the value should never be set to 0, otherwise the cluster health may be affected during a failure or failover scenario.

      • A value of 1 (one) provides the best transaction stability by ensuring that all writes to disk are flushed and committed before the transaction is returned as complete. Using this setting implies an increased disk load and so may impact the overall performance.

        When using Continuent Tungsten™ in a multi-master, multi-site, fan-in or data critical cluster, the value of innodb_flush_log_at_trx_commit should be set to 1. This not only ensures that the transactional data being stored in the cluster are safely written to disk, this setting also ensures that the metadata written by Continuent Tungsten™ describing the cluster and replication status is also written to disk and therefore available in the event of a failover or recovery situation.

      • A value of 2 (two) ensures that transactions are committed to disk, but data loss may occur if the disk data is not flushed from any OS or hardware-based buffering before a hardware failure, but the disk overhead is much lower and provides higher performance.

        This setting must be used as a minimum for all Continuent Tungsten™ installations, and should be the setting for all configurations that do not require innodb_flush_log_at_trx_commit set to 1.

      At a minimum innodb_flush_log_at_trx_commit should be set to 2; a warning will be generated if this value is set to zero:

      innodb_flush_log_at_trx_commit = 2

      MySQL configuration settings can be modified on a running cluster, providing you switch your host to maintenance mode before reconfiguring and restarting MySQL Server. See Section 5.14, “Performing Database or OS Maintenance”.

    Optional configuration changes that can be made to your MySQL configuration:

    • InnoDB Flush Method

      innodb_flush_method=O_DIRECT

      The InnoDB flush method can effect the performance of writes within MySQL and the system as a whole.

      O_DIRECT is generally recommended as it eliminates double-buffering of InnoDB writes through the OS page cache. Otherwise, MySQL will be contending with Tungsten and other processes for pages there — MySQL is quite active and has a lot of hot pages for indexes and the like this can result lower i/o throughput for other processes.

      Tungsten particularly depends on the page cache being stable when using parallel apply. There is one thread that scans forward over the THL pages to coordinate the channels and keep them from getting too far ahead. We then depend on those pages staying in cache for a while so that all the channels can read them — as you are aware parallel apply works like a bunch of parallel table scans that are traveling like a school of sardines over the same part of the THL. If pages get kicked out again before all the channels see them, parallel replication will start to serialize as it has to wait for the OS to read them back in again. If they stay in memory on the other hand, the reads on the THL are in-memory, and fast. For more information on parallel replication, see Section 4.1, “Deploying Parallel Replication”.

    • Increase InnoDB log file size

      The default InnoDB log file size is 5MB. This should be increased to a larger file size, due to a known issue with xtrabackup during backup and restore operations.

      To change the file size, read the corresponding information in the MySQL manual for configuring the file size information. See MySQL 5.1, MySQL 5.5, MySQL 5.6, MySQL 5.7.

    • Binary Logging Format

      Tungsten Replicator works with both statement and row-based logging, and therefore also mixed-based logging. The chosen format is entirely up to the systems and preferences, and there are no differences or changes required for Tungsten Replicator to operate. For native MySQL to MySQL master/slave replication, either format will work fine.

      Depending on the exact use case and deployment, different binary log formats imply different requirements and settings. Certain deployment types and environments require different settings:

      • For multi-master deployment, use row-based logging. This will help to avoid data drift where statements make fractional changes to the data in place of explicit updates.

      • Use row-based logging for heterogeneous deployments. All deployments to Oracle, MongoDB, Vertica and others rely on row-based logging.

      • Use mixed replication if warnings are raised within the MySQL log indicating that statement only is transferring possibly dangerous statements.

      • Use statement or mixed replication for transactions that update many rows; this reduces the size of the binary log and improves the performance when the transaction are applied on the slave.

      • Use row replication for transactions that have temporary tables. Temporary tables are replicated if statement or mixed based logging is in effect, and use of temporary tables can stop replication as the table is unavailable between transactions. Using row-based logging also prevents these tables entering the binary log, which means they do not clog and delay replication.

      The configuration of the MySQL server can be permanently changed to use an explicit replication by modifying the configuration in the configuration file:

      binlog-format = row

      Note

      In MySQL 5.7, the default format is ROW.

      For temporary changes during execution of explicit statements, the binlog format can be changed by executing the following statement:

      mysql> SET binlog-format = ROW;

    You must restart MySQL after any changes have been made.

  • Ensure the tungsten user can access the MySQL binary logs by either opening up the directory permissions, or adding the tungsten user to the group owner for the directory.

B.3.3. MySQL User Configuration

  • Tungsten User Login

    The tungsten user connects to the MySQL database and applies the data from the replication stream from other datasources in the dataservice. The user must therefore be able execute any SQL statement on the server, including grants for other users. The user must have the following privileges in addition to privileges for creating, updating and deleting DDL and data within the database:

    • SUPER privilege is required so that the user can perform all administrative operations including setting global variables.

    • GRANT OPTION privilege is required so that users and grants can be updated.

    To create a user with suitable privileges:

    mysql> CREATE USER tungsten@'%' IDENTIFIED BY 'password';
    mysql> GRANT ALL ON *.* TO tungsten@'%'  WITH GRANT OPTION;

    The connection will be made from the host to the local MySQL server. You may also need to create an explicit entry for this connection. For example, on the host host1, create the user with an explicit host reference:

    mysql> CREATE USER tungsten@'host1' IDENTIFIED BY 'password';
    mysql> GRANT ALL ON *.* TO tungsten@'host1'  WITH GRANT OPTION;

    The above commands enable logins from any host using the user name/password combination. If you want to limit the configuration to only include the hosts within your cluster you must create and grant individual user/host combinations:

    mysql> CREATE USER tungsten@'client1' IDENTIFIED BY 'password';
    mysql> GRANT ALL ON *.* TO tungsten@'client1'  WITH GRANT OPTION;

    Note

    If you later change the cluster configuration and add more hosts, you will need to update this configuration with each new host in the cluster.

  • MySQL Application Login

    Tungsten Connector requires a user that can be used as the application user to connect to the MySQL server. The login will allow connections to the MySQL databases servers to be used in a consistent fashion across different hosts within the cluster. You must configure this user with access to your database, and then use it as the 'application' user in your cluster configuration.

    mysql> CREATE USER app_user@'%' IDENTIFIED BY 'password!';
    mysql> GRANT ALL ON *.* TO app_user@'%';
    mysql> REVOKE SUPER ON *.* FROM app_user@'%';

    Additional application user logins can be configured by using the user.map file within your Continuent Tungsten™ configuration.

    As noted above, the creation of explicit host-specific user entries may be required.