MySQL数据库备份

rehoni / 2021-12-15

mysqldump

语法：

默认不带参数的导出，导出文本内容大概如下：创建数据库判断语句-删除表-创建表-锁表-禁用索引-插入数据-启用索引-解锁表。

Usage: mysqldump [OPTIONS] database [tables]
OR     mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]
OR     mysqldump [OPTIONS] --all-databases [OPTIONS]

导出所有数据库

mysqldump -uroot -proot --all-databases >/tmp/all.sql

导出db1、db2两个数据库的所有数据

mysqldump -uroot -proot --databases db1 db2 >/tmp/user.sql

导出db1中的a1、a2表

注意导出指定表只能针对一个数据库进行导出，且导出的内容中和导出数据库也不一样，导出指定表的导出文本中没有创建数据库的判断语句，只有删除表-创建表-导入数据。

mysqldump -uroot -proot --databases db1 --tables a1 a2  >/tmp/db1.sql

条件导出，导出db1表a1中id=1的数据

mysqldump -uroot -proot --databases db1 --tables a1 --where='id=1'  >/tmp/a1.sql

只导出表结构不导出数据，–no-data

mysqldump -uroot -proot --no-data --databases db1 >/tmp/db1.sql

跨服务器导出导入数据

mysqldump --host=h1 -uroot -proot --databases db1 |mysql --host=h2 -uroot -proot db2

将h1服务器中的db1数据库的所有数据导入到h2中的db2数据库中，db2的数据库必须存在否则会报错

mysqldump --host=192.168.80.137 -uroot -proot -C --databases test |mysql --host=192.168.80.133 -uroot -proot test

导出存储过程和自定义函数–routines, -R

mysqldump  -uroot -p --host=localhost --all-databases --routines

压缩备份

压缩备份
mysqldump -uroot -p -P3306 -q -Q --set-gtid-purged=OFF --default-character-set=utf8 --hex-blob --skip-lock-tables --databases abc 2>/abc.err |gzip >/abc.sql.gz
还原
gunzip -c abc.sql.gz |mysql -uroot -p -vvv -P3306 --default-character-set=utf8 abc 1> abc.log 2>abc.err

脚本

可以通过配置定时任务，定时执行脚本来进行数据库备份。

注意定时查看 Linux 的密码是否过期，会影响到备份操作。

#!/bin/bash
# Name:clear_tomcat_log.sh
# This is a ShellScript For Auto DB Backup and Delete old Backup
#
# 参数说明：
# backupdir 保存路径的绝对地址
# 代码中 time=` date +%Y%m%d`也可以写为time=”$(date +”%Y%m%d”)” 找到当前日期时间格式
# 其中`符号是TAB键上面的符号，不是ENTER左边的’符号，还有date后要有一个空格。
#mysql_bin_dir：mysql的bin路径；
#dataname：数据库名；
#user：数据库用户名；
#password：用户密码；
#name：自定义备份文件前缀标识
# name：自定义备份文件前缀标识。
# -type f    表示查找普通类型的文件，f表示普通文件。
# -mtime +30   按照文件的更改时间来查找文件，+30表示文件更改时间距现在30天以前；如果是 -mmin +7 表示文件更改时间距现在7分钟以前。
# -exec rm -rf {} \;   表示执行一段shell命令，exec选项后面跟随着所要执行的命令或脚本，然后是一对儿{}，一个空格和一个\，最后是一个分号。
#数据库备份的位置
backupdir=/home/mysqlbak
time=` date +%Y%m%d%H%M%S`
#如果文件夹不存在则创建
if [ ! -d $backupdir ];
then
    mkdir -p $backupdir;
fi
#需要备份的数据库的连接的用户名和密码和数据库
#mysql_bin_dir/mysqldump -uuser -ppassword dataname | gzip > $backupdir/name$time.sql.gz
/usr/bin/mysqldump -uhis -pNRec-9000: -A | gzip > $backupdir/mysql_$time.sql.gz
#传输至备份服务器，如果保留本机则不需要此步骤
#scp $backupdir/mysql_$time.sql.gz 22.122.51.158:/data/bakup/159/
#find $backupdir -name "name*.sql.gz" -type f -mtime +30 -exec rm -rf {} \;
#保留10日
find $backupdir -name "mysql_*.sql.gz" -type f -mtime +10 -exec rm -rf {} \;