1.在mysql中创建数据库：
[admin@dell angel ~\]$ mysql －－version # 查询mysql版本号
mysql ver 15.1 distrib 5.5.68-mariadb, for linux (x86\_64) using readline 5.1
启动mysql：
systemctl start mariadb
连接mysql：mysql -uroot -p123 -p3306 -c -a -h 127.1
查看数据库：
创建数据库：create database if not exists lxs_db4;
打开数据库：use lxs_db4;
创建表：
create table `table4_lxs` (
`lxs_id` int(11) not null auto_increment,
`lxsname` varchar(50) not null,
primary key (`lxs_id`)
) engine=innodb auto_increment=3 default charset=utf8;
mariadb [lxs_db4]> show tables;
±------------------
| tables_in_lxs_db4 |
±------------------
| table4_lxs |
±------------------
1 row in set (0.00 sec)
给表中插入数据：
mariadb [lxs_db4]> insert into table4_lxs (lxs_id, lxsname) value(1, ‘angel’);
query ok, 1 row affected (0.00 sec)
mariadb [lxs_db4]> insert into table4_lxs (lxs_id, lxsname) value(2, ‘alice’);
query ok, 1 row affected (0.00 sec)
mariadb [lxs_db4]> select * from table4_lxs;
±-------±--------
| lxs_id | lxsname |
±-------±--------
| 1 | angel |
| 2 | alice |
±-------±--------
2 rows in set (0.01 sec)
2.在mysql数据库中导出表的结构和数据：
[admin@dell angel ~]$ mysqldump -uroot -p123 -p3306 -h127.1 -d lxs_db4 --compact>table4_lxs_ddl.sql
[admin@dell angel ~]$ mysqldump -uroot -p123 -p3306 -h127.1 -t lxs_db4 > table4_lxs_data.sql
[admin@dell angel ~]$ ll /home/admin/*.sql
-rw-rw-r–. 1 admin admin 1393 3月 23 04:38 /home/admin/table4_lxs_data.sql
-rw-rw-r–. 1 admin admin 353 3月 23 04:33 /home/admin/table4_lxs_ddl.sql
3.启动ob：
[admin@dell angel ~]$ cd /home/admin/oceanbase && bin/observer -i wlp1s0 -p 2881 -p 2882 -z zone1 -d /home/admin/oceanbase/store -r ‘192.168.43.196:2882:2881’ -c 20220221 -n obdemo -o “memory_limit=8g,cache_wash_threshold=1g,__min_full_resource_pool_memory=268435456,system_memory=3g,memory_chunk_cache_size=128m,cpu_count=16,net_thread_count=4,datafile_size=50g,stack_size=1536k”
查看进程：
ps -ef|grep observer
监听：
netstat -ntlp
集群：
obclient -h 192.168.43.196 -p 2881 -uroot -c -a
mysql [(none)]> show databases;
±-------------------
| database |
±-------------------
| oceanbase |
| information_schema |
| mysql |
| sys |
| lbacsys |
| oraauditor |
| test |
±-------------------
7 rows in set (0.010 sec)
mysql [(none)]> exit
bye
有ob数据库证明集群成功。
连接代理：cd ~/obproxy-3.2.0 && bin/obproxy -r “192.168.43.196:2881” -p 2883 -o “enable_strict_kernel_release=false,enable_cluster_checkout=false,enable_metadb_used=false” -c obdemo
查看进程：ps -ef|grep obproxy
监听：netstat -ntlp |grep obproxy
用代理登录ob：obclient -h 192.168.43.196 -u root@sys#obdemo -p 2883 -p123 -c -a oceanbase
mysql [oceanbase]> show databases;
±-------------------
| database |
±-------------------
| oceanbase |
| information_schema |
| mysql |
| sys |
| lbacsys |
| oraauditor |
| test |
±-------------------
7 rows in set (0.003 sec)
mysql [oceanbase]> select * from __all_unit_config;
±---------------------------±---------------------------±---------------±----------------±--------±--------±-----------±-----------±---------±---------±--------------±--------------------
| gmt_create | gmt_modified | unit_config_id | name | max_cpu | min_cpu | max_memory | min_memory | max_iops | min_iops | max_disk_size | max_session_num |
±---------------------------±---------------------------±---------------±----------------±--------±--------±-----------±-----------±---------±---------±--------------±--------------------
| 2022-03-20 14:17:38.179901 | 2022-03-20 14:17:38.179901 | 1 | sys_unit_config | 5 | 2.5 | 1610612736 | 1342177280 | 10000 | 5000 | 53687091200 | 9223372036854775807 |
| 2022-03-20 19:01:31.380284 | 2022-03-20 19:01:31.380284 | 1001 | lxs_unit1 | 1 | 1 | 1073741824 | 1073741824 | 1000 | 1000 | 10737418240 | 10000 |
±---------------------------±---------------------------±---------------±----------------±--------±--------±-----------±-----------±---------±---------±--------------±--------------------
2 rows in set (0.015 sec)
mysql [oceanbase]> select resource_pool_id, name from __all_resource_pool;
±-----------------±----------
| resource_pool_id | name |
±-----------------±----------
| 1 | sys_pool |
| 1001 | lxs_pool1 |
±-----------------±----------
2 rows in set (0.004 sec)
mysql [oceanbase]> select gmt_create,gmt_modified,tenant_id,tenant_name,replica_num,zone_list,primary_zone,info,locality,default_tablegroup_id,drop_tenant_time,status from __all_tenant;
±---------------------------±---------------------------±----------±------------±------------±----------±-------------±--------------±--------------±----------------------±-----------------±---------------------
| gmt_create | gmt_modified | tenant_id | tenant_name | replica_num | zone_list | primary_zone | info | locality | default_tablegroup_id | drop_tenant_time | status |
±---------------------------±---------------------------±----------±------------±------------±----------±-------------±--------------±--------------±----------------------±-----------------±---------------------
| 2022-03-20 14:17:38.200085 | 2022-03-20 14:17:38.200085 | 1 | sys | -1 | zone1 | zone1 | system tenant | full{1}@zone1 | -1 | -1 | tenant_status_normal |
| 2022-03-20 19:05:40.831319 | 2022-03-20 19:05:40.831319 | 1001 | lxs_tenant1 | -1 | zone1 | random | | full{1}@zone1 | -1 | -1 | tenant_status_normal |
±---------------------------±---------------------------±----------±------------±------------±----------±-------------±--------------±--------------±----------------------±-----------------±---------------------
2 rows in set (0.002 sec)
mysql [oceanbase]> exit
bye
设置白名单：
[admin@dell angel obproxy-3.2.0]$ obclient -h 192.168.43.196 -u root@sys#obdemo -p 2883 -p123 -c -a oceanbase
welcome to the oceanbase. commands end with ; or \g.
your mysql connection id is 6
server version: 5.6.25 oceanbase 3.1.2 (r10000392021123010-d4ace121deae5b81d8f0b40afbc4c02705b7fc1d) (built dec 30 2021 02:47:29)
米乐app官网下载 copyright © 2000, 2018, oracle, mariadb corporation ab and others.
type ‘help;’ or ‘\h’ for help. type ‘\c’ to clear the current input statement.
mysql [oceanbase]> alter tenant lxs_tenant1 set variables ob_tcp_invited_nodes=’%’;
query ok, 0 rows affected (0.011 sec)
mysql [oceanbase]>
登录租户：
[admin@dell angel obproxy-3.2.0]$ obclient -h 192.168.43.196 -u root@lxs_tenant1 -p 2883 -c -a
welcome to the oceanbase. commands end with ; or \g.
your mysql connection id is 8
server version: 5.6.25 oceanbase 3.1.2 (r10000392021123010-d4ace121deae5b81d8f0b40afbc4c02705b7fc1d) (built dec 30 2021 02:47:29)
米乐app官网下载 copyright © 2000, 2018, oracle, mariadb corporation ab and others.
type ‘help;’ or ‘\h’ for help. type ‘\c’ to clear the current input statement.
mysql [(none)]> show databases;
±-------------------
| database |
±-------------------
| oceanbase |
| information_schema |
| mysql |
| test |
| lxs_db |
±-------------------
5 rows in set (0.016 sec)
mysql [(none)]> use lxs_db
database changed
mysql [lxs_db]> show tables;
empty set (0.007 sec)
4.导入mysql数据库的表到ob中：
导入数据：
mysql [lxs_db]> source /home/admin/table4_lxs_data.sql
query ok, 0 rows affected (0.002 sec)
导入表结构：
mysql [lxs_db]> source /home/admin/table4_lxs_ddl.sql
query ok, 0 rows affected (0.001 sec)
mysql [lxs_db]> show tables;
±-----------------
| tables_in_lxs_db |
±-----------------
| table4_lxs |
±-----------------
1 row in set (0.002 sec)
mysql [lxs_db]> select * from table4_lxs;
±-------±--------
| lxs_id | lxsname |
±-------±--------
| 1 | angel |
| 2 | alice |
±-------±--------
2 rows in set (0.001 sec)
mysql [lxs_db]>
mysql [lxs_db]> select count(*) from table4_lxs;
±---------
| count(*) |
±---------
| 2 |
±---------
1 row in set (0.017 sec)
mysql [lxs_db]>
5.导入成功，踩过和坑：
a.mysqldump不支持sql_notes，删除相关语句。
b.mysqldump不支持latin1，删除相关语句。
c.特别要注意创建表的语法格式！
如果查看ddl 文件中，直接指定了charset=latin1，会导致导入数据时出现乱码，为了防止这种情况发生，可以在创建表格的时候做成这样的格式：
create table `table4_lxs` ( `lxs_id` int(11) not null auto_increment, `lxsname` varchar(50) not null, primary key (`lxs_id`) ) engine=innodb auto_increment=3 default charset=utf8;
后面在mysql上建表的时候，可以先指定charset utf8，或者直接
把charset 删除掉也可以。
1.下载datax ：
wget # 下载 datax
tar -xf datax.tar.gz # 解压
find ./datax/plugin -name “.*” | xargs rm –f # 删除datax中的隐藏文件
2.验证datax 的有效性：
sudo python /usr/local/datax/bin/datax.py …/job/job.json
验证成功！
3.编辑json文件：
{
"job": {
"content": \[{
"reader": {
"name": "mysqlreader",
"parameter": {
"column": \[
"\*"
\],
"connection": \[{
"jdbcurl": \["jdbc:mysql://127.0.0.1:3306/lxs\_db4?useunicode=true&characterencoding=utf8"\],
"table": \[
"table4\_lxs"
\]
}\],
"password": "123",
"username": "root",
"where": ""
}
},
"writer": {
"name": "oceanbasev10writer",
"parameter": {
"column": \[
"\*"
\],
"presql": \[
"truncate table lxs\_table4"
\],
"connection": \[{
"jdbcurl": "||\_dsc\_ob10\_dsc\_||obdemo:lxs\_tenant1||\_dsc\_ob10\_dsc\_||jdbc:oceanbase://192.168.43.196:2883/lxs\_db4",
"table": \[
"lxs\_table4"
\]
}\],
"obwritemode": "insert",
"password": "123",
"username": "root"
}
}
}\],
"setting": {
"speed": {
"channel": "4"
}
}
}
}
4.命令执行成功：
sudo python /usr/local/datax/bin/datax.py /usr/local/datax/job/table4 .json
5.踩过的坑：
- datax执行时数据库及表的密码必须与json文件一致，否则无法执行成功。
| 给我点个赞 |