mysqlbackup.sh 1.8 KB

12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849505152535455565758
  1. #!/bin/bash
  2. # Simple script to backup MySQL databases
  3. # Parent backup directory
  4. backup_parent_dir="/var/backups/mysql"
  5. # MySQL settings
  6. mysql_user="root"
  7. mysql_password="ROOTPASSWD"
  8. # Read MySQL password from stdin if empty
  9. # if [ -z "${mysql_password}" ]; then
  10. # echo -n "Enter MySQL ${mysql_user} password: "
  11. # read -s mysql_password
  12. # echo
  13. # fi
  14. # Check MySQL password
  15. echo exit | mysql --user=${mysql_user} --password=${mysql_password} -B 2>/dev/null
  16. if [ "$?" -gt 0 ]; then
  17. echo "MySQL ${mysql_user} password incorrect"
  18. exit 1
  19. else
  20. echo "MySQL ${mysql_user} password correct."
  21. fi
  22. # Create backup directory and set permissions
  23. backup_date=`date +%Y_%m_%d_%H_%M`
  24. backup_dir="${backup_parent_dir}/${backup_date}"
  25. echo "Backup directory: ${backup_dir}"
  26. mkdir -p "${backup_dir}"
  27. chmod 644 "${backup_dir}"
  28. # Get MySQL databases
  29. mysql_databases=`echo 'show databases' | mysql --user=${mysql_user} --password=${mysql_password} -B | sed /^Database$/d`
  30. # Backup and compress each database
  31. for database in $mysql_databases
  32. do
  33. if [ "${database}" == "information_schema" ] || [ "${database}" == "performance_schema" ]; then
  34. additional_mysqldump_params="--skip-lock-tables --compact --no-autocommit "
  35. else
  36. additional_mysqldump_params=""
  37. fi
  38. echo "Creating backup of \"${database}\" database"
  39. mysqldump ${additional_mysqldump_params} --user=${mysql_user} --password=${mysql_password} ${database} | gzip > "${backup_dir}/${database}.sql.gz"
  40. chmod 644 "${backup_dir}/${database}.sql.gz"
  41. done
  42. # compress the folder
  43. # tar -zcvf "${backup_dir}.tar.gz" "${backup_dir}"
  44. # rm -rf "${backup_dir}"
  45. # Rotate backups
  46. # Delete files older than 30 days
  47. find $backup_parent_dir/ -type f -mtime +60 -delete;
  48. # Delete empty directories
  49. find $backup_parent_dir/ -type d -empty -delete;