How to Import and Export MySQL or MariaDB Databases

Introduction: Importing and exporting databases is a common task in software development. You can use data dumps to back up and restore your information. You can also use them to migrate data to a new server or development environment.

In this guide, you will work with database dumps in MySQL or MariaDB. Specifically, you will export a database and then import that database from the SQL dump file.

Prerequisites:

Before we begin, make sure you have the following:

• Basic knowledge of Linux and how to use the terminal.
• SSH access to your server (for Windows users, you can use PuTTY).

What You Should Know:

Before we get started, here are some key details you should know:

• Username: root (Your SSH user, this is the default user)
• IPv4 Address: 192.0.2.1 (Standard IP Address)

Step 1: Setting Up Your Server

1. Create or Choose Your Server:

   • Deploy a virtual private server (VPS) with your preferred cloud provider.
   • Choose the server's operating system (e.g., Ubuntu 20.04).

Exporting a MySQL or MariaDB Database:

2. Connect to your server using SSH. If you are a Windows user, you can use PuTTY for SSH access.

Exporting The MySQL or MariaDB Database:

4. Use the mysqldump command to export the database. Run the following command:

   mysqldump -u username -p database_name > database.sql
   • Replace username with the database username.
   • Replace database_name with the name of the database to export.
   • database.sql will be the file in the current directory that stores the output.

   Example: mysqldump -u root -p mydatabase > mydatabase.sql

5. The command will produce no visual output, but you can inspect the contents of database.sql to confirm that the database dump was successful. Run the following command to confirm:

   head -n 5 database.sql

   The top of the file should show a MySQL dump for your database.

Step 2: Importing a MySQL or MariaDB Database

To import an existing dump file into MySQL or MariaDB, you will need to have a database created.

1. After creating your new MySQL or MariaDB database, use the following command to import the dump file:

   mysql -u username -p new_database < database.sql
   • Replace username with the database username.
   • new_database is the name of the new database.
   • database.sql is the dump file to import.

2. If the command runs successfully, it won't produce any output. Errors, if any, will be displayed on the terminal.

3. To check if the import was successful, log into the MySQL shell or use PhpMyAdmin and inspect the data.

Conclusion:

Congratulations! You have successfully imported and exported a MySQL or MariaDB database. Thank you for following this tutorial.