Backing Up and Restoring MySQL Databases on Ubuntu

mysqldump is a simple and straightforward method to backup MySQL databases by creating a .sql file of the database which can be restored to any empty MySQL database.

Login to the Mysql database:

Login to the MySQL database by using the following command,

$ mysql -u root -p
Enter Password:

and enter your database password then log into your database.

Check existing databases and tables:

To check existing databases and a list of tables related to a particular database by using the following commands,

$ show databases;

$ use database_name;

$ show tables;

here dsatabase_name is the name of the database present in your MySQL database.

For example database name is project1.

$ show databases;

$ use project1;

$ show tables;

Back up the database:

Using the following command, make a backup of the database.

$ mkdir backupfolder

$ cd backupfolder

$ mysqldump -u [username] –p [database_name] > [dump_file.sql]
Enter Password:

Then enter your database password.

here,

[username] - root MySQL username.

[database_name] - The name of the database you want to back up.

[dump_file.sql] - The name of the backup dump file.

For example,

$ mkdir databasebackup

$ cd databasebackup

$ mysqldump -u root –p project1 > backup_project1.sql
Enter Password:

After generating the above commands the .sql file is stored in the backup folder.

Restore the database backup:

Restore the backup to a local database server the Mysql command allows you to immediately restore the contents of a.sql file backup to a database. Using the following command, restore a backup of the database.

$ mysql -u [username] –p [database_name] < [dump_file.sql]

Then enter your database password.

[username] - root MySQL username.

[database_name] - The name of the database you want to restore.

[dump_file.sql] - The name of the backup dump file.

For example existing databases,

$ mysql -u root –p project1 < backup_project1.sql
Enter Password:

For example new database,

$ mysql -u root -p
Enter passowrd:

mysql>create database project2;
mysql>use project2;
mysql>exit;

$ mysql -u root –p project2 < backup_project1.sql
Enter Password:

After generating the above command restore the database by using the .sql file from the backup folder.

Conclusion:

This blog explains how to backup and restore the MySQL database on ubuntu Linux.