azuremigrate-database-azure

Migrate Your Application Database to Microsoft Azure

Introduction

If you would like to migrate your Bitnami application’s database to Microsoft Azure, this guide will walk you through the necessary steps. The example application used in this guide is WordPress, although the steps will work generically for any Bitnami application stack.

NOTE: Before following the steps in this guide, ensure that you have a Microsoft Azure account, that you have access credentials for your Bitnami application stack database, and that the server running your Bitnami application stack has a public IP address.

Step 1: Create a new database on Microsoft Azure

Begin by creating a new database on Microsoft Azure, as described below:

  • Log in to the Microsoft Azure management portal.

  • From the navigation menu, select the “App Services” menu item.

  • On the resulting page, click the “Add” button.

    Azure database migration

  • Search for “MySQL database” in the search box. On the results page, select the “MySQL Database - ClearDB” option from the list of related links, and click “Create”.

    Azure database migration

  • Enter a database name and resource group name.

    Azure database migration

  • In the “Pricing Tier” subsection, click the pricing tier and modify it depending on your requirements. Click “Select” once done to save your settings.

    Azure database migration

  • Review the legal terms and click the “Purchase” button to indicate your agreement.

    Azure database migration

  • Click “Create” to create the new database instance.

Your new database instance will now be launched. You will be redirected to an overview page which shows the current status of the instance, as shown below:

Azure database migration

Click the instance name and you will be redirected to a detail page. Click “Settings -> Properties” and make a note of the instance’s public host name, username and password, as you will need this information in subsequent steps. By default, an empty database will also be created. The database name can be obtained from the “Connection String” field. Note this database name as well.

Azure database migration

Step 2: Disable write access to the application

You can now start migrating your application database to the new MySQL instance on Microsoft Azure. However, before you do this, you must disable write access to the application so that the original and new databases stay in sync.

The method to do this varies from application to application. In some cases, the application itself offers a “maintenance mode” which can be activated for the duration of the data migration. In others, you might need to download a maintenance mode plugin for this purpose. If neither of these options is available, you must manually disable login access by redirecting users to a static maintenance page.

In this WordPress-based example, the easiest way to disable write access is to install and activate the WP Maintenance Mode plugin. This plugin will display a splash screen notification to inform users that the WordPress blog/website is down for maintenance and cannot be accessed.

Step 3: Export the application database from your Bitnami stack

The next step is to export the application database. You can either do this using the command-line mysqldump tool or the browser-based phpMyAdmin application.

Using the command line

Use the mysqldump tool to create a backup of the current database as follows:

  • Obtain the password for your Bitnami application database.

  • Log in to the server console via SSH.

  • Execute the following command to create a backup of the current database. In this example, the database is named bitnami_wordpress and the username to access the database is root. Replace these with the correct values for your Bitnami application stack.

    $ mysqldump -u root -p bitnami_wordpress --compatible=mysql4 > backup.sql
    

    The --compatible option ensures that the output file does not use the utf8mb4 collation, which is the default collation but unsupported by ClearDB’s MySQL database (at the time of writing). Learn more about this issue.

  • Enter the database password when prompted.

This will produce a backup.sql file in the current directory with the content of the selected database.

By default, the backup file uses the TYPE keyword, which is unsupported in the MySQL version provided by ClearDB (at the time of writing). To resolve this issue, replace it with the ENGINE keyword by running the stream editor command below:

$ sed -i -e 's/TYPE=/ENGINE=/g' backup.sql

Store the backup.sql file carefully, as you will need it in the next step.

Using phpMyAdmin

You can also accomplish this with the browser-based phpMyAdmin application included by default with Bitnami stacks.

  • Log in to the included phpMyAdmin application using these instructions.

  • Select the application database in the left navigation menu. In this example, the database is named bitnami_wordpress.

  • Select the “Export” menu item.

  • On the resulting page, select the “Custom” export method and the “SQL” output format.

    Microsoft Azure database migration

  • On the same page, find the “Format-specific options” section and set the value of the field “Database system or older MySQL server to maximize output compatibility with” to “MYSQL40”.

    Microsoft Azure database migration

  • Click “Go”.

An SQL export file will be created and downloaded to your desktop. Store this file carefully, as you will need it in the next step.

Step 4: Import the application database to Microsoft Azure

The next step is to import the application database to your Microsoft Azure database instance. Again, you can accomplish this either using the command-line tool or the browser-based phpMyAdmin application.

Using the command line

Use the mysql command-line client to connect to the Microsoft Azure database instance and create a new database and user as follows:

  • Log in to the server console via SSH.

  • Execute the following command to connect to the remote MySQL database and import the backup file. Replace the HOSTNAME, USERNAME and DBNAME placeholders with the host name, user name and database name obtained at the end of Step 1.

    $ mysql -u USERNAME -p -h HOSTNAME -D DBNAME < backup.sql
    

    Enter the database password for the user (also obtained in Step 1) when prompted.

The original application database content will now be imported into the new Microsoft Azure database.

Using phpMyAdmin

You can also accomplish this with the browser-based phpMyAdmin application included with the Bitnami stack. However, it is first necessary to configure phpMyAdmin to connect to your Microsoft Azure database instance, as described below:

  • Log in to your server console via SSH.

  • Edit the phpMyAdmin configuration file at /opt/bitnami/apps/phpmyadmin/htdocs/config.inc.php and add the lines below to the end of the file:

    $i++;
    $cfg['Servers'][$i]['verbose'] = 'Microsoft Azure MySQL Database';
    $cfg['Servers'][$i]['host'] = 'HOSTNAME';
    $cfg['Servers'][$i]['port'] = '3306';
    $cfg['Servers'][$i]['socket'] = '';
    $cfg['Servers'][$i]['connect_type'] = 'tcp';
    $cfg['Servers'][$i]['extension'] = 'mysqli';
    $cfg['Servers'][$i]['auth_type'] = 'cookie';
    $cfg['Servers'][$i]['AllowNoPassword'] = false;
    

    Remember to replace the HOSTNAME placeholder in the above code with the actual host name of your Microsoft Azure database instance. These lines will make it possible to access your Microsoft Azure database instance through phpMyAdmin.

  • Save the file.

Next:

  • Browse to the phpMyAdmin application using these instructions and select the “Microsoft Azure MySQL Database” server.

  • Log in with the database username and password retrieved at the end of Step 1.

    Microsoft Azure database migration

  • Select the database created automatically (for the database name, refer to the end of Step 1) in the left navigation menu.

  • Select the “Import” menu item.

  • On the resulting page, select the SQL export file created in Step 4.

  • Click “Go”.

    Microsoft Azure database migration

The original application database content will now be imported into the new Microsoft Azure MySQL database. A confirmation message will be displayed and the new tables will appear in the left navigation menu.

Step 5: Reconfigure the application to use the new database

Once your database has been transferred to the MySQl database on Microsoft Azure, the next step is to update your application configuration and point it to the new database. The procedure to do this varies from application to application, but typically involves modifying a configuration file and specifying the new database host, database username and password, and new database name. Remember that these values were all retrieved at the end of Step 1.

For example, to update the WordPress configuration, edit the file at /opt/bitnami/apps/wordpress/htdocs/wp-config.php and modify the various configuration variables as shown in the image below:

Microsoft Azure database migration

Step 6: Re-enable write access to the application

You can now turn off maintenance mode and re-enable full access to the application, by reversing the steps performed in Step 2.