从MYSQL到oracle的迁移以及备份_MySQL-mysql教程-PHP中文网

首页

数据库

mysql教程

从MYSQL到oracle的迁移以及备份_MySQL

WBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWB

Jun 01, 2016 pm 01:43 PM

oracle 开发项目

bitsCN.com 这几天做榆林政协的项目，因为关系到从MYSQL向Oracle的迁移，所以，有了一些经验。
因为项目一开始就是用MYSQL开发的，所以没想到会出现的问题都出现了。
一、MYSQL可以导入导出脚本，本来应该准备好数据库的备份的初始化数据库和初始化数据的两个初始化脚本，经由MYSQL导出为一个full.sql的总的初始化脚本，用过mysql的都知道，导出的数据库脚本中，包含了建库、建表、以及插入数据的语句。需要分割成两个：建库、建表的初始化脚本和插入数据的初始化脚本。于是我写了一个类，用于分割SQL脚本，在类中，我查找到create table开头的后面紧跟着的字符串，就是表的名字，取出insert开头的语句，改行就是插入数据的，于是，通过文件流读取文件的每一行，如果是插入的取出来放入一个方法convertInsertSQL处理后写入一个脚本文件insert_data.sql中，剩下的写入一个脚本文件create_table.sql中, 方法convertInsertSQL对于插入语句做处理，因为脚本中的插入语句是批量插入的，insert into 表名(列名) values(对应值),( 对应值),( 对应值)，所以需要拆分成insert into 表名(列名) values (对应值)这样的语句，所以我通过将前面values(前的值截取后，对剩下的所有对应数据，进行通过),(用正则分割成String数组，对数组进行循环，每次追加组成插入语句，最后将插入语句全部返回后写入文件流。
package com.test.file;

import java.io.BufferedReader;
import java.io.BufferedWriter;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStreamReader;
import java.io.OutputStreamWriter;
import java.util.HashMap;
import java.util.Map;

/**
* 将mysql数据库导出的脚本
* 分割成为建库和建表的两个脚本
* @author gaofei
*
*/
public class MyFileReader {

    /**
     * @param args
     */
    public static void main(String[] args) {
       String full_sql_path="C:/Documents and Settings/gaofei/桌面/最终mysql脚本(12.15).sql";
       String create_sql_path="C:/Documents and Settings/gaofei/桌面/建立数据库初始化.sql";
       String insert_sql_path="C:/Documents and Settings/gaofei/桌面/数据初始化.sql";
       try {
           readFull_to_insert(full_sql_path, create_sql_path, insert_sql_path);
     } catch (IOException e) {
           System.out.println("文件读取或写入出错");
           e.printStackTrace();
       }

//     String aa="insert into `templatetype`(`id`,`templatetypename`,`deflong`,`defdate`,`defvar`) values (0,'通用模板类型',0,NULL,NULL),(1,'首页模板类型',0,NULL,NULL),(2,'栏目模板类型',0,NULL,NULL),(3,'专题模板类型',0,NULL,NULL),(4,'内容模板类型',0,NULL,NULL),(5,'留言模板类型',0,NULL,NULL),(6,'投票模板类型',0,NULL,NULL),(7,'特殊模板类型',0,NULL,NULL);";
//     String bb=full_to_part(aa);
//     System.out.println(bb);
    }
    /**
     * 将整体的导出的mysql脚本，拆分为建库建表和插入数据的脚本
     * 将其中的批量插入数据语句转换为每条插入数据语句的脚本
     * @param full_sql_path 原始全部导出的mysql脚本
     * @param create_sql_path 拆出来的建库建表的脚本
     * @param insert_sql_path 拆出来的插入数据脚本
     * @throws IOException
     */
    private static void readFull_to_insert(String full_sql_path,String create_sql_path,String insert_sql_path) throws IOException{
       File fullFile=new File(full_sql_path);
       File createFile=new File(create_sql_path);
       if(!createFile.exists())
           createFile.createNewFile();
       File insertFile=new File(insert_sql_path);
       if(!insertFile.exists())
           insertFile.createNewFile();
       InputStreamReader isr=new InputStreamReader(new FileInputStream(fullFile), "UTF-8");
       BufferedReader br=new BufferedReader(isr);
       OutputStreamWriter osw_create=new OutputStreamWriter(new FileOutputStream(createFile), "UTF-8");
       OutputStreamWriter osw_insert=new OutputStreamWriter(new FileOutputStream(insertFile), "UTF-8");
       BufferedWriter bw_create=new BufferedWriter(osw_create);
       BufferedWriter bw_insert=new BufferedWriter(osw_insert);
       Map allData=new HashMap();
       String line=null;
       int num=17;
       while((line=br.readLine())!=null){
           String lowerLine=line.toLowerCase();
           if(lowerLine.startsWith("insert")){                         //在该语句下用来判断插入数据的先后顺序
              if(lowerLine.indexOf("`sequenceblock`")!=-1){
                  allData.put(1, line);
              }else if(lowerLine.indexOf("`operationlogtype`")!=-1){
                  allData.put(2, line);
              }else if(lowerLine.indexOf("`website`")!=-1){
                  allData.put(3, line);
              }else if(lowerLine.indexOf("`fucdefine`")!=-1){
                  allData.put(4, line);
              }else if(lowerLine.indexOf("`role`")!=-1){
                  allData.put(5, line);
              }else if(lowerLine.indexOf("`department`")!=-1){
                  allData.put(6, line);
              }else if(lowerLine.indexOf("`cmsuser`")!=-1){
                  allData.put(7, line);
              }else if(lowerLine.indexOf("`account`")!=-1){
                  allData.put(8, line);
              }else if(lowerLine.indexOf("`accountrole`")!=-1){
                  allData.put(9, line);
              }else if(lowerLine.indexOf("`flowdefine`")!=-1){
                  allData.put(10, line);
              }else if(lowerLine.indexOf("`flowtask`")!=-1){
                  allData.put(11, line);
              }else if(lowerLine.indexOf("`rolefucperm`")!=-1){
                  allData.put(12, line);
              }else if(lowerLine.indexOf("`templategroup`")!=-1){
                  allData.put(13, line);
              }else if(lowerLine.indexOf("`templatetype`")!=-1){
                  allData.put(14, line);
              }else if(lowerLine.indexOf("`template`")!=-1){
                  allData.put(15, line);
              }else if(lowerLine.indexOf("`contenttype`")!=-1){
                  allData.put(16, line);
              }else{
                  allData.put(num++, line);
              }
           }else{
              bw_create.append(line+"/r/n");
           }
       }
       for (int i = 1; i            if(allData.containsKey(i)){
              bw_insert.append(full_to_part(allData.get(i)));
           }
       }

       bw_create.flush();
       bw_insert.flush();
       br.close();
       bw_create.close();
       bw_insert.close();
    }
// private static void setSequence(){
//
// }

    /**
     * 将一行批量插入的数据转换为多行插入
     * @param line
     * @return
     */
    private static String full_to_part(String line){
       StringBuffer sb=new StringBuffer();
       String lowerLine=line.toLowerCase();
       int firstDan=lowerLine.indexOf("`");
       int firstQuot=lowerLine.indexOf("(");
       String tableName=lowerLine.substring(firstDan, firstQuot);
       System.out.println("--------------------------开始转换插入----"+tableName+"---的数据----------");
       int values_position=lowerLine.indexOf("values")+7;
       String forward_line=line.substring(0, values_position);

       String datas_line=line.substring(values_position,line.length()-1); //得到后面插入的数据
       String[] datas=datas_line.split("//)//,//(");//根据),(分割为一个字符串数组

       for (int i = 0; i            String data=null;
           if(datas.length==1){            //如果只有一条数据，不会被分割的，数组就会只有一条数据
              data=datas[i];
           }else{
              if(i==0)                        //如果是第一条，那么后面需要追加一个括号
                  data=datas[i]+")";
              else if(i==datas.length-1)         //如果是最后一条，需要在前面加一个括号
                  data="("+datas[i];
              else                     //如果是中间的数据，前后都需要加括号
                  data="("+datas[i]+")";
           }
           sb.append(forward_line);           //将insert 字段名和values先行加入
           sb.append(data+";");
           sb.append("/r/n");
       }
       sb.append("/r/n");
       return sb.toString();
    }

}
这是通用方法，以后需要得到MYSQL的两个初始化脚本就通过这个方法已转换就可以。
二、Oracle建表的问题没有通过这个解决，可以通过Hibernate逆向生成。建表和表结构，表关系，都有了。
下面就是插入数据了：
关于MYSQL的insert脚本基本上和Oracle的一致，但是也有不一致，对于日期，对于一些符号’都是问题。将MYSQL的insert语句转换成ORACLE的insert语句。
1、需要将’去掉，这个符号是在插入数据时的表名上括起来的，所以，将表名上的’都替换掉。
2、需要将date日期，经过函数to_date(date,”yyyy-MM-dd”)转换一下，所以我通过正则表达式来查找到所有的日期，还有一种日期，携带时分秒的日期，需要另一个正则，最后当然都是替换成为年月日就可以了。
package com.sql.convert;

import java.io.BufferedReader;
import java.io.BufferedWriter;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStreamReader;
import java.io.OutputStreamWriter;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

public class FromMYSQLInsertToOracleInsert {
    /**
     * 需要转换一下地方：
     * 将所有的表名以及列名中的'去掉
     * @param args
     * @throws IOException
     */

    public static void main(String[] args) throws IOException {
       String mysql_file="C:/Documents and Settings/gaofei/桌面/insertData.sql";
        String oracle_file="C:/Documents and Settings/gaofei/桌面/oracle_insertData.sql";

       turnSQL(mysql_file, oracle_file);

    }
    private static void turnSQL(String mysql_file,String oracle_file) throws IOException{
       File mysqlFile=new File(mysql_file);
       File oracleFile=new File(oracle_file);
       if(!oracleFile.exists())
           oracleFile.createNewFile();
       InputStreamReader isr=new InputStreamReader(new FileInputStream(mysqlFile), "UTF-8");
       OutputStreamWriter osw=new OutputStreamWriter(new FileOutputStream(oracleFile), "UTF-8");

       BufferedReader br=new BufferedReader(isr);
       BufferedWriter bw=new BufferedWriter(osw);

       String line=null;
       while((line=br.readLine())!=null){
           bw.append(convertString(line));
           bw.append("/r/n");
       }
       bw.flush();
       br.close();
       bw.close();
       isr.close();
       osw.close();
    }
    private static String convertString(String line){
       line=line.replace("`", "");
       Pattern p=Pattern.compile("'//d{4}//-//d+//-//d+'");
       Matcher m=p.matcher(line);
       String date=null;
     while(m.find()){
           date=m.group(0);
           line=line.replace(date, "to_date("+date+",'yyyy-MM-dd')");
       }
       p=Pattern.compile("'//d{4}//-//d+//-//d+//s//d+//://d+//://d+'");
       m=p.matcher(line);
       date=null;
       while(m.find()){
           date=m.group(0);
           String newDate=date.substring(0,date.indexOf(" "));
           line=line.replace(date, "to_date("+newDate+"','yyyy-MM-dd')");
       }
       return line;
    }
}

三、看似没有问题了，直接执行脚本就没问题了，但是问题又来了：
         我是直接用控制开的sqlplus来访问Oracle的，没有工具，每次执行脚本，是通过@脚本名.sql来执行的，但是请注意如果是路径太长，比如@”C:/Documents and Settings/gaofei/桌面/oracle_insertData.sql”路径中间有空格需要加上””的，
执行报错。很多错：
1、关于数据中的等这类的特殊，oracle会认为是plsql编程中自定义的变量，当然会出问题了，所以需要设置set define off，关掉自定义变量。
2、一个问题很少会有人用到，就是关于Oracle的插入数据，一个字段最多插入2999个字符。太多将会插入不进的。而我的数据都是模板或新闻，很多多是上万，所以问题来了。没有想到办法。最后我是将很多的数据删除的很短，插入成功后，通过程序一条条插入的。

迁移完成了，下面就是关于Oracle的备份问题了。

说是可以准备SQL脚本，但是脚本到时候还会是INSERT语句，到时候又不能插入了。
所以是通过ORACLE的备份命令，备份出DMP文件。
刚开始是用SYSTEM用户直接备份的，但是会有很多的系统表，这是不符合要求的。于是建立一个用户future ，用future用户逆向生成表结构、表关系。然后通过赋予futureDBA权限，通过insert into sequenceblock select * from system.sequenceblock;将所有表中有数据的表都从system导入到future用户中。
这样表就再future用户的指定表空间中建立出来了。
通过future直接备份数据。有两种备份方式。
$exp future/ylzxdb1219@ylzxdb file=D:/future_final.dmp full=y;
以future身份导出了所有库的信息
$exp future/ylzxdb1219@ylzxdb file=D:/future_user.dmp owner=future;
以future的身份导出了future用户中所有的数据。
但是future有DBA权限，所以会再备份时将系统表备份。所以推荐第二种方式。导出的只是当前用户future的所有表的备份。
导入数据：
         $imp future/ylzxdb1219@ylzxdb fromuser=future touser=future ignore=y file=D:/future_user.dmp;
         （注：例句是将数据库的备份文件放在了D:盘下的future.user.dmp文件）
本文出自 “另类的自由” 博客 bitsCN.com