mysql备份

1.前言

数据库备份是最重要的事情，当误删数据库时可以使用备份来恢复数据库数据，本文来讲解一下使用mysqldump命令备份数据库

2.mysqldump常用命令参数

-A --all-databases 备份所有数据库（创建库命令一同写入）

-B --databases 备份指定多个数据库（创建库命令一同写入）

-d 只备份表结构

-t 只备份表数据

-T --tab 将表结构和表数据分开存储，数据为纯文本格式，而非sql语句，此参数后跟路径

-F 刷新binlog日志文件，生成一个新的binlog日志文件

-x --lock-all-tables 备份时锁定所有表，不能与--single-transaction共用，会产生冲突

--lock-tables 锁定一个库里的所有表

--log-error   将报错输出到指定文件中，备份没报错则没有输出

--master-data=[1|2] 参数为1时，mysqldump出来的文件就会包括这个语句，后面紧接着就是file和position的记录，参数为2时chang master to也会写到dump文件里面去，一般使用2

--single-transaction 不锁表备份，但会在备份开始时生成一个快照，后面写入的数据对快照里的数据没有任何影响，备份出来的数据还是备份开始时的数据,适用于innodb引擎

注：在配置了gtid的主从架构中，导出数据时必须加以下配置，不然报错

--set-gtid-purged=OFF  导出数据库时忽略gtid

3.mysqldump备份操作

3.1备份单个表

mysql -u用户名 -p密码 库名 表名 > /opt/test.sql

3.2备份单个数据库

mysqldump -u用户名 -p密码 库名>/opt/test.sql

3.3备份多个数据库

mysqldump -u用户名 -p密码 -B 库名 库名 库名 > /opt/test.sql

3.4备份所有数据库

mysqldump -u用户名 -p密码 --all-databases --single-transaction --master-data=2 >/opt/full.sql

3.5备份所有数据库且压缩

mysqldump -u用户名 -p密码 --all-databases --single-transaction --master-data=2 | gzip > /opt/full.sql.gz

4.通过备份脚本与cron定时任务实现在每天的00点全备数据库

4.1备份脚本内容如下

vi /opt/mysqlbackup.sh

#!/bin/bash
date_time=`date "+%Y%m%d"`
DATE_TIME=`date "+%Y%m%d %H:%M:%S"`
echo "begin time $DATE_TIME" >> /opt/mysql/db.log

rm -rf /opt/mysql/backup_error.log

mysqldump -uGreatTeam -p'Tapd!%!^' -F --single-transaction --log-error=/opt/mysql/backup_error.log  --all-databases --master-data=2 > /opt/mysql/full_"$date_time"000000.sql
if [ -s /opt/mysql/backup_error.log ];then  #判断备份时是否有输出报错，没有报错则文件为空，有报错则不为空，判断文件是否输出报错从而判断备份是否成功
   echo "end time $DATE_TIME" >> /opt/mysql/db.log
   echo "db backup lose" >> /opt/mysql/db.log
else
   echo "end time $DATE_TIME" >> /opt/mysql/db.log
   echo "db backup success" >> /opt/mysql/db.log
fi

cp /opt/mysql/full_"$date_time"000000.sql /share/backup/mysql

4.2配置cron配置文件内容如下

vi /etc/crontab

00 00 * * * sh /opt/mysqlbackup.sh

crontab /etc/crontab

crontab -l