Migrate 10 Gb database from MSSQL to MYSQL
-
Hi All,
Does anyone have experience in moving /migrating database from mssql to mysql. I have tired in test server, 10gb took more than 4 hours by using mysql workbench tool (Still running with unresponsive state). I am thinking to export each table to CSV and import it.
Any suggestion pls.
-
Question: what kind of connectivity do you have between the two instances?
Migrating the data via CSV export/import is one of the most labour-intensive methods.
-
Hi,
I have created ODBC connection.
- Eirikur Eiriksson wrote:
Question: what kind of connectivity do you have between the two instances?
Migrating the data via CSV export/import is one of the most labour-intensive methods.If you have any other suggestion for connectivity and data transfer, I can try that as well. Only issue we cannot go with cost to buy any tool.
-
Questions:
- What is the operating system of the MySQL server?
- Can you connect in both directions, just thinking if you have both push and pull options?
- Have you created the schemas and objects in the MySQL database?
- Are there constraints in the database (unique, foreign key etc.)?
-
Can you make a linked server to MYSQL ? Then use that to push data ?
- homebrew01 wrote:
Can you make a linked server to MYSQL ? Then use that to push data ?
I have linked server from MS to MYSQL, How to push the data? Cannot do insert into select * from mssql.
-
if workbench is that slow it may have to do with your server specs - or with your network.
can you tell us the specs of both servers - as well as the route taken between source , workbench and destination.
e.g. is it local network, is it cloud, mix of both.
and is workbench running in windows or linux
-
Further on Frederico's post, can you supply the answers to my questions and his, please? Without the information, it is going to be really hard to help you!
A third-party tool for the job is around $200, an elbow grease method might take you a few days
- frederico_fonseca wrote:
if workbench is that slow it may have to do with your server specs - or with your network.
can you tell us the specs of both servers - as well as the route taken between source , workbench and destination.
e.g. is it local network, is it cloud, mix of both.
and is workbench running in windows or linux
Hi,
I am doing testing, both SQL and MYSQL on same server. I tis a VM running on Vcenter.
Specs - 4 vCPU, 8 GB of RAM.
I can migrate small database ex: 500 MB. I have one 7 GB database, tired to exclude top 5 big tables as well.
-
and you running workbench on same server?
if so then in all likelyhood your server is maxed - such a small spec for 2 db servers is bad - and if you running user side programs on it then even worst.
but speak with your VM admins to see how performance on that server while you are doing the migration.
if you are running on your own pc then you still need to tell us what your network is (between your PC and the serve)
one other thing you can try is to use the file based migration option on the workbench - for such a small db it should work ok.
-
Hi,
I do not think it is VM or resource issue. I have same setup in my local laptop as well, 8 CPU with 16 GB RAM on SSD. DB size is 1 GB, same happening in workbench. It says not responding and I left for 2+ hours and killed after that.
Is there any other best way to move data from MSSQL to MYSQL.
-
DID you try the script file option on workbench?
-
so... decided to experiment it myself.
Workbench data migration is a pile of crap.. no wonder you are having issues.
your best option is to use the option "create a batch file to copy the data" or the "create a shell script to use native server dump".
in either case the scripts maybe incorrect (the second one is for sure) and depending on the datatypes on your source tables you may need to change the queries within the scripts (if you have anything defined as SYSNAME you need to convert them as SYSNAME is NVARCHAR but workbench treats them as VARCHAR so you may loose data)
if using the first option you may need to change the server "max_allowed_packet" - required for some big column sizes
if using the second option you need to ensure that the startup variable/config --secure-file-priv is set to the folder you put your files in to be imported.
good thing is that either of these 2 options are fast enough
-
Thank for your valuable time frederico_fonseca for testing it out.
I have tried earlier the other two methods as you said. It is bit complicated with create schema should match with source and target and collation etc. Following is the error, I just tried to load single table, I have 100+ tables, not sure How long it would take to load each tables one by one with fixing error.
Also, other important thing I have noticed - Data is mismatching after a load between MSSQL and MYSQL.
Ex: I picked random row - let's says where ID= 100, in MYSQL ID 100 in MYSQL having lot of missing data, since I have given data type as longtext.
20:15:41 LOAD DATA INFILE 'db_xx_#####_import/xxx.csv' INTO TABLE xxx
FIELDS TERMINATED BY ',' ENCLOSED BY '' Error Code: 29. File
'G:\ProgramData\MySQL\MySQL Server 8.0\Data\dxxx.csv'
not found (OS errno 2 - No such file or directory) 0.000 sec
20:25:22 CREATE TABLE IF NOT EXISTS
(xxDetail_IDBIGINT NOT NULL,xx_Master_ID` BIGINT NOT NULL,xxIE_IDBIGINT NULL,xxFU_IDBIGINT NULL,MCODEVARCHAR(32) CHARACTER SET 'utf8mb4' NULL,RequestedDATETIME(6) NULL,ScheduledDATETIME(6) NULL,ExecutedDATETIME(6) NULL,BodyPartVARCHAR(32) CHARACTER SET 'utf8mb4' NULL,E_PDescVARCHAR(0) CHARACTER SET 'utf8mb4' NULL,S_HeadingVARCHAR(0) CHARACTER SET 'utf8mb4' NULL,E_HeadingVARCHAR(0) CHARACTER SET 'utf8mb4' NULL,SignPathVARCHAR(150) CHARACTER SET 'utf8mb4' NULL, PRIMARY KEY (xxDetail_ID), CONSTRAINTFK_tblxxsDetail_tblxxIEFOREIGN KEY (
xxIE_ID) REFERENCES xx.xx(xx_ID`)ON DELETE NO ACTION ON UPDATE NO ACTION) 0 row(s) affected,
3 warning(s): 1681 Integer display width is deprecated and will be removed in a future release.
1681 Integer display width is deprecated and will be removed in a future release. 1681 Integer display width is
deprecated and will be removed in a future release. 0.063 sec
20:36:42 LOAD DATA INFILE 'xxx.csv' INTO TABLE xxx FIELDS TERMINATED BY ',' ENCLOSED BY ''
Error Code: 1366. Incorrect integer value: '' for column 'xx_ID' at row 1 0.000 sec
20:52:28 LOAD DATA INFILE 'xxx.csv' INTO TABLE tblxxsDetail FIELDS TERMINATED BY ',' ENCLOSED BY ''
Error Code: 1300. Invalid utf8mb4 character string: ' and the xx has had conservative care with several ' 0.000 sec
20:54:41 SET sql_mode = "" SET NAMES utf8mb4 Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your
MySQL server version for the right syntax to use near 'SET NAMES utf8mb4' at line 2 0.000 sec
21:26:01 LOAD DATA INFILE 'xxxxloc.csv' INTO TABLE tblLocations FIELDS TERMINATED BY ',' ENCLOSED BY ''
Error Code: 1300. Invalid utf8mb4 character string: 0.000 sec
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply