Difference between revisions of "MySQL"

From ZoneMinder Wiki
Jump to navigationJump to search
 
(56 intermediate revisions by the same user not shown)
Line 1: Line 1:
==About==
==Setup==


MySQL (or MariaDB) creates a db named zm after ZoneMinder is installed.
MySQL (or MariaDB) creates a db named zm after ZoneMinder is installed.
Line 10: Line 10:
</pre>
</pre>


==Setup==
There are some .sql setup scripts. And typically, permissions are granted for zmuser to access the zm database. This may or may not be  done manually depending on what install guide you follow. It is recommended to use the guides here: [[Debian]] or [[Ubuntu]]. Sometimes things break, and you will need to rebuild the database, so for reference the steps are kept below (example from Debian):
 
Follow installation guides in [[Documentation]]
 
In older installs, you needed to setup the db, and user after installing ZM. In more recent installs (1.34), this is handled by the dpkg scripts (for Debian), but it will be included here for reference:


  mysql -u root -p < /usr/share/zoneminder/db/zm_create.sql
  mysql -u root -p < /usr/share/zoneminder/db/zm_create.sql
Line 22: Line 18:
==Backup==
==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.  
There are two options: 1) Backup the full database. 2) Backup only the configuration.
See this [https://forums.zoneminder.com/viewtopic.php?f=32&t=23815 link on the forums]
For 1), It does not backup any videos or images. So you will have a copy of all metadata referring to events, yet the video files on the filesystem will not be saved. If it's necessary to have an offsite backup, you may want to consider a VPS as storage. Amazon S3FS support is built into Zoneminder and that is one option. Though it can be as simple as rsync-ing videos to a network share. See [https://forums.zoneminder.com/viewtopic.php?f=32&t=23815 link on the forums]  
 
For 2), the config saved will allow you to restore all the camera configuration, zones, filters, runstates, etc... It will not, however save any event data, nor will it save any videos. If you have a hardware failure, you will lose all camera footage, and event data. For less critical installations, this is acceptable. Since most events are exported shortly after they happen, most users will want to use option 2.


/usr/bin/zmaudit.pl will delete any database entries that don't have corresponding images on the filesystem.
/usr/bin/zmaudit.pl is usually recommended to be run to cleanup some broken database to filesystem references.  
This means simply, that backing up a DB will backup your configuration, but not anything else, and when zmaudit.pl runs it will clean the DB of orphaned events.


'''Backup:'''
===Full Backup===
  mysqldump -u root -p zm > zmdb.sql
This may take a while. It is recommended to stop the Zoneminder service while this backup is running.
'''Backup config only:'''
mysqldump -u root  zm > zmdb.sql
'''Restore'''
  mysql -u root zm < zmdb.sql
===Backup config only===
This will run quickly: usually less than a second. The Zoneminder service does not need to be stopped.
<pre>DATE="$(date +'%D'|sed  's/\//_/g')"
<pre>DATE="$(date +'%D'|sed  's/\//_/g')"
  mysqldump -u root -p zm --ignore-table=zm.Events --ignore-table=zm.Frames --ignore-table=zm.Logs --ignore-table=zm.Stats --ignore-table=zm.Events_Day --ignore-table=zm.Events_Hour --ignore-table=zm.Events_Month --ignore-table=zm.Events_Week > zmdb_configonly_$DATE.sql
  mysqldump -u root zm --ignore-table=zm.Events --ignore-table=zm.Frames --ignore-table=zm.Logs --ignore-table=zm.Stats --ignore-table=zm.Events_Day --ignore-table=zm.Events_Hour --ignore-table=zm.Events_Month --ignore-table=zm.Events_Week --ignore-table=zm.Event_Summaries > zmdb_configonly_$DATE.sql
</pre>
</pre>
'''Restore:'''
'''Restore from config only or Recreate db'''
  mysql -u root -p zm < zmdb.sql
 
As mentioned above, if restoring to a new machine and starting from scratch, you will need to add permissions for zmuser to mysql. If you are restoring the config without the events table, you may need to recreate the db.
mysql -u root  -e "drop database zm;"
mysql -u root  -e "create database zm;"
  mysql -u root zm < /usr/share/zoneminder/db/zm_create.sql
mysql -u root  zm < zmdb.sql
mysql -u root  -e "grant all on zm.* to 'zmuser'@localhost identified by 'zmpass';"


If upgrading, make sure to zmupdate.pl -f
You may also want to run zmupdate.pl -f to make sure the database is updated properly.


If restoring to a new machine, or starting from scratch, you will need to add permissions for zm to mysql. See: https://wiki.zoneminder.com/MySQL#Recreate_ZM_DB
===Regular Backups===
  mysql -u root -p -e "drop database zm;"
You should use cron to do regular backups (where mysql_config_backup.sh is the script above, with chmod +x) . You may also want to keep copies offsite, in case of catastrophic hard drive failure.
mysql -u root -p -e "create database zm;"
in /etc/crontab
mysql -u root -p zm < zmdb.sql
  0 12 1 * * root cd /home/user/zmbackups && /home/user/mysql_config_backup.sh
mysql -u root -p -e "grant all on zm.* to 'zmuser'@localhost identified by 'zmpass';"


==Example Queries==
==Example Queries==
Here are a number of MySQL queries that may be useful for Zoneminder. Often, accessing the database directly will be faster than using the web gui.


To start, here's an example of how you might access the ZM database from a bash shell:
  sudo mysql -u root -p
  sudo mysql -u root -p
  mysql> use zm;
  mysql> use zm;
  > show tables;
  > show tables;
  > select * from Users;
  > select * from Users\G
  > update Users set MaxBandwidth = 'Low' where Username = 'user1';
  > update Users set MaxBandwidth = 'Low' where Username = 'user1';


A full list of db columns can be found in zm source under db folder. If any of these queries fail, review the field names. Things are changed in the DB from time to time.
A full list of db columns can be found in the [https://www.github.com/zoneminder/zoneminder/ source code] under the db folder. If any of these queries fail, review the field names. Things change in the db from time to time.


===Change Storage Area for Multiple Cameras===
===Change Storage Area for Multiple Cameras===
Say you have 50 cameras to set to a HDD. It would be painful to manually update each one to a new storage area...
If you have multiple cameras set to a storage area. It would be tedious to manually update each one.


Storage Area is the key StorageID in Monitors (1.32.3), therefore
Storage Area is the key StorageID in Monitors (1.32.3), therefore
Line 66: Line 75:
press q to exit this view.
press q to exit this view.
  update Monitors set StorageId = 2 where StorageId = 0;
  update Monitors set StorageId = 2 where StorageId = 0;
In practice you would probably set specific monitors, not all of them.


===Set all Cameras to Use H264 Encode===
===Set all Cameras to Use H264 Encode===
Line 71: Line 82:


  update Monitors set VideoWriter = 1 where VideoWriter = 0;
  update Monitors set VideoWriter = 1 where VideoWriter = 0;
In 1.36 you should also set the following, otherwise HEVC aka H265 will be used (H265 support in 2024 is still limited in browsers and not recommended).
update Monitors set OutputCodec = 27;
update Monitors set Encoder    = "libx264";


You may also want to disable JPEG encoding.
You may also want to disable JPEG encoding.


  update Monitors set SaveJPEGs = 0 where SaveJPEGs = 3;
  update Monitors set SaveJPEGs = 0 where SaveJPEGs = 3;
Though, you may want to use passthrough instead of encode when possible. This example is more of an example of ZM SQL administration, not a recommendation for Zoneminder settings.


===Set all cameras to limit zma to 2 FPS===
===Set all cameras to limit zma to 2 FPS===
FPS in zma can be limited to lower CPU use.
Framerate in the analysis (previously zma) can be limited to lower CPU use.
 
NOTE: double check all monitors are working after changing. It may take a minute for camera capture to restart.


'''1.30.4 or older:'''
'''1.30.4 or older:'''
Line 94: Line 110:
(1.34)
(1.34)
  UPDATE Monitors SET LabelFormat ="%N - %m-%d-%Y %H:%M:%S %z" WHERE LabelFormat ="%N - %Y-%m-%d %H:%M:%S %z";
  UPDATE Monitors SET LabelFormat ="%N - %m-%d-%Y %H:%M:%S %z" WHERE LabelFormat ="%N - %Y-%m-%d %H:%M:%S %z";
===Change All Cameras to Have mm/dd/yy and AM/PM in Timestamp===
(1.34)
UPDATE Monitors SET LabelFormat ="%N - %m/%d/%Y %l:%M:%S:%p" WHERE LabelFormat ="%N - %Y-%m-%d %H:%M:%S %z";
The %z is for offset from GMT, which most people won't need. These can be found from the man page for date.
You can review the existing Timestamps with:
select LabelFormat from Monitors\G
Zoneminder must be restarted for changes to take effect.


===Check Value of AUTH_RELAY===
===Check Value of AUTH_RELAY===
If you set Auth relay to none, then it's possible to access cameras from wan via direct monitor link. So check any WAN accessible installations.
If you set Auth relay to none, then it's possible to access cameras from wan via a direct monitor link. So check any WAN accessible installations.
   
   
  cd zoneminder
  cd zoneminder
Line 104: Line 129:
This also means that you can get direct video URLs from a secure LAN without authentication, if desired.
This also means that you can get direct video URLs from a secure LAN without authentication, if desired.
   
   
ref: https://forums.zoneminder.com/viewtopic.php?t=28144&p=117900#p117900
https://forums.zoneminder.com/viewtopic.php?t=28144&p=117900#p117900


===Add API/Mobile User with View Permissions===
===Add API/Mobile User with View Permissions===
Quick.
  mysql -u zmuser -p
  mysql -u zmuser -p
   use zm;
   use zm;
Line 118: Line 141:
</small>
</small>


 
===Delete User===
===Delete User the Quick Way===
Some administration tasks as faster when handled via direct SQL entry. e.g.
<pre>
<pre>
select * from Users where Username="Defunct User"\G
select * from Users where Username="Defunct User"\G
(confirm that it looks correct, then)
delete from Users where Username="Defunct User";
delete from Users where Username="Defunct User";
</pre>
</pre>
Line 151: Line 171:
13 rows in set (3.46 sec)
13 rows in set (3.46 sec)
</pre>
</pre>
ref:http://forums.zoneminder.com/viewtopic.php?f=40&p=119899&sid=c115f6a9443d70e8a4cb00c5e04883f8#p119899
http://forums.zoneminder.com/viewtopic.php?f=40&p=119899&sid=c115f6a9443d70e8a4cb00c5e04883f8#p119899


===Disable Logging via cli===
===Disable Logging via cli===
Here is an example where mysql is scripted from the shell.
<pre>
<pre>
#!/bin/bash
#!/bin/bash
Line 165: Line 186:
===Set all video lengths to be 1 hour===
===Set all video lengths to be 1 hour===
  UPDATE Monitors SET SectionLength = "3600" where SectionLength = "600";
  UPDATE Monitors SET SectionLength = "3600" where SectionLength = "600";
This is most useful when you are using h264 encoding and or passthrough, and additionally have a large number of monitors. In this scenario, with video lengths of 10 minutes, you will end up with more video files. But if you change the video length to 1 hour, you will have less files, which makes deletion and management of files necessary. Related to this, is that if you have JPEG frames enabled, you will also end up with lots of files. Although this change will not affect how many files that mode creates. Video encoding is more efficient in terms of files, in that case.
This can be useful for smaller setups, or where you want to limit the number of videos created. It can, however
make finding motion events more difficult, so is not ideal for all setups.


===Set AlarmMaxFrame to 3===
===Set AlarmMaxFrame to 3===
Line 171: Line 193:
  update zm.Monitors set AlarmFrameCount="3" where AlarmFrameCount="1";
  update zm.Monitors set AlarmFrameCount="3" where AlarmFrameCount="1";


The default value is 1. This might help avoid glitches causing alarms.
The default value is 1. This may avoid glitches causing alarms (camera feed errors can cause a blank screen for 1 frame).


==Optimization==
==Optimization==
Line 230: Line 252:


===Forgot Root Password for MySQL===
===Forgot Root Password for MySQL===
MySQL and MariaDB in Debian based distros can have the root password changed with dpkg-reconfigure mysql-server-#.# where #.# is your server version (type dpkg-reconfigure mysql and hit tab). This does not affect the ZM database.
dpkg-reconfigure mysql-server-#.# works in older Debian releases, but not as of Bullseye.
 
Other options:
https://dev.mysql.com/doc/refman/8.0/en/resetting-permissions.html


Option 2 is to login to mysqld_safe --skip-grant-tables and change password for root (many guides online follow this approach).
https://stackoverflow.com/questions/7534056/mysql-root-password-change


===Forgot Admin Password for Zoneminder===
===Forgot Admin Password for Zoneminder===


There are different ways to resolve this. Best advice, is probably option 3.
There are different ways to resolve this. The easiest method, is probably option 3.


* Option 1: black out password
* Option 1: black out password
Line 251: Line 276:
ref: https://forums.zoneminder.com/viewtopic.php?f=5&t=14543
ref: https://forums.zoneminder.com/viewtopic.php?f=5&t=14543


Alternatively you can add a new password to the ZM DB. Note that mysql passwords for ZM must be encrypted. You can't just query add a new plaintext password. The following should work with mariaDB, and mysql < 8.0.11 (untested). You may also need to run zmupdate.pl -f (passwords on 1.34+ are encrypted with bcrypt and this will use perl libraries to encrypt the password if it is not.
Alternatively you can add a new password to the ZM DB. Note that mysql passwords for ZM must be encrypted. You can't just query add a new plaintext password. The following should work with mariaDB, and mysql < 8.0.11 (untested). You may also need to run zmupdate.pl -f (passwords on 1.34+ are encrypted with bcrypt and this will use perl libraries to encrypt the password if it is not. To explain this clearly, what you do is enter a plaintext password, and then run zmupdate.pl which will encrypt it for you.
  '''MariaDB''' [zm]> update Users set Password=PASSWORD(NewPassword) where Username="David";
  '''MariaDB''' [zm]> update Users set Password=PASSWORD(NewPassword) where Username="David";  
 


Reference: [https://dev.mysql.com/doc/refman/8.0/en/encryption-functions.html#function_password  Mysql Reference Docs 8 : Password Function Deprecated]
Reference: [https://dev.mysql.com/doc/refman/8.0/en/encryption-functions.html#function_password  Mysql Reference Docs 8 : Password Function Deprecated]
Line 259: Line 285:


* Option 3: Turn off Auth, fix, turn auth on
* Option 3: Turn off Auth, fix, turn auth on
Another approach (forums):
From the forums.
<pre>
<pre>
Best bet is to turn off auth, use UI to change admin password, turn auth back on.
Best bet is to turn off auth, use UI to change admin password, turn auth back on.
Line 269: Line 295:
</pre>
</pre>


===Recreate ZM DB===
===Manually Update MySQL if zmupdate.pl fails===
mysql -uroot -p < /usr/share/zoneminder/db/zm_create.sql
This shouldn't be required but for reference (from: https://forums.zoneminder.com/viewtopic.php?p=131434#p131434)
mysql -uroot -p -e "grant all on zm.* to 'zmuser'@localhost identified by 'zmpass';"
<pre>
zmupdate.pl -f
mysql -u root
  use zm
  source /usr/share/zoneminder/db/zm_update-1.36.16.sql
  source /usr/share/zoneminder/db/zm_update-1.36.18.sql
  quit
</pre>
Where you would substitute the source entries for whichever updates you need.
===[ERROR] InnoDB: Missing FILE_CHECKPOINT at ########## between the checkpoint ########## and the end===
This error can happen when you have a larger ZM setup that suffers an abrupt power loss. What happens is that
mysql will fail to start. And if you run it in debug mode with '''mysqld --verbose''' it will give the
error message above as the first reason why it won't start.
 
The steps to resolve this, are to start the db in innodb recovery mode, dump the full database, delete all
files from /var/lib/mysql except for the mysql folder (careful!), start mysql again, then recreate zm and restore
the dump. There is a chance that dumping the database can fail, so you will want to have other db backups available.
You should have a cron script that regularly backs up at least the config. Here are the full steps:
 
reference: https://dba.stackexchange.com/questions/317572/mariadb-missing-file-checkpoint


May also need to run zmupdate.pl once or twice, and possibly assign permissions to new db.
* set innodb_force_recovery=1 in /etc/mysql/my.cnf (or mariadb.cnf)(if mysql doesn't open in the next step, increment the numbers by one. I found it was necessary to use 4 or 6).
* start mysql ('''mysqld --verbose''')(if it later has trouble closing, just kill the process once your work is over)(check htop to make sure mysql is idle before running the kill command)
* dump the database with '''mysqldump -u root  zm > zmdb.sql'''
* close mysql
* delete the entire contents of /var/lib/mysql EXCEPT for /var/lib/mysql/mysql.
* comment out innodb_force_recovery
* start mysql normally (service mysql start)
* recreate the zm database:
mysql -u root  -e "create database zm;"
mysql -u root  zm < /usr/share/zoneminder/db/zm_create.sql
mysql -u root  zm < zmdb.sql
mysql -u root  -e "grant all on zm.* to 'zmuser'@localhost identified by 'zmpass';"
If all goes well, you should be back up and running. If there is still an error, you may need to restore from an earlier backup.


==See Also==
==See Also==

Latest revision as of 13:19, 20 March 2024

Setup

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

$ mysql -u root -p 
>
>use zm;
>show tables;
>select * from Monitors\G

There are some .sql setup scripts. And typically, permissions are granted for zmuser to access the zm database. This may or may not be done manually depending on what install guide you follow. It is recommended to use the guides here: Debian or Ubuntu. Sometimes things break, and you will need to rebuild the database, so for reference the steps are kept below (example from Debian):

mysql -u root -p < /usr/share/zoneminder/db/zm_create.sql
mysql -u root -p -e "grant all on zm.* to 'zmuser'@localhost identified by 'zmpass';"
mysqladmin -u root -p reload

Backup

There are two options: 1) Backup the full database. 2) Backup only the configuration.

For 1), It does not backup any videos or images. So you will have a copy of all metadata referring to events, yet the video files on the filesystem will not be saved. If it's necessary to have an offsite backup, you may want to consider a VPS as storage. Amazon S3FS support is built into Zoneminder and that is one option. Though it can be as simple as rsync-ing videos to a network share. See link on the forums

For 2), the config saved will allow you to restore all the camera configuration, zones, filters, runstates, etc... It will not, however save any event data, nor will it save any videos. If you have a hardware failure, you will lose all camera footage, and event data. For less critical installations, this is acceptable. Since most events are exported shortly after they happen, most users will want to use option 2.

/usr/bin/zmaudit.pl is usually recommended to be run to cleanup some broken database to filesystem references.

Full Backup

This may take a while. It is recommended to stop the Zoneminder service while this backup is running.

mysqldump -u root  zm > zmdb.sql

Restore

mysql -u root  zm < zmdb.sql

Backup config only

This will run quickly: usually less than a second. The Zoneminder service does not need to be stopped.

DATE="$(date +'%D'|sed  's/\//_/g')"
 mysqldump -u root  zm --ignore-table=zm.Events --ignore-table=zm.Frames --ignore-table=zm.Logs --ignore-table=zm.Stats --ignore-table=zm.Events_Day --ignore-table=zm.Events_Hour --ignore-table=zm.Events_Month --ignore-table=zm.Events_Week --ignore-table=zm.Event_Summaries > zmdb_configonly_$DATE.sql

Restore from config only or Recreate db

As mentioned above, if restoring to a new machine and starting from scratch, you will need to add permissions for zmuser to mysql. If you are restoring the config without the events table, you may need to recreate the db.

mysql -u root  -e "drop database zm;"
mysql -u root  -e "create database zm;"
mysql -u root  zm < /usr/share/zoneminder/db/zm_create.sql
mysql -u root  zm < zmdb.sql
mysql -u root  -e "grant all on zm.* to 'zmuser'@localhost identified by 'zmpass';"

You may also want to run zmupdate.pl -f to make sure the database is updated properly.

Regular Backups

You should use cron to do regular backups (where mysql_config_backup.sh is the script above, with chmod +x) . You may also want to keep copies offsite, in case of catastrophic hard drive failure.

in /etc/crontab
0 12 1 * * root cd /home/user/zmbackups && /home/user/mysql_config_backup.sh

Example Queries

Here are a number of MySQL queries that may be useful for Zoneminder. Often, accessing the database directly will be faster than using the web gui.

To start, here's an example of how you might access the ZM database from a bash shell:

sudo mysql -u root -p
mysql> use zm;
> show tables;
> select * from Users\G
> update Users set MaxBandwidth = 'Low' where Username = 'user1';

A full list of db columns can be found in the source code under the db folder. If any of these queries fail, review the field names. Things change in the db from time to time.

Change Storage Area for Multiple Cameras

If you have multiple cameras set to a storage area. It would be tedious to manually update each one.

Storage Area is the key StorageID in Monitors (1.32.3), therefore

use tmux to view mysql

select * from Monitors\G

use ctrl-b pageup pagedn to navigate and review fields.

press q to exit this view.

update Monitors set StorageId = 2 where StorageId = 0;

In practice you would probably set specific monitors, not all of them.

Set all Cameras to Use H264 Encode

Per previous example: (1.32.3 tested, review your tables for changes)

update Monitors set VideoWriter = 1 where VideoWriter = 0;

In 1.36 you should also set the following, otherwise HEVC aka H265 will be used (H265 support in 2024 is still limited in browsers and not recommended).

update Monitors set OutputCodec = 27;
update Monitors set Encoder     = "libx264";

You may also want to disable JPEG encoding.

update Monitors set SaveJPEGs = 0 where SaveJPEGs = 3;

Though, you may want to use passthrough instead of encode when possible. This example is more of an example of ZM SQL administration, not a recommendation for Zoneminder settings.

Set all cameras to limit zma to 2 FPS

Framerate in the analysis (previously zma) can be limited to lower CPU use.

1.30.4 or older:

update Monitors set AnalysisFPS="2.00" where AnalysisFPS="0";

1.34 or newer:

Analysisfps cell in zm.Monitors table has changed, therefore: instead of = 0.00 or = null we must do is null

update Monitors set AnalysisFPSLimit="2.00" where AnalysisFPSLimit is NULL;

Change All Cameras to Have mm/dd/yy Instead of dd/mm/yy in Timestamp

(1.34)

UPDATE Monitors SET LabelFormat ="%N - %m-%d-%Y %H:%M:%S %z" WHERE LabelFormat ="%N - %Y-%m-%d %H:%M:%S %z";

Change All Cameras to Have mm/dd/yy and AM/PM in Timestamp

(1.34)

UPDATE Monitors SET LabelFormat ="%N - %m/%d/%Y %l:%M:%S:%p" WHERE LabelFormat ="%N - %Y-%m-%d %H:%M:%S %z";

The %z is for offset from GMT, which most people won't need. These can be found from the man page for date.

You can review the existing Timestamps with:

select LabelFormat from Monitors\G

Zoneminder must be restarted for changes to take effect.

Check Value of AUTH_RELAY

If you set Auth relay to none, then it's possible to access cameras from wan via a direct monitor link. So check any WAN accessible installations.

cd zoneminder
grep -ri auth_relay
select * from Config where Name = "ZM_AUTH_RELAY"\G

This also means that you can get direct video URLs from a secure LAN without authentication, if desired.

https://forums.zoneminder.com/viewtopic.php?t=28144&p=117900#p117900

Add API/Mobile User with View Permissions

mysql -u zmuser -p
 use zm;
 INSERT INTO Users(Username,Password,Language,Enabled,Stream,Events,Monitors,APIEnabled)   VALUES("testguy",Password("somepass"),"en_us","1","View","View","View","1");
 \q
zmupdate.pl -f 

(-f will 'freshen' up the db, encrypting password with bcrypt, handled by perl in zmupdate.pl.in) Note: If you add a user and don't specify APIEnabled or not, it will default to enabled.

Delete User

select * from Users where Username="Defunct User"\G
delete from Users where Username="Defunct User";

Estimate RAM usage from Monitors

select x.Id, x.Width, x.Height, x.ImageBufferCount, x.Colours, x.BufferSpace as BufferMB, 1.2*sum(x.BufferSpace) over (Order by Id) as RunningTotalMB_w_OH from (select Id, Width,Height,ImageBufferCount,Colours,(Width*Height*ImageBufferCount*Colours/1024/1024) as BufferSpace  from Monitors order by Id) x;

results:

+----+-------+--------+------------------+---------+---------------+---------------------+
| Id | Width | Height | ImageBufferCount | Colours | BufferMB      | RunningTotalMB_w_OH |
+----+-------+--------+------------------+---------+---------------+---------------------+
|  1 |  1920 |   1080 |              100 |       3 |  593.26171875 |       711.914062500 |
|  2 |  1920 |   1080 |               50 |       3 |  296.63085938 |      1067.871093756 |
|  5 |  1920 |   1080 |               40 |       3 |  237.30468750 |      1352.636718756 |
|  6 |   704 |    480 |               20 |       3 |   19.33593750 |      1375.839843756 |
|  7 |   704 |    480 |               20 |       3 |   19.33593750 |      1399.042968756 |
|  8 |  1920 |   1080 |               20 |       3 |  118.65234375 |      1541.425781256 |
|  9 |   704 |    480 |               20 |       3 |   19.33593750 |      1564.628906256 |
| 10 |   704 |    480 |               20 |       3 |   19.33593750 |      1587.832031256 |
| 11 |   640 |    480 |               20 |       1 |    5.85937500 |      1594.863281256 |
| 12 |   480 |    360 |               20 |       1 |    3.29589844 |      1598.818359384 |
| 13 |  2560 |   1920 |              110 |       4 | 2062.50000000 |      4073.818359384 |
| 14 |  2560 |   1920 |              121 |       4 | 2268.75000000 |      6796.318359384 |
| 15 |   640 |    480 |               20 |       4 |   23.43750000 |      6824.443359384 |
+----+-------+--------+------------------+---------+---------------+---------------------+
13 rows in set (3.46 sec)

http://forums.zoneminder.com/viewtopic.php?f=40&p=119899&sid=c115f6a9443d70e8a4cb00c5e04883f8#p119899

Disable Logging via cli

Here is an example where mysql is scripted from the shell.

#!/bin/bash
echo "Disabling logging by setting to -5 (1 for debug, -5 for nothing)"
mysql -u root -p zm -e "Update Config set Value = '-5' where Name = 'ZM_LOG_LEVEL_SYSLOG';"
mysql -u root -p zm -e "Update Config set Value = '-5' where Name = 'ZM_LOG_LEVEL_FILE';"
mysql -u root -p zm -e "Update Config set Value = '-5' where Name = 'ZM_LOG_LEVEL_DATABASE';"

And restart zm.

Set all video lengths to be 1 hour

UPDATE Monitors SET SectionLength = "3600" where SectionLength = "600";

This can be useful for smaller setups, or where you want to limit the number of videos created. It can, however make finding motion events more difficult, so is not ideal for all setups.

Set AlarmMaxFrame to 3

From Understanding_ZoneMinder's_Zoning_system_for_Dummies:

update zm.Monitors set AlarmFrameCount="3" where AlarmFrameCount="1";

The default value is 1. This may avoid glitches causing alarms (camera feed errors can cause a blank screen for 1 frame).

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 DB 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. Zoneminder 1.32 and newer, defaults to InnoDB, and this section can be ignored.

The solutions I found were:

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.

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

MySQL Out Of Memory

If you have recently added more cameras (especially higher resolution and framerate) and you find that periodically ZM is crashing, it may be caused by MySQL running out of RAM. As an example, I have 26 cameras, ranging from 1024x720 to SD analog resolution with framerates of 3 for the HD, and 5 for the SD. This is running under 8GB of RAM. If I add two more 1024x720 cameras with a higher framerate of 5 or 6 (and double the ZMA/ZMC CPU usage) my server will periodically run out of memory and crash. Now, it's important to note that the memory doesn't run out immediately - instead, over a period of an hour or 30 minutes, or two hours (or more), the RAM will become overloaded and begin swapping, at which point there is a user mode crash from numerous programs. The lesson to all of this, is to beware of overloading a system. You may need more powerful hardware. Or split the load over multiple servers.

Forgot Root Password for MySQL

dpkg-reconfigure mysql-server-#.# works in older Debian releases, but not as of Bullseye.

Other options: https://dev.mysql.com/doc/refman/8.0/en/resetting-permissions.html

https://stackoverflow.com/questions/7534056/mysql-root-password-change

Forgot Admin Password for Zoneminder

There are different ways to resolve this. The easiest method, is probably option 3.

  • Option 1: black out password

If this happens, blank out the password for admin, and you should be able to login with a blank password.

mysql -u root -p
use zm;
show tables;
select * from Users\G
update Users set Password = "" where Password = "OLDHASHEDPASSWORD";
(or if you just have admin user only)
update Users set Password = "";

ref: https://forums.zoneminder.com/viewtopic.php?f=5&t=14543

Alternatively you can add a new password to the ZM DB. Note that mysql passwords for ZM must be encrypted. You can't just query add a new plaintext password. The following should work with mariaDB, and mysql < 8.0.11 (untested). You may also need to run zmupdate.pl -f (passwords on 1.34+ are encrypted with bcrypt and this will use perl libraries to encrypt the password if it is not. To explain this clearly, what you do is enter a plaintext password, and then run zmupdate.pl which will encrypt it for you.

MariaDB [zm]> update Users set Password=PASSWORD(NewPassword) where Username="David"; 


Reference: Mysql Reference Docs 8 : Password Function Deprecated

  • Option 2: Delete DB and restore from backup.
  • Option 3: Turn off Auth, fix, turn auth on

From the forums.

Best bet is to turn off auth, use UI to change admin password, turn auth back on.

So to turn if off use mysql

mysql -u zmuser -p zm
UPDATE Config set Value=0 where Name='ZM_OPT_USE_AUTH';

Manually Update MySQL if zmupdate.pl fails

This shouldn't be required but for reference (from: https://forums.zoneminder.com/viewtopic.php?p=131434#p131434)

mysql -u root 
  use zm
  source /usr/share/zoneminder/db/zm_update-1.36.16.sql
  source /usr/share/zoneminder/db/zm_update-1.36.18.sql
  quit

Where you would substitute the source entries for whichever updates you need.

[ERROR] InnoDB: Missing FILE_CHECKPOINT at ########## between the checkpoint ########## and the end

This error can happen when you have a larger ZM setup that suffers an abrupt power loss. What happens is that mysql will fail to start. And if you run it in debug mode with mysqld --verbose it will give the error message above as the first reason why it won't start.

The steps to resolve this, are to start the db in innodb recovery mode, dump the full database, delete all files from /var/lib/mysql except for the mysql folder (careful!), start mysql again, then recreate zm and restore the dump. There is a chance that dumping the database can fail, so you will want to have other db backups available. You should have a cron script that regularly backs up at least the config. Here are the full steps:

reference: https://dba.stackexchange.com/questions/317572/mariadb-missing-file-checkpoint

  • set innodb_force_recovery=1 in /etc/mysql/my.cnf (or mariadb.cnf)(if mysql doesn't open in the next step, increment the numbers by one. I found it was necessary to use 4 or 6).
  • start mysql (mysqld --verbose)(if it later has trouble closing, just kill the process once your work is over)(check htop to make sure mysql is idle before running the kill command)
  • dump the database with mysqldump -u root zm > zmdb.sql
  • close mysql
  • delete the entire contents of /var/lib/mysql EXCEPT for /var/lib/mysql/mysql.
  • comment out innodb_force_recovery
  • start mysql normally (service mysql start)
  • recreate the zm database:
mysql -u root  -e "create database zm;"
mysql -u root  zm < /usr/share/zoneminder/db/zm_create.sql
mysql -u root  zm < zmdb.sql
mysql -u root  -e "grant all on zm.* to 'zmuser'@localhost identified by 'zmpass';"

If all goes well, you should be back up and running. If there is still an error, you may need to restore from an earlier backup.

See Also