# 非压缩版
#!/bin/bash

MYSQL_USER=root
MYSQL_PASS=123456
MYSQL_HOST=127.0.0.1
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS} -h${MYSQL_HOST}"
#
# Collect all database names except for
# mysql, information_schema, and performance_schema
#
SQL="SELECT schema_name FROM information_schema.schemata WHERE schema_name NOT IN"
SQL="${SQL} ('mysql','information_schema','performance_schema')"

#   -A, --no-auto-rehash
#                       No automatic rehashing. One has to use 'rehash' to get
#                       table and field completion. This gives a quicker start of
#                       mysql and disables rehashing on reconnect.

#    -N, --skip-column-names
#                        Don't write column names in results.

#   -e, --execute=name  Execute command and quit. (Disables --force and history
#                       file.)

DBLISTFILE=/tmp/DatabasesToDump.txt
mysql ${MYSQL_CONN} -ANe"${SQL}" >${DBLISTFILE}

DBLIST=""
for DB in $(cat ${DBLISTFILE}); do DBLIST="${DBLIST} ${DB}"; done

MYSQLDUMP_OPTIONS="--routines --triggers --single-transaction"
mysqldump ${MYSQL_CONN} ${MYSQLDUMP_OPTIONS} --databases ${DBLIST} >all-dbs.sql

压缩版

#!/bin/bash

MYSQL_USER=root
MYSQL_PASS=123456
MYSQL_HOST=127.0.0.1
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS} -h${MYSQL_HOST}"
#
# Collect all database names except for
# mysql, information_schema, and performance_schema
#
SQL="SELECT schema_name FROM information_schema.schemata WHERE schema_name NOT IN"
SQL="${SQL} ('mysql','information_schema','performance_schema')"

#   -A, --no-auto-rehash
#                       No automatic rehashing. One has to use 'rehash' to get
#                       table and field completion. This gives a quicker start of
#                       mysql and disables rehashing on reconnect.

#    -N, --skip-column-names
#                        Don't write column names in results.

#   -e, --execute=name  Execute command and quit. (Disables --force and history
#                       file.)

DBLISTFILE=/tmp/DatabasesToDump.txt
mysql ${MYSQL_CONN} -ANe"${SQL}" >${DBLISTFILE}

DBLIST=""
for DB in $(cat ${DBLISTFILE}); do DBLIST="${DBLIST} ${DB}"; done

MYSQLDUMP_OPTIONS="--routines --triggers --single-transaction"
mysqldump ${MYSQL_CONN} ${MYSQLDUMP_OPTIONS} --databases ${DBLIST} |gzip -9 >all-dbs.sql.gz

压缩版还原命令

zcat your_db_name.sql.gz | mysql -u username -p your_db_name