Mysql – Moving a large MariaDB database between MariaDB servers

mariadbMySQL

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)

Best Answer

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:

Use Percona XtraBackup to make a physical backup. It doesn't take long, even for a multi-hundred-GB database instance.
Transfer this backup to the other host.
Restore the backup on that new host.
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.
Wait for the new instance to catch up.
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).
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.

Related Solutions

Mysql – “freeing items” taking forever on inserts, updates and deletes

My knowledge of MySQL is somewhat limited so I may be entirely wrong here, but this is me thinking through your issue given my experience with MySQL w/innodb and other MVCC db's.

If you are seeing processes or threads waiting on eachother it has to be a locking issue. That's the only explanation. Given MySQL's structure, it has to be a thread locking issue. There are no other options.

This leads to a few specific questions:

How is performance writing the log files? Any issues there?
I there something else going on? Any deadlocks being reported?
What does top show regarding CPU time? Sleeping, idle, or wait?
What sort of write concurrency are we talking about? How many concurrent writers?

Mysql – InnoDB temporary table location

Symlinking InnoDB is definitely not a good idea for regular maintenance of InnoDB.

I wrote posts on why not to do this:

Aug 07, 2011 : MySQL InnoDB data file directly on a disk slice (fixed-size)?
Mar 12, 2012 : How to set a specific directory location for individual MySQL database?

However, if this strictly for the purpose of shrinking a large table, I guess you can symlink the whole data directory where the temp table is to go, but you will have to personally script the shrinkage.

For example

Suppose the table to shrink is mydata.mytable
datsdir is /var/lib/mysql

Your procedure would be

STEP 01: You should create a database called mydatatemp. Do not put any data in it just yet.

STEP 02: Move the /var/lib/mysql/mydatatemp over to another disk.

STEP 03: Create a Symlink /var/lib/mysql/mydatatemp over to the folder on the other disk

STEP 04: chown -R mysql:mysql against the external folder and against the symlink.

STEP 05: Now perform the data shrinkage as follows:

CREATE TABLE mydatatemp.mytable LIKE mydata.mytable;
INSERT INTO mydatatemp.mytable SELECT * FROM mydata.mytable;
DROP TABLE mydata.mytable;
ALTER TABLE mydatatemp.mytable RENAME mydata.mytable;

To recap, do the following (assuming /tmpdata is the external disk)

STEPS 01-04

chown mysql:mysql /tmpdata
ln -s /tmpdata /var/lib/mysql/mydatatemp
chown -R mysql:mysql /var/lib/mysql/mydatatemp

then run STEP 05

CAVEAT

This would be rather high risk, but it should work. Please test this with an empty table first like this:

USE mydatatemp
CREATE TABLE mrnorm (id int not null, name varchar(20) primary key (id));
INSERT INTO mrnorm (name) VALUES ('one'),('two'),('three');
SELECT * FROM mrnorm;

If these SQL command worked, go to OS and do this:

cd /tmpdata
ls -l

You should see two files:

mrnorm.frm
mrnorm.ibd

Also, test if mrnorm can be moved

CREATE DATABASE helloworld;
ALTER TABLE mydatatemp.mrnorm RENAME helloworld.mrnorm;

Go to the OS and run

cd /tmpdata
ls -l

The table should be gone. Now, do this:

cd /var/lib/mysql/helloworld
ls -l

The table should be there

END OF TEST

Keep in mind that the rename of the table would essentially be controlled copy of the table from one disk to another disk with a proper adjusting of the data dictionary section of the system tablespace in ibdata1. This could take some time.

Best Answer

Related Solutions

Mysql – “freeing items” taking forever on inserts, updates and deletes

Mysql – InnoDB temporary table location

CAVEAT

Related Question