MySQL Database Backup and Restore from Terminal

Tutorial Features:

• Create MySQL Database backup
• Create new database from terminal
• Restore Database from database dump file.

If you are working on a project where your using MySQL as your database you might need to take database backup from local server and post on it a product server or it vice versa.

In this tutorial I am going to give you simple steps to take MySQL Database backup from terminal/command line and also steps to restore a database from .sql file.

I am guessing you’re using Linux probably ubuntu operating system, it can be on server or local development environment.

You need have following credentials:

• MySQL User name ex. root
• MySQL User password

We will use mysqldump here for creating database backup file, mysqldump is used to dumping structure and contents of MySQL databases and tables.

Take MySQL Database Backup:

Open your terminal and fire following command to execute the first step of creating mysql database backup, make sure to change database_name as per your database name and filename.sql will be your choice.

$ mysqldump -u root -p database_name > filename.sql

It will ask you to enter password for the mysql user, enter the password and hit enter.

Enter password:

Restoring Database Backup:

As you see in the above steps we have used mysqldump to generate dump file with .sql extortion, this file is actually simple consist of SQL queries so that we can re-run them to get the backup restored.

Our first step to restore database is we will have to create empty database or we can say new database, you can do that by login into mysql from command line as well:

Create New Database:

Login to mysql:

$ mysql -u root -p

Enter password:

Type mysql user password again and hit enter, after successfully login it should show something like following output:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 5.7.19-0ubuntu0.16.04.1 (Ubuntu)

Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

Now use Following command to create a database:

$ CREATE DATABASE database_name;

Query OK, 1 row affected (0.00 sec)

Now we are good to go, type exit on terminal to logout from mysql.

Restore Database:

We have our backup file and a database to restore, let’s use following command to execute restore action:

$ mysql -u root -p newly_created_database_name < filename.sql

Enter password:

Type the password and hit enter.

Your done, you can check mysql database and your newly created database has been restored from the dump file.