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 :

sudo mysqladmin -u root password new_password -p

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

sudo useradd -d /home/save_db/ -m save_db
sudo passwd save_db
Password: save_db_password
Retype new password: save_db_password
passwd: password updated successfully

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 :

echo "[mysqldump]" > /home/save_db/.my.cnf
echo "user=login_mysql" >> /home/save_db/.my.cnf
echo "password=password_mysql" >> /home/save_db/.my.cnf

### Reading/writing rights only for the owner save_db
chmod 600 /home/save_db/.my.cnf
chown save_db:save_db /home/save_db/.my.cnf

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 :

### Safeguard all the databases
mysqldump --user=login_mysql --password=password_mysql --all-databases > dump_bdd.sql
### Safeguard only one database 
mysqldump -u login_mysql -p password_mysql --databases name_bdd > dump_bdd.sql
### Safeguard of several databases
mysqldump -u login_mysql -p password_mysql --databases name_bdd1 name_bdd2 > dump_bdd.sql
### Safeguard of just one table
mysqldump -u login_mysql -p password_mysql -B name_bdd --tables name_table > dump_bdd.sql
### Safeguard of several tables
mysqldump -u login_mysql -p password_mysql -B name_bdd --tables name_table1 name_table2 > dump_bdd.sql

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 :

mysqldump --add-drop-table --complete-insert --quote-names -B name_bdd > /home/save_db/dump_name_bdd_$(date +%Y-%m-%d).sql
name_bdd

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 :

mysqldump --add-drop-table --complete-insert --quote-names -B name_bdd | gzip > /home/save_db/dump_name_bdd_$(date +%Y-%m-%d).sql
name_bdd

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) :

mysql -u login_mysql -p password_mysql name_bdd < dump_name_bdd_YYY-MM-DD.sql

Cron creation

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

crontab -u save_db -l
### Safeguard of the database name_bdd every Monday at 10am
* * * * 1 /usr/bin/ mysqldump --add-drop-table --complete-insert --quote-names -B name_bdd | gzip > /home/save_db/dump_name_bdd_$(date +%Y-%m-%d).sql

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


Vote