Shulou(Shulou.com)05/31 Report--

This article introduces the relevant knowledge of "the migration from MYSQL to oracle and the method of backup". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

First, MYSQL can import and export scripts, should have been ready for the backup of the database initialization database and initialization data of the two initialization scripts, exported through MYSQL to a full.sql total initialization script, those who have used mysql know, the export of the database script, including building a database, building tables, and inserting data statements. It needs to be divided into two: the initialization script for creating the library, the initialization script for creating the table, and the initialization script for inserting data. So I wrote a class to split the SQL script. In the class, I found the string immediately following the beginning of create table, that is, the name of the table, took out the statement at the beginning of insert, and changed the line to insert data. Therefore, every line of the file is read through the file stream, if it is inserted, it is taken out and put into a method convertInsertSQL, and then written into a script file insert_data.sql. The rest is written into a script file create_table.sql, and the method convertInsertSQL handles the insert statements. Because the insert statements in the script are inserted in batches, the insert into table name (column name) values (corresponding value), (corresponding value), needs to be split into statements such as insert into table name (column name) values (corresponding value), so I truncated the previous values (the previous value) Pass all the remaining corresponding data, (use regular division into String arrays, loop the array, each append to form an insert statement, and finally write all the insert statements back to the file stream.

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

/ * *

* script that exports the mysql database

* split into two scripts for building a database and a table

* @ author gaofei

*

, /

Public class MyFileReader {

/ * *

* @ param args

, /

Public static void main (String [] args) {

String full_sql_path= "C:/Documents and Settings/gaofei/ Desktop / final mysql script (12.15) .sql"

String create_sql_path= "C:/Documents and Settings/gaofei/ Desktop / Establishment Database initialization. SQL"

String insert_sql_path= "C:/Documents and Settings/gaofei/ Desktop / data initialization. SQL"

Try {

ReadFull_to_insert (full_sql_path, create_sql_path, insert_sql_path)

} catch (IOException e) {

System.out.println ("file read or write error")

E.printStackTrace ()

}

/ / String aa= "insert into `templatetype` (`id`, `temtypename`, `defdate`, `defvar`) values (0meme 'generic template type', 0meme null null), (1meme 'homepage template type', 0meme null null), (2pari 'column template type', 0pl null), (3pari 'thematic template type', 0blaster null), (4recital 'content template type', 0minute null null), (5pm 'message template type', 0null null) NULL), (6 'voting template type', 0 'null null'), (7 'special template type', 0 'null null') "

/ / String bb=full_to_part (aa)

/ / System.out.println (bb)

}

/ * *

* split the overall exported mysql script into scripts for building databases, creating tables and inserting data

* convert the bulk insert data statements into scripts for each insert data statement

* @ param full_sql_path original fully exported mysql script

* @ param create_sql_path dismantled the script for building database and table

* @ param insert_sql_path detached insert data script

* @ 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")) {/ / is used under this statement to determine the order in which data is inserted.

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 ("`roomt`")! =-1) {

AllData.put (8, line)

} else if (lowerLine.indexOf ("`roomtrole`")! =-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 < num; iTunes +) {

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 () {

/ /

/ /}

/ * *

* convert one row of bulk inserted data to multiple row inserts

* @ 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 ("- start conversion insert -" + tableName+ "--data -")

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); / / get the data inserted later

String [] datas=datas_line.split ("\\)\\,\\ ("); / / based on), (split into an array of strings

For (int I = 0; I < datas.length; iTunes +) {

String data=null

If (datas.length==1) {/ / if there is only one piece of data and will not be split, the array will have only one piece of data

Data=datas [i]

} else {

If (iComple0) / / if it is the first one, you need to append a parenthesis after it

Data= [I] + ")"

Else if (i==datas.length-1) / / if it is the last item, it needs to be preceded by a parenthesis

Data= "(" + datas [I]]

Else / / if it is intermediate data, parentheses are required before and after

Data= "(" + datas [I] + ")"

}

Sb.append (forward_line); / / add the insert field name and values first

Sb.append (data+ ";")

Sb.append ("\ r\ n")

}

Sb.append ("\ r\ n")

Return sb.toString ()

}

}

This is a general method, and you need to get two initialization scripts for MYSQL later and you can just use this method to convert it.

Second, the problem of building tables in Oracle is not solved through this, but can be generated in reverse through Hibernate. Build table and table structure, table relationship, all have.

Here's how to insert the data:

The insert script for MYSQL is basically the same as Oracle's, but there are also inconsistencies, for dates, for some symbols'is a problem. Convert the insert statement of MYSQL into the insert statement of ORACLE.

1. You need to remove the 'symbol, which is enclosed in the table name when you insert the data, so replace the' on the table name.

2, need to date date, after the function to_date (date, "yyyy-MM-dd") to convert, so I use the regular expression to find all the dates, there is a date, with minutes and seconds of the date, need another regular, and finally, of course, replace it with the year, month and day.

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 {

/ * *

* need to change places:

* remove 'from all table names and column names

* @ param args

* @ throws IOException

, /

Public static void main (String [] args) throws IOException {

String mysql_file= "C:/Documents and Settings/gaofei/ Desktop / insertData.sql"

String oracle_file= "C:/Documents and Settings/gaofei/ Desktop / 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+\\ -\\ dholders'")

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+\:\\ dcards'")

M=p.matcher (line)

Date=null

While (m.find ()) {

Date=m.group (0)

String newDate=date.substring (0ddate.indexOf (""))

Line=line.replace (date, "to_date (" + newDate+ ", 'yyyy-MM-dd')")

}

Return line

}

}

Third, there seems to be no problem, it is no problem to execute the script directly, but here comes the problem again:

I access Oracle directly with the control-open sqlplus. There is no tool. Every time a script is executed, it is executed through @ script name .sql, but please note that if the path is too long, for example, there is a space in the @ "C:/Documents and Settings/gaofei/ Desktop / oracle_insertData.sql" path that needs to be added "".

Execute the error report. A lot of mistakes:

1. With regard to such special things as those in the data, oracle will think that it is a custom variable in plsql, and of course there will be a problem, so you need to set set define off and turn off the custom variable.

2. A problem that few people use is about the insertion data of Oracle. A field is inserted with a maximum of 2999 characters. Too many will not be plugged in. And my data are all templates or news, many of which are tens of thousands, so the problem comes. I can't think of a way. Finally, I delete a lot of data very short, insert successfully, insert one by one through the program.

Now that the migration is complete, here are the backup questions about Oracle. Www.2cto.com

It is said that the SQL script can be prepared, but the script will still be an INSERT statement and cannot be inserted at that time.

Therefore, the DMP file is backed up through the backup command of ORACLE.

At first, it is backed up directly by SYSTEM users, but there will be a lot of system tables, which does not meet the requirements. So a user future is established, and the table structure and table relationship are inversely generated by the future user. Then import all tables with data in the table from system to the future user through insert into sequenceblock select * from system.sequenceblock; by granting futureDBA permission.

In this way, the table is created in the specified table space of the future user.

Back up data directly through future. There are two ways to back up.

$exp future/ylzxdb1219@ylzxdb file=D:/future_final.dmp full=y

Exported the information of all libraries as future

$exp future/ylzxdb1219@ylzxdb file=D:/future_user.dmp owner=future

All the data in the future user is exported as future.

However, future has DBA permission, so the system table is backed up when it is backed up again. So the second way is recommended. What is exported is only a backup of all tables for the current user future.

Import data:

$imp future/ylzxdb1219@ylzxdb fromuser=future touser=future ignore=y file=D:/future_user.dmp

(note: for example, the backup file is placed in the future.user.dmp file under the D: disk)

This is the end of the introduction of "Migration from MYSQL to oracle and backup methods". Thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.