Managing database backups and migrations is an essential task for any MySQL database administrator. Using the command line, you can easily export databases to backup files or import them into a new environment. This guide covers how to import and export databases in MySQL using the command line.
Prerequisites
Before starting, ensure that:
- MySQL is installed and running on your server or local machine.
- You have access to a terminal or command line.
- You have the necessary MySQL credentials, including the username and password.
Exporting Databases with mysqldump
The mysqldump utility is the standard tool for exporting databases in MySQL. It creates a .sql file that contains the SQL statements needed to recreate the database.
1. Export a Single Database
To export a single database, use the following syntax:
- -u [username]: The MySQL username.
- -p: Prompts for the MySQL password.
- [database_name]: The name of the database you want to export.
- [filename].sql: The name of the output file that will contain the database backup.
Example:
This command will prompt for the password of the root user and create a file named mydatabase_backup.sql containing all the data and structure of mydatabase.
2. Export Multiple Databases
To export multiple databases, use the –databases flag followed by the names of the databases:
Example:
This command will export both db1 and db2 into multiple_databases_backup.sql.
3. Export All Databases
To export all databases on your MySQL server, use the –all-databases flag:
Example:
This creates a full backup of all databases into all_databases_backup.sql.
4. Export Only Table Structure (No Data)
If you only need the structure of the tables without the data, add the –no-data flag:
Example:
5. Export Specific Tables
To export specific tables from a database, list them after the database name:
Example:
Importing Databases with mysql
To import a database from an SQL file, use the mysql command.
1. Import a Single Database
To import an SQL file into a MySQL database, use the following command:
- -u [username]: The MySQL username.
- -p: Prompts for the MySQL password.
- [database_name]: The name of the database where the data will be imported.
- [filename].sql: The name of the .sql file to import.
Example:
This command will prompt for the password of the root user and import the contents of mydatabase_backup.sql into mydatabase.
2. Import into a New Database
If you want to import into a new database, you first need to create the database and then import the .sql file.
Step 1: Create a new database.
Step 2: Import the .sql file into the new database.
Example:
mysql -u root -p newdatabase < mydatabase_backup.sql
3. Import All Databases
If you backed up all databases using –all-databases, you can import them back using:
Example:
This command will restore all databases contained in the all_databases_backup.sql file.
Tips for Using mysqldump and mysql
- Use Compression: To save disk space and transfer time, you can compress the backup file using gzip:mysqldump -u root -p mydatabase | gzip > mydatabase_backup.sql.gz
And to import it:
gunzip < mydatabase_backup.sql.gz | mysql -u root -p mydatabase - Verify Database Before Import: Make sure the database you are importing into is empty or contains compatible data to avoid conflicts.
- Check Character Set: If you are dealing with non-ASCII characters, ensure that you export and import with the correct character set using the –default-character-set option:mysqldump -u root -p –default-character-set=utf8 mydatabase > mydatabase_backup.sql
- Error Handling: If you encounter errors during import, check for common issues like missing databases or table structures and ensure that your .sql file is complete.
Summary
Exporting and importing MySQL databases using the command line is straightforward with mysqldump and mysql. These tools allow for flexibility in managing backups, migrating databases between servers, and creating structure-only or data-specific exports. By understanding the commands and their options, you can ensure smooth database operations and maintain data integrity during transfers.