Pages

▼

Mysql Database Maintenance & Optimization


ZoneMinder uses a mysql/mariadb database to store information. Consequently, all the normal system administration tasks that go along with managing a sql database should be applied.

What follows is intended to be a quick way to achieve a reasonable level of optimization and maintenance on your database. It it is not meant to be fully exhaustive, nor does it attempt to include every possible form of optimization, because that would take a long time and be very boring.

Schedule Database Backups

First, we need a backup script. I use the following script, which will backup all databases into a folder you specify, compress the backup, and delete backups older than 30 days.


 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
#!/bin/bash
 
# Path to the tools this scripts needs
MYSQLDUMP="/usr/bin/mysqldump"
GZIP="/bin/gzip"
FIND="/usr/bin/find"
 
# Check to see if this script has access to all the commands it needs
for CMD in $MYSQLDUMP $GZIP $FIND; do
  type $CMD &> /dev/null
 
  if [ $? -ne 0 ]; then
    echo
    echo "ERROR: The script cannot find the required command \"${CMD}\"."
    echo
    exit 1
  fi
done
 
# Enter SQL database login credentials
USER="root"
PASS="password"
 
# Specify which databases to backup
DB="--all-databases"
 
# Specify where the databases will be saved to
BKUP_PATH="/path/to/your/backup/folder"
 
# Format for the timestamp
timestamp="$(date +%Y-%m-%d_%a)"
 
# Run the backup and compress it
error=0
${MYSQLDUMP} -u${USER} -p${PASS} --events --single-transaction --quick ${DB} | ${GZIP} -9 > ${BKUP_PATH}/Nightly_dB_Backup_${timestamp}.sql.gz
 
if [ "$?" -ne 0 ]; then
 echo
 echo "WARNING: Mysqldump returned an error!"
 echo
 error=1
fi
 
# Now find & delete old backups we no logner need
${FIND} ${BKUP_PATH} -maxdepth 1 -type f -mtime +30 -delete
 
if [ "$?" -ne 0 ]; then
 echo
 echo "WARNING: Find returned an error!"
 echo
 error=1
fi
 
if [ "$error" -eq 0 ]; then
 echo "Mysql backup script completed successfully!"
fi

  1. Create a folder called "bin" under your home folder
  2. Create a folder for the database backup files to live. Make sure your user account has write permission to the folder.
  3. Copy the script above and paste it into a file using your favorite text editor. 
  4. Edit the database login credentials, and the backup destination folder to suite. 
  5. Save the script as ~/bin/mysql_backup.sh
  6. Mark the script as executable e.g. chmod +x ~/bin/mysql_backup.sh
  7. Add a cron task to run the backup script on a regular interval. e.g. crontab -e 
  8. To run a backup every night at 11pm, your cronjob will look like this:
0 23 * * * /home/yourusername/bin/mysql_backup.sh

Schedule Database Repair

Add the following cronjob to perform regular checks on your database:
00 04 * * 0 /usr/bin/mysqlcheck -uroot -ppassword --all-databases --auto-repair
As you can see, this will run every morning at 4am. That's it. That's all you have to do.

 

Optimize the dB Engine

For machines with little memory, such as the Raspberry Pi, your goal can only be to optimize mysql to use as little memory as possible. This will mean taking a performance hit, but as long as the number of clients access the database is low, it should be useable.

To optimize memory consumption, I recommend you start with one of the templates included with some sql server installations. In my case, the templates are under /usr/share/mysql and are named my-small.cnf, my-medium.cnf, and my-large.cnf. Read through the descriptions of each to learn how much memory each configuration uses. For my Raspberry Pi 3, I used the my-medium.cnf template. In the template, I uncommented "skip-networking" and all of the "innodb_" parameters. When finished, save the template to the mysql config folder. On my machine, that is under /etc/my.cnf.d.

Machines with lots of memory can optimize for performance. A simple, though not exhaustive way, to do that is to install and run the tool mysqltuner. The general idea here is to run mysqltuner once a day for a week or so, and basically make the changes it says to make. Note, however, it is a good idea to read about each parameter suggested by mysqltuner rather than blindly making the suggested change. Mysqltuner is not perfect, and it may suggest you make changes that could be considered controversial (Read -> Angry Nerds), should you dig deep into the Internets to learn the binary bits of the mysql parameter you are about to modify.

As an example, here are the end results of running mysqltuner on a new zoneminder system:
General recommendations:
    Configure your accounts with ip or subnets only, then update your configuration with skip-name-resolve=1
    When making adjustments, make tmp_table_size/max_heap_table_size equal
    Reduce your SELECT DISTINCT queries which have no LIMIT clause
    Set thread_cache_size to 4 as a starting value
Variables to adjust:
    query_cache_type (=1)
    tmp_table_size (> 16M)
    max_heap_table_size (> 16M)
    thread_cache_size (start at 4)
    innodb_buffer_pool_instances (=1)

This was run on a system with an empty zoneminder database so the values themselves are not accurate, but as you can see, mysqltuner tells you exactly what parameters to modify in your mysql config file. Don't forget, to keep running mysqltuner over a period of a week or so. Over time, it will suggest new/different values to adjust.