当前位置:   article > 正文

MySQL分表备份方案(后附一键迁移脚本,经验证安全可用!!)_分表原表数据不动,单独备份数据

分表原表数据不动,单独备份数据

环境数据库迁移引发的关于mysqldump的思考......

首先我们需要了解两个概念,衡量一个数据中心的容灾能力时,有两个常用的指标:

RTO:Recovery Time Objective,恢复时间目标。指的是灾难发生后,必须在这个时间内恢复数据。

RPO:Recovery Point Objective,数据恢复点目标。指的是灾难发生后,数据可以恢复到的时间点。

物理备份 VS 逻辑备份

物理备份

就是备份物理文件,由存储数据库内容的目录和文件的原始副本组成,可以理解为cp。其优缺点如下:

优点:

1.备份、恢复速度快,尤其是恢复速度直接关系着数据库服务的RTO。

2.无需实例在线,在实例关闭的情况下,可直接拷贝文件,不用担心备份的一致性。

3.关闭实例进行备份,也称之为 “冷备” 。

缺点:

1.备份文件大

2.恢复时,对平台、操作系统、MySQL版本有要求,必须一致或兼容

3.只能在本地发起备份

4.因为是拷贝物理文件,即使文件中存在很多“空洞”(大量DELETE导致),也无法通过恢复来收缩

逻辑备份

备份表的逻辑记录,保存以逻辑数据库结构(CREATE DATABASECREATE TABLE语句)和内容(INSERT语句或分隔文本文件)表示的信息。。其优缺点如下:

优点:

1.可移植性强。恢复时,对平台、操作系统、MySQL版本无要求。

2.灵活。尤其是在恢复时,可只恢复一个库或一张表。

3.对表的存储引擎没有要求,任何类型的表都可备份。

4.备份文件较小。

5.可远程发起备份。

6.恢复后,能有效收缩空间。

缺点:

1.备份、恢复速度慢

2.备份会”污染”Buffer Pool,业务热点数据会被备份数据驱逐出 Buffer Pool。

正文开始

那我们先了解下为什么会慢,也就是数据库的瓶颈在哪里?

1、IO瓶颈

第一种:磁盘读IO瓶颈,热点数据太多,数据库缓存放不下,每次查询时会产生大量的IO,降低查询速度。

第二种:网络IO瓶颈,请求的数据太多,网络带宽不够。

2、CPU瓶颈

第一种:SQL问题,如SQL中包含join,group by,order by,非索引字段条件查询等,增加CPU运算的操作 。

第二种:单表数据量太大,查询时扫描的行太多,SQL效率低,CPU率先出现瓶颈 。

不管是IO瓶颈,还是CPU瓶颈,最终都会导致数据库的活跃连接数增加,进而逼近甚至达到数据库可承载活跃连接数的阈值。在业务Service来看就是,可用数据库连接少甚至无连接可用。

        通过查阅资料和自己的测试,分库分表可以很大程度上降低数据迁移的时间。为什么分库分表能降低时间呢?原理我看了,我看不懂,所以只能从表层进行阐述。首先这种多库一起备份,就会产生一个问题,如果只是其中一个数据库有问题了,就不好进行单库恢复了,因此多库备份时就需要进行多次单库备份的操作。分库备份是为了恢复数据库时方便操作,同样如果是某个库中的某一个表有损坏,但又不有全库进行恢复,所以实际生产中常用的是分库、分表进行备份,这样数据也备份了,恢复时也好操作。

分库分表,解决的是硬件(cpu,磁盘,网络)问题和应对迁移过程中失败方便排查的问题。

通过同事和查阅资料了解到,如果数据库存在视图,做数据迁移时会失败。通过日志发现视图存在依赖关系,会因为创建顺序问题而报错,虽然目前我们的数据库不存在视图的问题,但是为了以后的使用方便,本次把视图的问题考虑进去,我们在分表的同时也做数据和结构的分离。

分结构和数据,解决的是视图的依赖问题。

时间上的缩短,也是mysql优化的结果,分库分表就是一种优化,从硬件和应对错误的能力上的优化,也是对现有资源(生产环境的正常使用和资源池)的一种减压。

操作步骤:

1.分库分表备份数据库

  01-备份所有的表结构

mysqldump  -h'' -u'root' -p''  --set-gtid-purged=OFF -d portal  > /server/db/schema/portal-schema.sql

  02-导出所有的表名称

          mysql -h'' -u'root' -p'' portal -e "show tables;" > /server/db/table/portal-table.txt

  03-删除第一行

          sed -i '1d' portal-table.txt

  04-执行脚本导出数据

#! /usr/bin/env bash

  base_path=/server/db/table

  mysql_output=${base_path}/portal-table

  database_name=portal

  cat ${base_path}/portal-table.txt | while read eachline

  do

      echo -e "\n\n${eachline}"

      mysqldump -h -u'root' -p'' --single-transaction  ${database_name} ${eachline} > ${mysql_output}/${eachline}.sql

  done

2.还原数据库

  01-创建数据库

  02-导入所有表结构

         mysql -u'root' -p'' --one-database portal < /server/db/schema/portal

   03-执行脚本

#! /usr/bin/env bash

SQL_PATH=/server/db/table/portal-table

mysql_database=portal

mysql_pwd=

ls ${SQL_PATH}/*.sql > ${SQL_PATH}/portal-table.txt

cat ${SQL_PATH}/mysql-table-sql.txt | while read eachline

do

    echo "mysql -uroot -p ${mysql_database} < ${eachline}"

    mysql  -u'root' -p${mysql_pwd} ${mysql_database} < ${eachline}

done

压缩备份

有时候,数据库的数据比较大,可能会用到压缩后进行备份,节省备份时间与磁盘空间的使用

导出

mysqldump -u'root' -p'' database table | gzip > table.sql.gz

导入

gunzip < table.sql.gz | mysql -u'root' -p'' database

附页

这里来说不足,我说我是在分库分表,其实并不完全是分库分表,甚至算不上是优化,只是有其形而无其神,怎么说呢?首先我们先来说什么是分库分表。

分库分表分为分库和分表,分库和分表又有水平和垂直之分。

1、水平分库

 

概念:

以字段为依据,按照一定策略(hashrange等),将一个库中的数据拆分到多个库中。

结果:

每个库的结构都一样;

每个库的数据都不一样,没有交集;

所有库的并集是全量数据;

场景

系统绝对并发量上来了,分表难以根本上解决问题,并且还没有明显的业务归属来垂直分库。

分析:

库多了,iocpu的压力自然可以成倍缓解。

2、水平分表

 

概念:

以字段为依据,按照一定策略(hashrange等),将一个表中的数据拆分到多个表中。

结果:

每个表的结构都一样;

每个表的数据都不一样,没有交集;

所有表的并集是全量数据;

场景:

系统绝对并发量并没有上来,只是单表的数据量太多,影响了SQL效率,加重了CPU负担,以至于成为瓶颈。推荐:一次SQL查询优化原理分析

分析:

表的数据量少了,单次SQL执行效率高,自然减轻了CPU的负担。

3、垂直分库

 

概念:

以表为依据,按照业务归属不同,将不同的表拆分到不同的库中。

结果:

每个库的结构都不一样;

每个库的数据也不一样,没有交集;

所有库的并集是全量数据;

场景:

系统绝对并发量上来了,并且可以抽象出单独的业务模块。

分析:

到这一步,基本上就可以服务化了。例如,随着业务的发展一些公用的配置表、字典表等越来越多,这时可以将这些表拆到单独的库中,甚至可以服务化。再有,随着业务的发展孵化出了一套业务模式,这时可以将相关的表拆到单独的库中,甚至可以服务化。

4、垂直分表

 

概念:

以字段为依据,按照字段的活跃性,将表中字段拆到不同的表(主表和扩展表)中。

结果:

每个表的结构都不一样;

每个表的数据也不一样,一般来说,每个表的字段至少有一列交集,一般是主键,用于关联数据;

所有表的并集是全量数据;

场景:

系统绝对并发量并没有上来,表的记录并不多,但是字段多,并且热点数据和非热点数据在一起,单行数据所需的存储空间较大。以至于数据库缓存的数据行减少,查询时会去读磁盘数据产生大量的随机读IO,产生IO瓶颈。

分析:

可以用列表页和详情页来帮助理解。垂直分表的拆分原则是将热点数据(可能会冗余经常一起查询的数据)放在一起作为主表,非热点数据放在一起作为扩展表。这样更多的热点数据就能被缓存下来,进而减少了随机读IO。拆了之后,要想获得全部数据就需要关联两个表来取数据。

内容来源:

MySQL:互联网公司常用分库分表方案汇总

一键迁移脚本

  1. #!/usr/bin/bash
  2. # ******************************************************
  3. # Author : Yang Zhi Bin
  4. # Email : 1724720581@qq.com
  5. # Filename : mysql-in-out.sh
  6. # Last modified: 2022-06-23 17:08
  7. # Description : 此脚本适合从远程数据库拉取数据导入本地数据库,使用前请创建好库并修改my.cnf文件
  8. # ******************************************************
  9. #输入连接信息
  10. host=
  11. user=
  12. That_password=
  13. This_password=
  14. database_name=
  15. #创建目录
  16. schema_path=/server/db/schema
  17. table_path=/server/db/table
  18. table_data_path=${table_path}/${database_name}
  19. /bin/mkdir -p ${table_data_path}
  20. /bin/mkdir -p $(schema_path)
  21. /bin/mkdir -p ${table_path}
  22. #导出操作
  23. echo "导出表结构"
  24. /bin/mysqldump -h"${host}" -u"${user}" -p"${That_password}" --set-gtid-purged=OFF -d --add-drop-table ${database_name} > ${schema_path}/${database_name}-schema.sql
  25. echo "导出表名"
  26. /bin/mysql -h"${host}" -u"${user}" -p"${That_password}" ${database_name} -e "show tables;" > ${table_path}/${database_name}-table.txt
  27. echo "导出表数据"
  28. cat ${table_path}/${database_name}-table.txt | while read eachline
  29. do
  30. echo -e "\n\n${eachline}"
  31. /bin/mysqldump -h"${host}" -u"${user}" -p"${That_password}" --single-transaction --skip-extended-insert ${database_name} ${eachline} > ${table_data_path}/${eachline}.sql
  32. done
  33. echo "修改gtid"
  34. /bin/sed -i '20,26d' ${table_data_path}/*.sql
  35. #导入操作
  36. echo "导入表结构"
  37. /bin/mysql -u"${user}" -p"${This_password}" --one-database ${database_name} < ${schema_path}/${database_name}-schema.sql
  38. echo "导入表数据"
  39. ls ${table_data_path}/*.sql > ${table_data_path}/all-sql.txt
  40. cat ${table_data_path}/all-sql.txt | while read eachline
  41. do
  42. echo "The imported database is ${eachline}"
  43. /bin/mysql -u"${user}" -p"${This_password}" ${database_name} < ${eachline}
  44. done

声明:本文内容由网友自发贡献,不代表【wpsshop博客】立场,版权归原作者所有,本站不承担相应法律责任。如您发现有侵权的内容,请联系我们。转载请注明出处:https://www.wpsshop.cn/w/IT小白/article/detail/410286
推荐阅读
相关标签
  

闽ICP备14008679号