Sep 13, 2018

Practical configurations for MySQL in production

There are sample my.cnf files. These files include my-huge.cnf, my-large.cnf, my-medium.cnf, my-small.cnf and my-innodb-heavy-4G.cnf. You probably would choose my-huge.cnf or my-innodb-heavy-4G.cnf in production. Copy them to /etc/my.cnf (/usr/my.cnf for CentOS)

That ibdata1 isn't shrinking is a particularly annoying feature of MySQL.
To setup your server to use separate files for each table you need to change my.cnf in order to enable this:
[mysqld]
innodb_file_per_table
Now you're fit to go. When you create a new database for analysis, the tables will be located in separate ibd* files, not in ibdata1. As you usually drop the database soon after, the ibd* files will be deleted.
If you didn't separate those files, and you want to reclaim the space from ibdata1 you actually have to delete the file:
  1. Do a mysqldump of all databases, procedures, triggers etc 
  2. Drop all databases except the mysql database 
  3. Stop mysql 
  4. Delete ibdata1 and ib_log files 
  5. Start mysql 
  6. Restore from dump
##########################
/usr/bin/mysql_secure_installation