v2

mkdir /root/backups; vim /scripts/backup_db.sh 
#!/bin/bash

# Prompt user for root password
read -sp "Enter MySQL root password: " root_password
echo

# Get list of databases
databases=$(mysql -uroot -p"$root_password" -e "SHOW DATABASES;" | grep -Ev "(Database|information_schema|performance_schema|mysql)")

for db in $databases; do
   echo "Dumping database: $db"
   mysqldump -uroot -p"$root_password" $db | gzip > "/root/backups/$db.sql.gz"
   echo "Database '$db' dumped and compressed as '$db.sql.gz'"
done

v1

# run this
crontab -e

# run backups at 2:00
0 2 * * * /root/scripts/backup.sh

# daily updates at 3:00
0 3 * * * /scripts/update.sh

mkdir /root/backups;
vim /scripts/backup_db.sh

#!/bin/bash
# what to backup
WEBROOT=/var/www/html

# where to backup to
BACKUPTO=/root/backups

# delete old backups?
DAYS2KEEP=30

echo "=== backup database ==="
for db in $(mysql -u root -e 'show databases' -s --skip-column-names); do
	echo "... backup $db"
	mysqldump $db | gzip > "$BACKUPTO/$(date +%Y-%m-%d)-$db.sql.gz";
done

echo "=== backup web root ==="
for FULLPATH in $WEBROOT/*; do
    if [ -d "$FULLPATH" ]; then
	BASENAME=$(basename $FULLPATH);

	BACKUPTO_FILE="$BACKUPTO/$(date '+%Y-%m-%d')_$BASENAME.tar.gz"

	echo "... backup $FULLPATH to $BACKUPTO_FILE"
 	tar fcz $BACKUPTO_FILE $FULLPATH
    fi
done

# uncomment, if the user want's backups older than 30 days to be deleted automatically
# echo "=== tidy up? (delete backups older than $DAYS2KEEP days==="
# output found filed
# find $BACKUPTO/*.tar.gz -mtime +$DAYS2KEEP -type f;
# delete found files
# find $BACKUPTO/*.gz -mtime +$DAYS2KEEP -type f -delete;

this will place database backups & files.tar.gz next to each other in the format:

ls -l /root/backups
2022-07-25-information_schema.sql.gz
2022-07-25-mysql.sql.gz
2022-07-25-performance_schema.sql.gz
2022-07-25-wordpress.sql.gz
2022-07-25_web_root.tar.gz

liked this article?

  • only together we can create a truly free world
  • plz support dwaves to keep it up & running!
  • (yes the info on the internet is (mostly) free but beer is still not free (still have to work on that))
  • really really hate advertisement
  • contribute: whenever a solution was found, blog about it for others to find!
  • talk about, recommend & link to this blog and articles
  • thanks to all who contribute!
admin