How to export a database with phpMyAdmin?

Introduction

phpMyAdmin is a comprehensive tool that offers numerous functions for managing databases. One of these functions is the function Export database. In this guide we will go through the process of exporting one or all databases. It will also show how to exclude unneeded database tables during this process. After that we will discuss the two types of exports that phpMyAdmin offers - Quick and Custom.

In case you don't know what phpMyAdmin is, we have also put together an article for you here: What is PHPMyAdmin and where can I access it?

Access phpMyAdmin

Before you can access the phpMyAdmin feature, you need to log in to us with your cPanel account. Please read our instructions on how to do this: How can I log in to the cPanel?

After logging in, navigate to the area "Databases" and click on the icon "phpMyAdmin".

After that you will be redirected to the function "phpMyAdmin". Here you can start the Management of your database start

Export database

To export a database from your account, you need to access the "Export" tab. To access it, please select a database from the list on the left side of the screen. You will then be taken to the page of the respective database, where you should click on the "Export" tab in the tab bar at the top.

Once you have been redirected to the "Export" page, you will see the section "Export Method".

In the "Export method" section you can see the both available options – Custom and Quick. Let's go through both of them so that you can choose the most suitable option for your case.

Fast export method

The "Quick" export method is relatively simple. To select it, please use the radio button in the "Export Method" section.

You only have the option to select the format of the exported database file. This can be done using the drop-down menu in the "Format" section.

After you have selected the format, please click the "OK" button below the drop-down menu.

The result is a download and saving of the exported file on your local computer.

Custom export method

To select the "Custom" export method, please use the radio button in the "Export Method" section. If you select this option, some sections below will be expanded so that you can customize the export process.

In the "Tables" section you can select which tables should be exported. Below that there is a content area that contains all the tables of the selected database. On the right side you will find checkboxes that allow you to select whether you want to export the structure of the database, its data or both.

On the left side there is another check box. With this checkbox you can select or deselect a specific table by activating or deactivating the checkbox.

Output section

In the "Output" section you can customize the data to be exported. Let's go through all the options.

Rename exported databases/tables/columns: If you select this check box, the exported databases, their tables and columns will be renamed.

Save output to file: Selecting this option via the radio button will provide 4 additional options for the output of the file:

1. Template for the file name: In this text field you can specify the name of the file that will be created during export. The default value is @SERVER@, which means that the file will be named either "localhost.sql" or "localhost.zip", since the MySQL server is localhost.
2. Character set of the file: Use this drop-down list to select the character set of the exported file.
3. Compression: In this drop-down list you can select a compression method for the exported file.
4. Export databases to separate files: You can check this box to make phpMyAdmin export each database to a separate file instead of creating one big file with all databases.

Display output as text: When this option is selected, phpMyAdmin refreshes the page and generates a text format of the exported data.

Skip tables larger than: This text field allows you to define the largest table that phpMyAdmin should export.

Format specific options

The settings in this section allow you to format the file to be exported. The following options are available:

Show comments (contains information such as export timestamp, PHP version and server version): If you put a check mark in this check box, all comments included in the exported file will be displayed.

Additional custom header comment (\n separates lines): This text field allows adding custom header comments separated by line breaks.

Adding a timestamp for the date of creation, last update and last check of the databases. This adds timestamps to events in your database, such as - date of creation, last update and last check.

Merge export into transaction: If you select this check box, the export will be included in a transaction.

Disable foreign key checks: If you select this check box, foreign key checks are disabled.

Export view as table: If you check this box, phpMyAdmin will export the views as tables.

Export metadata: If you check this box, the database metadata will be exported.

Database system or older MySQL server to maximize output compatibility with: This drop-down menu allows you to select the database system with which the exported file is most compatible.

Object creation options

The settings in this section allow you to customize the instructions added during the export process.

CREATE DATABASE / USE - Add statement: This check box adds the DROP DATABASE IF EXISTS statement.

DROP TABLE / VIEW / PROCEDURE / FUNCTION / EVENT / TRIGGER Add instruction: If this checkbox is selected, the Drop View / Procedure / Function / Event / Trigger instructions will be added.

CREATE TABLE Add statement: When this check box is selected, the Create TABLE statement is added.

IF NOT EXISTS (less efficient, because indexes are generated during table creation)

AUTO_INCREMENT Value: This check box is selected by default and will be applied to your database.

CREATE VIEW Add statement: If you select this check box, the Create View statement is added when it is selected.

CREATE PROCEDURE / FUNCTION / EVENT Add statement: If this checkbox is selected, the "Create Procedure/Function/Event" statement will be added when it is selected.

Instruction CREATE TRIGGER Add: If this checkbox is selected, the Create Trigger statement will be added.

Data creation options

Truncate table before inserting: If you select this check box, the table will be deleted before insertion.

INSERT DELAYED INSTRUCTIONS: If you select this check box, the delayed statement will be inserted.

IGNORE Insert instructions: If you select this check box, the IGNORE statement will be inserted.

Function to be used when exporting data: With this dropdown list you can select which function phpMyAdmin should use when saving the data.

Syntax to use when entering data: You can select the different syntax types to be used when inserting data. The following syntaxes can be selected via a radio button:

1. Column names include in every INSERT statement
2. several lines insert into each INSERT statement
3. both of the above
4. none of the above

Maximum length of the created query: In this text field you can specify the length of the generated query.

Output binary columns in hexadecimal notation (e.g. "abc" becomes 0x616263): If you want to output the binary columns in hexadecimal notation, select this check box.

Output TIMESTAMP columns as UTC (allows output and reload of TIMESTAMP columns between servers in different time zones): If you select this check box, the data is output in the UTC time zone.

Once you have made all the settings, please click on the "OK" button at the bottom left of the page.

The result is a download and saving of the exported file on your local computer.

Export all databases

The procedure is identical to the one described above, with a few exceptions. When you are redirected from cPanel to the phpMyAdmin page, simply direct your attention to the tab bar on the current page instead of selecting a database from the list on the left. Next to the tab { "status". you will see the tab "Export". Please click on it.

When you are redirected to the "Export" page and select the "Custom" method, instead of the "Tables" section you will now see the "Databases" section where you can select the databases you want to export.

By default, all are selected, but you can click on a database to select it for a single export. You will know you have selected it because it will be highlighted in blue. You can also use the Select All and Deselect All buttons in this section to select and deselect all databases in the Contents pane, respectively.

And ready! Both methods for Export your database with the phpMyAdmin Function of cPanel. We hope that after reading this guide you will have the necessary knowledge to fully use this feature.

If you encounter any technical problems during this process, please contact our support team. You can reach us around the clock via the Ticket system in your customer area.

Video tutorial

Attached you will find a video tutorial. The video explains the quick export of a database most often used by users.

Read also: How to import a database with phpMyAdmin?