上篇文章,实验了主从mysql复制,这次在上篇文章的基础上,完成mysql分库分表并测试主从复制。
https://archive.apache.org/dist/incubator/shardingsphere/4.0.0/
authentication: users: root: password: root sharding: password: sharding authorizedSchemas: sharding_db props: executor.size: 16 # Infinite by default. sql.show: true
schemaName: sharding_db dataSources: ds_0: # 库demo_ds_0的连接地址,根据自己的配置修改ip和端口 url: jdbc:mysql://192.168.94.133:3307/demo_ds_0?serverTimezone=UTC&useSSL=false username: root password: root connectionTimeoutMilliseconds: 30000 idleTimeoutMilliseconds: 60000 maxLifetimeMilliseconds: 1800000 maxPoolSize: 50 ds_1: # 库demo_ds_1的连接地址,根据自己的配置修改ip和端口 url: jdbc:mysql://192.168.94.133:3307/demo_ds_1?serverTimezone=UTC&useSSL=false username: root password: root connectionTimeoutMilliseconds: 30000 idleTimeoutMilliseconds: 60000 maxLifetimeMilliseconds: 1800000 maxPoolSize: 50 # 规则 shardingRule: tables: t_order: actualDataNodes: ds_${0..1}.t_order_${0..1} # 根据user_id取模的结果,选择库;根据order_id取模的结果,选择表;选择库和表的规则在 algorithmExpression 做了配置 tableStrategy: inline: shardingColumn: order_id #表中的order_id列 algorithmExpression: t_order_${order_id % 2} # 对order_id取模 keyGenerator: type: SNOWFLAKE # 使用雪花算法,生成order_id column: order_id t_order_item: actualDataNodes: ds_${0..1}.t_order_item_${0..1} tableStrategy: inline: shardingColumn: order_id algorithmExpression: t_order_item_${order_id % 2} keyGenerator: type: SNOWFLAKE column: order_item_id bindingTables: # 绑定表规则,表示t_order和表t_order_item在做连接操作时,不会进行跨库操作 - t_order,t_order_item defaultDatabaseStrategy: # 选择库使用的策略 inline: shardingColumn: user_id algorithmExpression: ds_${user_id % 2} # 根据user_id取模的结果,选择库 defaultTableStrategy: none:
# 使用配置文件config-master_slave_0.yaml配置库demo_ds_0主从复制规则 schemaName: sharding_db0 dataSources: master_ds_0: #主库demo_ds_0配置 url: jdbc:mysql://192.168.94.133:3307/demo_ds_0?serverTimezone=UTC&useSSL=false username: root password: root connectionTimeoutMilliseconds: 30000 idleTimeoutMilliseconds: 60000 maxLifetimeMilliseconds: 1800000 maxPoolSize: 50 slave_ds_0: # 从库demo_ds_0的配置,多个从库,可以继续添加配置 url: jdbc:mysql://192.168.94.133:3317/demo_ds_0?serverTimezone=UTC&useSSL=false username: root password: root connectionTimeoutMilliseconds: 30000 idleTimeoutMilliseconds: 60000 maxLifetimeMilliseconds: 1800000 maxPoolSize: 50 masterSlaveRule: # name: ms_ds_0 # 规则名称 masterDataSourceName: master_ds_0 # 主数据源名称 slaveDataSourceNames: - slave_ds_0 # 从数据源名称,slaveDataSourceNames数组中的一个元素 loadBalanceAlgorithmType: ROUND_ROBIN
# 使用配置文件config-master_slave_1.yaml配置库demo_ds_1主从复制规则 schemaName: sharding_db1 dataSources: master_ds_1: url: jdbc:mysql://192.168.94.133:3307/demo_ds_1?serverTimezone=UTC&useSSL=false username: root password: root connectionTimeoutMilliseconds: 30000 idleTimeoutMilliseconds: 60000 maxLifetimeMilliseconds: 1800000 maxPoolSize: 50 slave_ds_1: url: jdbc:mysql://192.168.94.133:3317/demo_ds_1?serverTimezone=UTC&useSSL=false username: root password: root connectionTimeoutMilliseconds: 30000 idleTimeoutMilliseconds: 60000 maxLifetimeMilliseconds: 1800000 maxPoolSize: 50 masterSlaveRule: name: ms_ds_1 masterDataSourceName: master_ds_1 slaveDataSourceNames: - slave_ds_1 loadBalanceAlgorithmType: ROUND_ROBIN
log1: Starting the Sharding-Proxy ... The port is configured as 3388 Please check the STDOUT file: /mydata/sharding-proxy/logs/stdout.log log2: ... [INFO ] 08:35:31.564 [main] c.a.icatch.provider.imp.AssemblerImp - USING: com.atomikos.icatch.force_shutdown_on_vm_exit = false [INFO ] 08:35:31.564 [main] c.a.icatch.provider.imp.AssemblerImp - USING: com.atomikos.icatch.default_jta_timeout = 300000 [INFO ] 08:35:31.565 [main] c.a.icatch.provider.imp.AssemblerImp - Using default (local) logging and recovery... [INFO ] 08:35:31.613 [main] c.a.d.xa.XATransactionalResource - resource-1-master_ds_0: refreshed XAResource [INFO ] 08:35:31.627 [main] c.a.d.xa.XATransactionalResource - resource-2-slave_ds_0: refreshed XAResource [INFO ] 08:35:31.669 [main] com.zaxxer.hikari.HikariDataSource - HikariPool-3 - Starting... [INFO ] 08:35:31.677 [main] com.zaxxer.hikari.HikariDataSource - HikariPool-3 - Start completed. [INFO ] 08:35:31.678 [main] com.zaxxer.hikari.HikariDataSource - HikariPool-4 - Starting... [INFO ] 08:35:31.690 [main] com.zaxxer.hikari.HikariDataSource - HikariPool-4 - Start completed. [INFO ] 08:35:31.709 [main] c.a.d.xa.XATransactionalResource - resource-3-ds_0: refreshed XAResource [INFO ] 08:35:31.725 [main] c.a.d.xa.XATransactionalResource - resource-4-ds_1: refreshed XAResource [INFO ] 08:35:32.553 [main] com.zaxxer.hikari.HikariDataSource - HikariPool-5 - Starting... [INFO ] 08:35:32.560 [main] com.zaxxer.hikari.HikariDataSource - HikariPool-5 - Start completed. [INFO ] 08:35:32.561 [main] com.zaxxer.hikari.HikariDataSource - HikariPool-6 - Starting... [INFO ] 08:35:32.574 [main] com.zaxxer.hikari.HikariDataSource - HikariPool-6 - Start completed. [INFO ] 08:35:32.586 [main] c.a.d.xa.XATransactionalResource - resource-5-master_ds_1: refreshed XAResource [INFO ] 08:35:32.597 [main] c.a.d.xa.XATransactionalResource - resource-6-slave_ds_1: refreshed XAResource [INFO ] 08:35:32.940 [epollEventLoopGroup-2-1] i.n.handler.logging.LoggingHandler - [id: 0x711df74e] REGISTERED [INFO ] 08:35:32.943 [epollEventLoopGroup-2-1] i.n.handler.logging.LoggingHandler - [id: 0x711df74e] BIND: 0.0.0.0/0.0.0.0:3388 [INFO ] 08:35:32.946 [epollEventLoopGroup-2-1] i.n.handler.logging.LoggingHandler - [id: 0x711df74e, L:/0.0.0.0:3388] ACTIVE