Category Archives: IT / Computers

DBeaver connecting to MySQL in Namecheap Shared Hosting

Posted on by
Reply

Namecheap already provided instructions to connect MySQL Workbench client for its shared hosting, which involves SSH-tunneling because they disallowed direct MySQL connection out of security concerns.

So here’s basically the logistics:

  1. SSH to your namecheap hostname (can use your domain name) at SSH port 21098
  2. Tunnel listens to Port 5522 and forward it to localhost (the client itself) at MySQL Port 3306
  3. Instead of connecting directly to the {namecheap shared hosting server}:3306, connect to the localhost:3306

It’s a little confusing on how to do it on DBeaver because “Advanced settings” is hidden by default which you will need. The name ‘local client’ (source) vs ‘remote’ (destination) in the dialog box is confusing. It’s actually equivalent to 

ssh -L ["Local host":]"Local port":"Remote host":"Remote port"
ssh -L [bind_address:]port:host:hostport

bind_address can be left blank. If you are paranoid and don’t want other machines to use your current MySQL client machine as a gateway (they tunnel into your machine to use the tunnel you are currently establishing), set (aka bind) it to localhost, or you can bind it to the client’s network adapter’s IP which you want to allow machines on a trusted network to use this MySQL client computer as a gateway.

For some reason (I suspect it’s IPv6), “Remote host” needs to be set to the loopback adapter 127.0.0.1 (cannot use the special hostname ‘localhost‘).

Remember MySQL’s username and password is the special database-only login credentials you created at cPanel.

Loading

Text manipulation idioms in linux

Posted on by
Reply 
awk: select columns
sed: stream editor (operations like select, substitute, add/delete lines, modify)
sed expressions can be separated by ";"
sed can substitute all occurrences with 'g' modified at the end: 's/(find)/(replace)/g'

# https://unix.stackexchange.com/questions/92187/setting-ifs-for-a-single-statement

# arg I/O
$@: unpack all input args
$*: join all inputs as ONE arg, separated by FIRST character of IFS (empty space if unspecified)

# Remember the double quotes around "$*" or "$array[*]" usages or else IFS won't function

array[@]: entire array
${array[@]}: unpacks entire array into MULTIPLE arguments
${array[*]}: join entire array into ONE argument separated by FIRST character of IFS (defaults to an empty space if unspecified)
( IFS=$'\n'; echo "${my_array[*]}" )

${#str}: length of string
${#array[@]}: length of array
${#array[@]:start:after_stop}: select array[start] ... array[after_stop-1]

${str:="my_string"}: initializes variable str with "my_string" (useful for side-effect)

$(str##my_pattern}: delete front matching my_pattern
${str%%my_pattern}: deletes tail matching my_pattern (can use one % instead)
$(str%?}: delete last character (the my_pattern is a single character wildcard "?")

$( whatever_command ): captures stdout created by running whatever_command
( $str ): tokenize to string array, governed by IFS (specify delimiter)
( $( whatever_command ) ): combines the two operations above: capture stdout from command and tokenize the results

# https://unix.stackexchange.com/questions/92187/setting-ifs-for-a-single-statement
function strjoin { local IFS="$1"; shift; echo "$*"; }

Loading

Auto mount USB drives

Posted on by
2

Raspbian OS (Raspberry Pi) do not mount USB drives automatically out of the box.

I’m pretty annoyed by the lack of easy to use packages by 2021 and I still have to do it myself with the instructions here: https://github.com/avanc/mopidy-vintage/wiki/Automount-USB-sticks

These are cookbook instructions, but I’ll add some insights to what each component means so it’s easier to remember the steps.

At top level, to auto-detect and mount USB drives, we need the following components

  • udev: analogous to what happens behind device manager, it keeps track of and updates devices as they are connected and disconnected immediately. Need to register USB sticks by adding a event handler, which triggers a systemd service (see below)
  • systemd: analogous to Windows’ services. Need to register the service by stating what commands it will call on start (mostly mounting) and cleanup (mostly unmounting)
  • automount script: it’s a user defined script that abstracts most of the hard work detecting the partitions on the USB stick, assign the mount point names, and mount them
# Register udev event handler (rules)
# /etc/udev/rules.d/usbstick.rules

ACTION=="add", KERNEL=="sd[a-z][0-9]", TAG+="systemd", ENV{SYSTEMD_WANTS}="usbstick-handler@%k"

# It triggers a systemd call to "usbstick-handler@" service registered under /etc/systemd/system/
# Register systemd service
# /etc/systemd/system/usbstick-handler@.service
# (Note: instructions used /lib instead of /etc. It's better to add it as /etc as this is manually registered as user-defined service rather than from a package)

[Unit]
Description=Mount USB sticks
BindsTo=dev-%i.device
After=dev-%i.device

[Service]
Type=oneshot
RemainAfterExit=yes
ExecStart=/usr/local/bin/automount %I
ExecStop=/usr/bin/pumount /dev/%I

# %I is the USB stick's device name under /dev, usually sda
# Abstracted the logic of determining the mount point name and mounting to 'automount' (see below)

Create the file /usr/local/bin/automount and give it execution permission: chmod +x /usr/local/bin/automount 

#!/bin/bash

# $1 (first argument) is usually "sda" (supposedly USB stick device name) seen from %I in the systemd service commands
PART=$1

# Within the "sda" (USB stick device of interest), extract the partition labels (if applicable) from lsblk command. The first column (name) is dropped
FS_LABEL=`lsblk -o name,label | grep ${PART} | awk '{print $2}'`

# Decide the mount point name {partition label}_{partition name}
# e.g. MS-DOS_sda1
tokens=($FS_LABEL)
tokens+=($PART)
MOUNT_LABEL=$(IFS='_'; echo "${tokens[*]}")
# Using string array makes it easier to drop the prefix if there's no {partition label}
# Bash use IFS to specify separators for listing all elements of the array

# Suggestion: drop --sync for faster USB access (if you can umount properly)
/usr/bin/pmount --umask 000 --noatime -w --sync /dev/${PART} /media/${MOUNT_LABEL}

This automount script is adapted from https://raspberrypi.stackexchange.com/questions/66169/auto-mount-usb-stick-on-plug-in-without-uuid with my improvements.

Loading