Importing and Exporting Databases

This topic describes how to import and export data from local MySQL Servers to MySQL DB Systems, and exporting data from MySQL DB Systems to Compute instances.

Managing Database Import and Export

The import process for MySQL Database Service is a manual one.

  • Export local database to a file, using mysqldump.
    Note

    It is strongly recommended that you use the same version of MySQL locally, as that used in your DB System. To check the version used in the MySQL DB System, check the MySQL DB System Details page. See MySQL DB System Information for more information.
  • Copy that file to a compute instance, using the copy utility of your choice, depending on the operating system you chose for your Compute instance.
  • Import the data to your DB System using the mysql client application, which you installed on your Compute instance.

Note

If your export is very large, ensure it is not larger than the available space on your Compute instance. If it is larger, you must add storage to your Compute instance. For more information, see Adding Storage for Windows or Adding Storage for Linux.

Exporting Local Data with mysqldump

To export a database from a local MySQL server:
  1. Run the following command:
    mysqldump --databases databaseName -h hostName --port portNumber -u username -p --single-transaction > exportname.sql
    • databaseName: the name of the database you want to export.
    • hostName: the address of the MySQL server where the database resides.
    • portNumber: the port the MySQL server is listening on. Default is 3306.
    • username: user with the required privileges to access the required database. For more information on the privileges required, see mysqldump.
    • -p: the user's password. If you add the parameter, you are prompted for the password when running the command.
    • --single-transaction: sets the transaction mode to REPEATABLE READ. For more information, see mysqldump's --single-transaction.
    • exportname: the filename of the exported content with an sql file extension.
    The export file is created.
You can now copy this exported file to your OCI Compute instance and import it into your MySQL Database Service DB System.

Importing a Database using the mysql client

This task assumes you have created a Linux Compute instance and configured the network to permit connections from the Compute instance to the target DB System. For more information on this network configuration, see Configuring the Network.
For information on connecting to Linux and Windows Compute instances, see Connecting to an Instance.
Ensure the mysql client is installed on the Compute instance and that you can connect to the DB System using the endpoint's IP address, and the credentials of a user with the rights to import data.
To import a backup:
  1. ssh into the Compute instance.
  2. Run the following command, replacing the values as required:
    mysql -u username -p -h DBSystemEndpointIPAddress < exportName.sql
    • mysql: specifies the MySQL client application.
    • -u: the username defined on the DB System.
    • -p: the user's password.
    • -h: the hostname. The IP address of the DB System Endpoint. To retrieve this value, open the DB System's details page, and select Endpoint from the Resources section.
    The data is imported. If you exported a specific database, then imported that file, the database created in the DB System will have the same name as the one you exported. For more information on using the MySQL client, see The MySQL Command-Line Client.
  3. Confirm the database was created by starting the mysql client and running the show databases; command. The resulting list contains all databases in the DB System.
Your database is imported into the DB System and is accessible from the Compute.

Exporting a Database from a DB System with mysqldump

This task assumes you have created a Compute instance, a MySQL Database DB System, and installed the MySQL community edition on your Compute instance. For more information, see Connecting to a DB System.
To export a database from a local MySQL server:
  1. Connect to your Compute instance. For example, opc@computeInstanceIP.
  2. Run the following command from your home folder (/home/opc) or from a folder owned by the opc user:
    mysqldump --databases databaseName -h hostName --port portNumber -u username -p --single-transaction --set-gtid-purged=OFF > exportname.sql
    • databaseName: the name of the database you want to export.
    • hostName: the address of the MySQL server where the database resides.
    • portNumber: the port the MySQL server is listening on. Default is 3306.
    • username: user with the required privileges to access the database. For more information on the privileges required, see mysqldump.
    • -p: the user's password. If you add the parameter, you are prompted for the password when running the command.
    • --single-transaction: sets the transaction mode to REPEATABLE READ. For more information, see mysqldump's --single-transaction.
    • --set-gtid-purged=OFF: (mandatory) For more information on this parameter, see set-gtid-purged.
    • exportname: the filename of the exported content with an sql file extension.
    The export file is created in the same directory as the command was run.