MySQL Database backup shell script

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

Back up the MySQL database in addition to Information_schema, Performance_schema, MySQL and other production libraries from the library.

Back up each library in the MySQL database is backed up (exhaustive), generating a backup file containing data structures and figures, and a backup file containing only data structures.

Typically this script will be used in conjunction with crontab, and the use of crontab is already listed in the script.

The variables that allow the user to modify are as follows:

mysql_host=127.0.0.1 # Host Address mysql_port=3306 # host port number mysql_username=dev# the user name used when backing up mysql_password=dev# backup using the username password for MySQL _basedir=/usr/local/mysql # Mysqlbase Directory, if it is a custom installation of MySQL, then modify here, using the package installed MySQL will not need to modify here save_old_backups_for_days=5# Number of days to save backup files 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:44:43.895515929 +0800# author:# open source software written by  ' Guodong ding <[email protected]> ' # 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# &NBSP;EXAMPLE&NBSP;OF&NBSP;JOB&NBSP;DEFINITION:#&NBSP:---------------- minute  (0 - 59) #  | &nbsp.------------- hour  (0 - 23) # |  |  .---------- day of month  (1  - 31) # |  |  |  ------- month  (1 - 12) &NBSP;OR&NBSP;JAN,FEB,MAR,APR&NBSP;...#&NBSP;|&NBSP;&NBSP;|&NBSP;&NBSP;|&NBSP;&NBSP;|&NBSP;&NBSP,----  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= "' Id -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 = '   ' $ ' \ t ' $ ' \ n ' for mysql_database in ${mysql_databases_list};d o    ${ 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} |        &nBsp; 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 2doneifs=${saved_ifs}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--

This article is from "Communication, My Favorites" blog, please make sure to keep this source http://dgd2010.blog.51cto.com/1539422/1792808

MySQL Database backup shell script