MySQL Import & Export

Posted on 2003-11-22
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.
Question by:mailsaravanan
  • 2
  • 2
LVL 17

Expert Comment

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.

Author Comment

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

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

LVL 17

Accepted Solution

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.

Author Comment

ID: 9815704
Hi Squeebee,

Thank u for ur immediate help. Keep it up.


Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
MYSQL simple update statement 3 52
PHP connection to remote AWS MySQL RDS 4 93
count download link and run update query 9 70
Help needed with Powershell  XML to MySQL 5 38
Fore-Foreword Today (2016) Maxmind has a new approach to the distribution of its data sets.  This article may be obsolete.  Instead of using the examples here, have a look at the MaxMind API ( …
As a database administrator, you may need to audit your table(s) to determine whether the data types are optimal for your real-world data needs.  This Article is intended to be a resource for such a task. Preface The other day, I was involved …
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…

785 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