Migrating MySQL DB to Azure Linux CentOS VM using MySQLDump and SCP
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:
- Use the ClearDB MySQL offer?
- Spin up a VM & install MySQL?
- Re-create everything using the Web app + MySQL template?
- 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.
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 http://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
Harden the MySQL 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
Go back to the target machine
list the contents and you’ll see the copied file should be there
Import the database
mysql -u root -p testdb < /tmp/exportedtestdb.sql
That should do it.