How to backup and restore MySQL databases in Linux?

 

I use Linux so I will show you two ways in which you can take backup in Linux. 

1) Using MySQL workbench

2) Using the command line or terminal

First I will show the easy one which is from the terminal.... Just Kidding. Will show the workbench one first.

Using MySQL Workbench

Step 1)

 Install MySQL workbench if you don't have already. How to install that? Don't even know that? Then you should switch line. You don't have any future in this line. 

He He. Kidding again. I will show you how to install that from the command line. Don't tell me you don't like command line. It is the best. Try it with me. 

First open your terminal then write 

sudo apt-get install mysql-workbench-community

This will ask you for your pc user pass and install all the necessary packages.

I have already installed. So it is showing me that message.

Step 2)

Now open MySql workbench. It should look almost like this except test1 connection.

Click on Local instance 3306. It should ask for a password. Then give the password that you set up when you installed MySQL. 

If you don't know the password then you should really choose another career as your profession. Well you know I am kidding. You can reset the password using terminal easily. Just google it. 

Step 3)

After you enter the password it should take you to this page

It should show all the databases you have created. But it calls them schema. So if it doesn't show then just click on Schemas beside Administration on the upper left corner.

Step 3)

Then from the menu click on the server or from the Administration tab click on Data Export for taking Backup.

Step 4)

It will take you to the page below:

Step 5)

Choose the schema or database you want to export and where to export and the rest of the options are self-explanatory. I don't think I have to make you understand.

Step 6)

After Backing up let's say you want to restore this into another database. Then create a database. To create a database click on the icon in the top left corner below the View tab a little right( if you hover over them you will see it will show create a schema. ). Or you can just log in to MySQL from the terminal and write the command create database [database name];

Step 7)

Click on Administrator and then Data Import/Restore. Then choose whichever backup and in whichever database you want to restore. Choose Import from  Self-Contained file.

Using the command line or terminal

Backup:

To backup using terminal write the command below:

mysqldump -u root -p --single-transaction --flush-logs --source-data=2 --all-databases > [backup_name].sql

This command will backup all the databases in your Server.

 But if you want to take backup of only one database then you need to write the command below:

mysqldump -u [username] -p[password] [database_name] > [dump_file.sql]

The parameters of the said command as follows:

[username] - A valid MySQL username.

[password] - A valid MySQL password for the user.

[database_name] - A valid Database name you want to take backup.

[dump_file.sql] - The name of backup dump file you want to generate.

Restore:

To restore using terminal write the command below:

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

Told you!!! The command line was easier.