How to Generate a Full MYSQL Backup using MySQLDump

On this post we will explore the best way to generate a full MYSQL backup of all your databases inside a Linux server. This is useful for most Linux distributions such as CentOS/RHEL, Ubuntu/Debian, and of course it’s compatible with cPanel servers.

Generating backups of all your applications is the best practice you can do to keep your data safe in case of hacking intrusion, or data loss because of hardware or software failure.

For MySQL, the best way to generate a complete backup of MySQL databases is using mysqldump tool. mysqldump allows you to dump a complete MySQL database as SQL file, which can be restored if needed.

Generate a Full MYSQL Backup of one single MySQL database

Let’s start dumping data in SQL Format with mysqldump for one single MySQL database. For this you just need to login as root on your server and run this command:

mysqldump --opt -Q -u user -p database > /home/database.sql

Make sure you replace ‘user’, ‘database’ and ‘/home/database.sql’ with the real names of your mysql user, database and directory path where you want to store the .sql file.

How can I Generate a Full MYSQL Backup of all my databases?

Let’s see how to export a single .sql file that will include all your databases.

On cPanel servers you can generate an all in one single file backup for all your databases using this command:

mysqldump --all-databases > /home/mysql-alldump.sql

For plain Linux servers this is the right command to use:

mysqldump --all-databases -p > /home/mysql-alldump.sql

Backup your MySQL databases separately, one per .sql file automatically

If you need to backup all your databases separately with one .sql file per database, you can use this command:

For this we will have to use a tiny little for loop script that will get the list of all MySQL databases on the system except for the information_schema database, that is useless for most people. At the end we will dump each database with mysqldump tool. Let’s begin.

Create your backup destination directory, for example:

mkdir /home/mysql-all
cd /home/mysql-all

For cPanel servers:

for db in $(mysql -B -s -u root -e 'show databases' | grep -v information_schema); do mysqldump -u root "$db"  > "/home/mysql-all/$db.sql"; done

For plain Linux servers:

Set your MySQL root password as you see below, replace “yourpassword” with your real MySQL root password.

MYSQL_PASS="yourpassword"

Then run this loop:

for db in $(mysql -B -s -u root --password=$MYSQL_PASS -e 'show databases' | grep -v information_schema); do mysqldump -u root --password=$MYSQL_PASS "$db"  > "/home/mysql-all/$db.sql"; done

This can take a while depending on how fast are your disks, and the size of each database.

How can I restore my .sql dump files?

Resting .sql dump files is pretty much easy, it is almost the same process as generating. Check out the next steps.

Restore a single database .sql file on cPanel

mysql database -u root < file.sql -f

Restore a single database .sql file on plain Linux Servers

mysql database -p -u root < file.sql

Restoring your all in one full backup of all your databases on cPanel

mysql -u root < mysql-alldump.sql -f

Restoring your all in one full backup of all your databases on plain Linux servers

mysql -u root -p < mysql-alldump.sql -f

 

Conclusion

Now you know how to generate a full MySQL backup using the amazing mysqldump tool. At this point you should be able to export and import an .sql file from your server without any issues. Just be aware that if you dump large databases and you also use slow SATA or IDE disks, this process can take a really long time.

Do you know other tips and tricks for creating and restoring MySQL database backups With mysqldump? Please share your knowledge with us.

 

About the Author: Esteban Borges

Experienced Sr. Linux SysAdmin and Web Technologist, passionate about building tools, automating processes, fixing server issues, troubleshooting, securing and optimizing high traffic websites.

Leave a Reply

Your email address will not be published.