I'm in the process of potentially migrating my entire website from a dedicated physical server located in Ireland to a DigitalOcean server located in America (New York). We'll also be moving from MySQL 5.7 to MySQL 8.
When I first migrated the site (sans this 35GB table) it was almost 5GB and took maybe an hour and a half. I then had the site on the DigitalOcean server connect to the source server in Ireland for MySQL to test performance out of curiousity. There was about a 300-600ms delay connecting back to the server in Ireland. This could partly be caused by the weakish DigitalOcean droplet, which I plan on resizing before launch to a larger server.
My plan is to use mysqldump to send this remaining 35GB data but I am worried about:
- How long this will take. The table is pretty heavily used on the source server so I will probably have to lock it when sending.
- If the millisecond delay from Ireland to America will heavily affect this.
- Having it potentially time-out will be an issue, especially if the table is being locked and I then need to try it.
Are there any suggestions or optimization routes I can look into before I begin this task?