首页 > 数据库 >MySQL5.7到OceanBase4.0数据迁移

MySQL5.7到OceanBase4.0数据迁移

时间：2022-11-13 16:35:33浏览次数：52  
标签：ok oceanbase MySQL5.7 192.168 TPCD KEY mysql 迁移 OceanBase4.0

2057702.jpg

一、安装MySQL数据库

系统版本：CentOS 7.5 数据库版本：MySQL 5.7.38

创建目录

mkdir -p /mysql/{data,tmp,binlog,logs}
chown -R mysql:mysql /mysql

创建用户

groupadd -g 300 mysql
useradd -u 300 -g mysql mysql

安装依赖

yum -y install ncurses ncurses-devel bison cmake gcc gcc-c++

cmake配置编译参数

cmake . -DCMAKE_INSTALL_PREFIX=/usr/local/mysql \
 -DMYSQL_DATADIR=/mysql/data \
 -DMYSQL_UNIX_ADDR=/mysql/tmp/mysql.sock \
 -DDEFAULT_CHARSET=utf8mb4 \
 -DDEFAULT_COLLATION=utf8mb4_general_ci \
 -DSYSCONFDIR=/etc \
 -DEXTRA_CHARSETS=all \
 -DENABLED_LOCAL_INFILE=ON \
 -DWITH_INNOBASE_STORAGE_ENGINE=1 \
 -DWITH_FEDERATED_STORAGE_ENGINE=1 \
 -DWITH_BLACKHOLE_STORAGE_ENGINE=1 \
 -DWITHOUT_EXAMPLE_STORAGE_ENGINE=1 \
 -DWITHOUT_PARTITION_STORAGE_ENGINE=1 \
 -DWITH_ZLIB=bundled \
 -DWITH_EMBEDDED_SERVER=1 \
 -DWITH_DEBUG=0 \
 -DWITH_BOOST=/usr/local/boost

编译和安装

make
make install

修改MySQL参数文件

[mysqld]
port=3306
user=mysql
basedir=/usr/local/mysql/
datadir=/mysql/data
socket=/mysql/tmp/mysql.sock
character-set-server=utf8mb4
collation-server=utf8mb4_general_ci
max-connections=1000

[mysqld_safe]
log-error=/mysql/logs/error.log
pid-file=/var/run/mysql/mysql.pid

初始化数据库

cd /usr/local
chown -R mysql:mysql mysql

mysqld --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/mysql/data --socket=/mysql/tmp/mysql.sock

cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysql

启动数据库

service mysql start

二、部署OceanBase集群

环境说明

名词解释

  • 中控机器 存储 OceanBase 数据库安装包和集群配置信息的机器。
  • 目标机器 安装 OceanBase 数据库的机器。
  • OBD OceanBase Deployer，OceanBase 开源软件的安装部署工具，简称为 OBD。
  • OBProxy OceanBase Database Proxy，OceanBase 高性能反向代理服务器，简称为 OBProxy

  • 机器列表

IP地址 主机名 机器配置 系统版本 内核版本 角色
192.168.31.150 obcontrol 1C2G CentOS 7.5 Linux3.10.0 OBD，中控机
192.168.31.151 z1 2C8G CentOS 7.5 Linux3.10.0 OBserver
192.168.31.152 z2 2C8G CentOS 7.5 Linux3.10.0 OBserver
192.168.31.153 z3 2C8G CentOS 7.5 Linux3.10.0 OBserver

部署准备

配置hosts

四台机器都要配置hosts

[root@obcontrol ~]# cat /etc/hosts
127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4
::1         localhost localhost.localdomain localhost6 localhost6.localdomain6
192.168.31.150 obcontrol
192.168.31.151 z1
192.168.31.152 z2
192.168.31.153 z3

配置免密登录

以下操作在中控机进行

ssh-keygen -t rsa
scp -p ~/.ssh/id_rsa.pub root@z1:/root/.ssh/authorized_keys
scp -p ~/.ssh/id_rsa.pub root@z2:/root/.ssh/authorized_keys
scp -p ~/.ssh/id_rsa.pub root@z3:/root/.ssh/authorized_keys

配置NTP时钟同步

服务端（中控机）上配置

cat /etc/ntp.conf
server 127.127.1.0

service ntpd restart
chkconfig ntpd on

客户端（OBserver）上配置

cat /etc/ntp.conf
server 192.168.31.150 prefer

service ntpd restart
chkconfig ntpd on

查看NTP信息

[root@z2 .ssh]# ntpq -p
     remote           refid      st t when poll reach   delay   offset  jitter
==============================================================================
-time.cloudflare 10.211.8.4       3 u   34   64    3  139.541   -3.923   0.495
*tock.ntp.infoma .GPS.            1 u   35   64    3  223.843    2.985   0.957
+119.28.183.184  100.122.36.196   2 u   35   64    3   40.520    0.713   0.576
+119.28.206.193  100.122.36.196   2 u   31   64    3   45.111    1.564   0.914
 obcontrol       .INIT.          16 u    -   64    0    0.000    0.000   0.000

配置 limits.conf

所有机器进行如下配置：

cat /etc/security/limits.conf
root soft nofile 655350
root hard nofile 655350
* soft nofile 655350
* hard nofile 655350
* soft stack 20480
* hard stack 20480
* soft nproc 655360
* hard nproc 655360
* soft core unlimited
* hard core unlimited

配置 sysctl.conf

由于只是测试用，我们只配置fs.aio-max-nr

cat /etc/sysctl.conf
# # for oceanbase
# ## 修改内核异步 I/O 限制
fs.aio-max-nr=1048576

sysctl -p
fs.aio-max-nr = 1048576

关闭防火墙

关闭防火墙

systemctl stop firewalld.service

永久关闭防火墙

systemctl disable firewalld.service

查看防火墙状态

systemctl status firewalld.service
● firewalld.service - firewalld - dynamic firewall daemon
   Loaded: loaded (/usr/lib/systemd/system/firewalld.service; disabled; vendor preset: enabled)
   Active: inactive (dead)
     Docs: man:firewalld(1)

Nov 11 11:56:01 z3 systemd[1]: Starting firewalld - dynamic firewall daemon...
Nov 11 11:56:14 z3 systemd[1]: Started firewalld - dynamic firewall daemon.
Nov 11 15:55:00 z3 systemd[1]: Stopping firewalld - dynamic firewall daemon...
Nov 11 15:55:01 z3 systemd[1]: Stopped firewalld - dynamic firewall daemon.

关闭SELinux

vim /etc/selinux/config
SELINUX=disabled

重启服务器，使更改生效

setenforce 0

查看更改是否生效

sestatus

创建用户

groupadd -g 300 ob
useradd -u 300 -g ob ob

passwd ob

在线部署

部署模式

本次测试采用三副本部署模式，共使用四台机器。

  • 使用一台机器部署 OBProxy、Grafana、Prometheus。
  • 使用三台机器部署 OceanBase 集群。

安装 all-in-one package

在官网下载all-in-one package https://open.oceanbase.com/softwareCenter/community 解压安装包

tar zxvf oceanbase-all-in-one.4.0.0.0-beta-100120221102135736.el7.x86_64.tar.gz

安装

cd oceanbase-all-in-one/bin/
./install.sh
name: grafana
version: 7.5.17
release:1
arch: x86_64
md5: 1bf1f338d3a3445d8599dc6902e7aeed4de4e0d6
add /software/oceanbase-all-in-one/rpms/grafana-7.5.17-1.el7.x86_64.rpm to local mirror
name: obagent
version: 1.2.0
release:4.el7
arch: x86_64
md5: 0e8f5ee68c337ea28514c9f3f820ea546227fa7e
add /software/oceanbase-all-in-one/rpms/obagent-1.2.0-4.el7.x86_64.rpm to local mirror
name: obproxy-ce
version: 4.0.0
release:5.el7
arch: x86_64
md5: de53232a951184fad75b15884458d85e31d2f6c3
add /software/oceanbase-all-in-one/rpms/obproxy-ce-4.0.0-5.el7.x86_64.rpm to local mirror
name: oceanbase-ce
version: 4.0.0.0
release:100000272022110114.el7
arch: x86_64
md5: 42611dc51ca9bb28f36e60e4406ceea4a74914c7
add /software/oceanbase-all-in-one/rpms/oceanbase-ce-4.0.0.0-100000272022110114.el7.x86_64.rpm to local mirror
name: oceanbase-ce-libs
version: 4.0.0.0
release:100000272022110114.el7
arch: x86_64
md5: 188919f8128394bf9b62e3989220ded05f1d14da
add /software/oceanbase-all-in-one/rpms/oceanbase-ce-libs-4.0.0.0-100000272022110114.el7.x86_64.rpm to local mirror
name: prometheus
version: 2.37.1
release:10000102022110211.el7
arch: x86_64
md5: 58913c7606f05feb01bc1c6410346e5fc31cf263
add /software/oceanbase-all-in-one/rpms/prometheus-2.37.1-10000102022110211.el7.x86_64.rpm to local mirror
Disable remote ok

#####################################################################
 Install Finished 
=====================================================================
Setup Environment:     source ~/.oceanbase-all-in-one/bin/env.sh 
Quick Start:           obd demo 
More Details:          obd -h 
=====================================================================

检查是否安装成功

# which obd
/root/.oceanbase-all-in-one/obd/usr/bin/obd
# which obclient
/root/.oceanbase-all-in-one/obclient/u01/obclient/bin/obclient

部署 OceanBase 集群

编辑default-example.yaml 位置在/root/.oceanbase-all-in-one/conf/autodeploy

## Only need to configure when remote login is required
user:
   username: ob
   password: ob123
#   key_file: your ssh-key file path if need
#   port: your ssh port, default 22
#   timeout: ssh connection timeout (second), default 30
oceanbase-ce:
  servers:
    - name: z1
      # Please don't use hostname, only IP can be supported
      ip: 192.168.31.151
    - name: z2
      ip: 192.168.31.152
    - name: z3
      ip: 192.168.31.153
  global:
    # Please set devname as the network adaptor's name whose ip is  in the setting of severs.
    # if set severs as "127.0.0.1", please set devname as "lo"
    # if current ip is 192.168.1.10, and the ip's network adaptor's name is "eth0", please use "eth0"
    devname: enp0s3
    cluster_id: 1
    redo_dir: /oceanbase/redo
    data_dir: /oceanbase/data
    # please set memory limit to a suitable value which is matching resource. 
    memory_limit: 6G
    system_memory: 4G
    stack_size: 512K
    max_syslog_file_count: 4
    # observer cluster name, consistent with obproxy's cluster_name
    appname: ob_cluster
    root_password: 123456
    proxyro_password: 123456
  z1:
    mysql_port: 2881
    rpc_port: 2882
    home_path: /oceanbase/home/observer
    zone: zone1
  z2:
    mysql_port: 2881
    rpc_port: 2882
    home_path: /oceanbase/home/observer
    zone: zone2
  z3:
    mysql_port: 2881
    rpc_port: 2882
    home_path: /oceanbase/home/observer
    zone: zone3
obproxy-ce:
  servers:
    - 192.168.31.150
  global:
    listen_port: 2883
    home_path: /oceanbase/home/obproxy
    # oceanbase root server list
    # format: ip:mysql_port,ip:mysql_port
    rs_list: 192.168.31.151:2881;192.168.31.152:2881;192.168.31.153:2881
    enable_cluster_checkout: false
    # observer cluster name, consistent with oceanbase-ce's appname
    cluster_name: ob_cluster
    obproxy_sys_password: 123456
    observer_sys_password: 123456

在中控机部署OceanBase集群

obd cluster autodeploy ob_cluster -c default-example.yaml 
install oceanbase-ce-4.0.0.0 for local ok
install obproxy-ce-4.0.0 for local ok
Cluster param config check ok
Open ssh connection ok
Generate observer configuration ok
Generate obproxy configuration ok
install oceanbase-ce-4.0.0.0 for local ok
install obproxy-ce-4.0.0 for local ok
+--------------------------------------------------------------------------------------------+
|                                          Packages                                          |
+--------------+---------+------------------------+------------------------------------------+
| Repository   | Version | Release                | Md5                                      |
+--------------+---------+------------------------+------------------------------------------+
| oceanbase-ce | 4.0.0.0 | 100000272022110114.el7 | 42611dc51ca9bb28f36e60e4406ceea4a74914c7 |
| obproxy-ce   | 4.0.0   | 5.el7                  | de53232a951184fad75b15884458d85e31d2f6c3 |
+--------------+---------+------------------------+------------------------------------------+
Repository integrity check ok
Parameter check ok
Open ssh connection ok
Cluster status check ok
Initializes observer work home ok
Initializes obproxy work home ok
Remote oceanbase-ce-4.0.0.0-100000272022110114.el7-42611dc51ca9bb28f36e60e4406ceea4a74914c7 repository install ok
Remote oceanbase-ce-4.0.0.0-100000272022110114.el7-42611dc51ca9bb28f36e60e4406ceea4a74914c7 repository lib check !!
Remote obproxy-ce-4.0.0-5.el7-de53232a951184fad75b15884458d85e31d2f6c3 repository install ok
Remote obproxy-ce-4.0.0-5.el7-de53232a951184fad75b15884458d85e31d2f6c3 repository lib check ok
Try to get lib-repository
install oceanbase-ce-libs-4.0.0.0 for local ok
Remote oceanbase-ce-libs-4.0.0.0-100000272022110114.el7-188919f8128394bf9b62e3989220ded05f1d14da repository install ok
Remote oceanbase-ce-4.0.0.0-100000272022110114.el7-42611dc51ca9bb28f36e60e4406ceea4a74914c7 repository lib check ok
ob_cluster deployed
Get local repositories ok
Search plugins ok
Open ssh connection ok
Load cluster param plugin ok
Check before start observer ok
[WARN] OBD-1007: (192.168.31.151) The recommended number of max user processes is 12288 (Current value: 4096)
[WARN] (192.168.31.151) clog and data use the same disk (/)
[WARN] OBD-1007: (192.168.31.152) The recommended number of max user processes is 12288 (Current value: 4096)
[WARN] (192.168.31.152) clog and data use the same disk (/)
[WARN] OBD-1007: (192.168.31.153) The recommended number of max user processes is 12288 (Current value: 4096)
[WARN] (192.168.31.153) clog and data use the same disk (/)

Check before start obproxy ok
Start observer ok
observer program health check ok
Connect to observer ok
Initialize cluster ok
Start obproxy ok
obproxy program health check ok
Connect to obproxy ok
Initialize cluster ok
Wait for observer init ok
+--------------------------------------------------+
|                     observer                     |
+----------------+---------+------+-------+--------+
| ip             | version | port | zone  | status |
+----------------+---------+------+-------+--------+
| 192.168.31.151 | 4.0.0.0 | 2881 | zone1 | ACTIVE |
| 192.168.31.152 | 4.0.0.0 | 2881 | zone2 | ACTIVE |
| 192.168.31.153 | 4.0.0.0 | 2881 | zone3 | ACTIVE |
+----------------+---------+------+-------+--------+
obclient -h192.168.31.151 -P2881 -uroot -p123456 -Doceanbase

+--------------------------------------------------+
|                     obproxy                      |
+----------------+------+-----------------+--------+
| ip             | port | prometheus_port | status |
+----------------+------+-----------------+--------+
| 192.168.31.150 | 2883 | 2884            | active |
+----------------+------+-----------------+--------+
obclient -h192.168.31.150 -P2883 -uroot@proxysys -p123456 -Doceanbase
ob_cluster running

连接 OceanBase 数据库

obclient -h192.168.31.150 -P2883 -uroot@proxysys -p123456
ERROR 2027 (HY000): received malformed packet
[root@obcontrol autodeploy]# obclient -h192.168.31.150 -P2883 -uroot -p123456
Welcome to the OceanBase.  Commands end with ; or \g.
Your OceanBase connection id is 12
Server version: OceanBase_CE 4.0.0.0 (r100000272022110114-6af7f9ae79cd0ecbafd4b1b88e2886ccdba0c3be) (Built Nov  1 2022 14:57:18)

Copyright (c) 2000, 2018, OB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

obclient [(none)]> 
obclient [(none)]> 
obclient [(none)]> 
obclient [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| LBACSYS            |
| mysql              |
| oceanbase          |
| ORAAUDITOR         |
| SYS                |
| test               |
+--------------------+
7 rows in set (0.029 sec)

obclient [(none)]> use oceanbase;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
obclient [oceanbase]> show tables;
+-------------------------------------------+
| Tables_in_oceanbase                       |
+-------------------------------------------+
| CDB_INDEXES                               |
| CDB_IND_COLUMNS                           |
| CDB_IND_PARTITIONS                        |
| CDB_IND_SUBPARTITIONS                     |
| CDB_OBJECTS                               |
| CDB_OB_ARCHIVELOG                         |

问题梳理

问题一

[ERROR] (192.168.31.151) /home not enough disk space. (Avail: 10.2G, Need: 50.0G).
[ERROR] (192.168.31.152) /home not enough disk space. (Avail: 10.2G, Need: 50.0G).
[ERROR] (192.168.31.153) /home not enough disk space. (Avail: 10.2G, Need: 50.0G).

原因是observer三个节点home_path设置在/home目录下，而由于安装系统时候/home目录只设置了10G空间。 解决方法： 修改default-example.yaml文件的home_path配置，寻找空间大于50G的目录。

问题二

[WARN] OBD-1007: (192.168.31.151) The recommended number of max user processes is 12288 (Current value: 4096)
[ERROR] OBD-2000: (192.168.31.151) not enough memory. (Free: 7.0G, Need: 8.0G)
[WARN] (192.168.31.151) clog and data use the same disk (/)
[WARN] OBD-1007: (192.168.31.152) The recommended number of max user processes is 12288 (Current value: 4096)
[ERROR] OBD-2000: (192.168.31.152) not enough memory. (Free: 7.0G, Need: 8.0G)
[WARN] (192.168.31.152) clog and data use the same disk (/)
[WARN] OBD-1007: (192.168.31.153) The recommended number of max user processes is 12288 (Current value: 4096)
[ERROR] OBD-2000: (192.168.31.153) not enough memory. (Free: 7.0G, Need: 8.0G)
[WARN] (192.168.31.153) clog and data use the same disk (/)

解决方法 每台observer最大物理内存8G，memory_limit设置为8G。安装提示内存不足，修改default-example.yaml memory_limit: 6G。

问题三

[ERROR] Deploy "ob_cluster" is deployed. You could not deploy an deployed cluster.
See https://www.oceanbase.com/product/ob-deployer/error-codes .

解决方法 重新执行部署之前，需要对部署一半的集群进行清理。

obd cluster destroy ob_cluster

参考文档： https://www.oceanbase.com/docs https://zhuanlan.zhihu.com/p/448637597

三、TPC-H生产数据

本次实验采用TPC-H 3.0.0

解压和编译

解压文件

解压tpch文件，进入dbgen目录。

cp makefile.suite makefile

修改makefile

修改makefile文件，将103-111行修改如下：

CC      = gcc
# Current values for DATABASE are: INFORMIX, DB2, TDAT (Teradata)
#                                  SQLSERVER, SYBASE, ORACLE, VECTORWISE
# Current values for MACHINE are:  ATT, DOS, HP, IBM, ICL, MVS, 
#                                  SGI, SUN, U2200, VMS, LINUX, WIN32 
# Current values for WORKLOAD are:  TPCH
DATABASE= MYSQL
MACHINE = LINUX
WORKLOAD = TPCH

CC = gcc既指定编辑器。

修改tpcd.h

文件末尾添加如下信息：

#ifdef MYSQL
#define GEN_QUERY_PLAN  ""
#define START_TRAN      "START TRANSACTION"
#define END_TRAN        "COMMIT"
#define SET_OUTPUT      ""
#define SET_ROWCOUNT    "limit %d;\n"
#define SET_DBASE       "use %s;\n"
#endif

进行编译

make

make完dbgen目录下之后就会多出很多.o 文件，dbgen、qgen可执行程序，dss.ri，dss.dll文件。

生成数据

使用dbgen生成数据，共生成8个表(.tbl)。

./dbgen -s 10

-s 10表示生产10G数据，可以自行设置。

修改脚本

由于生成的SQL脚本可执行性不好，我们需要对其中一些脚本进行修改。

修改dss.dll

脚本开头添加如下SQL：

drop database TPCD;
create database TPCD;
use TPCD;

修改dss.ri

首先修改第6-13行，“--”注释符后边加空格：

-- ALTER TABLE TPCD.REGION DROP PRIMARY KEY;
-- ALTER TABLE TPCD.NATION DROP PRIMARY KEY;
-- ALTER TABLE TPCD.PART DROP PRIMARY KEY;
-- ALTER TABLE TPCD.SUPPLIER DROP PRIMARY KEY;
-- ALTER TABLE TPCD.PARTSUPP DROP PRIMARY KEY;
-- ALTER TABLE TPCD.ORDERS DROP PRIMARY KEY;
-- ALTER TABLE TPCD.LINEITEM DROP PRIMARY KEY;
-- ALTER TABLE TPCD.CUSTOMER DROP PRIMARY KEY;

然后分别在第25，40，55，73，78，84，90和96行为MySQL添加外键名字：

修改第25行：
ADD FOREIGN KEY NATION_FK1 (N_REGIONKEY) references TPCD.REGION;
=>
ADD FOREIGN KEY NATION_FK1 (N_REGIONKEY) references TPCD.REGION(R_REGIONKEY); 
修改第40行：
ADD FOREIGN KEY SUPPLIER_FK1 (S_NATIONKEY) references TPCD.NATION;
=>
ADD FOREIGN KEY SUPPLIER_FK1 (S_NATIONKEY) references TPCD.NATION(N_NATIONKEY);
修改第55行：
ADD FOREIGN KEY CUSTOMER_FK1 (C_NATIONKEY) references TPCD.NATION;
=>
ADD FOREIGN KEY CUSTOMER_FK1 (C_NATIONKEY) references TPCD.NATION(N_NATIONKEY); 
修改第73行：
ADD FOREIGN KEY PARTSUPP_FK1 (PS_SUPPKEY) references TPCD.SUPPLIER;
=>
ADD FOREIGN KEY PARTSUPP_FK1 (PS_SUPPKEY) references TPCD.SUPPLIER(S_SUPPKEY);
修改第78行：
ADD FOREIGN KEY PARTSUPP_FK2 (PS_PARTKEY) references TPCD.PART;
=>
ADD FOREIGN KEY PARTSUPP_FK2 (PS_PARTKEY) references TPCD.PART(P_PARTKEY);
修改第84行：
ADD FOREIGN KEY ORDERS_FK1 (O_CUSTKEY) references TPCD.CUSTOMER;
=>
ADD FOREIGN KEY ORDERS_FK1 (O_CUSTKEY) references TPCD.CUSTOMER(C_CUSTKEY);
修改第90行：
ADD FOREIGN KEY LINEITEM_FK1 (L_ORDERKEY)  references TPCD.ORDERS;
=>
ADD FOREIGN KEY LINEITEM_FK1 (L_ORDERKEY)  references TPCD.ORDERS(O_ORDERKEY);
修改第96行：
TPCD.PARTSUPP;
=>
TPCD.PARTSUPP(PS_PARTKEY,PS_SUPPKEY);

将大写表名改为小写，在脚本最后添加如下SQL：

ALTER TABLE CUSTOMER rename to customer;
ALTER TABLE LINEITEM rename to lineitem;
ALTER TABLE NATION rename to nation;
ALTER TABLE ORDERS rename to orders;
ALTER TABLE PART rename to part;
ALTER TABLE PARTSUPP rename to partsupp;
ALTER TABLE REGION rename to region;
ALTER TABLE SUPPLIER rename to supplier;

导入数据

建库建表

mysql> source /software/tpc-h-tool-3.0.0/TPC-H_Tools_v3.0.0/dbgen/dss.ddl

mysql> use TPCH;
Database changed
mysql> show tables;
+----------------+
| Tables_in_tpch |
+----------------+
| CUSTOMER       |
| LINEITEM       |
| NATION         |
| ORDERS         |
| PART           |
| PARTSUPP       |
| REGION         |
| SUPPLIER       |
+----------------+
8 rows in set (0.00 sec)

添加外键和主键

mysql> source /software/tpc-h-tool-3.0.0/TPC-H_Tools_v3.0.0/dbgen/dss.ri

生成导入SQL脚本

将以下代码保存在dbgen目录下，保存为“load.sh”，然后执行：

#!/bin/bash
write_to_file()
{
    file="loaddata.sql"
    if [ ! -f "$file" ] ; then
        touch "$file"
    fi
    echo 'USE TPCD;' >> $file
    echo 'SET FOREIGN_KEY_CHECKS=0;' >> $file
    DIR=`pwd`
    for tbl in `ls *.tbl`; do
        table=$(echo "${tbl%.*}")
        echo "LOAD DATA LOCAL INFILE '$DIR/$tbl' INTO TABLE $table" >> $file
        echo "FIELDS TERMINATED BY '|' LINES TERMINATED BY '|\n';" >> $file
    done
    echo 'SET FOREIGN_KEY_CHECKS=1;' >> $file
 }
write_to_file

sh load.sh

dbgen目录下就会生成一个loaddata.sql，里面是从8个tbl里导入数据的sql命令，然后执行导入脚本。

mysql -u root -p < loaddata.sql

四、迁移数据

本次数据迁移采用datax直接从MySQL迁移至OceanBase集群。 datax下载地址： https://github.com/alibaba/DataX

配置文件

{
    "job": {
        "setting": {
            "speed": {
                "channel": 2 
            },
            "errorLimit": {
                "record": 0,
                "percentage": 0.1
            }
        },
        "content": [
            {
                "reader": {
                    "name": "mysqlreader",
                    "parameter": {
                        "username": "migrate",
                        "password": "migrate",
                        "column": [
                            "*"
                        ],
                        "connection": [
                            {
                                "table": [
                                    "lineitem"
                                ],
                                "jdbcUrl": ["jdbc:mysql://192.168.31.57:3306/TPCD?useUnicode=true&characterEncoding=utf8"]
                            }
                        ]
                    }
                },

                "writer": {
                    "name": "oceanbasev10writer",
                    "parameter": {
                        "obWriteMode": "insert",
                        "column": [
                            "*"
                        ],
                        "preSql": [
                            "set foreign_key_checks = 0"
                        ],
                        "connection": [
                            {
                                "jdbcUrl": "||_dsc_ob10_dsc_||ob_cluster:sys||_dsc_ob10_dsc_||jdbc:oceanbase://192.168.31.150:2883/TPCD?useLocalSessionState=true&allowBatch=true&allowMultiQueries=true&rewriteBatchedStatements=true",
                                "table": [
                                    "lineitem"
                                ]
                            }
                        ],
                        "username": "migrate",
                        "password":"migrate",
                        "writerThreadCount":10,
                        "batchSize": 2048,
                        "memstoreThreshold": "0.9"
                    }
                }
            }
        ]
    }
}

启动迁移

python /software/datax/bin/datax.py /software/datax/job/mysql-ob.json

问题梳理

问题一

2022-11-13 10:12:33.473 [job-0] ERROR RetryUtil - Exception when calling callable, 异常Msg:Code:[DBUtilErrorCode-10], Description:[连接数据库失败. 请检查您的 账号、密码、数据库名称、IP、Port或者向 DBA 寻求帮助(注意网络环境).].  -  具体错误信息为：java.sql.SQLException: cluster not exist
com.alibaba.datax.common.exception.DataXException: Code:[DBUtilErrorCode-10], Description:[连接数据库失败. 请检查您的 账号、密码、数据库名称、IP、Port或者向 DBA 寻求帮助(注意网络环境).].  -  具体错误信息为：java.sql.SQLException: cluster not exist

解决方法： 配置文件中配置信息需要指定集群名字，如下：

ob_cluster:sys

问题二

2022-11-13 10:17:29.262 [job-0] ERROR Engine - 
经DataX智能分析,该任务最可能的错误原因是:
com.alibaba.datax.common.exception.DataXException: Code:[DBUtilErrorCode-06], Description:[执行数据库 Sql 失败, 请检查您的配置的 column/table/where/querySql或者向 DBA 寻求帮助.].  - 执行的SQL为: select * from t1 where 1=2 具体错误信息为：com.alipay.oceanbase.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Table 'test.t1' doesn't exist

解决方法： 目标库（OceanBase）需要手动创建表结构，手动创建需要迁移的表结构即可。

问题三

2022-11-13 11:46:18.037 [0-insertTask-23] WARN  InsertTask - ERROR : record {"data":[{"byteSize":4,"rawData":2976,"type":"LONG"},{"byteSize":6,"rawData":810694,"type":"LON
G"},{"byteSize":5,"rawData":60711,"type":"LONG"},{"byteSize":1,"rawData":4,"type":"LONG"},{"byteSize":5,"rawData":"22.00","type":"DOUBLE"},{"byteSize":8,"rawData":"35302.3
0","type":"DOUBLE"},{"byteSize":4,"rawData":"0.00","type":"DOUBLE"},{"byteSize":4,"rawData":"0.04","type":"DOUBLE"},{"byteSize":1,"rawData":"A","type":"STRING"},{"byteSize
":1,"rawData":"F","type":"STRING"},{"byteSize":8,"rawData":760636800000,"subType":"DATE","type":"DATE"},{"byteSize":8,"rawData":762624000000,"subType":"DATE","type":"DATE"
},{"byteSize":8,"rawData":760982400000,"subType":"DATE","type":"DATE"},{"byteSize":16,"rawData":"TAKE BACK RETURN","type":"STRING"},{"byteSize":3,"rawData":"FOB","type":"S
TRING"},{"byteSize":32,"rawData":"ncies kindle furiously. carefull","type":"STRING"}],"size":16}

解决方法： 由于需要迁移的表有外键约束，所以需要处理涉及外键的表数据。表lineitem依赖表part和partsupp。

标签：ok,oceanbase,MySQL5.7,192.168,TPCD,KEY,mysql,迁移,OceanBase4.0
From： https://blog.51cto.com/u_12592884/5847764

相关文章

赞助商

阅读排行

网站主页关于我们联系我们网站地图

本网站内容转载自其他媒体，侵权联系[admin##ips99.com]。

Copyright © 2020-2023 IPS99 版权所有 IPS99