How to take mysql backups

Why should I backup my mysql database?
It is a good practice to backup your database, periodically. In case of a corruption or a compromise, backup restore can be a ‘life saver’.

How can you create MySQL database backup?
You can create a backup of your database using the mysqldump utility to quickly backup the mysql database to flat files. You may follow these commands to create a backup:

# mysqldump -u[Username] -p[password] [databasename] > [backupfile.sql]

This will backup the database as a flat file called backupfile.sql.
Let us take an example to backup the database called football for account, root, into the file, football.sql.

1) If you want to take the full backup of all tables including the data:

# mysqldump -u root -ppassword  football > football.sql

Where ‘password’ is the password for the user root, which has full permissions over the database called football.

2) If you want to take the backup of structures, only:

# mysqldump -u root  -ppassword --no-data football > football.sql

3)If you want to backup the data only:

# mysqldump -u root -ppassword --no-create-info football > football.sql

  Restoring mysql database

If you want to restore the football database from the backup, you may use the following command:

# mysql -u root -ppassword football < football.sql

You are done.