MySQL八、备份和还原

 
 
 
 
 
 
MySQL 八、数据库备份和还原
 
 
 
1、二进制日志相关配置
 
 
1)查看使用中的二进制日志文件列表,及大小
 
  SHOW {BINARY | MASTER} LOGS
 
  ex: show master logs;
  show binary logs;
 
这两个命令是一样的
MySQL八、备份和还原
物理大小
MySQL八、备份和还原
2)查看使用中的二进制日志文件
 
SHOW MASTER STATUS;
 
表示当前二进制数据库,处于什么位置(下图中当前处于245位置)
MySQL八、备份和还原
当对表进行修改后,可看到位置发生了变化
 
update students set name='Shi Banyu' where stuid=1;
show master status;
MySQL八、备份和还原
 
3)查看查看二进制文件中的指定内容
 
SHOW BINLOG EVENTS [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count]
 
ex: show binlog events in ‘mysql-bin.000001' from 6516 limit 2,3
 
show binlog events in 'a.000002';
MySQL八、备份和还原
 
show binlog events in 'a.000002' from 245;
MySQL八、备份和还原
2、二进制日志客户端命令工具:mysqlbinlog
 
 
 
1)生成新日志文件: flush logs
 
flush logsl 触发生成新的日志
MySQL八、备份和还原
生成新的日志a.000003,
新的事件将记录到这个新的日志文件里
MySQL八、备份和还原
2)窥探二进制文件的内容
 
mysqlbinlog [OPTIONS] log_file…
 
ex: mysqlbinlog /data/mylog/a.000003
 
对表做一次修改
MySQL八、备份和还原
再查看二进制日志文件,可看到:
at 245及之后的位置,记录了数据库发生的改变。
因此,也就可以利用二进制文件,来还原数据库
MySQL八、备份和还原
3)一个还原的小实验
 
将二进制文件导出到文件
mysqlbinlog /data/mylog/a.000003 > bin.sql
 
对表再次进行修改:
 
update students set name='Shi Po Tiandi' where stuid=2;
select * from students;
 
将修改之前的二进制文件内容,再导入到数据库中(猜猜会发生什么?)
mysql < bin.sql
 
表又复原到修改之前的状态了
 
可以看到
MySQL八、备份和还原
4)mysqlbinlog客户端命令参数
 
mysqlbinlog [OPTIONS] log_file…
 
--start-position=# 指定开始位置
--stop-position=#
--start-datetime=
--stop-datetime=
 
时间格式:YYYY-MM-DD hh:mm:ss
--base64-output[=name]
 
示例:
 
mysqlbinlog --start-position=6787 --stop-position=7527 /var/lib/mysql/mariadb-bin.000003
 
mysqlbinlog --start-datetime="2018-01-30 20:30:10" --stop-datetime="2018-01-30 20:35:22" mariadb-bin.000003;
 
 
 
ex:
 
mysqlbinlog /data/mylog/a.000003 --start-position=316 --stop-position=431
 
5)删除二进制日志文件
 
PURGE { BINARY | MASTER } LOGS
{ TO 'log_name' | BEFORE datetime_expr }
 
 
删除指定日志文件之前的文件
purge binary logs to 'a.000003'; (删除到3,3之前的都删掉)
MySQL八、备份和还原
删除所有二进制日志,index文件重新记数
 
RESET MASTER [TO #];
 
日志文件从#开始记数,默认从1开始,一般是master第一次启动时执行,MariaDB10.1.6开始支持TO #
 
RESET MASTER; 彻底删除,重新计数
MySQL八、备份和还原
二、备份和恢复
 
 
1)全备份、增量备份示意(每周全备、每日增量备份)
 
全 增 增 增 增 增 增
+--------+-------+---------+------+-------+-------+
日 一 二 三 四 五 六
 
 
2)默认mysql备份相关配置
 
 
 
 
分库、分表
innodb_file_per_table=ON (建议默认配置)
 
2、备份工具
 
mysqldump:逻辑备份工具,适用所有存储引擎,温备;支持完全或部分备份;对InnoDB存储引擎支持热备
 
cp, tar等复制归档工具:物理备份工具,适用所有存储引擎;只支持冷备;完全和部分备份
 
LVM的快照:先加锁,做快照后解锁,几乎热备;借助文件系统管理工具进行备份
 
mysqlhotcopy:几乎冷备;(仅适用于MyISAM存储引擎)
 
 
1)mysqldump备份工具的使用
 
mysqldump是一个客户端命令,通过mysql协议连接至mysqld服务器进行备份:
 
mysqldump [OPTIONS] database [tables] 备份特定数据库的某张单表
mysqldump [OPTIONS] –B DB1 [DB2 DB3...] 备份指定的若干个数据库
mysqldump [OPTIONS] –A [OPTIONS] 备份所有数据库
 
 
 
2)mysqldump选项
 
-A, --all-databases 备份所有数据库,含create database
-B , --databases db_name… 指定备份的数据库,包括create database语句
-E, --events:备份相关的所有event scheduler
-R, --routines:备份所有存储过程和存储函数
--triggers:备份表相关触发器,默认启用,用--skip-triggers,不备份触发器
--master-data[=#]: 此选项须启用二进制日志
1:所备份的数据之前加一条记录为CHANGE MASTER TO语句,非注释,不指定#,默认为1
2:记录为注释的CHANGE MASTER TO语句
此选项会自动关闭--lock-tables功能,自动打开--lock-all-tables功能(除非开启--single-transaction)
 
-F, --flush-logs :备份前滚动日志,锁定表完成后,执行flush logs命令,生成新的二进制日志
文件,配合-A时,会导致刷新多次数据库,在同一时刻执行转储和日志刷新,则应同时使
用--flush-logs和-x,--master-data或-single-transaction,此时只刷新一次,建议:
和-x,--master-data或 --single-transaction一起使用
 
--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 使用十六进制符号转储二进制列(例如,“abc”变为0x616263),受影响的数据类型包括BINARY, VARBINARY,BLOB,BIT
-q, --quick 不缓存查询,直接输出,加快备份速度
 
 
 
 
实验一 :冷备份数据库,并还原
 
思路:停服务,拷贝出数据库文件,进行冷备份
 
 
备份
 
1)查看当前的数据库,共有5个数据库
mysql -e 'show databases'
MySQL八、备份和还原
2)停服务
systemctl stop mariadb
 
 
3)备份文件:打包压缩拷贝出来 (对/var/lib/mysql整个文件夹,即所有数据全都备份)
tar Jcvf /data/all.tar.xz /var/lib/mysql/
 
注:1、生产环境应将备份文件与数据库服务器分开存放
2、将备份文件传输到非本 地服务器保存,以备还原时使用
MySQL八、备份和还原
4)模拟mysql数据库出现故障
 
rm -rf /var/lib/mysql/* (仅删除/var/lib/mysql/目录下的所有文件)
 
还原
 
 
5)用备份的文件读数据库进行还原
 
tar xvf /data/all.tar.xz
mv  var/lib/mysql/* /var/lib/mysql/
 
查看文件都已复原回去
 
MySQL八、备份和还原
6)重启服务
 
systemctl start mariadb
 
7)测试还原效果
MySQL八、备份和还原
实验三、基于LVM快照的备份(配合tar、cp等工具)
 
前提:
 
要实现基于LVM快照的备份,需要将数据库放到快照的空间,才能实现这种备份
 
1、将数据库数据迁移到逻辑卷
2、
 
 
备份
 
1)创建逻辑卷
 
fdisk /dev/sda
 
pvcreate /dev/sda6 创建pv
pvdisplay 确认
 
vgcreate vg0 dev/sda6 创建vg,将该pv加到vg中
vgdisplay 确认
 
lvcreate -L 5G -n lv_mysql vg0 创建lv.存放mysql数据
lvcreate -L 3G -n lv_binlog vg0 创建lv.存放二进制日志文件
lvs
 
mkfs.xfs /dev/vg0/lv_mysql 格式化文件系统
mkfs.xfs /dev/vg0/lv_binlog
MySQL八、备份和还原
mkdir /data/{mysql,binlog} 创建两个挂载点
 
mount /dev/vg0/lv_mysql /data/mysql/ 挂载
mount /dev/vg0/lv_binlog /data/binlog/
 
或需要永久挂载,修改/etc/fstab
 
vim /etc/fstab
添加两行
 
UUID=4615d276-95f9-4d03-a337-9438e9fb89d4 /data/mysql xfs default 0 0
UUID=9ab9b41f-1315-490a-b573-3144807f7313 /data/binlog xfs default 0 0
 
mount -a
 
 
2)修改目录权限
 
chown -R mysql: /data/mysql
chown -R mysql: /data/binlog
MySQL八、备份和还原
3)修改配置文件
 
按照规划将二进制日志和数据库文件分别存放于这两个目录
 
vim /etc/my.cnf
MySQL八、备份和还原
4)重启服务
 
systemctl restart mariadb
 
 
5)对数据库添加读锁,防止写入数据的操作
 
备份过程中暂停用户的读写,因此要先对数据库添加读锁,暂时禁止写的操作
 
MariaDB[(none)]> flush tables with read lock;
 
flush logs; 刷新日志,让生成新的日志
 
记录当前的二进制日志:mysql-bin.000004 245
MySQL八、备份和还原
注意:锁定,然后做快照再解锁,这个过程要快速,不能让用户一直不能访问,达到近乎热备的效果
 
6)对数据库中的某张表进行修改
 
MariaDB [(none)]> use hellodb;
MariaDB [hellodb]> show tables;
MySQL八、备份和还原
6)创建lvm快照(相当于对数据库做了备份)
 
lvcreate -L 1G -n lv_mysql_snap -s -p r /dev/vg0/lv_mysql
 
-s :快照
-p r:只读
 
 
7)对数据库解锁(恢复让用户正常访问)
 
MariaDB [(none)]> unlock tables; 解锁
 
测试下解锁后能否正常写入
create database dbx1;
show databases;
MySQL八、备份和还原
8)挂载逻辑卷快照
 
要导出快照里的备份,必须先能够访问快照的内容,因此对快照进行临时挂载
 
mount -o nouuid,norecovery /dev/vg0/lv_mysql_snap /mnt

MySQL八、备份和还原

9)拷贝出逻辑卷快照里的文件,进行备份
 
cp -a /mnt/* /backup/
MySQL八、备份和还原
10)删除快照,否则影响用户访问数据库性能
 
umount /mnt 取消快照挂载
lvremove /dev/vg0/lv_mysql_snap 删除快照
 
再创建两个数据库,这是删除快照后创建的
 
MariaDB [(none)]> create database dbx2;
MariaDB [(none)]> create database dbx3;
 
 
模拟数据库损坏
 
  rm -rf /data/mysql/*
 
 
还原
 
1)停止服务
systemctl stop mariadb
 
 
2) 拷贝备份文件至数据目录
 
cp -av /backup/* /data/mysql/
 
3)启动服务
systemctl start mariadb
 
此时可看到,数据库还原至快照前状态

MySQL八、备份和还原

但是删除快照后创建的两个数据库dbx2,dbx3已经丢失了。
如果想恢复,可用二进制日志继续恢复:
 
5)添加读锁
 
flush tables with read lock; 添加读锁,暂时保持数据不能修改
 
(记录当前的二进制日志:mysql-bin.000004 245)
 
当前二进制已变为:mysql-bin.000005 | 245
MySQL八、备份和还原
6)将二进制日志导出,用以修复数据库
 
因为mysql-bin.000004 和mysql-bin.000005是快照之后,记录了最近发生的变化,因此将其导出,再利用它们修复数据库
 
cd /data/binlog (必须在此目录下)
 
mysqlbinlog --start-position=245 mysql-bin.000004 > /backup/bin.sql
mysqlbinlog mysql-bin.000005 >> /backup/bin.sql
 
 
7)确保恢复过程中,没有用户可以读取或写入
 
vim /etc/my.cnf
 
[mysqld]
skip_networking (禁止使用网络连接将使用户将无法连接数据库,只有自己可连)
iptables –A
 
重启mysql服务使生效
systemctl  restart mariadb
 
导入二进制文件
mysql < /backup/bin.sql
 
此时数据库已还原至最新状态
MySQL八、备份和还原
8)恢复用户访问数据库
 
vim /etc/my.cnf
 
[mysqld]
skip_networking              去掉
或清除防火墙
systemctl restart mariadb
 
至此,通过lvm快照方式的备份还原就完成了。
上一篇:javascirpt的json.stringify()方法在IE浏览器兼容性模式下出错的原因与解决办法


下一篇:解决IE8 内置JSON.stringify,中文变unicode的问题