Difference between revisions of "MySQL Backup and Restore"

#------------------------
#!/bin/bash
# run script as sudo
password="xxx"
user="plankandreas"
date=`date +'%Y%m%d'`
backuppath="/mnt/dump"
backupfile="${date}_alldb.sql.gz"

if [ ! -d "${backuppath}" ]; then #no directory?
  echo "Create ${backuppath} … "
  mkdir -p "${backuppath}" # create also parent folders
fi

# all Databases
# see http://www.velikan.net/import-sql-dump-file-to-mysql-database/
echo "Start backup ("`date`") … "
mysqldump --user=${user} --password=${password} --all-databases --single-transaction | gzip > "${backuppath}/${backupfile}"
echo "End backup ("`date`") … "
echo "Done. Backup see '${backupfile}'  in cd ${backuppath}"

#------------------------

sudo mysql -u root -p < "/mnt/dump/alldb.sql"
# Note: for security reasons, never add the password directly on the command line, 
# adding -p without a passwort will cause a prompt!