How to speed up the transfer of data from MSSQL to MySQL?

Question

PHP

Tables are dumped from the MSSQL database to .CSV and loaded into MySQL on another server, recently the tables have become very heavy and such a download takes an hour.

The problem is that at the time of import, MYSQL is heavily loaded and tells clients mysql server has gone away

Answer 1

1. Spread the load over time. Transfer data more often in smaller portions so as not to load the main database for an hour.
2. Build a master-replica architecture. Write only to the master. Read only from the replica. Then replica users won't have "mysql server has gone away" problems.
3. When transferring, does the record occur in the same tables from which users read? Perhaps it is worth writing to some temporary tables and replacing them after completion. In order not to block the main tables.
Well, actually - what prompted you to build such a strange architecture, where you need to transfer a large amount of data from one database to another on a non-regular basis?

Answer 2

mysql server has gone away is an error when you run the script, you connect to the database, then you do not use it for a long time (for example, you parse the file and prepare the data), and after the time (wait_timeout) the database closes the connection, and you try to work with it . There are several solutions here, either check the connection and reconnect when it breaks, or increase the time in the wait_timeout setting.

Answer 3

Throw away the export step.
Set up replication using this utility.
Then you will not transfer the entire database, but only the changes
https://www.symmetricds.org/doc/3.12/html/tutorial...