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.<\/p>\n\n\n\n
Moving your MySQL databases to Managed Databases will take away the need for manual maintenance. In this tutorial, we\u2019ll show you the tools, steps and requirements to make the migration easy and painless.<\/p>\n\n\n\n
Start by setting up a new Managed Database cluster<\/a> you are migrating into. You will need to choose a plan with enough storage capacity for your existing database.<\/p>\n\n\n\n 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.<\/p>\n\n\n\n Managed Databases perform the best by serving a Cloud Server over a Private network within the same data centre which will make importing much quicker.<\/p>\n\n\n\n Create a new Cloud Server<\/a> to export and import your MySQL database.<\/p>\n\n\n\n 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.<\/p>\n\n\n\n Mysqldump<\/a> 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.<\/p>\n\n\n\n MySQL client<\/a> 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.<\/p>\n\n\n\n You can install both of these with one of the following commands.<\/p>\n\n\n\n Additionally, it\u2019s important to note that the migration steps will take some time, especially with larger databases. And while mysqldump<\/tt> includes verbose output options, MySQL client doesn\u2019t 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.<\/p>\n\n\n\n With the prerequisites all set, you are ready to start migrating!<\/p>\n\n\n\n When you\u2019ve 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<\/tt> command-line tool to create a single-file backup.<\/p>\n\n\n\n Note that the migration process will take some time.<\/strong> 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.<\/p>\n\n\n\n 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\u2019t use the default port.<\/p>\n\n\n\n The command will export the database into a file mydb_export.sql in the directory it is executed in.<\/p>\n\n\n\n After you have exported your MySQL database to the Cloud Server, you can then begin importing the data to your new Managed Database.<\/p>\n\n\n\n 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.<\/p>\n\n\n\n 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.<\/p>\n\n\n\n You can check the list of users in your old database by connecting with the MySQL client and using the following query.<\/p>\n\n\n\n In the example above, we have a user account called wordpress<\/tt> that is used by a WordPress website. It will need to be recreated in the new database to allow WordPress to be switched over.<\/p>\n\n\n\n Next, connect to the new database using the MySQL client.<\/p>\n\n\n\n Then run the following commands to create the user and grant it permissions to the relevant database.<\/p>\n\n\n\n Once done, you can exit the command-line client and continue below with finalising the migration.<\/p>\n\n\n\n2. Host for the migration<\/h3>\n\n\n\n
3. Database tools<\/h3>\n\n\n\n
# Debian or Ubuntu\nsudo apt install mysql-client\n\n# CentOS\nsudo dnf install mysql-client<\/pre>\n\n\n\n
# Ubuntu or Debian\nsudo apt install pv\n\n# CentOS\nsudo dnf install pv<\/pre>\n\n\n\n
Exporting data from your old database<\/h2>\n\n\n\n
mysqldump --database databasename(s)<\/span> -h source.db.hostaddress.com<\/span> -P 3306 -u username<\/span> -p --single-transaction --set-gtid-purged=OFF --hex-blob | pv > mydb_export.sql<\/code><\/p>\n\n\n\n
If you have multiple databases, you can split the task and migrate them individually or migrate them together with --databases db1 db2<\/tt><\/strong> etc.<\/li>
Note that if your old database is not in the same UpCloud data centre as the Cloud Server used for the migration, you\u2019ll need to allow connection over the public network.<\/li>
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.<\/li>
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.<\/li>
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.<\/li><\/ul>\n\n\n\nImporting data to Managed Databases<\/h2>\n\n\n\n
pv mydb_export.sql | mysql -h target.db.upclouddatabases.com<\/span> -P 11550 -u upadmin -p<\/code><\/p>\n\n\n\n
You will be prompted to enter your password when running the command. The password for your upadmin<\/em> account can be found in your UpCloud Control Panel<\/a>.<\/li><\/ul>\n\n\n\nRecreating user accounts<\/h2>\n\n\n\n
mysql databasename<\/span> -h source.db.hostaddress.com<\/span> -P 3306 -u username<\/span> -p<\/code><\/p>\n\n\n\nSELECT user,host FROM mysql.user;<\/code><\/p>\n\n\n\n+-----------------------+------------+\n| user | host |\n+-----------------------+------------+\n| repluser | % |\n| root | %:% |\n| wordpress | 10.5.9.116 |\n| metrics_user_datadog | ::1 |\n| metrics_user_telegraf | ::1 |\n| mysql.infoschema | localhost |\n| mysql.session | localhost |\n| mysql.sys | localhost |\n+-----------------------+------------+\n8 rows in set\nTime: 0.013s<\/pre>\n\n\n\n
mysql databasename<\/span> -h target.db.upclouddatabases.com<\/span> -P 11550 -u upadmin -p<\/code><\/p>\n\n\n\nCREATE USER 'username'@'app.host.ip' IDENTIFIED BY 'password';\nGRANT ALL ON databasename.* TO 'username'@'app.host.ip';\nFLUSH PRIVILEGES;<\/pre>\n\n\n\n
Finalising the migration<\/h2>\n\n\n\n