MySQL的数据备份

MySQL数据完全备份

数据备份目的？

数据丢失或者损坏，可以使用备份数据恢复

数据备份策略

• 完全备份

  备份所有数据（一张表，一个库、一台服务器）

• 增量备份

  备份上次备份后，所有新产生的数据

• 差异备份

  备份完全备份后，所有新产生的数据

数据备份方式：

• 物理备份

  • 冷备 cp、tar

• 逻辑备份

  • mysqldump

• mysql

物理备份及恢复

备份操作

cp -r /var/lib/mysql 备份目录/mysql.bak

tar -zcvf /root/mysql.tar.gz /var/lib/mysql/*

恢复操作

cp -r 备份目录/mysql.bak /var/lib/mysql

tar -zxvf /root/mysql.tar.gz -C /var/lib/mysql/*

chown -R mysql:mysql /var/lib/mysql

例：

把数据库服务器的数据库文件复制到测试主机上

#192.168.4.3（数据库服务器）
cp -r /var/lib/mysql   /root/mysql.bak
scp -r /root/mysql.bak root@192.168.4.100:/root 
#192.68.4.100(测试服务器)
ss -nultp | grep 3306
systemctl stop mysqld  #数据库服务如果开启，请关闭
rm -rf /var/lib/mysql
cp -r /root/mysql.bak/  /var/lib/mysql
chown -R mysql:mysql  /var/lib/mysql
systemctl start mysqld
#登录MySQL查看，数据是否和数据库服务器的数据一致

逻辑备份：

使用软件自带的备份程序（备份命令）对数据做备份，在执行备份命令时，会根据已有的数据生成对应的命令，把命令存放到指定的备份文件里，恢复时，执行备份文件里的命令恢复数据

备份

mysqldump命令

命令格式：

mysqldump -uroot -p 密码 库名 > 目录/xxx.sql

恢复

mysql 命令

命令格式：

mysq -uroot -p 密码 库名 < 目录/xxx.sql

备份时库名的表示方式

• –all-databases 或 -A #所有库
• 数据库名 #单个库
• 数据库名 表名 #单张表
• -B 数据库1 数据库2 #多个库

注意事项：

无论时备份还是恢复都需要验证用户权限

#先建立一个目录来存放备份数据
mkdir /mybak
mysqldump -uroot -p123456 -A >/mybak/alldb.sql  #备份整个数据库
mysqldump -uroot -p123456 db1 >/mybak/db1.sql   #备份db1
mysqldump -uroot -p123456 db3 user >/mybak/db3_user.sql  #备份单个表
mysqldump -uroot -p123456 -B db3 db1 >/mybak/twodb.sql #备份多个库#测试，进入数据库删除库中的所有数据进行测试
#恢复单个库，单个表需要提前需要建好对应的库或者表
mysql -u root -p123456
drop database db1;
create database db1;  #恢复库数据需要提前建立好数据库
\q
mysql -u root -p123456 db1 </mybak/db1.sql
mysql -u root -p123456
use db1;
show table;
#恢复多个库，不需要提前建立相应的库，因为多个库的恢复，备份文件中有建立库和表的sql
mysql -u root -p123456 </mybak/alldb.sql

MySQL数据增量备份

使用MySQL服务的binlog日志实现数据的增量备份与恢复

binlog日志的使用

binlog日志介绍

什么是binlog日志

• 也称作 二进制日志
• MySQL服务日志文件的一种，默认没有启用
• 记录除查询之外的所有SQL命令
• 可用于数据备份和恢复
• 配置mysql主从同步的必要条件

启用binlog日志

配置项	用途
server id=数字	指定id值（1-255）
log-bin=[目录名/文件名]	启用binlog日志
max_binlog_size=数值m	指定日志文件容量，默认1G

vim /etc/my.cnf
[mysqld]
server_id=3
log_bin    #默认在 /var/lib/mysql,默认命名主机名-bin。随机六位数systemctl restart mysqld
#查看日志
mysql> show master status;
ls /var/lib/mysql#自定义目录，这个目录需要提前建立好，并且mysql用户要对这个文件有写入权限
mkdir /mylog
vim /etc/my.cnf
[mysqld]
server_id=3   
log_bin=/mylog/bxl chown mysql /mylog
systemctl restart mysqld
#查看日志
mysql> show master status;  #查看日志名称是否发生变化

手动创建新的binlog日志文件

binlog相关文件

• 主机名-bin.index #索引文件
• 主机名-bin.000001 #第一个二进制文件
• 主机名-bin.000002 #第二个二进制文件

每次重启服务，就会生成一个新的二进制文件

systemctl restart mysqld
mysql> flush logs;  #刷新日志文件
mysql -uroot -p123456 -e "flush logs"  #在命令行执行SQL语句
mysql -uroot -p123456 -e "show master status"
mysqldump -uroot -p123456 --flush-logs db3 > /mybak/db3.sql  #在执行备份时刷新日志文件

以上命令都会刷新二进制文件

删除已有的binlog日志

清理日志

• 删除指定编号之前的binlog日志文件

  mysql> purge master logs to “binlog文件名”;

• 删除所有的binlog日志，重建新日志

  mysql> reset master;

#示例：删除4之前的日志文件
mysql> system ls /mylog  #查看当前有哪些日志文件
mysql> purge master logs to "bxl.000004";
mysql> system ls /mylog
#删除所有的binlog日志，重建新日志
mysql> reset master;
mysql> system ls /mylog
mysql> show master status;

查看binlog日志内容

mysql> show master status;  #在没有进行对数据的改变时，position偏移量不会发生改变，偏移量发生改变说明数据发生了变化
insert into db3.user(name,uid,gid) values("x",1001,1001);
insert into db3.user(name,uid,gid) values("y",1002,1002);
insert into db3.user(name,uid,gid) values("z",1003,1003);
insert into db3.user(name,uid,gid) values("aa",1004,1004);
insert into db3.user(name,uid,gid) values("bb",1005,1005);
show master status;  #偏移量发生了改变
\q  #退出数据库
mysqlbinlog /mylog/bxl.000001   #查看二进制文件

使用binlog日志恢复数据

命令格式：

mysqlbinlog /路径/日志文件 | mysql -uroot -p123546

例：

#在test主机上进行恢复测试，在上面使用过test主机进行过物理备份，所以他两的数据时一致的，现在进行增量恢复
#192.168.4.3（主服务器）
scp /mylog/bxl.000001 root@192.168.4.100:/root
#192.168.4.100(test服务器)
mysql -uroot -p123456 -e "select count(*) from db3.user"
mysqlbinlog /root/bxl.000001 |mysql -uroot -p123456
mysql -uroot -p123456 -e "select count(*) from db3.user"
mysql -uroot -p123456 -e "select * from db3.user where id > 21" 
#查看是否恢复成功

使用binlog日志恢复指定数据

修改日志格式:

通过修改日志格式，可以看到sql语句，来决定那些需要备份，那些不需要

日志格式类型

• statement 报表模式
• row 行模式
• mixed 混合模式

查看默认使用的日志格式

show variables like "binlog_format";  #默认行模式

修改日志记录格式

vim /etc/my.cnf
binlog_format="mixed"systemctl restart mysqld
mysql -uroot -p123456 -e"reset master"
mysql -uroot -p123456 -e"show master status"
mysql -uroot -p123456 -e'insert into db3.user(name,uid,gid) values("tom",1006,1006)'
mysql -uroot -p123456 -e"show master status"
mysqlbinlog /mylog/bxl.000001 | grep -i insert

在日志文件中能看到我们刚刚写入的指令

日志文件如何区分记录的多条sql命令：

偏移量，时间点

命令格式：

mysqlbinlog 选项 /路径/日志文件 | mysql -uroot -p密码

指定偏移量

–start-position=偏移量开始值 --stop-position=偏移量结束值

指定时间点

–start-datetime=“yyyy-mm-dd hh:mm:ss” --stop-datetime=“yyyy-mm-dd hh:mm:ss”

#刚刚添加了用户，现在删除他，我们只需要恢复他添加用户的内容，删除不恢复
mysql -uroot -p123456 -e ' delete from db3.user where name="tom" '
mysqlbinlog /mylog/bxl.000001 | grep -i delete  #查看删除是否写入成功
#日志文件传输,在test主机上进行测试
scp /mylog/bxl.000001 root@192.168.4.100:/tmp
#test主机
mysqlbinlog /tmp/bxl.000001

这就是一个sql语句的binlog

at 325开始偏移量

211214 系统时间

at 452语句偏移量

commit 提交

at 483 这个语句提交后的偏移量

这里我们使用的是开始偏移量和语句提交后的偏移量（提交后语句才会生效）