Solved

MySQL Export / Dump and Import

Posted on 2014-01-20
3
810 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 109

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 55

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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Suggested Solutions

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Creating and Managing Databases with phpMyAdmin in cPanel.
The viewer will learn how to dynamically set the form action using jQuery.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.

840 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