MySQL backup script

Backup script MySQL database

Example:

crontab -e

0 1 * * * /root/scripts/mysql-backup.sh >> /var/log/mysql-backup.log 2>&1

edit script, change options

Mega Link

#!/bin/bash
#----------------------------------------
# OPTIONS
#----------------------------------------
USER='root'       # MySQL User
PASSWORD='your_password' # MySQL Password
DAYS_TO_KEEP=3    # 0 to keep forever
GZIP=1            # 1 = Compress
BACKUP_PATH='/root/mysql_backup/'
#----------------------------------------
echo /////////////////////////////////// >> /var/log/mysql-backup.log
echo "`date`" >> /var/log/mysql-backup.log
echo /////////////////////////////////// >> /var/log/mysql-backup.log
# Create the backup folder
if [ ! -d $BACKUP_PATH ]; then
  mkdir -p $BACKUP_PATH
fi

# Get list of database names
databases=`mysql -u $USER -p$PASSWORD -e "SHOW DATABASES;" | tr -d "|" | grep -v Database`

for db in $databases; do

  if [ $db = 'information_schema' ] || [ $db = 'performance_schema' ] || [ $db = 'mysql' ] || [ $db = 'sys' ]; then
    echo "Skipping database: $db"
    continue
  fi
  
  date=$(date -I)
  if [ "$GZIP" -eq 0 ] ; then
    echo "Backing up database: $db without compression"      
    mysqldump -u $USER -p$PASSWORD --databases $db > $BACKUP_PATH/$date-$db.sql
  else
    echo "Backing up database: $db with compression"
    mysqldump -u $USER -p$PASSWORD --databases $db | gzip -c > $BACKUP_PATH/$date-$db.gz
  fi
done

# Delete old backups
if [ "$DAYS_TO_KEEP" -gt 0 ] ; then
  echo "Deleting backups older than $DAYS_TO_KEEP days"
  find $BACKUP_PATH/* -mtime +$DAYS_TO_KEEP -exec rm {} \;
fi

Hi, do I have to make file /var/log/mysql-backup.log? Because I have warning in email: E: File could not be read: /var/log/mysql-backup.log

regards,
Przemek

You can remove this lines

echo /////////////////////////////////// >> /var/log/mysql-backup.log
echo "`date`" >> /var/log/mysql-backup.log
echo /////////////////////////////////// >> /var/log/mysql-backup.log

@Slawek Do I have to change crontab entry?

If you dont want log yes

OK, Thanks.

did you run script as root ?

Yes, I 'sudo su' and then 'crontab -e'.

do you have file /var/log/mysql-backup.log ?
maybe warning is not because script is not able create file
warning is read not write

No I don't have that file.

try run manualy as root

chmod 755 mysql-backup.sh
./mysql-backup.sh

No I see error:

ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
Deleting backups older than 3 days
find: ‘/root/mysql_backup//*’: No such file or directory

I have similar error when try to install traccar on VPS. I have to use that command:
mysql -u root -p --execute not mysql -u root --execute like I tutorial.

you have to edit script and there where is user write your database user, there where is password write your database password

USER='root'
PASSWORD='your_password' <- here write your database password

if user and password is root

USER='root'
PASSWORD='root'

Yes, I know. I change it.

I did chceck in my system case where password is wrong and !!!!

ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
Deleting backups older than 2 days

you use wrong credentials