MySQL Reference
Field types
Integer Types (Exact Value)
Type Bytes Min Max Max (unsigned)
-----------------------------------------------------------------------------
TINYINT 1 -128 127 255
SMALLINT 2 -32768 32767 65535
MEDIUMINT 3 -8388608 8388607 16777215
INT 4 -2147483648 2147483647 4294967295
BIGINT 8 -9223372036854775808 9223372036854775807 18446744073709551615
String Types
Type Max length
-------------------------------------------------
TINYTEXT 255 (2^8−1) bytes
TEXT 65,535 (2^16−1) bytes = 64 KiB
MEDIUMTEXT 16,777,215 (2^24−1) bytes = 16 MiB
LONGTEXT 4,294,967,295 (2^32−1) bytes = 4 GiB
Queries
Check Table Disk Usage
SELECT table_schema AS "Database",
table_name AS "Table",
round(data_length / 1024 / 1024, 2) AS "Data MB",
round(index_length / 1024 / 1024, 2) AS "Index MB",
round((data_length + index_length) / 1024 / 1024, 2) AS "Total MB"
FROM information_schema.tables
WHERE table_schema NOT IN ('mysql', 'information_schema', 'sys','performance_schema')
ORDER BY (data_length + index_length) DESC;
Moving MySQL data directory
Adapted from https://www.digitalocean.com/community/tutorials/how-to-move-a-mysql-data-directory-to-a-new-location-on-ubuntu-16-04
Step 1 — Moving the MySQL Data Directory
$ sudo systemctl stop mysql
$ sudo rsync -av /var/lib/mysql /mnt/volume-nyc1-01
$ sudo mv /var/lib/mysql /var/lib/mysql.bak
(or symlink to new directory instead of moving datadir itself)
Step 2 — Pointing to the New Data Location
$ sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf
. . .
datadir=/mnt/volume-nyc1-01/mysql
. . .
Step 3 — Configuring AppArmor Access Control Rules
$ sudo vim /etc/apparmor.d/tunables/alias
. . .
alias /var/lib/mysql/ -> /mnt/volume-nyc1-01/mysql/,
. . .
$ sudo systemctl restart apparmor
Step 4 — Restarting MySQL
$ sudo systemctl start mysql