0

I have a 150+GB database I need to migrate to a new host. Most of the data is event logs, which I already partitioned "by hand" (into table_2020 etc.), and moved all past years (10 GB per year, for a sense of scale) into a completely separate, archival database. Having slimmed down the main database to about 10 GB total, I can probably migrate it quickly enough. Once I have the current data on the new host, operating correctly, I'll migrate the archives (they won't get any updates for sure, so they can wait). But I wonder if there's a better approach I could be taking:

  • Is it possible to cold-backup-and-restore individual databases? There's this "tablespace" thing that keeps not recognizing my files, how do I force a starting mysqld to just use the database folders (I have file-per-table options set) and not try to use any "common file" nonsense?
  • Is it possible to manage table partitions like a cold backup, that is, copy just the new partitions, and later copy the older ones as needed?

Edit: The tables in question are InnoDB, but there are some smaller MyISAM tables in the db, too. Edit2: I'm moving from MySQL 5.6.51 to MariaDB 10.6.20.

Sinus tentaclemonster
  • 101
  • 2

1 Answers1

0

Research "Transportable Tablespaces" for InnoDB. That should let you move each table one at a time as a file. (You cannot simply move a .ibd file from one server to another -- there is some info about that table in other places.)

We need more details if you would like to discuss how to further improve the storing of "logs" and the retrieving of info from them.

Rick James
  • 80,479
  • 5
  • 52
  • 119