Automated encrypted incremental backups with restic to a Synology mount

Synology The Synology devices are easy to setup but the backup applications are not that great. In addition, Synology does not offer SSH key based authentication for non-privileged accounts out of the box. After each upgrade the SSH key setup might be gone and it always feels like a hack. The following text describes how to setup painless, automated, encrypted, incremental backups via CIFS using Restic. The script makes use of passwords stored in the gnome keyring and thus can mount a CIFS aka Samba share without having to store the Synology user password in the /etc/fstab file or in the clear in a local file.

Connecting to MySQL 5.6 using TLSv1 on Ubuntu 20.04.1

Ubuntu 20.04 updated some security policies which has the effect that some connections might not work anymore. This happened for instance with connections to AWS Aurora 5.6 recently. Background AWS Aurora 5.6 only supports TLSv1 for encrypted connection. This TLS protocol version is considered insecure for some time now and has been disabled in Ubuntu 20.04 and MySQL Client 8 respectively. If MySQL configuration permits TLSv1, TLSv1.1, and TLSv1.2, but your host system configuration permits only connections that use TLSv1.

Use an SSH tunnel ending at the host also within a Docker container

There are many use cases for SSH tunnels as they allow accessing resources behind firewalls and other obstacles over a secure channel. Since more and more services are containerized, it makes sense to use SSH tunnels also within the context of containers, especially for testing. Using SSH tunnels within Docker containers would require installing an SSH client and mounting keys. In many cases this is not possible without building a new Docker image which includes the client.

Setup a SSH tunnel in IntelliJ

There seems to be a problem with the older MySQL driver and IntelliJ Ultimate. When I tried to connect to a remote MySQL instance using a tunnel, IntelliJ would refuse to connect. IntelliJ would show an error similar to the following: Cannot connect to a database. Tried three times. The linked online help page was not really helpful. I could create the tunnel manually and verify the settings, all seemed fine.

Demystifying MySQL User Management (Part 1)

Managing user accounts can be a bit confusing, when working with MySQL. The two main commands in that context: CREATE USER and GRANT. Obviously, CREATE user is for adding a new user while GRANT is used for assigning permissions. Pre-requisits: Docker Compose For testing access to a MySQL instance, we can use Docker for running said instance and two different client containers. The docker-compose snipped below orchestrates those three containers and puts them on a shared network.

Percona Xtrabackup – Some General Tips

When you stream Xtrabackup to agzipped tar file, it also contains the information about the binary log position where the server was at when the backup was started. This information is needed to start the lave at the same position again time nice -n 15 xtrabackup --backup --slave-info --safe-slave-backup --stream=tar | gzip -c > /var/backup/percona.dump.tar.gz</code>``` In order to get that position without extracting the archive file, you can use the following command tar -xOf /var/backup/percona.

Lightning fast integration tests with Docker, MySQL and tmpfs

Integration tests that involve database operations requite to tear down and re-initialize a database multiple times. Although most developer machines offer SSD and enough RAM, database initialization can consume a considerable amount of time nevertheless. Docker allows defining volumes that are mounted directly into the memory by using tmpfs. We can utilize this feature to utilize the speed up disk operations as during database imports, by moving the data from the disk into the memory.

Measure Replication Delay with Percona Toolkit Heartbeat (pt-heartbeat)

Replication lag occurs when the slave cannot keep up with the changes being made on the master instance. The reasons for a lag can be network congestion, poor performance on the slave or service interruptions of the slave mechanism, leading to a large pile of work to be catched up by the slave. When you useSHOW SLAVE STATUSyou can monitor the current delay, which is indicated in the Seconds_Behind_Master: 0column. Ideally, this value is zero, but the smallest granularity you get for this delay counter is one second.

Setup AWS MySQL 5.6 Aurora as a Slave for an external Master with SSL

Setting up Aurora as a slave for an external MySQL server that acts as the master is a bit tricky. Of course we want a secured connection. For this reason we need to create client certificates to be used by AWS RDS. The steps below should work for RDS as well. Generate and Sign the Certificates The process is actually simple, but AWS is picky how you generate the certificates. I was using a SHA flag that was accepted by a regular MySQL 5.

Install Innotop from Source

Innotop is a great tool but not included in the current Ubuntu repositories. Here is how you install it manually: # Install perl database interface sudo apt-get install libdbi-perl # Install MySQL and Terminal perl modules sudo cpan Term::ReadKey DBD::mysql # Clone innotop git clone https://github.com/innotop/innotop.git # Enter directory cd innotop # Make perl Makefile.PL # Install sudo make install Then you can run innotop like this innotop --user $ADMIN_USER --password $ADMIN_PASSWORD --host $HOST``` <div class="twttr_buttons"> <div class="twttr_twitter"> <a href="http://twitter.

Grafana and InfluxDB with SSL inside a Docker Container

Self-signed SSL certificates On the host, create a directory for storing the self signed SSL certificates. This directory will be mounted in the Grafana container as well as in the InfluxDB container to /var/ssl. Create the self signed SSL certificates as follows: mkdir -p /docker/ssl cd /docker/ssl/ # Generate a private key openssl genrsa -des3 -out server.key 1024 # Generate CSR openssl req -new -key server.key -out server.csr # Remove password openssl rsa -in server.

Compile Percona Query Playback

Install the prerequisits and clone the repository. sudo apt-get install libtbb-dev libmysqlclient-dev libboost-program-options-dev libboost-thread-dev libboost-regex-dev libboost-system-dev libboost-chrono-dev pkg-config cmake libssl-dev git clone https://github.com/Percona-Lab/query-playback.git cd query-playback/ mkdir build_dir cmake -DCMAKE_BUILD_TYPE=RelWithDebInfo ..``` You might see this error CMake Error: The following variables are used in this project, but they are set to NOTFOUND. Please set them or make sure they are set and tested correctly in the CMake files: MYSQL_LIB linked by target “mysql_client” in directory /home/sproell/git/query-playback/percona_playback/mysql_client

Jupyter docker stacks with a custom user

Jupyter allows to set a custom user instead of**_jovyan_** which is the default for all containers of the [Jupyter Docker Stack][1]. You need to change this user or its UID and GID in order to get the permissions right when you mount a volume from the host into the Jupyter container. The following steps are required: Create an unprivileged user and an asociated group on the host. Here we call the user and the group docker_worker Add your host user to the group.

Unbrick a Netgear WNR3500L V2

It finally happened… After experimenting with alternative [firmware][1] for my WNR3500L router, I uploaded the wrong version and converted the device into a rather expensive paperweight. After some searching, I found thispage, explaining how to revitalize the router by using a USB-TTL converter cable. You can get (e.g. [CP2102 Module Modul USB to TTL Converter)][2] for a few bucks Ebay, but expect a few weeks for the delivery from China. If you follow the tutorial on the OpenRouters page closely, it should work out quite nicely.

Illegal mix of collations: IntelliJ and UTF8mb4

When using variables inside SQL scripts within IntelliJ products (e.g. DataGrip), certain queries will not work because the encodings of the IntelliJ client and the server missmatch. This occurs for instance when you compare variables. A typical error message looks like this: [HY000][1267] Illegal mix of collations (utf8mb4_unicode_520_ci,IMPLICIT) \ and (utf8mb4_general_ci,IMPLICIT) for operation 'like'``` IntelliJ products do not yet support MySQL&#8217;s utf8mb4 character set encodings. The problem occurs when using variables in queries.

Flashing a OnePlus One from CM to LineageOS

As Cyanogen Inc closed down its operations in December 2016, CyanogenMod was affected too and my OnePlus One (OPO) did not receive updates anymore. This is not ideal, as new and old bugs have will remain. For instance did my phone often not reconnect to the 4G network, when a wifi connection was lost, This was very annoying. For this reason, if was about time to upgrade to a new OS: LineageOS.

Verifying Replication Consistency with Percona’s pt-table-checksum

Replication is an important concept for improving database performance and security. In this blog post, I would like to demonstrate how the consistency between a MySQL master and a slave can be verified. We will create two Docker containers, one for the master one for the slave. Installing the Percona Toolkit The Percona Toolkit is a collection of useful utilities, which can be obained for free from the company’s portal. The following commands install the prerequisits, download the package and eventually the package.

Fixing Random Freezes with Ubuntu 16.04 LTS, Intel Skylake and an Nvidia GPU

My Lenovo ThinkCentre m900 (10FHCTO1WW) with an Intel i7-6700 showed weird and random freezes from day 1 when trying to install Mint 18 / Ubuntu 16 with any kernel newer than 3x. After investigating for quite some hours, I gave up and installed an Ubuntu 14.04 LTS on it. The device is certified to it, but the old version did not support all features and even some basic things such as audio did not work.

Predicting Visitors with Facebook Prophet

Facebook open sourced its forecasting tool [Prohpet][1] for time series data. Although forecasting is not a trivial task, the libraries are very easy to use and produce nice results quickly. In this basic blog post, I am going to forecast the visitor statistics based on the historical data I collected with Piwik. Python Prerequisites Install and initialize a new virtual Python environment # Install virtual environments package sudo pip3 install virtualenv # Create a new folder for the project mkdir python-projects cd python-projects/ # Create a new virtual environment virtualenv -p python3 py Install Prophet and its Dependencies Within your new Python virtual environment, install the required dependencies first and then Prophet

Validate Hibernate Search Input with an Analyzer

Stop Words Hibernate Search lets you easily assign an @Analyzer on Fields, which are used to process terms before they are written to the index. An anlyzer can be used for instance for stemming and removing of words which are so frequent that they are insignificant for the results. These are examples for stop words: [“a”, “an”, “and”, “are”, “as”, “at”, “be”, “but”, “by”, “for”, “if”, “in”, “into”, “is”, “it”, “no”, “not”, “of”, “on”, “or”, “such”, “that”, “the”, “their”, “then”, “there”, “these”, “they”, “this”, “to”, “was”, “will”, “with”]``` It is a common technique, to split input search terms into single keywords and use these keywords for combining a complex queries over several fields.

Using Hibernate Search with Spring Boot

Spring Boot is a framework, that makes it much easier to develop Spring based applications, by following a convention over configuration principle (while in contrast Spring critics claim that the framework’s principle is rather configuration over everything). In this article, I am going to provide an example how to achieve the following: Create a simple Web application based on Spring Boot Persist and access data with Hibernate Make it searchable with Hibernate Search (Lucine) I use Eclipse with a Gradle plugin for convenience.

Hibernate Search and Spring Boot: Building Bridges

Hibernate Search is a very convenient way for storing database content in a Lucine index and add fulltext search capabilities to data driven projects simply by annotating classes. It can be easily integrated into Spring Boot applications and as long as only the basic features are used, it works out of the box. The fun starts when the Autoconfiguration cannot find out how to properly configure things automatically, then it gets tricky quite quickly.

Deploying MySQL in a Local Development Environment

Installing MySQL via apt-get is a simple task, but the migration between different MySQL versions requires planning and testing. Thus installing one central instance of the database system might not be suitable, when the version of MySQL or project specific settings should be switched quickly without interfering with other applications. Using one central instance can quickly become cumbersome. In this article, I will describe how any number of MySQL instances can be stored and executed from within a user’s home directory.

Flashing a NanoPc T3 with DietPi

The NanoPc T3 is a 64 bit octa core single board computer, quite similar to the famous Raspberry Pi boards. It is also often referred to as NanoPi T3 as well. Hardware Specification The single board computer has eight cores with up to 1.4GhZ and 1 GB of DDR3 RAM. It has a lot of nice interfaces, the specification below is taken from [here][1]. SoC: Samsung S5P6818 Octa-Core Cortex-A53, 400M Hz - 1.

Switch the Git Clone Protocol from HTTPS to SSH

Gitlab offers several options for interacting with remote repositories: git, http, https and ssh. The first option – git – is the native transport protocol and does not encrypt the traffic. The same applies for http, rendering https and ssh the only feasible protocols if you commit and retrieve data via insecure networks. Ssh and https are also both available via the web interfaces of Github and Gitlab. In both systems you can simply copy and paste the clone URLs including the protocol.