初学Mysql主从复制和分库分表[利用ShardingSphere-proxy]

最新推荐文章于 2024-10-17 16:28:24 发布

原创最新推荐文章于 2024-10-17 16:28:24 发布 · 449 阅读

CC 4.0 BY-SA版权

主从复制【第1部分是MySQL内置的主从模式，第2部分是利用ShardingSphere写配置实现主从复制】

利用docker启动两个mysql，一个叫mysql-master，另一个叫mysql-slaver-01，做好资源的挂载便于配置。启动命令：

[root@taomaster ~]# docker run -p 3307:3306 --name mysql-master \
> -v /mydata/mysql/master/log:/var/log/mysql \
> -v /mydata/mysql/master/data:/var/lib/mysql \
> -v /mydata/mysql/master/conf:/etc/mysql \
> -e MYSQL_ROOT_PASSWORD=root \
> -d mysql:5.7
e239c7177986a41623d1a80f3233f718d5c71a6fd665071f021f0cee3926381f
[root@taomaster ~]# docker run -p 3317:3306 --name mysql-slaver-01 \
> -v /mydata/mysql/slaver/log:/var/log/mysql \
> -v /mydata/mysql/slaver/data:/var/lib/mysql \
> -v /mydata/mysql/slaver/conf:/etc/mysql \
> -e MYSQL_ROOT_PASSWORD=root \
> -d mysql:5.7
f47097d0c7db8301390a8d680675a85a569ba356e65bf57a66f261ed51258307

上面要是直接复制了会有>>，肯定不行，复制下面

docker run -p 3307:3306 --name mysql-master \
-v /mydata/mysql/master/log:/var/log/mysql \
-v /mydata/mysql/master/data:/var/lib/mysql \
-v /mydata/mysql/master/conf:/etc/mysql \
-e MYSQL_ROOT_PASSWORD=root \
-d mysql:5.7
docker run -p 3317:3306 --name mysql-slaver-01 \
-v /mydata/mysql/slaver/log:/var/log/mysql \
-v /mydata/mysql/slaver/data:/var/lib/mysql \
-v /mydata/mysql/slaver/conf:/etc/mysql \
-e MYSQL_ROOT_PASSWORD=root \
-d mysql:5.7

改配置文件，在/master(slaver)/conf/下创建my.cnf，除了通用配置，标识好server_id，主可以给1，从给2；还有read-only的值，主就是0，从是1；也需要指定要复制哪些库，这里的demo_ds_0和demo_ds_1是后面分库要用到；另外把mysql默认的库忽略复制

init_connect='SET collation_connection = utf8_unicode_ci'
init_connect='SET NAMES utf8'
character-set-server=utf8
collation-server=utf8_unicode_ci
skip-character-set-client-handshake
skip-name-resolve

server_id=1
log-bin=mysql-bin
read-only=0

binlog-do-db=demo_ds_0
binlog-do-db=demo_ds_1

replicate-ignore-db=mysql
replicate-ignore-db=sys
replicate-ignore-db=information_schema
replicate-ignore-db=performance_schema

启动docker restart mysql-master mysql-slaver-01
登录进去master的mysql，要做授权给slaver，backup是从mysql要连上主mysql的话需要master_user，123456是master_password

GRANT REPLICATION SLAVE ON *.* to 'backup'@'%' identified by '123456';

登录进去slaver的mysql，用上一步的授权信息连上主mysql

change master to
master_host='192.168.172.172',master_user='backup',master_password='123456',master_log_file='mysql_bin.000001',master_log_pos=0,master_port=3307;

这一步中master_log_file可以使用show master status这个sql命令，得到File字段的信息就是这个，同理可以查看show slave status查看从mysql信息。

测试一下，可以先用之前配置的demo_ds_0库中的t_order表，当然这是第二部分分库分表要用到的。

CREATE TABLE `t_order` (
`order_id` bigint(20) NOT NULL,
`user_id` int(11) NOT NULL,
`status` varchar(50) COLLATE utf8_bin DEFAULT NULL,
PRIMARY KEY (`order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

分库分表

如果数据量不大，一个库一个表就够了，但是如果一张表就大到检索好久才出来结果，才需要去分库分表。比如：把本来一个库一张表的存储，变为存到两个库，每个库中表也相同，按照某种分类规则，决定要去哪个库的哪张表存。
下面的举例是存user_id和status信息，再加一个主键order_id(这个字段的数据并不是手动指定的，是后面配置keyGenerator：type: SNOWFLAKE指定的)
直接利用shardinsphere-proxy，改里面的配置文件，就能实现分库分表，具体步骤如下：

去ShardingSphere官网下载ShardingSphere-Proxy二进制包，解压
proxy相当于是操作mysql数据库的代理，所以需要给第一步解压后的lib文件夹下复制一下mysql-connector-java-[版本].jar.
改conf下server.yaml，指定了一会用客户端连接proxy用的用户名、密码和库名，root用户和sharding用户都能连上proxy查看数据库。

authentication:
  users:
    root:
      password: root
    sharding:
      password: sharding 
      authorizedSchemas: sharding_db
props:
  acceptor.size: 16

配置分库分表规则config-sharding.yaml，demo_ds_0和demo_ds_1是需要提前在数据库中创建这两个库，其实就是分库；同理也要在这两两个库下创建表t_order_0和t_order_1，也就是一共有四张表来存储信息，按什么分库？就是ds_{user_id % 2}，对user_id取余来决定存ds_0还是ds_1(这并不是真实数据库中的库名，ds_0和真实的库demo_ds_0对应)；按什么分表？t_order_{order_id % 2}，对order_i取余来决定存t_order_0还是t_order_1(是真实的表此处)

schemaName: sharding_db
#
dataSources:
  ds_0:
    url: jdbc:mysql://192.168.172.172:3307/demo_ds_0?serverTimezone=UTC&useSSL=false
    username: root
    password: root
    connectionTimeoutMilliseconds: 30000
    idleTimeoutMilliseconds: 60000
    maxLifetimeMilliseconds: 1800000
    maxPoolSize: 50
  ds_1:
    url: jdbc:mysql://192.168.172.172: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}
      tableStrategy:
        inline:
          shardingColumn: order_id
          algorithmExpression: t_order_${order_id % 2}
      keyGenerator:
        type: SNOWFLAKE
        column: order_id
  bindingTables:
    - t_order
  defaultDatabaseStrategy:
    inline:
      shardingColumn: user_id
      algorithmExpression: ds_${user_id % 2}
  defaultTableStrategy:
    none:

配置主从的信息config-master_slave_1.yaml和config-master_slave.yaml，这里只贴了_1的，把所有1标识改成0就是_0的配置

schemaName: sharding_db_1
dataSources:
  master_1_ds:
    url: jdbc:mysql://192.168.172.172: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.172.172: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_1_ds
  slaveDataSourceNames:
    - slave_ds_1
  loadBalanceAlgorithmType: ROUND_ROBIN