原生mysql-迁移到TDSQL分布式 (cvs方式）

原生mysql-迁移到TDSQL分布式 (cvs方式）

原生MYSQL迁移到TDSQL的方式有很多，这里测试其中一种，通过cvs落盘的方式，数据迁移到TDSQL分布式实例中。TDSQL分布式实例需提前创建好。下面演示测试步骤：

原生MYSQL操作：

1.修改源端导出参数：

mysql> show variables like '%secure_file_priv%';

+--------------------------+-------+

| Variable_name            | Value |

+--------------------------+-------+

| secure_file_priv         | NULL  |

+--------------------------+-------+

 

secure_file_prive=null   -- 限制mysqld 不允许导入导出

secure_file_priv=/tmp/   -- 限制mysqld的导入导出只能发生在/tmp/目录下

secure_file_priv=' '         -- 不对mysqld 的导入 导出做限制

 

mysql> set GLOBAL  secure_file_priv = '/tmp';

ERROR 1238 (HY000): Variable 'secure_file_priv' is a read only variable

该参数是静态参数，需在my.cnf 中修改后，重启生效

 

vi /mysql/data/3306/my.cnf

secure_file_priv=/tmp/

 

[root@huyidb05 soft]# systemctl stop mysqld.service

[root@huyidb05 soft]# systemctl start mysqld.service  

[root@huyidb05 soft]# mysql -uroot -proot

mysql> show variables like '%secure_file_priv%';

+------------------+-------+

| Variable_name    | Value |

+------------------+-------+

| secure_file_priv | /tmp/ |

+------------------+-------+

2.将查询结果输出到cvs文件中

MySQL [(none)]>      select * from huyidb.bm ;

+---------------+--------------------+------------+-------------+

| DEPARTMENT_ID | DEPARTMENT_NAME    | MANAGER_ID | LOCATION_ID |

+---------------+--------------------+------------+-------------+

|            10 | 行政部             |        200 |        1700 |

|            20 | 营销部             |        201 |        1800 |

|            90 | 经营管理           |        100 |        1700 |

|           100 | 财务部             |        108 |        1700 |

|           110 | 会计部             |        205 |        1700 |

|           120 | 财政部             |       NULL |        1700 |

|           140 | 控制与信贷         |       NULL |        1700 |

|           160 | 福利保险           |       NULL |        1700 |

|           170 | 制造部             |       NULL |        1700 |

|           180 | 施工               |       NULL |        1700 |

|           200 | 运营部             |       NULL |        1700 |

|           220 | 网络运营中心       |       NULL |        1700 |

|           240 | *行业销售       |       NULL |        1700 |

|           250 | 零售销售           |       NULL |        1700 |

|            30 | 采购管理           |        114 |        1700 |

|            40 | 人力资源           |        203 |        2400 |

|            50 | 航运管理           |        121 |        1500 |

|            60 | IT信息化           |        103 |        1400 |

|            70 | 公共关系管理       |        204 |        2700 |

|            80 | 销售部             |        145 |        2500 |

|           130 | 法人税             |       NULL |        1700 |

|           150 | 股东服务           |       NULL |        1700 |

|           190 | 合同管理           |       NULL |        1700 |

|           210 | 信息技术支持       |       NULL |        1700 |

|           230 | 桌面支持           |       NULL |        1700 |

|           260 | 人才招募           |       NULL |        1700 |

|           270 | 工资管理           |       NULL |        1700 |

+---------------+--------------------+------------+-------------+

   

   

mysql> select  * from itpuxdb.bm  into outfile '/tmp/huyi.csv'  fields terminated by ',' enclosed by '"' lines terminated by '
';

Query OK, 27 rows affected (0.00 sec)

3.获取表结构

mysql> show create table bm ;

 

CREATE TABLE `bm` (

  `DEPARTMENT_ID` decimal(12,0) NOT NULL COMMENT 'Primary key column of departments table.',

  `DEPARTMENT_NAME` varchar(90) NOT NULL COMMENT 'A not null column that shows name of a department. Administration,
Marketing, Purchasing, Human Resources, Shipping, IT, Executive, Public
Relations, Sales, Finance, and Accounting. ',

  `MANAGER_ID` decimal(18,0) DEFAULT NULL COMMENT 'Manager_id of a department. Foreign key to employee_id column of employees table. The manager_id column of the employee table references this column.',

  `LOCATION_ID` decimal(12,0) DEFAULT NULL COMMENT 'Location id where a department is located. Foreign key to location_id column of locations table.',

  PRIMARY KEY (`DEPARTMENT_ID`) USING BTREE,

  KEY `DEPT_LOCATION_IX` (`LOCATION_ID`) USING BTREE,

  KEY `DEPT_MGR_FK` (`MANAGER_ID`) USING BTREE,

  CONSTRAINT `DEPT_LOC_FK` FOREIGN KEY (`LOCATION_ID`) REFERENCES `dd` (`LOCATION_ID`),

  CONSTRAINT `DEPT_MGR_FK` FOREIGN KEY (`MANAGER_ID`) REFERENCES `yg` (`EMPLOYEE_ID`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT='Departments table that shows details of departments where employees
work. Contains 27 rows; references with locations, employees, and job_history tables.' |

TDSQL中操作：

1.在赤兔中为TDSQL 库创建用户，并测试可以登录

mysql -uhuyi47 -phuyi -h 10.85.10.52 -P 15004

 

Welcome to the MariaDB monitor.  Commands end with ; or g.

Your MySQL connection id is 6547

Server version: 5.7.17-11-V2.0R540D002-20191226-1152-log Source distribution

 

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

 

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

 

MySQL [(none)]>

2.创建数据库，创建表，需要修改表结构指定shard key

mysql -uhuyi47 -phuyi -h 10.85.10.52 -P 15004

 

CREATE TABLE `bm` (

  `DEPARTMENT_ID` decimal(12,0) NOT NULL ,

  `DEPARTMENT_NAME` varchar(90) NOT NULL ,

  `MANAGER_ID` decimal(18,0) DEFAULT NULL  ,

  `LOCATION_ID` decimal(12,0) DEFAULT NULL ,

  PRIMARY KEY (`DEPARTMENT_ID`) USING BTREE,

  KEY `DEPT_LOCATION_IX` (`LOCATION_ID`) USING BTREE,

  KEY `DEPT_MGR_FK` (`MANAGER_ID`) USING BTREE  

) ENGINE=InnoDB DEFAULT CHARSET=utf8 shardkey=DEPARTMENT_ID ;

#注意：

必须要设置主键，必须指定 shardkey，删掉外键，必须注意表名大小问题，建议删除多余注释，否则建表可能不成功。

3.开启local_infile 参数 on

a.直接在赤兔上修改，不常见的参数可能赤兔上没有

b.能过后参管理员在所有节点，包括主备都要更改

set GLOBAL  local_infile = OFF;    需要用管理员登录 ./jmysql

 

cd /data/tdsql_run/4002/percona-5.7.17/install

./jmysql.sh 4002

 

mysql> show variables  like '%local%';

+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| local_infile  | OFF   |

+---------------+-------+

1 row in set (0.00 sec)

 

mysql>  set GLOBAL  local_infile = ON ;  （备库也需要确认）

Query OK, 0 rows affected (0.00 sec)

 

mysql>  show variables  like '%local%';

+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| local_infile  | ON    |

+---------------+-------+

1 row in set (0.00 sec)

4.把CSV文件传到 tdsql

scp /tmp/huyi.csv   10.85.10.51:/tmp

5.用tdsdql 网关自带的，这里输入网关地址，方便自动分片

cd /data/tdsql_run/15002/gateway/bin

mode1

format:./load_data mode0/mode1 proxy_host proxy_port user password   shardkey_index file field_terminate filed_enclosed

example:./load_data mode1 10.231.136.34 3336 test test123 shard.table  1 '/tmp/datafile'  ' ' ''

 

[root@huyidb01 bin]# ./load_data mode1 10.85.10.51 15004 huyi47 huyi huyidb.bm  0 '/tmp/huyi.csv'  ','  '"'

connect to proxy to get router info

shardkey_indes:0,file:/tmp/huyi.csv,field_terminate:,,field_enclosed:"

set:set_1607430658_1,begin:0,end:1

key:0,set:set_1607430658_1

key:1,set:set_1607430658_1

begin to separate file

separate file success,begin to send data

begin to send data to set:set_1607430658_1

the sql:/*set_1607430658_1*/load data local infile '/tmp/huyi.csv_set_1607430658_1' into table huyidb.bm fields terminated by ',' enclosed by '"' is execute success

send data to set:set_1607430658_1 success

load data sucess

[root@huyidb01 bin]#

#注意：

1.源文件必须以 ' ' 作为换行符。

2. mode0 只切分源文件，不做数据导入，一般用于调试，正式导入数据使用 mode1。

3. shardkey_index 从0开始，如果 shardkey 在第2个字段，则 shardkey_index 为1。

4. tdsql中的 ./load_date 不支持非分布式

5. noshard 可以使用原生的load data那种方式

6.登录查询验证

 mysql -uhuyi47 -phuyi -h 10.85.10.52 -P 15004 -c    

     # -c  增加透传功能

     /*sets:allsets*/ select * from huyidb.bm ;

MySQL [(none)]>  /*sets:allsets*/ select * from huyidb.bm ;

+---------------+--------------------+------------+-------------+------------------+

| DEPARTMENT_ID | DEPARTMENT_NAME    | MANAGER_ID | LOCATION_ID | info             |

+---------------+--------------------+------------+-------------+------------------+

|            10 | 行政部             |        200 |        1700 | set_1607430658_1 |

|            20 | 营销部             |        201 |        1800 | set_1607430658_1 |

|            90 | 经营管理           |        100 |        1700 | set_1607430658_1 |

|           100 | 财务部             |        108 |        1700 | set_1607430658_1 |

|           110 | 会计部             |        205 |        1700 | set_1607430658_1 |

|           120 | 财政部             |       NULL |        1700 | set_1607430658_1 |

|           140 | 控制与信贷         |       NULL |        1700 | set_1607430658_1 |

|           160 | 福利保险           |       NULL |        1700 | set_1607430658_1 |

|           170 | 制造部             |       NULL |        1700 | set_1607430658_1 |

|           180 | 施工               |       NULL |        1700 | set_1607430658_1 |

|           200 | 运营部             |       NULL |        1700 | set_1607430658_1 |

|           220 | 网络运营中心       |       NULL |        1700 | set_1607430658_1 |

|           240 | *行业销售       |       NULL |        1700 | set_1607430658_1 |

|           250 | 零售销售           |       NULL |        1700 | set_1607430658_1 |

|            30 | 采购管理           |        114 |        1700 | set_1607430658_1 |

|            40 | 人力资源           |        203 |        2400 | set_1607430658_1 |

|            50 | 航运管理           |        121 |        1500 | set_1607430658_1 |

|            60 | IT信息化           |        103 |        1400 | set_1607430658_1 |

|            70 | 公共关系管理       |        204 |        2700 | set_1607430658_1 |

|            80 | 销售部             |        145 |        2500 | set_1607430658_1 |

|           130 | 法人税             |       NULL |        1700 | set_1607430658_1 |

|           150 | 股东服务           |       NULL |        1700 | set_1607430658_1 |

|           190 | 合同管理           |       NULL |        1700 | set_1607430658_1 |

|           210 | 信息技术支持       |       NULL |        1700 | set_1607430658_1 |

|           230 | 桌面支持           |       NULL |        1700 | set_1607430658_1 |

|           260 | 人才招募           |       NULL |        1700 | set_1607430658_1 |

|           270 | 工资管理           |       NULL |        1700 | set_1607430658_1 |

+---------------+--------------------+------------+-------------+------------------+

27 rows in set (0.00 sec)

由于我的分步式实例只创建了一个分片，所有数据都在同一个SET当中，到此我们测试的原生MYSQL迁移到TDSQL分布式实例就完成了。

load_data命令的参数说明：

命令的参数说明：

导出命令：select * from $table_name into outfile '$file_name' fields terminated by ',' enclosed by '"';

导入命令：./load_data mode1 $proxy_host $proxy_port $proxy_username $proxy_passwd $proxy_dbname.$proxy_tablename auto '$file_name' ',' '"'

 

参数说明：

mode0：load_data 的原理是分割数据，然后导入数据，mode0 是先分割数据不进行导入。

mode1：分割数据，然后导入数据。

proxy_host：网关的 host

proxy_port：网关的端口

user：用户名

password：密码

db_talbe：库表，格式为 db.table

shardkey_index：shardkey 字段在导入文件的索引（位置，从 0 开始）

file：绝对路径的文件所在位置

field_terminated：与导出时使用的 field terminated 一致，行的分割

field_enclosed：与导出时使用的 field enclosed 一致，设置字段包围字符

原生mysql-迁移到TDSQL分布式 (cvs方式）

相关推荐