Exporting a MySQL database helps create a database backup that can then be used during the website restoration process, cloning, or migration to a new server. You can easily restore the database later or use it to upload data to a newly created database by importing the database backup you created.
In this post, we explore what a database backup is and how to export and import MySQL databases using different tools.
Nexcess database management options
Before we can learn how to export a MySQL database and import one when needed, let’s quickly look at the Nexcess database management options.
You can manage MySQL databases in Nexcess Cloud by adding new and removing existing ones, creating and deleting MySQL users, and changing their passwords. You can also use SiteWorx to manage your MySQL databases and launch phpMyAdmin:
MySQL database backup — SQL and other file formats
A website backup usually consists of a database backup and a backup of all website files, often placed in a file archive. We can use Tar and Gzip to create compressed file archives. Still, when it comes to saving a database backup, it is not always clear how exactly your database tables can be saved to file and what happens during the process of importing MySQL databases.
A MySQL database backup can be created in different file formats depending on what type of file you would like to get and how you plan to use it afterward. The most popular file formats for exporting MySQL databases are SQL, XML, CSV, and JSON. Each format allows you to create a file of a different structure containing your database data.
CSV stands for comma-separated values, and a CSV file will simply contain all table data separated by a specific delimiter. This format can be useful for importing information into spreadsheet software like Excel. XML and JSON, which are all similar in some ways, with the latter being widely used in NoSQL database management systems.
Database dump
Database backups in SQL format that are often referred to as database dumps are the most common database backup format. MySQL dumps may be challenging to read and navigate for a human, but it is the best format for MySQL with regard to understanding and interpreting the database structure and data elements.
A MySQL database dump is a file containing a list of SQL statements that can fully recreate the database structure and import all data to the newly recreated tables. You can view a database dump as any other file, search for information in it, or even manually add new tables or table rows to it. So mysqldump is the MySQL or MariaDB command line utility for creating database dumps.
Exporting and importing MySQL databases — three main options
You can export a MySQL database in a few different ways, both using the command line interface and database administration utilities like phpMyAdmin. In this article, we will review the process of importing and exporting MySQL databases using three main options:
- MySQL/MariaDB command line interface
- phpMyAdmin tool
- WordPress Command Line Interface (WP-CLI)
What method to choose
Using the phpMyAdmin tool can be useful if you need to download a backup to your computer. But if you would like to transfer a database backup somewhere else, for example, when you need to migrate your WordPress site manually, using the command line interface can be more convenient.
Another essential thing to remember about phpMyAdmin is that it uses its PHP limits specified for database export and import operations, including the size of the file uploaded and the amount of time the script can run.
Therefore, phpMyAdmin can freeze when importing and exporting massive MySQL databases. When working with large databases using the command line interface, the operation will need a little more time to complete, but you should not run into server resource limits.
Exporting a MySQL database
Exporting a MySQL database can help you back up your website manually, for example, before making any major changes to it or performing upgrades. As we learned, you can export a MySQL database in different file formats, but in this tutorial, we will focus on creating a database dump in SQL.
Exporting a MySQL database method #1 — using the MySQL/MariaDB command line interface
A MySQL database backup in SQL format can be created from the command line using a MySQL/MariaDB mysqldump utility. Here is how the mysqldump command can be used to create a database dump:
mysqldump -u <database_user> -p -v <database> > <path to the destination folder>/<database backup>.sqlUnless you are logged in as root, which would allow you access to any database bypassing authentication in most cases, we need to connect to the database first. After that, you can run the mysqldump command from anywhere on the system, but unless you specify another folder to save the backup to, it will be saved to your current working directory.
We use the -u flag to specify the database user and -p for MySQL to request our user’s password. Please note that we do not include the password itself in the command, as MySQL will ask us to authenticate once we run the command. The -v flag is a very useful option that enables verbose mode. This way, you can see the exact procedures that MySQL or MariaDB is performing to create a backup.
You must include the database you would like to export and specify the path to and the name of the database backup that will be created. You can skip the path if you need to save the dump to the same folder from which you are running the command.
In the example below, we export a MySQL database named wptest to the backup folder. The file in SQL format wptest_database.sql is the name of the backup we specified. Next, we entered the database user’s password, after which we connected to the database server, and MySQL began performing backup operations:
[wordpress@host html]# mysqldump -u wpadmin -p -v wptest > backup/wptest_database.sql
Enter password:
-- Connecting to localhost...
-- Retrieving table structure for table wp_actionscheduler_actions...
-- Sending SELECT query...
-- Retrieving rows…
…
Exporting a MySQL database method #2 — using the phpMyAdmin tool
The phpMyadmin tool is a popular database administration software that can help export and import MySQL databases. The main benefit of using it for exporting MySQL databases is that a backup gets saved to your computer instead of a folder on the server.
Log in to phpMyAdmin from your Nexcess Client Portal or the SiteWorx control panel and choose the database you want to export. Then, select Export from the top menu to open the database export interface:
There are two export methods to choose from — Quick and Custom. The Quick method allows you to choose what format you would like to use for your database backup, with SQL being chosen by default. The Custom method allows you to select only specific tables to include in the backup and presents you more options to get more control over the export process and the way data is saved.
Once ready, click Go, and a new database backup in the chosen file format will be saved to your computer:
Exporting a MySQL database method #3 — using the WordPress Command Line Interface (WP-CLI)
WordPress Command Line Interface (WP-CLI) is a powerful utility for managing a WordPress website. There are a lot of useful WP-CLI commands for managing your WordPress database. For example, the wp db export command allows you to export your WordPress database by saving a database backup in SQL format.
You do not need to specify your WordPress database name, as WP-CLI reads it from your WordPress configuration file, along with the database user and password:
wp db export <database backup name>The wb db export command simply runs the mysqldump utility behind the scenes. If you do not specify the database backup name, WP-CLI will choose it automatically, containing the date when the backup was saved:
[wordpress@host html]# wp db export
Success: Exported to 'wptest-2022-05-19-65a3f19.sql'.Importing a MySQL database
Knowing how to import a MySQL database can help you restore it in case of data loss and any other issues, move your website to a new server or create a staging version of it.
The phpMyAdmin tool allows you to import a database backup in different formats, so using it is recommended if you import a non-SQL file. You can use the MySQL or MariaDB command line interface to restore from a file in a different format, but it will create additional difficulties. Below we will review the process of importing MySQL databases from SQL files.
Importing a MySQL database method #1 — using the MySQL/MariaDB command line interface
A MySQL database backup can be imported into a database from the command line using the mysql command. Please note that any existing content in the target database will be overwritten when importing a MySQL database backup. You can always modify your database dump to achieve different results or use specific flags like --ignore or --replace to modify the import process:
mysql -u <database_user> -p <database> < <path to the database backup>/<database backup>.sqlWe will need to authenticate with the database user like we did when exporting a MySQL database and specify the path to the database backup.
As you can see, we are using an arrow pointing to the database name here as it is an import operation. You can still use the -v flag, but importing a MySQL database will produce a hugely verbose output. If completed successfully, the command will not display anything when the database backup is imported:
[wordpress@host html]# mysql -u wpadmin -p wptest < wptest-2022-05-19-65a3f19.sql
Enter password:In the example above, we successfully restored our wptest database from the wptest-2022-05-19-65a3f19.sql database backup we saved earlier.
Importing a MySQL database method #2 — using the phpMyAdmin tool
Log in to the phpMyAdmin interface, choose the destination database, and click Import from the top menu. Next, choose the database backup file by clicking the Choose File button and locating it on your computer. You can choose a file of any different format to import; phpMyadmin also supports compressed files, so you do not need to uncompress your backup before importing it. The phpMyAdmin tool will read the file format and may add more configuration options based on it.
The phpMyAdmin tool will present you with multiple options for specifying the import operation behavior. Typically, you can leave the default values in place unless you know what you want to change:
Once ready, click Go, and phpMyAdmin will restore the database from the file you provided. The phpMyadmin utility will provide detailed output when the database backup is successfully imported.
If the software encounters any issues during the process, phpMyAdmin would show an error message you must review carefully to understand what happened. For example, it can import the backup only partially or not do it at all. In that case you, may need to use the command line interface to finish the operation:
Importing a MySQL database method #3 — using the WordPress Command Line (WP-CLI)
The WordPress Command Line Interface (WP-CLI) can make importing MySQL databases for WordPress sites more straightforward. For example, the wp db import command will take the backup file you specified and import it into the WordPress database mentioned in wp-config.php, also using the credentials provided there:
wp db import <database backup name>We have imported our wptest-2022-05-19-65a3f19.sql database backup that we created using the wp db export command:
[wordpress@host html]# wp db import wptest-2022-05-19-65a3f19.sql
Success: Imported from 'wptest-2022-05-19-65a3f19.sql'.Conclusion
Importing and exporting a MySQL database can help you create a database backup in different file formats that can then be used for a wide variety of tasks. A MySQL database dump — which is a backup file in SQL format — is the most popular option to use when creating a database backup.
You can export and import MySQL databases using the command line interface or database administration software like phpMyAdmin. If you are dealing with database backups in a format other than SQL, database administration applications that provide a graphical user interface make the import and export processes much manageable.
Nexcess has transformed the concept of managed hosting to ensure your business stays online at all times and is protected from various threats. Nexcess hosting plans are optimized for the platform of choice to provide better performance and easier administration.
A High-Performance Database Technology: MariaDB 10.5
Run a carefully tuned and tested configuration of MariaDB: an open-source enhanced version of MySQL database server, written by the original creators of MySQL.
Boasting solutions with our enterprise-level technology stack, performance monitoring, and automatic updates — check out our Managed Hosting plans to discover what else we can offer to take your online business to a new level.
Note about the original publication date
This article was originally published in September 2019. It has since been updated for accuracy and comprehensiveness.
Recent articles
- How to customize a WordPress site | How to customize the Nexcess MAPPS MU (must-use) plugin
- Nexcess-provided WordPress plugins for ecommerce and sites
- Convert MyISAM to InnoDB tables for database performance
