Bash script for exporting MySQL database mode and Data

ArticleDirectory

• Bash script file: export_db.sh

Bash script file: export_db.sh

#! /Bin/sh

# Export_db.sh
# Exporting database modes and Data
# Codingstandards@gmail.com
# V1: 2010-05-23
# V2: 2011-10-08/09

# Command instructions
# Read Database Name and table name from standard input
# Usage: export_db.sh
# Usage: export_db.sh-
# Read the Database Name and table name from the specified file
# Usage: export_db.sh db_and_tables.txt
# Use here string to specify the Database Name and table name
# Usage: export_db.sh <"dbname"
# Usage: export_db.sh <"dbname tablename"

# Input format description
# It consists of one or more rows. It can be one of the following:
# (1) Empty rows: ignored
# (2) Note: rows starting with # are ignored.
# (3) db_name: specifies only the database name. All Tables in the database will be exported.
# (4) db_name table_name: Specifies the Database Name and table name. Only the table is exported.

# Database connection Parameters
# Todo: Modify the following parameters to adapt to database settings.
Dbopts = "-pxxx -- default-character-set = GBK"

# Save to directory
# Todo: the directory where the script is located or another directory.
Saveto = $ (dirname "$0 ")

# If you want to run the command in crontab, add the following line to ensure that the environment variables are correctly set.
./Etc/profile

# Go To The save directory
Cd "$ saveto" | Exit 1

# Export directory, in the format of dbyyyymmddhhmmss, such as db20111008100923
Dir = dB $ (date + % Y % m % d % H % m % s)
Mkdir-P "$ dir"

# Print the current time
Now ()
{
Date "+ % F % T. % N"
}

# Execute SQL statements and output execution results
# Usage: db_exec <dB> <SQL>
Db_exec ()
{
MySQL $ dbopts-s "$1"-e "$2"
}

# Export the database to a file, save the database in different directories, and save the data table in different files
# Usage: db_dump <dB> <Table>
Db_dump ()
{
# Echo-n "$1 $2"
# Db_exec "$1" "select count (*) from $2"
Local COUNT = $ (db_exec "$1" "select count (*) from $2 ")
Printf "$ (now) %-32 S % 16s \ n" "$1. $2" "$ count"
Mkdir-P "$ DIR/$1"
Mysqldump $ dbopts -- quick -- add-drop-table "$1" "$2"> "$ DIR/$1/$2. SQL"
Echo "$ (now) OK ."
}

# Read the name of the database and table to be exported from the file specified by the first parameter of the command line, and then export
# Cat "$ {1: -export_db.txt}" |
# Cat "$1" |
Cat $1 |
While read db_name table_name;
Do
# Echo "$ db_name $ table_name"
If ["$ db_name"-a "$ {db_name: 0: 1 }"! = "#"]; Then # ignore empty rows and comments
If [! "$ Table_name"]; then # If the table name is not specified, the whole database is exported.
Db_exec "$ db_name" "show tables" |
While read table_name;
Do
Db_dump "$ db_name" "$ table_name ";
Done
Else # export tables in the specified database
Db_dump "$ db_name" "$ table_name"
Fi
Fi
# Done &> $ DIR/export_db.log
Done 2> & 1 | tee $ DIR/export_db.log

# If no table is exported, delete the $ dir directory.
If [! "$ (Ls $ DIR/*. SQL 2>/dev/null)"]; then
Echo "$ (now) No tables dumped! "
Rm-RF $ dir
Exit 2
Fi

# Compressing Exported Files
Tar zcf nvidir.tar.gz $ dir
Echo "$ (now) returns dir.tar.gz generated." | tee-A $ DIR/export_db.log

# End.