Migrating existing Databases to a Managed Database Instance

Database Migration - Overview

The Managed Database product provides reliable high-performance managed SQL database engines, both for PostgreSQL and MySQL. Using the service allows you to stay focussed on the development of your applications and to profit from Scaleway’s expertise in the management of your database engines.

In this guide you will learn how to migrate your existing databases on your Managed Database instances using an intermediate host.

Requirements

This guide consists of two parts, depending on which database Engine your instances are running:

Important: If you run your database in a private network with no public endpoint, you have to create the intermediate instance in your private network and attach a NAT gateway to be able to reach public Internet, and then run the migration steps from there.

Database migration for PostgreSQL

Exporting the source database

1 . Connect to your Virtual Instance using SSH:

ssh root@<virtual_instance_ip>

2 . Update the apt packet cache and upgrade the software already installed on the system:

apt update && apt upgrade -y 

3 . We use pg_dump to dump the content of the database to migrate. It is included in the package postgresql-client-common. Install it using apt:

apt install postgresql-client-common

4 . Dump the content of the originating database into a local SQL file on the Virtual Instance:

pg_dump –-host=<host> --port=<port> --username=<user> --dbname=<database_name> --file=<filename>.sql

In the above command, replace the following values as follows:

  • <host>: The database server hosting the PostgreSQL database
  • <port>: The TCP port on which the database server is listening (by default 5432)
  • <user>: The username used for the connection to the database server
  • <database_name>: The name of the database to export
  • <filename>: The name of the local file of the exported database

Note: If your database is running in a Docker container, the command to use will be:

docker exec -it $CONTAINER_NAME pg_dump --username=<name> --dbname=<database_name> > <database>.sql

Preparing the Managed Database for PostgreSQL

Note: In case you do not have a Managed Database for PostgreSQL yet, create a new one from your Scaleway Console.

1 . Enter the Database section of the Scaleway Console.

2 . Select your Managed Database instance from the list.

3 . Click on the Databases tab, then on + Create Database to create a new database in the instance.

4 . Enter the name of the new database and click Create Database to confirm the operation:

5 . Click on the Users tab to visualize a list of all your database users. To create a new user for your database, click + Add user.

6 . Click on the Permissions tab and grant All permissions to your database for the user:

7 . Click on the User Information tab and enter the credentials for the new user. Confirm by clicking on Create a user:

Importing the database

Once you have created both, the destination database and its users, import your data from the SQL file to your Managed Database instance by using the psql command-line tool.

1 . Retrieve the database host and port from your Scaleway console. The Endpoint information is visible on your Managed Database information page:

2 . Use the psql command-line tool to import your database with the following command:

psql --file=<filename>.sql --host=<managed_database_endpoint_ip> --port=<managed_database_endpoint_port> --username=<user> --dbname=<database_nme>

In the above command, replace the following values as follows:

  • <filename>: The file name of the exported database
  • <managed_database_endpoint_ip>: The IP address of the Endpoint of your Managed Database for PostgreSQL
  • <managed_database_endpoint_port>: The TCP port of the Endpoint of your Managed Database instance
  • <user>: The username for the Managed Database
  • <database_name>: the name of the Managed Database

Database migration for MySQL

Note: In case you do not have a Managed Database for MySQL yet, create a new one from your Scaleway Console.

Exporting the source database

1 . Connect to your Virtual Instance using SSH:

ssh root@<virtual_instance_ip>

2 . Update the apt packet cache and upgrade the software already installed on the system:

apt update && apt upgrade -y 

3 . We use mysqldump to dump the content of the database to migrate. It is included in the package mysql-client. Install it using apt:

apt install mysql-client

4 . Dump the content of the originating database into a local SQL file on the Virtual Instance:

mysqldump -P <port> -h <host> -u <user> -p <password> --opt <database_name> > <filename>.sql

In the above command, replace the following values as follows:

  • <port>: The TCP port on which the database server is listening (by default 3306)
  • <host>: The database server hosting the MySQL database
  • <user>: The username used for the connection to the database server
  • <database_name>: The name of the database to export
  • <filename>: The name of the local file of the exported database

Preparing the Managed Database for MySQL

Note: In case you do not have yet a Managed Database for MySQL, create a new one from your Scaleway Console.

1 . Enter the Database section of the Scaleway Console.

2 . Select your Managed Database instance from the list.

3 . Click on the Databases tab, then on + Create Database to create a new database in the instance.

4 . Enter the name of the new database and click Create Database to confirm the operation:

5 . Click on the Users tab to visualize a list of all your database users. To create a new user for your database, click + Add user.

6 . Click on the Permissions tab and grant All permissions to your database for the user:

7 . Click on the User Information tab and enter the credentials for the new user. Confirm by clicking on Create a user:

Importing the database

Once you have created both, the destination database and its users, import your data from the SQL file to your Managed Database instance by using the psql command-line tool.

1 . Retrieve the database host and port from your Scaleway console. The Endpoint information is visible on your Managed Database information page:

2 . Use the mysql command-line tool to import your database with the following command:

mysql -P <managed_database_endpoint_port> -h <managed_database_endpoint_ip> -u <user> -p <password> <database_name> < <filename>.sql

In the above command, replace the following values as follows:

  • <managed_database_endpoint_port>: The TCP port of the Endpoint of your Managed Database instance
  • <managed_database_endpoint_ip>: The IP address of the Endpoint of your Managed Database for MySQL
  • <user>: The username for the Managed Database
  • <password>: The password for the database user
  • <database_name>: The name of the Managed Database
  • <filename>: The file name of the exported database

Conclusion

You now have exported your existing PostgreSQL or MySQL database and imported it into a Scaleway Managed Datatabase instance. To finish the migration process of your database, update the credentials and server information in the configuration files of your existing application. Once these information are updated your application will use your database on instances managed by Scaleway Elements. For more information about the Managed Database product, refer to the product information page and the product documentation.

Discover a New Cloud Experience

Deploy SSD Cloud Servers in seconds.