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