Difference between revisions of "MySQL"

Revision as of 14:13, 2 April 2021

About

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

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

Setup

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 -e "grant all on zm.* to 'zmuser'@localhost identified by 'zmpass';"
mysqladmin -u root -p reload

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

/usr/bin/zmaudit.pl will delete any database entries that don't have corresponding images on the filesystem. 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:

mysqldump -u root -p zm > zmdb.sql

Backup config only:

DAY="$(date +'%A')"
 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_$DAY.sql

Restore:

mysql -u root -p zm < zmdb.sql

If upgrading, make sure to zmupdate.pl -f

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

mysql -u root -p -e "drop database zm;"
mysql -u root -p -e "create database zm;"
mysql -u root -p zm < zmdb.sql
mysql -u root -p -e "grant all on zm.* to 'zmuser'@localhost identified by 'zmpass';"

Example Queries

sudo mysql -u root -p
mysql> use zm;
> show tables;
> select * from Users;
> 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.

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...

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;

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;

You may also want to disable JPEG encoding.

update Monitors set SaveJPEGs = 0 where SaveJPEGs = 3;

Set all cameras to limit zma to 2 FPS

FPS in 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:

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";

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.

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.

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

Add API/Mobile User with View Permissions

Quick.

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 the Quick Way

Some administration tasks as faster when handled via direct SQL entry. e.g.

select * from Users where Username="Defunct User"\G
(confirm that it looks correct, then)
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)

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

Disable Logging via cli

#!/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.

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:

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.

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

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.

Option 2 is to login to mysqld_safe --skip-grant-tables and change password for root (many guides online follow this approach).

Forgot Admin Password for Zoneminder

There are different ways to resolve this. Best advice, 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.

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

Another approach (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';

Recreate ZM DB

mysql -uroot -p < /usr/share/zoneminder/db/zm_create.sql
mysql -uroot -p -e "grant all on zm.* to 'zmuser'@localhost identified by 'zmpass';"
zmupdate.pl -f

May also need to run zmupdate.pl once or twice, and possibly assign permissions to new db.