How to use mysqldump to export and import databases

Where necessary, you may need to have access to a VPS server so you can follow how to implement the steps in this article.  You can get a cheaper VPS Server from Contabo with 4vCPU cores, 8GM RAM, and 32TB Bandwidth for less than $5.50 per month. Get this deal here now

Table of Contents

Cloud VPS S

$5.50 Monthly
  • 4 vCPU Cores | 8GB RAM


$15.50 Monthly
  • 6 vCPU Cores | 16GB RAM


$17.50 Monthly
  • 8 vCPU Cores | 24GB RAM

mysqldump is a command-line utility used for creating backups of MySQL databases by generating SQL statements that can recreate the entire database or specific tables, including the data, structure, and other database objects.

Backup Generation

The primary purpose of mysqldump is to create backups of MySQL databases. It generates a set of SQL statements that, when executed, can recreate the database schema and populate it with the existing data.

This can be achieved by running the following command in the MySQL terminal;

mysqldump -u username -p password dbname > backup.sql

The above command will export a database file named backup.sql file.

The following parameters have to be specified;

  • username: This is represented by the -u flag in the command. The username belongs to the database user. This could be root user or any other user you specify.
  • password: This is the password of the database as assigned to the user.
  • dbname: This the name of the database

Specifying the directory

To specify the directory for the output file when using mysqldump, you can provide the full path to the file in the command. Here’s an example:

mysqldump -u username -p password dbname > /path/to/directory/backup.sql

In this example, replace /path/to/directory/ with the actual path to the directory where you want to store the backup.sql file. If the directory doesn’t exist, make sure to create it before running the command.

Alternatively, you can change your current working directory to the desired location before running the mysqldump command. For example:

cd /path/to/directory/
mysqldump -u username -p password dbname > backup.sql

This will create the backup.sql file in the specified directory. Adjust the paths according to your system’s directory structure.

Transfer Data to a remote server

If you want to move a database from one server to another, you can use mysqldump to export the database to a file and then import it on the new server. Example:

mysqldump -u username -p password dbname > dump.sql

To import the dump on another server:

mysql -u username -p password new_dbname < dump.sql

On the remote server, you can also specify the directory path where the database file is located;

mysql -u username -p password dbname < /path/to/remote/backup.sql

mysqldump automation

If your database needs to be constantly backed up frequently, then you can leverage automation mechanisms on how to do it.

For example, you can create a script with defined mysqldump instructions and then use scheduling utilities like cron jobs to execute it.

Here is the detailed guide on how to create a cron job for database backup using mysqldump,

Hire us to handle what you want

Hire us through our Fiverr Profile and leave all the complicated & technical stuff to us. Here are some of the things we can do for you:

  • Website migration, troubleshooting, and maintenance.
  • Server & application deployment, scaling, troubleshooting, and maintenance
  • Deployment of Kubernetes, Docker, Cloudron, Ant Media, Apache, Nginx,  OpenVPN, cPanel, WHMCS, WordPress, and more
  • Everything you need on AWS, IBM Cloud, GCP, Azure, Oracle Cloud, Alibaba Cloud, Linode, Contabo, DigitalOcean, Ionos, Vultr, GoDaddy, HostGator, Namecheap, DreamHost, and more.

We will design, configure, deploy, or troubleshoot anything you want. Starting from $10, we will get your job done in the shortest time possible. Your payment is safe with Fiverr as we will only be paid once your project is completed.