Use mysqldump to back up MySQL database using the LinuxShell script (detailed description)

Source: Internet
Author: User
Use the mysqldump command to back up the MySQL database script (without a comment version, suitable for production environments)

Use the mysqldump command to back up the MySQL database script (without a comment version, suitable for production environments)

Design and write considerations for this script:

Use the mysqldump command to back up the MySQL database script (without a comment version, suitable for production environments)

#! /Bin/bash
MYSQLDBUSERNAME = root
MYSQLDBPASSWORD = password
MYSQBASEDIR =/usr/local/mysql
MYSQL = $ MYSQBASEDIR/bin/mysql
MYSQLDUMP = $ MYSQBASEDIR/bin/mysqldump
BACKDIR =/var/backup/db
DATEFORMATTYPE1 = $ (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 5
Done

Use the mysqldump command to back up the MySQL database script (with a comment version, suitable for learning and testing)

#! /Bin/bash
# MYSQLDBUSERNAME is the username of the MySQL database, which can be customized
MYSQLDBUSERNAME = root
# MYSQLDBPASSWORD is the password of the MySQL database, which can be customized
MYSQLDBPASSWORD = password
# MYSQBASEDIR is the installation directory of the MySQL database, -- prefix = $ MYSQBASEDIR, which can be customized
MYSQBASEDIR =/usr/local/mysql
# MYSQL is the absolute path of the mysql Command, which can be customized
MYSQL = $ MYSQBASEDIR/bin/mysql
# MYSQLDUMP is the absolute path of the mysqldump command, which can be customized
MYSQLDUMP = $ MYSQBASEDIR/bin/mysqldump
# BACKDIR is the storage address of database backup. You can customize it to a remote address.
BACKDIR =/var/backup/db
# Obtain the current time, in the format of year-month-day, used to generate a directory name in this time format
DATEFORMATTYPE1 = $ (date + % Y-% m-% d)
# Obtain the current time in the format of year, month, day, hour, minute, and second. It is used to generate a file name in this time format.
DATEFORMATTYPE2 = $ (date + % Y % m % d % H % M % S)
# If the MYSQBASEDIR directory exists, set MYSQDATADIR to $ MYSQBASEDIR/data. For specific paths, change data to the path. Otherwise, set MYSQBASEDIR to/var/lib/mysql, customizable
[-D $ MYSQBASEDIR] & MYSQDATADIR = $ MYSQBASEDIR/data | MYSQDATADIR =/var/lib/mysql
# If the mysql command exists and can be executed, continue; otherwise, set MYSQL to mysql, mysql in the default path
[-X $ MYSQL] | MYSQL = mysql
# If the mysqldump command exists and can be executed, continue. Otherwise, set MYSQLDUMP to mysqldump. The default path is mysqldump.
[-X $ MYSQLDUMP] | MYSQLDUMP = mysqldump
# Create a backup directory if the Backup Directory does not exist
[-D $ {BACKDIR}] | mkdir-p $ {BACKDIR}
[-D $ {BACKDIR}/$ {DATEFORMATTYPE1}] | mkdir $ {BACKDIR}/$ {DATEFORMATTYPE1}
# Obtain the databases in MySQL and confirm them according to the directory name in mysqldatadir. You can customize TODO here.
DBLIST = 'LS-p $ MYSQDATADIR | grep/| tr-d /'
# Cyclically retrieve the database name from the Database List and perform the 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
#
# -- Routines: Backup stored procedures and functions
# -- Events: Skip the mysql. event table.
# -- Triggers: Backup trigger
# -- Single-transaction: For InnoDB, a consistent snapshot is created by dumping all database tables in a single transaction. This option will automatically lock the table, so -- lock-all-tables is not required.
# -- Flush-logs, refresh the log before dump
# -- Ignore-table: ignore a table. -- ignore-table = database. table
# -- Master-data = 2. If MySQL replication is enabled, you can add this option.
# Use gzip to compress the dump SQL statement to a file named after the time.
Do $ {MYSQLDUMP} -- user =$ {MYSQLDBUSERNAME} -- password =$ {MYSQLDBPASSWORD} -- routines -- events -- triggers -- single-transaction -- flush-logs -- ignore-table = mysql. event -- databases $ {DBNAME} | gzip >$ {BACKDIR}/$ {DATEFORMATTYPE1}/${dbname=-backup-1_1_dateformattype2). SQL .gz
# Check the execution result. If the error code is 0, the output is successful. Otherwise, the output fails.
[$? -Eq 0] & echo "$ {DBNAME} has been backuped successful" | echo "$ {DBNAME} has been backuped failed"
# Wait for 5 s, which can be customized
/Bin/sleep 5
Done

Execution result:

[Root @ htvm ~] #./Backupmysqlbydate. sh
Mysql has been backuped successful
Test has been backuped successful
[Root @ htvm ~] # Ls/var/backup/db/2015-07-27/
Mysql-backup-20150727195515. SQL .gz test-backup-20150727195515. SQL .gz
[Root @ htvm ~] #

-- End --

Use mysqldump to back up MariaDB

Use mysqldump to export a database

Quickly build a slave Database Based on mysqldump

Restore the backup set created by mysqldump

Use mysqldump command line tool to create logical backup

This article permanently updates the link address:

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: info-contact@alibabacloud.com 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.