I have to migrate a table from MSSQL Server to MySql. The problem is that the table is quite big (65 millions records) and the whole process takes too much time. Does anyone have any idea how to speed things up ? Any useful tools that could improve this?
1
-
Similar answer: stackoverflow.com/questions/11619177/…Jacob– Jacob2012-08-02 11:30:41 +00:00Commented Aug 2, 2012 at 11:30
Add a comment
|
7 Answers
Need to do it only once? Don't waste too much time optimizing, wait 'till it's through and move on.
Need to do it more often? Then elaborating what tools/techniques you use currently would be helpful.
Comments
Here is how I did to migrate a 800K records table from MS Sql Server to MySQL.
Create a query to display the data in a tabular format :
SELECT [PostalCode] + ' ' +
[StateCode] + ' ' +
[Latitude] + ' ' +
[Longitude] + ' ' +
[CityName]
FROM [dbo].[PostalCode]
Execute the query with SQL Server Management Studio, and select to output the results into a file (Menu : Query -> Results To -> Results to File)
The filename must be the name of the table in MySQL. The file extension doesn't matter.
Then use mysqlimport.exe (on Windows) to import the data (the table must exist in the MySQL database) :
mysqlimport.exe --user=user_name
--columns=postalcode,statecode,latitude,longitude,cityname
--ignore-lines=2 databaseName pathToFile
After the import, I had to delete the last 2 records of the table because the end of the file contained some garbage : (818193 row(s) affected)
For 800K it is pretty quick : 10 seconds to export, and then 10 seconds to import.
Hope this helps.
Comments
you could export the data to text and then use the mysql load statement:
load data local infile '/somefolder/text_file_with_data.txt' into table some_table fields terminated by '\t' lines terminated by '\n'
or if you put the data file on the mysql server you can:
load data infile '/somefolder_on_the_mysql_server/text_file_with_data.txt' into table some_table fields terminated by '\t' lines terminated by '\n'
I am not sure what the syntax for mssql is to export
You can always export in sets of 10,000 or 100,000.
Comments
Make sure that the mysql tables initially have no indexes; add them once the loads are finished.
Comments
Make sure the MySQL table storage file is big enough before starting insertion by using such a statement in your my.ini file:
innodb_data_file_path=ibdata1:1000M:autoextend
Comments
Try if you can get the data out of mssql and into mysql by using the SSIS/DTS packages you can generate with the import/export wizard of SQL Server. (Connect to MySQL with the appropriate OLEDB/ADO provider.
Start it when you head home on friday, and check back after the weekend ;)
Comments
Make sure no columns have an index on them, and delete any foreign keys (if you are using InnoDB for the table type) for the import.
