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
-
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:
-
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:
-
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
- Replace
-
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.
-
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.
- Replace
-
If the command runs successfully, it won't produce any output. Errors, if any, will be displayed on the terminal.
-
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.