How To Import and Export Databases in MySQL or MariaDB

For conducting MySQL exports or imports, commence by accessing your server, preferably utilizing sudo (root) privileges.

1. Export Process with MySQL

The optimal method for exporting a MySQL database to a text file involves using the tool called mysqldump.

When working with mysqldump, you’ll need to possess the login credentials of a suitable MySQL user endowed with the essential permissions to carry out the export of the specified database.

Armed with this information, input the mysqldump command while specifying the appropriate flags and choices:

$ mysqldump -u my_user -p target_database > output_file_location

The flags in utilization are:

• The -u flag designates the MySQL username to be utilized.
• The -p flag signifies that a password prompt will follow, corresponding to the aforementioned username.
• ‘target_database’ is, of course, the precise name of the database earmarked for export.
• The > symbol, a Unix instruction for STDOUT, permits Unix commands to channel the textual results of the issued command to an alternate destination. In this instance, the destination is a file path, indicated by ‘output_file_location’.

Note: It is generally prudent to input the fully qualified pathway and filename for ‘output_file_location’, ensuring that the resultant file materializes exactly where intended.

To illustrate, if we desire to export the ‘books’ database under the user ‘bookkeeper’ to the directory ‘~/backup/database’, the ensuing command might resemble:

$ mysqldump -u bookkeeper -p books > ~/backup/database/books.sql
Enter password:

Following password entry upon the prompt, the command crafts a backup file appended with the .sql extension (which is optional yet recommended) within the designated directory.

By default, mysqldump refrains from safeguarding commands that could alter the actual database’s existence. Instead, the default behavior centers around conserving solely the actual tables (alongside their corresponding data). This approach readies them for subsequent importation using the generated file. For exporting multiple databases for recreation later, consult the official documentation for insight on the –databases flag.

2. Importing into MySQL

Having gained proficiency in exporting MySQL database backups, we now delve into the reverse process of importing a backup into an existing database.

As one might anticipate, to complement the mysqldump command employed for exports, there exists a parallel command named mysqlimport tailored for imports.

In most instances, importing involves the straightforward task of providing akin options to the mysqlimport command. To import the afore-preserved ‘books.sql’ file, created earlier, we’d utilize analogous flags and comparable syntax:

$ mysqlimport -u bookkeeper -p production_books ~/backup/database/books.sql
Enter password:

Parallel to the previous scenario, the -u and -p flags mandate authentication, followed by the designated database’s name for import (in this instance, it’s a distinct production database). Lastly, specify the fully qualified path to the SQL dump file containing the import data, i.e., ‘~/backup/database/books.sql’. It’s noteworthy that mysqlimport dispenses with the < or > (STDIN/STDOUT) symbols used in conjunction with mysqldump.

Conclusively, you’ve acquired proficiency in both exporting/backing up existing databases and subsequently importing/restoring that data into the same or divergent databases.