How to take dump or backup of all mysql databases in Linux ?

Today we will look how to take backup/dump of all mysql databases of our linux system from command line.

I. Taking Backup
To take backup, we will use mysqldump command and mention the sql file in which we want to backup.

[root@nglinux ]# mysqldump -u root -p --all-databases > ~/db_backup_all.sql
Enter password:
-- Warning: Skipping the data of table mysql.event. Specify the --events option explicitly.

[root@nglinux ]# ls -l ~/db_backup_all.sql
-rw-r--r--. 1 root root 3916858 Jun 15 21:23 /root/db_backup_all.sql

[root@nglinux ]# du -sh ~/db_backup_all.sql
3.8M /root/db_backup_all.sql
[root@nglinux ]#

II. Restoring the data back.
In order to restore we simply login and mention the sql file name to restore.

[root@nglinux ]# mysql -u root -p < ~/db_backup_all.sql
Enter password:
[root@nglinux ]#

Note: remember the user “root” and password is of mysql server user, not OS user.

Tags: backupcentosdatabaselinuxmysqlmysqldumpredhatrestorerestoredrhelsqlunix