Have you thought about continuous database backup? Perhaps using the Control Panel is the choice of many people. But it will not be optimal in case you want to customize the backup file name. Or your server only runs MySQL, MariaDB (without Control Panel, no PHP,…). I will show you how to Automated Smart Database Backup (MariaDB, MySQL) on RHEL Linux (Centos/Rocky) using Cron Job.
Use *.sh to make a Job
My advice is to write a *.sh file to do this. And next is to install the Cron Job schedule.
The file *.sh will have the following content:
#!/bin/bash
# backup_filename=dbname_`date +%F_%H%M%S`
mysqldump -R -u root -pMypass@999 -R --compact your_database_name | gzip > /home/dbbackup/dbname_`date +%F_%H%M%S`.sql.gz
Note:
- In above example, my SQL account is root, and password is Mypass@999. If your user have no password, do not type -p
- You need to create the “/home/dbbackup/” folder, or change to other exist folder
- “-R” mean included procedures
- “–compact” mean include foreign key
- You can do this *.sh from a other server with some changes:
- You open port TCP 3306 in your database server to that server
- in command, you add “-P <database_server_ip>
- To do this, you can read this post: How to remote database (MySQL, MariaDB).
And setting up cronjob is also very simple.
Make Cron job on (Centos/ Rocky/ RHEL Linux)
First, you need to check cronie service on your Centos
rpm -q cronieMy Centos show me: cronie-1.5.2-4.el8.x86_64
If have no cron service, you need to install:
sudo dnf install crontabs
systemctl status crond.service
crontab -e
:wqYou can edit your cron here (in root file): /var/spool/cron/
Delete outdate backup files
find /home/dbbackup/ -mtime +10 -type f -delete
You can use this command in *.sh file to delete any file outdate 10 days
So, my cron config:
00 00 * * * find /home/dbbackup/ -mtime +7 -type f -delete
40 8 * * * /bin/sh /home/backup-mysql.sh 2>&1 >> /home/backup-mysql.log
50 8 * * * /bin/sh /home/backup-mysql.sh 2>&1 >> /home/backup-mysql.log
00 9 * * * /bin/sh /home/backup-mysql.sh 2>&1 >> /home/backup-mysql.log
10 9 * * * /bin/sh /home/backup-mysql.sh 2>&1 >> /home/backup-mysql.log
20 9 * * * /bin/sh /home/backup-mysql.sh 2>&1 >> /home/backup-mysql.log
30 9 * * * /bin/sh /home/backup-mysql.sh 2>&1 >> /home/backup-mysql.log
40 9 * * * /bin/sh /home/backup-mysql.sh 2>&1 >> /home/backup-mysql.log
50 9 * * * /bin/sh /home/backup-mysql.sh 2>&1 >> /home/backup-mysql.log
00 10 * * * /bin/sh /home/backup-mysql.sh 2>&1 >> /home/backup-mysql.log
10 10 * * * /bin/sh /home/backup-mysql.sh 2>&1 >> /home/backup-mysql.log
20 10 * * * /bin/sh /home/backup-mysql.sh 2>&1 >> /home/backup-mysql.log
30 10 * * * /bin/sh /home/backup-mysql.sh 2>&1 >> /home/backup-mysql.log
40 10 * * * /bin/sh /home/backup-mysql.sh 2>&1 >> /home/backup-mysql.log
50 10 * * * /bin/sh /home/backup-mysql.sh 2>&1 >> /home/backup-mysql.log
00 11 * * * /bin/sh /home/backup-mysql.sh 2>&1 >> /home/backup-mysql.log
10 11 * * * /bin/sh /home/backup-mysql.sh 2>&1 >> /home/backup-mysql.log
20 11 * * * /bin/sh /home/backup-mysql.sh 2>&1 >> /home/backup-mysql.log
30 11 * * * /bin/sh /home/backup-mysql.sh 2>&1 >> /home/backup-mysql.log
40 11 * * * /bin/sh /home/backup-mysql.sh 2>&1 >> /home/backup-mysql.log
50 11 * * * /bin/sh /home/backup-mysql.sh 2>&1 >> /home/backup-mysql.log
00 12 * * * /bin/sh /home/backup-mysql.sh 2>&1 >> /home/backup-mysql.log
00 13 * * * /bin/sh /home/backup-mysql.sh 2>&1 >> /home/backup-mysql.log
40 13 * * * /bin/sh /home/backup-mysql.sh 2>&1 >> /home/backup-mysql.log
50 13 * * * /bin/sh /home/backup-mysql.sh 2>&1 >> /home/backup-mysql.log
00 14 * * * /bin/sh /home/backup-mysql.sh 2>&1 >> /home/backup-mysql.log
10 14 * * * /bin/sh /home/backup-mysql.sh 2>&1 >> /home/backup-mysql.log
20 14 * * * /bin/sh /home/backup-mysql.sh 2>&1 >> /home/backup-mysql.log
30 14 * * * /bin/sh /home/backup-mysql.sh 2>&1 >> /home/backup-mysql.log
40 14 * * * /bin/sh /home/backup-mysql.sh 2>&1 >> /home/backup-mysql.log
50 14 * * * /bin/sh /home/backup-mysql.sh 2>&1 >> /home/backup-mysql.log
00 15 * * * /bin/sh /home/backup-mysql.sh 2>&1 >> /home/backup-mysql.log
10 15 * * * /bin/sh /home/backup-mysql.sh 2>&1 >> /home/backup-mysql.log
20 15 * * * /bin/sh /home/backup-mysql.sh 2>&1 >> /home/backup-mysql.log
30 15 * * * /bin/sh /home/backup-mysql.sh 2>&1 >> /home/backup-mysql.log
40 15 * * * /bin/sh /home/backup-mysql.sh 2>&1 >> /home/backup-mysql.log
50 15 * * * /bin/sh /home/backup-mysql.sh 2>&1 >> /home/backup-mysql.log
00 16 * * * /bin/sh /home/backup-mysql.sh 2>&1 >> /home/backup-mysql.log
10 16 * * * /bin/sh /home/backup-mysql.sh 2>&1 >> /home/backup-mysql.log
20 16 * * * /bin/sh /home/backup-mysql.sh 2>&1 >> /home/backup-mysql.log
30 16 * * * /bin/sh /home/backup-mysql.sh 2>&1 >> /home/backup-mysql.log
40 16 * * * /bin/sh /home/backup-mysql.sh 2>&1 >> /home/backup-mysql.log
50 16 * * * /bin/sh /home/backup-mysql.sh 2>&1 >> /home/backup-mysql.log
00 16 * * * /bin/sh /home/backup-mysql.sh 2>&1 >> /home/backup-mysql.log
10 16 * * * /bin/sh /home/backup-mysql.sh 2>&1 >> /home/backup-mysql.log
20 16 * * * /bin/sh /home/backup-mysql.sh 2>&1 >> /home/backup-mysql.log
30 16 * * * /bin/sh /home/backup-mysql.sh 2>&1 >> /home/backup-mysql.log
40 16 * * * /bin/sh /home/backup-mysql.sh 2>&1 >> /home/backup-mysql.log
50 16 * * * /bin/sh /home/backup-mysql.sh 2>&1 >> /home/backup-mysql.log
10 17 * * * /bin/sh /home/backup-mysql.sh 2>&1 >> /home/backup-mysql.log
20 17 * * * /bin/sh /home/backup-mysql.sh 2>&1 >> /home/backup-mysql.log
30 17 * * * /bin/sh /home/backup-mysql.sh 2>&1 >> /home/backup-mysql.log
40 17 * * * /bin/sh /home/backup-mysql.sh 2>&1 >> /home/backup-mysql.log
50 17 * * * /bin/sh /home/backup-mysql.sh 2>&1 >> /home/backup-mysql.log
00 18 * * * /bin/sh /home/backup-mysql.sh 2>&1 >> /home/backup-mysql.log
10 18 * * * /bin/sh /home/backup-mysql.sh 2>&1 >> /home/backup-mysql.log
20 18 * * * /bin/sh /home/backup-mysql.sh 2>&1 >> /home/backup-mysql.log
30 18 * * * /bin/sh /home/backup-mysql.sh 2>&1 >> /home/backup-mysql.log
40 18 * * * /bin/sh /home/backup-mysql.sh 2>&1 >> /home/backup-mysql.log
00 19 * * * /bin/sh /home/backup-mysql.sh 2>&1 >> /home/backup-mysql.log
00 00 * * * /bin/sh /home/backup-mysql.sh 2>&1 >> /home/backup-mysql.logand my *.sh file:
mysqldump -u root -pMypass@999 -R --compact your_database_name | gzip > /home/dbbackup/dbname_`date +%F_%H%M%S`.sql.gz
find /home/dbbackup/ -mtime +10 -type f -deleteNOTE: This command do not backup your SQL functions, procedures
Backup by timestamp
This is my code to backup somthing by Cron Schedule (1), somthing by (1) and hour, something by (1) and minute.
#!/bin/bash
mysqldump -R -u root -R --compact mydb1name | gzip > /home/_backup/mydb1name_`date +%F_%H%M%S`.sql.gz
this_hour=`date +%H`
this_minute=`date +%M`
if [[ $this_hour == "00" ]]; then
mysqldump -R -u root mydb2name | gzip > /home/_backup/mydb2name_`date +%F_%H%M%S`.sql.gz
find /home/_backup/ -mtime +7 -type f -delete
elif [[ $this_minute < "10" ]]; then
mysqldump -R -u root mydb3name | gzip > /home/_backup/mydb3name_`date +%F_%H%M%S`.sql.gz
else
exit
fi
Restore mysql/mariadb with non-check foreign key:
gunzip your file:
gunzip dbfile.sql.gzNon-check help you escape error…
mysql dbname --init-command="SET SESSION FOREIGN_KEY_CHECKS=0;" < dbfile.sqlif error String:
mysql dbname --init-command="SET SESSION FOREIGN_KEY_CHECKS=0;SET NAMES utf8mb4;" < dbfile.sqlDon’t forget reset FOREIGN_KEY_CHECKS=1; again.
mysql;
use dbname;
FOREIGN_KEY_CHECKS=1;If you want create an user for this database and grant:
GRANT ALL PRIVILEGES ON dbname.* to 'username'@'localhost' IDENTIFIED BY 'passsword' WITH GRANT OPTION;
FLUSH PRIVILEGES;#change procedure definer:
UPDATE `mysql`.`proc` p SET definer = 'newuser@%' WHERE definer='olduser@%';
FLUSH PRIVILEGES;# Show all procedure:
SELECT name, definer FROM mysql.proc WHERE db = 'dbname' AND type = 'PROCEDURE';
shell syntax error: unexpected end of file
When you tried to create *.sh file using SFTP, Linux will show you this error. Please delete *.sh file then try to create it with “vi” command:
vi /home/@backup/backup-mysql.sh
<paste your code here>This error cause your file is not exactly format (.sh for DOS, not 3L)
