Oli's Spickliste / Cheat sheet

My brain is getting old and mushy, so I am starting to write stuff down...

Table of Contents

• File Limit für MySQL hoch setzen
• Entropie auf virtuellen Maschinen (VM guests)
• Unleserliche Firefox Tooltips unter KDE/Kubuntu
• Apache mod_rewrite Beispiele
• Historische Performance-Daten mittels sar
• Debian Pakete mit Abhängigkeiten installieren
• Linux Filesystem Cache flushen
• MySQL/MariaDB CLI mini-Benchmark mit Microsekunden Präzision
• Bilder auf 1200x900 skalieren
• StarOffice Dokumente nach LibreOffice ODF konvertieren
• HPLIP Farbdruckproblem-Workaround
• ISO Image aus CD/DVD bauen
• Pile of Poo Emoji
• echo before cat
• File download from Xiaomi Mi Mix 2 on Linux
• Pretty Print MariaDB output
• List Listeners with lsof
• SystemD Notizen
• Cutting and manipulating PDF
• Stopping Baloo
• Distribution independent PHP configuration
• Check if a Linux reboot is required
• Vim Cheat Sheet
• Create Swap File and set Swappiness
• Teamviewer hacks
• Ersatz für ifconfig Statistiken
• xtrabackup 2.4 - wichtigste Befehle
• CentOS boot device full
• Convert LibreOffice Spreadsheet to CSV file format
• Compare LibreOffice Presentations
• DHCP client handling
• Excise Baloo
• Cat from STDIN to file for fpmmm test
• Remote Syslog Server
• List of most recent installed packages
• Upgrade only specific packages
• GLIBC version
• InnoDB NUMA interleave with MariaDB
• Extracting and inspecting packages
• Taking a node out of Galera Cluster
• Make Linux fast again
• Checking Galera Cluster SST timeout with Systemd
• MariaDB persistent statistics
• Find recently modified files
• Change timestamps of files
• Trick to Simulate a Linux Server with less RAM
• MySQL Repository Key expired
• Lengths of lines
• Debian 11 vim Copy/Paste broken
• Kill MariaDB Galera node under SystemD
• Audio is broken
• Powershell cheat sheet
• ssh tunnel for port forwarding
• Cutting lines out of a huge file
• Colorize a tag with tail
• Which GLIBC version is required?
• Backtrace when MariaDB hangs
• tar with pigz
• AppArmor on Ubuntu 24.04 and MySQL 8.0
• Find columns with non-central-european-characters
• Generate rows with SQL means
• List absolute path of a file

File Limit für MySQL hoch setzen

Caution: This is obsolete nowadays. Use SystemD if possible!

shell> su - mysql
shell> ulimit -Sn
1024
shell> ulimit -Hn
4096

shell> cat /etc/security/limits.d/mysql.conf
#
# /etc/security/limits.d/mysql.conf
#

mysql            hard    nofile          16384
mysql            soft    nofile          16384

shell> grep pam_limits /etc/pam.d/common-session
session required        pam_limits.so

Login reicht und Reboot sollte auf Ubuntu 14.04 nicht notwendig sein.

shell> su - mysql
shell> ulimit -Sn
16384
shell> ulimit -Hn
16384

Achtung: Siehe auch SystemD Notizen.

Entropie auf virtuellen Maschinen (VM guests)

Ein neues Phänomen, mit welchem wir möglicherweise in Zukunft zu kämpfen haben, ist die Entropie in virtuellen Maschinen. Diese wird für Verschlüsselung benötig. Da wir aus Sicherheitsgründen vermehrt https und SSL etc. verwenden sollten, und alle Welt Virtualisierung so super cool findet, wird dieses Problem in naher Zukunft verstärkt auftreten. Insbesondere auch mit MySQL ab der Version 5.7 bei welcher ja SSL per default eingeschaltet ist.

Je nach Quelle sollte die Entropie nicht unter 1000 oder nicht unter 100 fallen. Wie dem auch sei: Monitoren kann nicht schaden:

shell> cat /proc/sys/kernel/random/entropy_avail
956

Siehe dazu auch:

• Check available entropy in Linux
• Myths about /dev/urandom

Unleserliche Firefox Tooltips unter KDE/Kubuntu

Deutsch: KDE-Menü: Einstellungen -> Systemeinstellungen -> Allgemeines Aussehen und Verhalten -> Anwendungs-Aussehen (öffnet neues Fenster) -> Farben (in Auswahl links) -> Farben (als Reiter) -> Tooltip Hintergrund und Tooltip Text

English: KDE-Menue: Settings -> System Settings -> Common Appearance and Behavior -> Application Appearance -> Colors (left column) -> Colors (tab) -> Tooltip Background and Tooltip Text

Apache mod_rewrite Beispiele

Gilt nur für Apache 2.4 und neuer: Rewrite trace aktivieren (nicht auf der Produktion!)

#
#/etc/apache2/conf-available/rewrite_log.conf 
#
LogLevel rewrite:trace8

shell> a2enconf rewrite_log
shell> service apache2 restart

Logged ins Apache Error Log (/var/log/apache2/error.log) und sieht wie folgt aus:

[Thu Feb 23 11:24:15.615062 2017] [rewrite:trace3] [pid 19548] mod_rewrite.c(468): [client 127.0.0.1:46136] 127.0.0.1 - - [localhost/sid#7efc6e9452d8][rid#7efc6e6710a0/initial] [perdir /var/www/html/] strip per-dir prefix: /var/www/html/forumdisplay.php -> forumdisplay.php
[Thu Feb 23 11:24:15.615147 2017] [rewrite:trace3] [pid 19548] mod_rewrite.c(468): [client 127.0.0.1:46136] 127.0.0.1 - - [localhost/sid#7efc6e9452d8][rid#7efc6e6710a0/initial] [perdir /var/www/html/] applying pattern 'forumdisplay.php' to uri 'forumdisplay.php'
[Thu Feb 23 11:24:15.615181 2017] [rewrite:trace4] [pid 19548] mod_rewrite.c(468): [client 127.0.0.1:46136] 127.0.0.1 - - [localhost/sid#7efc6e9452d8][rid#7efc6e6710a0/initial] [perdir /var/www/html/] RewriteCond: input='fid=1q' pattern='fid=1' => matched
[Thu Feb 23 11:24:15.615195 2017] [rewrite:trace2] [pid 19548] mod_rewrite.c(468): [client 127.0.0.1:46136] 127.0.0.1 - - [localhost/sid#7efc6e9452d8][rid#7efc6e6710a0/initial] [perdir /var/www/html/] rewrite 'forumdisplay.php' -> 'https://fromdual.com/forum/416'
[Thu Feb 23 11:24:15.615216 2017] [rewrite:trace2] [pid 19548] mod_rewrite.c(468): [client 127.0.0.1:46136] 127.0.0.1 - - [localhost/sid#7efc6e9452d8][rid#7efc6e6710a0/initial] discarding query string
[Thu Feb 23 11:24:15.615229 2017] [rewrite:trace2] [pid 19548] mod_rewrite.c(468): [client 127.0.0.1:46136] 127.0.0.1 - - [localhost/sid#7efc6e9452d8][rid#7efc6e6710a0/initial] [perdir /var/www/html/] explicitly forcing redirect with https://fromdual.com/forum/416
[Thu Feb 23 11:24:15.615241 2017] [rewrite:trace1] [pid 19548] mod_rewrite.c(468): [client 127.0.0.1:46136] 127.0.0.1 - - [localhost/sid#7efc6e9452d8][rid#7efc6e6710a0/initial] [perdir /var/www/html/] escaping https://fromdual.com/forum/416 for redirect
[Thu Feb 23 11:24:15.615255 2017] [rewrite:trace1] [pid 19548] mod_rewrite.c(468): [client 127.0.0.1:46136] 127.0.0.1 - - [localhost/sid#7efc6e9452d8][rid#7efc6e6710a0/initial] [perdir /var/www/html/] redirect to https://fromdual.com/forum/416 [REDIRECT/301]

Einige Rewrite Regeln:

#
# ./.htaccess
#

# To activate .htaccess it needs:
# AllowOverride All
# on <Directory ... />

# Activate the following line to test if .htaccess is read (Error 500):
# Break.

RewriteEngine On

# Forum rewrite: /forumdisplay.php&fid=1 -> https://fromdual.com/forum/416
# Do NOT attach query string (QSD), Redirect permanently (R), Do not continue if match
RewriteCond "%{QUERY_STRING}" ="fid=1"
RewriteRule "forumdisplay.php" "https://fromdual.com/forum/416" [QSD,R=301,L]

...

RewriteCond "%{QUERY_STRING}" ="fid=99"
RewriteRule "forumdisplay.php" "https://fromdual.com/forum/775" [QSD,R=301,L]

# Catch-all
RewriteRule ".*" "https://fromdual.com/forum" [QSD,R=301,L]

Historische Performance-Daten mittels sar

Allenfalls vorgängig sar entsprechend konfigurieren (Ubuntu):

# /etc/default/sysstat
ENABLED="true"

# /etc/cron.d/sysstat
# Activity reports every minute everyday
*  *  *  *  * root command -v debian-sa1 > /dev/null && debian-sa1 1 1

Die sar History-Files (31 verfügbare Tage) liegen hier:

# Ubuntu
shell> ll -ltr /var/log/sysstat/sa??

# CentOS
shell> ll -ltr /var/log/sa/sa??

Network

shell> sar -n DEV,EDEV -s 09:00:00 -e 10:59:59 -f /var/log/sysstat/sa03 | grep -e IFACE -e eth0

09:56:01        IFACE   rxpck/s   txpck/s    rxkB/s    txkB/s   rxcmp/s   txcmp/s  rxmcst/s   %ifutil
09:58:01         eth0      2.45      2.74      0.69      0.52      0.00      0.00      0.01      0.00
10:00:01         eth0      7.47      6.95      0.84      0.87      0.00      0.00      0.01      0.00
10:02:01         eth0      4.78      5.03      0.85      1.05      0.00      0.00      0.01      0.00
10:04:01         eth0      6.42      5.70      2.90      0.79      0.00      0.00      0.01      0.00
Average:         eth0      5.28      5.10      1.32      0.81      0.00      0.00      0.01      0.00

09:56:01        IFACE   rxerr/s   txerr/s    coll/s  rxdrop/s  txdrop/s  txcarr/s  rxfram/s  rxfifo/s  txfifo/s
09:58:01         eth0      0.00      0.00      0.00      0.00      0.00      0.00      0.00      0.00      0.00
10:00:01         eth0      0.00      0.00      0.00      0.00      0.00      0.00      0.00      0.00      0.00
10:02:01         eth0      0.00      0.00      0.00      0.00      0.00      0.00      0.00      0.00      0.00
10:04:01         eth0      0.00      0.00      0.00      0.00      0.00      0.00      0.00      0.00      0.00
Average:         eth0      0.00      0.00      0.00      0.00      0.00      0.00      0.00      0.00      0.00

shell> sar -n DEV,EDEV -s 09:00:00 -e 10:59:59 -4 | grep -e IFACE -e eth0
Linux 3.13.0-110-generic (chef)         03/03/17        _x86_64_        (4 CPU)

09:54:24          LINUX RESTART

09:56:01        IFACE   rxpck/s   txpck/s    rxkB/s    txkB/s   rxcmp/s   txcmp/s  rxmcst/s   %ifutil
09:58:01         eth0      2.45      2.74      0.69      0.52      0.00      0.00      0.01      0.00
10:00:01         eth0      7.47      6.95      0.84      0.87      0.00      0.00      0.01      0.00
10:02:01         eth0      4.78      5.03      0.85      1.05      0.00      0.00      0.01      0.00
10:04:01         eth0      6.42      5.70      2.90      0.79      0.00      0.00      0.01      0.00
10:06:01         eth0      5.21      3.20      0.62      0.49      0.00      0.00      0.01      0.00
Average:         eth0      5.26      4.72      1.18      0.74      0.00      0.00      0.01      0.00

09:56:01        IFACE   rxerr/s   txerr/s    coll/s  rxdrop/s  txdrop/s  txcarr/s  rxfram/s  rxfifo/s  txfifo/s
09:58:01         eth0      0.00      0.00      0.00      0.00      0.00      0.00      0.00      0.00      0.00
10:00:01         eth0      0.00      0.00      0.00      0.00      0.00      0.00      0.00      0.00      0.00
10:02:01         eth0      0.00      0.00      0.00      0.00      0.00      0.00      0.00      0.00      0.00
10:04:01         eth0      0.00      0.00      0.00      0.00      0.00      0.00      0.00      0.00      0.00
10:06:01         eth0      0.00      0.00      0.00      0.00      0.00      0.00      0.00      0.00      0.00
Average:         eth0      0.00      0.00      0.00      0.00      0.00      0.00      0.00      0.00      0.00

Disk

shell> sar -d -p -1 -f /var/log/sysstat/sa03  | grep -e DEV -e sdb

09:56:01          DEV       tps  rd_sec/s  wr_sec/s  avgrq-sz  avgqu-sz     await     svctm     %util
09:58:01          sdb      3.92      1.73    100.04     25.94      0.04      9.63      0.75      0.29
10:00:01          sdb      3.39      0.00     76.45     22.55      0.05     14.07      0.47      0.16
10:02:01          sdb      5.46      0.00    294.50     53.94      0.11     20.54      0.76      0.42
10:04:01          sdb      3.20      0.00     91.65     28.65      0.04     11.02      0.71      0.23
10:06:01          sdb      3.98      1.07    105.26     26.69      0.06     14.45      0.59      0.23
Average:          sdb      3.99      0.56    133.67     33.62      0.06     14.56      0.67      0.27

Swap und Swapping

shell> sar -B -s 15:00:00 -e 15:59:59 -f /var/log/sysstat/sa02

15:00:01     pgpgin/s pgpgout/s   fault/s  majflt/s  pgfree/s pgscank/s pgscand/s pgsteal/s    %vmeff
15:01:01         1.66    822.28   5624.93      0.08   5309.67    239.14      0.00    232.13     97.07
15:02:01        18.77    506.68   4902.25      0.20   5094.89      0.00      0.00      0.00      0.00
15:03:01         5.81    644.31   3742.01      0.07   3823.89      0.00      0.00      0.00      0.00
15:04:01         4.82    507.51   4387.05      0.17   4695.08      0.00      0.00      0.00      0.00
15:05:01         9.48    563.11   6127.06      0.02   6717.79      0.00      0.00      0.00      0.00

shell> sar -S -s 15:00:00 -e 15:59:59 -f /var/log/sysstat/sa02

15:00:01    kbswpfree kbswpused  %swpused  kbswpcad   %swpcad
15:01:01     30132988   1117440      3.58     18380      1.64
15:02:01     30132988   1117440      3.58     18380      1.64
15:03:01     30132988   1117440      3.58     18380      1.64
15:04:01     30132988   1117440      3.58     18388      1.65
15:05:01     30132988   1117440      3.58     18388      1.65

shell> sar -W -s 15:00:00 -e 15:59:59 -f /var/log/sysstat/sa02

15:00:01     pswpin/s pswpout/s
15:01:01         0.00      0.10
15:02:01         0.00      0.00
15:03:01         0.00      0.00
15:04:01         0.03      0.00
15:05:01         0.00      0.00

Debian Pakete mit Abhängigkeiten installieren

shell> dpkg -i some_package.deb
shell> apt-get install --fix-broken

shell> apt install ./some_package.deb

Linux Filesystem Cache flushen

shell> sudo echo 3 > /proc/sys/vm/drop_caches

MySQL/MariaDB CLI mini-Benchmark mit Microsekunden Präzision

SQL> SELECT UNIX_TIMESTAMP(CURRENT_TIMESTAMP(6)) INTO @begin; DO BENCHMARK(10, SLEEP(1)); SELECT UNIX_TIMESTAMP(CURRENT_TIMESTAMP(6)) INTO @end; SELECT ROUND(@end - @begin, 6) AS elapsed;
Query OK, 1 row affected (0.00 sec)
Query OK, 0 rows affected (10.00 sec)
Query OK, 1 row affected (0.00 sec)
+-----------+
| elapsed   |
+-----------+
| 10.000955 |
+-----------+
1 row in set (0.00 sec)

Bilder auf 1200x900 skalieren

shell> for i in $(ls -1 *.JPG | cut -b5-8) ; do
  convert DSCN${i}.JPG -resize 1200x900 DSCN${i}_1200x900.JPG
done

StarOffice Dokumente nach LibreOffice ODF konvertieren

LibreOffice 5.3 soll StarCalc (.sdc), StarDraw/StarImpress (.sda, .sdd, .sdp) und StarWriter (.sdw) wieder unterstützen. Vielleicht ist das der einfachere Weg?

Basis: VirtualBox Debian 9 VM. StarOffice Download.

shell> wget https://downloadarchive.documentfoundation.org/libreoffice/old/3.6.7.2/deb/x86_64/LibO_3.6.7.2_Linux_x86-64_install-deb_en-US.tar.gz
shell> wget https://downloadarchive.documentfoundation.org/libreoffice/old/3.6.7.2/deb/x86_64/LibO-SDK_3.6.7.2_Linux_x86-64_install-deb_en-US.tar.gz

shell> tar xf LibO_3.6.7.2_Linux_x86-64_install-deb_en-US.tar.gz 
shell> tar xf LibO-SDK_3.6.7.2_Linux_x86-64_install-deb_en-US.tar.gz 

shell> dpkg -i libobasis3.6-core01_3.6.7.2-2_amd64.deb libreoffice3.6-ure_3.6.7.2-2_amd64.deb
shell> dpkg -i libobasis3.6-core0?_3.6.7.2-2_amd64.deb
shell> dpkg -i libreoffice3.6-writer_3.6.7.2-2_amd64.deb libobasis3.6-writer_3.6.7.2-2_amd64.deb libreoffice3.6_3.6.7.2-2_amd64.deb libobasis3.6-images_3.6.7.2-2_amd64.deb 
shell> dpkg -i libobasis3.6-writer_3.6.7.2-2_amd64.deb libreoffice3.6-base_3.6.7.2-2_amd64.deb libobasis3.6-base_3.6.7.2-2_amd64.deb
shell> dpkg -i *.deb
shell> dpkg -i libobasis3.6-sdk_3.6.7.2-2_amd64.deb 
shell> apt-get install openjdk-8-jre

Dann von einer neuen Konsole aus:

shell> ssh -X root@192.168.56.101
/opt/libreoffice3.6/program/swriter

Dokumente finden

shell> find . \( -name "*.sdw" -o -name "*.sdc" -o -name "*.sdd" \)

Zu alte Dokumente (2001) haben nicht funktioniert. Um noch zumindest an den Text zu gelangen wurde wie folgt vorgegangen:

shell> strings rumtopf.sdw > rumtopf.txt

HPLIP Farbdruckproblem-Workaround

• VirtualBox Ubuntu 16.04 starten.
• HPLIP installieren (deinstallieren hat nicht funktioniert):
  

  shell> sudo hp-uninstall
  shell> dpkg -l | grep hpli
  shell> ./hplip-3.17.11.run
  shell> hp-setup
  

• Drucker konfigurieren:
  

  shell> ssh -X oli@192.168.1.131
  shell> hp-setup
  shell> hp-testpage
  

• Show all status information:
  

  shell> lpstat -t

• Show printers:
  

  shell> lpstat -p

• Show default destination:
  

  shell> lpstat -d

• Set default destination:
  

  shell> lpoptions -d<printer>

• List print jobs:
  

  shell> lpq -P<printer>
  shell> lpq -l -P<printer>
  

• Delete a job:
  

  shell> lprm -P<pritner> <job_id>

• Print a file:
  

  shell> lpr -P<printer> <file>

• Drucken:
  

  shell> lp -d HP_PageWide_Pro_477dw_MFP *.pdf

ISO Image aus CD/DVD bauen

Geklaut bei Thomas Krenn (hat sehr oft sehr gute Artikel dort!).

shell> isoinfo -d -i /dev/cdrom | grep -i -E 'block size|volume size'
Logical block size is: 2048
Volume size is: 327867
shell> dd if=/dev/cdrom of=test.iso bs=<block size> count=<volume size>

Pile of Poo Emoji

Caution: My current Drupal 7 cannot Emojis!

SQL> SET NAMES utf8mb4;
SQL> ALTER TABLE test CONVERT TO CHARACTER SET utf8mb4;
SQL> INSERT INTO test VALUES (NULL, 0xF09F92A9, NULL);
SQL> -- 💩
SQL> -- U+1F4A9
SQL> -- 0xF09F92A9
SQL> SELECT id, HEX(data), data FROM test;
+----+-----------+------+
| id | hex(data) | data |
+----+-----------+------+
|  1 | F09F92A9  | 💩    |
+----+-----------+------+

echo before cat

shell> echo 'SET SESSION sql_log_bin = 0;' | gzip --to-stdout | \
       zcat - full_dump.sql.gz | mysql --user=root

or even nicer:

shell> {
  echo 'SET SESSION sql_log_bin = 0;'
  zcat full_dump.sql.gz
} | mysql --user=root

or alternatively:

shell> cat full_dump.sql | mysql -u root --init-command='SET SESSION sql_log_bin=0; \
          SET GLOBAL innodb_flush_log_at_trx_commit=0; SET GLOBAL super_read_only = off;'

File download from Xiaomi Mi Mix 2 on Linux

On my Linux I get some strange error messages when I want to download the photos from my mobile via USB. So I had to find another way.
I installed an ssh server and then I can easily download my photos and GPS tracks. Unfortunately this ssh server only allows the weak dss (DSA) keys. It seems like somebody wants to participate in my pleasure:

shell> scp -o HostKeyAlgorithms=+ssh-dss -P 12222 ssh@192.168.1.121:/storage/emulated/0/DCIM/Camera/* .
ssh@192.168.1.121's password: 
IMG_20180530_174731.jpg                       100% 5274KB   5.2MB/s   00:01    
IMG_20180603_110345_1.jpg                     100% 6294KB   3.1MB/s   00:02    

shell> scp -o HostKeyAlgorithms=+ssh-dss -P 12222 ssh@192.168.1.121:/storage/emulated/0/Android/data/net.osmand/files/tracks/rec/*.gpx .
ssh@192.168.1.121's password:                                                                                                   
2018-06-02_21-17_Sat.gpx                      100%   14KB  14.1KB/s   00:00     
2018-06-03_09-27_Sun.gpx                      100%  122KB 122.0KB/s   00:00

Pretty Print MariaDB output

shell> mysql --user=root --execute='SHOW GLOBAL VARIABLES LIKE "innodb%undo%"' | column -t
Variable_name             Value
innodb_max_undo_log_size  10485760
innodb_undo_directory     ./
innodb_undo_log_truncate  OFF
innodb_undo_logs          128
innodb_undo_tablespaces   0

List Listeners with lsof

List Listeners without using old netstat or ugly ss.

shell> lsof -itcp:3306 -P
COMMAND   PID  USER   FD   TYPE   DEVICE SIZE/OFF NODE NAME
mysqld  22798 mysql   38u  IPv6 11528169      0t0  TCP *:3306 (LISTEN)

shell> lsof -itcp -P | grep mysqld | grep LIST
mysqld     3406    mysql   19u  IPv6    19530      0t0  TCP *:35622 (LISTEN)
mysqld     4384    mysql   35u  IPv6    19278      0t0  TCP *:3311 (LISTEN)
mysqld     4440    mysql   45u  IPv6    20634      0t0  TCP *:3309 (LISTEN)
mysqld     4440    mysql   63u  IPv6    19309      0t0  TCP *:33060 (LISTEN)
mysqld     4487    mysql   33u  IPv6    19319      0t0  TCP *:3324 (LISTEN)
mysqld     4532    mysql   24u  IPv6    20681      0t0  TCP *:3320 (LISTEN)
mysqld     4581    mysql   23u  IPv6    20714      0t0  TCP *:3332 (LISTEN)
mysqld     4904    mysql   35u  IPv6    20863      0t0  TCP *:3340 (LISTEN)
mysqld    16950    mysql   31u  IPv6  6647356      0t0  TCP *:3392 (LISTEN)
mysqld    20285    mysql   30u  IPv6  6671523      0t0  TCP *:3391 (LISTEN)
mysqld    22798    mysql   11u  IPv4 11528151      0t0  TCP *:4567 (LISTEN)
mysqld    22798    mysql   38u  IPv6 11528169      0t0  TCP *:3306 (LISTEN)
mysqld    25909    mysql   14u  IPv6  4128243      0t0  TCP *:3319 (LISTEN)
mysqld    26049    mysql   16u  IPv6  8302167      0t0  TCP *:3318 (LISTEN)

Cutting and manipulating PDF

shell> kpdf file.pdf
shell> evince file.pdf
shell> gpdf file.pdf

# Remove pages from PDF
shell> pdftk A=file.pdf cat A1-9 A11-end output cut.pdf

# Rotate 180°
shell> pdftk file.pdf cat 1-endS output out.pdf

# Concatenate PDF documents:
shell> pdftk 1.pdf 2.pdf 3.pdf cat output 123.pdf

# Convert PDF to PNG and JPG to PDF
shell> convert nda_3.pdf -resize 2380 nda_3.png
shell> convert -page A4 file.jpg file.pdf (with OpenOffice!)

# Putting 2 A4 input pages onto 1 A4 output page
# Ubuntu: package "bookletimposer"
shell> bookletimposer -o OUTPUT.pdf -p 2x1 INPUT.pdf

# Cut some pages from ad PDF document
shell> pdftk document.pdf cat 1-3 5 output part_p1-3_and5.pdf

See also: Shrink PDF size with this command line trick

Stopping Baloo

shell> balooctl status
Baloo Index could not be opened
shell> balooctl stop
shell> balooctl disable
Disabling the File Indexer

Distribution independent PHP configuration

shell> cd /etc/php/7.2/mods-available
shell> touch fromdual_cli.ini
shell> touch fromdual_apache2.ini

shell> phpenmod -s cli fromdual_cli
shell> phpenmod -s apache2 fromdual_apache2

shell> phpquery -v 7.2 -s cli -m fromdual_cli
shell> phpquery -v 7.2 -s apache2 -m fromdual_apache2

# This does NOT show the correct values vor apache2 SAPI!!!
shell> php -r 'phpinfo();' | grep --color timezone
Default timezone => Europe/Zurich
date.timezone => Europe/Zurich => Europe/Zurich

Check if a Linux reboot is required

• Red Hat/CentOS: sudo yum install yum-utils; sudo needs-restarting -r; sudo needs-restarting -s
• Debian/Ubuntu: cat /var/run/reboot-required

Vim Cheat Sheet

• Word to upper/lower case: gUw / guw
• Record: ESC q<letter>...ESC q
• Play: @<letter> or @@ for last recording.

Create Swap File and set Swappiness

shell> swapon --show
shell> fallocate -l 16G /swapfile
shell> # dd if=/dev/zero of=/swapfile bs=1048576 count=16384
shell> chmod 600 /swapfile
shell> mkswap /swapfile
shell> swapon /swapfile
shell> swapon --show
shell> echo '/swapfile none swap sw 0 0' >>/etc/fstab
shell> free

shell> sysctl vm.swappiness=1
shell> echo 'vm.swappiness=1' >> /etc/sysctl.d/30-mysql.conf
shell> systemctl restart procps
shell> sysctl vm.swappiness

Teamviewer hacks

In Teamviewer sessions the following problemes regularly appear:

• ESC does not work any more or launches Windows Start Menu. Hack: Ctrl+[ [1].
• Copy/Paste does not work as expected (Windows style: right mouse click).
• Mouse pointer gets black (you do not see any more where you click on a black background). Change PuTTY color: PuTTY Configuration -> Window -> Colours -> Use system colours.

Ersatz für ifconfig Statistiken

Der Linux Befehl ifconfig ist heute veraltet und sollte daher nicht mehr benutzt werden [ Lit ]. Auf einigen Distributionen wird er schon gar nicht mehr mit installiert und muss nachinstalliert werden. Es ist somit an der Zeit sich nach zeitgemässen Alternativen umzuschauen:

shell> ifconfig enp0s25
enp0s25: flags=4163  mtu 1500
        inet 192.168.1.135  netmask 255.255.255.0  broadcast 192.168.1.255
        inet6 fe80::d250:99ff:fe70:f88e  prefixlen 64  scopeid 0x20
        inet6 2a02:120b:2c01:ac30:d250:99ff:fe70:f88e  prefixlen 64  scopeid 0x0
        ether d0:50:99:70:f8:8e  txqueuelen 1000  (Ethernet)
        RX packets 31134351  bytes 40549421982 (40.5 GB)
        RX errors 0  dropped 23616  overruns 0  frame 0
        TX packets 12088228  bytes 11847729353 (11.8 GB)
        TX errors 0  dropped 0 overruns 0  carrier 0  collisions 0
        device interrupt 20  memory 0xefc00000-efc20000  

Ein möglicher Ersatz ist der Befehl netstat. Aber auch dieser Befehl ist inzwischen obsolet und sollte nicht mehr verwendet werden: This program is mostly obsolete. Replacement for netstat is ss. Replacement for netstat -r is ip route. Replacement for netstat -i is ip -s link. Replacement for netstat -g is ip maddr.

shell> netstat -i
Kernel Interface table
Iface      MTU    RX-OK RX-ERR RX-DRP RX-OVR    TX-OK TX-ERR TX-DRP TX-OVR Flg
ens160    1500    19929      0    961 0           622      0      0      0 BMRU
lo       65536       92      0      0 0            92      0      0      0 LRU

Somit kommen wir zum empfohlenen Befehl:

shell> ip -s link show ens160
2: ens160:  mtu 1500 qdisc mq state UP mode DEFAULT group default qlen 1000
    link/ether 00:50:56:a4:5c:27 brd ff:ff:ff:ff:ff:ff
    RX: bytes  packets  errors  dropped overrun mcast   
    1466147    17798    0       861     0       883     
    TX: bytes  packets  errors  dropped carrier collsns 
    100282     383      0       0       0       0       

xtrabackup 2.4 - wichtigste Befehle

# Place ssh keys on target
shell> export PATH=$PATH:/home/mysql/product/percona-xtrabackup-2.4.16-Linux-x86_64/bin
# Compress is probably NOT a good idea over a fast network?
shell> xtrabackup --user=root --backup --compress --stream=xbstream | ssh -p 22 mysql@targethost "/home/mysql/product/xtrabackup/bin/xbstream -x -C /var/lib/mysql"

# Install qpress on target
# Debian/Ubuntu
shell> wget http://repo.percona.com/apt/pool/main/q/qpress/qpress_11-1.bionic_amd64.deb
shell> dpkg -i qpress_11-1.bionic_amd64.deb 
# Red Hat/CentOS
shell> wget https://repo.percona.com/yum/release/7/RPMS/x86_64/qpress-11-1.el7.x86_64.rpm
shell> yum localinstall qpress-11-1.el7.x86_64.rpm

shell> xtrabackup --user=root --decompress --target-dir=/var/lib/mysql
shell> xtrabackup --user=root --prepare --target-dir=/var/lib/mysql
shell> chown -R mysql: /var/lib/mysql

CentOS boot device full

shell> vi /etc/yum.conf
installonly_limit=3

shell> yum install yum-utils

shell> package-cleanup --oldkernels --count=2

Literature: StackExchange

Convert LibreOffice Spreadsheet to CSV file format

shell> file *.ods
expenses_2019-40.ods: OpenDocument Spreadsheet

shell> unoconv --show
shell> unoconv --doctype spreadsheet --format=csv expenses_2019-*.ods
func=xmlSecCheckVersionExt:file=xmlsec.c:line=188:obj=unknown:subj=unknown:error=19:invalid version:mode=abi compatible;expected minor version=2;real minor version=2;expected subminor version=25;real subminor version=26

shell> libreoffice --convert-to csv expenses_2019-*.ods

func=xmlSecCheckVersionExt:file=xmlsec.c:line=188:obj=unknown:subj=unknown:error=19:invalid version:mode=abi compatible;expected minor version=2;real minor version=2;expected subminor version=25;real subminor version=26
convert /tmp/expenses_2019-40.ods -> /tmp/expenses_2019-40.csv using filter : Text - txt - csv (StarCalc)

shell> cat *.csv | grep -v -e ',,,,,,' -e Employee -e Submitted -e Exchange > expenses_2019.csv 

Compare LibreOffice Presentations

shell> unoconv --doctype presentation --format=html galera*.odp
shell> diff galera_new.html galera_old.html

DHCP client handling

Stop running DHCP client without releasing the current lease:

shell> dhclient -x

Stop running DHCP client and release current lease.

shell> dhclient -r

Start DHCP client and configure interface using the DHCP protocol:

shell> dhclient enp4s0f1

Excise Baloo

I hope this helps:

shell> dpkg -l | grep baloo
ii  baloo-kf5                                       5.44.0-0ubuntu1                                          amd64        framework for searching and managing metadata
ii  libkf5baloo5                                    5.44.0-0ubuntu1                                          amd64        framework for searching and managing metadata core lib.
ii  libkf5balooengine5                              5.44.0-0ubuntu1                                          amd64        framework for searching and managing metadata plugins
ii  libkf5baloowidgets-bin                          4:17.12.3-0ubuntu1                                       amd64        Wigets for use with Baloo - binaries
ii  libkf5baloowidgets-data                         4:17.12.3-0ubuntu1                                       all          Wigets for use with Baloo - data files
ii  libkf5baloowidgets5:amd64                       4:17.12.3-0ubuntu1                                       amd64        Wigets for use with Baloo

shell> dpkg-query -L baloo-kf5 | grep bin
/usr/bin
/usr/bin/baloo_file
/usr/bin/baloo_file_extractor
/usr/bin/balooctl
/usr/bin/baloosearch
/usr/bin/balooshow

shell> dpkg -r --force-depends baloo-kf5

Cat from STDIN to file for fpmmm test

shell> cat << _EOF >>/tmp/tst
FromDual.maas2.prod2 FromDual.MySQL.backup.schema_rc 1587719826 "42"
FromDual.maas2.prod2 FromDual.MySQL.backup.schema_duration 1587719826 "4242"
FromDual.maas2.prod2 FromDual.MySQL.backup.schema_size 1587719826 "424242"
_EOF

Remote Syslog Server

Remote Syslog Server

Show passwords hidden with mysql_config_editor

With the mysql_config_editor utility you can obfuscate MySQL passwords stored on disk. Some people have written about how to de-obfuscate these passwords. So this is not new. But one Galera Cluster training participant showed me a much simpler approach I did not know yet:

mysql --login-path=mypath

my_print_defaults --show root
--user=root
--password=Secret!

List of most recent installed packages

Recently we had the problem that one of our systems was suddenly crashing from time to time. To exclude that is related to new kernel or hardware drivers (graphical card?) we wanted to check what packages where recently installed:

grep -e ' install ' -e upgrade dpkg.log.7 dpkg.log.6 dpkg.log.5 dpkg.log.4 dpkg.log.3 dpkg.log.2 dpkg.log.1 dpkg.log | cut -d: -f2-
2021-03-03 10:01:45 upgrade grub2-common:amd64 2.02+dfsg1-20+deb10u3 2.02+dfsg1-20+deb10u4
2021-03-03 10:01:45 upgrade grub-pc:amd64 2.02+dfsg1-20+deb10u3 2.02+dfsg1-20+deb10u4
2021-03-03 10:01:46 upgrade grub-pc-bin:amd64 2.02+dfsg1-20+deb10u3 2.02+dfsg1-20+deb10u4
2021-03-03 10:01:46 upgrade grub-common:amd64 2.02+dfsg1-20+deb10u3 2.02+dfsg1-20+deb10u4
2021-03-11 08:23:50 install memtest86:amd64  4.3.7-3
2021-03-11 08:23:50 install memtest86+:amd64  5.01-3
2021-03-11 11:00:31 install mtr:amd64  0.92-2

Upgrade only specific packages

Sometimes one wants not to upgrade all packages but only some specific ones:

shell> apt-get --only-upgrade install pkg1 pkg2 pkg3

GLIBC version

# mysqld: /lib/x86_64-linux-gnu/libc.so.6: version `GLIBC_2.28' not found (required by mysqld)
shell> ldd --version | grep LIBC
ldd (Ubuntu GLIBC 2.27-3ubuntu1.4) 2.27

Extracting and inspecting packages

Sometimes one wants to look into DEB/RPM packages or one have to extract something (see also I prefer MySQL binary tar balls with Galera...) or needs to rebuild the *.spec file:

shell> ar xv glb-1.0.1.deb
shell> tar tf control.tar.gz

shell> rpm2cpio ./glb-1.0.1-3.1-CentOS6-x86_64.rpm | cpio -idmv

shell> rpmrebuild --spec-only=glb.spec --notest-install --package glb-1.0.1-3.1-CentOS6-x86_64.rpm && cat glb.spec

Taking a node out of Galera Cluster

Sometimes you want to take a node out of a Galera Cluster. We found those 2 methods working:

SQL> SET GLOBAL wsrep_cluster_address='gcomm://';
Query OK, 0 rows affected (3.009 sec)

SQL> SELECT variable_value INTO @uuid
  FROM information_schema.global_status
 WHERE variable_name = 'wsrep_gcomm_uuid';
SQL> SET GLOBAL wsrep_provider_options = CONCAT("evs.evict=", @uuid);
Query OK, 0 rows affected (0.000 sec)

After this you can to for example a backup and then restart the database. Method 2 seems do work smoother.

Make Linux fast again

Make Linux fast again

MariaDB persistent statistics

• Collect: ANALYZE TABLE test.test PERSISTENT FOR ALL;
• Make aware: FLUSH TABLES test.test;
• Store in: mysql.table_stats, mysql.column_stats and mysql.index_stats
• Modify: Analyze on Slave and restore to Master:
  

  shell> mysqldump --user=root --hex-blob --replace --skip-extended-insert --where='db_name="test" AND table_name="test"' mysql table_stats column_stats index_stats | grep '^REPLACE '

Find recently modified files

Find files modified later than this date:

shell> find ./ -newermt 2021-11-07 -type f -print

Change timestamps of files

shell> touch file
shell> ls -la file
-rw-rw-r-- 1 oli oli 4 Jan 23 11:05 file

shell> stat file
  File: file
  Size: 4               Blocks: 8          IO Block: 4096   regular file
Device: 811h/2065d      Inode: 4853116     Links: 1
Access: (0664/-rw-rw-r--)  Uid: ( 1000/     oli)   Gid: ( 1000/     oli)
Access: 2023-01-23 11:06:10.837199954 +0100
Modify: 2023-01-23 11:05:32.401429848 +0100
Change: 2023-01-23 11:05:32.401429848 +0100
 Birth: -

• Access: -a = access time (reading the file)
• Modify: -m = modification time (writing to the file)
• Change: change time (Inode was changed: chmod, chown, mv, ln)

shell> touch -d "3 years ago" file
shell> touch -a -m -t 201512180130 file
shell> touch -c -m -t 201106062145 file

Trick to Simulate a Linux Server with less RAM

Jean-François Gangné described the possibility to simulate a Linux server with less RAM with huge pages in his blog post.

MySQL Repository Key expired

Downloading packages:
warning: /var/cache/yum/x86_64/7/mysql57-community/packages/mysql-community-server-5.7.37-1.el7.x86_64.rpm: Header V4 RSA/SHA256 Signature, key ID 3a79bd29: NOKEY
Retrieving key from file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql


The GPG keys listed for the "MySQL 5.7 Community Server" repository are already installed but they are not correct for this package.
Check that the correct key URLs are configured for this repository.


 Failing package is: mysql-community-server-5.7.37-1.el7.x86_64
 GPG Keys are configured as: file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql

shell> rpm --import https://repo.mysql.com/RPM-GPG-KEY-mysql-2022
shell> yum update

Lengths of lines

If you want to find the lengths of all lines of a document/stream the following command will do what you want:

shell> mysqldump --user=root test test --net-buffer-length=8192 | awk '{print length}' | less

Debian 11 vim Copy/Paste broken

In Debian 11 they have somehow completely broken the Copy/Paste mechanism. To go back to the old working style I used:

: set mouse=r

or:

: set mouse-=a

To make it persistent possibly one of the files /etc/vim/vimrc.local or ~/.vimrc will help.

Audio is broken

Unter (K)ubuntu kommt es alle paar Monate mal vor, dass Audio kaputt geht. Wenn Neustarten des Desktops oder gar des ganzen Servers nicht hilft, hier noch ein paar Tipps/Tools:

Ubuntu verwendet ALSA und PulseAudio.

shell> ps -ef | grep pulseaudio
oli       6519     1  2 10:57 ?        00:00:22 /usr/bin/pulseaudio --start --log-target=syslog

shell> pacat < /dev/random   # Achtung: Macht lauten Lärm!
shell> speaker-test -t wav -c 2 --nloops=2

shell> aplay -l
shell> arecord -f FLOAT_LE -r 192000 -B 1 > test.wav
shell> aplay test.wav

shell> vlc

Oder: Kmixer installieren (kmix) und dann: Lautsprecher → rechte Maustaste → Audio Setup → Test.

Fix:

shell> rm -rf ~/.config/pulse; pulseaudio -k

Quellen:

• https://wiki.ubuntu.com/Audio/Testing
• https://wiki.ubuntu.com/PulseAudio

Powershell cheat sheet

tail -f -n 10:

powershell> Get-Content .\mariadb-error.log -Tail 10 -Wait

ssh tunnel for port forwarding

shell> ssh -p 22 -N -L 127.0.0.1:3306:example.com:33006 joe@example.com

shell> ps -ef | grep ssh
oli  5769  5180  0 16:41 pts/7  00:00:00 ssh -p 12222 -N -L 127.0.0.1:3306:example.com:33006 joe@example.com

shell> ss -tlpn
State   Recv-Q  Send-Q  Local Address:Port  Peer Address:Port                                           
LISTEN  0       128         127.0.0.1:3306       0.0.0.0:*     users:(("ssh",pid=5769,fd=4))            

shell> mariadb --user=brman --host=127.0.0.1 --port=3306 --password

Source: SSH Tunneling: Examples, Command, Server Config - Local Forwarding

Cutting lines out of a huge file

$ grep -n '13:55:55' file.log | sed '1b;$b;d'
4:13:55:55
6:13:55:55
$ sed -i '1,3d;7,$d' file.log

Colorize a tag with tail

$ tail -fF frontend.log | grep --color=always 'ERROR\|$'

Which GLIBC version is required?

Recently we had an interesting new question to answer: Which is the newest MySQL 8.0 server package which can be installed on an old system?

First we had to find out which GLIBC is installed: GLIBC 2.26 (on SLES 15 SP1). The MySQL 8.0.36 RPM packages requires GLIBC 2.28. Then we found that SLES 15 SP3 provides GLIBC 2.28. But an upgrade was not an option.

To understand the GLIBC version schema look here. An excerpt is shown here:

2024-01-31	GLIBC 2.39
2018-08-01	GLIBC 2.28
2017-08-02	GLIBC 2.26
2012-12-25	GLIBC 2.17
2009-05-09	GLIBC 2.10
2008-11-13	GLIBC 2.9
2006-03-06	GLIBC 2.4
2004-12-29	GLIBC 2.3.4
2003-12-01	GLIBC 2.3.3
2003-02-28	GLIBC 2.3.2
2002-10-02	GLIBC 2.3
2002-01-20	GLIBC 2.2.5

And finally we had to check whicht MySQL server package requires which GLIBC version:

rpm --query --requires ./mysql-community-server-8.0.17-1.sl15.x86_64.rpm | grep ^libc.so | sort
...
libc.so.6(GLIBC_2.17)(64bit)

rpm --query --requires ./mysql-community-server-8.0.20-1.sl15.x86_64.rpm | grep ^libc.so | sort
...
libc.so.6(GLIBC_2.17)(64bit)

rpm --query --requires ./mysql-community-server-8.0.25-1.sl15.x86_64.rpm | grep ^libc.so | sort
...
libc.so.6(GLIBC_2.17)(64bit)

rpm --query --requires ./mysql-community-server-8.0.27-1.sl15.x86_64.rpm | grep ^libc.so | sort
...
libc.so.6(GLIBC_2.17)(64bit)

rpm --query --requires ./mysql-community-server-8.0.28-1.sl15.x86_64.rpm | grep ^libc.so | sort
...
libc.so.6(GLIBC_2.28)(64bit)

rpm --query --requires ./mysql-community-server-8.0.30-1.sl15.x86_64.rpm | grep ^libc.so | sort
...
libc.so.6(GLIBC_2.28)(64bit)

So the newest possibly Package for us is MySQL 8.0.27.

Backtrace when MariaDB hangs

When MariaDB server hangs in for example shutdown and does not terminate (we have seen this in Galera Cluster) then the developers want to see a gdb backtrace of the MariaDB server. This should be done as follows:

shell> sudo gdb --batch --eval-command="thread apply all backtrace" /usr/sbin/mariadbd $(pgrep -xn mariadbd) > /tmp/mariadbd_bt_all_threads.txt
shell> sudo gdb --batch --eval-command="thread apply all backtrace full" /usr/sbin/mariadbd $(pgrep -xn mariadbd) > /tmp/mariadbd_full_bt_all_threads.txt

and attached to the bug report.

Source: How to Produce a Full Stack Trace for mariadbd

tar with pigz

tar -cf - -C /var/lib/mysql . | pv | pigz --processes 8  > /tmp/backup.tar.gz
tar -xf /tmp/backup.tar.gz -C /var/lib/mysql

AppArmor on Ubuntu 24.04 and MySQL 8.0

MySQL 8.0 on Ubuntu 24.04 comes with more strict AppArmor rules. If you relocate the datadir the mysqld process will not start any more. To fix this:

shell> systemctl status apparmor
shell> aa-status
shell> apt install apparmor-profiles apparmor-utils
shell> grep -i -rn -E 'apparmor=.*denied|apparmor=.*allowed' /var/log/sylog /var/log/kern.log
shell> # datadir, log_bin
shell> vi /etc/apparmor.d/usr.sbin.mysqld
shell> cat /etc/apparmor.d/usr.sbin.mysqld | apparmor_parser -a
shell> apparmor_parser -r -T /etc/apparmor.d/usr.sbin.mysqld

Find columns with non-central-european-characters

When converting from latin1 to utf8, utf8mb3 or utf8mb4 or vice versa it would be good to know if we have some non-central-european (non-latin1) charcters. Here is a query to help searching:

SQL> SELECT id, data FROM test
 WHERE (data RLIKE '^[[:ascii:]äöüéêèâàçô]+$') = 0;
+----+------------------------------------------------------------------------------------------------------+
| id | data                                                                                                 |
+----+------------------------------------------------------------------------------------------------------+
|  6 | Spanish: "El veloz murciélago hindú comía feliz cardillo y kiwi" is a popular Spanish equivalent.    |
|  7 |                                                                                                      |
+----+------------------------------------------------------------------------------------------------------+

PS: ú and í ARE latin1 characters. It is just as an example...

Generate rows with SQL means

Generate rows with SQL means. Works since MariaDB 10.2 and MySQL 8.0:

SQL> WITH RECURSIVE sequence AS (
  SELECT 1 AS level
  UNION ALL
  SELECT level + 1 AS value
  FROM sequence
  WHERE sequence.level < 5
)
SELECT level
FROM sequence;
+-------+
| level |
+-------+
|     1 |
|     2 |
|     3 |
|     4 |
|     5 |
+-------+

Source: https://stackoverflow.com/questions/701444/how-do-i-make-a-row-generator...

List absolute path of a file

# ls -la Logo_25wht.gif 
-rw-rw-r-- 1 oli oli 1607 Dec 17 11:55 Logo_25wht.gif
# pwd
/tmp
# readlink -f Logo_25wht.gif 
/tmp/Logo_25wht.gif