mysql数据库的备份与恢复

一，前景准备

1、安装MySQL数据库用以测试

这里准备的是8.0版本的MySQL数据库

下载地址：MySQL :: Download MySQL Community Server (Archived Versions)

为了方便实验，执行脚本进行安装：

# /bin/bash
# 
# 官方下载地址: https://downloads.mysql.com/archives/community/
SRC_DIR=`pwd`
#MYSQL='mysql-5.7.38-linux-glibc2.12-x86_64.tar.gz'
URL=http://mirrors.163.com/mysql/Downloads/MySQL-5.7
MYSQL='mysql-8.0.29-linux-glibc2.12-x86_64.tar.xz'
#URL=http://mirrors.163.com/mysql/Downloads/MySQL-8.0COLOR='echo -e \E[01;31m'
END='\E[0m'
MYSQL_ROOT_PASSWORD=123456check(){if [ $UID -ne 0 ];thenecho "当前用户非root,安装失败" falseexit 1
fi
cd $SRC_DIR
yum -y -q install wget libaio numactl-libs ncurses*
#wget $URL/$MYSQL
if [ ! -e $MYSQL ];then$COLOR"缺少${MYSQL}文件"$END$COLOR"请将相关的软件放在${SRC_DIR}目录下"$ENDexit
elif [ -e /usr/local/mysql ];thenecho "数据库已存在，安装失败" falseexit
elsereturn
fi
}install_mysql(){$COLOR"开始安装MySQL数据库..."$ENDyum -y -q install libaio numactl-libs ncurses*cd $SRC_DIRtar xf $MYSQL -C /usr/localMYSQL_DIR=`echo $MYSQL | sed -nr 's/^(.*[0-9]).*/\1/p'`ln -s /usr/local/$MYSQL_DIR /usr/local/mysqlchown -R root.root /usr/local/mysqlid mysql &> /dev/null || { useradd -s /sbin/nologin -r mysql ;echo "创建mysql用户"; }echo 'PATH=/usr/local/mysql/bin/:$PATH' > /etc/profile.d/mysql.sh. /etc/profile.d/mysql.shln -s /usr/local/mysql/bin/* /usr/bincat > /etc/my.cnf << EOF
[mysqld]
server-id=1
log-bin
socket=/data/mysql/mysql.sock
datadir=/data/mysql
log-error=/data/mysql/mysql.log
pid-file=/tmp/mysql.sock[client]
socket=/data/mysql/mysql.sock
EOF[ -d /data ] || mkdir /datamysqld --initialize --user=mysql --datadir=/data/mysqlcp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqldchkconfig --add mysqldchkconfig mysqld onservice mysqld start[ $? -ne 0 ] && { $COLOR"数据库启动失败,退出!"$END;exit; }MYSQL_OLDPASSWORD=`awk '/A temporary password/{print $NF}' /data/mysql/mysql.log`mysqladmin -uroot -p$MYSQL_OLDPASSWORD password $MYSQL_ROOT_PASSWORD &> /dev/nullecho "数据库安装完成"
}check
install_mysql

2、创建测试使用的库和表

进入MySQL数据库中，进行创建对应的库表，密码在脚本中有定义哈!

mysql -u root -p

创建数据库test

create database test;#进入库中use test#将准备好的数据导入到test库中 ，需指定路径\. /root/a.sql

查看test库中导入的表数据

show tables;

二、冷备份（物理备份）

1、物理备份       

        物理备份，直接使用cp、tar 等复制归档工具，与存储引擎有关，占用较多空间，备份速度快，恢复快。

        备份前，需要将数据库进行停服。

service mysqld stop

进入数据存储目录，将数据文件直接进行复制备份

cd /datacp -rp mysql/ backup

当数据库中的数据被错误操作时

#删除表students
drop tables students;

2、恢复数据

将数据库进行停服，并将原文件进行删除，将备份文件替换原文件的位置

启动服务，并进行检测

可以看到数据恢复正常

三、热备份。读写操作均可执行

使用mysqldump ，逻辑备份工具，适用所有存储引擎，温备，支持完全或部分备份

常见选项：

-A ，--all-databases 备份所有数据，含create database

-B ，--databases db_name  指定备份的数据库，包括create database语句

-E ，--events  备份相关的所有event scheduler

-R ，--routines  备份所有存储过程和自定义函数

  --triggers  ：备份表相关触发器，默认启用，使用 --skip-triggers，不备份触发器

  --default-character-ser=utf8 指定字符集

  --master-data [=#] :此选项须启用二进制日志   （现在使用 --source-data=#）

           1：所备份的数据之前加一条记录为change master to语句，非注释，不指定#，默认为1，（即备份又同步）

           2：记录为注释的change master to 语句，此选项会自动关闭 --lock-tables功能，自动打开 -x | --lock-all-tables功能（除非开启--single-transaction）（只支持热备，为热备而准备的）

-F ， --flush-logs ：备份前滚动日志，锁定表完成后，执行flush logs 命令，生成新的二进制日志文件，配合-A 或 -B选项时，会导致刷新多次数据库，建议在同一时刻执行转储和日志刷新，可通过和--single-transaction 或-x，--master-data一起使用实现，此时只刷新一次日志

   --compact 去掉注释，适合调试，生产不使用

-d ， --no-data 只备份表结构（表头，无数据）

-t , --no-create-info  只备份数据不备份create table（无表头）

-n ，--no-create-db 不备份create database，可被-A或-B覆盖

   --flush-privileges 备份mysql或相关时需要使用

-f ， --force 忽略SQL错误，继续执行

   --hex-blob 使用十六进制符号转储二进制列，当包括binary，varbinary，blob，bit的数据类型的列时使用，避免乱码

-q , --quick 不缓存查询，直接输出，加快备份速度

1、数据备份

对数据库mysql 和 test 进行备份

mysqldump -u root -p -B mysql test -F --single-transaction --source-data=2 > /data/all.sql

使用 --master-data ，须打开二进制日志才行

须先开启记录日志，使  sql_log_bin = on ,一般默认是 on

其也需要确保启用二进制日志功能，使得 log_bin = on

查看使用中的二进制日志文件

show master status;

进入二进制日志文件中进行查看

mysqlbinlog chen2-bin.000005

进入test库中进行删除teachers表进行测试，mysql库中db表删除

2、恢复数据

（1）将备份的数据导入到mysql数据库中

mysql -u root -p < /data/all.sql

进入数据库中进行查看

（2）、在数据库中直接插入备份数据也可以

查看二进制日志节点状态，可见，恢复数据时，也是会记录到二进制日志中的，但这些数据一般都为无用的数据，已做过备份的

因此当数据恢复时，一般都将二进制日志进行临时关闭

show master status;

四、通过二进制日志进行恢复错误操作的数据

1、重置二进制日志

先对二进制日志进行刷新，重启，方便观察

reset master;

在表中插入若干数据

insert into teachers (tid,name) values (5,'simayi');

insert into students (stuid,name) values (26,'chen'),(27,'xiaoming');

2、删除或修改数据

然后对插入的数据进行删除修改

删除students 表中的一行数据

delete from students where stuid=26;

3、将二进制文件导出为SQL脚本

查看二进制日志内容

show binlog events;

因为在SQL语句中，无法直接进行处理，因此，需要先对日志导出为SQL脚本

mysqlbinlog mysql/chen2-bin.000001 > ./binlog-000001.sql

4、修改导出的SQL脚本，将错误的命令删除

上面是使用 drop 命令删除的 teachers 表，找到该项，进行删除

使用的delete命令删除的students 表中的内容，找到该项，进行删除

5、临时关闭二进制日志，进行数据恢复

临时关闭二进制日志

删除test 库

先将备份的test和mysql 数据库进行导入到MySQL服务中，

查看表的数据

全面备份后，增加的数据并未恢复

借助二进制节点进行进一步的恢复数据，并进行查看数据是否恢复

然后将二进制日志打开；