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

Storage requirements detail

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

Resources

Opinionated SQL formatter