MYSQL备份

post thumb
Mysql
作者 Louis 发表于 2018年6月13日

摘要:MYSQL的lvm快照备份实现过程,mysqldump备份的过程.

LVM快照备份

特点:近乎热备的备份

实现LVM

$ echo '- - -' > /sys/class/scsi_host/host2/scan
$ lsblk
$ pvcreate /dev/sdb
$ vgcreate vg0 /dev/sdb
$ lvcreate -n lv_mysql -l 50%FREE vg0
$ lvcreate -n lv_binlog -l 50%FREE vg0
$ mkfs.ext4 /dev/vg0/lv_mysql
$ mkfs.ext4 /dev/vg0/lv_binlog
$ mkdir /data/{mysql,binlog}
$ mount /dev/vg0/lv_mysql /data/mysql
$ mount /dev/vg0/lv_binlog /data/binlog
$ chown -R mysql.mysql  /data/
$ vim /etc/my.cnf
datadir=/data/mysql
log_bin=/data/binlog/mysql-bin
$ systemctl restart mariadb

快照备份

-- 读锁表
flush tables with read lock;
-- 刷新日志
flush logs;
-- 记录bin-log的position
show master logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |     17750 |
| mysql-bin.000002 |       385 |
+------------------+-----------+
-- 创建lvm快照
lvcreate  -n lv_mysql_snap -L 10G -s -p r /dev/vg0/lv_mysql
-- 解锁
unlock tables;
-- 将快照的数据备份出来,centos6不用加-o选项
$ mount  -o nouuid,norecovery /dev/vg0/lv_mysql_snap /mnt
$ cp -a /mnt/ /backup
$ umount /mnt
$ lvremove /dev/vg0/lv_mysql_snap

模拟场景

-- 数据已经变更
create database testdb;
use hellodb;
update students set name='houzi' where stuid=25;
drop table teachers;

备份恢复至最新状态

$ rm -rf  /data/mysql/*
-- 禁止用户访问
$ systemctl stop mariadb
$ cp -av /backup/* /data/mysql
$ vim /etc/my.cnf
[mysqld]
skip_network
$ systemctl start mariadb
$ mysql
flush tables with read lock;
show master logs;
-- 导入最新的二进制日志,恢复至破坏后的节点
$ mysqlbinlong --start-position=385 mysql-bin.000004 >/backup/bin.sql
$ mysqlbinlong --start-position=385 mysql-bin.000005 >>/backup/bin.sql
$ mysql < /backup/bin.log

mysqldump备份

mysqldump参考: 官方说明

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 不缓存查询,直接输出,加快备份速度

实验选项演示:

比较常用的是mysqldump -A,mysqldump -A -F,mysqldump -B database

$ mysqldump -uroot -pcentos -B hellodb > /backup/hellodb_B.sql
$ mysqldump -uroot -pcentos -A |gzip > /backup/all.sql.gz
$ rm -rf /var/lib/mysql/*
$ gzip -d /backip/all.sql.gz   #gunzip /backip/all.sql.gz 
$ mysql < /backup/all.sql
$ mysqldump -A -F > /backup/all_f.log  	# 滚动刷新日志
$ mysql -e 'show binary logs'

InnoDB备份选项:

支持热备,可用温备但不建议用 --single-transaction 此选项Innodb中推荐使用,不适用MyISAM,此选项会开始备份前,先执行START TRANSACTION指令开启事务; 此选项通过在单个事务中转储所有表来创建一致的快照。 仅适用于存储在支持多版本控制的存储引擎中的表(目前只有InnoDB可以); 转储不保证与其他存储引擎保持一致。 在进行单事务转储时,要确保有效的转储文件(正确的表内容和二进制日志位置),没有其他连接应该使用以下语句:ALTER TABLE,DROP TABLE,RENAME TABLE,TRUNCATE TABLE; 此选项和--lock-tables(此选项隐含提交挂起的事务)选项是相互排斥; 备份大型表时,建议将--single-transaction选项和--quick结合一起使用;

MyISAM备份选项:

支持温备;不支持热备,所以必须先锁定要备份的库,而后启动备份操作 锁定方法如下: -x,--lock-all-tables:,加全局读锁,锁定所有库的所有表,同时加--single-transaction--lock-tables选项会关闭此选项功能 注意:数据量大时,可能会导致长时间无法并发访问数据库 -l,--lock-tables:对于需要备份的每个数据库,在启动备份之前分别锁定其所有表,默认为on,--skip-lock-tables选项可禁用,对备份MyISAM的多个库,可能会造成数据不一致 注:以上选项对InnoDB表一样生效,实现温备,但不推荐使用

实验

实验1

实现不同数据库的备份,但是和mysqldump -A不同,将每个数据库分别打包 **方法1:**利用sed,生成一个备份脚本

$ mysql -e 'show databases' | grep -iEv 'database|schema'  | sed -r 's/(.*)/mysqldump -uroot  -B \1 |gzip >\1-`date +%F`.sql.gz/' >backup.sh
$ cat backup.sh
mysqldump -uroot  -B hellodb |gzip >hellodb-`date +%F`.sql.gz
mysqldump -uroot  -B mysql |gzip >mysql-`date +%F`.sql.gz
mysqldump -uroot  -B test |gzip >test-`date +%F`.sql.gz

**方法2:**利用for循环,

#!/bin/bash
for db in `mysql -e 'show databases' | grep -iEv 'database|schema'` ;do
	mysqldump -uroot  -B $db > ${db}-`date +%F`.sql.gz
done

总结:

复习了脚本的用法及sedgrep,gzipfor循坏,又能够实现备份不同数据库的分类存档

实验2

场景:数据库文件损坏,如何恢复。前提:有备份文件,启用了二进制日志

$ mysql < hellodb.sql
$ mysqldump -A -F --single-transaction --master-data=1 >/backup/full.sql
$ less |/backup/full.sql
···
#记录的节点位置
CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000004', MASTER_LOG_POS=245;
···

模拟数据改变

-- 数据发生了变化
create database testdb;
use hellodb;
update students set name='houzi' where stuid=25;
drop table teachers;

删库恢复

$ vim /etc/my.cnf
[mysqld]
skip_network
$ rm -rf /data/mysql/*  #数据被破坏
$ mysql -e 'use hellodb'
ERROR 1049 (42000): Unknown database 'hellodb'
$ systemctl restart mariadb
$ mysqlbinlog --start-position=245 mysql-bin.000004 >/backup/bin.sql
$ mysql < /backup/full.sql
$ mysql < /backup/bin.sql

实验3

场景:9点有全备份,10点的时候误删除表,10:10后续用户修改过另外一张表,如何还原把删除表的操作还原;条件:log-bin必须启用,有数据备份。

场景模拟

$ mysqldump -A -F --single-transaction --master-data=1 >/backup/full.sql
-- 修改表
use hellodb
insert students values(26,'dongfei',25,'M',2,3)
-- 误删除
drop table students; 
-- 用户继续修改
insert courses values(8,'magedu');

定点到删除的位置

flush tables with read lock;
flush logs;
-- 查看当前的bin-log的position
show master logs;
#查询全备份的position,确定全备份到目前之间的所有日志文件及position
$ head /backup/full.sql
CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000009', MASTER_LOG_POS=245;
#找到删除语句的,删除此语句
$ mysqlbinlog --start-position=245 mysql-bin.000009 >/backup/bin.sql
$ vim /backup/bin.sql
将drop table students; 这个语句的执行at段删掉即可

还原

$ systemctl stop mariadb
$ rm -rf /data/mysql/*
$ systemctl start mariadb
$ mysql < /backup/full.sql
$ mysql < /backup/bin.sql
$ mysql -e 'show tables' 
+-------------------+
| Tables_in_hellodb |
+-------------------+
| classes           |
| coc               |
| courses           |	
| scores            |
| students          |	#恢复删错的表,且最新数据保存下来了
| teachers          |
| toc               |
+-------------------+

生产环境备份实战策略

innodb:建议的策略

#!/bin/bash
BACKUP="/backup"
BACKUP_TIME=`date +%F_%T`
mysqldump -uroot -ppassword -A -F -E -R --single-transaction --master-data=1 --flush-privileges > $BACKUP/fullbak_$BACKUP_TIME.sql

MyISAM:建议备份策略

#!/bin/bash
BACKUP="/backup"
BACKUP_TIME=`date +%F_%T`
mysqldump -uroot -ppassword -A -F -E -R -x --master-data=1 --flush-privileges  >${BACKUP} /fullbak_${BACKUP_TIME}.sql
上一篇
MYSQL日志