0 
0 This article shows you several practical examples on how to perform various backup operations of MySQL databases using mysqldump command and also we will see how to restore them with the help of mysql and mysqlimport command in Linux.
mysqldump is a command-line client program, it is used to dump local or remote MySQL database or collection of databases for backup into a single flat file.
How to Backup MySQL Database?
The command that does the heavy lifting is mysqldump. The command’s structure looks like this:
mysqldump [options] [database_name] [backup_name]
The process is quite simple. Let’s say you have a database named products, and you want to back it up to an external drive mounted at /mnt/backups/. The basic command to run that dump (aka backup) would be this:
sudo mysqldump -p products > /mnt/backups/products.sql
There are a couple of issues at hand. First and foremost, you must use the -p option; this instructs mysqldump that you will be entering the password associated with the MySQL user. If that fails (depending upon your setup), you may have to include the option to instruct mysqldump which user has said permission, like so (USER is the actual user that has permission to work with the databases, such as root):
mysqldump -u USER -p products > /mnt/backups/products.sql
Another issue might appear if the user doesn’t have write permission to that directory; if this is the case, you will receive a Permission denied error. If you don’t want to run the risk of opening the permissions on that backup directory, you can first issue the command sudo su and then run the mysqldump command. Remember: After completing the task, exit out of the root bash prompt with the exit command.
Backing up all your databases
If you want to back up all of your databases, you can accomplish this with the –all-databases option, like so:
mysqldump -u USER -p --all-databases > /mnt/backups/all_databases.sql
Compressing your backup
If your database is considerably larger and you plan on manually running backups on a daily basis, you might consider compressing the backups. This is another feature the developers of MySQL rolled in.
Let’s stick with our same example as above, only with compression. To accomplish this, we’ll use the -C option, like so (USER is the username that has permission to work with the databases):
mysqldump -u USER -p -C products > /mnt/backups/products.sql.tgz
The above command will dump a compressed version of your database backup in the defined file (in this case products.sql.tgz).
Keep it going
You’re ready to continue the work. In a future article, I’ll explain how to automate the process of backing up your databases, as well as how to restore a database from a backup. If you’d rather run your backups manually, you now have all you need to do so.
