While mysql stores files in /var/lib/mysql, backing up those files, especially when the database is still running, does not help. This is because when a database is active, portions of the database state is stored in RAM. A file-based approach to back up and restore a database generally will mean a corrupted restored image.
A utility program called mysqldump can be used to dump the entire content of a database in SQL statements. Such a “dump” can, then, be used to restore the content of the database.
mysqldump accepts many options. An important one is --all-databases. This options specifies that mysqldump saves all databases in an MySQL installation. Another useful one is --add-drop-database. This option is useful if the backup of a database will be used to start a new database server from scratch.
In order for mysqldump to access all databases, it needs the password of the root user. There are two ways to do this. The password can be a part of the command, or it can be supplied by a file. Because “ps -Af” can display all parameters of a command, it is best not to include the password in the command.
Instead, create a file “~/my.secret” and put the password in it. Then, protect the file using “chmod 600 ~/my.secret”. This makes sure that no one else has access to it. Then, use the back tick symbol (‘) in the command as follows:
This way, the password is passed along without any operator interaction, and yet it remains invisible to “ps -Af”.
Note that the previous command dumps everything to the terminal. To make this more useful, you should redirect the output to a file, or pipe the output through a compressing program to reduce the amount of disk space needed.
To store the dump file in plain text:
To pipe the dump file to compress it:
You can also consider using GPG to encrypt the file if the database may store sensitive data.
To restore a database dump from a plain text file:
To restore a database dump from a compressed file: