How to Import a MySQL File

Introduction

Importing a MySQL file is a common task of any database or system administrator in the software development environment. You can use the mysqldump command-line utility to backup and restore your databases. You can use the MySQL backup file to migrate the database to a new server. You can also use this backup file to restore the existing database in the event of database crashes.

In this post, we will show you how to export the MySQL database and import that database from the dump file.

Export a MySQL Database

The mysqldump is a simple and powerful command-line utility that allows you to export MySQL databases to a SQL file. You will need a database name and credential to perform the database export.

The basic syntax to export the MySQL database to a SQL file is shown below:

mysqldump -u username -p databasename > dump.sql

Where:

• username is used to log in to the MySQL shell.

• databasename is the name of the database that you want to export.

• dump.sql is the name of the dump file.

For example, to export the MySQL database named testdb into a SQL file named backup_mysql.sql, run the following command:

mysqldump -u username -p testdb > backup_mysql.sql

After the successful backup, you can display the content of the backup_mysql.sql file using the following command.

cat backup_mysql.sql

You will get the following output:

-- MySQL dump 10.13 Distrib 5.7.37, for Linux (x86_64)
--
-- Host: localhost Database: testdb
-- ------------------------------------------------------
-- Server version 5.7.37-0ubuntu0.18.04.1
 
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
 
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
 
-- Dump completed on 2022-02-06 12:02:39

Import a MySQL Database

At this point, you have an SQL dump file that you can use to migrate the database to a new server. You can use the MySQL command to import the database from the dump file.

The basic syntax to import a MySQL database is shown below:

mysql -u username -p newdatabase < dump.sql

Where:

• username is used to log in to the MySQL shell.

• newdatabasename is the name of the new database on which you insert the data from the dump file.

• dump.sql is the MySQL dump file to be imported.

For example, to import the MySQL database from the backup_mysql.sql dump file to a new database named newdb, run the following command:

mysql -u username -p newdb < backup_mysql.sql

If the command runs successfully, it won’t produce any output.

Automate Your MySQL Backups with SnapShooter

SnapShooter is a leading backup solution provider that makes it easy to schedule frequent backups for your servers, websites, and databases. SnapShooter creates backups and archives from your server and stores them in the cloud storage provider of your choice. It supports many cloud providers including, Amazon AWS, Google Cloud, Vultr, DropBox, Hetzner, UpCloud, and more.

Conclusion

In this post, we explained how to export the MySQL database to an SQL file using the mysqldump utility. We also explained how to import a MySQL database to a new database using an SQL dump file. I hope this will help you to restore the database in the event of a database crash. For information, read the mysqldump official documentation page.