Migrating Large MySQL Database to New Server

Question

I have a large Windows MyMSQL 5.5 database that I need to migrate to a new server. While I have some knowledge of MySQL it's not extensive so could use some advise on the best way to migrate the data.

The C:\ProgramData\MySQL\MySQL Server 5.5\data directory is about 50GB split across a couple databases, but there’s only a couple GB free on the existing server. Most of the space is from the xxxx.bin.##### files, which I think are the binlog files?

Couple questions, but open to other better ideas:

• can I install MySQL on the new server and just copy the entire C:\ProgramData\MySQL\MySQL Server 5.5\data directory to the new server and have it work? If so, do I need to copy the binlog files also or just the database directories?

• looking into whether I can add disk space to the existing server. However, with the databases being big, I haven’t had success with the mysqldump approach. I can dump the data, but when I try to load it back in it errors out after a while, maybe because the file is too big? Are there other export/import options?

Appreciate the help.

Answer 1

You can copy the datadir to a new server, but not while the MySQL Server is running on either server. You need to shut down the MySQL Server process, or you will copy files while they are being modified. Since file copying is not an atomic action, there's a high risk that the resulting copy is corrupted and unusable if you try to copy it while it's being used by a running MySQL Server.

You may also be able to free up disk space by purging binary log files. Your description sounds like they have accumulated and are occupying a lot of space.

Typically we configure binary logs to expire automatically after 1-3 days. Read the manual about binlog_expire_logs_seconds.

You can also purge binary logs manually. Read the manual about the PURGE BINARY LOGS statement.

Pardon me for linking to manual pages for MySQL 8.0. You said you're using MySQL 5.5, but this is an old version and its manual is no longer online. I recall the binlog_expire_logs_seconds variable was called expire_logs_days in the old version of MySQL.

• StockTsi: Thanks for the info - I’ll give it a try. I’ve always been leery of changing the bin log expiry term for fear of corrupting something. If I canopy the data, do I need to copy the entire datadir, or just the individual directories by database?
• Bill Karwin: You do need the entire datadir, because ibdata1 and some other files contain some crucial metadata.