One of the most important tasks you can perform with your data is backing it up. Disaster can strike at any time without any notice, and if you haven’t backed up your data, you will lose everything.
Disaster could be as simple as mistakenly deleting a row or corruption of data, or it could be the loss of your backup server altogether.
MySQL provides a tool to allow you to quickly, easily, and safely backup your databases: mysqldump.
Backup a Single Database
Sometimes you only need to backup a single database. Mysqldump allows us to specifically target one database and dump its contents into a file.
myswldump -u <user> -p <password> <database> > outputfile
For example, if you are backing up a database called ‘wordpress’ with a user named ‘backup_user’, you would execute the following command.
mysqldump -u backup_user -p wordpress > wordpress.sql
You will notice that a password wasn’t specified with the -p flag. When this flag isn’t assigned a value it will prompt for a user to enter the password using a secure method — masking the characters.
Backing up Selected Databases
You may have multiple databases that need to be backed up. Rather running mysqldump several times, you can run it once by listing the databases you want it to backup.
mysqldump -u <user> -p <password> <database> --databases database1,database2,database3 > my_databases.sql
Backing All Databases
And finally, sometimes you want to capture everything. When you have a large collection of databases that need to be protected, this is the simplest method of ensuring they are backed up.
mysqldump -u <user> -p <password> <database> -A > mysql_dump.sql
Backup Users
It is important to ensure your backup users only have the permissions required to do their job and nothing more. Segregation of duties is an important policy to ensure your accounts are only allowed to do whatever is actually required of them.
When automating backups, for example, you should ensure the account being used has the least amount of permissions to run. Creating a read-only backup user provides a means of being able to backup your data without worrying about the account being abused.
