Vote

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


Categories: DatabaseTutorials

Related Posts

System

Installation of a web server LEMP (Linux, Nginx, MySQL, PHP)

Vote The installation of a server LEMP Linux + Nginx + MySQL + PHP might be more useful and efficient than an Apache (server LAMP). Articles similaires

System

Operating load-balancing with HAproxy

Vote HAproxy is a software allowing Load-Balancing between several web servers by allocating requests in an almost transparent way for the user. Articles similaires

System

10 tips to write efficient scripts on Linux

Vote The shell script is the most simple type of programming you would write on Linux. Moreover, it is a necessary skill to automate your system administration tasks, to develop new simple features, … In Read more…