ServerAdminBlog

One of the biggest mistakes you can make with MySQL is to leave its default configuration. It will work, but not as it should.

For example, default MySQL installation (5.0x) will keep all InnoDB data in one file – ibdata1. This file is usually located in /var/lib/mysql (at least on RH and Debian based distros) and after few months this file can became very big (in my case it was 20GB). This file has a initial size of 10Mb and it automatically extends but it can’t be reduced with DELETE, TRUNCATE or DROP. The file could reach the maximum size allowed by the filesystem if no limit is set in the my.cnf file (Debian/Ubuntu -/etc/mysql/my.cnf or RH based distros /etc/my.cnf). The best idea is to force MySQL to create an ibd file for each InnoDB table (add innodb_file_per_table in my.cnf and restart mysql). Unf. this won’t affect old tables which are already created and which are in use.

You can “FIX” this on several ways but no matter what option do you choose, BACKUP YOUR WHOLE MYSQL DATA DIRECTORY and stop services connected to your MySQL server (httpd, radius, postfix, dovecot, etc).

Keep in mind that converting InnoDB table to MyISAM will kill foreign keys so do not do it unless you know how to recreate your foreign keys.

The best option is to dump all your databases in one sql file.

Step 1

# /usr/bin/mysqldump ––extended-INSERT ––all-DATABASES ––add-drop-DATABASE ––disable-KEYS ––flush-privileges ––quick ––routines ––triggers > backup.sql

Step 2

Stop mysql server with

# service mysqld stop

or

# /etc/init.d/mysqld stop

Step 3

Backup complete mysql data dir (/var/lib/mysql).

# cd /var/lib/
# mv mysql mysql_backup
# mkdir mysql
# chown mysql:mysql mysql

Then you should have something like

drwxr-xr-x  5 mysql     mysql     4096 2010-12-30 13:38 mysql

Step 4

Add innodb_file_per_table option in /etc/my.cnf file and save file

Step 5

Re-initialize the database with the following commands

# su mysql
$ mysql_install_db
$ exit

Step 6

Start mysql server with service mysqld start and get into mysql console with

# mysql -u root

Then exec next commands

SET FOREIGN_KEY_CHECKS=0;
SOURCE backup.sql;
SET FOREIGN_KEY_CHECKS=1;

Step 7

Restart mysql server with service mysqld restart

That should be all.

In case that something goes wrong you still have mysql_backup dir which contains all you databases and files. Simple rename the new mysql dir to mysql_new and mysql_backup to mysql. Then restart mysql.

Keep in mind that this operation will kill all services who depends on mysql. So, be quick 🙂

Also, good idea is to execute command mysql_secure_installation which will “tight” your MySQL server.