--- title: "Import a database into a DBaaS instance" description: "Move a MySQL, MariaDB, or PostgreSQL database into a DBaaS instance of the same engine with the engine's dump tool, and check that it arrived." content_type: "how-to" product: "database" platform: "MyAccount" url: "/myaccount/database/how-to/import-a-database/" topic: ["create-and-connect"] relationships: {"requires":["/myaccount/database/how-to/connect-to-a-database/"]} --- # 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](/myaccount/database/how-to/create-a-database/). - 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](/myaccount/database/how-to/connect-to-a-database/). - 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, `` and `` are the address of the server you are moving from and a user that can read it. `` is the **Endpoint** under **Connection Details**, and `` 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](/myaccount/parameter-group/how-to/create-and-edit-a-parameter-group/) and [Attach a parameter group to a DBaaS instance](/myaccount/parameter-group/how-to/attach-a-parameter-group-to-a-database/). 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. ```text 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: ```bash mysqldump -h -u -p --single-transaction --routines --skip-triggers \ --set-gtid-purged=OFF --databases > dump.sql ``` `` 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: ```bash sed -E 's/DEFINER=`[^`]+`@`[^`]+`/DEFINER=``@`%`/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](/myaccount/database/how-to/connect-to-a-database/): ```bash mysql -h -P 3306 -u -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 `` is that table: ```bash mysql -h -P 3306 -u -p --ssl-mode=VERIFY_CA --ssl-ca=ca-cert.pem \ -D -e "SHOW TABLES; SELECT COUNT(*) FROM ;" ``` The tables and row counts match the source. If the import stops with an error, see [Troubleshooting DBaaS](/myaccount/database/troubleshooting/database/#an-import-stops-with-error-1227-1418-or-1419). ## Import a PostgreSQL database In the commands below, `` and `` are the address of the server you are moving from and a user that can read it. `` is the **Endpoint** under **Connection Details**, and `` 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: ```bash pg_dump -h -U -Fc -f dump.pgc ``` `` is the name of the database to move. ### Restore a PostgreSQL dump Restore into the database created with the DBaaS instance, where `` is the **Database Name** under **Connection Details**: ```bash pg_restore --no-owner --no-privileges \ -d "host= port=5432 user= dbname= 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 `` is that table: ```bash psql "host= port=5432 user= dbname= sslmode=verify-ca sslrootcert=ca-cert.pem" \ -c "\dt" -c "SELECT count(*) FROM ;" ``` The tables and row counts match the source. If the restore prints an error, see [Troubleshooting DBaaS](/myaccount/database/troubleshooting/database/#a-restore-prints-must-be-superuser-to-create-this-extension). 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](/myaccount/database/how-to/manage-database-users/).