MySQLdump is a tool allowing to operate backups of your datables, we are going to combine it with cron before automating your backups.

Prerequisite

Before preparing our safeguards, you must know the login (ex: root) and the password to access MySQL.
If you forget the password, you can reinitialize it with the following command :

We are also going to define a user on your system that will be used for your MySQL backups.

Now, we are going to store the login and the password to access your database MySQL in a configuration file in order to avoid revealing information in your crontab thereafter :


Use of MySQLdump

MySQLdump is a tool allowing safeguard and datas restoration in a MySQL base.
It is possible to operate on all the system databases, only one base, just one table or to search tables through one or several bases as shown by the following examples :

The configuration parameters of MySQLdump are numerous and the most common are the following ones :

Parameter to specify the user MySQL.

Parameter to specify the password of the user MySQL.

Parameter to specify that the safeguard includes all the database.

Parameter to specify the database to save.

Parameter to specify the table to save.

Parameter to add a DROP TABLE before each INSERT.

Parameter to specify the columns’ names in the INSERT.

Add quotes</strong> <strong> to the tables’ and columns’ names.

The list is exhaustive and fully available on the documentation :


Backup and restoration

We are going to use a command for the backup implementation for only one table while intentionally omitting the user and password that our system will automatically get back in the pre-configured file /home/save_db/.my.cnf :

Date display in the name of the SQL backup file.

It is also possible to compress the file if the titration is big enough thanks to gzip :

Finally, if you would like to restore a safeguard, the action can be operated according to the following way (optional login and password as with the safeguard) :


Cron creation

Finally, all there’s left is to program a cron task so as to make the backup automatic :

Text


Conclusion

Your automatic backup is ready and will be at the location /home/save_db/
It is also possible to follow the performed operations and so to see if the safeguard worked on /var/log/syslog