MySQL主从同步与分表

但盼风雨来能留你在此

已于 2022-10-15 15:16:16 修改

阅读量343

点赞数

分类专栏：其他技术文章标签： mysql 数据库 database

于 2021-10-28 20:40:54 首次发布

本文链接：https://blog.csdn.net/weixin_56697114/article/details/121004030

版权

其他技术专栏收录该内容

14 篇文章 0 订阅

订阅专栏

1、配置

C:\Program Files\MySQL

C:\ProgramData\MySQL\

修改my.ini

主库：

[mysqld]
#开启日志
log-bin = mysql-bin
#设置服务id，主从不能一致
server-id = 1
#设置需要同步的数据库
binlog-do-db=store_db
binlog-do-db=product_db_1
binlog-do-db=product_db_2
#屏蔽系统库同步
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
binlog-ignore-db=performance_schema

从库：

[mysqld]
#设置3307端口
port = 3307
# 设置mysql数据库的数据的存放目录(该目录不一定在mysql安装目录下)
datadir=D:\mysql-5.7.25-s1\data
#开启日志
log-bin = mysql-bin
#设置服务id，主从不能一样
server-id = 2
#设置需要同步的数据库
replicate_wild_do_table=store_db.%
replicate_wild_do_table=product_db_1.%
replicate_wild_do_table=product_db_2.%
#屏蔽系统库同步
replicate_wild_ignore_table=mysql.%
replicate_wild_ignore_table=information_schema.%
replicate_wild_ignore_table=performance_schema.%

然后在命令行窗口 (以管理员身份运行)中将从库安装为windows服务，注意配置文件位置：

mysqld install mysqlc --defaults-file="C:\ProgramData\MySQL\MySQL Server 8.0-c\my.ini"

请注意，从库数据(data)目录下有个文件auto.cnf，也要与主库不一样，建议直接删除掉，重启服务后将会重新生成。

2. 授权主从复制专用账号

创建用户
create user 'yc'@'%' identified by '123';

grant all privileges on *.* to 'yc'@'%'

#登录主库，授权主从复制专用账号
GRANT REPLICATION SLAVE ON *.* TO 'yc'@'%';
#刷新权限
FLUSH PRIVILEGES;
#确认位点 记录下文件名以及位点
show master status;

3、设置从库向主库同步数据、并检查链路

主库需要用yc登录

从库
#修改从库指向到主库，使用上一步记录的文件名以及位点
CHANGE MASTER TO
master_host = 'localhost',
master_user = 'yc',
master_password = '123',
master_log_file = 'mysql-bin.000001',
master_log_pos = 1388;

#执行该命令前，一定要重启主库和从库服务
show slave status\G

#执行该命令后，确认Slave_IO_Runing以及Slave_SQL_Runing两个状态位是否为“Yes”，如果不为
Yes，请检查error_log，然后排查相关异常。

#注意：如果之前此从库已有主库指向，需要先执行以下命令清空
STOP SLAVE IO_THREAD FOR CHANNEL '';

reset slave all;

主数据库存放的数据会与从数据库同步

4、ShardingJdbc

server.port=56081

spring.application.name = sharding-jdbc-demo
server.servlet.context-path = /sharding-jdbc-demo
server.servlet.encoding.enabled = true
server.servlet.encoding.charset = UTF-8
server.servlet.encoding.force = true

# 开启swagger
swagger.enable = true

# 同名bean允许覆盖
spring.main.allow-bean-definition-overriding=true

# 将带有下划线的表字段映射为驼峰格式的实体类属性
mybatis.configuration.map-underscore-to-camel-case = true

# 定义真实数据源(6个)
spring.shardingsphere.datasource.names = m0,m1,m2,s0,s1,s2

spring.shardingsphere.datasource.m0.type = com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.m0.driver-class-name = com.mysql.jdbc.Driver
spring.shardingsphere.datasource.m0.url = jdbc:mysql://localhost:3306/store_db?serverTimezone=GMT%2B8&useSSL=false
spring.shardingsphere.datasource.m0.username = root
spring.shardingsphere.datasource.m0.password = mysql

spring.shardingsphere.datasource.m1.type = com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.m1.driver-class-name = com.mysql.jdbc.Driver
spring.shardingsphere.datasource.m1.url = jdbc:mysql://localhost:3306/product_db_1?serverTimezone=GMT%2B8&useSSL=false
spring.shardingsphere.datasource.m1.username = root
spring.shardingsphere.datasource.m1.password = mysql

spring.shardingsphere.datasource.m2.type = com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.m2.driver-class-name = com.mysql.jdbc.Driver
spring.shardingsphere.datasource.m2.url = jdbc:mysql://localhost:3306/product_db_2?serverTimezone=GMT%2B8&useSSL=false
spring.shardingsphere.datasource.m2.username = root
spring.shardingsphere.datasource.m2.password = mysql

spring.shardingsphere.datasource.s0.type = com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.s0.driver-class-name = com.mysql.jdbc.Driver
spring.shardingsphere.datasource.s0.url = jdbc:mysql://localhost:3307/store_db?serverTimezone=GMT%2B8&useSSL=false
spring.shardingsphere.datasource.s0.username = root
spring.shardingsphere.datasource.s0.password = mysql

spring.shardingsphere.datasource.s1.type = com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.s1.driver-class-name = com.mysql.jdbc.Driver
spring.shardingsphere.datasource.s1.url = jdbc:mysql://localhost:3307/product_db_1?serverTimezone=GMT%2B8&useSSL=false
spring.shardingsphere.datasource.s1.username = root
spring.shardingsphere.datasource.s1.password = mysql

spring.shardingsphere.datasource.s2.type = com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.s2.driver-class-name = com.mysql.jdbc.Driver
spring.shardingsphere.datasource.s2.url = jdbc:mysql://localhost:3307/product_db_2?serverTimezone=GMT%2B8&useSSL=false
spring.shardingsphere.datasource.s2.username = root
spring.shardingsphere.datasource.s2.password = mysql

# 定义逻辑数据源(主从对应关系)
spring.shardingsphere.sharding.master-slave-rules.ds0.master-data-source-name=m0
spring.shardingsphere.sharding.master-slave-rules.ds0.slave-data-source-names=s0

spring.shardingsphere.sharding.master-slave-rules.ds1.master-data-source-name=m1
spring.shardingsphere.sharding.master-slave-rules.ds1.slave-data-source-names=s1

spring.shardingsphere.sharding.master-slave-rules.ds2.master-data-source-name=m2
spring.shardingsphere.sharding.master-slave-rules.ds2.slave-data-source-names=s2

# 分库策略
spring.shardingsphere.sharding.default-database-strategy.inline.sharding-column = store_info_id
spring.shardingsphere.sharding.default-database-strategy.inline.algorithmexpression = ds$->{store_info_id % 2+1}

# 分表策略  3张表
# store_info分表策略，固定分配至ds0的store_info真实表
spring.shardingsphere.sharding.tables.store_info.actual-data-nodes = ds$->{0}.store_info
spring.shardingsphere.sharding.tables.store_info.table-strategy.inline.sharding-column = id
spring.shardingsphere.sharding.tables.store_info.table-strategy.inline.algorithm-expression = store_info

# product_info分表策略，分布在ds1,ds2的product_info_1和product_info_2表 ，分片策略为product_info_id % 2+1，product_info_id采用雪花算法
spring.shardingsphere.sharding.tables.product_info.actual-data-nodes = ds$->{1..2}.product_info_$->{1..2}
spring.shardingsphere.sharding.tables.product_info.table-strategy.inline.sharding-column = product_info_id
spring.shardingsphere.sharding.tables.product_info.table-strategy.inline.algorithm-expression = product_info_$->{product_info_id % 2+1}
spring.shardingsphere.sharding.tables.product_info.key-generator.column=product_info_id
spring.shardingsphere.sharding.tables.product_info.key-generator.type=SNOWFLAKE

# product_descript分表策略，分布在ds1,ds2的product_descript_1和product_descript_2表，分片策略为product_info_id % 2+1，id采用雪花算法
spring.shardingsphere.sharding.tables.product_descript.actual-data-nodes = ds$->{1..2}.product_descript_$->{1..2}
spring.shardingsphere.sharding.tables.product_descript.table-strategy.inline.sharding-column = product_info_id
spring.shardingsphere.sharding.tables.product_descript.table-strategy.inline.algorithm-expression = product_descript_$->{product_info_id %2+1}
spring.shardingsphere.sharding.tables.product_descript.key-generator.column=id
spring.shardingsphere.sharding.tables.product_descript.key-generator.type=SNOWFLAKE

# 设置绑定表
spring.shardingsphere.sharding.binding-tables = product_info,product_descript

# 打开sql输出日志
spring.shardingsphere.props.sql.show = true

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

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

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-configuration-processor</artifactId>
            <optional>true</optional>
        </dependency>

        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>2.2.0</version>
        </dependency>

        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid-spring-boot-starter</artifactId>
            <version>1.1.10</version>
        </dependency>

        <dependency>
            <groupId>io.springfox</groupId>
            <artifactId>springfox-swagger2</artifactId>
            <version>2.9.2</version>
        </dependency>

        <dependency>
            <groupId>org.mybatis</groupId>
            <artifactId>mybatis</artifactId>
            <version>3.5.7</version>
        </dependency>

        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-tx</artifactId>
            <version>5.3.8</version>
        </dependency>

        <dependency>
            <groupId>io.springfox</groupId>
            <artifactId>springfox-swagger-ui</artifactId>
            <version>2.9.2</version>
        </dependency>

        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
        </dependency>

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

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

        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid-spring-boot-starter</artifactId>
        </dependency>

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

        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-generator</artifactId>
            <version>3.1.0</version>
        </dependency>

        <dependency>
            <groupId>org.mybatis</groupId>
            <artifactId>mybatis-typehandlers-jsr310</artifactId>
            <version>1.0.2</version>
        </dependency>

        <dependency>
            <groupId>javax.interceptor</groupId>
            <artifactId>javax.interceptor-api</artifactId>
            <version>1.2</version>
        </dependency>
    </dependencies>

但盼风雨来能留你在此

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
MySQL主从同步与分表

1、配置C:\Program Files\MySQLC:\ProgramData\MySQL\修改my.ini主库：[mysqld]#开启日志log-bin = mysql-bin#设置服务id，主从不能一致server-id = 1#设置需要同步的数据库binlog-do-db=store_dbbinlog-do-db=product_db_1binlog-do-db=product_db_2#屏蔽系统库同步binlog-ignore-db=mysql.
复制链接

扫一扫