Tutorials How to migrate MySQL DB to UpCloud Managed Databases

How to migrate MySQL DB to UpCloud Managed Databases

Migrating computer services is always a daunting task and moving over databases with business-critical data can be doubly so. However, with just a few simple steps, you can safely migrate a MySQL database to UpCloud Managed Databases.

Moving your MySQL databases to Managed Databases will take away the need for manual maintenance. In this tutorial, we’ll show you the tools, steps and requirements to make the migration easy and painless.

Pre-requisites to migrating MySQL

1. New Managed Database

Start by setting up a new Managed Database cluster you are migrating into. You will need to choose a plan with enough storage capacity for your existing database.

2. Host for the migration

You will also need a host system that can facilitate the migration. While in principle, you could export your database on almost any computer, the storage capacity and network speeds might make it impractical in most cases.

Managed Databases perform the best serving a Cloud Server over the Private network within the same data centre which will make importing much quicker.

Create a new Cloud Server to export and import your MySQL database.

3. Database tools

Once you have a Cloud Server up and running, you are almost ready to start migrating. However, you still need to install the tools that will do the heavy lifting.

Mysqldump is a common MySQL client utility and database backup program that performs logical backups. It is used to produce a set of SQL statements that when executed will reproduce the original database object definitions and table data. It can be used to dump one or more MySQL databases for backup or to migrate to a new SQL server.

MySQL client is a popular command-line tool for manual input editing capabilities. In addition to offering interactive query options, the MySQL client can be used to import the data dump into the new Managed Database.

You can install both of these with one of the following commands.

# Debian or Ubuntu
sudo apt install mysql-client

# CentOS
sudo dnf install mysql-client

Additionally, it’s important to note that the migration steps will take some time, especially with larger databases. And while mysqldump includes verbose output options, MySQL client doesn’t provide progress status. Therefore, before starting the migration, you should install a pipe viewer which can be used to keep tabs on the transfer processes.

# Ubuntu or Debian
sudo apt install pv

# CentOS
sudo dnf install pv

With the pre-requisites all set, you are ready to start migrating!

Exporting data from your old database

When you’ve set up your new Managed Database and a Cloud Server to facilitate the migration, you can start by taking a backup of your old MySQL database. This is done using the mysqldump command-line tool to create a single-file backup.

Note that the migration process will take some time. You should stop any applications from modifying the database during the migration which can create some downtime to your services. To help estimate the downtime caused by the migration, you should do a practice run of the migration before committing to the move.

Create a backup of your MySQL database by running the command below. Replace the database name, hostname and username with those corresponding to your old database. You may also need to change the port number if your database doesn’t use the default port.

mysqldump --database databasename(s) -h source.db.hostaddress.com -P 3306 -u username -p --single-transaction --set-gtid-purged=OFF --hex-blob | pv > mydb_export.sql

The command will export the database into a file mydb_export.sql in the directory it is executed in.

  • databasename(s) is used to define the names of the databases you want to export.
    If you have multiple databases, you can split the task and migrate them individually or migrate them together with --databases db1 db2 etc.
  • -h source.db.hostaddress.com defines the database host you want to export.
    Note that if your old database is not in the same UpCloud data centre as the Cloud Server used for the migration, you’ll need to allow connection over the public network.
  • -P 3306 sets the port number used to connect to the database host. Change it as needed according to your old database connection settings.
  • -u username -p options are used to pass the credentials needed to access the database.
    Note that it is insecure to include the password in the command itself. Rather you will be prompted to enter your password when running the command.
  • --single-transaction option is used to start a transaction before running the export instead of locking the entire database.
    This way mysqldump can read the database in its current state at the time of the transaction which makes the data dump consistent. Note that only InnoDB tables are dumped in a consistent state using this option. For example, any MyISAM or MEMORY tables may still change state while exporting using this option.
  • --set-gtid-purged=OFF option should be set if you are using Global Transaction Identifiers (GTID) so that the target server records these transactions as applied.
    For a server where GTIDs are not in use, use the AUTO option. Only use this option for a server where GTIDs are in use if you are sure that the required GTID set is already present in gtid_purged on the target server and should not be changed, or if you plan to identify and add any missing GTIDs manually.

Importing data to Managed Databases

After you have exported your MySQL database to the Cloud Server, you can then begin importing the data to your new Managed Database.

pv mydb_export.sql | mysql -h target.db.upclouddatabases.com -P 11550 -u upadmin -p

The MySQL client command used to import the data has mostly the same parameters as the mysqldump in the previous section. Below is a quick recap of the parameters you will need to set.

  • -h target.db.upclouddatabases.com defines the database host.
  • -P 11550 sets the port number used to connect to the database host.
  • -u upadmin -p options are used to pass the credentials needed to access the database.
    You will be prompted to enter your password when running the command. The password for your upadmin account can be found at your UpCloud Control Panel.

Recreating user accounts

Having finished migrating over your MySQL databases to the UpCloud Managed Databases, you are almost done. However, you will still need to recreate the user accounts used to access your databases by your applications.

You can check the list of users in your old database by connecting with the MySQL client and using the following query.

mysql databasename -h source.db.hostaddress.com -P 3306 -u username -p

SELECT user,host FROM mysql.user;

+-----------------------+------------+
| user                  | host       |
+-----------------------+------------+
| repluser              | %          |
| root                  | %:%        |
| wordpress             | 10.5.9.116 |
| metrics_user_datadog  | ::1        |
| metrics_user_telegraf | ::1        |
| mysql.infoschema      | localhost  |
| mysql.session         | localhost  |
| mysql.sys             | localhost  |
+-----------------------+------------+
8 rows in set
Time: 0.013s

In the example above, we have a user account called wordpress that is used by a WordPress website. It will need to be recreated in the new database to allow WordPress to be switched over.

Next, connect to the new database using the MySQL client.

mysql databasename -h target.db.upclouddatabases.com -P 11550 -u upadmin -p

Then run the following commands to create the user and grant it permissions to the relevant database.

CREATE USER 'username'@'app.host.ip' IDENTIFIED BY 'password';
GRANT ALL ON databasename.* TO 'username'@'app.host.ip';
FLUSH PRIVILEGES;

Once done, you can exit the command-line client and continue below with finalising the migration.

Finalising the migration

To finalise the migration, you should still run the mysqlcheck on the database to ensure proper database statistics are in place for the newly loaded data.

The mysqlcheck command-line tool is used to perform table maintenance to check, repair, optimise, or analyses tables. The tables are locked for the duration of the check operation and are therefore unavailable to other sessions so this should be run before the new database is adopted to use. Note that this check operation can be time-consuming depending on the size and number of tables in the database.

Run the mysqlcheck command on your new Managed Database using the example underneath. Remember to replace the host address with yours.

mysqlcheck databasename -h target.db.upclouddatabases.com -P 11550 -u upadmin -p

The output of the check command would be similar to the example below.

databasename.table1                                  OK
databasename.table10                                 OK
databasename.table2                                  OK
databasename.table3                                  OK
databasename.table4                                  OK
databasename.table5                                  OK
databasename.table6                                  OK
databasename.table7                                  OK
databasename.table8                                  OK
databasename.table9                                  OK

Once all tables have been checked, your MySQL database migration is complete!

You can now begin configuring your applications to use the new Managed Databases.

For example, reconfigure the database connection details for a WordPress instance by setting up the wp-config.php file by making a backup of the existing file and then change the settings as indicated below.

cp /var/www/html/wordpress/wp-config.php /var/www/html/wordpress/wp-config-backup.php
nano /var/www/html/wordpress/wp-config.php

Set the database and username as shown below and replace the password and hostname with the details of the new Managed Database.

// ** MySQL settings - You can get this info from your web host ** //
/** The name of the database for WordPress */
define( 'DB_NAME', 'wordpress' );

/** MySQL database username */
define( 'DB_USER', 'wordpress' );

/** MySQL database password */
define( 'DB_PASSWORD', 'password' );

/** MySQL hostname */
define( 'DB_HOST', 'hostname.db.upclouddatabases.com:11550' );

When done, save the file and exit the editor. The WordPress site should then be connected to the new Managed Database.

Editor-in-chief and Technical writer at UpCloud since 2015. Cloud enthusiast writing about server technology and software.

Leave a Reply

Your email address will not be published. Required fields are marked *

Locations

Helsinki (HQ)

In the capital city of Finland, you will find our headquarters, and our first data centre. This is where we handle most of our development and innovation.

London

London was our second office to open, and a important step in introducing UpCloud to the world. Here our amazing staff can help you with both sales and support, in addition to host tons of interesting meetups.

Singapore

Singapore was our 3rd office to be opened, and enjoys one of most engaged and fastest growing user bases we have ever seen.

Seattle

Seattle is our 4th and latest office to be opened, and our way to reach out across the pond to our many users in the Americas.