How to create a database dump

A database dump is a copy of all the data, structure, and settings of a database saved as a file. This file contains all the tables, records, indexes, and other elements. It allows for restoring the database to its original state if needed.

A dump can be used to transfer data between different servers or for development and testing, where having an exact copy of the working database is important.

There are 3 main ways to create a database dump — we will discuss each of them in detail.

Dump via phpMyAdmin

1. Log in to the hosting control panel and open phpMyAdmin.
2. Select the desired database.
3. Click on "Export."
4. Click "Export" again to download the database dump file with the .sql extension.

Important: Before exporting, ensure that the connection encoding is set to UTF-8 to avoid encoding issues when transferring data.

Dump via Hosting Control Panel

1. Find the "Backups" section.
2. Select the database you want to back up.
3. Click "Create Copy" or "Create Dump."
4. Download the dump file once the process is completed. The file is usually in .sql or .gz format.

Dump via SSH

This method is suitable for more experienced users who are comfortable working in the Terminal or in cases where the hosting control panel or phpMyAdmin is unavailable.

To create a backup, enter the following command in the console:

mysqldump -u[username] -p[password] [database_name] > dump.sql

Let's break it down:

• [username] — the database username.
• [password] — the connection password.
• [database_name] — the name of the database for which you want to create the dump.

If you encounter encoding issues (e.g., question marks instead of text), add a parameter to use UTF-8 encoding:

mysqldump --default-character-set=utf8 -u[username] -p[password] [database_name] > dump.sql