Solved

MySQL Export / Dump and Import

Posted on 2014-01-20
3
789 Views
Last Modified: 2014-01-22
Hello,
I am working with a large mysql database (a couple GB) and some tables are very large.  I am wondering how I can export data from one database and import the data into another mysql database on a different server.

So far, I've got these parts of the process working:

- Exporting of data into a .sql file
- Copying of the data from server to server
- I can import the data if I was happy with the table names.

The problem is the table names are different.  The structure is the same.

I can just import the data then copy the data from the old table to the new one and delete the old one but I'm wondering if it's possible to…

Import the .sql data into the table (which has a different table name) directly.  I know there's probably a way to do a replace of the .sql since that's just a text file… however, how do you know if the data isn't getting replaced as well since some of the table names are quite generic (like "users")?

I can have the exported .sql in separate files so that each export dump contains different table data if that's easier.

Thanks!
0
Comment
Question by:adrian78
3 Comments
 
LVL 32

Accepted Solution

by:
Daniel Wilson earned 250 total points
ID: 39795838
MySqlDump will wrap all the table and field names in ` marks, so any reference to the users table will be `users`.

That will enable a search/replace.

However, are you loading any auto_increment fields?  You can't have them going in on top of the ones that already exist, assuming the target DB also has data.  If you have that to deal with, you will need copy queries that deal with changing PK/FK entries.
0
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 39795949
If your servers are professionally hosted, your hosting company may be able to make the copy/move with a "tarball" in a matter of minutes, at most.  Worth asking them!
0
 
LVL 51

Assisted Solution

by:Julian Hansen
Julian Hansen earned 250 total points
ID: 39796097
If I understand your question

1. You are able to dump the source database to a .SQL file
2. The structure of the tables in the two databases is identical - but the names are different i.e.

abc_table1 in the source is def_table1 in the target

3. You want to replace the data in the target with the data in the source (I am assuming this)

Based on the above

1. Dump source to SQL file
2. Open file with text editor and change the names of the table - this might be done with a regular expression search and replace if the target names are sufficiently similar to the source names for instance in the above

Search abc_(.*)
Replace def_\1

Otherwise a manual rename will do the trick.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…

707 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

17 Experts available now in Live!

Get 1:1 Help Now