MySQL Database Export and Import from the Command Line

MySQL provides powerful command-line tools for managing databases, including exporting data to SQL files and importing data from them. This tutorial covers how to perform these tasks effectively, including options for controlling the export process.

Exporting a MySQL Database

The mysqldump utility is used to create a logical backup of a MySQL database, typically in the form of a .sql file containing SQL statements.

The basic syntax for exporting a database is:

mysqldump -u [username] -p [database_name] > [filename.sql]
  • -u [username]: Specifies the MySQL username to connect with.
  • -p: Prompts for the password. It’s generally recommended not to include the password directly in the command for security reasons.
  • [database_name]: The name of the database you want to export.
  • > [filename.sql]: This redirects the output of mysqldump to a file named [filename.sql].

Example:

To export a database named blog using the username vivek, you would use the following command:

mysqldump -u vivek -p blog > blog_backup.sql

You will be prompted to enter the password for the vivek user.

Advanced Export Options

mysqldump offers several options to customize the export process. Here are some useful ones:

  • --all-databases: Exports all databases on the server. Use this with caution!
  • --databases [db1] [db2] ...: Exports only the specified databases.
  • --tables [table1] [table2] ...: Exports only the specified tables within a database.
  • --no-data: Exports only the table structure (schema) without any data. This is useful for creating a blank copy of a database.
  • --routines: Includes stored procedures and functions in the export.
  • --triggers: Includes triggers in the export.
  • --events: Includes events in the export.
  • --single-transaction: Creates a consistent snapshot of the database by starting a transaction. This is useful for exporting a live database without locking it.
  • --lock-tables=false: Prevents tables from being locked during the export. Be careful when using this on a live database, as it might lead to inconsistent data.

Example with advanced options:

To export a database named my_database including routines, triggers, and events, you can use:

mysqldump -u root -p my_database --routines --triggers --events > my_database_with_extras.sql

Importing a MySQL Database

Once you have a .sql file containing SQL statements, you can import it into a MySQL database using the mysql command-line client.

The basic syntax for importing a database is:

mysql -u [username] -p [database_name] < [filename.sql]
  • -u [username]: Specifies the MySQL username to connect with.
  • -p: Prompts for the password.
  • [database_name]: The name of the database you want to import into. This database must already exist.
  • < [filename.sql]: This redirects the contents of [filename.sql] to the mysql client as input.

Example:

To import the blog_backup.sql file into a database named blog using the username vivek, you would use:

mysql -u vivek -p blog < blog_backup.sql

You will be prompted to enter the password for the vivek user.

Alternative Import Method (Using the MySQL Client)

If you are already connected to the MySQL server using the mysql client, you can import a .sql file using the source command:

use [database_name];
source [filename.sql];

For example:

use blog;
source blog_backup.sql;

This method avoids the need to specify the username and password repeatedly.

Important Considerations:

  • Database Existence: Ensure that the target database exists before importing. If it doesn’t, create it using the CREATE DATABASE statement.
  • Permissions: The user you use to import the database must have sufficient permissions to create tables, insert data, and perform other necessary operations.
  • Large Files: For very large SQL files, consider using the mysql command with the --max_allowed_packet option to increase the maximum packet size allowed.
  • Foreign Key Checks: Disabling foreign key checks during import can speed up the process, especially if the data is not in the correct order. However, be sure to re-enable them afterward to maintain data integrity. You can do this by adding SET FOREIGN_KEY_CHECKS=0; at the beginning of your SQL file and SET FOREIGN_KEY_CHECKS=1; at the end.

Leave a Reply

Your email address will not be published. Required fields are marked *