MySQL

About

MySQL (or MariaDB) contains a db named zm after ZoneMinder is installed..

Setup

Follow installation guides in Documentation

Backup

You can backup the database, without much resource strain. Note that this backs up ONLY the configuration and events metadata. It does not backup any videos or images. See this link on the forums

Optimization

MySQLTuner

mysqltuner

Then read the output, and perform any recommended database tweaks.

Other

mysqlcheck -u root -p --optimize --databases zm

This will attempt to optimize your databases. Functions are limited with InnoDB format, however.

Troubleshooting

API Can't Connect

If you change the password from the default, the API CakePHP config files will need to have their password changed as well.

IBData files Large

In ZoneMinder 1.28, I had an issue with the ibdata1 file in /var/lib/mysql/ growing too large. It includes some database information and in my 10GB root partition, was taking up 8GB. This was because the DB was not in InnoDB format. Newer Zoneminder, I think, default to InnoDB.

The solutions I found were:

• backup zm database, delete zm db, delete ibdata file, then restore database How to Shrink/Purge Ibdata1 file

OR

• Move the ibdata file to another partition

OR

• Change DB type to InnoDB (requires backup, deletion, and restoring db, per first solution)

Changing the database type to have an innodb file per each table as mentioned in the "how to shrink purge ibdata1 file in mysql" link will keep less data used in the ibdata1 file in the future, allowing the former to be deleted when not needed. On the other hand the ibdata file by default, will not shrink, ever. This may not be an issue in MariaDB.

Looking for the least invasive procedure, I went with moving /var/lib/mysql, and adding the optional my.cnf parameter. This required the following tricks (may only apply to Ubuntu 14.04).

There are a number of guides on moving Mysql, yet many of them omit adding the alias to apparmors settings. This is required. Failing to do so will result in "Job failed to start" when mysql is run with #service mysql start.

A guide that covers all the steps required to move mysql on Ubuntu Trusty without omitting anything is here: Ask Ubuntu: Moving Mysql datadir Note that within my mysql installation there was no socket file in /var/lib or in my.cnf.

Backup/Restore Mysql DB

After moving the Data directory, I ended up backing up the zm db and restoring it anyways, in order to get the ibdata files to split correctly. This is not hard to do. The only DB you need to mysqldump from a stock ZM installation is the ZM db. And it's also the only DB you need restore.

For a full walkthrough on converting a MyISAM DB to InnoDB (also covers backing up ZM DB) see Enable and convert MySQL to innodb file per table for Zoneminder.

MySQL server has gone away error with ZMTrigger

See ZMTrigger#MySQL_server_has_gone_away_error

See Also

I haven't checked these links thoroughly, so use caution

• rjweb.org mysql docs