31 мая 2019 г.

mysql rsnapshot backup separate database table files

Backing up MySQL databases and tables as separate files with rsnapshot. Simply copying /var/lib/mysql won't always work, so the databases need to be exported.

First, create a separate MySQL user backup with these limited to read-only permissions: LOCK TABLES, SELECT, SHOW VIEW.

Install rsync - otherwise you'd get bash: rsync: command not found; rsync: connection unexpectedly closed.

Then set up this script:

#! /bin/bash

MYSQL=$(which mysql)
MYSQLDUMP=$(which mysqldump)

databases=`$MYSQL --user=$MYSQL_USER -p$MYSQL_PASSWORD -e "SHOW DATABASES;" | grep -Ev "(Database|information_schema|performance_schema)"`

for db in $databases; do
  for tbl in `$MYSQL -u $MYSQL_USER -p$MYSQL_PASSWORD -N -B -e "SHOW TABLES FROM $db"`; do
    mkdir -p "$(dirname ${VAR_LOCATION})"
    $MYSQLDUMP --skip-comments --compact -u $MYSQL_USER -p$MYSQL_PASSWORD $db $tbl > "${VAR_LOCATION}"

The above is a combination of these scripts from mensfeld.pl and jamescoyle.net.

I previously tried using the following line from this answer:

mysqldump --user=dbuser --password --tab=~/output/dir dbname
But that doesn't work, really: it throws Access denied (using password: YES) when executing 'SELECT INTO OUTFILE' because it requires a special kind of GRANT you can't easily set via the MySQL Workbench UI. After all, you end up with a CSV backup which you have to treat differently.

16 апреля 2019 г.

4 апреля 2019 г.

cakephp 3 multiple levels deep prefix

Router::prefix('api', function ($routes) {
    $routes->prefix('users', function ($routes) {

namespace App\Controller\Api\Users;

use App\Controller\AppController;
use Cake\Event\Event;
use Cake\ORM\TableRegistry;

class UsersController extends AppController

    public function index()
        // ...


1 февраля 2019 г.

dompdf setPaper size orientation not working

According to the Usage page, in order to set the paper size and orientation you need to call setPaper(). However, this won't work if in your actual HTML you're rendering you have page size definition such as this:
    @page {
        margin: 0;
        padding: 0;
        size: 210mm 297mm;
    html {
        margin: 0;
        padding: 0;
    body {
        margin: 0;
        padding: 0;
This will override whatever you set with setPaper(). Either remove it, or edit it.

24 декабря 2018 г.

Setting Apple Photos exported file created and modified times to match capture date using EXIFTool on Mac OS

I exported a few originals from the Mac Photos app. The resulting photos had Date Modified and Date Created set to the time of export. However, the EXIF capture time contained the actual capture time. Need to set file creation and modification times to match the capture time using the EXIF Tool.
exiftool "-DateTimeOriginal>FileModifyDate" /Users/admin/PhotosTest
Few things were confusing to me initially as those don't seem to be clarified in the documentation.
  • DateTimeOriginal actually stands for the capture date
  • FileModifyDate represents not only the Date Modified (file modification time), but also the Date Created (file creation time), though won't change the Date Added time. I'm referring to the Finder column names here.
  • Be careful with AllDates shortcut which you may notice in the docs, as it will alter the original capture date as well, and you'll lose it if you don't have the backup.
P.S. This works both ways: you can swap the parameters to set the EXIF capture date to match the file modification date.
exiftool "-FileModifyDate>DateTimeOriginal" /Users/admin/PhotosTest

14 декабря 2018 г.

raspberry pi openbox screen resolution

Sometimes a monitor won't report it's largest available screen resolution correctly. In such a case, you'll have to set it manually. My first hit on Google suggested some xrandr trickery which didn't work for me. Here's what did.
sudo raspi-config
Go to Advanced Options -> Resolution and choose yours. Both DMT and CEA modes worked on multiple devices, just make sure you got the right screen ratio and refresh rate.

29 августа 2018 г.

ssh tunnel systemctl systemd service

sudo vi /lib/systemd/system/mytunnel@.service:
Description=My Tunnel Service
ExecStart=/usr/bin/autossh -f -N %i
Host myhost
Hostname my-ssh-host.example.com
User username
RemoteForward localhost:22
IdentityFile /home/username/.ssh/id_rsa
ServerAliveInterval 30
ServerAliveCountMax 32
# warning: MITM
StrictHostKeyChecking no
Replace username with actual user name. Usage: sudo systemctl start mytunnel@myhost sources: