I have 2 VPS and plan to migrate the DB from first to second. The original DB is taking around 15GB of storage and I manage to dump out a 7GB SQL. However, when I imported the SQL to second VPS, it takes up 2x of storage becoming 32GB.
My questions are:
- What might cause my DB from 15GB to become 32GB by just dump+import?
- How can I avoid this?
Below are some further detail:
- Both VPS are not exactly same and so do the MySQL version. The source DB is version 5.5.47. The receiving DB is version 5.7.13.
- The receiving DB is newly install and empty.
- The command I used to dump out SQL is:
mysqldump -u <username> -p --all-databases --add-drop-database --max_allowed_packet=16777216 --single-transaction > full_dump.sql. The reason I am setting max_allowed_packet is to have the same value compare to receiving DB
- Not sure if this is relevant. If I do not specify
single-transactionin my mysqldump command, it will error
mysqldump: Got error: 2006: MySQL server has gone away when using LOCK TABLES
- The source DB storage distribution is different from the receiving DB:
- This is the receiving DB storage distribution:
- I've tried to run table optimisation, but no impact
PARTITIONed? Many columns
CHARas opposed to
SHOW CREATE TABLE wp_wfLeechers;before and after? Also
SHOW TABLE STATUS LIKE 'wp_wfLeechers';