• 垂直分库

1. 收集分析业务模块间的关系
2. 复制数据库到其他实例
3. 配置mycat垂直分库配置信息
4. 配置mycat访问db
5. 删除原库中已迁移的表

 

• 收集分析业务模块间的关系

垂直分表的优点：数据库维护简单，定位容易，拆分规则明确模块清晰 ；缺点：如果业务间表有关联关系的话，使用mycat做关联查询的话比较麻烦，方法一是冗余数据，方法二是建立全局表，方法三是程序用api关联信息。对数据量大的表依然存在瓶颈，只是解决部分写负载问题。

• 复制数据库到其他实例，开启主从复制，保证数据一致性

备份原数据库并记录相关事务点

#配置主从复制 修改log-bin=mysql-bin ，有必要配置下expire_logs_days = 7 日志保留事件
#修改主服务器master
vi /etc/my.cnf
[mysqld]
log-bin=mysql-bin   //[必须]启用二进制日志
server-id=222      //[必须]服务器唯一ID，默认是1，一般取IP最后一段
#修改从服务器slave:
#vi /etc/my.cnf
[mysqld]
log-bin=mysql-bin   //[不是必须]启用二进制日志
server-id=226      //[必须]服务器唯一ID，默认是1，一般取IP最后一段
#重启服务
/etc/init.d/mysql restart
#使用二进制数据  保证数据一致性 routines 备份存储过程 triggers触发器 events 事件
mysqldump --master-data=2 --single-transaction --routines --triggers --events -uroot -p imooc_db>bak_imooc.sql
#然后打开备份文件，查看MASTER_LOG_FILE,MASTER_LOG_POS信息用户还原数据用
vim bak_imooc.sql

1. 在原数据库中建立复制用户

#创建主从复制用户,刷新权限
create user 'im_repl'@'192.168.1.%' identified by '123456';
FLUSH PRIVILEGES; 
#赋予主从复制权限
grant replication slave on  *.* to 'im_repl'@'%192.168.1.%'; 

1. 在新实例上回复备份数据库

#复制数据库
mysql -uroot -p order_db<bak_iimooc.sql

1. 在新实例上配置复制链路并启动复制

#查看帮助
\h change master to
#设置通需要同步的主机 用导出sql文件的master_log_file master_log_pos
change master to master_host='129.204.66.135',master_user='im_repl',master_password='123456',master_log_file='mysql-bin.000002',master_log_pos=3056;
#查看主从复制状态
show slave status\G
#主复制名和从数据库名不同（主库名，副库名） 复制链路前修改过滤信息 版本要5.7
change replication filter replicate_rewire_db=((imooc_db,order_db));
#查看主从链路状态
show slave status\G
#启动复制链路
#start slave;

• 配置mycat垂直分库配置信息

1. 配置schema.xml逻辑库信息

<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
	<!--检查SQL中是否含库名 mysql不支持直接夸物理数据库的访问 用户自己检查-->
    <schema name="imooc_db" checkSQLschema="false" sqlMaxLimit="100">
        <table name="order_master" dataNode="orderdb01,orderdb02,orderdb03,orderdb04" rule="order_master" primaryKey="order_id" autoIncrement="true">
            <childTable name="order_detail" joinKey="order_id" parentKey="order_id" primaryKey="order_detail_id" autoIncrement="true"/>
        </table>
	<!--name和物理库相同 如果分片键不是主键则会记录主键信息，分片查询时，再次发起查询时，速度会比较快
	     dataNode节点顺序就是分片规则的节点顺序，一旦定义不要轻易修改，不然数据会混乱-->
        <table name="order_cart" dataNode="ordb" primaryKey="cart_id"/>
        <table name="order_customer_addr" dataNode="ordb" primaryKey="customer_addr_id"/>
         <!-- global全局表，-->
	<table name="region_info" dataNode="ordb,prodb,cusdb" primaryKey="region_id" type="global"/>
        <table name="shipping_info" dataNode="ordb" primaryKey="ship_id"/>
        <table name="warehouse_info" dataNode="ordb" primaryKey="w_id"/>
        <table name="warehouse_product" dataNode="ordb" primaryKey="wp_id"/>
        <table name="product_brand_info" dataNode="prodb" primaryKey="brand_id"/>
        <table name="product_category" dataNode="prodb" primaryKey="category_id"/>
        <table name="product_comment" dataNode="prodb" primaryKey="comment_id"/>
        <table name="product_info" dataNode="prodb" primaryKey="product_id"/>
        <table name="product_supplier_info" dataNode="prodb" primaryKey="supplier_id"/>
        <table name="product_pic_info" dataNode="prodb" primaryKey="product_pic_id"/>
        <table name="customer_balance_log" dataNode="cusdb" primaryKey="balance_id"/>
        <table name="customer_inf" dataNode="cusdb" primaryKey="customer_inf_id"/>
        <table name="customer_level_inf" dataNode="cusdb" primaryKey="customer_level"/>
        <table name="customer_login" dataNode="cusdb" primaryKey="customer_id"/>
        <table name="customer_login_log" dataNode="cusdb" primaryKey="login_id"/>
        <table name="customer_point_log" dataNode="cusdb" primaryKey="point_id"/>
    </schema>
    <dataNode name="ordb" dataHost="mysqlS2" database="order_db"/>
    <dataNode name="prodb" dataHost="mysqlS1" database="product_db"/>
    <dataNode name="cusdb" dataHost="mysqlS3" database="customer_db"/>
    <dataNode name="orderdb03" dataHost="mysqlM1" database="orderdb03"/>
    <dataNode name="orderdb04" dataHost="mysqlM1" database="orderdb04"/>
    <dataNode name="orderdb01" dataHost="mysqlS3" database="orderdb01"/>
    <dataNode name="orderdb02" dataHost="mysqlS3" database="orderdb02"/>
    <dataNode name="mycat" dataHost="mysqlM1" database="mycat"/>
	<!-- mysql一主一从是可以保证写的高可用的 连接池连接数
	     balance 0 不开启读写分离 1 发送到当前的writeHost对应的readHost和备用的writeHost
               2.所有的读操作都随机发送到所有的writeHost,readHost上
 		3.所有的读操作都只发送到writeHost的readHost上
           wtireType 0 写主机挂了，才会发送到另一台写主机  1 写请求随机放到写请求 （pxc集群多写mysql集群的时候才能用，不然出问题）
	   dbtype 支持sql类型 dbDrive native mysql原生的驱动 jdbc 用来连接其他数据库
	   switchType 1 只要一台挂了，另一台自动切换 ，-1则不启动 后台数据库使用一主多从集群mha 3m 主从复制高可用工具，这里就可以设置-1
	 -->
    <dataHost balance="3" maxCon="1000" minCon="10" name="mysqlM1" writeType="0" switchType="1" dbType="mysql" dbDriver="native">
        <heartbeat>select user()</heartbeat>
        <writeHost host="hostM1" url="129.204.66.6:3306" password="qqqwww" user="im_mycat"/>
        	<readHost host="hostS1" url="47.107.218.8:3306" password="123456" user="im_mycat"/>
         </writeHost>
         <writeHost host="hostS1" url="47.107.218.8:3306" password="qqqwww" user="im_mycat"/>
    </dataHost>
    <dataHost balance="3" maxCon="1000" minCon="10" name="mysqlS1" writeType="0" switchType="1" dbType="mysql" dbDriver="native">
        <heartbeat>select user()</heartbeat>
        <writeHost host="hostS1" url="47.107.149.9:3306" password="qqqwww" user="im_mycat"/>
    </dataHost>
    <dataHost balance="3" maxCon="1000" minCon="10" name="mysqlS2" writeType="0" switchType="1" dbType="mysql" dbDriver="native">
        <heartbeat>select user()</heartbeat>
        <writeHost host="hostS2" url="120.79.218.8:3306" password="qqqwww" user="im_mycat"/>
    </dataHost>
    <dataHost balance="3" maxCon="1000" minCon="10" name="mysqlS3" writeType="0" switchType="1" dbType="mysql" dbDriver="native">
        <heartbeat>select user()</heartbeat>
        <writeHost host="hostS3" url="118.25.89.1:3306" password="qqqwww" user="im_mycat"/>
    </dataHost>
</mycat:schema>

1. 配置server.xml的环境变量和用户权限信息

<mycat:server xmlns:mycat="http://io.mycat/">
    <system>
        <!--charset 字符集与mysql需要一直 -->
	<!--sqlExecuteTimeout sql超时断开连接 默认单位秒 -->
        <property name="nonePasswordLogin">0</property> <!--0 需要密码，1 不需要-->
        <property name="useSqlStat">0</property>
        <property name="useGlobleTableCheck">0</property>
        <property name="processors">2</property><!--线程数-->
        <property name="sequnceHandlerType">1</property>
        <property name="txIsolation">2</property><!--隔离级别 1,读未提交 2 读已提交 ，3可重读，4序列化-->
        <property name="defaultMaxLimit">100</property><!--mycat 默认数据集大小 分布式一般数据量比较大 没配置默认返回这个数据数 -->
        <property name="maxPacketSize">104857600</property> <!--mysql包数据大小-->
        <property name="serverPort">8066</property><!--mycat 端口 -->
        <property name="managerPort">9066</property><!--mycat 管理端口 -->
        <property name="idleTimeout">1800000</property><!--前端多久没有访问，会断开连接，mycat连接池更好提供服务 毫秒-->
        <property name="bindIp">0.0.0.0</property><!--网卡ip-->
        <property name="frontWriteQueueSize">2048</property><!--前端写队列大小-->
        <property name="sqlExecuteTimeout">300</property>
        <property name="sqlInterceptor">io.mycat.server.interceptor.impl.StatisticsSqlInterceptor</property>
        <property name="sqlInterceptorType">UPDATE,DELETE,INSERT</property>
        <property name="sqlInterceptorFile">/tmp/sqllog.txt</property>
    </system>
    <user name="app_mycat">
	<!--java -cp Mycat-server-1.6.5-release.jar io.mycat.util.DecryptUtil 0:root:123456 获取加密密码 -->
        <property name="password">aI1N4myKYg6oMXmWvWuDQvpOMbEkhnHIWDRPj3z3u6vaFUXZm67kH9MP8G4bvhtSK7a9Z0InTTOGsfw==</property>
        <property name="usingDecrypt">1</property>
        <property name="schemas">imooc_db</property>
    </user>
<!--
      配置用户访问权限
	<user name="app_mycat" defaultAccount="true"> 表示默认账号
        <property name="schemas">imooc_db</property>
        <property name="password">aI1N4myKYg6oMXmWvWuDQvpOMbEkhnHIWDRPj3z3u6vaFUXZm67kH9MP8G4bvhtSK7a9Z0InTTOGsfw==</property>
        <property name="usingDecrypt">1</property>
		<privileges check="true">       
        	  <schema name="imooc_db" dml="0110"> 数字对应insert,update,select,delete操作 0是没有权限
			<table  name="table1" dml="0000"></table>
			<table  name="table2" dml="1111"></table>
    		  </schema>
		</privileges>
	</user>
-->
</mycat:server>

• 删除原库中已迁移的表

#停止主从复制
#stop slave
#清除主从复制信息
#reset slave all
#删除表
drop table xx

• 应用程序切换mycat数据库连接信息
• 如果通过全局表解决数据关联问题

#导出表
mysqldump  -uroot -p imooc_db region_info>region_info
#导入表
mysql -uroot -p customer_db<region_info
#全局表要用mycat操作，不然数据会不一致
#mycat stop 重启
#查看是否数据库只读
show variables like 'read_only';
#关掉readonly属性
set global read_only=off
#然后在schema.xml逻辑表加上全局表类型

• 用到的相关其他命令

#直接创建数据库
mysql -u root -p -e"create database imooc_db" 
#复制文件，-p可以不用
scp -p 4588 remote@www.abc.com:/usr/local/sin.sh /home/administrator
#查看主表信息
show master status
#防火墙查看关闭
iptables -L;
/etc/init.d/iptables stop;
#删除mysql
yum remove mysql*,yum list installed | grep mysql
#停止mysql服务
systemctl stop mysqld.service