Backup all MySQL databases

Goal : Saving MySQL databases to a samba (windows share) server (Ubuntu Linux).

Relatively easy thing to do, but I will keep this here if I need to do it again someday… Here is the code of a small bash script used to do that (not optimal, I know). But first, to get things started  :

# Create a backup user to get read access to the database
mysql -u root -p
mysql> GRANT SELECT,RELOAD,LOCK TABLES ON *.* TO ‘backup_user’@’localhost’;

# Add the root user to the mysql group (as cron jobs run as root)
sudo usermod -a -G mysql root

# Allows read access to users of the mysql group for the directories of mysql data files
sudo find /var/lib/mysql -maxdepth 1 -type d -exec chmod g+r \{\} \;
# List all databases
mysql -u backup_user -B -e ‘show databases;’
# Backup one database
mysqlhotcopy -u backup_user databaseName /TheBackupDir/
# Backup all databases -> this does not work as some databases listed do not really exist !
for i in `mysql -u backup_user -B -e ‘show databases;’`; do   echo $i; mysqlhotcopy -u backup_user “$i” /TheBackupDir/ ; done

On the samba share, create a directory WebServerBackup and in this directory, an empty file autoBackup to allow the script to confirm that the right directory is mounted before backing the database to it.

The samba share used here does not need a username or password, but adding user=”myuser”,password=”mypassword” to the list of options when mounting the share should work nicely in other cases.

The /var/www dir will be saved as is (no old versions) in the same directory and kept syncrhonized by rsync. A log will be written in /var/log/backup.log

Here is the script itself :

#!/bin/bash
echo >> /var/log/backup.log
echo >> /var/log/backup.log
day=$(date +%d-%m-%Y_%Hh%M)
# List of databases to backup. Unfortunately, 
#   mysql -u backup_user -B -e 'show databases;'
#   lists system databases that cannot be saved. Using manual list instead in the for loop
echo "-------------------------------------------------" >> /var/log/backup.log
echo "Beginning WebServer Backup -> Launching backupWebsite.sh" >> /var/log/backup.log
date -R >> /var/log/backup.log
mount -t cifs -o iocharset=utf8,file_mode=0777,dir_mode=0777 //MySambaServer/MySambaShare  /backupDisk
if [ -f "/backupDisk/WebServerBackup/autoBackup" ]
then
  rsync -rltp --del --stats  /var/www /backupDisk/WebServerBackup/www >> /var/log/backup.log # --del
  mkdir "/backupDisk/WebServerBackup/mysql_$day"
  # Backup all databases
  for i in 'mybase' 'wordpress' 'mysql'; 
    do 
      echo $i; mysqlhotcopy -u backup_user "$i" "/backupDisk/WebServerBackup/mysql_$day" >> /var/log/backup.log ;
    done
  # Remove the old database backups (more than 30 days)
  find /backupDisk/WebServerBackup -maxdepth 1 -type d -name "mysql_*" -mtime +30 -exec rm -rf \{\} \;
else
  echo "Echec du montage ! ***Pas de sauvegarde du serveur web !***" >> /var/log/backup.log
fi

umount /backupDisk

echo "WebServer BACKUP FINISHED (in //MySambaServer/MySambaShare/WebServerBackup )" >> /var/log/backup.log
date -R >> /var/log/backup.log
echo "**************************************************" >> /var/log/backup.log

Finally, the script (executable, thanks to a sudo chmod a+x /usr/bin/backupWebsite.sh) is linked to from the directory /etc/cron.daily/  so it will launch automatically once a day. The .sh is removed in the link name as some versions of cron don’t execute .sh files.

sudo ln -s /usr/bin/backupWebsite.sh /etc/cron.daily/backupWebsite