2

I am looking into migrating my MySQL DB to Azure Database for MySQL https://azure.microsoft.com/en-us/services/mysql/. It currently resides on a server hosted by another company. The DB is about 100 GB. (It worries me that Azure uses the term "relatively large" for 1GB.)

Is there a way to migrate the DB without any or little (a few hours, max) downtime? I obviously can't do a dump and load as the downtime could be days. Their documentation seems to be for syncing with a MySQL server that is already on a MS server.

Is there a way to export the data out of MS Azure if I later want to use something else, again without significant downtime?

Conor Cunningham MSFT
  • 4,151
  • 1
  • 15
  • 21
user984003
  • 28,050
  • 64
  • 189
  • 285

2 Answers2

0

Another approach: Use Azure Data Factory to copy the data from your MySQL source to your Azure DB. Set up a sync procedure that updates your Azure Database with new rows. Sync, take MYSQL db offline, sync once more and switch to the Azure DB.

See Microsoft online help

Daniel
  • 426
  • 3
  • 14
  • The link hardly has any information on how to actually do it. Can I sync a MySQL DB on a server anywhere, with any service, to Azure's MySQL service? This is something you have done? – user984003 Apr 11 '20 at 17:47
  • @user984003 We are talking about a bigger project here - can´t expect us to provide a ready solution. I have done something like this on premises with SSIS and and for selected tables with linked server in MSSQL over ODBC. You´ll have to try and see if this fits your needs, we don´t know your Database. 100GB can be just a lot of poorly optimized tables or highly optimized with billions of rows? Never used Azure Database for anything but Data warehouse (few GB) for Power-BI so I don´t know how it scales. In my scenarios I pushed the data from local MSSQL Server over VPN. – Daniel Apr 11 '20 at 19:40
  • @user984003 But yes, in theory it should let you consume data from any reachable mySQL server and import it (or update etc. full ETL) – Daniel Apr 11 '20 at 19:42
  • Thanks for your comments. I wasn't looking for a ready-made solution - I get this is big! - but some ideas of where to start, and maybe what I hadn't looked at. Or if we have to hire a specialist. – user984003 Apr 11 '20 at 19:47
  • @user984003 No Problem. From what I read from Microsoft I would defiantly look into the Azure Integration service / Data factory as this is set as Microsoft's route to replace SSIS. Could be beneficial to your skill set in the future integrating not only databases but also rest/soap api services etc. Also as you said, I´d double check if the Azure Db can handle such a big database within your performance requirements. My clients until now all use on premises MSSQL (or hosted) for production environment so I don´t know how well the Azure suff scales. – Daniel Apr 11 '20 at 21:54
  • @user984003 Just re-read your original post and saw that I got confused by Microsofts stupid Marketing naming. I thought you wanted to port to a "Azure SQL database", wicht is a trimmed-down version of MS-SQL-Server. I see you want to stay within MySQL, just get the hosted Azure version. Did you see that MS actually has a porting tool for your scenario? [https://azure.microsoft.com/en-us/services/database-migration/](https://azure.microsoft.com/en-us/services/database-migration/) – Daniel Apr 11 '20 at 22:06
-1

Don't underestimate the complexity of this migration.

With 100GB, it's a good guess that most rows in your tables don't get UPDATEd or DELETEd.

For my suggestion here to work, you will need a way to

SELECT * FROM table WHERE (the rows are new or updated since a certain date)

Some INSERT-only tables will have autoincrementing ID values. In this case you can figure out the ID cutoff value between old and new. Other tables may be UPDATEd. Unless those table have timestamps saying when they were updated, you'll have a challenge figuring it out. You need to understand your data to do that. It's OK if your WHERE (new or updated) operation takes some extra rows that are older. It's NOT OK if it misses INSERTed or UPDATEd rows.

Once you know how to do this for each large table, you can start migrating.

Mass Migration Keeping your old system online and active, you can use mysqldump to migrate your data to the new server. You can take as long as you require to do it. Read this for some suggestions. getting Lost connection to mysql when using mysqldump even with max_allowed_packet parameter

Then, you'll have a stale copy of the data on the new server. Make sure the indexes are correctly built. You may want to use OPTIMIZE TABLE on the newly loaded tables.

Update Migration You can then use your WHERE (the rows are new or updated) queries to migrate the rows that have changed since you migrated the whole table. Again, you can take as long as you want to do this, keeping your old system online. It should take much less time than your first migration, because it will handle far fewer rows.

Final Migration, offline Finally, you can take your system offline and migrate the remaining rows, the ones that changed since your last migration. And migrate your small tables in their entirety, again. Then start your new system.

Yeah but, you say, how will I know I did it right?

  1. For best results, you should script your migration steps, and use the scripts. That way your final migration step will go quickly.

  2. You could rehearse this process on a local server on your premises. While 100GiB is big for a database, it's not an outrageous amount of disk space on a desktop or server-room machine.

  3. Save the very large extracted files from your mass migration step so you can re-use them when you flub your first attempts to load them. That way you'll save the repeated extraction load on your old system.

  4. You should stand up a staging copy of your migrated database (at your new cloud provider) and test it with a staging copy of your application. You may be able to do this with a small subset of your rows. But do test your final migration step with this copy to make sure it works.

  5. Be prepared for a fast rollback to the old system if the new one goes wrong .

AND, maybe this is an opportunity to purge out some old data before you migrate. This kind of migration is difficult enough that you could make a business case for extracting and then deleting old rows from your old server, before you start migrating.

O. Jones
  • 103,626
  • 17
  • 118
  • 172
  • MySQL has solved the issue of changed rows with binlogs. It doesn't really answer whether I can use this to migrate to Azure's MySQL service. – user984003 Apr 11 '20 at 17:38