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

Source: Internet
Author: User
Tags mysql backup

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    # 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] ~]#./ 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


This article is from "Communication, My Favorites" blog, please make sure to keep this source

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.