MySQL Database Dump

Exporting a database dump
Importing a database dump
Working with dumps from the command line
 

Exporting a database dump

To create a backup, open the PHPMyAdmin interface.

• In the left-hand panel, select the required database.
• Go to the Export tab.

• If necessary, adjust the export settings and click Go at the bottom of the page.

The file with database contents will be downloaded to your computer. For large databases, we recommend using the command-line connection method.

Importing a database dump

We recommend creating a new database and importing the dump into it.

To restore a database, open the PHPMyAdmin interface and follow these steps:

• In the left-hand panel, select the new database.
• Open the Import tab.
• Click Choose File and select the dump file stored on your computer. If the dump file is larger than 1 GB uncompressed, it can only be imported via the command line.
• Specify the file encoding (usually the same as the site encoding).
• If necessary, adjust the import settings and click Go. Wait for the process to complete.

After the database has been imported, update your site’s configuration files to replace the old database name with the new one, then verify functionality.  For large databases, we again recommend using the command line.

Working with dumps from the command line

Connecting to the hosting server via SSH allows you to manage databases remotely without downloading dumps to your computer. Databases larger than 1 GB uncompressed can only be handled via the command line.

• SSH client setup (e.g., PuTTY, WinSCP)
• SSH access

To create a database dump, run the following command:

mysqldump -y -f -q --default-character-set=binary --create-options --single-transaction --skip-extended-insert --add-drop-table -h dbhost -u dbuser -pdbpassword dbname > dump.sql

where:

dbhost — database server address,
dbuser — MySQL username,
dbpassword — MySQL user password (written immediately after -p, without a space),
dbname — database name,
dump.sql — name of the dump file. Dump will be created in the current directory.

To import a database dump, run the following command:

mysql -h dbhost -u dbuser -pdbpassword dbname < dump.sql

We recommend creating a new database beforehand and importing the dump into it.  The dump file must be uploaded to the hosting server (see the article FTP — Uploading Files to the Server).

After the database has been imported, update your website’s configuration files to use the new database name and verify functionality.