Import a database into a DBaaS instance

Import a database into a DBaaS instance of the same engine to move an existing application’s data onto E2E. You export the source with the engine’s own dump tool, then load the file into the DBaaS instance through its Endpoint.

Before you begin

• The DBaaS instance runs the same engine as the source and is Running; see Create a DBaaS instance.
• Your machine can reach the DBaaS instance’s Endpoint, the whitelist allows your address, and you have saved its CA certificate as ca-cert.pem; see Connect to a DBaaS instance.
• You can read the source database from your machine.

These commands work on MySQL 8.0, MariaDB 10.11, and PostgreSQL 18. Each one asks for the password when it connects. To run a command without the prompt, set the password in the client’s environment variable for that command, MYSQL_PWD for MySQL and MariaDB or PGPASSWORD for PostgreSQL.

Import a MySQL or MariaDB database

In the commands below, <source-host> and <source-user> are the address of the server you are moving from and a user that can read it. <endpoint> is the Endpoint under Connection Details, and <username> is the Username there, admin by default.

For both engines, install the MySQL 8.0 client, which includes mysqldump and mysql; the commands below use its options. Against a MariaDB server, mysqldump prints a warning about column statistics; the dump is still complete.

The admin user creates tables, views, and stored procedures. To import triggers and stored functions too, turn them on with a parameter group: set this value, attach the group to the DBaaS instance, and sync it, which restarts the DBaaS instance; see Create and edit a parameter group and Attach a parameter group to a DBaaS instance. Wait until the DBaaS instance reads Running again; on MariaDB 10.6, a connection during the restart gets ERROR 1815. Then export with triggers included by dropping --skip-triggers from the export command.

log_bin_trust_function_creators = 1

Run scheduled jobs from your application, since the event scheduler is off on a MySQL or MariaDB DBaaS instance. To load a CSV file from your machine on MariaDB, use LOAD DATA LOCAL INFILE. On MySQL, load the rows as INSERT statements, such as those in a dump.

Export a MySQL or MariaDB source

On your machine, dump the source database’s tables, views, and stored procedures:

mysqldump -h <source-host> -u <source-user> -p --single-transaction --routines --skip-triggers \
  --set-gtid-purged=OFF --databases <database-name> > dump.sql

<database-name> is the name of the database to move; the import creates a database of the same name on the DBaaS instance.

A dump from another server usually names that server’s users in its DEFINER clauses. Point them at the admin user so the DBaaS instance accepts them:

sed -E 's/DEFINER=`[^`]+`@`[^`]+`/DEFINER=`<username>`@`%`/g' dump.sql > dump-e2e.sql

Load the MySQL or MariaDB dump

The dump drops and recreates each table it contains, so a table on the DBaaS instance with the same name as one in the dump is replaced. Load the dump through the Endpoint, with the same SSL check as in Connect to a DBaaS instance:

mysql -h <endpoint> -P 3306 -u <username> -p --ssl-mode=VERIFY_CA --ssl-ca=ca-cert.pem < dump-e2e.sql

The import is done when your shell prompt comes back; an error prints a line that starts with ERROR.

Check a MySQL or MariaDB import

Connect and count the rows in a table you know, where <table-name> is that table:

mysql -h <endpoint> -P 3306 -u <username> -p --ssl-mode=VERIFY_CA --ssl-ca=ca-cert.pem \
  -D <database-name> -e "SHOW TABLES; SELECT COUNT(*) FROM <table-name>;"

The tables and row counts match the source. If the import stops with an error, see Troubleshooting DBaaS.

Import a PostgreSQL database

In the commands below, <source-host> and <source-user> are the address of the server you are moving from and a user that can read it. <endpoint> is the Endpoint under Connection Details, and <username> is the Username there, admin by default.

Install the PostgreSQL client tools from a release at least as new as the source server: the dump tool works with servers of its own release and older.

The admin user creates tables, views, functions, stored procedures, and triggers, and triggers run as they did on the source. The restore installs every extension the admin user has the rights to install. One that needs a superuser prints Must be superuser to create this extension, and the rest of the restore carries on. To load or save a file, use psql’s \copy command, which reads and writes files on your machine, in place of COPY with a file on the server.

Export a PostgreSQL source

On your machine, dump the source database in PostgreSQL’s custom format:

pg_dump -h <source-host> -U <source-user> -Fc -f dump.pgc <database-name>

<database-name> is the name of the database to move.

Restore a PostgreSQL dump

Restore into the database created with the DBaaS instance, where <target-database> is the Database Name under Connection Details:

pg_restore --no-owner --no-privileges \
  -d "host=<endpoint> port=5432 user=<username> dbname=<target-database> sslmode=verify-ca sslrootcert=ca-cert.pem" \
  dump.pgc

The source’s owners and grants name roles from the source server. --no-owner makes the admin user the owner of everything restored, and --no-privileges leaves those grants out. The restore is done when your shell prompt comes back; an error prints a line that starts with the tool’s name followed by error:.

Check a PostgreSQL restore

Connect and count the rows in a table you know, where <table-name> is that table:

psql "host=<endpoint> port=5432 user=<username> dbname=<target-database> sslmode=verify-ca sslrootcert=ca-cert.pem" \
  -c "\dt" -c "SELECT count(*) FROM <table-name>;"

The tables and row counts match the source. If the restore prints an error, see Troubleshooting DBaaS.

Your data is now on the DBaaS instance. Next, give your application a user of its own; see Manage database users on a DBaaS instance.