For replication between MySQL hosts, you must configure each MySQL database server to support the required user names and core MySQL configuration.
For MySQL extraction, Tungsten Clustering must have write access to the database so that status and progress information can be recorded correctly.
Native MySQL replication should not be running when you install Tungsten Clustering™. The replication service will be completely handled by Tungsten Clustering™, and the normal replication, management and monitoring techniques will not provide you with the information you need.
|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. JSON datatype support only available in Tungsten Replicator v5.3 onwards|
|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. JSON datatype support only available in Tungsten Replicator v5.3 onwards|
|MariaDB||5.5, 10.0, 10.1||Primary platform|
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.
tungsten user must be
able to read the MySQL configuration file (for installation) and the
binary logs. Either the
user should be a member of the appropriate group (i.e.
mysql), or the permissions altered
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:
By default, MySQL is configured only to listen on the localhost
address (127.0.0.1). The
should be checked to ensure that it is either set to a valid
value, or commented to allow listening on all available network
#bind-address = 127.0.0.1
Specify the server id
Each server must have a unique server id:
server-id = 1
The best practice is for all servers to have a unique ID across all clusters. For example, use a numbering scheme like 0101, 0102, 0201, 0201, where the leading two digits are the cluster number and the last two digits are the node number, which allows for 99 participating clusters with 99 nodes each.
(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
parameter to 1 (one).
In MySQL 5.7, the default value is 1.
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
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
Tungsten Clustering 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 Tungsten Clustering.
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
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 Tungsten Clustering™ 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 Tungsten Clustering™ in a multi-master,
multi-site, fan-in or data critical cluster, the value of
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 Tungsten Clustering™ 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 Tungsten Clustering™ installations,
and should be the setting for all configurations that do not
At a minimum
should be set to
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
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 Redo Log file size is 48MB. This should be increased to a larger file size for performance and other reasons. Values of 512MB are common.
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
In MySQL 5.7, the default format is
For temporary changes during execution of explicit statements, the binlog format can be changed by executing the following statement:
SET binlog-format = ROW;
You must restart MySQL after any changes have been made.
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.
If you are inserting to the same table at the same time at two or more different sites, and using bi-directional or multimaster replication, then special care must be taken to avoid primary key conflicts. Either the auto-increment keys on each need to be offset so they do not conflict, or the application needs to be be able to generate unique keys taking multiple sites into account.
The following configuration is required if your application is relying upon the MySQL-native auto-increment primary key feature:
auto-increment-offset variables to affect the way that
MySQL generates the next value in an auto-increment field.
For example, edit
my.cnf on all
# for all servers at site 1
auto-increment-increment = 10 auto-increment-offset = 1
# for all servers at site 2
auto-increment-increment = 10 auto-increment-offset = 2
# for all servers at site 3
auto-increment-increment = 10 auto-increment-offset = 3
Restart MySQL on all servers.
The following are required for replication to heterogeneous targets to ensure that MySQL has been configured and generating row change information correctly:
MySQL must be using Row-based replication for information to be
replicated to heterogenous targets. For the best results, you should change the
global binary log format, ideally in the configuration file
binlog-format = row
Alternatively, the global binlog format can be changed by executing the following statement:
SET GLOBAL binlog-format = ROW;
For MySQL 5.6.2 and later, you must enable full row log images:
binlog-row-image = full
This information will be forgotten when the MySQL server is restarted;
placing the configuration in the
my.cnf file will ensure this
option is permanently enabled.
Table format should be updated to UTF8 by updating the MySQL
Tables must also be configured as UTF8 tables, and existing tables should be updated to UTF8 support before they are replicated to prevent character set corruption issues.
To prevent timezone configuration storing zone adjusted values and
exporting this information to the binary log and PostgreSQL, fix the
timezone configuration to use UTC within the configuration file
Tungsten User Login
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:
To create a user with suitable privileges:
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
create the user with an explicit host reference:
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:
CREATE USER tungsten@'client1' IDENTIFIED BY 'password';mysql>
GRANT ALL ON *.* TO tungsten@'client1' WITH GRANT OPTION;
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.
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 Tungsten Clustering™
As noted above, the creation of explicit host-specific user entries may be required.