Moving a large MariaDB database between MariaDB servers

Question

Once my DB size is multiple hundred GB in size, it is not practical to use the documented methods for transferring a database to an alternate db server due to sheer amount of time (days to weeks) for that slow process. The database is primarily located within a directory tree structure which can easily be copied, however, there seem to be files at the "data" directory (ibdata1, ...) not unique to a specific database, that contain some information that is required to successfully xfer to a new location. Is there a way to extract the needed information from this file or set of files and provide to the new dB server? Or is there some way to cause MariaDB to maintain each database within the appropriate directory tree, thereby permitting file transfers to function? Some background on my objective: I capture data to my databases on a specific server, but that server is limited to only 1TB disk size.
Once the space on that disk reaches a threshold level (say 10% remaining), I want to move that database to an alternate server with much greater storage available, and free the space on the capturing server.

Stating the objective a little differently: I wish to take a db from one server(1) and add that db to a different server(2), allowing all tables in the "copied" db(1) to be emptied, freeing space for additional collection. (but do so in finite time -- minuites/hours instead of days/weeks)

Answer 1

At my job, we do this several times per week. I'm part of a team who manages about 9000 MySQL database instances, and we're constantly moving them from server to server as they grow.

NOTE: You cannot just copy files from a MySQL / MariaDB datadir while the server is running. You are virtually guaranteed to corrupt your database. Some subset of the data is in log files or in RAM, and it won't be copied with consistency.

We do it this way:

1. Use Percona XtraBackup to make a physical backup. It doesn't take long, even for a multi-hundred-GB database instance.

2. Transfer this backup to the other host.

3. Restore the backup on that new host.

4. Configure replication on the new host, to subscribe to changes on the original host. This allows it to "catch up" to all the changes that occurred while you were transferring the file and restoring them.

5. Wait for the new instance to catch up.

6. Set the original instance to be read-only, so applications can't make any new changes. Then let the replica catch up with respect to the final few seconds of changes (this might take no time at all).

7. Update applications to use the new server instead of the old one. This is done either by changing configuration in the app, or else just use DNS to give the name of the original server to the new server.

This is all assuming you want the whole MySQL instance to be moved to a new server. If you have individual schemas in the original server that you want to move, that's a bit more complicated.