Migrating mariaDB database to new server and new mariaDB version, and with raw innoDB partitions

Question

I have a linux server running mariaDB-5.5; the tables are all (I think) innoDB tables and the data are stored on a raw partition; actually on two raw partitions because some years ago at the last hardware upgrade, we added a second one for growth capacity. I've just checked and right now, the "data length" + "index length" of my tables comes to about 95GB.

I'm about to migrate to new storage and servers -- and OS, so will be using mariaDB-10.5 (or I may go for 11.4 instead of the OS-supplied version). As far as I can see from the docs, this should not have any major issues, but I'd appreciate expert/exsperienced thoughts on some points, which are interconnected:

1. Raw partitions: does it make any real difference if I continue with multiple raw filesystems vs making one big one?
2. Is a raw partition still sensible? This was set up by my predecessor, but reading around the docs, I'm not entirely sure that this is likely to give an improvement in performance vs the "one file per table" config. Any thoughts? How to do speed tests that reflect the usage in a production environment is not really clear to meL: I don't have the capacity to create a dev clone of the live system.
3. mysqldump vs dd. Last time I did this, I just used dd to literally byte-wise copy the old raw partition to the new one. That's why we have two partitions because this of course precluded resizing it. This is partly because mysqldump produces a very large file, and partly because I'm slightly scared that there will end up with a foreign key deadlock or something silly like that if I go that route. On the other hand, using dd requires me to create raw partitions on the new machine, exactly duplicating the number and size on the old one (hence question 1) which is obviously somewhat restrictive.

And I guess while I'm here, any nasty gotchas that I haven't foreseen that I should be aware of?

I will read on up the percona toolkit options for this as well, but haven't got that far yet!

Answer 1

Welcome to your upgrade.

Given 10.5 has about 1 more year of maintenance a jump to 11.4 sounds prudent and consistent with your infrequent updates so far.

Raw partitions as you've discovered are a bit of administrative mess to manage. In a filesystem, the filesystems purpose is to provide as direct access to raw storage as possible. While is not 0 overhead, the few cpu cycles of a filesystem processing are little in comparison to the raw storage read/write speed.

For this reason, I recommend dropping the raw partitions in your next server.

Minimising downtime, create the new server as a replica of the current one. Key aspect here is you'll need binary logging enabled on the current server, and taking a point in time snapshot that records the current data position.

Long transactions of mysqldump (now called mariadb-dump) are of concern if (non-temporary) tables are created/deleted/altered during the dump. If this isn't the case, it might be possible to stream a mysqldump into mariadb (the client formerly known as mysql) to populate the new server. Take note of using --dump-slave and --single-transaction. change master to should be executed to list the master_host and after that replication should start. Ensure your new server has large innodb buffer pool and innodb log file size to facilitate a large number of writes.

As a trial run to test process, maybe do a first step with mariadb-dump --no-data .... to ensure that table structures are copied. Replication won't work, but making sure you have all steps in the migration understood.

When complete, a small outage:

1. stop application
2. ensuring that replication is caught up on the new server
3. stop replica, reset replica and
4. switch the application traffic to the new database
5. start application