Export & Import MySQL Database

Exporting and importing databases is a common task that is often required when migrating a website to a new server or making backups. MySQL provides utilities to export and import databases in various formats. In this tutorial, we'll cover how to export and import MySQL databases using command-line tools.

Exporting a Database

To export a MySQL database, you can use the mysqldump utility. Here's an example of how to export a database called "mydatabase" to a file called "mydatabase.sql":

mysqldump -u username -p mydatabase > mydatabase.sql

Replace "username" with the username of a MySQL user with sufficient privileges to read the table structures and data of the database you want to export. You'll be prompted to enter the password for the user.

If you want to export all the databases on the server, you can use the --all-databases option instead of the database name:

mysqldump -u username -p --all-databases > alldatabases.sql

This will create a file called "alldatabases.sql" that contains the schema and data of all the databases on the server.

Importing a Database

To import a MySQL database from a file, you can use the mysql utility. First, create a new empty database. For example, to create a database called "newdatabase", run the following command:

mysql -u username -p -e "CREATE DATABASE newdatabase"

Replace "username" with the username of a MySQL user with sufficient privileges to create a database.

Next, import the database from the file using the following command:

mysql -u username -p newdatabase < mydatabase.sql

Replace "username" with the username of a MySQL user with sufficient privileges to write data to the new database.

Compression

To compress the exported file, you can use the gzip utility. For example, to export and compress a database called "mydatabase" to a file called "mydatabase.sql.gz", run the following command:

mysqldump -u username -p mydatabase | gzip > mydatabase.sql.gz

To decompress and import a compressed file, you can use the gunzip utility in combination with the mysql utility. For example, to import a file called "mydatabase.sql.gz" to a database called "newdatabase", run the following command:

gunzip < mydatabase.sql.gz | mysql -u username -p newdatabase

Summary

Exporting and importing MySQL databases is a common task that can be performed using command-line utilities such as mysqldump and mysql. These utilities make it easy to migrate a website to a new server or make backups. By following the steps outlined in this tutorial, you can export and import MySQL databases with ease.