2
---
Automate Backups with cron
Entries can be added to /etc/crontab to regularly schedule database backups.
Create a file to hold the login credentials of the MySQL root user which will be performing the backup. Note that the system user whose home directory this file is stored in can be unrelated to any MySQL users.
/home/example_user/.mylogin.cnf
1
2
3
[client]user = root password = MySQL root user's password
Restrict permissions of the credentials file:
chmod 600 /home/example_user/.mylogin.cnf
Create the cron job file. Below is an example cron job to back up the entire database management system every day at 1am:
/etc/cron.daily/mysqldump
The restoration command’s general syntax is:
mysql -u [username] -p [databaseName] < [filename].sql
Restore an entire DBMS backup. You will be prompted for the MySQL root user’s password:
This will overwrite all current data in the MySQL database system
mysql -u root -p < full-backup.sql
Restore a single database dump. An empty or old destination database must already exist to import the data into, and the MySQL user you’re running the command as must have write access to that database:
mysql -u [username] -p db1 < db1-backup.sql
Restore a single table, you must have a destination database ready to receive the data:
mysql -u dbadmin -p db1 < db1-table1.sql
-
1
0 1 * * * /usr/bin/mysqldump --defaults-extra-file=/home/example_user/.my.cnf -u root --single-transaction --quick --lock-tables=false --all-databases > full-backup-$(date +\%F).sql