1.mysql分庫分表:解決單個mysql存儲上限問題1.實現(xiàn)方法:存儲層面:利用分布式存儲解決方案分庫分表:拆分庫和表到其它服務(wù)器2.常用設(shè)計思路:垂直分庫(庫里面的表分開)+水平分表(表里面的數(shù)據(jù)分開)分庫:數(shù)據(jù)庫分為多個,每個數(shù)據(jù)庫里面都有表,數(shù)據(jù)均勻存儲分庫分表:在分的每庫里面,進行分表,數(shù)據(jù)均勻存儲到各表3.分片規(guī)則MOD_HASHUNI_HASHRIGHT_SHIFTYYYYMMWEEK...4.架構(gòu)客戶端分片服務(wù)器主                主從                從5.分庫分表操作分類:(1)分片表,(2)ER表:針對具有關(guān)聯(lián)關(guān)系的表,對于沒有聲明分庫分表的邏輯,也會參考復(fù)雜表的分庫分表邏輯(3)全局表:所有庫都有表的全部數(shù)據(jù)(副本)語法:dbpartition by 規(guī)則(字段)tbpartitino by 規(guī)則(字段)mode_hash() 取余哈系分片規(guī)則tbpartitions 表分片數(shù)量dbpartitions 庫分片數(shù)量broadcast:全局表6.分庫分表搭建步驟1.配置主從同步2.裝mycat包,以及依賴包3.配置mycat,users的用戶4.配置本機datasource數(shù)據(jù)源用戶,創(chuàng)建用戶授權(quán)5.mycat中,配置datasource數(shù)據(jù)源連接其它主機,mysql的主服務(wù)器創(chuàng)建plj用戶//添加MySQL59主機MySQL>/*+ mycat:createdatasource{"name":"dw0","url":"jdbc:mysql://192.168.88.59:3306","user":"plj","password":"123456"}*/;//添加MySQL60主機//添加MySQL61主機//添加MySQL62主機6.mycat上搭建2個主從結(jié)構(gòu)的集群c0,c1(分庫分表的集群名稱必須定義為c0,c1...)mysql>/*! mycat:createcluster{"name":"c0","masters":["dw0"], #主服務(wù)器名"replicas":["dr0"] #從服務(wù)器名}*/;...7.分片操作(1)mycat上建立數(shù)據(jù)庫,不需要配置邏輯庫.mysql -h192.168.88.63 -P8066 -umycat –p654321create database tarena;(2)mycat,建表,分片create table tarena.employees(employee_id  int  primary key,name char(10),dept_id int , mail varchar(30)) dbpartition BY mod_hash(字段) tbpartition BY mod_hash(employee_id) dbpartitions 2tbpartitions 1;(3)mycat,插入數(shù)據(jù)測試mysql -h192.168.88.63 -P8066 -umycat –p654321insert into tarena.employees values (9,"jim","1","jim@163.com");insert into tarena.employees values (8,"tom","3","tom@QQ.com");insert into tarena.employees values (7,"lucy","2","lucy@QQ.com");insert into tarena.employees values (6,"john","2","john@QQ.com");

3.mysql分庫分表:解決單個mysql存儲上限問題

?? ?1.實現(xiàn)方法:
?? ??? ?存儲層面:利用分布式存儲解決方案
?? ??? ?分庫分表:拆分庫和表到其它服務(wù)器

?? ?2.常用設(shè)計思路:
?? ??? ?垂直分庫(庫里面的表分開)+水平分表(表里面的數(shù)據(jù)分開)

?? ??? ?分庫:數(shù)據(jù)庫分為多個,每個數(shù)據(jù)庫里面都有表,數(shù)據(jù)均勻存儲
?? ??? ?分庫分表:在分的每庫里面,進行分表,數(shù)據(jù)均勻存儲到各表

?? ?3.分片規(guī)則
?? ??? ?MOD_HASH
?? ??? ?UNI_HASH
?? ??? ?RIGHT_SHIFT
?? ??? ?YYYYMM
?? ??? ?WEEK
?? ??? ?...
?? ??? ?
?? ?4.架構(gòu)
?? ??? ??? ? 客戶端
?? ??? ??? ?分片服務(wù)器
?? ??? ?主 ? ? ? ? ? ? ? ?主
?? ??? ?從 ? ? ? ? ? ? ? ?從

?? ?5.分庫分表操作
?? ??? ?分類:(1)分片表,
?? ??? ? ? ?(2)ER表:針對具有關(guān)聯(lián)關(guān)系的表,對于沒有聲明分庫分表的邏輯,也會參考復(fù)雜表的分庫分表邏輯
?? ??? ? ? ?(3)全局表:所有庫都有表的全部數(shù)據(jù)(副本)
?? ??? ?語法:
?? ??? ??? ?dbpartition by 規(guī)則(字段)
?? ??? ??? ?tbpartitino by 規(guī)則(字段)
?? ?
?? ??? ??? ?mode_hash() 取余哈系分片規(guī)則
?? ??? ??? ??? ?
?? ??? ??? ?tbpartitions 表分片數(shù)量
?? ??? ??? ?dbpartitions 庫分片數(shù)量

?? ??? ??? ?broadcast:全局表

?? ?6.分庫分表搭建步驟
?? ??? ?1.配置主從同步
?? ??? ?2.裝mycat包,以及依賴包
?? ??? ?3.配置mycat,users的用戶
?? ??? ?4.配置本機datasource數(shù)據(jù)源用戶,創(chuàng)建用戶授權(quán)
?? ??? ?5.mycat中,配置datasource數(shù)據(jù)源連接其它主機,mysql的主服務(wù)器創(chuàng)建plj用戶
?? ??? ??? ?//添加MySQL59主機
?? ??? ??? ?MySQL>/*+ mycat:createdatasource{
?? ??? ??? ?"name":"dw0",
?? ??? ??? ?"url":"jdbc:mysql://192.168.88.59:3306",
?? ??? ??? ?"user":"plj",
?? ??? ??? ?"password":"123456"
?? ??? ??? ?}*/;
?? ??? ??? ?//添加MySQL60主機
?? ??? ??? ?//添加MySQL61主機
?? ??? ??? ?//添加MySQL62主機
?? ??? ?6.mycat上搭建2個主從結(jié)構(gòu)的集群c0,c1(分庫分表的集群名稱必須定義為c0,c1...)
?? ??? ??? ?mysql>/*! mycat:createcluster{
?? ??? ??? ??? ?"name":"c0",
?? ??? ??? ??? ?"masters":["dw0"], #主服務(wù)器名
?? ??? ??? ??? ?"replicas":["dr0"] #從服務(wù)器名
?? ??? ??? ?}*/;
?? ??? ??? ?...
?? ??? ?7.分片操作
?? ??? ??? ?(1)mycat上建立數(shù)據(jù)庫,不需要配置邏輯庫.
?? ??? ??? ??? ?mysql -h192.168.88.63 -P8066 -umycat –p654321
?? ??? ??? ??? ?create database tarena;
?? ?
?? ??? ??? ?(2)mycat,建表,分片
?? ??? ??? ??? ?create table tarena.employees(
?? ??? ??? ??? ??? ?employee_id ?int ?primary key,
?? ??? ??? ??? ??? ?name char(10),
?? ??? ??? ??? ??? ?dept_id int ,?
?? ??? ??? ??? ??? ?mail varchar(30)
?? ??? ??? ??? ?)?
?? ??? ??? ??? ?dbpartition BY mod_hash(字段)?
?? ??? ??? ??? ?tbpartition BY mod_hash(employee_id)?
?? ??? ??? ??? ?dbpartitions 2
?? ??? ??? ??? ?tbpartitions 1;
?? ?
?? ??? ??? ?(3)mycat,插入數(shù)據(jù)測試
?? ??? ??? ??? ?mysql -h192.168.88.63 -P8066 -umycat –p654321
?? ??? ??? ??? ?insert into tarena.employees values (9,"jim","1","jim@163.com");
?? ??? ??? ??? ?insert into tarena.employees values (8,"tom","3","tom@QQ.com");
?? ??? ??? ??? ?insert into tarena.employees values (7,"lucy","2","lucy@QQ.com");
?? ??? ??? ??? ?insert into tarena.employees values (6,"john","2","john@QQ.com");