目前开发的平台使用单体MySQL数据库，数据量激增后陆陆续续遇到了一些问题，此处做下总结。

单体数据库会遇到以下几个问题：
1、需要定时备份数据并上传到云存储，防黑客攻击和删库；
2、需要有个备份数据库，数据库挂了或者不见了需要马上顶上；
3、读的次数远大于写的时候，写锁和读锁的争用导致性能下降，单个物理机的并发压力大；
4、单体数据表太大索引性能下降，就需要把表拆分；
5、单库中表数量太多整体降低性能，就需要按业务分成多个库。

上面特意说复杂了，解决办法归纳一下就是几个点：
1、冷备份(定时全量/增量备份)
2、热备份(在主从架构上实现)
3、主从架构(N主M从)
4、读写分离(在主从架构上实现)
5、数据分片(分库分表)

冷备份

参考文章：https://blog.csdn.net/zone_/article/details/81293131
分三个步骤：
1、每周全量备份输出sql文件，并压缩；
2、每天增量备份输出bin-log文件（需要更改数据库配置并重启生效）；
3、备份完就调用脚本上传到七牛云。

全量备份：

#!/bin/bash
#在使用之前，请提前创建以下各个目录
#获取当前时间
date_now=$(date "+%Y%m%d-%H%M%S")
backUpFolder=/home/db/backup/mysql
username="root"
password="xtionai"
db_name="zone"
#定义备份文件名
fileName="${db_name}_${date_now}.sql"
echo "${fileName}"
#定义备份文件目录
backUpFileName="${backUpFolder}/${fileName}"
echo "starting backup mysql ${db_name} at ${date_now}."
#进入到备份文件目录
cd ${backUpFolder}
/usr/bin/mysqldump -u${username} -p${password}  --lock-all-tables --flush-logs ${db_name} > ${backUpFileName}
#压缩备份文件
tar zcvf ${fileName}.tar.gz ${fileName} --remove-files

# use nodejs to upload backup file other place
#NODE_ENV=$backUpFolder@$backUpFileName node /home/tasks/upload.js
date_end=$(date "+%Y%m%d-%H%M%S")
echo "finish backup mysql database ${db_name} at ${date_end}."

# 使用 python 上传备份文件到 私有云
python /home/ubuntu/aiplat/upload.py $backUpFolder/ ${fileName}.tar.gz

增量备份，这里还是需要开启mysql的bin-log功能的，看参考文献：

backupDir=/home/db/backup/mysql-daily
#增量备份时复制mysql-bin.00000*的目标目录，提前手动创建这个目录
mysqlDir=/var/lib/mysql
#mysql的数据目录
logFile=/home/ubuntu/aiplat/increment.log
BinFile=/var/lib/mysql/mysql-bin.index
#mysql的index文件路径，放在数据目录下的

mysqladmin -uroot -pxtionai flush-logs
#这个是用于产生新的mysql-bin.00000*文件
# wc -l 统计行数
# awk 简单来说awk就是把文件逐行的读入，以空格为默认分隔符将每行切片，切开的部分再进行各种分析处理。
Counter=`wc -l $BinFile |awk '{print $1}'`
NextNum=0
#这个for循环用于比对$Counter,$NextNum这两个值来确定文件是不是存在或最新的
for file in `cat $BinFile`
do
   base=`basename $file`
   echo $base
   #basename用于截取mysql-bin.00000*文件名，去掉./mysql-bin.000005前面的./
   NextNum=`expr $NextNum + 1`
   if [ $NextNum -eq $Counter ]
   then
       echo $base skip! >> $logFile
   else
       dest=$backupDir/$base
       if(test -e $dest)
       #test -e用于检测目标文件是否存在，存在就写exist!到$logFile去
       then
           echo $base exist! >> $logFile
       else
           cp $mysqlDir/$base $backupDir
           echo $base copying >> $logFile
           # 使用 python 上传备份文件到 私有云
       echo $backupDir/$base >> $logFile
           python /home/ubuntu/aiplat/upload.py $backupDir/ $base
        fi
    fi
done
echo `date +"%Y年%m月%d日 %H:%M:%S"` $Next Bakup succ! >> $logFile
#执行上传备份文件到七牛云
#NODE_ENV=$backUpFolder@$backUpFileName /root/node/v8.11.3/bin/node /usr/local/upload.js
# 使用 python 上传备份文件到 私有云
#python /home/ubuntu/aiplat/upload.py $backupDir $backUpFileName

上传脚本，python写的，版本随意：

from qiniu import Auth, put_file, etag
import sys
print('参数个数为:', len(sys.argv), '个参数。')
print('参数列表:', str(sys.argv))

# backUpFolder
backUpFolder = sys.argv[1]
# backUpFileName
backUpFileName = sys.argv[2]
import qiniu.config

# 需要填写你的 Access Key 和 Secret Key
access_key = 'xxx'
secret_key = 'xxx'
# 构建鉴权对象
q = Auth(access_key, secret_key)
# 要上传的空间
bucket_name = 'aiimage'
# 上传到七牛后保存的文件名
key = backUpFileName
# 生成上传 Token，可以指定过期时间等
token = q.upload_token(bucket_name, key, 3600)
# 要上传文件的本地路径
localfile = backUpFolder + backUpFileName
ret, info = put_file(token, key, localfile)
print(info)
assert ret['key'] == key
assert ret['hash'] == etag(localfile)

最后设置crontab 定时任务，输入命令crontab -e设置下面的就好了：

0 0 * * 0 sudo sh /home/dbbackup/all.sh >/home/dbbackup/cron.log
0 0 * * * sudo sh /home/dbbackup/increment.sh >/home/dbbackup/cron.log

主从架构

参考文章：https://blog.csdn.net/qq_22152261/article/details/80374990
分三个步骤：
1、主从都开启bin-log
2、主设置帐号权限组给从用(记得指定IP)
3、从设置主的信息并启动slave模式

主设置的命令：

GRANT REPLICATION SLAVE ON *.* TO 'user'@'X.X.X.X' IDENTIFIED BY 'password';

从设置的命令：

change master to master_host='x.x.x.x', master_user='root', master_password='123456', master_log_file='mysql-bin.000003',master_log_pos=669835806,master_port=3307; start slave; 

命令中的master_log取决于主执行命令show master status;出来的数据。

自己去主库测试下，新建个数据库就可以了，从库也会跟着建。

热备份

在一主多从的架构之下，理论上主宕机之后，必须马上选举一个从来充当主的角色，而这种行为叫做热备。
而这个切换的过程分为手动和自动，但是都没办法避免一个问题：第三方工具可以推举出新的主，但是没办法通知应用层。

目前是一般手动更新主库，也手动更新应用层的数据库配置。

读写分离

在一主多从的架构之下，读写分离成为可能，主为写库，从为读库。
使用SpringBoot + Sharding-jdbc实现读写分离，分为三步：
1、设置好主从库；
2、引入maven包；
3、写好yaml配置文件

一般推荐用docker+mysql做主从库，方便！参考文章：https://www.cnblogs.com/sweetchildomine/p/7814692.html

我的docker启动命令：

#slave0 是从库
docker run -d -e MYSQL_ROOT_PASSWORD=123456  -v /home/mysql/cnf/s0.cnf:/etc/mysql/my.cnf -v /home/mysql/slave0_data:/var/lib/mysql -p 3308:3306 mysql:5.7

#master是主库
docker run -d -e MYSQL_ROOT_PASSWORD=123456  -v /home/mysql/cnf/m.cnf:/etc/mysql/my.cnf -v /home/mysql/master_data:/var/lib/mysql -p 3307:3306 mysql:5.7

我的my.cnf文件内容，主从库一模一样，除了serverid不能一样！

# Copyright (c) 2014, Oracle and/or its affiliates. All rights reserved.
#
# This program is free software; you can redistribute it and/or modify
# it under the terms of the GNU General Public License as published by
# the Free Software Foundation; version 2 of the License.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
# GNU General Public License for more details.
#
# You should have received a copy of the GNU General Public License
# along with this program; if not, write to the Free Software
# Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA  02110-1301 USA

#
# The MySQL Community Server configuration file.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html

[client]
port        = 3306
socket      = /var/run/mysqld/mysqld.sock

[mysqld_safe]
pid-file    = /var/run/mysqld/mysqld.pid
socket      = /var/run/mysqld/mysqld.sock
nice        = 0

[mysqld]
user        = mysql
pid-file    = /var/run/mysqld/mysqld.pid
socket      = /var/run/mysqld/mysqld.sock
port        = 3306
basedir     = /usr
datadir     = /var/lib/mysql
tmpdir      = /tmp
lc-messages-dir = /usr/share/mysql
explicit_defaults_for_timestamp

log-bin = mysql-bin 
server-id = 10001 

# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
#bind-address   = 127.0.0.1

#log-error  = /var/log/mysql/error.log

# Recommended in standard MySQL setup
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

# * IMPORTANT: Additional settings that can override those from this file!
#   The files must end with '.cnf', otherwise they'll be ignored.
#
!includedir /etc/mysql/conf.d/

引入maven包：

HikariCP连接池是必须的，所以我们需要剔除掉默认的tomcat-jdbc连接池。

        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>1.3.1</version>
            <exclusions>
                <exclusion>
                    <groupId>org.apache.tomcat</groupId>
                    <artifactId>tomcat-jdbc</artifactId>
                </exclusion>
            </exclusions>
        </dependency>
        <dependency>
            <groupId>io.shardingsphere</groupId>
            <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
            <version>3.0.0</version>
        </dependency>
        <dependency>
            <groupId>com.zaxxer</groupId>
            <artifactId>HikariCP</artifactId>
        </dependency>

后面就是写yml配置了：

具体配置的解释看官网：http://shardingsphere.io/document/current/cn/overview/

sharding:
  jdbc:
    datasource:
      names: master,slave0
      master:
        type: com.zaxxer.hikari.HikariDataSource
        driver-class-name: com.mysql.jdbc.Driver
        jdbcUrl: jdbc:mysql://127.0.0.1:3307/aiplattest?useUnicode=true&characterEncoding=utf-8&useSSL=false
        username: root
        password: 123456
      slave0:
        type: com.zaxxer.hikari.HikariDataSource
        driver-class-name: com.mysql.jdbc.Driver
        jdbcUrl: jdbc:mysql://127.0.0.1:3308/aiplattest?useUnicode=true&characterEncoding=utf-8&useSSL=false
        username: root
        password: 123456
    config:
      masterslave:
        load-balance-algorithm-type: round_robin
        name: ds_ms
        master-data-source-name: master
        slave-data-source-names:
          - slave0
        props:
          sql:
            show: true

数据分片

数据分片：指的是 垂直分库 和 水平分表。

目前主流分库分表框架是sharding-jdbc和mycat，核心就是开发者不需要管分了多少个库，多少个表，对于开发只有多个库单个表，就是物理上的库和逻辑上的表。
比如：订单库的order表分成order_01,order_02,order_03,我们用的时候直接就是用order表就好了，剩下的由框架去组合，当然这个也是要配置的，参考官网配置http://shardingsphere.io/document/current/cn/overview/
。

dataSources: #数据源配置，可配置多个data_source_name
  <data_source_name>: #<!!数据库连接池实现类> `!!`表示实例化该类
    driverClassName: #数据库驱动类名
    url: #数据库url连接
    username: #数据库用户名
    password: #数据库密码
    # ... 数据库连接池的其它属性

shardingRule:
  tables: #数据分片规则配置，可配置多个logic_table_name
    <logic_table_name>: #逻辑表名称
      actualDataNodes: #由数据源名 + 表名组成，以小数点分隔。多个表以逗号分隔，支持inline表达式。缺省表示使用已知数据源与逻辑表名称生成数据节点。用于广播表（即每个库中都需要一个同样的表用于关联查询，多为字典表）或只分库不分表且所有库的表结构完全一致的情况
        
      databaseStrategy: #分库策略，缺省表示使用默认分库策略，以下的分片策略只能选其一
        standard: #用于单分片键的标准分片场景
          shardingColumn: #分片列名称
          preciseAlgorithmClassName: #精确分片算法类名称，用于=和IN。。该类需实现PreciseShardingAlgorithm接口并提供无参数的构造器
          rangeAlgorithmClassName: #范围分片算法类名称，用于BETWEEN，可选。。该类需实现RangeShardingAlgorithm接口并提供无参数的构造器
        complex: #用于多分片键的复合分片场景
          shardingColumns: #分片列名称，多个列以逗号分隔
          algorithmClassName: #复合分片算法类名称。该类需实现ComplexKeysShardingAlgorithm接口并提供无参数的构造器
        inline: #行表达式分片策略
          shardingColumn: #分片列名称
          algorithmInlineExpression: #分片算法行表达式，需符合groovy语法
        hint: #Hint分片策略
          algorithmClassName: #Hint分片算法类名称。该类需实现HintShardingAlgorithm接口并提供无参数的构造器
        none: #不分片
      tableStrategy: #分表策略，同分库策略
        
      keyGeneratorColumnName: #自增列名称，缺省表示不使用自增主键生成器
      keyGeneratorClassName: #自增列值生成器类名称。该类需实现KeyGenerator接口并提供无参数的构造器
        
      logicIndex: #逻辑索引名称，对于分表的Oracle/PostgreSQL数据库中DROP INDEX XXX语句，需要通过配置逻辑索引名称定位所执行SQL的真实分表
  bindingTables: #绑定表规则列表
  - <logic_table_name1, logic_table_name2, ...> 
  - <logic_table_name3, logic_table_name4, ...>
  - <logic_table_name_x, logic_table_name_y, ...>
  
  defaultDataSourceName: #未配置分片规则的表将通过默认数据源定位  
  defaultDatabaseStrategy: #默认数据库分片策略，同分库策略
  defaultTableStrategy: #默认表分片策略，同分库策略
  defaultKeyGeneratorClassName: #默认自增列值生成器类名称，缺省使用io.shardingsphere.core.keygen.DefaultKeyGenerator。该类需实现KeyGenerator接口并提供无参数的构造器
  
  masterSlaveRules: #读写分离规则，详见读写分离部分
    <data_source_name>: #数据源名称，需要与真实数据源匹配，可配置多个data_source_name
      masterDataSourceName: #详见读写分离部分
      slaveDataSourceNames: #详见读写分离部分
      loadBalanceAlgorithmClassName: #详见读写分离部分
      loadBalanceAlgorithmType: #详见读写分离部分
      configMap: #用户自定义配置
          key1: value1
          key2: value2
          keyx: valuex
  
  props: #属性配置
    sql.show: #是否开启SQL显示，默认值: false
    executor.size: #工作线程数量，默认值: CPU核数
    
  configMap: #用户自定义配置
    key1: value1
    key2: value2
    keyx: valuex