MySQL Database Instance size

database-sizeMySQL

We found 3 different size of a MySQL Database Instance as follows:

Mysqldump output
Data directory size (even if we exclude size of bin-logs,error-logs)
SUM (data_length + index_length ) calculated from information_schema.tables

Why we faced these differences and what could be used as my Instance size?

Please help.

Best Answer

Dump output is raw SQL statements, numbers and everything else is represented as text so the size is in no way relevant (one int is 4 bytes, but as text 1 is probably 1 byte and maximal int value (signed) = 2147483647 is 10 bytes and minimal int value = -2147483648 is 11 bytes).
I suppose those are all ibdataX, *.ibd, *.myi, *.myd, *.frm. Both MyISAM and InnoDB tables may contain some empty space (for example after deleting some rows) which will not be automatically reclaimed by the filesystem, InnoDB files contain not only actual row data but old versions of records, redo logs and other information needed for transactions.
The free space mentioned before is not accounted for here, the same applies for redo logs probably.

In the end it depends on your needs for the size - do you need to know how much space to allocate for backups? #1 or #2 may be what you need, depending on your backup strategy and after accounting for compression. Do you need to know how big disks/cloud storage to pay for - #2 is probably the best bet.

I personally do not see much use for #3 in general, but you can use it to get some average storage needed for a record so you can project future grow of specific tables.

I am sure I missed some differences but hopefully nothing big.

Related Solutions

MySQL gives me:“Can’t open and lock privilege tables: Table ‘host’ is read only”

custom mysql datadir? removed apparmor and still having mysql process failing to start issues? try this. may help some of you depending on your config. worked for me:

when using a custom mysql (5.5) data dir as described above- ie., if the abs. path to datadir (my.cnf) is /opt/some/other/mysql

even with apparmor removed/purged, you must make certain that permissions on ALL of the folders in the mysql datadir path are perm'd correctly. this is a mysql convention- not related to apparmor.

look at /var/lib/mysql- the default datadir (my.cnf) on most installations: perms are 755 for /var and /var/lib.

in my custom datadir example above, when I set any/all of the dirs called 'opt' or 'some' or 'other' without an execute permission for allusers (ie., 750), mysql will not start. once i add an execute perm to allusers (ie. 755, 771, etc.), BINGO. mysql starts.

Mysql – Connecting to MYSQL on an EC2 instance

Since Zapier is coming in from outside your instance, a possible cause of your problem is the security group for your instance. Most ports on EC2 instances (except the SSH port) are normally blocked by default unless you explicitly open them.

So you would need to open the mysql port 3306 on your instance up to the ip address Zapier is coming in from. (preferably you would not open it up to everyone in the world.)

See Using Amazon RDS/EC2 on the Zapier site, starting from the section where it says Can't Connect to My Database. They continue on to provide specific instructions for the security group settings.

You will also probably need to grant permission on the database using the mysql GRANT command to the user coming in from the Zapier ip address.

Something like this should work :

GRANT ALL PRIVILEGES
ON mydb.*
TO 'user'@'54.86.9.50'
IDENTIFIED BY 'newpassword';

Or you can allow that user to come in from any ip adress like this:

GRANT ALL PRIVILEGES
ON mydb.*
TO 'user'@'%'
IDENTIFIED BY 'newpassword';

(Incoming requests would still be limited to Zapier ip addresses due to the security group settings.)

Best Answer

Related Solutions

MySQL gives me:“Can’t open and lock privilege tables: Table ‘host’ is read only”

Mysql – Connecting to MYSQL on an EC2 instance

Related Question