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 SysAdmins 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

Simple script [for loop] to create backup / dump of MySQL databases.

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

This is a simple bash for loop to create DB backups of all MySQL databases. You can add this as a cron, if you want to configure daily or hourly backup.

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