Solved

MySQL Export / Dump and Import

Posted on 2014-01-20
3
838 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 110

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 58

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

Three Considerations for Containers

Containers like Docker and Rocket are getting more popular every day. In my conversations with customers, they consistently ask what containers are and how they can use them in their environment. If you’re as curious as most people, read our article on Experts Exchange.

Question has a verified solution.

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

This article discusses how to create an extensible mechanism for linked drop downs.
Originally, this post was published on Monitis Blog, you can check it here . In business circles, we sometimes hear that today is the “age of the customer.” And so it is. Thanks to the enormous advances over the past few years in consumer techno…
The viewer will learn how to dynamically set the form action using jQuery.
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

623 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