• Home
  • Ubuntu 20.04
    • Upgrade Ubuntu
    • Install Java
    • Install Node.js
    • Install Docker
    • Install LAMP Stack
  • Tutorials
    • AWS
    • Shell Scripting
    • Docker
    • Git
    • MongoDB
  • Funny Tools
  • FeedBack
  • Submit Article
  • About Us
TecAdmin
Menu
  • Home
  • Ubuntu 20.04
    • Upgrade Ubuntu
    • Install Java
    • Install Node.js
    • Install Docker
    • Install LAMP Stack
  • Tutorials
    • AWS
    • Shell Scripting
    • Docker
    • Git
    • MongoDB
  • Funny Tools
  • FeedBack
  • Submit Article
  • About Us

How To Import and Export MySQL Database

Written by Rahul, Updated on February 5, 2021

MySQL is an relation database management system to storing data in table format. It is an opensource database server available to install on variety of operating systems

In case of mysql database migration, you can easily create a dump of database and restore it on target database server. MySQL server provides console utilities to export and import databases.

This tutorial help you to export MySQL database using system console. Also helped you to restore database from dump file.

Step 1 — Export MySQL Database

Use mysqldump command line utility to perform a database backp. Which makes the process more easier to transfer database to other system. In order to export database, you need database’s name and login credentials with at least read-only privileges to the databases.

Let’s export your database using mysqldump:

mysqldump -u root -p database_name > db_backup.sql 

Here:

  • root – is the username to login to database server
  • database_name – is the name of the database to export
  • db_backup.sql -is the text file name, that will stores the output

The above command will run silently with no output on screen. If any errors occur during the export process, mysqldump will print them to the screen.

Step 2 — Verify Backup File

Let’s, verify the database dump file created in above step. First make sure there is no error displayed on screen with mysqldump command.

Next, Run the following command:

head db_backup.sql 

This will show you the database details like below

-- MySQL dump 10.13  Distrib 8.0.23, for Linux (x86_64)
--
-- Host: localhost    Database: mysql
-- ------------------------------------------------------
-- Server version       8.0.23

/*!40101 SET @[email protected]@CHARACTER_SET_CLIENT */;
/*!40101 SET @[email protected]@CHARACTER_SET_RESULTS */;
/*!40101 SET @[email protected]@COLLATION_CONNECTION */;
/*!50503 SET NAMES utf8mb4 */;

Next, Run the following command to view the last line from the backup file.

tail db_backup.sql 
/*!40101 SET [email protected]_CHARACTER_SET_RESULTS */;
/*!40101 SET [email protected]_COLLATION_CONNECTION */;
/*!40111 SET [email protected]_SQL_NOTES */;

-- Dump completed on 2021-02-02 17:07:24

The last line must contain a message like “Dump completed” followed by the date time.

Step 3 — Import MySQL Database

Before importing the data from backup file, make sure to create database on database server.

You can use “mysqladmin” console command to create a new database. To create a new database, execute:

mysqladmin -u root -p create new_database_name 

Enter mysql user password to complete process. This will create a database in mysql server.

Next, you can import the dump file using “mysql” console command. The command will be like below:

mysql -u root -p new_database_name < db_backup.sql

Here:

  • root - is the username to access database server
  • database_name - is the name of the newly created database
  • db_backup.sql -is the dump file name taken from source database server.

On successfuly command execution, you will get the command prompt back without any message. In case of any error occurs with restore process, the error message will be printed on terminal screen.

Now, you can connect your database and access database tables from mysql shell.

Conclusion

In this tutorial, you have learned to create dump of mysql database. Then create a new database on destination and restore from backup file.

You can visit mysqldump official documentation page to read more about this command.

Share it!
Share on Facebook
Share on Twitter
Share on LinkedIn
Share on Reddit
Share on Tumblr
Share on Whatsapp
Rahul
Rahul
Connect on Facebook Connect on Twitter

I, Rahul Kumar am the founder and chief editor of TecAdmin.net. I am a Red Hat Certified Engineer (RHCE) and working as an IT professional since 2009..

Leave a Reply Cancel reply

Popular Posts

  • How To Install Python 3.9 on Debian 10
  • Download Ubuntu 20.04 LTS – DVD ISO Images
  • Linux Run Commands As Another User
  • How to Check PHP Version (Apache/Nginx/CLI)
  • How To Install and Configure GitLab on Ubuntu 20.04
  • How to Install PyCharm on Ubuntu 20.04
  • How to Check Ubuntu Version with Command or Script
  • How to Set all directories to 755 And all files to 644
© 2013-2021 Tecadmin.net. All Rights Reserved | Terms  | Privacy Policy