mailsaravanan
asked on
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.
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.
ASKER
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/samp
mysqldump: Got error: 1: Can't create/write to file '/home/sridharp/samples/te
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
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/samp
mysqldump: Got error: 1: Can't create/write to file '/home/sridharp/samples/te
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hi Squeebee,
Thank u for ur immediate help. Keep it up.
Regards
Saravanan
Thank u for ur immediate help. Keep it up.
Regards
Saravanan
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.differenttablenam
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.