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

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

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

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

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

物理备份 VS 逻辑备份

物理备份

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

优点：

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

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

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

缺点：

1.备份文件大

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

3.只能在本地发起备份

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

逻辑备份

备份表的逻辑记录，保存以逻辑数据库结构（CREATE DATABASE、 CREATE 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、水平分库

 

概念：

以字段为依据，按照一定策略（hash、range等），将一个库中的数据拆分到多个库中。

结果：

每个库的结构都一样；

每个库的数据都不一样，没有交集；

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

场景：

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

分析：

库多了，io和cpu的压力自然可以成倍缓解。

2、水平分表

 

概念：

以字段为依据，按照一定策略（hash、range等），将一个表中的数据拆分到多个表中。

结果：

每个表的结构都一样；

每个表的数据都不一样，没有交集；

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

场景：

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

分析：

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

3、垂直分库

 

概念：

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

结果：

每个库的结构都不一样；

每个库的数据也不一样，没有交集；

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

场景：

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

分析：

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

4、垂直分表

 

概念：

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

结果：

每个表的结构都不一样；

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

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

场景：

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

分析：

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

内容来源：

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

一键迁移脚本

#!/usr/bin/bash
# ******************************************************
# Author       : Yang Zhi Bin
# Email        : 1724720581@qq.com
# Filename     : mysql-in-out.sh
# Last modified: 2022-06-23 17:08
# Description  : 此脚本适合从远程数据库拉取数据导入本地数据库,使用前请创建好库并修改my.cnf文件
# ******************************************************
#输入连接信息
host=
user=
That_password=
This_password=
database_name=
 
#创建目录
schema_path=/server/db/schema
table_path=/server/db/table
table_data_path=${table_path}/${database_name}
/bin/mkdir -p ${table_data_path}
/bin/mkdir -p $(schema_path)
/bin/mkdir -p ${table_path}
 
#导出操作
echo "导出表结构"
/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
 
echo "导出表名"
/bin/mysql -h"${host}" -u"${user}" -p"${That_password}" ${database_name} -e "show tables;" > ${table_path}/${database_name}-table.txt
 
echo "导出表数据"
cat ${table_path}/${database_name}-table.txt | while read eachline
do
  echo -e "\n\n${eachline}"
  /bin/mysqldump -h"${host}" -u"${user}" -p"${That_password}" --single-transaction --skip-extended-insert ${database_name} ${eachline} > ${table_data_path}/${eachline}.sql
done
 
echo "修改gtid"
/bin/sed -i '20,26d' ${table_data_path}/*.sql
 
 
#导入操作
echo "导入表结构"
/bin/mysql -u"${user}" -p"${This_password}" --one-database ${database_name} < ${schema_path}/${database_name}-schema.sql
 
echo "导入表数据"
ls ${table_data_path}/*.sql > ${table_data_path}/all-sql.txt
cat ${table_data_path}/all-sql.txt | while read eachline
do
  echo "The imported database is  ${eachline}"
  /bin/mysql  -u"${user}" -p"${This_password}" ${database_name} < ${eachline}
done