Aim; daily backup mysql in root folder using cron and mysqldmp & rsync
Local Host mysql backup:
0 1 * * * /usr/local/mysql/bin/mysqldump -uroot -ppassword –opt database > /path/to/directory/filename.sql
(There is no space between the -p and password or -u and username – replace root with a correct database username.)
sudo mkdir /var/backup/db
vim script.sh or nano script.sh
# (1) set up all the mysqldump variables
DATE=`date +”%d_%b_%Y_%H%M”`
SQLFILE=/var/backup/db/db_backup_${DATE}.sql
DATABASE=<database_name>
USER=<db_user>
PASSWORD=<db_user_password>
# (2) in case you run this more than once a day,
# remove the previous version of the file
unalias rm 2> /dev/null
rm ${SQLFILE} 2> /dev/null
rm ${SQLFILE}.gz 2> /dev/null
# (3) do the mysql database backup (dump)
sudo mysqldump -u ${USER} -p${PASSWORD} ${DATABASE}|gzip > ${SQLFILE}.gz
edit the my.cnf
[mysqldump]
quick
quote-names
max_allowed_packet = 16M
user=root
password=complexPassword
Traverse to the script’s folder:
cd /var/backup/
And execute the following command:
sudo ./script.sh
Scheduling Execution with Cron
Edit the server’s cron with the undermentioned command:
sudo crontab -e
0 1 * * * /var/backup/script.sh
delete backups older than 7 days
sudo find /var/backup/db/. -mtime +7 -exec rm {} \;
back Up MySQL Databases with AutoMySQLBackup
sudo apt-get install automysqlbackup
Once installed, the script will automatically run once a day. Backups will be stored in the directory:
/var/lib/automysqlbackup
confuguration file
sudo nano /etc/default/automysqlbackup
manual run
sudo automysqlbackup
verify mysql
ls -R /var/lib/automysqlbackup/
/var/lib/automysqlbackup/daily/jobsite:
jobsite_2022-12-14_13h04m.Wednesday.sql.gz
/var/lib/automysqlbackup/daily/phpmyadmin:
phpmyadmin_2022-12-14_13h04m.Wednesday.sql.gz
/var/lib/automysqlbackup/daily/sarkarijobz:
sarkarijobz_2022-12-14_13h04m.Wednesday.sql.gz
/var/lib/automysqlbackup/daily/sys:
sys_2022-12-14_13h04m.Wednesday.sql.gz
/var/lib/automysqlbackup/monthly:
delete mysql backups older than 7 days
run rm command weekly
@weekly rm -r /var/lib/automysqlbackup/daily/*
@monthly rm -r /var/lib/automysqlbackup/weekly/*
view crontab -l
crontab -e
service cron reload
sudo find /var/lib/automysqlbackup/daily/. -mtime +7 -exec rm {} \;
Automysqlbackup – Could not open required defaults file: /etc/mysql/debian.cnf
by default, assigns many variables by the MySQL file located at “/etc/mysql/debian.cnf”. This contains maintenance login information
From this file, it reads the user, password, and databases that must be backed up.
Create /etc/mysql/debian.cnf file add below details
client
db name
db pass
[client]
host = localhost
user = root
password = "mysupersecretpass"
socket = /var/run/mysqld/mysqld.sock
optional
[mysql_upgrade]
host = localhost
user = root
password = mysupersecretpass
socket = /var/run/mysqld/mysqld.sock
basedir = /usr