1. 集群规划
– 节点数：9节点（3 Zone * 3节点）
– 配置：64核 256G SSD
– 副本数：3副本
– 网络：万兆以太网

2. 部署配置
$ cat operator_cluster.yaml
# from:www.itpux.com.qq113257174.wx:itpux-com
user:
username: admin
password: admin
port: 22
oceanbase-ce:
servers:，更多视频教程www.fgedu.net.cn。
– name: observer01
ip: 192.168.1.101
zone: zone1
– name: observer02
ip: 192.168.1.102
zone: zone1
– name: observer03
ip: 192.168.1.103
zone: zone1
– name: observer04
ip: 192.168.1.104
zone: zone2
– name: observer05
ip: 192.168.1.105
zone: zone2
– name: observer06
ip: 192.168.1.106
zone: zone2
– name: observer07
ip: 192.168.1.107
zone: zone3
– name: observer08
ip: 192.168.1.108
zone: zone3
– name: observer09
ip: 192.168.1.109
zone: zone3
global:
home_path: /ob/app/oceanbase
data_dir: /ob/fgdata
log_dir: /ob/fglog
mysql_port: 3306
rpc_port: 2882

3. 参数优化
obclient> ALTER SYSTEM SET memory_limit = ‘200G’;
obclient> ALTER SYSTEM SET __data_mem_limit = ‘150G’;
obclient> ALTER SYSTEM SET workers_per_cpu_quota = 12;
obclient> ALTER SYSTEM SET enable_sql_audit = TRUE;更多学习教程公众号风哥教程itpux_com。

4. 租户创建
obclient> CREATE RESOURCE UNIT operator_unit
MEMORY_SIZE = ‘100G’,
MAX_CPU = 32,
MIN_CPU = 16,
IOPS = 50000,
DISK_SIZE = ’10T’;

obclient> CREATE RESOURCE POOL operator_pool
UNIT = ‘operator_unit’,
UNIT_NUM = 3,
ZONE_LIST = (‘zone1′,’zone2′,’zone3’);

obclient> CREATE TENANT operator_billing
PRIMARY_ZONE = ‘zone1;zone2,zone3’,
RESOURCE_POOL_LIST = (‘operator_pool’)
SET ob_tcp_invited_nodes = ‘%’;

1. 迁移评估
– 源系统：Oracle Exadata
– 数据量：500TB
– 表数量：10000+
– 日增量：10TB，from DB视频:www.itpux.com。

2. 迁移方案
– 全量迁移：历史数据
– 增量同步：OMS实时同步
– 双写阶段：并行运行
– 切换阶段：流量切换

3. OMS配置
$ cat oms_operator_config.json
{
“source”: {
“type”: “Oracle”,
“host”: “10.1.1.100”,
“port”: 1521,
“service”: “OPERATOR”,
“user”: “billing”,
“password”: “billing123”
},
“target”: {
“type”: “OceanBase”,
“host”: “192.168.1.101”,
“port”: 3306,
“user”: “billing@operator_billing”,
“password”: “billing123”
},
“migration”: {
“type”: “FULL_INCR”,
“tables”: [“billing.*”, “cdr.*”],
“parallel”: 32
}
}

4. 数据校验
#!/bin/bash
# verify_operator_migration.sh
# from:www.itpux.com.qq113257174.wx:itpux-com

# 表数量校验
SOURCE_TABLES=$(sqlplus -s billing/billing123@//10.1.1.100:1521/OPERATOR <<< "SELECT COUNT(*) FROM user_tables;" | tail -1) TARGET_TABLES=$(obclient -h192.168.1.101 -P3306 -ubilling@operator_billing -pbilling123 -e "SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = 'billing';" | tail -1) echo "Source tables: $SOURCE_TABLES" echo "Target tables: $TARGET_TABLES" # 行数校验 for table in billing_record cdr_record user_info; do SOURCE_ROWS=$(sqlplus -s billing/billing123@//10.1.1.100:1521/OPERATOR <<< "SELECT COUNT(*) FROM $table;" | tail -1) TARGET_ROWS=$(obclient -h192.168.1.101 -P3306 -ubilling@operator_billing -pbilling123 -e "SELECT COUNT(*) FROM billing.$table;" | tail -1) if [ "$SOURCE_ROWS" -eq "$TARGET_ROWS" ]; then echo "Table $table: OK ($SOURCE_ROWS rows)" else echo "Table $table: MISMATCH (source: $SOURCE_ROWS, target: $TARGET_ROWS)" fi done # 数据校验 obclient -e " SELECT '校验通过' as status, COUNT(*) as total_records, SUM(amount) as total_amount, MAX(create_time) as latest_record FROM billing.billing_record WHERE create_time > DATE_SUB(NOW(), INTERVAL 1 DAY);
”

1. 分区优化
obclient> CREATE TABLE billing_record (
record_id BIGINT PRIMARY KEY,
user_id BIGINT NOT NULL,
amount DECIMAL(18,4) NOT NULL,
create_time TIMESTAMP NOT NULL,
INDEX idx_user_id (user_id),
INDEX idx_create_time (create_time)
) PARTITION BY RANGE (UNIX_TIMESTAMP(create_time))
(
PARTITION p202401 VALUES LESS THAN (UNIX_TIMESTAMP(‘2024-02-01’)),
PARTITION p202402 VALUES LESS THAN (UNIX_TIMESTAMP(‘2024-03-01’)),
PARTITION p202403 VALUES LESS THAN (UNIX_TIMESTAMP(‘2024-04-01’))
);

2. 索引优化
obclient> CREATE INDEX idx_billing_user_time
ON billing_record(user_id, create_time);

obclient> CREATE INDEX idx_billing_amount
ON billing_record(amount)
WHERE amount > 1000;

3. SQL优化
– 绑定变量
– 分区裁剪
– 并行查询
– 批量操作

4. 监控配置
– OCP部署
– 自定义告警
– 慢SQL监控
– 资源监控