How to import and export MySQL databases?

Modern websites generate their content dynamically - this means all important information is stored in a database. Some formats, like SQLite for example, store their records in a simple file, but most of them store them in memory only, such as MySQL and MariaDB, to ensure high performance. It is highly advisable to regularly back up these databases to be able to restore all data up to a specific point in time in case of unexpected difficulties, such as a server failure due to hardware malfunction. There are various methods and procedures available for creating a backup for importing.

How does backing up a MySQL or MariaDB database work?

A MySQL database consists of a series of records that can be created, deleted, manipulated, and linked together. A server application manages these in a defined and protected area in memory, so that the current information is not stored as a single file during operation. Access is done through the SQL database language that originated in the 1970s, allowing querying of individual entries or complete records.

Although MySQL and MariaDB also store their records on drives to be able to read them back in after a restart, they do so in a binary encoded format that is not suitable for exporting or importing. To create a backup of a database or an entire server, the contents need to be collectively queried and converted into a readable format. Subsequently, a utility program writes them to a file where they are then available in a universally usable plain text. Such a file is not tied to a specific operating system, software version, or even a format like MySQL, PostgreSQL, Oracle Database, or Microsoft SQL Server - with certain limitations. With an IDE PHP editor, you can usually create and import a local database as well.

What methods are available for exporting a database?

The regular exporting of a database is, with few exceptions, an even more important step than backing up the website - while a website based on a Content Management System (CMS) can often be reconstructed, the stored information is unique and cannot be restored, only imported from a backup. There are various ways to export:

• Direct exporting via commands on the command line
• Using web interfaces like PhpMyAdmin or Adminer
• Server administration interfaces like Plesk, DirectAdmin, or WebMin
• With some providers through the customer panel's user interface
• Scripts in a programming language like Bash or PHP

However, a closer look reveals that appearances can be deceiving from a technical perspective: In fact, many of the methods mentioned above operate through the command line or simply provide a mask or a graphical interface for manually entering commands. Ultimately, exporting always occurs directly through the database server or, in many cases, for performance reasons, through associated command line tools like mysqldump. This is as straightforward as it is logical because programming languages like PHP do not have direct access to databases but communicate exclusively with the responsible server.

How to export databases via the command line?

The command line, in both Linux and Windows, refers to a shell or terminal: it is a single-line text input through which commands can be written and executed, allowing for server administration. Virtual or dedicated servers on Linux generally provide an SSH server that waits for a client connection. In web hosting, however, it depends on the provider whether they allow their customers to use SSH. The ssh command in Linux or open-source applications like PuTTY in Microsoft Windows can be used as a client. After establishing a connection, a single command is all that is needed to export a database:

mysqldump -u User -p Password Database > Filename.sql

In this case, it is important to use the user and password of the database server, not the regular login. The -databases option allows for exporting multiple databases simultaneously:

mysqldump -u User -p Password -databases Database1 Database2 > Filename.sql

If you are using your own Virtual Private Server or Dedicated Server, you can also export all databases at once:

mysqldump -u root -p password - all-databases > filename.sql

For importing, you just need to reverse the comparison sign that controls the output. In the first case, it directs it into the file filename.sql, points it to the command, reads it from the file, and passes the data to mysqldump, making the import command look almost identical:

mysqldump -u user -p password database < filename.sql

In practice, exporting or importing looks like this, for example:

mysqldump -u wp_domain -p 123456789 wp_data > filename.sql

How to export databases using phpMyAdmin and Adminer?

Both phpMyAdmin and Adminer offer an option to export and import databases directly from the web interface. In PhpMyAdmin, after logging in, you can find this option directly next to the user accounts on the right and settings on the left. The program allows exporting and importing in various formats, including SQL syntax, LaTeX, CSV, PDF, PHP Array, or Microsoft Word 2000. For most users, selecting the Quick settings with minimal options should be sufficient. In this case, the selection is limited to the database format - SQL should be chosen, enabling later import into any server with SQL support. The advanced options are largely uninteresting for ordinary users, with the only exception being the Output section. Here, for example, you can change the character encoding from the default UTF-8 or automatically compress the file with ZIP or GZIP to significantly reduce the size of extensive databases.

Like phpMyAdmin, Adminer also provides options for importing and exporting directly on its homepage after logging in - these can be found in the standard interface on the left side directly under the title Adminer and a collapsible field for selecting the database. When choosing the options, the tool is slightly more limited than phpMyAdmin but offers all relevant possibilities. The main differences are that Adminer supports only three different formats - SQL, CSV, and TSV, and offers GZIP as the only compression option.

Both programs also allow you to import databases just as easily. It is important to note here that the file size may be limited by PHP. There are various ways to bypass this limit - whether by globally increasing the PHP upload limit, for example in a .htaccess file, or by uploading the file to the server via FTP.

Photo: Gerd Altmann on Pixabay