-1

I'm planning on moving all my VMs to shared storage, and I have a question about how to deal with a MySQL VM:

Currently the size of the VM image is 100GB, where most of the space is taken up by the MySQL data. I feel there are two options when moving to shared storage but I'm not sure which is the most common/recommended practice:

  1. Simply put the 100GB VM on shared storage
  2. Move the 95GB data directory to shared storage, Resize VM to 5GB and move to shared storage, then point the VM to the data directory

Does anyone have a view on this?

Data and index sizes as follows:

+----------------+----------------------+----------------------+----------------------+
| Storage Engine | Data Size            | Index Size           | Table Size           |
+----------------+----------------------+----------------------+----------------------+
| MEMORY         |             0.002 GB |             0.001 GB |             0.003 GB |
| FEDERATED      |             2.198 GB |             0.000 GB |             2.198 GB |
| MyISAM         |             2.258 GB |             0.337 GB |             2.595 GB |
| InnoDB         |            47.249 GB |            30.568 GB |            77.817 GB |
| Total          |            51.706 GB |            30.906 GB |            82.613 GB |
+----------------+----------------------+----------------------+----------------------+
James
  • 325
  • 2
  • 11
  • 22
  • Please run the query from my post : http://dba.stackexchange.com/questions/8661/mysql-workbench-database-sizes/8662#8662. That way I can see how much actual MySQL data your have. – RolandoMySQLDBA Dec 13 '14 at 01:09
  • Thanks for you input @RolandoMySQLDBA I've now edited the question to contain the data and index size by storage engine. Can I ask why the data/index sizes make a difference to the storage location? – James Dec 13 '14 at 12:57

1 Answers1

1

The common/recommended practive should work just fine. Just make sure you run this

chown -R mysql:mysql /var/lib/mysql

ALTERNATIVE

Based on the query output and the data directory size, you have 13GB of wasted space due to some fragmentation. Now is your chance to eliminate that fragmentation.

mysqldump your data and gzip it

MYSQL_USER=root
MYSQL_PASS=rootpassword
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
MYSQLDUMP_OPTIONS="--routines --triggers --single-transaction --all-databases"
mysqldump ${MYSQL_CONN} ${MYSQLDUMP_OPTIONS} | gzip > MySQLData.sql.gz

Setup MySQL Instance with datadir in the share storage

Load that backup into the MySQL Instance

gzip -d < MySQLData.sql.gz | mysql ${MYSQL_CONN}

That way the data directory should be 82GB when reloaded instaed of 95GB

RolandoMySQLDBA
  • 16,544
  • 3
  • 48
  • 84
  • I'm unclear from your answer what you refer to when you suggest the common approach, and whether you recommend the 100GB VM or the 5GB VM with 82GB data directory? In either case what are the advantages and disadvantages? – James Dec 13 '14 at 22:12