mysql表空间碎片整理会锁表时间 mysql表空间自动扩容

转载

恋上一只猪 2023-10-12 22:51:15

文章标签 mysql表空间碎片整理会锁表时间 mycat实现mysql大表自动水平扩展 mysql xml 数据 文章分类 MySQL 数据库

1.mycat是一种数据库中间件，详细信息访问官网http://www.mycat.org.cn/

2.架构图

mysql表空间碎片整理会锁表时间 mysql表空间自动扩容_mycat实现mysql大表自动水平扩展

从上图可以看出：原来架构单服务器单库单表向多服务器多库多表转换

这样做的好处是：

解决单台服务器始终存在单个文件大小上限。无论是共享或独占方式的表空间，mysql数据文件大小受操作系统限制，不能无限增长
解决单台服务器资源上限。单台机器的硬件资源不可能无限扩展。
解决成本问题。1台昂贵的小型机的价格>20台普通服务器价格
解决性能瓶颈。
解决大表在线DDL操作难。

实现思路：利用mycat强大的分表功能，首先配置3个节点用于分表，每个节点写500w数据，其中1个节点做为扩展接点，如果扩展节点被写数据那么，自动配置mycat增加数据节点。所以，始终保持至少有1个空数据节点。注意：一个server可以有多个数据节点

3.mycat配置

schema.xml配置 
                 <?xml version="1.0"?> 
                 <!DOCTYPE mycat:schema SYSTEM "schema.dtd"> 
                 <mycat:schema xmlns:mycat="http://org.opencloudb/"> 
                         <schema name="transdb" checkSQLschema="false"> 
                                <table name="orders" primaryKey="id" autoIncrement="true" rule="auto-sharding-long-orders" dataNode="dn1,dn2,dn3" /> 
                         </schema> 
                  
                         
<dataNode name="dn1" dataHost="localhost1" database="transdb0" />                         <dataNode name="dn2" dataHost="localhost2" database="transdb1" />                         <dataNode name="dn3" dataHost="localhost3" database="transdb2" /> 
                  
                         <dataHost name="localhost1" maxCon="100000" minCon="10" balance="0" 
                                 writeType="0" dbType="mysql" dbDriver="native"> 
                                 <heartbeat>select user()</heartbeat> 
                                 <writeHost host="hostM1" url="db1:3306" user="root" password="123456"></writeHost> 
                         </dataHost> 
                         <dataHost name="localhost2" maxCon="100000" minCon="10" balance="0" 
                                 writeType="0" dbType="mysql" dbDriver="native"> 
                                 <heartbeat>select user()</heartbeat> 
                                 <!--heartbeat>select user()</heartbeat--> 
                                 <writeHost host="hostS1" url="db2:3306" user="root" password="123456"></writeHost> 
                         </dataHost> 
                         <dataHost name="localhost3" maxCon="100000" minCon="10" balance="0" 
                                 writeType="0" dbType="mysql" dbDriver="native"> 
                                 <heartbeat>select user()</heartbeat> 
                                 <!--heartbeat>select user()</heartbeat--> 
                                 <writeHost host="hostS2" url="db3:3306" user="root" password="123456"></writeHost> 
                         </dataHost> 
                 </mycat:schema> 
 rule.xml

在rule.xml 添加如下内容（分表规则和算法）

<tableRule name="auto-sharding-long-orders"> 
                             <rule> 
                                     <columns>id</columns> 
                                     <algorithm>rang-long-orders</algorithm> 
                             </rule> 
                     </tableRule>    
                      <function name="rang-long-orders" 
                                 class="org.opencloudb.route.function.AutoPartitionByLong"> 
                                 <property name="mapFile"> autopartition-long-orders.txt</property> 
                      </function> 
 autopartition-long-orders.txt

在conf目录中新增 autopartition-long-orders.txt文件，用于rang-long-orders算法选择数据接点，内容如下（0,1,2分别对应schema.xml的dn1,dn2,dn3）：

# range start-end ,data node index 
             # K=1000,M=10000. 
             0-500M=0 
             5000001-1000M=1 
             10000001-1500M=2 
 sequence_conf.properties

在sequence_conf.properties添加全局自增序列，用于orders表自增id（调用方式：next value for MYCATSEQ_ORDER），添加如下内容：

ORDER.CURID=0 
                 ORDER.HISIDS= 
                 ORDER.MINID=0 
                 ORDER.MAXID=1000000000

server.xml配置如下：

<?xml version="1.0" encoding="UTF-8"?> 
             <!DOCTYPE mycat:server SYSTEM "server.dtd"> 
             <mycat:server xmlns:mycat="http://org.opencloudb/"> 
                     <system> 
                             <property name="defaultSqlParser">druidparser</property> 
                             <property name="sequnceHandlerType">0</property> 
                             <property name="serverPort">3307</property> <property name="managerPort">9067</property> 
                     </system> 
                     <user name="mycat"> 
                             <property name="password">123456</property> 
                             <property name="schemas">transdb</property> 
                     </user> 
                     <quarantine> 
                        <whitehost> 
                           <host host="127.0.0.1" user="mycat"/> 
                           <host host="192.168.2.27" user="mycat"/> 
                           <host host="192.168.2.45" user="mycat"/> 
                        </whitehost> 
                    <blacklist check="false"></blacklist> 
                     </quarantine> 
             </mycat:server>

4.在每个节点上创建数据库和表(数据库编号dn1=>transdb0,dn2=>transdb1....)

create database 
transdb0; 
             use 
transdb0; 
             create table orders (id int(11) NOT NULL AUTO_INCREMENT primary key,user_id int,pay decimal(22,2),goods_name char(50));

5.启动mycat

bin/mycat start

6.测试分表功能

插入数据python脚本

#!/bin/env python 
                 #-*-coding:utf-8-*- 
                  
                 import MySQLdb 
                 from multiprocessing import Process; 
                 import random 
                  
                 def mysql_exec(): 
                     host='192.168.2.155' 
                     port=3307 
                     user='mycat' 
                     passwd='123456' 
                     dbname='transdb' 
                     goods_name=random.choice(["IPONE6S", "APPLE", "IPONE7S", "IPONE8S", "IPONE9S","IPONE6P", "APPLE1", "IPONE7P", "IPONE8P", "IPONE9P"]) 
                     pay=round(random.uniform(100, 10000),2) 
                     user_id=random.randint(1, 20000) 
                     sql="insert into orders(id,user_id,pay,goods_name)values(next value for MYCATSEQ_ORDER ,%s ,%s ,%s);" 
                     param=(user_id,pay,goods_name) 
                     try: 
                         conn=MySQLdb.connect(host=host,user=user,passwd=passwd,port=int(port),connect_timeout=1,charset='utf8') 
                         conn.select_db(dbname) 
                         curs = conn.cursor() 
                         if param <> '': 
                             curs.execute(sql,param) 
                         else: 
                             curs.execute(sql) 
                         conn.commit() 
                         curs.close() 
                         conn.close() 
                     except Exception,e: 
                        print "mysql execute: " + str(e) 
                 def main(): 
                         i=0 
                         while i<10000000: 
                             mysql_exec() 
                             i=i+1 
                 if __name__=='__main__': 
                     main()

登录mycat，查询所有节点数据

mysql -umycat -p123456 -h192.168.2.155 transdb -P3307 
             mysql> select count(1) from orders; 
             +---------+ 
             | COUNT0  | 
             +---------+ 
             | 7127006 | 
             +---------+

查询单个节点数据

节点1：

mysql -uroot -p -hdb1 transdb0 
             mysql> select count(1) from orders; 
             +----------+ 
             | count(1) | 
             +----------+ 
             |  5000000 | 
             +----------+ 
             1 row in set (1.09 sec) 
              
             mysql> select min(id),max(id) from orders; 
             +---------+---------+ 
             | min(id) | max(id) | 
             +---------+---------+ 
             |       1 | 5000000 | 
             +---------+---------+ 
             1 row in set (0.00 sec)

节点2：

mysql -uroot -p -hdb2 transdb1 
             mysql> select min(id),max(id) from orders; 
             +---------+---------+ 
             | min(id) | max(id) | 
             +---------+---------+ 
             | 5000001 | 7137006 | 
             +---------+---------+

7.自动扩展

schema.xml配置，红色字体为增加内容

<?xml version="1.0"?> 
                 <!DOCTYPE mycat:schema SYSTEM "schema.dtd"> 
                 <mycat:schema xmlns:mycat="http://org.opencloudb/"> 
                         <schema name="transdb" checkSQLschema="false"> 
                                 <table name="orders" primaryKey="id" autoIncrement="true" rule="auto-sharding-long-orders" dataNode="dn1,dn2,dn3 ,dn4" /> 
                         </schema> 
                  
                         <dataNode name="dn1" dataHost="localhost1" database="transdb0" /> 
                         <dataNode name="dn2" dataHost="localhost2" database="transdb1" /> 
                         <dataNode name="dn3" dataHost="localhost3" database="transdb2" /> 
                        <dataNode name="dn1" dataHost="localhost1" database="transdb3" /> 
 autopartition-long-orders.txt，红色字体为增加内容 
   # range start-end ,data node index 
             # K=1000,M=10000. 
             0-500M=0 
             5000001-1000M=1 
             10000001-1500M=2 
            15000001-2000M=3

在扩展节点上创建数据库和表

create database 
transdb3; 
             use 
transdb3; 
             create table orders (id int(11) NOT NULL AUTO_INCREMENT primary key,user_id int,pay decimal(22,2),goods_name char(50));

重启mycat（实际上可以登录server.xml配置中的9067端口进行reload，但是该功能貌似不完善，restart更稳妥，但是会断开连接）

8.扩展节点注意

一定要有一个备用的空节点，防止现有节点数据满了，插入报错：(1064, "can't find any valid datanode :ORDERS -> ID -> 5000001")
扩展节点如果与旧节点在相同的server上，应该确缓冲池可以加载所有节点数据页
分表算法难以保证数据节点易扩展和访问热点分散，这个案例是才用范围分片算法，存在热点问题，即，可能80%的查询集中在最后一个数据节点
每个节点的数据量并不是严格对应autopartition-long-orders.txt文件中的范围划分，实际上这个范围对应的是分片键的值。（这里是orders的id值）

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。