Enable and convert MySQL to innodb file per table for Zoneminder

Enable and convert MySQL to innodb_file_per_table for Zoneminder

Note: You may wish to convert MyISAM tables to InnoDB tables before you proceed. Upgrading Zoneminder to 1.26 or newer should do this for you.

innodb_file_per_table is by default ON Mysql 5.6.6 and onwards. There is plenty of stuff on Google about pros & cons of innodb_file_per_table. This post details how to enable innodb_file_per_table on an existing database. Because innodb_file_per_table affects new tables only, created after innodb_file_per_table is enabled, we need to recreate old databases to force innodb_file_per_table on old tables and reclaim some disk space. This has been tested on Ubuntu 14.04-1 running Zoneminder 1.26.5 and 1/28.0. I converted a database with over 30,000 Zoneminder events and 900 GIG of event files. I was able to reclaim over 500M of drive space!

If you have a new system and have not yet installed Zoneminder, run this procedure then install Zoneminder.

Become root

sudo su

Backup First

Create a dir to take backups:

cd ~

Note: I found it helpful to create a file which contained the MySQL user and password. Otherwise you will have to enter the user and password for every operation.

nano .my.cnf

Enter this content

[client]
user=root
password=mysqlpass

Ctrl+o Enter to save

CTRL+x to exit

Make backup directory

mkdir backup

cd backup

Copy MySQL data files (raw) (If all goes well, we will not need this)

Stop Zoneminder

service zoneminder stop

If you have other services that use MySQL you will want to stop them and possibly Apache.

service mysql stop && cp -ra /var/lib/mysql mysqldata && service mysql start

Take mysqldump As soon as above line completes, take a mysqldump of all databases

mysqldump --routines --events --flush-privileges --all-databases > all-db.sql

Drop Databases Create a sql file to drop all databases EXCEPT mysql database

mysql -e "SELECT DISTINCT CONCAT ('DROP DATABASE ',TABLE_SCHEMA,' ;') FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA <> 'mysql' AND TABLE_SCHEMA <> 'information_schema';" | tail -n+2 > drop.sql

Verify if drop.sql has correct database names and then execute drop.sql queries.

mysql < drop.sql

Verify all InnoDB tables gone

SELECT table_name, table_schema, engine FROM information_schema.tables WHERE engine = 'InnoDB';

Remove InnoDB files Stop mysql server first

service mysql stop

Then

rm /var/lib/mysql/ibdata1 && rm /var/lib/mysql/ib_logfile0 && rm /var/lib/mysql/ib_logfile1

At this point most likely you will have only /var/lib/mysql/mysql directory only.

Enable innodb_file_per_table

Open my.cnf file

nano /etc/mysql/my.cnf

Add following line after [mysqld]

innodb_file_per_table

Ctrl+o Enter to save

CTRL+x to exit

Time to import from mysqldump Start mysql server now

service mysql start

Run mysql import

mysql < all-db.sql

Force mysql_upgrade (to generate performance_schema)

mysql_upgrade --force

That’s All!

Restart Zoneminder (and any other services you have stopped)

service zoneminder start

Check for proper operation and that all your events are present.

When you are satisfied that all is worling well remove the backup directory and password filr=e

cd ~

rm -r backup

rm .my.cnf

You are finished!

This procedure has been adopted from https://rtcamp.com/tutorials/mysql/enable-innodb-file-per-table. Thanks to Rahul Bansal!