Managing MySQL database backup

How to create/restore MySQL databases’ dump/backup of all the databases?

Yes!! this is an important section in  MySQL administration, and every Linux Admin should know about it. Because, creating backups are such an important task for a SysAdmin. In MySQL, the DB backup creation and its restoration is not a big task. This can be simply done via command line interface. If you don’t have much experience in Linux command line, check your control panel; there must be an option to create and restore MySQL database.

Here, CryBit is listing some common command line usages of MySQL to create a backup and restore it. The most important thing is the file extension of backup file. The backup file will be end with an extension “.sql”, example, “backup.sql”.

The command “mysqldump” is for creating the backup of the databases. Please see the examples:

Create database backup.

If you need to backup only one database, then

mysqldump database_name > database_name.sql

If you need to backup more than one database, then

mysqldump --databases database_one database_two > two_databases.sql

If you need to backup all the databases, then

mysqldump --all-databases > all_databases.sql

Restoring the MySQL backup.

If you need to restore a single backup, then

mysql database_name < database_name.sql

If you need to restore a single database from the backup of all the databases, then

mysql --one-database database_name < all_databases.sql

To backup all the databases in the server as separate individual sql files

for db in `echo 'show databases;' |mysql |grep -Ev "Database|information_schema|performance_schema"`; do mysqldump $db | gzip > /backup/$db.sql.gz ;done

This will take the backup of all the databases in the server and then it will zip the sql backup files and will save the backup to the location /backup. Now the backup of all the databases can be found in the /backup location in the format dbname.sql.gz

That’s it!

Related links

PHP script to check the MySQL database connection
Know the MySQL uptime from commandline
How to get the MySQL root password details – cPanel
Create a New Database-user and Grand Permissions

Arunlal Ashok

Sr. Linux Server Administrator. I'm managing Linux servers since 2012. I started this blog to share and discuss my ideas. Check My Profile in uPwork (oDesk) and create a job, if you need any administration help. Thanks!!

You may also like...

2 Responses

  1. Sree Parvathy says:

    The script for taking backup of all databases in a separate backup file does not seem to be working.

    Please cross-check it.

Leave a Reply

Your email address will not be published. Required fields are marked *