MySQL 脚本备份相关

Shell 脚本定期备份

mysql_config_editor — MySQL Configuration Utility 需要配置下mysql_config_editor

  1. mysql_config_editor set --login-path=client
  2.      --host=localhost --user=localuser --password

backup_parent_dir 是备份路径mysql_user="root" 备份用户，其实这里可以不用

  1. #!/bin/bash
  2. # Simple script to backup MySQL databases
  4. # Parent backup directory
  5. backup_parent_dir="./backup"
  7. # MySQL settings
  8. mysql_user="root"
  10. # Read MySQL password from stdin if empty
  12. # Check MySQL password
  13. #echo exit | mysql --login-path=client  --host=localhost --user=${mysql_user} --password=${mysql_password} -B 2>/dev/null
  14. echo exit | mysql --login-path=client -B 2>/dev/null
  15. if [ "$?" -gt 0 ]; then
  16.   echo "MySQL ${mysql_user} password incorrect"
  17.   exit 1
  18. else
  19.   echo "MySQL ${mysql_user} password correct."
  20. 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 700 "${backup_dir}"
  28. logfile="$backup_parent_dir/"backup_log_"$(date +'%Y_%m')".txt
  30. # Get MySQL databases
  31. mysql_databases=`echo 'show databases' | mysql --login-path=client | sed /^Database$/d`
  33. # Backup and compress each database
  34. for database in $mysql_databases
  35. do
  36.   if [ "${database}" == "information_schema" ] || [ "${database}" == "performance_schema" ]; then
  37.         additional_mysqldump_params="--skip-lock-tables"
  38.   else
  39.         additional_mysqldump_params=""
  40.   fi
  41.   echo "Creating backup of \"${database}\" database"
  42.   mysqldump --login-path=client  ${additional_mysqldump_params} -e  | gzip > "${backup_dir}/${database}.gz"
  43.   echo "mysqldump finished at $(date +'%d-%m-%Y %H:%M:%S')" >> "$logfile"
  44.   chmod 600 "${backup_dir}/${database}.gz"
  45. done
  46. echo "mysqldump finished at $(date +'%d-%m-%Y %H:%M:%S')" >> "$logfile"

再用crontab 来定制备份计划

python 备份脚本

  1. import os
  2. import time
  4. filestamp = time.strftime("%Y-%m-%d")
  6. database_list_command="mysql --login-path=client -e 'show databases'"
  7. for database in os.popen(database_list_command).readlines():
  8.     database = database.strip()
  9.     if database == 'information_schema':
  10.         continue
  11.     if database == 'performance_schema':
  12.         continue
  13.     filename = '/Users/xiaodian/mysql/backup/{0}_{1}.sql'.format(database, filestamp)
  14.     os.popen("mysqldump --login-path=client -c {0}| gzip > {1}.gz".format(database, filename))
  16. os.popen("echo mysqldump finished at {0} >> backup_log_{1}.txt".format(filename, filestamp))

脚本参考https://blog.sleeplessbeastie.eu/2012/11/22/simple-shell-script-to-backup-mysql-databases/

原文: https://cxiaodian.gitbooks.io/mysql/content/script_back.html