<sub id="ntl1z"></sub>

<sub id="ntl1z"></sub>

<thead id="ntl1z"></thead>

      MySQL Backup--使用mysqldump依次备份所有数据库

      某些场景下需要将数据库分开备份,有些场景又需要将所有数据库合在一起备份,特此整理此备份脚本

      #!/bin/bash
      ##===========================================================================##
      ## use mysqldump to backup all user database, keey 5 days.
      ##===========================================================================##
      ## mysql_backup_database option:
      ## ONE_BACKUP: backup all user database into one zip file
      ## MORE_BACKUP: backup user database into different zip files.
      ## DATABASE_NAME: only backup the specified database.
      
      ##===========================================================================##
      ## mysql backup config
      mysql_exe="/export/servers/mysql/bin/mysql"
      mysqldump_exe="/export/servers/mysql/bin/mysqldump"
      mysql_backup_folder="/export/data/mysql/dumps/"
      mysql_backup_log="${mysql_backup_folder}mysql_dump_log.txt"
      mysql_backup_log_his="${mysql_backup_folder}mysql_dump_log_his.txt"
      mysql_backup_host="127.0.0.1"
      mysql_backup_port=3306
      mysql_backup_user="root"
      mysql_backup_password="rootpwd"
      mysql_backup_database="ONE_BACKUP"
      mysql_backup_table_name_list=""
      mysql_backup_keep_days=5
      
      ##====================================================##
      ## get mysql version
      ##====================================================##
      function get_mysql_version()
      {
          master_version_tmp=`${mysql_exe}     --host="${mysql_backup_host}" --port=${mysql_backup_port}     --user="${mysql_backup_user}" --password="${mysql_backup_password}"     -e "select @@version;"`
          if [[ master_version_tmp == 5.5.* ]]
          then
              mysql_version="mysql55"
          elif [[ master_version_tmp == 5.6.* ]]
          then
              mysql_version="mysql56"
          else
              mysql_version="mysql57"
          fi
      }
      
      ##===========================================================================##
      ## remove expired backup file
      ## keep the backup file of the last N days
      function remove_expired_file()
      {
          echo "$(date "+%Y-%m-%d %H:%M:%S")  start to remove expired backup file." >> ${mysql_backup_log}
          echo "keep days:    ${mysql_backup_keep_days}" >> ${mysql_backup_log}
          find "${mysql_backup_folder}" -mtime +${mysql_backup_keep_days} -name "*" -exec rm -rf {} \;
          echo "$(date "+%Y-%m-%d %H:%M:%S")  start to mysqldump." >> ${mysql_backup_log}
      }
      
      ##===========================================================================##
      ## backup single database
      function backup_single_database()
      {
          current_database_name=$1
          mysql_backup_file_path="${mysql_backup_folder}""${current_database_name}-`date -I`.sql.gz"
          echo "$(date "+%Y-%m-%d %H:%M:%S")  start to backup database ${current_database_name} to ${mysql_backup_file_path}" >> ${mysql_backup_log}
          
          ($mysqldump_exe     --host="${mysql_backup_host}"     --port=$mysql_backup_port     --user="${mysql_backup_user}"     --password="${mysql_backup_password}"     --databases "${current_database_name}"      --set-gtid-purged=OFF     --single-transaction     --hex-blob --opt --quick     --events --routines --triggers     --default-character-set="utf8"     --master-data=2     |gzip > "${mysql_backup_file_path}"     ) 1>>${mysql_backup_log} 2>>${mysql_backup_log} 
      
          if [ $? = 0 ]
          then
              echo "$(date "+%Y-%m-%d %H:%M:%S")  backup database ${current_database_name} success." >> ${mysql_backup_log}
          else
              echo "$(date "+%Y-%m-%d %H:%M:%S")  backup database ${current_database_name} failed." >> ${mysql_backup_log}
          fi
      }
      
      function backup_more_databases()
      {
          for database_name in ${database_name_list};
          do
              if [ "$database_name" == "" ]
              then
                  echo "database name can be empty"
              else
                  backup_single_database "${database_name}"
              fi
          done
      }
      
      function backup_all_databases()
      {
          mysql_backup_file_path="${mysql_backup_folder}""full-backup-`date -I`.sql.gz"
          echo "$(date "+%Y-%m-%d %H:%M:%S")  start to backup all databases to ${mysql_backup_file_path}" >> ${mysql_backup_log}
          
          ($mysqldump_exe     --host="${mysql_backup_host}"     --port=$mysql_backup_port     --user="${mysql_backup_user}"     --password="${mysql_backup_password}"     --all-databases     --ignore-table=mysql.*     --ignore-table=information_schema.*     --ignore-table=performance_schema.*     --ignore-table=sys.*     --set-gtid-purged=OFF     --single-transaction     --hex-blob --opt --quick     --events --routines --triggers     --default-character-set="utf8"     --master-data=2     |gzip > "${mysql_backup_file_path}"     ) 1>>${mysql_backup_log} 2>>${mysql_backup_log} 
      
          if [ $? = 0 ]
          then
              echo "$(date "+%Y-%m-%d %H:%M:%S")  backup database ${current_database_name} success." >> ${mysql_backup_log}
          else
              echo "$(date "+%Y-%m-%d %H:%M:%S")  backup database ${current_database_name} failed." >> ${mysql_backup_log}
          fi
      }
      
      
      function backup_databases()
      {    
          if [ "${mysql_backup_database}" == "ONE_BACKUP" ];
          then
              backup_all_databases
          elif [ "${mysql_backup_database}" == "MORE_BACKUP" ];
          then
              database_name_list=`${mysql_exe} --host="${mysql_backup_host}" --port=$mysql_backup_port --user="${mysql_backup_user}" --password="${mysql_backup_password}" -Ne "show databases;" |egrep -v "(mysql|sys|test|information_schema|performance_schema|dada_call_center)"`
              backup_more_databases
          else 
              database_name_list=${mysql_backup_database}
              backup_more_databases
          fi
      }
      
      
      ##====================================================##
      ## 1. dump user script on mysql
      ## 2. this script only can be used on mysql 5.7
      ##====================================================##
      function dump_user_script_5_7()
      {
          script_file_path="${mysql_backup_folder}""user-script-`date -I`.sql"
          echo "$(date "+%Y-%m-%d %H:%M:%S")  start backup user script." >> ${mysql_backup_log}
          (echo "select concat(‘show create user ‘‘‘,user,‘‘‘@‘‘‘,host, ‘‘‘;
          ,show grants for ‘‘‘,user,‘‘‘@‘‘‘,host, ‘‘‘;) 
          from mysql.user where user <>root and user<>‘‘ and host <> ‘‘ " | \
          ${mysql_exe} --host="${mysql_backup_host}" --port=${mysql_backup_port}     --user="${mysql_backup_user}" --password="${mysql_backup_password}" -N |     ${mysql_exe} --host="${mysql_backup_host}" --port=${mysql_backup_port}     --user="${mysql_backup_user}" --password="${mysql_backup_password}" -N |     sed "s/$/;/" >> ${script_file_path}) 1>>${mysql_backup_log} 2>>${mysql_backup_log} 
          
          if [ $? = 0 ]
          then
              echo "$(date "+%Y-%m-%d %H:%M:%S")  backup user script success." >> ${mysql_backup_log}
          else
              echo "$(date "+%Y-%m-%d %H:%M:%S")  backup user script failed." >> ${mysql_backup_log}
          fi
      }
      
      
      ##====================================================##
      ## 1. dump user script on mysql
      ## 2. this script only can be used on mysql 5.5
      ##====================================================##
      function dump_user_script_5_5()
      {
          script_file_path="${mysql_backup_folder}""user-script-`date -I`.sql"
          echo "$(date "+%Y-%m-%d %H:%M:%S")  start backup user script." >> ${mysql_backup_log}
          
          (echo "select concat(‘show grants for ‘‘‘,user,‘‘‘@‘‘‘,host, ‘‘‘;‘)  
          from mysql.user where user <>root and user<>‘‘ and host <> ‘‘ " | \
          ${mysql_exe} --host="${mysql_backup_host}" --port=${mysql_backup_port}     --user="${mysql_backup_user}" --password="${mysql_backup_password}" -N |     ${mysql_exe} --host="${mysql_backup_host}" --port=${mysql_backup_port}     --user="${mysql_backup_user}" --password="${mysql_backup_password}" -N |     sed "s/$/;/" >> ${script_file_path}) 1>>${mysql_backup_log} 2>>${mysql_backup_log} 
          
          if [ $? = 0 ]
          then
              echo "$(date "+%Y-%m-%d %H:%M:%S")  backup user script success." >> ${mysql_backup_log}
          else
              echo "$(date "+%Y-%m-%d %H:%M:%S")  backup user script failed." >> ${mysql_backup_log}
          fi
      }
      
      ##===========================================================================##
      
      function backup_mysql_user()
      {
          if [[ mysql_version == "mysql55" ]]
          then
              dump_user_script_5_5
          else
              dump_user_script_5_7
          fi
      }
      
      
      ##===========================================================================##
      function mysql_backup()
      {
          echo > ${mysql_backup_log}
          get_mysql_version
          remove_expired_file
          backup_databases
          backup_mysql_user
          cat ${mysql_backup_log} > ${mysql_backup_log_his}
      }
      
      mysql_backup
      相关文章
      相关标签/搜索
      最准的特马网站20202020四肖期期准四肖三期内必出四肖期期准免费公开四肖中特1肖1码期期大公开本港台开奖现场直播 开奖结果