This section discusses steps and some of the best practices recommendations for migrating an application or set of applications that are currently deployed on MySQL database(s) over to Clustrix. Objective is to conduct the migration with a minimal amount of downtime.
- Migration Overview
- Migration Prerequisites
- Migration Steps
- Application Server Cutover
- Risk Mitigation
- Cutover Caveats
There are essentially three basic steps to achieve successful migration from MySQL environment:
- Dump the MySQL database with mysqldump and import into Clustrix with clustrix_import
- Use MySQL replication to sync up Clustrix with the production MySQL database
- Cut over application servers to Clustrix
In addition, there are several additional steps which may be taken to minimize risk
- Validate that Clustrix responds to all read queries appropriately
- Configure Clustrix slave to facilitate switching back to MySQL as a roll back process
In order to migrate your application from MySQL to Clustrix, the following must be true
- MySQL server has binary logging enabled (--log-bin and other supporting arguments)
SBR mode of replication in the migration process has the benefit of
validating that write queries are handled properly by Clustrix. However,
if the application workload characterized to be extremely busy and
exhibits write heavy transactions mostly, Clustrix recommends to use RBR
mode to achieve better throughput in replication.
- Check whether tables being migrated are innodb or MyISAM. MyISAM requires some special care to get a consistent dump. All tables must be locked or the database must be quiesced completely since MyISAM provides no transaction isolation.
Take a consistent dump of the database
is important to note that database dump would need to be taken using
mysqldump utility. No other existing backup methods e.g. LVM
snapshots, xtrabackup etc can be used for migrating the database.
mysqldumpcommand to be used for dumping MySQL database is provided below
Please note that --single_transaction argument is important in order to get a consistent snapshot from where to start the replication slave. Additionally, --master_data argument stores the binlog position corresponding to the snapshot in the dump file.
It is recommended to use screen to make sure that the session is not killed before the backup finishes (better than & and nohup alternative). For monitoring the dump and ensure successful completion, tail command may be used. Using tail on the dump file should show something like:
If the dump is incomplete or incorrect due to wrong usage of mysqldump arguments, usually lots of time is wasted before finding out that replication won’t work. Correctness of
mysqldump command is critical.
Import the database dump using clustrix_import utility
clustrix_importcommand to be used for importing the dump file is provided below
- A few best practices recommended for using
clustrix_importcommand line tool
- Use Screen
- tee the output
- Pay close attention to the final output indicating success or failure
has many advantages over mysql client in loading data as it imports
data in parallel, taking maximal advantage of cluster resources. This
tool is also designed to optimally distribute the data across all
Clustrix nodes and automatically retries around transient error
Migrate Permissions with clustrix_clone_users
- mysqldump --all-databases will dump the mysql database but Clustrix cannot use this data to instantiate users
clustrix_clone_usersinstead which is available from the support site (support.clustrix.com)
clustrix_clone_usersutility will query a MySQL (or Clustrix) database to dump the users and permissions, generating SQL which can then be imported. Example of using clustrix_clone_users utility is provided below:
Start Replication Slave on Clustrix
Once import is complete, slave can be started using following command:
The proper log file and position are obtained from the beginning of the mysqldump (as generated by
Please refer to Configuring Replication module for obtaining information on monitoring slave status.
Application Server Cutover
Methods to cut over application servers
There are three common methods for switching app servers from MySQL to Clustrix:
- Reconfiguring application servers to point to Clustrix instead of MySQL
- MySQL server changes to new IP, and Clustrix VIP (Appliance only) takes over MySQL’s IP'
- Using an external load balancer e.g. HAProxy to direct traffic to Clustrix instead of MySQL. Please refer Load Balancing Clustrix with HAProxy section to configure HA proxy for load balancing the cluster.
Validating Clustrix Compatibility
To reduce any surprises during cutover, ensure that Clustrix properly handles all queries generated by the application
- To validate write
statements, SBR mode of replication ensures Clustrix slave's ability to
handle write queries. Although RBR will provide better performance for
write heavy workloads, it is recommended to use SBR initially for
validation of write queries. SBR mode also comes handy in
troubleshooting replication issues during initial adoption. Once all
statements are validated, SBR mode can be converted to RBR for better
- To validate read statements, SQL queries may be captured either using tcpdump utility or enabling full query logging. For tcpdump output, Clustrix support can help converting it into valid SQL sessions. Thereafter, these queries can be replayed using MySQL client for validation. To test performance with concurrency using SQL sessions obtained from tcpdump, stest utility may be used. Clustrix support can help during initial stest setup and execution.
Enable failing back to MySQL
The ability to switch back to MySQL greatly minimizes the risk of impact to production. Configuring MySQL to slave from Clustrix beforehand ensures a smooth transition should the need arise.
Steps to fail over are discussed under Configuring Replication Failover section and also outlined below:
Retain privileges for all application logins on the MySQL slave instance, but keep the instance read only, by setting read_only global
Enable binlogging on Clustrix, ensuring format is same as MySQL master. To enable failing back to MySQL, the binlog must be created on Clustrix before application writes are allowed.
application servers have been cut over to Clustrix, the slave on
Clustrix (from MySQL master) can be stopped. Alternatively,
bi-directional, or master-master replication can be configured, where
Clustrix continues to replicate from MySQL, while MySQL also replicates
from Clustrix. This is a more complex configuration, with some
caveats, as discussed in Configuring Replication Failover.
Configure MySQL as a slave from Clustrix, using MySQL's
CHANGE MASTER TOsyntax, specifying the beginning (position 4) of the binlog created in step 2.
Reverting to MySQL
In the even that it becomes necessary to revert to MySQL, given the steps above have been taken, the following steps are necessary:
Change Clustrix to read only mode:
Ensure slave has caught up by comparing binlog file and position shown by
SHOW MASTER STATUSon Clustrix and
SHOW SLAVE STATUSon MySQL
- Recreate or re-enable Clustrix slave from MySQL, specifying current binlog file and position shown in MySQL's
SHOW MASTER STATUS.
Enable MySQL to take writes again:
Application servers can now be pointed back to MySQL.
Best practice during cutover
Configure read-only on the inactive side (slave and root users are exempted):
Make sure that replication is caught up before cutting over. If Clustrix is significantly behind, an auto-increment INSERT coming from the newly cut-over app server will conflict with a prior INSERT in the replication stream. In order to avoid getting into such issues, it may be necessary to quiesce the MySQL server for some period of time to ensure that the Clustrix slave is caught up before cutting over.
Post cutover there could still be some applications or scripts that are still attempting to write to the MySQL database. These would be either failing as MySQL instance is set to read_only mode or silently manipulating data as root. Example of such possibilities could be admin application deployed locally on MySQL server or some of the database admin kind of shell scripts that are executed locally as root by the DBAs and manipulates data. It is necessary to review and migrate those scripts to Clustrix instance going forward as otherwise it would introduce data mismatch between Clustrix and MySQL