MySQL数据库备份之mysqldump备份

mysqldump备份工具使用详解

备份策略全量备份 + binlogs

命令详解

  • 语法格式:
mysqldump [OPTIONS] database [tables]

OR mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]

OR mysqldump [OPTIONS] --all-databases [OPTIONS]
  • 实例详解:
# 表级别备份;不会自动创建数据库

$ mysqldump mydb

# 库级别备份,自动创建数据库 

$ mysqldump --databases mydb
  • 选项详解
-x, --lock-all-tables 锁定'所有库的所有表',读锁;

-l, --lock-tables 锁定'指定库所有表';

-R, --routines 备份存储过程和存储函数;

-E, --events 备份事件调度器

-F,--flush-logs 锁定表完成后,即进行日志刷新操作,让日志滚动;

--triggers 备份触发器

--master-data[=#] :记录备份开始时 binlog中

  1:记录为CHANGE MASTER TO语句,此语句不被注释;

  2:记录为CHANGE MASTER TO语句,此语句被注释;


'InnoDB存储引擎:支持温备和热备;'

   --single-transaction:'创建一个事务,基于此快照执行备份'

全量备份一次整个数据库

  • 开启二进制日志
$ vim /etc/my.conf

[server]

log_bin=mysql-bin

$ systemctl restart mysqld
  • 开始备份数据库
# 使用mysqldump备份整个mysql数据库

$ mysqldump -uroot -p'password'  -E -R --triggers --master-data=2 -F -l --single-transaction --all-databases > /tmp/all-fullback-$(date +%F).sql


# 登陆至MySQL修改一些数据

$ mysql -uroot -p'password'

> SHOW MASTER LOGS;

+------------------+-----------+

| Log_name | File_size |

+------------------+-----------+

| mysql-bin.000001 | 30352 |

| mysql-bin.000002 | 1038814 |

| mysql-bin.000003 | 9235 |

| mysql-bin.000004 | 696 |

| mysql-bin.000005 | 245 |

+------------------+-----------+

> use hellodb;

> DELETE FROM students WHERE StuID=1;

> DELETE FROM students WHERE StuID=5;

> DELETE FROM students WHERE StuID=10;

> DELETE FROM students WHERE StuID=15;

> DELETE FROM students WHERE StuID=20;


# 拷贝全量备份后,未备份的二进制日志至/tmp目录中

$ cp /var/lib/mysql/mysql-bin.000005 /tmp/


# 模拟数据库崩溃情况,删除数据库数据目录下的所有文件


$ cp -rf /var/lib/mysql/  /var/lib/mysql_cp/ #先备份一份
$ rm -rf /var/lib/mysql/*

使用备份恢复数据库

  • 重启数据库
$ systemctl stop mysqld

$ systemctl start mysqld
  • 登陆数据库进行恢复
$ mysql -uroot -p'password'

# 关闭会话级别的二进制日志,因为我们需要执行恢复sql脚本,不希望二进制记录此信息

> SET @@session.sql_log_bin=OFF;

# 在MySQL中执行SQL脚本

> \. /tmp/alldb_fullbackup-2017-06-20.sql
  • 使用二进制日志恢复未备份的信息
$ cd /tmp

$ mysqlbinlog mysql-bin.000005 | mysql -uroot -p'password'
  • 开启二进制日志记录
SET @@session.sql_log_bin=ON;

 

写个shell脚本backup.sh自动备份:

#!/bin/bash

# MYSQLDBUSERNAME是MySQL数据库的用户名,可自定义
MYSQLDBUSERNAME=root

# MYSQLDBPASSWORD是MySQL数据库的密码,可自定义
MYSQLDBPASSWORD="12315Smm"

# MYSQLHOST是MySQL数据库的地址
MYSQLHOST="127.0.0.1"

# MYSQLPORT是MySQL数据库端口
MYSQLPORT="3306"

# 备份MySQL中有哪些数据库,数组用"空格"符号分割开,语法格式如下
DBLIST=(
 "live"
 "luosidao"
 "xihu"
 "mijiahezi"
)

# MYSQBASEDIR是MySQL数据库的安装父目录,--prefix=$MYSQBASEDIR,可自定义
MYSQBASEDIR=/usr

# MYSQL是mysql命令的绝对路径,可自定义
MYSQL=$MYSQBASEDIR/bin/mysql

# MYSQLDUMP是mysqldump命令的绝对路径,可自定义
MYSQLDUMP=$MYSQBASEDIR/bin/mysqldump

# BACKDIR是数据库备份的存放地址,可以自定义修改成远程地址
BACKDIR=/Users/shaozeming/tmp/backup/mysqldb

# 保留多少次数据,自定义
BACKNUM=3



#==================下面不用修改-ShaoZeMing=======================================
#备份文件夹前缀
DIRPRE="backup_"
# 获取当前时间,格式为:年月日,用于生成以这种时间格式的目录名称
DATEFORMATTYPE1=${DIRPRE}$(date +%Y%m%d)
# 获取当前时间,格式为:年月日时分秒,用于生成以这种时间格式的文件名称
DATEFORMATTYPE2=$(date +%Y%m%d%H%M%S)

# 如果mysql命令存在并可执行,则继续,否则将MYSQL设定为mysql,默认路径下的mysql
[ -x $MYSQL ] || MYSQL=mysql
# 如果mysqldump命令存在并可执行,则继续,否则将MYSQLDUMP设定为mysqldump,默认路径下的mysqldump
[ -x $MYSQLDUMP ] || MYSQLDUMP=mysqldump
# 如果不存在备份目录则创建这个目录
[ -d ${BACKDIR} ] || mkdir -p ${BACKDIR}
[ -d ${BACKDIR}/${DATEFORMATTYPE1} ] || mkdir ${BACKDIR}/${DATEFORMATTYPE1}

# 从数据库列表中循环取出数据库名称,执行备份操作
for DBNAME in ${DBLIST[@]}
    # mysqldump skip one table
    # -- Warning: Skipping the data of table mysql.event. Specify the --events option explicitly.
    # mysqldump --ignore-table=mysql.event
    # http://serverfault.com/questions/376904/mysqldump-skip-one-table
    # --routines,备份存储过程和函数
    # --events,跳过mysql.event表
    # --triggers,备份触发器
    # --single-transaction,针对InnoDB,在单次事务中通过转储所有数据库表创建一个一致性的快照,此选项会导致自动锁表,因此不需要--lock-all-tables
    # --flush-logs,在dump转储前刷新日志
    # --ignore-table,忽略某个表,--ignore-table=database.table
    # --master-data=2 ,如果启用MySQL复制功能,则可以添加这个选项
    # 将dump出的sql语句用gzip压缩到一个以时间命名的文件
    do ${MYSQLDUMP} -h${MYSQLHOST} -P${MYSQLPORT}  -u${MYSQLDBUSERNAME} -p${MYSQLDBPASSWORD} --routines --events --triggers --single-transaction --flush-logs --ignore-table=mysql.event --databases ${DBNAME} | gzip > ${BACKDIR}/${DATEFORMATTYPE1}/${DBNAME}-backup-${DATEFORMATTYPE2}.sql.gz
    # 检查执行结果,如果错误代码为0则输出成功,否则输出失败
    [ $? -eq 0 ] && echo "${DBNAME} has been backuped successful" || echo "${DBNAME} has been backuped failed"
    # 等待5s,可自定义
    /bin/sleep 2
done


#备份整个mysql数据库命令,不需要可以注释
${MYSQLDUMP}  -h${MYSQLHOST} -P${MYSQLPORT}  -u${MYSQLDBUSERNAME} -p${MYSQLDBPASSWORD}  -E -R --triggers --master-data=2  -F -l --single-transaction -A   | gzip > ${BACKDIR}/${DATEFORMATTYPE1}/all-fullback-${DATEFORMATTYPE2}.sql.gz
echo "alldatabases has been backuped successful"


#根据文件个数,删除超过的多余的,删除文件更改需谨慎===================
#ls -l:目的是按行列出文件
#grep "act-*":筛选出包含act-为开头的文件
#wc -l:统计行数(也就相当于统计有多少个这样的文件)
#-gt :大于
#ls -rt:把文件按从旧到新的顺序排列
#head -n1:选取排列的头两个文件,这个数字2可以任意的改变
#rm -r:移除目录
 cd ${BACKDIR}
 pwd
 if [ $(ls -l | grep "${DIRPRE}*" | wc -l) -gt ${BACKNUM} ]
 then
     echo "file > ${BACKNUM}"
     rm -r $(ls -rt | head -n1)
 fi

 

  • 修改文件属性,使其执行(主要是检测shell脚本能不能正常运行)

 

chmod  +x  ./backup.sh
./backup

 

分   时   日   月   周

*      *     *      *      *   命令

例如 : 0  2  *   *  *  sh dir_path/backup.sh  >  /dev/null   2>&1   #每天2点执行一次。
  • 常见crontab错误类型:

如果在日志文件中执行一条语句出现 : No  MTA  installed,discarding  output

则crontab执行脚本时,不会直接输出错误的信息,而是会以邮件的形式发送到邮箱中,如果没有安装邮件服务器,就会报错误。

这时候可以在每条定时脚本后面加入 : > /dev/null   2>&1