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.