Oracle Data Pump and MySQL mysqldump and mysql

Oracle Usage

Oracle Data Pump is a utility for exporting and importing data from/to an Oracle database. It can be used to copy an entire database, entire schemas, or specific objects in a schema. Oracle Data Pump is commonly used as a part of a backup strategy for restoring individual database objects (specific records, tables, views, stored procedures, and so on) as opposed to snapshots or Oracle RMAN, which provides backup and recovery capabilities at the database level. By default (without using the sqlfile parameter during export), the dump file generated by Oracle Data Pump is binary (it can’t be opened using a text editor).

Oracle Data Pump supports:

The term logical backup refers to a dump file created by Oracle Data Pump.

EXPDP and IMPDP can only read or write dump files from file system paths that were pre-configured in the Oracle database as directories. During export or import, users must specify the logical directory name where the dump file should be created; not the actual file system path.

Examples

Use EXPDP to export the HR schema.

$ expdp system/**** directory=expdp_dir schemas=hr dumpfile=hr.dmp logfile=hr.log

The command contains the credentials to run Data Pump, the logical Oracle directory name for the dump file location (which maps in the database to a physical file system location), the schema name to export, the dump file name, and log file name.

Use IMPDP to import the HR a schema and rename to HR_COPY.

$ impdp system/**** directory=expdp_dir schemas=hr dumpfile=hr.dmp logfile=hr.log REMAP_SCHEMA=hr:hr_copy

The command contains the database credentials to run Data Pump, the logical Oracle directory for where the export dump file is located, the dump file name, the schema to export, the name for the dump file, the log file name, and the REMAP_SCHEMA parameter.

For more information, see Oracle Data Pump in the Oracle documentation.

MySQL Usage

MySQL provides two native utilities — mysqldump and mysqlimport. You can use these utilities to perform logical database exports and imports. The functionality is comparable to Oracle’s Data Pump utility; however, in some use cases, the mysql connection utility is more equivalent to Oracle Data Pump import tool impdp. You can use these utilities to move data between two different databases or to create logical database backups.

To explain the difference between mysql and mysqlimport utilities, the equivalent Oracle reference will be used.

Use the mysql utility to interact with the database like SQL*Plus. For import purposes, you can run it with all of the CREATE and INSERT commands to rebuild your schema and insert data just like in Oracle.

The mysqlimport utility reads a comma-separated value (CSV) data file and is equivalent to SQL*Loader. This utility is a reference to the LOAD DATA command. It is mostly used to move schema or some of the objects between clusters. You should use this utility if you have a data file (not a script) and you want to load it fast.

Amazon Aurora MySQL supports data export and import using mysqldump, mysqlimport, or mysql creation scripts. The binaries for all utilities must be installed on your local workstation or on an Amazon EC2 server.

After export, MySQL dump files created using mysqldump can be copied to an Amazon S3 bucket. Later, when the dump files are needed for database restore, they can be copied back to a desktop/server with a MySQL client (such as your workstation or an Amazon EC2 server) to use mysqlimport.

Examples

Export data using mysqldump.

mysqldump --column-statistics=0 DATABASE_TO_RESTORE -h INSTANCE_ENDPOINT -P 3306 -u
USER_NAME -p > /local_path/backup-file.sql

Run an export and copy the backup file to an Amazon S3 bucket using a pipe and the Amazon CLI.

mysqldump --column-statistics=0 DATABASE_NAME -h MYSQL_INSTANCE_ENDPOINT -P 3306 -u
USER_NAME -p > /local_path/backup-file.sql | aws s3 cp -
s3://mysql-backups/mysql_bck-$(date"+%Y-%m-%d-%H-%M-%S")

Import data using mysql.

mysql DB_NAME -h MYSQL_INSTANCE_ENDPOINT -P 3306 -u
USER_NAME -p < /local_path/backupfile.sql

Copy the output file from the local server to an Amazon S3 Bucket using the Amazon CLI.

aws s3 cp /local_path/backup-file.sql
s3://my-bucket/backup-$(date "+%Y-%m-%d-%H-%M-%S")

In the preceding example, the {-$(date "+%Y-%m-%d-%H-%M-%S")} format is valid on Linux servers only.

Download the output file from the S3 bucket.

$ aws s3 cp s3://my-bucket/backup-2017-09-10-01-10-10 /local_path/backup-file.sql

Summary

expdp system/***
schemas=hr
dumpfile=hr.dmp
logfile=hr.log

mysqldump --column-statistics=0
DATABASE_TO_RESTORE -h
INSTANCE_ENDPOINT -P 3306 -u USER_NAME
-p > /local_path/backup-file.sql

expdp system/***
schemas=hr directory=EXP_DIR
dumpfile=hr.dmp logfile=hr.log

mysqldump --column-statistics=0
DATABASE_NAME -h MYSQL_INSTANCE_ENDPOINT
-P 3306 -u USER_NAME
-p > /local_path/backup-file.sql |
aws s3 cp - s3://mysql-backups/mysql_bck-$(
    date"+%Y-%m-%d-%H-%M-%S")

impdp system/***
schemas=hr dumpfile=hr.dmp
logfile=hr.log
REMAP_SCHEMA=hr:hr_copy
TRANSFORMM=OID:N

mysql DB_NAME
-h MYSQL_INSTANCE_ENDPOINT
-P 3306 -u USER_NAME
-p < /local_path/backup-file.sql

For more information, see mysqldump — A Database Backup Program, mysqlimport — A Data Import Program, and mysql — The MySQL Command-Line Client in the MySQL documentation.