Solved

MySQL Import & Export

Posted on 2003-11-22
4
80,225 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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
How do i connect MySQL on Visual Studio 3 57
MySQL database data submission 7 61
Insert data into database 2 37
myqsl update statement on phpMyAdmin 8 22
Both Easy and Powerful How easy is PHP? http://lmgtfy.com?q=how+easy+is+php (http://lmgtfy.com?q=how+easy+is+php)  Very easy.  It has been described as "a programming language even my grandmother can use." How powerful is PHP?  http://en.wikiped…
Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
This is used to tweak the memory usage for your computer, it is used for servers more so than workstations but just be careful editing registry settings as it may cause irreversible results. I hold no responsibility for anything you do to the regist…

863 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

23 Experts available now in Live!

Get 1:1 Help Now