How to Migrate a Local MySQL Database to a Vultr Managed Database

Introduction

Migrating a local MySQL Database to Vultr Managed Databases for MySQL is a good practice that implements remote accessibility, increased security, high availability, minimal maintenance, and administration for your database.

In this article, you'll learn how to migrate an on-premise MySQL database to a Vultr Managed Database cluster.

Prerequisites

To successfully migrate the database, you need to:

• Deploy a managed MySQL cluster at Vultr
• Have an existing local MySQL database server to export.
• Have a local MySQL user with SELECT, VIEW, TRIGGER, and LOCK TABLES privileges on the local database.

Export the MySQL Database

mysqldump is a backup utility that stores MySQL databases in the form of SQL statements used to reproduce the original database when executed. In this section, you'll back up a single database to a file and import it to a managed database.

1. Log in to your local MySQL database server. Replace dbuser with your actual database user.

     $ mysql -u dbuser -p
   

2. Show databases, and keep note of the database name you intend to export.

     SHOW DATABASES;
   

3. Exit the MySQL console.

     EXIT
   

4. Stop all services using the local database to prevent modifications during export. Services that use the local database will be offline during the migration.

5. Using the mysqldump utility, export a single database to a file.

    $ mysqldump -u dbuser -p example-database > localdb.sql

   To export all databases, run the following:

    $ mysqldump -u dbuser -p --all-databases > Alldb-backup.sql

   Exporting may take some time to complete depending on your database size.

Optional: Export Using PHPMyAdmin

If you prefer to use PHPMyAdmin instead of the MySQL client tool, export your database as follows:

1. Log in to PHP MyAdmin

     http://127.0.0.1/phpmyadmin
   

2. Select your target database from the left navigation panel.

3. Click Export on the top navigation menu.

4. Choose your desired export method, Quick or Custom.

5. Click GO to start exporting and download the database file.

Keep note of the downloaded file location. You'll use the MySQL client tool to import the database to your Vultr managed database.

Import the saved database dump to a Vultr Managed Database

1. List files in your directory and confirm your exported database dump is available.

    $ ls
   

2. Log in to your managed database.

    $ mysql -h dbhost.vultrdb.com -P <port> -u vultradmin -p

   Please verify the following parameters on your import command if the connection fails.

   -h – Defines the Vultr database hostname. -P – Sets the port number for connecting to the database host. -u – Sets the Database user that should import the database. -p – Turns on password authentication for the user to access the database server.

3. Create a new database with the same name as your old database.

    CREATE DATABASE example;
   

4. Exit the MySQL console.

    EXIT
   

5. Using the MySQL client, import the database to your managed database.

    $ mysql -h host.vultrdb.com -P <port> -u vultradmin -p --database=example-database < localdb.sql

   This rebuilds the database selected with the --database= flag.

6. When the database importation is complete, log in to your managed database.

    $ mysql -h dbhost.vultrdb.com -P <port> -u vultradmin -p
   

7. Switch to the database.

    USE example-database;
   

8. Show tables and verify that all data exists.

    SHOW tables;
   

9. Exit the console.

    EXIT

Recreate MySQL Database Users

1. Log in to your local database server.

    $ mysql -u dbuser -p
   

2. View all MySQL users.

    SELECT user,host FROM mysql.user;
   

3. Show the privileges granted to each user on the database. For example, dbuser.

    SHOW GRANTS FOR 'dbuser'@'localhost';

   Keep note of the privileges, or copy the listed GRANT statement in the output as below.

    +--------------------------------------------------------------------------------------------------------------+
    | Grants for dbuser@localhost                                                                                   |
    +--------------------------------------------------------------------------------------------------------------+
    | GRANT USAGE ON *.* TO `dbuser`@`localhost` IDENTIFIED BY PASSWORD '*23AE809DDACAF96AF0FD78ED04B6A265E05AA257' |
    | GRANT ALL PRIVILEGES ON `example-database`.* TO `dbuser`@`localhost`                                                |
    +--------------------------------------------------------------------------------------------------------------+
    2 rows in set (0.000 sec)
   

4. Exit the MySQL console.

    EXIT
   

5. Log in to your managed database.

    $ mysql -h dbhost.vultrdb.com -P <port> -u vultradmin -p
   

6. Create all the database users.

    CREATE USER 'dbuser'@'localhost' IDENTIFIED BY 'password';

   When logged in to a Vultr Managed Database for MySQL, it's referred to as localhost.

7. Assign the user correct privileges to the database.

    GRANT ALL PRIVILEGES ON `example-database`.* TO `dbuser`@`localhost`;
   

8. Refresh MySQL's privileges.

    FLUSH PRIVILEGES;
   

9. Exit the console.

    EXIT

Next Steps

You have migrated your local MySQL Database to a Vultr Managed Database for MySQL. Next, update the connection details in your applications to use the new database. Because you've created the same users and passwords in the new database, you should only need to change the database connection string to use the new managed database.