A little background on the script:
Back when I had a dedicated website hosting server, most of the issues that I had with the main CMS’ that I used could be solved with data that was within the database. Someone deleted a post, corrupted the database, etc. I also had a few cases where the server had a issue and they restored it back to the last known state, and many times that was a couple of days before. Without database dumps, recovering the CMS back to its “current state” proved to be difficult.
What this script does is grab the current time and date and dumps all databases known to the server into a folder that is away from prying eyes. It also removes the backups from the previous run so that the disk space could be preserved. I could then use a rsync scrypt / job or my favorite SFTP program to grab the files periodically. I created a second database user that received rights to each database as I brought a new website on-line so that the script could read it. I also used these files a couple of times that servers were upgraded to move all the sites and their databases to the new server.
You will see similar scripts all over the internet, and this version is no different. This is the code that fit my needs at the time.
#!/bin/bash
# Set date / time var to add to database name
NOW=$(date +"%Y-%m-%d-%H-%M")
# Set vars for mySQL command line
DB_USER=put_user_name_here
DB_PASS='put_pw_for_user_here'
# Remove all SQL bacup files from /var/mysql_backups
find /var/mysql_backups/*.sql -type f -exec rm {} \;
### Start MySQL Backup ###
# Get all databases name
DBS="$(mysql -u $DB_USER -p$DB_PASS -Bse 'show databases')"
for db in $DBS
do
echo "$db"
mysqldump -u $DB_USER -p$DB_PASS $db > /var/mysql_backups/$db-$NOW.sql
done