Exporting Files Using the MySQL Command Line

mysql export

There are several reasons you may need to learn how to perform a MySQL export. Don’t believe it? Check these out.

  1. Perhaps you are dissatisfied with your current hosting provider and want to switch to a better web host. By way of advice: using a site builder could give you more flexibility and control over your website design and as such, enhance your hosting experience and reduce the need to switch hosts later. Yourwebsite offers hundreds of themes, plugins, and so much more.

  2. Maybe you are interested in backing up your databases. However, you should note that this may take a very long time if the data is extremely large.

  3. It could be that a third-party MySQL database is involved. This means that you migrating across SQL platforms (for example, from Microsoft SQL Server to an Oracle database).

Whatever your reason, there are a few ways to carry out a MySQL export. One is through your phpMyAdmin tab in cPanel, and another is by using mysqldump. Today, you will learn how to use the mysqldump method.

mysqldump Program

When you create a copy of your database you perform a dump. mysqldump is a program that backs up databases using logical backups. A logical backup is one where all but the contents of a database is backed up. mysqldump works by reducing the current database to SQL statements that you can then run at a different location to rebuild your database.

Advantages of mysqldump

  1. One advantage of this method is that you can edit the output file before restoring it. This means that the exported file does not have to be imported in its original form.

  2. Invocation syntax allows you to dump either single or multiple tables, one or more whole databases, or even whole MySQL databases.

  3. If you have detected issues in the exported file, the debugging feature allows you to include debugging information into the backup statements. Also, should the issues be of a nature to inhibit the dump, you can overcome these easily and force the dump.

  4. A great option for those who may need a MySQL export from a third party is the internationalization feature that allows you to use character features of foreign languages.

Performing MySQL Exports using the Command Line

1. Exporting up a single database:

  • Access the command line
  • Enter the following command where ‘username’ represents your username, ‘dbname’ represents the name of your database you wish to export, and ‘newspot.sql’ represents the location receiving the MySQL export:
mysqldump -u username -p dbname > newspot.sql
  • Input your password

Your MySQL export file is now ready.

2. Exporting more than one database:

As was mentioned earlier, you can export multiple databases at the same time. To do this, you will need to input all the databases to be exported (in our example below we have two MySQL export databases). Use a space to separate them.

The command prompt required is:

mysqldump -u username -p --databases database_1 database _2 > newdatabasespot.sql

When the .sql file is created, it will contain both databases.

3. Exporting all your databases:

Unlike with the previous example, should you wish to export or backup all your MySQL databases, you are not required to input all their names. The following command prompt will get the job done:

mysqldump -u root -p --all-databases > newdatabasesot.sql

Again, a single .sql file will be created containing all your MySQL databases.

If you want to export to multiple files, you can consider adding a bash for loop.

Confirming the MySQL Export

Because there will be no visual cues to indicate that the file has been exported, you will need to check. You can:

  1. Run the following command to confirm if the dump was executed: head -n 5 data-dump.sql. The result should show that an SQL dump had been performed, and the location that it was exported to.

  2. Physically check for the .sql file in the specified folder

Using your MySQL Export File

While the reasons you created the backup would largely determine what you use the file for, one of the main reasons would be for migration purposes. You would, therefore, need to know how to import the newly created .sql file.

You can use the command line as well to help with this. The program you would require is mysqlimport. It works by populating a table’s contents to recreate your database.

Summary

You have options when it comes to backing up/exporting/dumping your databases. Using the command line is one of those options.

The command prompts to perform an export are:

To export one database: mysqldump -u username -p dbname > newspot.sql

To export more than one database: mysqldump -u username -p –databases database_1 database _2 > newdatabasespot.sql

To export all your databases: mysqldump -u root -p –all-databases > newdatabasesot.sql