Find unused MySQL tables

MyISAM

If using MyISAM you can use the following query to list all tables which has not been modified in the last 10 days (or not modified at all):

SELECT table_schema, table_name, create_time, update_time 
FROM information_schema.tables 
WHERE table_schema NOT IN ('information_schema', 'mysql', 'sys', 'performance_schema') 
AND engine IS NOT NULL 
AND ((update_time < (now() - INTERVAL 10 DAY)) OR update_time IS NULL);

InnoDB

This is not supported in InnoDB so we have to check the actual database files on the server when they were last modified. The DB files are located in /var/lib/mysql/<dbname> , but you will have to enter root prompt in order to access this directory.

Run this command to see when the DB tables were last modified:

ls -l --time-style="+%Y-%m-%d" | awk '{print $6,$7}' | grep ibd

Let’s break down the command a bit. We simply use the regular ls to list all files in the directory, then we change the date format to YYYY-MM-DD, then we print only filename+date using awk, then finally we grep (filter) by ibd (because all tables have two files, one .ibd file and one .frm file. – However, only the .ibd file gives the correct modified date).

Example output:

MySQL storage types and their limits

Below are some of the most used data types and its storage requirements:

Data type Storage required
TINYINT 1 byte
SMALLINT 2 bytes
MEDIUMINT 3 bytes
INT 4 bytes
 BIGINT 8 bytes
FLOAT 4 bytes
DOUBLE, REAL 8 bytes
VARCHAR(x) x+1 byte if value is less than 255 bytes and x+2 bytes if over 255 bytes.
TINYTEXT 255 bytes
TEXT 65 535 bytes
MEDIUMTEXT 16 777 215 bytes
LONGTEXT 4 294 967 295 bytes

Notes:

  • Characters can take up different amount of space depending on character encoding. Regular English ASCII characters only take 1 byte per character, meaning that a TEXT column can fit 65 535 characters (a-Z, 0-9 and some special signs)
  • VARCHAR(180) will require 181 bytes of space, regardless if it’s empty or not.

Date formats:

Data type < MySQL 5.6.4 > MySQL 5.6.4
YEAR 1 byte 1 byte
DATE 3 bytes 3 bytes
TIME 3 bytes 3 bytes + fractional seconds storage
DATETIME 8 bytes 5 bytes + fractional seconds storage
TIMESTAMP 4 bytes 4 bytes + fractional seconds storage

Sources:

Add remote access to MySQL server

Follow the commands below to setup a new user and open up for remote access to a specific database on your MySQL server.

$ mysql -u root -p
Enter your MySQL root password.
mysql> CREATE USER 'itdb_admin';
mysql> CREATE DATABASE itdb_db;
mysql> GRANT ALL PRIVILEGES ON itdb_db.* to 'itdb_admin'@'%' IDENTIFIED BY 'my-password' WITH GRANT OPTION;
mysql> FLUSH PRIVILEGES;
mysql> EXIT;
$ sudo nano /etc/mysql/my.cnf
Comment the following line by setting a # in front (to disable it):
bind-address = 127.0.0.1
$ sudo service mysql restart

Quick summary:

1) Open the MySQL CLI
2) Create a new database
3) Create a new user
4) Give the user full access to the database. Notice the '%' which means we’re talking about remote access. The same user can have different access levels based on the connection (whether it’s remote or local)
5) Flush/refresh the privileges so they become active
6) Disable bind-address so the MySQL server will listen on any source address
7) Restart the MySQL service to reload the config file

Backup MySQL data using mysqldump

Backup complete database:

mysqldump -u <username> -p<password> <dbname> > dbname.sql

Backup only specific tables:

mysqldump -u <username> -p<password> <dbname> <tablename> > tablename.sql

Restore a .sql file:

mysql -u <username> -p<password> <dbname> < file.sql

Note: There can’t be any empty spaces between -p and the password parameter.

Examples:

mysqldump -u root -p123456 itdb > itdb_280912.sql
mysqldump -u root -p123456 itdb itdb_posts > itdb_posts_280912.sql
mysql -u root -p123456 itdb < itdb_backup.sql

Setup automated database backup with mysqldump and cron jobs for Linux

Start cron manager by running command $ crontab -e and type the following line at the bottom:

0 4 * * * sudo mysqldump -u root -p<password> <dbname> | gzip > /mnt/nas/<dbname>_bak_`data ' %Y-%m-%d'`.sql.gz

This will create a job entry in cron which starts every night at 0400 AM. What it does is running mysqldump to backup the specified <dbname> and also compresses it to a gzip file and saves it to /mnt/nas/ (replace with wherever you want to store the file).