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

JN Community

Les Ressources, en particulier les tutoriaux, présupposent que l’Utilisateur qui décide de les mettre en œuvre dispose des connaissances, des compétences et de l’expérience nécessaire pour cette mise en œuvre. L’Utilisateur disposant d’une connaissance, compétence et/ou expérience limitée ou insuffisante doit absolument s’abstenir de mettre en œuvre les Ressources par lui-même. Jaguar Network décline toute responsabilité quant aux conséquences dommageables de la mise en œuvre des Ressources, notamment sur les infrastructures informatiques de l’Utilisateur, de ses commettants ou préposés ou de tout tiers. Il est précisé en tant que de besoin que toute intervention de Jaguar Network visant à réparer les dommages causés par la mise en œuvre des Ressources par un Utilisateur ne disposant pas des connaissances, compétences et/ou expériences suffisantes sera facturée et fera l’objet d’un devis préalable et d’un bon de commande aux conditions des contrats Jaguar Network en vigueur.

Related Posts

System

Shutdown and restart your Linux system

Vote On Linux system, there are a lot of commands to shutdown and reboot your system. This tutorial aims to detail some existing possibilities. Articles similaires

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