Introduction

phpMyAdmin is a vast tool providing many functionalities for database management. One of these features is the Export database utility. In this tutorial, we will review the process of exporting single and all databases. As well as how to exclude unnecessary database tables during this process. Afterward, we will go over the two export types phpMyAdmin provides - Quick and Custom. Let’s get started!

Accessing phpMyAdmin

Before you can access the phpMyAdmin functionality, you need to log into your cPanel account with us. To do so, please check our tutorial on how to access the cPanel service.

Once logged in, please navigate to the “Database” section and click on the “phpMyAdmin” icon. 

Clicking on the result will redirect you to the “phpMyAdmin” feature, allowing you to start managing your database.

Exporting Databases

To export a database on your account, you need to access the “Export” tab. To access it, please select a database from the list located in the left panel of the screen. This sends you to the individual database page, and from the tab bar on the top, you have to access the “Export” tab.

Once you have been redirected to the “Export” page, you can see the “Export method” section.

Under the “Export method” section, you can see the two available options - Custom and Quick. Let us go over both so you can be able to choose the best one for your case.

Quick export method

The “Quick“ export method is pretty straightforward. To select it, please use the radio button located under the “Export method” section.

It only gives you the option of choosing the format of the exported database file. This may be done via the dropdown menu under the “Format” section.

Once the format has been chosen, please click the “Go” button located beneath the dropdown menu.

This starts a download process and saves the exported file format on your local computer. 

Custom export method

To select the “Custom” export method, please use the radio button under the “Export method” section. Selecting it expands a couple of sections underneath, allowing you to customize the export process.

Format section

This is analogic to the “Quick” method. It allows you to choose the format in which the database to be exported.

Tables section

The “Tables” section allows you to choose which tables to export. It has a content box underneath it, containing all the tables of the selected database. On the right side, you can see tick boxes, which allow you to choose if you want to export the database’s structure, its data, or both.

On the left side, you can find another tickbox, allowing you to select/unselect a specific table by ticking/unticking the box.

Output section

The “Output” section allows you to customize the data that is being exported. Let’s go over all the options there.

• Rename export databases/tables/columns - Placing a tick inside this tickbox gives new names to the exported databases, their tables, and columns.
• Save Output to a file - Selecting this option with the radio button provides 4 additional options that can shape the output of the file:
  • File name template - Using this text field, you can define the name of the file generated by the export. The default value is @SERVER@, which means that the file is going to be called either “localhost.sql” or “localhost.zip” as the MySQL server is localhost. 
  • Character set of the file - You may use this dropdown to select the charset of the exported file.
  • Compression - You can use this dropdown to select a compression method for the exported file.
  • Export databases as separate files - You can put a tick inside this checkbox to cause phpMyAdmin to export each database under a separate file instead of generating one big file containing all the databases.

• View 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 - Using this text field, you can define the largest table that phpMyAdmin is to export.

Format-Specific options section

Using the settings under this section, you are able to format the file that is going to be export. Here they are:

• Display comments (includes info such as export timestamp, PHP version, and server version) - Putting a tick inside this checkbox displays all the comments included in the exported file.
• Additional custom header comment (\n splits lines) - This textbox allows you to add custom header comments that separated by newlines.
• Include a timestamp of when databases were created, last updated, and last checked - This adds timestamps for events in your database such as - date of creation, last updated, and last checked.
• Enclose export in a transaction - Ticking this checkbox encloses the export in a transaction.
• Disable foreign key checks - Checking this tickbox disables the foreign key checks.
• Export views as tables - Checking this tickbox causes phpMyAdmin to export the views as tables.
• Export metadata - Ticking this checkbox exports the database metadata.
• Database system or older MySQL server to maximize output compatibility with - This dropdown allows you to choose a database system that you want the exported file to be most compatible with.

Object & data creation options

The settings under this section help you customized the statements that added during the export process.

• Add DROP DATABASE IF EXISTS statement - This checkbox adds the DROP DATABASE IF EXISTS statement.
• Add DROP TABLE / VIEW / PROCEDURE / FUNCTION / EVENT / TRIGGER statement - This tickbox adds the Drop View/Procedure/ Function/Event/Trigger statements when selected.
• Add CREATE TABLE statement - This tickbox adds the Create table statement when selected.
  • IF NOT EXISTS (less efficient as indexes will be generated during table creation)
  • AUTO_INCREMENT value - This tickbox is enabled by default and applied to your database.

• Add CREATE VIEW statement - Ticking this checkbox adds the Create View statement when selected.
• Add CREATE PROCEDURE / FUNCTION / EVENT statement - Checking this box adds the Create Procedure/Function/Event statement when selected.
• Add CREATE TRIGGER statement - This checkbox adds the Create Trigger statement when selected.
• Truncate table before insert - Ticking this checkbox empties the table before inserting it.
  • INSERT DELAYED statements - Ticking this checkbox inserts the Delayed statement.
  • INSERT IGNORE statements - Checking this tickbox inserts the Ignore statement.

• Function to use when dumping data - Using this dropdown, you may choose which function phpMyAdmin uses when dumping data.
• Syntax to use when inserting data - You can choose the different types of syntax which are used when inserting data. The bellow syntaxes may be chosen with a radio button:
  • include column names in every INSERT statement
  • insert multiple rows in every INSERT statement
  • both of the above
  • neither of the above
• Maximal length of created query - Using this textbox, you can define the length of the generated query.
• Dump binary columns in hexadecimal notation (for example, "abc" becomes 0x616263) - If you want to dump the binary columns in hexadecimal notation, please put a tick inside this checkbox.
• Dump TIMESTAMP columns in UTC (enables TIMESTAMP columns to be dumped and reloaded between servers in different time zones) - Checking this tickbox dumps the data in the UTC timezone.

After all of the settings have been adjusted, please press the “Go” button located at the bottom left side of the page.

This starts a download process and saves the exported file format on your local computer.

Exporting all databases

The process is absolutely analogical to the above with a few minor exceptions. When you are redirected to the phpMyAdmin page from cPanel, instead of selecting a database from the list on the left side of the page, simply point your attention to the tab bar on the current page. You can see the “Export” tab located near the “Status” tab. Please click on it.

When you are redirected to the “Export” page, and you select the “Custom” method, instead of seeing the “Tables” section, you can now see the “Databases” section, allowing you to choose which databases you want to export.

By default, all of them are selected, but you can click on a database to choose it for an individual export. You know that you have chosen it, as it is highlighted in blue. You may also use the “Select all” and “Unselect all” buttons in this section to select and respectively unselect all databases in the content box.

And there you have it! Both methods of exporting your database using the phpMyAdmin feature of the cPanel. We hope that after reading this tutorial you have the necessary knowledge to fully utilize this functionality. If you notice any technical issues during this process, please be sure to contact our Technical Team. They are 24/7 available via the ticketing system in your Client Area.