0

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)

5
  • you copy the hole folder with all, after shuting down the first server, and transfer the data to the new server. it starts usually with out a problem. After that you should urgently think about replication. but that shpuldn't take weeks or month, as the data is usually transfered in gigabit connections between servers.
    – nbk
    Commented Nov 15, 2020 at 18:23
  • By "copy the whole folder", you mean the "data" directory and all lower level directories? Will that operation destroy the destination server's DB's, leaving only those that were copied? The destination server is intended to accumulate DB's Am I missing something?
    – StepAndFetchit
    Commented Nov 15, 2020 at 18:28
  • exact. the colplte data directorty, if there are fles missing or you didn't shutdown the original you have to rebuild it and that takes to much time. Evben so try first ti replicate and see where it gets you
    – nbk
    Commented Nov 15, 2020 at 18:33
  • With the lack of response to my question, either this is not the appropriate forum or MariaDB is not capable of replicating a database via copying a tree of files relating to the DB. So, does anyone have recommendation for some SQL database that can be copied instead? (is it possible in PostGreSQL?) Commented Dec 2, 2020 at 1:43
  • @nbk: The process you mention replicates the server. That is Not what I asked. I need to add the database of one server to a different server without destroying the destination servers other databases. This seems like it should be doable, but if so, the process has not been discovered. Copying the directories associated only with the targeted database to an alternate server does not work. Commented Dec 2, 2020 at 1:49

1 Answer 1

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.

3
  • 1
    When its MariaDB, I'd use their fork, mariabackup instead. Its been testing more frequently against the server.
    – danblack
    Commented Nov 15, 2020 at 20:27
  • Good plan. I don't use MariaDB so I assumed Percona XtraBackup would work. But MariaDB has diverged for 10+ years. It's a separate product from MySQL/Percona. Commented Nov 15, 2020 at 20:36
  • 1
    It will in some cases, however given the tight coupling between XtraBackup and the storage engine its prudent to substitute mariabackup in the process. The rest of the process is still compatible between all MySQL/MariaDB/Percona variants.
    – danblack
    Commented Nov 15, 2020 at 21:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.