数据迁移mysql-->sr

mysql-docker安装

docker run -itd \
    -p 3306:3306 \
    -v /home/mysql/conf:/etc/mysql/conf.d \
    -v /home/mysql/data:/var/lib/mysql \
    -v /home/mysql/log:/var/log/mysql \
    -e MYSQL_ROOT_PASSWORD=root \
    -e TZ=Asia/Shanghai \
    --name mysql5.6 \
    mysql:5.6


/home/mysql/conf下先准备配置文件my.cof


[mysqld]
#innodb_checksum_algorithm=innodb
#innodb_log_checksum_algorithm=innodb
innodb_data_file_path=ibdata1:12M:autoextend
innodb_log_files_in_group=4
innodb_log_file_size=1048576000
#innodb_fast_checksum=false
#innodb_page_size=16384
#innodb_log_block_size=512
innodb_undo_directory=.
innodb_undo_tablespaces=0
#redo_log_version=0
#server_uuid=b386e58f-b873-11ef-b5a2-9cc2c49ea427
#master_key_id=0
server_id=1                 
log_bin=mysql-bin            
binlog_format=ROW            
expire_logs_days=30           
sync_binlog=1                

 

 

恢复后的云数据库对root用户从新添加权限

容器内进入mysql

mysql -u root

 

修改root权限和密码，

 

-- 查看当前权限

SHOW GRANTS FOR 'root'@'localhost';

 

-- 按照输出的权限，给 root@% 同样授权

GRANT ALL PRIVILEGES ON . TO 'root'@'%' IDENTIFIED BY '你的密码' WITH GRANT OPTION;

FLUSH PRIVILEGES;

 

 

 

mysql数据备份恢复-xtrabackup

 

https://docs.percona.com/percona-xtrabackup/2.4/installation/yum_repo.html

 

https://cloud.tencent.com/document/product/236/33363

 

 

Starrocks-docker安装

 

version: "3.8"

services:
  starrocks-fe:
    image: starrocks/fe-ubuntu:3.3.5
    hostname: starrocks-fe
    container_name: starrocks-fe
    user: root
    command: |
      bash /opt/starrocks/fe/bin/start_fe.sh --host_type FQDN
    ports:
      - 8030:8030   # HTTP UI
      - 9020:9020   # RPC
      - 9030:9030   # MySQL protocol
    networks:
      starrocks_net:
    volumes:
      - /data/starrocks/fe/conf:/opt/starrocks/fe/conf
      - /data/starrocks/fe/log:/opt/starrocks/fe/log
      - /data/starrocks/fe/meta:/opt/starrocks/fe/meta
    healthcheck:
      test: 'mysql -u root -h starrocks-fe -P 9030 -e "SHOW FRONTENDS\G" |grep "Alive: true"'
      interval: 10s
      timeout: 5s
      retries: 3

  starrocks-be:
    image: starrocks/be-ubuntu:3.3.5
    hostname: starrocks-be
    container_name: starrocks-be
    user: root
    command:
      - /bin/bash
      - -c
      - |
        ulimit -n 65535;
        echo "# Enable data cache"  >> /opt/starrocks/be/conf/be.conf
        echo "block_cache_enable = true"  >> /opt/starrocks/be/conf/be.conf
        echo "block_cache_mem_size = 536870912" >> /opt/starrocks/be/conf/be.conf
        echo "block_cache_disk_size = 1073741824" >> /opt/starrocks/be/conf/be.conf
        sleep 15s
        mysql --connect-timeout 2 -h starrocks-fe -P 9030 -u root -e "ALTER SYSTEM ADD BACKEND \"starrocks-be:9050\";"
        bash /opt/starrocks/be/bin/start_be.sh
    ports:
      - 8040:8040
    depends_on:
      starrocks-fe:
        condition: service_healthy
    healthcheck:
      test: 'mysql -u root -h starrocks-fe -P 9030 -e "SHOW BACKENDS\G" |grep "Alive: true"'
      interval: 10s
      timeout: 5s
      retries: 3
    networks:
      starrocks_net:
    volumes:
      - /data/starrocks/be/conf:/opt/starrocks/be/conf
      - /data/starrocks/be/log:/opt/starrocks/be/log
      - /data/starrocks/be/storage:/opt/starrocks/be/storage

networks:
  starrocks_net:

先从容器中复制出对应的配置到组主机的配置文件夹。

 

修改be配置：设置主键长度

https://docs.starrocks.io/zh/docs/3.3/administration/management/BE_configuration/#primary_key_limit_size

 

docker compose up -d --wait --wait-timeout 400

 

 用户权限

 CREATE USER 'dbuser' IDENTIFIED BY 'pass';

 

 

-- 创建自定义角色。

CREATE ROLE read_only;

-- 赋予角色所有 Catalog 的使用权限。

GRANT USAGE ON ALL CATALOGS TO ROLE read_only;

-- 赋予角色所有表的查询权限。

GRANT SELECT ON ALL TABLES IN ALL DATABASES TO ROLE read_only;

-- 赋予角色所有视图的查询权限。

GRANT SELECT ON ALL VIEWS IN ALL DATABASES TO ROLE read_only;

-- 赋予角色所有物化视图的查询和加速权限。

GRANT SELECT ON ALL MATERIALIZED VIEWS IN ALL DATABASES TO ROLE read_only;

 

GRANT read_only TO USER dbreader;

 

SET DEFAULT ROLE read_only TO 'dbreader';

 

• GRANT read_only TO USER dbreader; 只是绑定角色到用户
• 还需要激活角色（临时 SET ROLE 或永久 SET DEFAULT ROLE）

 

 

cloudcanal同步

 mysql源参数