MySQL自动备份脚本

MySQL自动备份脚本

mysqldump命令将数据库中的数据备份成一个文本文件，表的结构和数据将存储在生成的文本文件中

基本语法

备份一个数据库下的多个表

# 备份一个数据库下的多个表

# username表示用户名
# pwd表示密码
# localhost为数据库地址
# dbname表示数据库名称
# table1和table2参数表示需要备份的表的名称，为空则整个数据库备份
mysqldump -u username -p pwd -h localhost --default-character-set=utf8 dbname table1 table2 > BackupName.sql

# 排除某些表不备份
mysqldump -u username -p pwd -h localhost --default-character-set=utf8 --database dbname -- --lock-all-tables --ignore-table=table1  > BackupName.sql
# 默认lock-tables会将所要备份的表进行锁表LOCK TABLES，所有表导出完成后，才会进行解锁UNLOCK TABLES
# lock-all-tables 在开始时会执行FLUSH TABLES WITH READ LOCK，导出完成，退出会话时，自动释放锁
# single-transaction  在开始时执行SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ，然后开启事务，并UNLOCK TABLES，且在每张表进行导出前，都会进行SAVEPOINT sp设置保存点，导出后执行ROLLBACK TO SAVEPOINT sp，回到保存点，保证数据一致性

备份多个数据库

# 备份多个数据库使用--databases

# username表示用户名
# pwd表示密码
# localhost为数据库地址
# dbname1 dbname2表示数据库名称
# table1和table2参数表示需要备份的表的名称，为空则整个数据库备份
mysqldump -u username -p pwd -h localhost --default-character-set=utf8 --databases dbname1 dbname2  > BackupName.sql

备份所有数据库使用

# 备份所有数据库使用--all-databases

# username表示用户名
# pwd表示密码
# localhost为数据库地址
# dbname1 dbname2表示数据库名称
# table1和table2参数表示需要备份的表的名称，为空则整个数据库备份
mysqldump -u username -p pwd -h localhost --default-character-set=utf8 --all-databases  > BackupName.sql

自动备份脚本

#!/bin/bash
# Mysql vars
DB_USER="root"
DB_PASS='123456'
MYSQLDUMP_OPT="--opt --events --ignore-table=mysql.events --default-character-set=utf8"

# Others vars
DATE=`date +%Y%m%d_%H%M`
BIN_DIR="/usr/local/mysql/bin/"
BACK_DIR="/opt/backup_database/20190830"

# GET DB_NAMES
DB_NAMES=`${BIN_DIR}/mysql -u${DB_USER} -p${DB_PASS} -A -e "show databases;" | egrep -v "Database|schema"`

# Todo mysqldump
[ -d ${BACK_DIR} ] || mkdir -p ${BACK_DIR}
# mysqldump all databases
${BIN_DIR}/mysqldump ${MYSQLDUMP_OPT} -u${DB_USER} -p${DB_PASS} --all-databases | gzip > ${BACK_DIR}/all_databases_${DATE}.sql.gz
for DB_NAME in ${DB_NAMES}; do
    sleep 1
    DATE=`date +%Y%m%d_%H%M%S`
    ${BIN_DIR}/mysqldump ${MYSQLDUMP_OPT} -u${DB_USER} -p${DB_PASS} ${DB_NAME} > ${BACK_DIR}/${DB_NAME}_${DATE}.sql
    # gzip backup
    # ${BIN_DIR}/mysqldump ${MYSQLDUMP_OPT} -u${DB_USER} -p${DB_PASS} ${DB_NAME} | gzip > ${BACK_DIR}/${DB_NAME}_${DATE}.sql.gz
    [ $? == 0 ] && echo "${DATE}:mysqldump ${DB_NAME} success." >>${BACK_DIR}/../mysqldump_backup.log || echo "${DATE}:mysqldump ${DB_NAME} err,please check." >>${BACK_DIR}/../mysqldump_backup.log
done
echo "==========All databases backups over==========" >>${BACK_DIR}/mysqldump_backup.log

数据还原

将备份出来的数据还原到某个数据库中

mysql -u root -p 123456 dbname < backup.sql

也可以登入数据之后进行

source 需要导入的sql文件位置

https://zhhll.icu/2021/数据库/关系型数据库/MySQL/运维/1.MySQL备份/