Shulou(Shulou.com)06/01 Report--

This script backs up the data from the database on the remote host or the local host locally.

Back up the production libraries of the MySQL database except information_schema, performance_schema, and mysql.

During the backup, every library in the MySQL database is backed up (exhaustively) to generate a backup file that contains the data structure and data and a backup file that contains only the data structure.

Usually this script will be used in conjunction with crontab, and the use of crontab has been listed in the script.

The variables that users are allowed to modify are as follows:

Mysql_host=127.0.0.1 # Host address mysql_port=3306 # Host port number mysql_username=dev # username mysql_password=dev used in backup # password of the user name used in backup mysql_basedir=/usr/local/mysql # mysqlbase directory If it is a custom installation of mysql, modify it here, and the mysql installed using the package does not need to modify the number of days save_old_backups_for_days=5 # saved backup files here mysql_backup_dir=/data/backup/db/mysql # mysql backup path

The script reads as follows:

#! / usr/bin/env bash# Function description:# Backup MySQL databases for each Backup schema and schema with data in one action.# Usage:# bash BackupMysqlByDate.sh# Birth Time:# 2016-06-24 17 backup schema and schema with data in one action.# Usage:# bash BackupMysqlByDate.sh# Birth Time:# 44V 43.895515929 + 080 Author:# Open Source Software written by 'Guodong Ding' # Blog: http://dgd2010.blog.51cto.com/# Github: https://github.com/DingGuodong# Others:# crontabs-- configuration and scripts for running periodical jobs# SHELL=/bin/bash# PATH=/sbin:/bin:/usr/sbin:/usr/bin# MAILTO=root# HOME=/# For Details see man 4 crontabs# Example of job definition:#.-minute (0-59) # |.-hour (0-23) # | |.-day of month (1-31) # |.-month (1-12) OR jan Feb,mar,apr. # |.-day of week (0-6) (Sunday=0 or 7) OR sun,mon,tue,wed,thu,fri Sat# | | # * user-name command to be executed# m h dom mon dow command# execute on 11:59 per sunday# 59 11 * / 0 / path/to/BackupMysqlByDate.sh > / tmp/log_backup_mysql_$ (date + "\% Y\% m\% d\% H\% M\% S"). Log# or# execute on 23:59 per day# 59 23 * / path/to/ BackupMysqlByDate.sh > / tmp/log_backup_mysql_$ (date + "\% Y\% m\% d\% H\% M\% S") .logUSER = "`UID-un`" LOGNAME= "$USER" if [$UID-ne 0] Then echo "WARNING: Running as a non-root user,\" $LOGNAME\ ". Functionality may be unavailable. Only root can use some commands or options "fiold_PATH=$PATHdeclare-x PATH=" / usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin:/usr/games:/usr/local/games "mysql_host=127.0.0.1mysql_port=3306mysql_username=devmysql_password=devmysql_basedir=/usr/local/mysqlsave_old_backups_for_days=5mysql_bin_mysql=$ {mysql_basedir} / bin/mysqlmysql_bin_dump=$ {mysql_basedir} / bin/mysqldumpmysql_backup_dir=/ Data/backup/db/mysqldate_format_type_dir=$ (date +% Y-%m-%d) date_format_type_file=$ (date +% Y%m%d%H%M%S) echo "-" echo "= > do backup scheduler start at $(date +% Y%m%d%H%M%S)" # TODO Check user privileges# check user if have 'RELOAD,EVENT' privileges,etc# backup role# GRANT ALTER,ALTER ROUTINE,CREATE,CREATE ROUTINE,CREATE TEMPORARY TABLES,CREATE VIEW,DELETE,DROP,EXECUTE,INDEX,INSERT,LOCK TABLES,SELECT,UPDATE,SHOW VIEW,RELOAD,EVENT ON *. * TO' dev'@ "%" # FLUSH PRIVILEGES [- d ${mysql_basedir}] & & mysql_datadir=$ {mysql_basedir} / data | | mysql_datadir=/var/lib/mysql [- x ${mysql_bin_mysql}] | | mysql_bin_mysql=mysql [- x ${mysql_bin_dump}] | | mysql_bin_dump=mysqldump [- d ${mysql_backup_dir} / ${date_format_type_dir}] | mkdir-p ${mysql_backup_dir} / ${date_format_type _ dir} mysql_databases_list= "" if [- d ${mysql_datadir}] Then mysql_databases_list= `ls-p$ {mysql_datadir} | grep / | tr-d / `else mysql_databases_list=$ (${mysql_bin_mysql}-h$ {mysql_host}-P$ {mysql_port}-u$ {mysql_username}-p$ {mysql_password}\-e "show databases;" | & grep-Eiv'(^ database$ | information_schema | performance_schema | ^ mysql$)') fisaved_IFS=$IFSIFS=''$'\ tasking'\ n'for mysql_database in ${mysql_databases_list} Do ${mysql_bin_dump}-host=$ {mysql_host}-port=$ {mysql_port}-user=$ {mysql_username}-password=$ {mysql_password}\-routines-events-triggers-single-transaction-flush-logs\-ignore-table=mysql.event-databases ${mysql_database} | &\ gzip > ${mysql_backup_dir} / ${date_format_type_dir} / ${mysql_database}-backup-$ {date_format_type_file} .sql.gz [$?-eq 0] & & echo "${mysql_database} backup successfully!" | |\ echo "${mysql_database} backup failed!" / bin/sleep 2 ${mysql_bin_dump}-host=$ {mysql_host}-port=$ {mysql_port}-user=$ {mysql_username}-password=$ {mysql_password }\-- routines-- events-- triggers-- single-transaction-- flush-logs\-- ignore-table=mysql.event-- databases ${mysql_database}-- no-data | &\ gzip > ${mysql_backup_dir} / ${date_format_type_dir} / ${mysql_database}-backup-$ {date_format_type_file} _ schema.sql.gz [$?-eq 0] & & echo "${mysql_database} schema backup successfully!" | |\ echo "${mysql_database} schema backup failed!" / bin/sleep 2 save_days=$ {save_old_backups_for_days:-10} need_clean=$ (find ${mysql_backup_dir}-mtime + ${save_days}-exec ls'{}'\) ) if [!-z ${need_clean}]; then find ${mysql_backup_dir}-mtime + ${save_days}-exec rm-rf'{}'\; echo "$need_clean have been cleaned!" Else echo "nothing can be cleaned, skipped!" Fiecho "= > do backup scheduler finished at $(date +% Y%m%d%H%M%S)" echo-e "\ n\ n\ n" declare-x PATH=$ {old_PATH}

Tag:mysqldump, backup MySQL database, MySQL database backup, mysql backup, mysql backup script

-- end--

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.