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

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: