Migrating data from one database to another database in mysql

Question

Is it possible to migrate related data but with different column names from one database to another database? and im talking about very large amount of data here. someone has an idea? I have tried exporting it to CSV and importing to the otherdatabase but i get errors saying:

invalid column count in CSV input in line 1

So if anyone has an efficient and effective way of doing this please share. Or if anyone can guide mo through this CSV mapping in excell on how to properly do it, i would really appreciate

Answer 1

There are two cases in this:

1. The databases are stored in the same server
2. The databases are stored in different servers

Solution 1: Databases in the same server

You just need to insert the data in the destination table:

insert into dbDestination.tblDestination (field1, field2, ...)
select ...
from dbSource.tblSource

Notes

1. The select statement must include the fields you need to copy to the destination table.
2. The fields in the select statement must be in the same order as the fields specified in the field list in the insert portion

Solution 2: Databases in different servers

I would export the data to a plain text file, and then import it. I personally prefer .csv files, but it's up to you.

You have two possibilities: To use select... into outfile or to use the system terminal (command window).

a. Using select... into outfile and load data

1. In the server where dbSource is:

   select ...
   from dbSource.tblSource
   into outfile [your destination file]
   ...
   

2. Copy the file to the destination server.

3. In the server where dbDestination is:

   load data local infile [your file] ...

Notes

1. The fields in the select statement must be in the same order as the fields specified in the field list in the insert portion
2. Check MySQL reference manual for the appropriate usage of select... into outfile and load data...

b. Using the terminal

1. In Linux (or other Unix systems), open a terminal window and enter the following command:

   $ mysql -h [sourceServer] -u [yourUser] -p[yourPassword] dbSource -e"select ..." | sed 's/\t/,/g' > yourDestinationFile.csv
   

2. Copy the file to the destination server

3. Start MySQL console, and use `load data ...``

Notes

1. The fields in the select statement must be in the same order as the fields specified in the field list in the insert portion
2. The | sed 's/\t/,/g' part converts the output of the mysql query to a .csv file. You can use another separator instead of ,. For further reference about sed check http://lowfatlinux.com/linux-sed.html
3. The destination file will have row headers, so you will need to ignore them when you import the data. Simply add ignore 1 lines at the end of the load data... sentence.

---

To copy data from one database to another is a very simple task. Hope this points you in the right direction.

A word of advice: Download the reference manual for your MySQL version and keep it at hand. You can find most of your solutions there.

Answer 2

A simple way to do this is with the following syntax:

INSERT INTO Database.Table(field 1, field 2,...)
SELECT * FROM Database2.Table2;