Backups are an essential part of any online system, if you don’t have any backups then you will end losing all your information, which will make you lose time, money and even customers.
Today I will show you how to backup your databases using MySQLDump utility so you can backup and restore your databases one by one, or all at once using the Linux command line. MySQLDump allows you to generate MySQL backups of all your databases in simple text files for restore process, or to read the content and extract the information you need also. After you are done with this tutorial, you will know how to Backup a MySQL database and be able to integrate the commands into your own shell scripts. Let’s begin.
Shell access to your VPS/Dedicated server: even you can run mysqldump from normal shells, this tutorial is based on root access, but you can simply replace the root user with the normal MySQL that has access to your database. If you don’t have shell access on your shared hosting account, you can get your own VPS from this two great companies:
Note: if you are running cPanel, you don’t need to specify the ‘-u root -p’ options.
Generate a MySQL Backup using MySQLDump
Backup a Single MySQL database
Replace “dbname” with your real database name.
mysqldump dbname -u root -p > /backup/database.sql
Backup Multiple MySQL databases
Replace “db1 db2 db3″ with the real database names you wish to backup
mysqldump --databases db1 db2 db3 -u root -p> /backup/multiple_databases.sql
Full MySQL backup of all your databases
As simple as that, the ‘–all-databases’ option will backup all your databases, including the ‘mysql’ database used by the MySQL server, this way you will preserve all privileges, users, passwords in case you are migrating the MySQL server from one host to another.
mysqldump –all-databases -u root -p > /backup/all-databases.sql
Restoring a MySQL Dump
MySQLDump backups files in plain text, the content of this backup files are simple SQL queries/commands, so when you need to restore the backups, the only thing you need to do is tell MySQL server that you need to run the commands inside the file. Following the previous examples, this is done in this way:
Restoring a Single MySQL database
Here you have to replcae “dbname” and use your real database name.
mysql dbname -u root -p < /backup/database.sql
Restoring all databases from a full MySQL dump is almost the same.
Because you are dumping back all your databases into the core of your MySQL server, you don’t need to specify any databases at all. Finally, after you ran the command you see below restart MySQL server so it can apply the new privileges from your .sql file.
mysql -u root -p < /backup/all-databases.sql /etc/init.d/mysqld restart
Important: if your database is too big (a few GBs) you may experience some high load average or get queued connections for your http server, this happens because when you generate a dump, MySQL locks all tables, and on small website this is almost unnoticeable, but on big websites it can take you down. For such cases you can try to setup a MySQL Slave and run the MySQL dumps there, that way you won’t affect your Master server.