How to Create a MySQL Database Dump

MySQL is the most popular relational database management system that performs various operations with tables, such as adding, deleting, searching, sorting, and outputting data based on user queries.

It's important to understand that MySQL controls databases but is not itself a database. Therefore, MySQL and the database are separate entities:

• MySQL is a program that operates on information.
• The database is the information recorded on a hard disk.

Based on this architecture, MySQL supports exporting information — creating a database dump.

This functionality allows several useful operations:

1. Database Backup: Unexpected situations when using cloud (or local) servers can lead not only to system failures but also to data loss. Therefore, it’s important to regularly create database dumps, which can be stored on other secure storage devices.
2. Database Transfer from One Server to Another: Manually copying database elements may be challenging or impossible when migrating from one server to another. A dump makes it possible to transfer data quickly.

A database dump is essentially a sequential set of SQL instructions that create an exact copy of the original database, including both its structure and content.

This guide will cover the primary methods for creating a database dump and importing it back into MySQL to restore data.

Preparing a Test Database

We will create a cloud database to test the examples in this guide. If you already have a MySQL database where you can test the dump creation function, you can skip this step.

In the Hostman panel, we will create a MySQL 8 database, leaving all other parameters as default.

You can connect to the cloud database via a terminal. The necessary command can be copied from the control panel.

Let's connect to our database:

mysql -u USER -p'PASSWORD' -h HOST -P 3306 -D DATABASE

For example, a real connection command might look like this:

mysql -u gen_user -p'sU}NEyx#<2P~\e' -h 91.206.179.29 -P 3306 -D default_db

Next, we need to create a simple table consisting of three columns:

CREATE TABLE People (
    id INT,
    name VARCHAR(255) NOT NULL,
    bord DATE
);

And populate it with some values:

INSERT INTO People VALUES 
(120, 'Natalie', NOW()), 
(121, 'Meredith', NOW()), 
(122, 'James', NOW());

This fills the new database so that we can later create a dump from it.

By the way, on the Users tab of the database management page, there are buttons that open interfaces for graphical MySQL database administration tools — phpMyAdmin and Adminer.

Method 1: Console Terminal

A more traditional but less interactive way to create a MySQL database dump is by using the appropriate command in a console terminal.

To do this, you need to connect to MySQL via an SSH connection and then enter the dump creation command:

mysqldump -u USER -p'PASSWORD' -h ADDRESS -P PORT DATABASE > FILE

Let's break down each of the specified parameters:

• USER: The username used to authenticate in MySQL.
• PASSWORD: The password for the user to authenticate in MySQL.
• ADDRESS: The IP address of the remote MySQL server.
• PORT: The port of the remote MySQL server.
• DATABASE: The name of the database to be dumped.
• FILE: The name of the file where the database dump will be saved on the local machine.

There are two possible ways to create a dump via the console:

1. Local MySQL: The dump is created from a database located on a local MySQL server. In this case, we don’t need to specify the ADDRESS and PORT parameters.

2. Remote MySQL: The dump is created from a database located on a remote MySQL server. In this case, you need to specify ADDRESS and PORT.

Local MySQL dump command example:

mysqldump -u admin -p'qwerty123' default_db > just_dump.sql

Remote MySQL dump command example:

mysqldump -u admin -p'qwerty123' -h 91.206.179.29 -P 3306 default_db > just_dump.sql

In both cases, for security reasons, you can omit the explicit password specification — this way, the system will prompt you to enter the password manually:

mysqldump -u admin -p default_db > just_dump.sql
mysqldump -u admin -p -h 91.206.179.29 -P 3306 default_db > just_dump.sql

Warnings and Errors

After executing the command, several warnings and errors may appear in the console output. Let’s break down each message in detail.

Password Security Warning

The first warning from MySQL notifies you about the insecurity of using a password as an explicit parameter:

mysqldump: [Warning] Using a password on the command line interface can be insecure.

To suppress this warning, use the -p flag without specifying the password directly.

Global Transaction Identifier (GTID) Warning

The next warning concerns the inclusion of the Global Transaction Identifier (GTID) in the resulting dump and suggests disabling it with the --set-gtid-purged=OFF flag:

Warning: A partial dump from a server that has GTIDs will by default include the GTIDs of all transactions, even those that changed suppressed parts of the database. If you don't want to restore GTIDs, pass --set-gtid-purged=OFF. To make a complete dump, pass --all-databases --triggers --routines --events.

GTID (Global Transaction Identifier) is a unique 128-bit identifier associated with each transaction, which improves overall data consistency.

Disabling GTID may lead to data inconsistency (for example, due to duplication of certain SQL statements).

Data Dump Consistency Warning

Another GTID-related warning indicates that the dump operation is not atomic:

Warning: A dump from a server that has GTIDs enabled will by default include the GTIDs of all transactions, even those that were executed during its extraction and might not be represented in the dumped data. This might result in an inconsistent data dump.
In order to ensure a consistent backup of the database, pass --single-transaction or --lock-all-tables or --master-data.

This means that database changes performed by other applications during the dump creation may be missing, leading to data inconsistency.

To avoid this issue, use one of the following flags:

• --single-transaction to create the dump within a single transaction.
• --lock-all-tables to block any other operations on the database during the dump.

Access Denied Error

You might encounter an error preventing the dump creation due to insufficient privileges:

mysqldump: Error: 'Access denied; you need (at least one of) the PROCESS privilege(s) for this operation' when trying to dump tablespaces

Even if the user specified in the command has all database privileges, they may lack the global PROCESS privilege.

To grant this privilege, execute the following command:

GRANT PROCESS ON *.* TO 'admin'@'localhost';

However, this is not the best solution from a security perspective. Instead of granting global privileges, it's better to use the --no-tablespaces flag during the dump command execution.

With all the additional flags, the dump creation command will look like this:

mysqldump -u admin -p'qwerty123' -h 91.206.179.29 -P 3306 default_db --no-tablespaces --set-gtid-purged=OFF --single-transaction > just_dump.sql

In this case, only one harmless warning will remain about explicitly specifying the password:

mysqldump: [Warning] Using a password on the command line interface can be insecure.

Non-existent Database Error

If you accidentally specify the name of a non-existent database, an unclear error will appear denying access to the database for the specified user:

ERROR 1044 (42000): Access denied for user 'admin'@'%' to database 'default_db'

This can cause confusion, so always double-check the accuracy of the database name specified in the command.

Dump File

After successfully executing the dump command, you can check the file system using:

ls

You should see the corresponding database dump file:

just_dump.sql  resize.log  snap

Although you can open this file in any text editor, its size may be quite large, especially if the original database contained a lot of information:

cat just_dump.sql

At the beginning of the file, there is information about the created dump, followed by SQL instructions:

-- MySQL dump 10.13  Distrib 8.0.40, for Linux (x86_64)
--
-- Host: 37.220.80.65    Database: default_db
-- ------------------------------------------------------
-- Server version       8.0.22-13

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;

...

/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2025-01-19  5:33:16

The output file doesn't have to be saved in the current directory; you can specify any other directory:

mysqldump -u admin -p'qwerty123' -h 91.206.179.29 -P 3306 default_db --no-tablespaces --set-gtid-purged=OFF --single-transaction > /tmp/just_dump.sql

In this case, we create the dump file just_dump.sql in the /tmp directory.

Dumping Multiple Databases

In real projects, MySQL often handles multiple databases. You can use a special flag to dump all existing databases:

mysqldump -u admin -p'qwerty123' -h 91.206.179.29 -P 3306 --all-databases --no-tablespaces --set-gtid-purged=OFF --single-transaction > just_dump.sql

This command differs from the previous one only in that the --all-databases flag is specified instead of a specific database name.

Alternatively, you can list the databases you want to include in the dump:

mysqldump -u admin -p'qwerty123' -h 91.206.179.29 -P 3306 db_first db_second db_third --no-tablespaces --set-gtid-purged=OFF --single-transaction > just_dump.sql

Structure Without Data

You can create a dump containing only the database structure (table schemas) without any data by using the --no-data flag:

mysqldump -u admin -p'qwerty123' -h 91.206.179.29 -P 3306 default_db --no-data --no-tablespaces --set-gtid-purged=OFF --single-transaction > just_dump.sql

Specific Tables

Instead of dumping an entire MySQL database, you can dump only specific tables by listing their names after the database name:

mysqldump -u admin -p'qwerty123' -h 91.206.179.29 -P 3306 default_db table1 table2 --no-data --no-tablespaces --set-gtid-purged=OFF --single-transaction > just_dump.sql

On the other hand, you can dump a database excluding specific tables using the --ignore-table parameter:

mysqldump -u admin -p'qwerty123' -h 91.206.179.29 -P 3306 default_db --ignore-table=default_db.logs --no-data --no-tablespaces --set-gtid-purged=OFF --single-transaction > just_dump.sql

Note that the table name must always be preceded by the database name and separated by a dot.

To exclude multiple tables, list each one with the --ignore-table option:

mysqldump -u admin -p'qwerty123' -h 91.206.179.29 -P 3306 default_db --ignore-table=default_db.table1 --ignore-table=default_db.table2 --no-data --no-tablespaces --set-gtid-purged=OFF --single-transaction > just_dump.sql

Character Encoding

In some cases, it may be necessary to explicitly specify the character encoding for the dump:

mysqldump -u admin -p'qwerty123' -h 91.206.179.29 -P 3306 default_db --no-tablespaces --set-gtid-purged=OFF --single-transaction --default-character-set=utf8 > just_dump.sql

Typically, UTF-8 is the preferred character encoding.

Archiving the Dump

Sometimes it’s useful to compress the dump immediately after creation. You can do this by piping the mysqldump output into gzip, then saving the compressed archive:

mysqldump -u admin -p'qwerty123' -h 91.206.179.29 -P 3306 default_db --no-tablespaces --set-gtid-purged=OFF --single-transaction | gzip > just_dump.sql.gz

If you check the current directory with the ls command, you’ll see the compressed dump:

just_dump.sql  just_dump.sql.gz  resize.log  snap

Restoring Data

A database dump is usually created to restore data in the future, for example, in case of data loss or server migration.

To load the database dump into MySQL and restore data, use the following command:

mysql -u admin -p'qwerty123' -h 91.206.179.29 -P 3306 default_db < just_dump.sql

If the dump file size is too large, MySQL may have a default limit that prevents loading it.

To adjust the maximum allowed dump size, you can use the --max_allowed_packet flag:

mysql -u admin -p'qwerty123' -h 91.206.179.29 -P 3306 default_db --max_allowed_packet=64M < just_dump.sql

In this example, the maximum allowed dump size is set to 64 MB.

mysql -u admin -p'qwerty123' -h 91.206.179.29 -P 3306 default_db < just_dump.sql
mysqldump -u admin -p'qwerty123' -h 91.206.179.29 -P 3306 --all-databases --no-tablespaces --set-gtid-purged=OFF > just_dump.sql

Method 2: Using phpMyAdmin

If you're using phpMyAdmin, creating a database dump can be done through the graphical interface without manually executing commands — phpMyAdmin handles everything for you.

1. Log In to phpMyAdmin. Open the phpMyAdmin interface and log in with your credentials. 
2. Select the database. In the left sidebar, choose the database you want to export. This will open a page displaying the list of existing tables within the selected database.
3. Configure the export. Click the Export button. It will take you to a dedicated page to configure the database export (dump).

You can also access the export page from the phpMyAdmin home page, but doing so may not display all databases available for export. It's better to first navigate to the specific database and then click Export.

Note that phpMyAdmin allows exporting only databases that contain tables. Empty databases cannot be exported.

There are two export options in phpMyAdmin:

• Quick Export. It creates the dump using default export settings.
• Custom Export. It Allows you to manually configure the export settings, such as excluding specific tables, changing character encoding, and adjusting format options.

phpMyAdmin supports exporting to various formats beyond just SQL, such as PDF, JSON, CSV, YAML, and others.

The configuration options for creating a dump in phpMyAdmin are more user-friendly and visually intuitive than command-line flags.

4. Start the export. Once you've configured all the export parameters, scroll down and click the Export button. The dump file will be generated and downloaded through your browser.

Method 3: Using Adminer

Creating a database dump in Adminer is very similar to phpMyAdmin. In fact, Adminer’s graphical interface is even simpler.

1. Log In to Adminer: Start by logging into Adminer, then navigate to the export page by clicking the Export link in the left sidebar.

2. Configure the Export. Adminer does not have predefined export types, so the system immediately offers all configuration options.

You can select specific database tables to include in the dump.

The dump file can be either saved (in a specific format or as a GZIP archive) or opened in a new window for manual copying of SQL instructions.

Conclusion

The native way to create a MySQL database dump, without requiring additional tools, is by using the mysqldump command with additional parameters.

An alternative is to use visual database management tools with graphical interfaces. Utilities like phpMyAdmin or Adminer simplify database interactions by providing a user-friendly and interactive environment. This is particularly useful for those who are not well-versed in SQL syntax, turning tasks such as creating a dump into a series of simple mouse clicks.