Home > Documentation > MySQL
Migrating database between different versions, platforms or DBMS is a complicated task to do. The procedure can lead to broken business logic and malfunction of important applications when doing it in a wrong way. The most popular reasons for migration is cost effectiveness, better performance and scalability of the target platform. This whitepaper is focused on the most popular open source tools that can be used for migration of MySQL, Percona and MariaDB databases.
These standard command line tools included in MySQL installation pack are designed to make logical backups of the entire database or particular tables. Logical backup is a set of SQL statements to create objects of the original database such as table definitions, data, indexes, constraints.
The most useful features of mysqldump are exposed via
--single-transaction and --master-data parameters.
First option starts new transaction before running the export that allows
to read the database in the current state at the time of the transaction.
This approach can help to make consistent data dump in concurrent
environment without locking the entire database.
The parameter --master-data produces dump file from
the source server that can be used to set up another server as a replica.
This options writes into the resulting dump file statement
CHANGE MASTER TO specifying the binary log file name and
position of the dumped server (coordinates where to start replicating
after the dump file is imported into the replica).
Being an alternative tool to producing logical backup, mysqlpump provides valuable advantage towards mysqldump: parallel processing of database to speed up the export. The weakness of mysqlpump is that it does not have a feature similar to --master-data of mysqldump.
The database can be restored from mysqldump or mysqlpump backups using the standard mysql client as follows:
shell> cat {path to backups}/backup_name.sql | mysql -u user_name -p
or
shell> mysql -u user_name -p < {path to backups}/backup_name.sql
To restore on the remote server, the -h {hostname} option must be specified:
shell> cat {path to backups}/backup_name.sql | mysql -u user_name -p -h {remote_hostname}
This is a universal GUI framework for managing MySQL, MariaDB or Percona databases. Product capacities cover numerous database migration tasks. MySQL Workbench can migrate not only between different MySQL versions and platforms, it also can be used for migration from PostreSQL, SQL Server, Microsoft Access, SQLite and other popular DBMS (except Oracle). Key features:
See Migrate to MySQL with MySQL Workbench article for further information.
Percona XtraBackup is a free open-source comprehensive database backup utility working with all versions of MySQL and Percona. It can handle non-blocking, compressed and highly secure full or partial backups based on transactional model. Due to that capability hi-load database systems remain fully available during backup and restore timeframe.
Hot online backup is very beneficial for database migration tasks since it essentially reduces duration of the process by moving data to the destination directly in streamed mode. The most straightforward way of using Percona XtraBackup for database migration is extracting the data from the source then streaming it to the destination.
MariaDB Backup is derived from Percona XtraBackup and so it has the same concept of backup and restore, implemented for MariaDB databases. Both products work in the Unix/Linux environment only.
phpMyAdmin is a free tool written in PHP to administrate of MySQL through the web interface. It supports a wide range of database management operations on MySQL, Percona and MariaDB:
One of the main phpMyAdmin advantages is local access to MySQL, Percona and MariaDB servers that decline remote connections (as web service running on the same machine).
Database migration can be a very sophisticated procedure, especially when it has to be implemented between different DBMS. Incorrect or insufficient migration may cause loosing data integrity and broken application flow. The person responsible for database migration must have strong expertise in this field. It is also reasonable to use special tools than can simplify some of database migration tasks or the overall process.
Have questions? Contact us