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

Post navigation

Arunlal A

Senior System Developer at Zeta. Linux lover. Traveller. Let's connect! Whether you're a seasoned DevOps pro or just starting your journey, I'm always eager to engage with like-minded individuals. Follow my blog for regular updates, connect on social media, and let's embark on this DevOps adventure together! Happy coding and deploying!

2 thoughts on “Managing MySQL database backup

  1. 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 *