摘要: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
总结:
复习了脚本的用法及sed
,grep
,gzip
,for循坏
,又能够实现备份不同数据库的分类存档
实验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