Solved

MySQL Export / Dump and Import

Posted on 2014-01-20
3
817 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 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 56

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

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
MS SQL Conditional WHERE clause 3 36
SQL query to select row with MAX date 7 39
Change a button after e-mail 4 19
Survey branching tutorial 11 36
I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
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…
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

679 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