Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 868
  • Last Modified:

MySQL Export / Dump and Import

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
adrian78
Asked:
adrian78
2 Solutions
 
Daniel WilsonCommented:
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
 
Ray PaseurCommented:
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
 
Julian HansenCommented:
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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now