How to migrate database to another server no downtime?

Question

I want to migrate my database server to new server because it was old.

I have thinks about setup replicate between two server.

But I can't off my database server to dump database and setup replicate.

My database too big to dump (about 60G), it will decrease preformance my server and my system.

I had find another way. It is copy files in datadir of mysql.

But I don't know if this can work for databases with both InnoDB and MyISAM.

For MyISAM, I found that I only need to copy .frm, .MYD and MYI files and I have to use mysql dump to be able to convert InnoDB tables.

But I don't know if when re-importing data on the new database, MyISAM tables will be affected by InnoDB's Binlog or not, and is there a way to only dump InnoDB tables because they are too many (about 200 tables).

Answer 1

Plan ahead. But the setup requires downtime.

Have some form of replication already in place. This could be either Primary-Replica pair of a cluster (Galera or InnoDB Cluster). With any of those, one of the servers can be replaced with nearly-zero downtime regardless of dataset size.

LVM is a way to get a dump "instantly". But, since you are moving to a new server, the network latency is still a problem.

You really need to get away from MyISAM. It is going away.

If there are dependencies between tables (whether MyISAM or InnoDB), you probably need to stop all writes during the data transfer -- else there will be inconsistencies.

The replication techniques allow for minimal downtime. But replication cannot be initially setup without downtime.

Answer 2

At my last job, we migrated databases from server to server every day, with no downtime.

Well, there was only brief downtime when we switch traffic over to the new server, but it was less than 1 second.

The solution used replication as you know. We had to create a backup of the source instance quickly. For this, we used Percona XtraBackup, which is a free physical backup tool, so there is no dump & import delay.

It's not safe to move files around yourself as a means of physical backup. You're likely to corrupt your database that way.

You asked about the difference between MyISAM and InnoDB tables with respect to the binary log. There is no difference. Both table types are logged in the same binary log, and the sequence of the binary log is important. If you copy the MyISAM tables physically, and then dump InnoDB as a separate step, you will miss any updates to the MyISAM tables.

Actually the truth is that the binary log is independent of storage engine. It records logical data changes, so in theory the same table could be in MyISAM in one instance and InnoDB in the replica instance, or vice versa.

Percona XtraBackup allowed us to make a physical backup without any downtime. It does its work without blocking continuous use of the database.

Then transfer the backup to the new server, use it as a new datadir, and configure replication. A step by step guide for this process is in the Percona XtraBackup manual.

Percona XtraBackup can back up MyISAM tables, but it must take a global lock while it's doing that. So your MyISAM tables should be very small, or even better if you can convert them to InnoDB. Then Percona XtraBackup can perform the backup entirely online, with no global lock. Note that in MySQL 5.x, some of the system tables in the mysql schema must still be MyISAM, but that's okay because they are small.

If we then want to switch traffic from the source to the replica, we wait until replication has caught up so the two instances are in sync. Then set both instances to read-only mode, and wait for the final updates to be replicated (this should only take a moment). Then make all client applications connect to the replica as their preferred database server (you could use a DNS update, for instance). Finally, disable read-only mode on the replica, and reset its replica configuration.

P.S. I Agree with Rick James that MyISAM should be avoided. InnoDB has better performance, and more features. MyISAM is getting no new development, and it has lots of disadvantages, for example it doesn't support any of the ACID properties.