[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 80272
  • Last Modified:

MySQL Import & Export

Dear friends,

In my client side, they are using different versions of software products in various places. They are using Same/Different MySQL versions depending upon the different versions of software products. The database schema structures may be differed in all versions of software products. They want to convert into common latest release of software product. So the target version is fixed now. So the target database schema structure also fixed now. Now i need to transfer data from all the previous versions into latest version. What my problem is.... The previous versions of all database table structures are differed depending upon the versions(not a database version). Ultimately the latest version database schema structure also differed. How can i transfer the data from previous versions (each version differs with table structure) into latest version without any problems. Which will be the best way to transfer data between previous versions & latest version?

What my idea is... First I may export data into csv/text files from previous versions & import data into MySQL DB from text files.  But the problem is... the previous versions all differed with schema structures.

1. What will be the best way to transfer datas from different previous versions into latest version?
2. All previous versions of schema structures is having BLOB data types also. Is it make any problem to transfer datas from previous versions into latest version?

Waiting for valuable replies.
0
mailsaravanan
Asked:
mailsaravanan
  • 2
  • 2
1 Solution
 
SqueebeeCommented:
I recently finished a conversion to a new version that had a different schema.

I copied the database to another database called version2 (using the file system copy commands while the server was not running).

I then created a new database called version3 and ran table creation statements so that I had the schema in place.

I then created a series of SQL statements to copy the data. This involved

INSERT INTO version3.tablename SELECT column1, column2, column5 FROM version2.differenttablename;

Once I had created all SQL statements (I stored each one in a text file when it worked properly), I then blew out both databases and recreated them for a second test run by copying all SQL statements from the text file and pasting them into the mysql command-line client. When I confirmed that this worked with no data loss, I created the final version of the new database, and ran the SQL statements against the live database. (Search and replace database2 with the live database name and database3 with the new database name).

And that worked great for me. YOu really won't find a prebuilt tool, you pretty much need to build a series of SQL statements.
0
 
mailsaravananAuthor Commented:
Dear Squeebee,

Thank u for ur valuable comments. Your point is really acceptable one. But I have a lot of problems with source databases & destination databases. Tables are not only differed with columns. They differed with PK, UK, Check constraints also(I am new to MySQL DB also). The source databases are available in large amount in client side. So they dont want hardcode values like using database names, etc. They want some generalized format to transfer data from source databases into destination database(like give some predefined text csv format according to destination database). If anything is possible, let me know & mail me mailsaravanan_24@yahoo.com.

Then when i am using mysqldump utility while creating the csv text file, the following error occurred(i am using telnet utility to connect my linux server). How to resolve this error? How to run the Mysqld daemon? whether below command will work in server side only? Is it possible to execute mysqldump utility using telnet services?

Shell> mysqldump -uroot --tab='/home/sridharp/samples' test text

mysqldump: Got error: 1: Can't create/write to file '/home/sridharp/samples/text.txt' (Errcode: 13) when executing 'SELECT INTO OUTFILE'

Is there any problem to transfer BLOB data from source databases into destination database. Is there any external BLOB available like oracle(Bfile concept)? If external Blob's are available, how to transfer Blob data into destination database?

waiting for ur immediate response

Regards
Saravanan
0
 
SqueebeeCommented:
You error means mysqldump could not get read permission on the /home....samples file. You need to make sure that your user has root permissions and that the mysql server daemon can write to the /home...samples file. mysqldump can be run via telnet. BLOB data can be handled via mysqldump.
0
 
mailsaravananAuthor Commented:
Hi Squeebee,

Thank u for ur immediate help. Keep it up.


Regards
Saravanan
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now