Mysql集群

最新推荐文章于 2023-03-05 21:12:10 发布

原创最新推荐文章于 2023-03-05 21:12:10 发布 · 340 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#mysql #java #数据库

主从备份

主库配置

server-id 服务器的唯一标示

server-id Master唯一标示数字必须小于Slave唯一标示的数字

log_bin 开启日志功能，此日志为命令日志，记录主库中执行的所有SQL命令。

log_bin不是必要的，只有配置主从备份时必要。

show master status 查看主库状态

从库配置

log_bin 不需开启

stop slave

change master to master_host='xx.xx.xx.xx',master_user='root',master_password='xx',master_log_file='master_log.xxx';

start slave

show slave status \G;

基因法

inline分表代码演示

application.properties

spring.shardingsphere.datasource.names=m1
spring.shardingsphere.datasource.m1.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.dataSource.m1.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.m1.url=jdbc:mysql://xxx:3306/test?serverTimezone=GMT%2B8
spring.shardingsphere.datasource.m1.username=root
spring.shardingsphere.datasource.m1.password=xxx
spring.shardingsphere.sharding.tables.course.actual-data-nodes=m1.course_$->{1..2}
spring.shardingsphere.sharding.tables.course.key-generator.column=cid
spring.shardingsphere.sharding.tables.course.key-generator.type=SNOWFLAKE
spring.shardingsphere.sharding.tables.course.table-strategy.inline.sharding-column=cid

spring.shardingsphere.sharding.tables.course.table-strategy.inline.algorithm-expression=course_$->{cid%2+1}
spring.shardingsphere.props.sql.show=true
spring.main.allow-bean-definition-overriding=true

pom.xml

<dependency>
    <groupId>org.apache.shardingsphere</groupId>
    <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
    <version>xxxx</version>
</dependency>

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-test</artifactId>
</dependency>

<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>druid</artifactId>
    <version>xxxx</version>
</dependency>

<dependency>
    <groupId>mysql/groupId>
    <artifactId>mysql-connector-java</artifactId>
</dependency>

<dependency>
    <groupId>com.baomidou</groupId>
    <artifactId>mybatis-plus-boot-starter</artifactId>
    <version>3.5.2</version>
</dependency>
<dependency>
    <groupId>org.mybatis.spring.boot</groupId>
    <artifactId>mybatis-spring-boot-starter</artifactId>
    <version>xxxx</version>
</dependency>

@MapperScan("com.windaka.sharding.jdbctest.mapper")
@SpringBootApplication
public class JdbcTestApplication{

    public static void main(String[] args){
        SpringApplication.run(JdbcTestApplication.class,args);
    }
}

mapper/CourseMapper.java

public interface CourseMapper extends BaseMapper<Course>{

    @Insert({"insert into course(cid,cname,user_id,cstatus) values(#{cid},#{cname},#{user_id},#{cstatus})"})
    public void inRt(Course c);

}

entity/Course.java

public class Course{

    /*如果使用雪花算法生成主键cid,cid只能使用包装类Interger,Long等,不能使用int,long*/
    private Integer cid; 

    private String cname;

    private Integer user_id;

    private String cstatus;

    public Integer getCid(){
        return cid;
    }

    public void setCid(Integer cid){
        this.cid = cid;
    }

    public void setUser_id(Integer user_id){
        this.user_id = user_id;
    }


    public String getCname(){
        return cname;
    }

    public void setCname(String cname){
        this.cname = cname;
    }

    public void setCstatus(String cstatus){
        this.cstatus = cstatus;
    }

    @Override
    public String toString(){
        return "Course{"+
               "cid=" + cid +
               ", cname='" + cname + '\'' +
               ", user_id=" + user_id +
               ", cstatus='" + cstatus + '\'' +
               '}';
    }
}

controller/SphereTest.java

@RestController
@RequestMapping("/Test")
public class SphereTest{

    @Resource
    private CourseMapper cMapper;

    @GetMapping("/testLoad")
    public String testLoads(){
        for(int i = 1;i < 10;i++){
            Course c = new Course();
            /*实际情况需要cid得根据雪花算法获取id*/
            c.setCid(i);
            c.setCname("java"+i)
            c.setUser_id(Integer.parseInt(i+""));

            cMapper.inRt(c);
        }
        return "ok";
    }
}

使用mybatis-plus

controller/SphereTest.java

@RestController
@RequestMapping("/test")
public class SphereTest{

    @Resource
    private CourseMapper cMapper;

    @GetMapping("/testLoad")
    public String testLoads(){
        for(int i = 1; i < 10; i++){
            Course c = new Course();

            /*不需要设置cid,mybatis-plus会使用指定的算法自动生成*/
            //c.setCid(i);

            c.setCname("java"+i);

            c.setCstatus(i+"");

            c.setUser_id(Integer.parseInt(i+""));

            cMapper.insert(c);
        }
        return "ok";
    }
}

Select查询总数演示

@Resource
private CourseMapper cMapper;

@Test
public void queryCourse(){

    /*获取总数,调用mybatis-plus*/
    int cnt = cMapper.selectCount(null);
    
    /*获取总数,调用mybatis*/
    Integer cCount = cMapper.qCount();

    /*调用mybatis-plus获取每一条记录*/
    List<Course> listCourse = cMapper.selectList(null);
    for(Course cOne : listCourse){
        System.out.println(cOne);
    }
}

mapper/CourseMapper.java

public interface CourseMapper extends BaseMapper<Course>{

    @Select({"select count(1) from course"})
    Integer qCount();

}

Select查询单条

@Resource
private CourseMapper cMapper;

@Test
public void queryCourse(){

    /*方法一:
     *使用mybatis-plus调用获取*/
    QueryWrapper qWrapper = new QueryWrapper();
    qWrapper.eq("cid",2L);
    List<Course> qList = cMapper.selectList(qWrapper);
    System.out.println("qList size = " + qList.size());

    for(Course cOne : qList){
        System.out.println(cOne);
    }

    /*方法二:
     *使用mybatis调用获取*/
    Course cSelect = cMapper.qSelect(2);
    System.out.println("cSelect");
}

mapper/CourseMapper.java

public interface CourseMapper extends BaseMapper<Course>{

    @Select({"select cname,user_id,cstatus from course where cid=#{cid}"})
    Course qSelect(Integer cid);
}

inline方式不足

inline不支持范围查询。列入查询cid为2到4之间的数据。如下所示，运行直接报错。Inline strategy cannot support range sharding.

QueryWrapper qWrapper = new QueryWrapper();

qWrapper.between("cid",2,4);
List<Course> qList = cMapper.selectList(qWrapper);

inline分库代码演示

application.properties

spring.shardingsphere.datasource.names=m1,m2
spring.shardingsphere.datasource.m1.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.m1.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.m1.url=jdbc:mysql://10.10.5.252.3306/test?serverTimezone=GMT%2B8
springshardingsphere.datasource.m1.username=root
springshardingsphere.datasource.m1.password=xxx

spring.shardingsphere.datasource.m2.type=com.alibaba.druid.pool.DrvidDataSource
spring.shardingsphere.datasource.m2.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.m2.url=jdbc:mysql://10.10.5.250:3306/test?serverTimezone=GMT%2B8
spring.shardingsphere.datasource.m2.username=root
spring.shardingsphere.datasource.m2.password=xxx

spring.shardingsphere.sharding.tables.course.actual-data-nodes=m$->{1..2}.course_$->{1..2}
spring.shardingsphere.sharding.tables.course.key-generator.column=cid
spring.shardingsphere.sharding.tables.course.key-generator.type=SNOWFLAKE
spring.shardingsphere.sharding.tables.key-generator.props.worker.id=1

#分表
spring.shardingsphere.sharding.tables.course.table-strategy.inline.sharding-column=cid
spring.shardingsphere.sharding.tables.course.table-strategy.inline.algorithm-expression=course_$->{cid%2+1}

#分库
spring.shardingsphere.sharding.tables.course.database-strategy.inline.sharding-column=cid
spring.shardingsphere.sharding.tables.course.database-strategy.inline.algorithm-expression=m$->{cid%2+1}

spring.shardingsphere.props.sql.show=true
spring.main.allow-bean-definition-overriding=true
#启用驼峰命令
mybatis.configuration.map-underscore-to-camel-case=true

Stander模式

application.properties


#分表
spring.shardingsphere.sharding.tables.course.table-strategy.standard.sharding-column=cid
spring.shardingsphere.sharding.table.course.table-strategy.standard.range-algorithm-class-name=com.wintong.sharding.jdbctest.algorithm.MyRangeTable
spring.shardingsphere.sharding.tables.course.table-strategy.standard.precise-algorithm-class-name=com.wintong.sharding.jdbctest.algorithm.MyPreciseTable

#分库
spring.shardingsphere.sharding.tables.course.database-strategy.standard.sharding-column=cid
spring.shardingsphere.sharding.tables.course.database-strategy.standard.range-algorithm-class-name=com.wintong.sharding.jdbctest.algorithm.MyRangeDatabase
spring.shardingsphere.sharding.tables.course.database-strategy.standard.precise-algorithm-class-name=com.wintong.sharding.jdbctest.algorithm.MyPreciseDatabase

public class MyRangeDatabase implements RangeShardingAlgorithm<Long>{

    @Override
    public Collection<String> doSharding(Collection<String> collection,RangeShardingValue<Long> rangeShardingValue){

        Iterator<String> iterator = collection.iterator();
        ArrayList dsName = new ArrayList<String>();
        while(iterator.hasNext()){
            dsName.add(iterator.next());
        }
        return dsName;
    }
}

public class MyPreciseDatabase implements PreciseShardingAlgorithm<Long>{

    @Override
    public String doSharding(Collection<String> collection,PreciseShardingValue<Long> preciseShardingValue){

         Long cidValue = preciseShardingValue.getValue();
         BigInteget cidInteger = BigInteger.valueOf(cidValue);
         /*cid%2+1*/
         BigInteger resB = (cidInteger.mod(new BigInteger("2"))).add(new BigInteger("1"));
         String key = "m" +resB;
         if(collection.contains(key)){
             return key;
         }
         return null;
    }
}

Cid需要Long类型，否则可能提示类型问题错误。

QueryWrapper qWrapper = new QueryWrapper();

QWrapper.between("cid",xxx,xxx);

List<Course> qList = cMapper.selectList(qWrapper);

for(Course cOne : qList){
    System.out.println(cOne);
}

Standard模式多主键查询采用全库，全表扫描查询，效率不高，如果想支持多主键查询，建议使用complex模式

complex模式

用于支持多分片键查询

application.properties

#comple模式分表策略
spring.shardingsphere.sharding.tables.course.table-strategy.complex.sharding-columns=cid,user_id
spring.shardingsphere.sharding.tables.course.table-strategy.complex.algorithm-class-name=com.windaka.sharding.jdbctest.algorithm.MycomplexTable

public class MycomplexTable implements ComplexKeysShardingAlgorithm<Long>{

    @Override
    public Collection<String> doSharding(Collection<String> collection,ComplexKeysShardingValue<Long> cinokexJetsSgardubgVakye){

        Collection<Long> userIdCol = complexKeysShardingValue.getColumnNameAndShardingValuesMap().get("user_id");
        Collection<Long> cidCol = complexKeysShardingValue.getColumnNameAndShardingValuesMap().get("cid");

        ArrayList result = new ArrayList();
        for(Long userId : userIdCol){
             BigInteger userIdBig = BigInteger.valueOf(userId);
             BigInteger target = (userIdBig.mod(new BigInteger("2"))).add(new BigInteger("1"));
             result.add(complexKeysShardingValue.getLogicTableName()+"_"+target);
        }
        return result;
    }
}

QueryWrapper qWrapper = new QueryWrapper();
qWrapper.between("cid",xx,xx);
qWrapper.eq("user_id",xx);
List<Course>  qList = cMapper.selectList(qWrapper);

for(Course cOne : qList){
    System.out.println(cOne);
}

//Ps
between方法第二个参数必须比第三个参数大，否则报错，不知道原因

hint模式

强制路由模式，没有分片键

application.properties

spring.shardingsphere.sharding.tables.course.table-strategy.hint.algorithm-class-name=com.windaka.sharding.jdbctest.algorithm.MyHintTable

public class MyHintTable implements HintShardingAlgorithm<Integer>{

    @Override
    public Collection<String> doSharding(Collection<String> collection,HintShardingValue<Integer> hintShardingValue){
        String key = hintShardingValue.getLogicTableName()+"_"_hintShardingValue.getValues().toArray()[0];
        if(collection.contains(key)){
            return Arrays.asList(key);
        }
        return null;
    }
}

hint模式虽然比较灵活，但不支持union等多关联查询

spring.shardingsphere.sharding.tables.表名.xx

可以根据不同表设置使用不同的策略模式inline,stardard,complex,hint

广播表

一般用于数据字典表等，字典信息会同时写入所有分库分表里面。

application.properties


spring.shardingsphere.sharding.broadcast-tables=t_dict(要广播的表名)

10.10.5.252 test数据库
 create table t_dict(dict_id bigint(0) PRIMARY KEY NOT NULL,ustatus varchar(100) NOT NULL,uvalue varchar(100) NOT NULL);

10.10.5.250 test数据库
 create table t_dict(dict_id bigint(0) PRIMARY KEY NOT NULL,ustatus varchar(100) NOT NULL,uvalue varchar(100) NOT NULL);

entity/Dict.java

/*使用TableName注解标记此实体类对应的表名*/
@TableName("t_dict")
public class Dict{
    private Long dictId;
    private String ustatus;
    private String uvalue;

    public Long getDictId(){
        return dictId;
    }

    public void setDictId(Long dictId){
        this.dictId = dictId;
    }

    public String getUstatus(){
        return ustatus;
    }

    public void setUstatus(String ustatus){
        this.ustatus = ustatus;
    }

    public String getUvalue(){
        return uvalue;
    }

    public void setUvalue(String uvalue){
        this.uvalue = uvalue;
    }
}

mapper/DictMapper.java

public interface DictMapper extends BaseMapper<Dict>{

}

private DictMapper dictMapper;

@Test
void addDict(){
    Dict d = new Dict();
    d.setDictId(1L);
    d.setUstatus("1");
    d.setUvalue("haha");
    dictMapper.insert(d);
}

绑定表

create table t_user_2(user_id bigint(0) PRIMARY KEY NOT NULL,username varchar(100) NOT NULL,ustatus varchar(50) NOT NULL,uage int(3));

create table t_user_1(user_id bigint(0) PRIMARY KEY NOT NULL,username varchar(100) NOT NULL,ustatus varchar(50) NOT NULL,uage int(3));

create table t_dict_2(dict_id bigint(0) PRIMARY KEY NOT NULL,ustatus varchar(100) NOT NULL,uvalue varchar(100) NOT NULL);

create table t_dict_1(dict_id bigint(0) PRIMARY KEY NOT NULL,ustatus varchar(100) NOT NULL,uvalue varchar(100) NOT NULL);

两台服务器上分表创建如上表

@TableName("t_user")
entity/User{

    private Long userId;

    private String username;

    private String ustatus;

    private int age;

    public Long getUserId(){
        return userId;
    }

    public void setUserId(Long userId){
        this.userId = userId;
    }

    public void setUsername(String username){
        this.username = username;
    }

    public String getUsername(){
        return username;
    }

    public String getUstatus(){
        return ustatus;
    }

    public void setUstatus(String ustatus){
        this.ustatus = ustatus;
    }

    public int getUage(){
        return uage;
    }

    public void setUage(int uage){
        this.uage = uage;
    }

}

@TableName(“t_dict”)
public class Dict{

    private Long dictId;

    private String ustatus;

    private String uvalue;

    public void setUvalue(String uvalue){
        this.uvalue = uvalue;
    }

    public String getUvalue(){
        return uvalue;
    }

    public void setUstatus(String ustatus){
        this.ustatus = ustatus;
    }

    public String getUstatus(){
        return ustatus;
    }

    public void setDictId(Long dictId){
        this.dictId = dictId;
    }

    public Long getDictId(){
        return dictId;
    }
}

mapper/DictMapper.java

public interface DictMapper extends BaseMapper<Dict>{
}

mapper/UserMapper.java

public interface UserMapper extends BaseMapper<User>{

    @Select("Select u.user_id,u.username,d.uvalue ustatus from t_user u left join t_dict d on u.ustatus=d.ustatus")
    public List<User> q_UserStatus();
}

-->没搞明白Select u.user_id,u.username,d.uvalue ustatus from中为什么去掉ustatus，变成
Select u.user_id,u.username,d.uvalue from查询结果就不准确了

@Resource
private DictMapper dictMapper;

@Resource
private UserMapper userMapper;

@Test
void addDict(){
    Dict d = new Dict();
    d.setUstatus("1");
    d.setUvalue("OK");
    dictMapper.insert(d);

    Dict d1 = new Dict();
    d1.setUstatus("0");
    d1.setUvalue("NG");
    dictMapper.insert(d1);

    for(int i=0;i<10;i++){
        User user = new User();
        user.setUsername("user name"+i);
        user.setUstatus(i%2+"");
        user.setUage(10*i+11);
        userMapper.insert(user);
    }
}

@Test
public void qUserStatus(){
    List<User> users = userMapper.q_UserStatus();
    for(User user : users){
        System.out.println(user);
    }
}

spring.shardingsphere.sharding.tables.t_dict.actual-data-nodes=m$->{1..2}.t_dict_${1..2}
spring.shardingsphere.sharding.tables.t_dict.key-generator.column=dict_id
spring.shardingsphere.sharding.tables.t_dict.key-generator.type=SNOWFLAKE

#t_dict分表
spring.shardingsphere.sharding.tables.t_dict.table-strategy.inline.sharding-column=ustatus
spring.shardingsphere.sharding.tables.t_dict.table-strategy.inline.algorithm-expression=t_dict_$->{ustatus.toInteger()%2+1}

#t_dict分库
spring.shardingsphere.sharding.tables.t_dict.database-strategy.inline.sharding-column=ustatus
spring.shardingsphere.sharding.tables.t_dict.database-strategy.inline.algorithm-expression=m$->{ustatus.toInteger()%2+1}

#user表
spring.shardingsphere.sharding.tables.t_user.actual-data-nodes=m$->{1..2}.t_user_$->{1..2}
spring.shardingsphere.sharding.tables.t_user.key-generator.column=user_id
spring.shardingsphere.sharding.tables.t_user.key-generator.type=SNOWFLAKE

#user分表
spring.shardingsphere.sharding.tables.t_user.table-strategy.inline.sharding-column=ustatus
spring.shardingsphere.sharding.tables.t_user.table-strategy.inline.algorithm-expression=t_user_$->{ustatus.toInteger()%2+1}

#user分库
spring.shardingsphere.sharding.tables.t_user.database-strategy.inline.sharding-column=ustatus
spring.shardingsphere.sharding.tables.t_user.database-strategy.inline.algorithm-expression=m$->{ustatus.toInteger()%2+1}

#绑定表
spring.shardingsphere.sharding.bing-tables[0]=t_user,t_dict

读写分离

配置主从库

#ds0是随便写的规则名称,配置规则ds0的主库
spring.shardingsphere.sharding.master-slave-rules.ds0.master-data-source-name=m0

#配置规则ds0的从库,从库可以是多个
spring.shardingsphere.sharding.master-slave-rules.ds0.slave-data-source-names=s0,s2

spring.shardingsphere.sharding.tables.t_dict.actual-data-nodes=ds0.t_dict
spring.shardingsphere.sharding.tables.t_dict.key-generator.column=dict_id
spring.shardingsphere.sharding.tables.t_dict.key-generator.type=SNOWFLAEK
spring.shardingsphere.sharding.tables.t_dict.table-strategy.inline.sharding-column=dict
spring.shardingsphere.sharding.tables.t_dict.table-strategy.inline.algorithm-expression=t_dict

public void updateUvalue(){

    Dict dict = new Dict();
    dict.setUstatus("0");
    dict.setUvalue("unNormal");

    UpdateWrapper<Dict> wrapper = new UpdateWrapper<>();
    wrapper.eq("ustatus",dict.getUstatus());

    /*update t_dict set uvalue="unNormal" where ustatus=0;*/
    dictMapper.update(dict,wrapper);
}

设置完主从库后，在进行读写数据库时shardingsphere会自动根据设定insert,update,del等写操作对主库就行操作，select操作对从库进行操作。

10.10.5.250 testdb

create table course_1(cid BIGINT(28) PRIMARY KEY,cname VARCHAR(58) NOT NULL,user_id BIGINT(20) NOT NULL,cstatus varchar(10) NOT NULL);

create table course_2(cid BIGINT(28) PRIMARY KEY,cname VARCHAR(58) NOT NULL,user_id BIGINT(20) NOT NULL,cstatus varchar(10) NOT NULL);

10.10.5.252 tesstdb

create table course_1(cid BIGINT(28) PRIMARY KEY,cname VARCHAR(58) NOT NULL,user_id BIGINT(20) NOT NULL,cstatus varchar(10) NOT NULL);

create table course_2(cid BIGINT(28) PRIMARY KEY,cname VARCHAR(58) NOT NULL,user_id BIGINT(20) NOT NULL,cstatus varchar(10) NOT NULL);