Manually copy a MySQL 5.5 database to another computer

Question

My company uses a product that uses MySQL 5.5 as the backend database. The product automatically installs and configures MySQL during installation. The product can be configured to operate in a hot standby redundant configuration. In these cases, perform the same installation process on 2 separate servers and then select redundant mode during the initial configuration of the product. The product handles all the process of copying database data internally and keeping the two databases in sync. MySQL knows about redundancy settings. MySQL is installed identically on both servers, in the same location, and has the same structure. The product does not have a very elegant/efficient way to synchronize large databases (e.g. 300G sized 3K tables) from the primary server to the backup server when needed, e.g. when creating a redundant system from a single server/that has been running for some time Master server configuration. My question is as follows.

Considering that the MySQL installation is identical on both servers, is there a safe/supported way to manually copy the database/files from the primary server to the backup server? BTW, this is on a production Windows server. I know I could completely export the database from the main database and import it onto the BU server, but that might take a few hours. I wish there was a faster supported method of copying files from one server to another, but while researching this I'm seeing conflicting information.

System information Windows MySQL 5.5 The installation is the same on both servers "C:\ProgramData\MySQL\MySQL Server 5.5\data" Innovation database files per table = true

Thanks in advance for any suggestions.

I once tried to just copy the Database Folder that contains all the innovative database table files, "C:\ProgramData\MySQL\MySQL Server 5.5\data\Mydbase", from one server to another but mysql would not start up and had errors.

Answer 1

Yes: Shut down the MySQL Server services on both computers. You can then move the files in datadir any way you want. But this will cause some downtime while you're doing the file transfer.

If there must be no downtime, it is possible, but requires more steps.

What I did was use Percona XtraBackup to take a physical backup of the source instance, but this is not easy for you since you are using Windows. XtraBackup is not available for Windows. Some people use tricksDocker containers on Windows.

Then restore XtraBackup to your new machine in the normal way and configure it as a copy of the source instance. See https://docs.percona.com/percona-xtrabackup/8.0 /howtos/setting_up_replication.html

By making the new instance a replica, you can have it update based on the latest changes that occurred on the source instance when the replica was set up.

Then at some point you decide to switch to a new instance. Then, set the source instance to read-only mode to prevent client applications from making any new changes. Let the replica catch up with the last final changes (this only takes a second if the replica has already caught up with the changes). You can now change the client application to use the copy instead of the previous source. Then use RESET SLAVE to unconfigure replication on the new instance, as the last thing you want to happen is any more changes happening on the previous source and replicating to the new instance.

If you try this procedure, I recommend testing on a test instance (rather than a production instance) until you are familiar with the tools.

P.S.: Besides not supporting Windows, I don't know if the current version of XtraBackup will work with MySQL 5.5. This version was released in 2010 and discontinued in 2018. Therefore, I think you need to research which version of XtraBackup can still read MySQL 5.5 instances. You may have to use an older version of XtraBackup.