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) to Xpand.
There are essentially three basic steps to achieve successful migration from MySQL environment:
There are several additional steps which may be taken to minimize risk:
In order to migrate your application from MySQL to Xpand, the following must be true:
mysql> mysqldump -u user -h mysql_host --single-transaction --master-data=2 --all-databases > mydumpfile.dump |
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 the linux screen window manager to insure that the session is not killed before the backup finishes (better than the & and nohup alternative). For monitoring the dump and ensure successful completion, the tail command may be used. Using tail on the dump file should show something like:
-- Dump completed on 2016-08-02 19:50:56 |
If the dump is incomplete or incorrect due to wrong usage of mysqldump arguments, lots of time may be wasted before finding out that replication won’t work. Correctness of mysqldump command is critical.
shell> clustrix_import -i dumpfile.sql -H clustrix_ip |
- Use screen
- tee the output
- Pay close attention to the final output indicating success or failure
clustrix_import has many advantages over mysql client in loading data as it imports data in parallel, taking full advantage of cluster resources. This tool is also designed to optimally distribute the data across all Xpand nodes and automatically retries transient errors.
clustrix_clone_users utility will query a MySQL (or Xpand) database to dump the users and permissions, generating SQL which can then be imported, per this example.
shell> ./clustrix_clone_users -H localhost > /tmp/grants.sql shell> head /tmp/grants.sql -- -- Clustrix Users dumpfile ver: 113:82f8694c98db -- Host: localhost -- GRANT ALL PRIVILEGES ON *.* TO 'mysql_slave'@'' WITH GRANT OPTION; GRANT ALL PRIVILEGES ON *.* TO 'clustrix_ui'@'127.0.0.1' IDENTIFIED BY PASSWORD '*46A23F3EF4B5568CD0D6951239A0345A78DDF61A' WITH GRANT OPTION; GRANT ALL PRIVILEGES ON `statd`.* TO 'statd'@'%' IDENTIFIED BY PASSWORD '*58D9255AEB513581F38430D559A1455461E6B74E'; shell> mysql -h mogwai -u root < /tmp/grants.sql |
Once the import is complete, the slave can be created on Xpand using the following command:
sql> CREATE SLAVE 'slave1' MASTER_LOG_FILE = 'foo.000001' , MASTER_LOG_POS = 4 , MASTER_HOST = 'host_name' , MASTER_USER = 'user_name' , MASTER_PASSWORD = 'password'; sql> START SLAVE 'slave1'; |
The proper log file and position are obtained from the beginning of the mysqldump (as generated by --master-data=2 argument).
Please refer to Configuring Replication module for obtaining information on monitoring slave status.
There are two common methods for switching app servers from MySQL to Xpand:
To reduce cutover surprises, ensure that Xpand properly handles all queries generated by the application
The ability to switch back to MySQL greatly minimizes the risk of impact to production. Configuring MySQL to slave from Xpand 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:
slave> SET GLOBAL read_only = true; |
Enable binlogging on Xpand, ensuring format is same as MySQL master. To enable failing back to MySQL, the binlog must be created on Xpand before application writes are allowed.
CREATE BINLOG binlog_name [format='row'] |
In the event that it becomes necessary to revert to MySQL, given the steps above have been taken, the following steps are necessary:
Change Xpand to read only mode:
master> SET GLOBAL read_only = true; |
Enable MySQL to take writes again:
slave> SET GLOBAL read_only = false; |
Configure read-only on the inactive side (slave and root users are exempted):
sql> SET GLOBAL read_only = true; |
Make sure that replication is caught up before cutting over. If Xpand 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 Xpand 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. Examples 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 Xpand instance going forward as otherwise it would introduce data mismatch between Xpand and MySQL