Migrating MySQL DB to Azure Linux CentOS VM using MySQLDump and SCP

  • Article

This week I was asked to help move stuff from a Linux machine to Azure. The task seemed simple – ‘web app & database. The web app went really fast, but moving a MySQL database from one VM to another is something that got me thinking.

After looking into various solutions for a while I figure there are multiple paths you can walk down:

  1. Use the ClearDB MySQL offer?
  2. Spin up a VM & install MySQL?
  3. Re-create everything using the Web app + MySQL template?
  4. Use Azure Site Recovery to migrate the VM?

For this example I decided to go with option #2 (that best matched the original setup): create a VM, install MySQL and copy the data there.

Note: this is not a ‘best practice’, ‘supported’ or ‘ideal way’ – just one possible way of migrating the DB.

Target setup

Since I didn’t have MySQL running in Azure I first created a new CentOS based Linux VM so that it would match the source setup as best as possible. This can be done either via the Azure portal, or using tools such as (Azure) Powershell or the Azure Command Line Interface (https://azure.microsoft.com/en-us/documentation/articles/xplat-cli-install/)

SSH to the new machine that will serve as MySQL host

I usually use Putty, but any form of SSH that you’re comfortable with is fine.

Update the installation

 sudo yum update

install WGET if the command is not found

 sudo yum install wget

Download and install MySQL from the community repository

 sudo wget repo.mysql.com/mysql-community-release-el7-5.noarch.rpm

 sudo rpm -ivh mysql-community-release-el7-5.noarch.rpm

 sudo yum update

Install and start MySQL

 sudo yum install mysql-server

 sudo systemctl start mysqld

clip_image001

Harden the MySQL installation

 sudo mysql_secure_installation

Login to MySQL as root to test the installation

 mysql -u root -p

SSH to the machine you'll be exporting the MySQL data from

Again, I’m using Putty, but go ahead and use whatever tool you want for the task.

Dump (export) a database to a specific location

In this example I'm dumping the 'testdb' database to the '/tmp' folder and naming it 'exportedtestdb.sql'

 sudo mysqldump -u root -p --opt testdb > /tmp/exportedtesttdb.sql

Use SCP to copy the export from the original server to your Azure VM

 scp exportedtestdb.sql <username>@<hostname>:/tmp

clip_image002

Go back to the target machine

list the contents and you’ll see the copied file should be there

clip_image003

Import the database

 mysql -u root -p testdb < /tmp/exportedtestdb.sql

That should do it.

P.