mysql 备份 shell脚本_mysql shell备份脚本

# 非压缩版

#!/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