Securing a database – what does that actually mean?
Since I am writing this review also for non-database specialists, here is a brief explanation: Online shops, inventory management systems, and even simple websites need to store data somewhere. They often do this in a database. You can picture a database like a (large) Excel spreadsheet with many sheets or tabs—except that a database can manage 1,000 or even 100,000 times more data than an Excel spreadsheet. An Excel sheet hits its absolute limit at around 1 million rows and runs at full throttle. A database server, on the other hand, is just getting into second gear and is still far from reaching its limits.
When you secure a database or a database server, you pursue two goals:
1. Data Security
Even if the database server crashes and cannot be revived, the data should still be preserved and not lost.
2. Availability
If the database server crashes, users should ideally not even notice and be able to continue working seamlessly in the system.
But how can you achieve such so-called high availability? Through backup and replication. Simply put, you regularly (e.g., once a day) create a backup copy of the entire database and store it on another server. Why on another server? Because it’s possible that the database server might completely crash and no longer be accessible. If the backup copy is stored in a physically separate location, such as another data center, it usually remains intact even if the database server crashes.
For databases that receive hundreds or thousands of changes each day—such as online shops—a daily backup is not sufficient. If the server crashes half an hour before the next backup, you could lose almost a full day’s worth of data! That could be tens of thousands of records. To avoid this, you also implement replication.
When two are in agreement ... Topic: Database Replication
From Theory to Practice
My task in the past few days was to set up the database servers (essentially: install the required programs) and configure them so that regular data backups are carried out automatically and Server 2 replicates Server 1.
The regular data backups are performed once a day using scheduled commands (so-called Cron jobs) and are kept for several days for easy access. The Linux tool logrotate takes care of deleting the old backups. (I think I’ll write a tutorial in German about this someday 🙂 ).
Since things have to happen quickly in case of an error, I also wrote some small command line tools that can spin up a new database slave server from an existing backup within a few minutes. For a small database backup of about 2 gigabytes, this only takes about 5 minutes. It’s not yet a value that the industry would call “high availability,” but it’s certainly better than having to type in a series of commands manually 🙂
But enough about databases …
Hello again, German Cash Register Security Ordinance! 👋
This week, a developer of a point-of-sale system approached me because he needs personnel support in maintaining the interface to the tax authorities (DSFinV-K). The DSFinV-K is closely linked to the Cash Register Security Ordinance (KassenSichV). While the KassenSichV is, among other things, about ensuring that all cash register transactions are recorded without gaps and cryptographically signed to prevent manipulation, the DSFinV-K is concerned with presenting cash register transactions and master data in a standardized data format.
To get an overview of the data format, I first had to work through 130 A4 pages of DSFinV-K documentation. It sounds like a lot at first glance. However, if you are used to working through data format descriptions and technical documentation, it’s not that bad 🙂 Besides, I had already familiarized myself with this documentation a few months ago for another project.
My task will be to implement the export of all cash register transactions from a MySQL database into the DSFinV-K format. A very exciting project that I am already looking forward to.
How many luggage locker systems are there in Germany, actually?
Unfortunately, I can’t answer this question either – but I do know where to find them: For the past few months, I’ve had the opportunity to work on the project lockport.online. Lockport.online was originally intended as a booking portal for a few bicycle lockers (or bicycle garages) in Chemnitz. However, behind lockport.online stands the locker manufacturer Locktec, whose locker systems and bicycle garages can be found at several hundred locations worldwide.
Last week, we began adding luggage lockers and bicycle lockers from other locations to the website. As of today, 16 different locations in Germany are already displayed on the map. At some of these, online reservations are possible.
We are currently working on improving the sign-in and registration process. We will also make the website a bit more communicative so users can find their way around more easily. Suggestions are always welcome.
And what about next week?
The topics of database replication and DSFinV-K will continue to accompany me next week.
Since the DSFinV-K format consists of several CSV files, I will probably write a small tool that creates corresponding model classes for Java from existing CSV sample data. Why? 1. I like to automate things 🙂 and 2. I could have used such a tool many times already.
