MySQL - Database Export

MySQL - Database Export and Import

Why Export and Import Data?

Exporting and importing data are fundamental database administration tasks. Common reasons include:

• Backups: Creating a logical backup of your data so you can restore it in case of data loss or corruption.
• Migration: Moving a database from one server to another (e.g., from a development to a production environment, or from an on-premise server to a cloud provider like AWS RDS).
• Cloning: Creating a copy of a database for testing, development, or analysis purposes.

The most common command-line tool for creating logical backups in MySQL is mysqldump.

Exporting with mysqldump

mysqldump is a client program that generates a SQL script file (a ‘dump’) containing CREATE statements to reconstruct the database objects and INSERT statements to repopulate the data.

Exporting a Single Database

The basic syntax to export an entire database:

mysqldump -u [username] -p [database_name] > backup.sql

You will be prompted to enter the password for [username]. The > symbol redirects the output to a file named backup.sql.

Example

# This command will dump the 'TUTORIALS' database to 'tutorials_backup.sql'
$ mysqldump -u root -p TUTORIALS > tutorials_backup.sql
Enter password: ********

Exporting Specific Tables

To export only one or more specific tables, list them after the database name.

mysqldump -u [username] -p [database_name] [table1] [table2] > tables_backup.sql

Example

# Dumps only the 'Customers' and 'Products' tables from the 'TUTORIALS' database
$ mysqldump -u root -p TUTORIALS Customers Products > customer_products_backup.sql

Exporting All Databases

To back up every database on the MySQL server that the user has access to, use the --all-databases flag.

mysqldump -u [username] -p --all-databases > all_databases_backup.sql

Key mysqldump Options for Modern Use

For reliable backups, especially in production, you should use additional flags:

• --single-transaction: Creates a consistent snapshot of InnoDB tables without locking them. This is crucial for backing up a live database without interrupting service. It starts a transaction and dumps the data from that consistent state.
• --routines: Includes stored procedures and functions in the dump.
• --events: Includes scheduled events in the dump.
• --triggers: Includes triggers (this is on by default, but it’s good to be explicit).
• --no-data: Creates a dump with only the schema (table structures, views, etc.), without any row data. Useful for creating a blank copy of the database structure.
• --where='condition': Dumps only the rows that satisfy the WHERE condition. For example, --where='is_active=1'.

Recommended Production Backup Command

mysqldump -u [username] -p \
  --single-transaction \
  --routines \
  --events \
  --triggers \
  [database_name] > full_backup_$(date +%F).sql

This command creates a comprehensive backup with a timestamp in the filename.

Importing a Database with mysql Client

To restore a database from a .sql dump file, you use the standard mysql command-line client.

First, you typically need to create an empty database for the data to be imported into.

# Connect to MySQL
$ mysql -u root -p

# Create a new database in the MySQL shell
mysql> CREATE DATABASE new_tutorials;
mysql> exit;

Now, import the data from your backup file into the newly created database.

mysql -u [username] -p [new_database_name] < backup.sql

Example

# Imports the 'tutorials_backup.sql' file into the 'new_tutorials' database
$ mysql -u root -p new_tutorials < tutorials_backup.sql
Enter password: ********

Security Considerations

• Do Not Put Passwords on the Command Line: Avoid using -pMyPassword directly. Always use -p alone to be prompted securely.
• Use a Dedicated Backup User: Create a specific MySQL user for backups with only the necessary privileges (SELECT, LOCK TABLES, SHOW VIEW, EVENT, TRIGGER).
• Secure Backup Files: The .sql dump file contains all your data in plain text. Store it in a secure, access-controlled location and consider encrypting it (e.g., using gpg).

Automation and Real-World Scenarios

In a real-world project, you would never run backups manually. You would automate the process using a scheduler like cron on Linux or Task Scheduler on Windows.

Example: Basic Cron Job for Nightly Backups

You can add a line to your crontab (crontab -e) to run a backup script every night.

/etc/cron.d/mysql-backup

# Run at 2:00 AM every day
0 2 * * * root /path/to/backup_script.sh

The backup_script.sh would contain your mysqldump command, and potentially extra logic for compressing the backup, uploading it to cloud storage (like Amazon S3), and deleting old backups.