MySQL backup with MySQL dump

MySQL dump can be used to create a database backup. The data is transferred from the MySQL internal tree structure into a text format. Creating a MySQL backup is a standard task for web and system administrators. We’ll explain what a MySQL dump is and how to create it.

MyDefender
Safeguard your data with easy cyber security
  • Protection against ransomware attacks
  • Regular virus scans
  • Automatic backups and simple file recovery

What is a MySQL backup and how is it created?

MySQL dump is used to create MySQL backups as well as to migrate data between two databases. A MySQL database contains data which has been formatted for storage optimisation and access. The data must be converted to a suitable format to extract it from the database.

Note

We answer the question `What is a backup?´ in the Digital Guide.

An SQL dump is a plain text file containing SQL statements. There are several ways to create a MySQL backup. You can use the administration interface phpMyAdmin or a create backup with MySQLDumper. We’ll be looking at creating a MySQL dump using the command line tool mysqldump.

Tip

MySQL is the most widely used relational database management system. Postgre is a powerful competitor which is becoming increasingly popular. We explain how to create a PostgreSQL backup in another article.

What does MySQL dump mean?

`MySQL dump´ has two meanings:

  1. The export file of a MySQL database
  2. The command line tool to create a database export

Let’s look at these two terms in detail.

MySQL dump as an export file of a MySQL database

The MySQL database organises data in optimised data structures. For general data, a B-tree is used and geographical data is stored in an R-tree. A data `dump´ is the process of transferring the data from these special tree structures into a linear representation, known as the `dump´.

Dumping the tree data creates a text file which can be stored and moved between systems using conventional methods. This is required to create a backup from a MySQL database. So, what is contained in a MySQL dump exactly?

A MySQL dump contains SQL statements which reflect the contents of the database before dumping. The dump can be imported on another database installation. In this case, the SQL statements are exported and the database is rebuilt step by step. After importing, the database should contain the same data in the same structure as prior to dumping.

The complete process from creating the MySQL backup to importing the dump results in the following scheme:

mysql_import(mysql_dump(database)) == database

You may have already opened a MySQL dump in a code or plain text editor. The contents of a MySQL dump may seem quite cryptic at first. Below you will find an overview of the most frequently encountered SQL statements in a MySQL dump:

Statement in MySQL dump Explanation
DROP TABLE IF EXISTS Remove existing table during import
CREATE TABLE Create new table during import
INSERT INTO ... VALUES Fill columns with values during import

MySQL dump as a tool to create a database export

The mysqldump command line tool is usually installed as part of the MySQL client or MySQL server. You should determine if mysqldump is installed on the local system if you want to use the tool. We use the following command to check where the mysqldump binary data is located:

which mysqldump

The command will return an error message if the tool is not installed.

Use the following command to display the version of mysqldump installation:

mysqldump --version

This is how to get help from the mysqldump command:

mysqldump --help

We explain below how to use the mysqldump command to create a MySQL backup.

Tip

If your MySQL installation is running in a Docker container, you can use MySQL dump inside the container to create a MySQL backup. It is also possible to save the entire container as Docker backup.

Step by step guide to create a MySQL dump

Creating a MySQL backup with the mysqldump tool takes place on the command line. You must first log in to the database server or Docker container through SSH. At least three pieces of information are needed to create the MySQL backup.

  1. The name of the database being exported
  2. The name of the database user
  3. The password of the database user

You can begin creating the MySQL backup once you have all three pieces of information. Different methods are used depending on the scenario and requirements. We’ll present three commonly used approaches.

Entering the mysqldump command on the command line

Firstly, the mysqldump command can be entered manually on the command line. This is useful if you want to create a one-time backup of a database, for example, to migrate a WordPress installation from one server to another. The general scheme to enable the mysqldump command is as follows:

mysqldump --user=<username> --password=<password> <database> > <path/to/mysql_dump.sql>

A typical mysqldump command consists of these six components:

  1. mysqldump command
  2. Database user
  3. Database password
  4. Database name
  5. Forwarding the output
  6. Path to the output file

The mysqldump command may be written differently depending on the source. Instead of descriptive names such as `--user´, shorter versions may be used. We show the scheme below to clarify. The following mysqldump example is the same as the one already presented:

mysqldump -u<username> -p<password> <database> > <path/to/mysql_dump.sql>

Converting the data from the tree structure usually results in a high redundancy of the SQL statements contained in the MySQL dump. The dump compresses easily since the MySQL dump is a plain text file. It is common practice to forward the output of the mysqldump command to the Gzip compression tool. The following convention results in a file with the ending .sql.gz:

mysqldump --user=<username> --password=<password> <database> | gzip > <path/to/mysql_dump.sql.gz>

Below is an overview of the three variants which are commonly encountered in the mysqldump command for creating a MySQL backup:

  mysqldump call Database users Database password Database name Output forwarding Path to the output file
Command with descriptive versions mysqldump --user=<username> --password=<password> <database> > <path/to/mysql_dump.sql>
Command with short versions mysqldump -u<username> -p<password> <database> > <path/to/mysql_dump.sql>
Command with compression mysqldump --user=<username> --password=<password> <database> | gzip > <path/to/mysql_dump.sql.gz>

Using the MySQL dump database configuration file

The mysqldump command can be easily executed on the command line, however, a security risk does exist. This is because the database password is transferred in plain text as part of the mysqldump command. However, all commands entered on the command line are recorded in a special file by default. Therefore, this sensitive data will show up in plain text if someone uses the history command to view previous commands entered.

it is a good idea to define the data for the databank access in a separate file as variables to protect the database from unauthorised access. The file is read in before the enabling mysqldump command. Only the variable names will appear in the history log and the actual values remain hidden.

We will now show you how to create a MySQL backup using a configuration file. Simply copy the following commands into your command line and run them.

  1. Firstly, create a folder for the MySQL dump database configuration file below the user folder:
mkdir ~/. mysql-dump/
  1. Write the MySQL dump database configuration into a new file using the following code: 
cat << EOF > ~/. mysql-dump/database.conf
DB_NAME= 'your_db_name'
DB_USER= 'your_db_user_name'
DB_PASS= 'your_db_password'
EOF
  1. Open the configuration file in the nano editor and adjust the database access data: 
nano ~/. mysql-dump/database.conf

Replace the 'your_db' placeholders with values that match your database. Then press [Ctrl] + [X] followed by [Y] and [Enter] to close the file and save the changes.

The preparation is now complete and a MySQL backup can be created.

  1. To create a MySQL dump, read the configuration with the source command and then run MySQL dump: 
source ~/. mysql-dump/database.conf
mysqldump --user="$DB_USER" --password="$DB_PASS" "$DB_NAME" > <path/to/mysql_dump.sql>
Tip

MySQL backups which have been created with MySQL Dump are usually stored in the database server’s file system. However, following the 3-2-1-backup-rule at least one backup should be stored in the cloud. We explain how to create a server backup with Rsync.

Using a MySQL backup script

The approaches which have been presented so far are suitable for exporting a single MySQL database quickly. It is a good idea to create a MySQL backup script for regular backups or for creating MySQL backups of multiple databases. This is carried out periodically and backs up the contents of all existing MySQL databases as MySQL dumps does in a predefined folder. Using a MySQL backup script standardises the backup process and reduces the risk of errors.

The last approach must be extended to create the backup script. Create an additional folder for MySQL backups. Each backup will end up in its own subfolder. This folder contains the MySQL dumps from individual databases. A script file which automates the process is also worth creating. Follow the individual steps to set up the MySQL backup script:

  1. Firstly, create an additional folder for the MySQL backups:
mkdir -p ~/.mysql-dump/backups/
  1. Create the actual MySQL backup script afterwards:
cat << EOF > ~/. mysql-dump/backup_databases
DB_USER='your_db_user_name'
DB_PASS='your_db_password'
# Create new folder for current backup
backup_dir='./backups/$(date +%F_%H%M)'
# Determine databases
databases=$(mysql --user="$DB_USER" --password="$DB_PASS" -Bs --execute 'SHOW DATABASES')
# Export all databases
for database in $databases; do
    mysqldump --user="$DB_USER" --password="$DB_PASS" "$database" > "${backup_dir}/${database}.sql"
done
# Delete variables from memory
unset DB_USER DB_PASS
EOF
  1. Open the configuration file in the nano editor and adjust the database access data:
nano ~/. mysql-dump/backup_databases

Replace the 'your_db' placeholders with values that match your database. Press [Ctrl] + [X] followed by [Y] and [Enter] to close the file and save the changes.

  1. Run the MySQL backup script once the preparations are complete:
cd ~/. mysql-dump/ && source backup_databases
  1. Test if the backup was created in the last step:
ls -la ~/. mysql-dump/backups/*

The backup worked if you can see files with the .sql extension.

Import MySQL dump to database

The mysqldump command is used only to export a MySQL database. The mysql command is needed to import a dump. We’ll show the general scheme below:

mysql --user=<username> --password=<password> database_name < <path/to/mysql_dump.sql>

The following command can be used if a MySQL dump database configuration file exists:

source ~/. mysql-dump/database.conf
mysql --user="$DB_USER" --password="$DB_PASS" "$DB_NAME" < <path/to/mysql_dump.sql>
Was this article helpful?