Use of Linux shell scripts mysqldump backup MySQL database (detailed annotations)

Design and write some design considerations for this script:

1. This script is suitable for compiling and installing MySQL and installing via yum or Apt-get

2. The script can be executed repeatedly without overwriting the data repeatedly

3. Can be added to save disk space by removing backups from N days ago

4. Make full use of mysqldump's self-locking table function, refresh log, copy and other functions
   

Scripts for backing up the MySQL database with the mysqldump command (no annotated version for use in production environments)

#!/bin/bashmysqldbusername=rootmysqldbpassword=passwordmysqbasedir=/usr/local/mysqlmysql= $MYSQBASEDIR/bin/ mysqlmysqldump= $MYSQBASEDIR/bin/mysqldumpbackdir=/var/backup/dbdateformattype1=$ (date +%y-%m-%d) dateformattype2=$ (date +%y%m%d%h%m%s) [ -d  $MYSQBASEDIR  ] && mysqdatadir= $MYSQBASEDIR/data | |  MYSQDATADIR=/var/lib/mysql[ -x  $MYSQL  ] | |  MYSQL=mysql[ -x  $MYSQLDUMP  ] | |  mysqldump=mysqldump[ -d ${backdir} ] | |  mkdir -p ${backdir}[ -d ${backdir}/${dateformattype1} ] | |  mkdir ${backdir}/${dateformattype1}dblist= ' ls -p  $MYSQDATADIR  | grep /  |tr -d /' for dbname in  $DBLIST     do ${mysqldump}  --user=${mysqldbusername} --password=${mysqldbpassword} --routines --events -- triggers --single-transaction --flush-logs --databases ${dbname} | gzip > ${backdir}/${dateformattype1}/${dbname}-backup-${ Dateformattype2}.sql.gz    [ $? -eq 0 ] && echo   "${dbname} has been backuped successful"  | |  echo  "${dbname} has been backuped failed"     /bin/sleep  5done

Script for backing up the MySQL database with the mysqldump command (annotated version for learning and test use)

#!/bin/bash# mysqldbusername is the user name of the MySQL database, customizable mysqldbusername=root# mysqldbpassword is the MySQL database password, Customizable Mysqldbpassword=password# mysqbasedir is the installation directory for MySQL database,--prefix= $MYSQBASEDIR, customizable mysqbasedir=/usr/local/ Mysql# mysql is the absolute path to the MySQL command, customizable mysql= $MYSQBASEDIR/bin/mysql# mysqldump is the absolute path to the mysqldump command, customizable mysqldump = $MYSQBASEDIR/bin/mysqldump# backdir is the storage address of the database backup, can be customized to the remote address backdir=/var/backup/db#  get the current time, in the format: year-month-day , which is used to generate the directory name dateformattype1=$ (date +%y-%m-%d) in this time format #  gets the current time, in the format: Month Day minute, Used to generate the file name in this time format dateformattype2=$ (date +%y%m%d%h%m%s) #  If there is a Mysqbasedir directory, set Mysqdatadir to $ Mysqbasedir/data, specifically what path, the data into what path, otherwise the Mysqbasedir is set to/var/lib/mysql, can be customized [ -d  $MYSQBASEDIR  ]  && mysqdatadir= $MYSQBASEDIR/data | |  MYSQDATADIR=/var/lib/mysql#  if the MySQL command is present and executable, continue, otherwise MySQL is set to MySQL, mysql[ -x  under the default path $MYSQL  ] | |  MYSQL=mysql#  if the mysqldump command exists and can be executed, continue, otherwise set mysqldump to Mysqldump, mysqldump[&nbsp under the default path;-x  $MYSQLDUMP  ] | |  MYSQLDUMP=mysqldump#  Create this directory if there is no backup directory [ -d ${backdir} ] | |  mkdir -p ${backdir}[ -d ${backdir}/${dateformattype1} ] | |  mkdir ${BACKDIR}/${DATEFORMATTYPE1}#  what databases are available in MySQL, based on the name of the directory under Mysqldatadir, which can be customized here, tododblist = ' ls -p  $MYSQDATADIR  | grep / |tr -d /' #  loops out the database name from the list of databases, Perform a backup operation for dbname in  $DBLIST     # mysqldump skip one  table    # -- warning: skipping the data of table  mysql.event. specify the --events option explicitly.    #  mysqldump --ignore-table=mysql.event    # http://serverfault.com/questions/ 376904/mysqldump-skip-one-table    # --routines, backing up stored procedures and functions     #  --events, skip Mysql.event table      # --triggers, backup Trigger     # --single-transaction, for InnoDB, Create a consistent snapshot in a single transaction by dumping all database tables, this option results in an automatic lock table, so no--lock-all-tables    # --flush-logs is required. Flush the log     # --ignore-table before the dump dump, ignoring a table,--ignore-table=database.table     # --MASTER-DATA=2&NBSP, if you enable the MySQL replication feature, you can add this option     #  Compress the dump out SQL statement to a time-named file     do ${mysqldump} --user=${mysqldbusername}  --password=${mysqldbpassword} --routines --events --triggers --single-transaction  --flush-logs --ignore-table=mysql.event --databases ${DBNAME} | gzip >  ${BACKDIR}/${DATEFORMATTYPE1}/${DBNAME}-backup-${DATEFORMATTYPE2}.sql.gz    #  Check the execution result, if the error code is 0, the output succeeds or the output fails     [ $? -eq 0 ] && echo   "${dbname} has been backuped  Successful " | |  echo  "${dbname} has been backuped failed"     #  wait 5s, Customizable     /bin/sleep 5done

Execution effect:

[Email protected] ~]#./backupmysqlbydate.sh MySQL has been backuped successfultest have been backuped successful[[email p Rotected] ~]# ls/var/backup/db/2015-07-27/mysql-backup-20150727195515.sql.gz test-backup-20150727195515.sql.gz[[ Email protected] ~]#

Tags: 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/1678653

Use of Linux shell scripts mysqldump backup MySQL database (detailed annotations)