My source database tables had over 600,000 records each hosted by MySQL 5.6x. The import servers included my own Centos 7, Ubuntu 14.4, and Ubuntu 18.4; and an online shared host. The Centos 7 runs MariaDB 5.6, and the online host runs MariaDB 10.1. This is what I encountered and methods to solve:
- If your export and import servers are MySQL, the versions must be similar (such as, 5.6x); otherwise the importing server will complain about the different version (such as 5.6 -> 5.7). MySQL 5.6x is available only for Ubuntu 14.4; 18.4 binaries are maintained for only for 5.7 and higher. No problem with import servers running MariaDB 5 & 10, which is supposed to be compatible with all versions of MySQL.
- Although you can export all tables at once into a single export file, I suggest that you export each table separately to better control the size of the exported file for reasons described below.
- MySQL export would die from a timeout if more than about 220k rows were exported at a time. I exported 200k rows at a time using the "Custom" options and specifying the starting row and # of rows, SQL format. For the first chunk I selected the "Drop table" command so that a new table would be created with the first chunk (but do not select for the other chunks!).
- MySQL export defaults to 50,000 queries (essentially attaching a massive number of rows to a single "Insert" statement), but this number may be too much for the import server to handle at once. In the export Custom options choose a query number of 1000.
- Successfully created export files might still be too large for the input server to handle. To the rescue: https://www.ozerov.de/bigdump/, a php script that gracefully regulates the flood of inserts into the new database. This really works well, and I suggest that you give Alexsey a few dollars for saving you from a gigantic headache.
