Solved

MySQL Import & Export

Posted on 2003-11-22
4
80,220 Views
Last Modified: 2011-08-18
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
Comment
Question by:mailsaravanan
  • 2
  • 2
4 Comments
 
LVL 17

Expert Comment

by:Squeebee
ID: 9804428
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
 

Author Comment

by:mailsaravanan
ID: 9808446
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
 
LVL 17

Accepted Solution

by:
Squeebee earned 125 total points
ID: 9810479
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
 

Author Comment

by:mailsaravanan
ID: 9815704
Hi Squeebee,

Thank u for ur immediate help. Keep it up.


Regards
Saravanan
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Loading csv or delimited data files to MySQL database is a very common task frequently questioned about and almost every time LOAD DATA INFILE comes to the rescue. Here we will try to understand some of the very common scenarios for loading data …
Introduction In this article, I will by showing a nice little trick for MySQL similar to that of my previous EE Article for SQLite (http://www.sqlite.org/), A SQLite Tidbit: Quick Numbers Table Generation (http://www.experts-exchange.com/A_3570.htm…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now