本站以分享各种运维经验和运维所需要的技能为主
《python零基础入门》:python零基础入门学习
《python运维脚本》: python运维脚本实践
《shell》:shell学习
《terraform》持续更新中:terraform_Aws学习零基础入门到最佳实战
《k8》从问题中去学习k8s
《docker学习》暂未更新
《ceph学习》ceph日常问题解决分享
《日志收集》ELK+各种中间件
《运维日常》运维日常
《linux》运维面试100问
# 生产中某个库全备的正确打开方式: mysqldump -u${MYSQL_USER} -p${MYSQL_PASSWORD} -h${MYSQL_HOST} -P${MYSQL_PORT} ${MYSQL_DATABASE} --set-gtid-purged=OFF
一、mysql数据备份
#mysql客户端 mysql mysqladmin mysqldump
1.备份的原因
1.备份就是为了恢复。 2.尽量减少数据的丢失(公司的损失)
2.备份的类型
1.冷备:停库,停服务,备份 2.热备:不停库,不停服务,备份 3.温备:不停服务,锁表(阻止数据写入),备份 #冷备份: 这些备份在用户不能访问数据时进行,因此无法读取或修改数据。这些脱机备份会阻止执行任何使用数据的活动。这些类型的备份不会干扰正常运行的系统的性能。但是,对于某些应用程序,会无法接受必须在一段较长的时间里锁定或完全阻止用户访问数据。 #温备份: 这些备份在读取数据时进行,但在多数情况下,在进行备份时不能修改数据本身。这种中途备份类型的优点是不必完全锁定最终用户。但是,其不足之处在于无法在进行备份时修改数据集,这可能使这种类型的备份不适用于某些应用程序。在备份过程中无法修改数据可能产生性能问题。 #热备份: 这些动态备份在读取或修改数据的过程中进行,很少中断或者不中断传输或处理数据的功能。使用热备份时,系统仍可供读取和修改数据的操作访问。
3.备份的策略
1.全备:全部数据备份 2.增备:针对于上一次备份,将新数据备份 3.差异备份:基于上一次全备进行新数据的备份
4.备份方式
1)逻辑备份
#基于SQL语句的备份 1.binlog 2.into outfile [root@db03 data]# vim /etc/my.cnf [mysqld] secure-file-priv=/tmp mysql> select * from world.city into outfile '/tmp/world_city.data'; 3.mysqldump 4.replication
2)物理备份
#备份底层的数据文件 1.备份整个data数据目录 2.xtrabackup
二、mysqldump客户端
1.常用参数
1.不加参数:用于备份单个表 1)备份库 [root@db02 ~]# mysqldump ku > /tmp/ku.sql 2)备份表 [root@db02 ~]# mysqldump ku test > /tmp/ku.sql 3)备份多个表 [root@db02 ~]# mysqldump ku test test2 test3 > /tmp/ku.sql #注意:当不加参数时命令后面跟的是库名,库的后面全都是必须是库下面的表名 2.连接服务端参数(基本参数):-u -p -h -P -S 3.-A, --all-databases:全库备份 4.-B:指定库备份 [root@db01 ~]# mysqldump -uroot -p123 -B db1 > /backup/db1.sql [root@db01 ~]# mysqldump -uroot -p123 -B db1 db2 > /backup/db1_db2.sql 5.-F:flush logs在备份时自动刷新binlog(不怎么常用) [root@db01 backup]# mysqldump -uroot -p123 -A -F > /backup/full_2.sql 6.--master-data=2:备份时加入change master语句0没有1不注释2注释 1)等于2:记录binlog信息,并注释(日常备份) 2)等于1:记录binlog信息,不注释(扩展从库) 0)等于0:不记录binlog信息 [root@db01 backup]# mysqldump -uroot -p123 --master-data=2 >/backup/full.sql 7.--single-transaction:快照备份 8.-d:仅表结构 9.-t:仅数据 10.-R, --routines:备份存储过程和函数数据 11.--triggers:备份触发器数据 12.gzip:压缩备份 #备份成压缩包 [root@db01 ~]# mysqldump -uroot -p123 -A | gzip > /backup/full.sql.gz #恢复压缩包中的数据 [root@db03 ~]# zcat /tmp/full.sql.gz | mysql -uroot -p123 #完整的备份命令: mysqldump -uroot -p123 -A -R --triggers --master-data=2 –-single-transaction > /tmp/full.sql
2.注意:
1)mysqldump在备份和恢复时都需要MySQL实例启动为前提 2)一般数据量级100G以内,大约15-30分钟可以恢复 3)mysqldump是以覆盖的形式恢复数据的
三、企业案例
1.背景
1.正在运行的网站系统,MySQL数据库,数据量25G,日业务增量10-15M。 2.备份策略:每天23:00,计划任务调用mysqldump执行全备脚本 3.故障时间点:上午10点开发人员误删除一个核心业务表,如何恢复?
2.思路
1.停库,避免二次伤害 2.创建新库 3.倒入前一天的全备 4.通过binlog找到前一天23:00到第二天10点之间的数据 5.导入找到的新数据 6.恢复业务 a.直接使用临时库顶替原生产库,前端应用割接到新库(数据量特别大的时候) b.将误删除的表单独导出,然后导入到原生产环境(数据量小的时候)
3.模拟案例
1)模拟生产数据
mysql> create database dump; mysql> use dump mysql> create table dump(id int); mysql> insert dump values(1),(2),(3),(4); mysql> select * from dump;
2)模拟23:00全备
[root@db03 mysql]# mysqldump -uroot -p123 -A -R --triggers --master-data=2 --single-transaction > /tmp/full.sql
3)模拟23:00到10:00的数据操作
mysql> use dump mysql> insert dump values(1000),(2000),(3000),(4000);
4)模拟删库
mysql> drop database dump; Query OK, 1 row affected (0.00 sec)
4.恢复数据
1)停库,避免二次伤害
[root@db03 mysql]# systemctl stop mysqld
2)创建新库
3)导入前一天的全备
#老库将数据传输到新库 [root@db03 ~]# scp /tmp/full.sql 172.16.1.52:/tmp/ #新库导入全备数据 [root@db02 ~]# mysql < /tmp/full.sql
4)通过binlog找到前一天23:00到第二天10点之间的数据
1.找到binlog的起始位置点 [root@db03 data]# head -22 /tmp/full.sql | tail -1 -- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000006', MASTER_LOG_POS=971790; 2.找到结束位置点 [root@db03 data]# mysqlbinlog mysql-bin.000006 > 1.txt [root@db03 data]# vim 1.txt 过滤 drop database dump,取出命令上面的位置点 # at 972068 ...... drop database dump 3.取出位置点之间的数据 [root@db03 data]# mysqlbinlog -d dump --start-position=971790 --stop-position=972068 mysql-bin.000006 > /tmp/new.sql
5)导入找到的新数据
#老库将binlog数据传到新库 [root@db03 ~]# scp /tmp/new.sql 172.16.1.52:/tmp/ #新库导入新的数据 [root@db02 ~]# mysql < /tmp/new.sql
6)确认数据
mysql> use dump mysql> show tables; +----------------+ | Tables_in_dump | +----------------+ | dump | +----------------+ mysql> select * from dump; +------+ | id | +------+ | 1 | | 2 | | 3 | | 4 | | 1000 | | 2000 | | 3000 | | 4000 | +------+
7)恢复业务
1.直接使用临时库顶替原生产库,前端应用割接到新库(数据量特别大的时候) 2.将误删除的表单独导出,然后导入到原生产环境(数据量小的时候) 1)新库导出指定业务库 [root@db02 ~]# mysqldump dump > /tmp/dump.sql 2)新库将数据推送回老库 [root@db02 ~]# scp /tmp/dump.sql 172.16.1.53:/tmp 3)将恢复的数据导入老库 mysql> create database dump; mysql> use dump; mysql> source /tmp/dump.sql