In the Database view, click on the Export link. Since version 3.4.0, the default export panel appears as shown in the following screenshot:
By default, $cfg['Export']['method'] is set to'quick' and $cfg['Export']['format'] is set to'sql'. Usability tests show that the most common goal of exporting is to produce a complete backup in SQL format and save it on our workstation; this is accomplished by just clicking on Go.
Other values for $cfg['Export']['method'] are'custom', which would show the detailed export options, and 'custom-no-form' which would also show the detailed options but without the possibility of selecting a quick export—this being the behavior of versions prior to 3.4.0.
In custom mode, sub-panels are shown. The Table(s), Output, and Format sub-panels occupy the top part of the page. The Format-specific options sub-panel varies in order to show the options for the export format chosen. Following screenshot shows the SQL format panel:
This sub-panel contains a table selector, from which we choose the tables that we want. By default, all tables are selected and we can use the Select All / Unselect All links to change our choice.
The default behavior is to transmit the export file via HTTP (the Save output to a file radio button being selected). This triggers a Save dialog into the browser, which ultimately saves the file on our local machine. An alternative option would have been to select View output as text, which can be done as a testing procedure, provided that the exported data is of a reasonable size.
The name of the proposed file will obey the File name template field. In this template, we can use the special @SERVER@, @DATABASE@, and @TABLE@ placeholders. These placeholders will be replaced by the current server, database, or table name (for a single-table export). Note that there is one "at sign" character before and after the words. We can also use any special character from the PHP strftime function; this is useful for generating an export file based on the current date or hour. Finally, we can put any other string of characters (not part of the strftime special characters), which will be used literally. The file extension is generated according to the type of export. In this case, it will be .sql. Following are some examples for the template:
@DATABASE@would generatemarc_book.sql@DATABASE@-%Y%m%dwould givemarc_book-20110920.sql
The use this for future exports option, when activated, stores the entered template settings into cookies (for database, table, or server exports) and brings them back the next time we use the same kind of export.
The default templates are configurable, via the following parameters:
$cfg['Export']['file_template_table'] = '@TABLE@'; $cfg['Export']['file_template_database'] = '@DATABASE@'; $cfg['Export']['file_template_server'] = '@SERVER@';
The possible placeholders such as @DATABASE@ are the same as those that can be used for the window title and are described in Documentation.html, FAQ 6.27.
It is possible to choose the exact character set for our exported file. phpMyAdmin verifies that the conditions for recoding are met. For the actual recoding of data, the PHP component of the web server must support the iconv or the recode module. The $cfg['RecodingEngine'] parameter specifies the actual recoding engine—the choices being none, auto, iconv, and recode. If it is set to auto, phpMyAdmin will first try the iconv module and then the recode module. If set to none, the character set dialog is not shown.
If phpMyAdmin detects the use of the Japanese language, it checks whether PHP supports the mb_convert_encoding() multibyte string function. If it does, additional radio buttons are displayed on the export and import pages and on the query box, so that we can choose between the EUC-JP and SJIS Japanese encodings.
Here is an example taken from the Export page:
To save transmission time and get a smaller export file, phpMyAdmin can compress to ZIP, GZIP, or BZIP2 formats. These formats are offered only if the PHP server has been compiled with the --with-zlib (for ZIP and GZIP) or --with-bz2 (for BZ2) configuration option respectively. The following parameters control which compression choices are presented in the panel:
$cfg['ZipDump'] = TRUE; $cfg['GZipDump'] = TRUE; $cfg['BZipDump'] = TRUE;
A system administrator installing phpMyAdmin for a number of users could choose to set all these parameters to FALSE, so as to avoid the potential overhead incurred by a lot of users compressing their exports at the same time. This situation usually causes more overhead than if all the users were transmitting their uncompressed files at the same time.
In older phpMyAdmin versions, the compression file was built in the web server memory. Some problems caused by this were:
- File generation depended on the memory limits assigned to running PHP scripts.
- During the time the file was generated and compressed, no transmission occurred. Hence, users were inclined to think that the operation was not working and that something had crashed.
- Compression of large databases was impossible to achieve.
The $cfg['CompressOnFly'] parameter (set to TRUE by default) was added to generate (for GZIP AND BZIP2 formats) a compressed file containing more headers. Now, the transmission starts almost immediately. The file is sent in smaller chunks so that the whole process consumes much less memory. The downside of this is a slightly larger resulting file.
We shall now discuss the formats (and the options available once they have been chosen) that can be selected with the Format sub-panel.
Note
Even if we can export into many formats, only some of these formats can be imported back using phpMyAdmin.
The SQL format is useful, as it creates standard SQL commands that would work on any SQL server.
If the Display comments checkbox is selected, comments are included in the export file. The first part of the export comprises comments (starting with the -- characters) that detail the utility (and version) that created the file, the date, and other environment information. We then see the CREATE and INSERT queries for each table.
phpMyAdmin generates ANSI-compatible comments in the export file. These comments start with --. They help with importing the file back on other ANSI SQL-compatible systems.
SQL options are used to define exactly what information the export will contain. The following screenshot depicts the general SQL options:
- Additional custom header comment: We can add our own comments for this export (for example, Monthly backup), which will show in the export headers (after the PHP version number). If the comment has more than one line, we must use the special character
- Display foreign key relationships: In Chapter 10, we will see that it's possible to define relations even for tables under the
MyISAMstorage engine; this option would export these relationship's definition as comments. These cannot be directly imported, but nonetheless are valuable as human-readable table information. - Display MIME types: This adds information (in the form of SQL comments), to describe which MIME type has been associated to columns. Chapter 16 explains this further.
- Enclose export in a transaction: Starting with MySQL 4.0.11, we can use the
START TRANSACTIONstatement. This command, combined withSET AUTOCOMMIT=0at the beginning andCOMMITat the end, asks MySQL to execute the import (when we will re-import this file) in one transaction, ensuring that all the changes are done as a whole. - Disable foreign key checks: In the export file, we can add
DROP TABLEstatements. However, normally a table cannot be dropped if it is referenced in a foreign key constraint. This option overrides the verification by addingSET FOREIGN_KEY_CHECKS=0to the export file. This override only lasts for the duration of the import. - Database system or older MySQL server to maximize output compatibility with: This lets us choose the flavor of SQL that we export. We must know about the system onto which we intend to import this file. Among the choices are MySQL 3.23, MySQL 4.0, ORACLE, and ANSI.
We may want to export the structure, the data, or both; this is performed with the Dump table option. Selecting Structure generates the section with CREATE queries, and selecting Data produces INSERT queries.
If we select Structure, the Object creation options sub-panel appears, as depicted in the following screenshot:
- Add DROP TABLE / VIEW / PROCEDURE / FUNCTION / EVENT: Adds a
DROP ... IF EXISTSstatement before eachCREATEstatement, for example,DROP TABLE IF EXISTS `author`;. This way, we can ensure that the export file is executed on a database in which the same element already exists, updating its structure but destroying the previous element's contents. - Add CREATE PROCEDURE / FUNCTION / EVENT: This includes all procedures, functions, and event definitions found in this database, in the export.
- CREATE TABLE OPTIONS / IF NOT EXISTS: Adds the
IF NOT EXISTSmodifier toCREATE TABLEstatements, avoiding an error during import if the table already exists. - CREATE TABLE OPTIONS / AUTO_INCREMENT: Puts auto-increment information from the tables into the export, ensuring that the inserted rows in the tables will receive the next exact auto-increment ID value.
- Enclose table and field names with backquotes: In the MySQL world, backquotes are the normal way of protecting table and column names that may contain special characters. In most cases, it is useful to have them. However, backquotes are not recommended if the target server (where the export file will be imported) is running a SQL engine that does not support backquotes.
The following screenshot displays options relevant to a Data export:
The options available in the Data section are:
- INSERT DELAYED statements: Adds the
DELAYEDmodifier toINSERTstatements. This accelerates theINSERToperation as it is queued to the server, which will execute it when the table is not in use. This is a MySQL non-standard extension, available only forMyISAM, MEMORY, andARCHIVEtables. - INSERT IGNORE statements: Normally, at import time, we cannot insert duplicate values for unique keys, as this would abort the insert operation. This option adds the
IGNOREmodifier toINSERTandUPDATEstatements, thus skipping the rows that generate duplicate key errors. - Function to use when dumping data: The choices are INSERT, UPDATE, and REPLACE. The most well-known of these types is the default INSERT—using
INSERTstatements to import back our data. At import time, however, we could be in a situation where a table already exists and contains valuable data, and we just want to update the columns that are in the current table we are exporting. UPDATE generates statements, such as the following line of code, updating a row when the same primary or unique key is found:UPDATE `author` SET `id` = 1, `name` = 'John Smith', `phone` = '111-1111' WHERE `id` = '1';
The third possibility, REPLACE, produces statements such as
REPLACE INTO `author` VALUES (1, 'John Smith', '111-1111'),These act similar to an INSERT statement for new rows and update existing rows, based on primary or unique keys. - Syntax to use when inserting data: There are several choices here. By including column names in every statement, the resulting file is bigger, but will prove more portable on various SQL systems with the added benefit of being better documented. Inserting multiple rows with a statement is faster than using multiple
INSERTstatements, but is less convenient as it makes reading the resultant file harder. It also produces a smaller file, but each line of this file is not executable in itself as each line does not have anINSERTstatement. If you cannot import the complete file in one operation, you cannot split the file with a text editor and import it chunk by chunk. - Maximal length of created query: The single
INSERTstatement generated for Extended inserts might become too big and could cause problems. Hence, we set a limit to the number of characters for the length of this statement. - Dump binary columns in hexadecimal notation: This option makes phpMyAdmin encode the contents of
BLOBcolumns in0xformat. Such a format is useful as, depending on the software that will be used to manipulate the export file (for example a text editor or mail program), handling a file containing 8-bit data can be problematic. However, using this option will produce an export ofBLOBcolumn type that is twice the size. - Dump TIMESTAMP columns in UTC: This is useful if the export file is to be imported back on a server located in a different time zone.
This format is understood by a lot of programs, and you may find it useful for exchanging data. Note that it is a data-only format—no SQL structure here.
- Columns separated with: We put a comma here, which means that a comma will be placed after each column. The default value comes from
$cfg['Export']['csv_separator']. - Columns enclosed with: We place an enclosing character here (double quotes) to ensure that a column containing the terminating character (comma) is not taken for two columns. The default value comes from
$cfg['Export']['csv_enclosed']. - Columns escaped with: If the export generator finds the Columns enclosed with character inside a column, this character will be placed before it in order to protect it. For example,
"John "The Great"Smith". The default value comes from$cfg['Export']['csv_escaped']. - Lines terminated with: This decides the character that ends each line. We should use a proper line delimiter here depending on the operating system on which we will manipulate the resulting export file. The default value of this option comes from the
$cfg['Export']['csv_terminated']parameter, which contains'AUTO'by default. The'AUTO'value produces a value of - Replace NULL with: This determines which string occupies the place in the export file of any
NULLvalue found in a column. - Remove carriage return/line feed characters within columns: As a column can contain carriage return or line feed characters, this determines if such characters should be removed from the exported data.
- Put column names in the first row: This gets some information about the meaning of each column. Some programs will use this information to name the column. For the exercise, we select this option.
Finally, we select the author table.
Clicking on Go produces a file containing the following lines:
"id","name","phone" "1","John Smith","+01 445 789-1234" "2","Maria Sunshine","+01 455 444-5683"
This export mode produces a CSV file specially formatted for Microsoft Excel (using semicolons instead of commas). We can select the exact Microsoft Excel edition as shown in the following screenshot:
It's possible to create a PDF report of a table by exporting in PDF. This feature always produces a file. Since phpMyAdmin 3.4.7, we can also export a complete database or multiple tables in one sweep. We can add a title for this report, and it also gets automatically paginated. Non-textual (BLOB) data as in the book table is discarded from this export format.
Here, we test it on the author table, asking to use "The authors" as a title. PDF is interesting because of its inherent vectorial nature—the results can be zoomed. Let us have a look at the generated report, as seen from the Adobe Reader:
This export format directly produces a .doc file suitable for all software that understands the Word 2000 format. We find options similar to those in the Microsoft Excel export, and a few more. We can independently export the table's Structure and Data.
Note that, for this format and the Excel format, we can choose many tables for one export. However, unpleasant results happen if one of these tables has non-textual data. Here are the results for the author table:
LaTeX is a typesetting language. phpMyAdmin can generate a .tex file that represents the table's structure and/or data in a sideways tabular format.
Note
Note that this file is not directly viewable, and must be processed further or converted for the intended final media.
|
Option |
Description |
|---|---|
|
Include table caption |
Displays captions in the tabular output |
|
Structure and Data |
The familiar choice to request structure, data, or both |
|
Table caption |
The caption to go on the first page |
|
Table caption (continued) |
The caption to go on, page after page |
|
Display foreign key relationships, comments, MIME types |
Other structure information we want as output. These choices are available if the phpMyAdmin configuration storage is in place |
This format is very popular these days for data exchange. We can choose which data definition elements (such as functions, procedures, tables, triggers, or views) we want exported. What follows is the output for the author table.
<?xml version="1.0" encoding="utf-8"?> <!-- - phpMyAdmin XML Dump - version 3.4.5 - http://www.phpmyadmin.net - - Host: localhost - Generation Time: Sep 16, 2011 at 03:18 PM - Server version: 5.5.13 - PHP Version: 5.3.8 --> <pma_xml_export version="1.0" xmlns:pma="http://www.phpmyadmin.net/some_doc_url/"> <!-- - Structure schemas --> <pma:structure_schemas> <pma:database name="marc_book" collation="latin1_swedish_ci" charset="latin1"> <pma:table name="author"> CREATE TABLE `author` ( `id` int(11) NOT NULL, `name` varchar(30) NOT NULL, `phone` varchar(30) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; </pma:table> </pma:database> </pma:structure_schemas> <!-- - Database: 'marc_book' --> <database name="marc_book"> <!-- Table author --> <table name="author"> <column name="id">1</column> <column name="name">John Smith</column> <column name="phone">+01 445 789-1234</column> </table> <table name="author"> <column name="id">2</column> <column name="name">Maria Sunshine</column> <column name="phone">333-3333</column> </table> </database> </pma_xml_export>
This spreadsheet format is a subset of the open document (http://en.wikipedia.org/wiki/OpenDocument), which was made popular with the OpenOffice.org office suite. We need to choose only one table to be exported in order to have a coherent spreadsheet. The following screenshot shows our author table, exported into a file named author.ods, and subsequently looked at from OpenOffice:
This is another subset of the open document standard, this time oriented towards text processing. Our author table is now exported and viewed from OpenOffice.
YAML stands for YAML Ain't Markup Language. YAML is a human-readable data serialization format; its official site is http://www.yaml.org. This format has no option that we can choose from within phpMyAdmin. Here is the YAML export for the author table:
1: id: 1 name: John Smith phone: +01 445-789-1234 2: id: 2 name: Maria Sunshine phone: 333-3333
This choice might some day support many formats related to code development. Currently, it can export in NHibernate Object-relation mapping (ORM) format. For more details, please refer to http://en.wikipedia.org/wiki/Nhibernate.
Texy! is a formatting tool (http://texy.info/en/) with its own simplified syntax. The following block of code is an example of export in this format:
===Database marc_book == Table structure for table author |------ |Field|Type|Null|Default |------ |//**id**//|int(11)|Yes|NULL |name|varchar(30)|Yes|NULL |phone|varchar(30)|Yes|NULL == Dumping data for table author |1|John Smith|+01 445 789-1234 |2|Maria Sunshine|333-3333
In PHP, associative arrays can hold text data; therefore, a PHP-array export format is available. The following is a PHP array export of the author table:
<?php
// marc_book.author
$author = array(
array('id'=>1,'name'=>'John Smith','phone'=>'+1 445 789-1234'),
array('id'=>2,'name'=>'Maria Sunshine','phone'=>'333-3333')
);
MediaWiki (http://www.mediawiki.org/wiki/MediaWiki) is a popular wiki package, which supports the ubiquitous Wikipedia. This wiki software implements a formatting language in which it's possible to describe data in tabular format. Choosing this export format in phpMyAdmin produces a file which can be pasted on a wiki page we are editing.
The JavaScript Object Notation (http://json.org) is a data-interchange format popular in the web world. Exporting the author table in this format is shown in the following block of code:
/**
Export to JSON plugin for PHPMyAdmin
@version 0.1
*/
/* Database 'marc_book' */
/* marc_book.author */
[{"id": 1,"name": "John Smith","phone": "+01 445 789-1234"}, {"id": 2,"name": "Maria Sunshine","phone": "333-3333"}]