Category: support

I HATE Optimum!!

There’s many reasons cable companies love local monopolies. One is that no matter how poor their service is, customers have no alternative.

Like many in the US I have only one ISP, Internet service provider, that can provide reasonable residential Internet upload and download speeds, e.g. >=200Mbps down, >=30Mbps up. That provider is Optimum. I’m not a gamer, don’t stream 4K, and almost never have more than one device streaming at a time so yes, >= 200Mbps down is reasonable for me.

To run this website from my home server I need the Optimum provided Altice Gateway (a combined cable modem and router) to have its router component set to bridge mode. My own router’s WAN port then gets a public IP address and that way I control NAT to the services I need. Optimum doesn’t provide access to the bridge mode setting for their gateway’s router so tech support must be contacted to have it set to bridge mode.

At this point I’ve had three gateways from Optimum. The first two gateways it took one or two days contact with support each time time to get the correct settings established. It was necessary to contact support, explain the router setting that needed to be made, and then follow up until it was actually working.

This third gateway, setup and config were nearly automatic, except of course, setting the router to bridge mode. As usual I contacted support to have the router set to bridge mode. It worked! My router got a public IP on the WAN port and everything was good to go. Then less than a week into the working setup it was no longer working. My router was getting a private IP on the WAN port. Checking my Optimum gateway on their website showed the router portion of the gateway had the LAN enabled and an address on that private LAN was what my WAN port was getting. Router working.

Call/text with support for more than three weeks trying to get them to set it back to the configuration I needed, the configuration it had been in for a short while after initial setup. Support even told me things like the problem was my router. The various techs I spoke/texted with continually blamed my router even though it was clear my router was getting a private IP address from the LAN configured in the Gateway’s router settings.

Every tech I spoke/texted with, their technical understanding of networking and their spoken English competency was not at the level needed for the work. It was clear basic networking wasn’t understood and also they would mistake words like “pass thru,” as “password.” I used “pass thru” while trying to explain (?explain? to tech support) what bridge mode meant. Getting them to recognize and acknowledge the problem was impossible, so I gave up. I decided to buy a cable modem to have both components needed to replace Optimum’s gateway.

Even that was fraught with concern for me. Given the problems I’ve had with knowledgeable support I wanted to be certain the modem I buy could be activated by Optimum. I went to an Optimum store to ask about modems. They directed me to a website, pickmymodem.com, to identify a compatible modem. I pointed out it wasn’t their website and asked if the modem didn’t work would Optimum simply say, “not our website, too bad,” even though they had directed me there? Silence from across the counter.

Pickmymodem.com specifically states DOCSIS 3.1 modems are not recommended for Optimum.



Next I contacted Optimum’s modem activation number to ask about modem compatibility. On calling, the voice menu doesn’t offer an option to speak to “modem activation” even though the website specifically identifies that number for modem activation. So of course one must guess the appropriate menu option, talk to a bot that tries to “solve the problem” without connecting you to a person, then, after getting over that, eventually speak with someone.

When I finally spoke to someone they told me a DOCSIS 3.0 compatible modem was needed but had no information about specific modems and would not confirm whether a particular modem would work. I pointed out Optimum’s website says a DOCSIS 3.1 compatible modem is needed and DOCSIS 2.0 and 3.0 modems are not allowed on their network. Which information is correct, what you’re telling me, DOCSIS 3.0, or what the website shows, DOCSIS 3.1? Just silence from them, no answer.

Optimum couldn’t/wouldn’t provide information about compatible modems, recommended a site they didn’t control to identify a modem, and between the website they recommended, their website and the Optimum support that I spoke with, gave conflicting information about what would work.

I gave up and just bought a DOCSIS 3.1 modem. The modem arrives and I’m amazed, amazed I say, activation went without a hitch! But this is Optimum so, of course, the trouble isn’t over.

When we moved here about six years ago Optimum was and still is the only service provider for my address. Service was started at 200Mbps and that has was never realized. Slightly above 100Mbps was the typical speed. The first gateway eventually started failing and Optimum replaced it. A little over a month ago Internet connectivity became very poor on the second gateway. Optimum sent me a third gateway, bumped speed up to 300Mbps, and reduced the monthly service charge by $5. Nice, right?

Of course problems don’t stop there. I never saw close to 300Mbps. The best was usually around 150Mbps.

Throughout all this I’ve been on their website many times and note new customers are being offered 300Mbps for $40/month. I’m paying more than 3x that, even after the $5/month discount, for the same speed.

I call the retention department to ask to get my bill reduced to something in the $40/month range. Nope, no can do, the $5 was all I would get. What they would do was bump the speed to 1Gps and reduce the bill for that to $95/month for three years. I doubted their ability to deliver the service given their history but that was the only way to get the bill below $100/month so I accepted the offer.

Next problem to resolve, I’ve supposedly got 1Gbps now but every speed test I’ve done until this morning tops out just below 200Gbps. This morning the speed tests across different test sites range from a low of 245Mbps to a high of 480Mbps. Better than I have had but still not even 50% of what’s supposedly being delivered.

I’m going to try and get the speed they’re supposedly giving me but I expect it won’t happen. They’ll tell me it is my equipment that’s the problem, I’m sure.

I expect the speed issue won’t be resolved and I’ll still hate Optimum after I try and get to the speed they say they’re providing. Have to wait see.

UI Design That Makes a Difference

Some things really are just easier

I’ve been a long time open source user. Some advocates disparage closed source, particularly Microsoft. That’s not productive IMO. Compare feature and function. If it has the feature(s) and function(s) that you need then how well are they implemented and how easy are they use? Those are the crucial considerations.

Microsoft’s Office Suite has many features that most will never use, much less understand how to use (myself included). There are other features that are obvious once they’re experienced and their superior usability is obvious.

One example of that I just experienced was filtering a list of values formatted as currency. Some values had more than two decimals, they were from a formula. All needed to display as currency. So all needed to display two decimal places. As a result different values might display the same. e.g. 0.445 thru 0.454 all display as $0.45.

Excel and Calc displayed the values as currency with two decimals just fine. However the filter list in Excel shows the values in currency format, the filter list in Calc shows the values without the applied formatting. Not nearly as useful.

In Excel different values that format to the same display value show up only once in the filter list and format matches the applied display format. The filter filters the rows based on the display value. As a result anything from 0.445 thru 0.454 displays as $0.45. The filter list displays $0.45 only once no matter how many different values it actually represents. And when $0.45 is selected from the filter list all rows with values from 0.445 thru 0.454 are filtered.

In Calc, not so nice. Any values 0.445 thru 0.454 display as $0.45 using the applied currency format. However, the filter list displays the value without formatting. As a result, to filter all values that display as $0.45, every value in the list from 0.445 thru 0.454 must be selected to filter the list so that only display values of $0.45 are displayed.

There are other issues too. Check out the image for more.

Retiring some hardware

…when a computer’s been around too long

Time to retire some old tech. That display is a whopping 15″ diagonal. Resolution was limited. Only used it for a terminal for a server these last six years or so. And this is it under my arm on the way to the dumpster.

Right after the monitor, the old server was carried out to rubbish.

BEFORE delivering to rubbish I made sure to wipe the HD with DBAN, Darik’s Boot and Nuke. Have relied on it for years.

The computer’s manufacturing date stamp was 082208. Didn’t think to take a photo. It was a Dell OptiPlex 330 SFF, Pentium Dual Core E2160 1.8GHz, 4GiB RAM, 90 GB HD. They looked like this.

I got it in 2015. It had been replaced during a customer hardware upgrade then sat on the shelf unpowered for about a year before I joined that office. On hardware clean-out day it was in a pile to take home or put in the dumpster.

It became my boba.org server sometime in 2015 and served that function until December 2022.

Six years of service and then it sat on the shelf for a year. Then eight years hosting boba.org. Fourteen years of service is a LONG life for a computer!

The replacement “server” is an old laptop, old, but it’s new enough it doesn’t have an Ethernet port. I got a USB Ethernet adapter, Realtek Semiconductor Corp. RTL8153 Gigabit Ethernet Adapter, and plugged a cable in. Better performance than WiFi.

Hardware is several steps above the old server too. Intel Core i3-5015U CPU @ 2.10GHz, 6GiB RAM, 320 GB HD (I should replace with SSD). Date of manufacture isn’t as clear. Maybe late 2015 early 2016.

The CPU Benchmark comparison of the two processors, Intel Core i3-5015U vs Intel Pentium E2160, shows clear differences in processing power.

Now that the new server is up, well has been for a few months but I didn’t want to add new services until I got secondary DNS running, its time to add features and services on the network.

Powershell – love it / hate it

Sometimes it’s hard for me to wrap my head around things.

Powershell makes so many things easier than before it existed. At least for me. I’m not a programmer but simple commands piped one to another, like bash in Linux, I can get a lot done.

One of the “things” I need to get done is checking how many computers got a program installed. Because of the environment I’m in and the program itself, there’s no GPO based install for an MSI and there’s no third party tool based install. This stumped me for a while until I came up with the idea of using a startup script for the install.

Another challenge, powershell scripting is disabled. However I learned from “PowerShell Security” by Michael Pietroforte and Wolfgang Sommergut that powershell can be called within a .bat using Powershell Invoke-Command -ScriptBlock {} even if powershell is disabled by policy. So I wrote a start up script that relied on .bat files that had Powershell Invoke-Command -ScriptBlock {} in them to run the program install. The -ScriptBlock {} checked first if the dependencies were installed, installed them if not, then checked if the desired program was installed and installed it if not. It also wrote a log file for each pc named as “progname_<hostname>.txt” and appended to the file with each restart.

The startup script wasn’t running reliably every time a pc booted. Seemed to be NIC initialization or network initialization related. In any case, the pcs that were to be installed were listed in an AD group. The pcs that had run the startup script output that info into a file named “progname_<hostname>.txt”. One way to see which of the pcs had not gotten the install was by comparing the members of the AD group, the computer names, to the <hostname> portion of the log file names that were being created. Computers from the group without a corresponding file hadn’t gotten installed.

Easy, right? Get the list of computers to install with Get-ADGroupMember and compare that list to the <hostname> portion of the log files. How to get only the <hostname> portion? Get-ChildItem makes it easy to get the list of file names. But then need to parse it to get only the <hostname> part. Simple in a spreadsheet but I really wanted to get a listing of only the <hostname> without having to take any other steps.

I knew I needed to look at the Name portion of the file name, handle it as a string, chop off the “progname_”, and drop the “.txt” portion. But how to do that? After what seemed like way to much searching and experimenting I finally came up with…

$( Foreach ( $name in $(Get-ChildItem progname* -Name) ) { $name.split('_')[1].split('.')[0] } ) | Sort

The first .split('_')[1] lops off the common part of the filename that’s there only to identify the program the log is being created for, “progname_”, and keeps the rest for the second split(). The next split(), .split('.')[0], cuts off the file extension, .txt, and keeps only the part that precedes it. And so the output is only the hostname portion of the filename that the startup script has created.

Compare that list to the list from Get-ADGroupMember and, voila, I know which of the targeted pcs have and have not had the program installed without doing any extra processing to trim the file names. Simple enough, but for some reason it took me a while to see how to handle the file names as strings and parse them the way I needed.

Ubuntu and Bluetooth headphones

You don’t know what you don’t know.

It’s impossible to be expert at everything. Or even good at everything. One of the things that Ubuntu has frustrated me over is headphones. I’ve used wired headphones and they’ve worked great. But of course I’m tethered to the computer. I’ve used wireless headphones and they too have worked great. But I’m miffed that a USB port has to be dedicated to their use. Why should a USB port be lost to use headphones when the pc has built-in Bluetooth? Why can’t I just use Bluetooth headphones that I use with my phone and keep all my ports open for other things.

Why, because every attempt to use Bluetooth headphones has failed. Used as a headset they work fine, as headphones, not so much. Either the microphone hasn’t been picked up or the audio is unintelligible or non existent. And I know it isn’t the headphones because every set I’ve had that doesn’t work with Ubuntu has worked great with Android phones and with Windows after installing the headphone’s Windows program.

I’ve tried digging into the details of how to set up audio on Ubuntu to get Bluetooth headphones supported. And doing so, I’ve buggered up test systems to the point I needed to reinstall the OS to get sound working again. I obviously didn’t understand it well enough to resolve the issue. Even so, every once in a while I try again to make it work.

Recently, I came across the solution! Thanks to Silvian Cretu and his post Linux and Bluetooth headphones.

The post touches on many of the things I’ve tinkered with trying to make Bluetooth headphones work. The section, “Solution 2: Replacing Pulseaudio with PipeWire”, in Silvian’s post provides the recipe that makes it work. If you’re on Ubuntu 20.04 and are frustrated trying to make Bluetooth headphones work then head over to Linux and Bluetooth headphones and see if that is the recipe for you too.

Tracking Things, SO MANY THINGS, Which Are the Important Things?

Don’t get overwhelmed.

Digital devices, for discussion the range from smartphones to computers and devices making up the networks they attach to, offer so much information for monitoring health and diagnosing failures.

To maintain the health of that cloud of devices it’s good to know what’s going on. What to monitor. And by the same token, good to monitor things that affect your experience so the provider can be shown when it’s their problem.

For home Internet users the big things are usually the reliability and speed of the Internet connection. If it’s fast but down a lot that’s no good. And if it’s up and performance is good is it actually performing to spec? Are you getting what you’re paying for?

Only as an exercise in curiosity, wondered how often my public IP address changed, and how quickly the log would grow. Have been tracking since May, 2014 and have 11,441 lines in the log. It’s only grown to 670K in that time. Had 129 different public addresses and top five are 2,267, 1,681, 1,176, and 702 occurrences. More than half the instances.

Mostly just trivia. Having the log did help me discover one of the temporary IPs that I got in Flushing was on some black lists. When that happened I couldn’t log in to my (ISC)2 account. Once troubleshoot I was able to get it removed from the blacklist and was again able to get to (ISC)2 when I got that IP.

More immediate, is the Internet performance I contracted for being delivered? In my case it certainly seems it isn’t being delivered.

A typical recent week of service from my ISP. Any not green is bad :-/. There’s quite a bit of it.

Better times :-). Start of November, 2020.

That’s examples of some things to track. One seemingly more immediately useful than the other. There’s so many more. Which are important for security? Authenticate by location, time of day, second factor, log file access (hierarchy of criticality). Web browsing?

Need to ask and answer what’s critical, confidential, who should have access and access paths allowed.

Got vsftpd?

The path from “need a few files” to providing any time you like self service.

I tend to have computer components and a few spare computers hanging around. Both because I haven’t got hit with Marie Kondo fever (I’m not really bad) and because I help my kids with equipment selection, sometimes purchase, and benefit from getting their leave behinds to experiment with.

In this case one son had upgraded so I got the old laptop. It needed some work to be useful, badly damaged digitizer. He also wanted files from the hard drive but didn’t have opportunity to get them before leaving me the pc.

I replaced the digitizer and swapped out the hard drive with a loose one I had around so I could use the pc. Put the original drive in an external USB3 enclosure I had, labeled it not to erase, and set it aside.

Then said son asked for four files from the old drive. No problem I thought. Plug the drive into the USB port of my laptop, read them off the drive and send. Nope.

This son is one I’ve gotten to use Linux on several systems. I’d set up Linux for him on this system and used the default partition method at the time, LVM. Couldn’t read the drive. My system, using the current default, ZFS, didn’t have the ability to mount the drive.

Here’s one of the reasons I find Linux to be easy to use, all I needed to do was install LVM on my system and reboot. Presto I could read the external drive. It now automatically mounts when plugged in to USB. And the ZFS install of my system wasn’t affected at all.

Now try to read his files for him. Nope. He had been traveling internationally so I’d set up an encrypted home directory for him. Fortunately I’d kept the encryption passphrase in my password safe and was able to mount the encrypted home directory. I still wasn’t getting files in the clear though. It seemed related to the fact the drive was no longer the boot drive. Went down that rabbit hole for a bit and seemed to be making progress. Finally though, to get him the files, I just asked him if he recalled his login password. He did.

Booted the old pc and selected the external HD to boot from, it went right to login screen, enter password, and I’m logged in to the old system. Another Linux advantage, take an original host drive, plug it into USB on another pc, select that drive as boot source, and Linux boots without complaint.

I sent him the files he wanted. Then I thought to send him a list of all files in his home directory. After all he might want others and just not recall their names. Sure enough, he wanted a few more after getting the list.

Now I’m thinking, if he wants more files, then more work for me. What if, instead, he can get the files on his own any time he likes? Could I set up an ftp site he could connect to and get files whenever he wished?

This is where vsftpd finally enters the picture. My plan was boot from the old hard drive using a spare pc, make an ftp site that used an encrypted connection so not even username/password are sent in the clear and provide him the connection information.

vsftpd is an easy set up. Run the installation and it accepts anonymous connections by default. Didn’t want anonymous though and wanted connection to go to his home directory. Read the man, linux.die.net is my favorite man source, search for others’ descriptions of how to set up a credentialed, encrypted connection, and keep hacking at it until it worked.

The thing that really stymied me was the obscure failure message when vsftpd was failing to start after some of the config changes I made. I couldn’t find a parameter to boost the detail of the logging and was left with only “status=2/INVALIDARGUMENT” to try and figure out what parameter was the problem. Fortunately I came across Why my vsftp service can’t start?. It offered the tip to run /usr/sbin/vsftpd manually from the command prompt and the specific issue might be revealed. I tried, the problematic option was revealed, changed the option and presto, working vsftpd using TLSV1 for connections!

For your interest, here’s my working vsftpd.conf

anonymous_enable=NO
local_enable=YES
chroot_local_user=YES
allow_writeable_chroot=YES
listen=NO
listen_ipv6=YES
dirmessage_enable=YES
use_localtime=YES
xferlog_enable=YES
user_sub_token=$USER
local_root=/home/$USER
rsa_cert_file=/etc/letsencrypt/live/fullchain16.pem
rsa_private_key_file=/etc/letsencrypt/live/privkey16.pem
ssl_enable=YES
allow_anon_ssl=NO
force_local_data_ssl=YES
force_local_logins_ssl=YES
ssl_tlsv1=YES
ssl_sslv2=NO
ssl_sslv3=NO
require_ssl_reuse=YES
ssl_ciphers=HIGH
pasv_min_port=xxxxx
pasv_max_port=yyyyy
pam_service_name=vsftpd
implicit_ssl=NO

Security – It needs to make sense

Don’t make things unnecessarily difficult and then say “it’s for security”.

At this point I hope most everyone knows basics about online security like don’t reuse passwords, use unique passwords at each site, use complex passwords, use multi-factor authentication when available, and use a password safe. These are all components that rely on the user (yes, in a corporate environment these things should be controlled by the IT department). The user though is only part of the security equation.

The website owner also needs to contribute to a secure online experience. And I submit that making access and credential requirements proportional to the criticality of the information available in the account is part of that responsibility. After all, if your credential isn’t easy to use or needs to be changed because of some requirement that isn’t an issue for any other website it doesn’t exactly make you want to use the website or promote it to family and friends.

This is a tale of a website which, IMHO, has account credential practices that are unnecessary and antithetical to positive user experience. Also they are not proportional to the value of what’s being protected and are uniquely cumbersome compared to any other websites I have credentials at.

I have a credit card. Surprised? It has a rewards program. The rewards program website is separate from the credit card company website. It is a third party provider of credit card reward program services, CU Rewards. And it has two “security features” that to me are absolutely abhorrent.

First is its CAPTCHA to prove I’m not a robot. I’m not against CAPTCHAs. I don’t mind them and they’re on many sites that I use. However the CU Rewards website CAPTCHA is one that regularly requires me to complete two, three, or more “click on all the …” CAPTCHA challenges to prove I’m real.

C’mon, really? Every other website I use that has CAPTCHA, it takes one challenge before it decides I’m not a robot. CU Rewards, never only one challenge. Why?

The images are lower resolution than most but certainly not the lowest. Why make access so difficult when I’ve already provided my credentials? What’s being protected? My retirement savings, no. My bank account with it’s wad of cash, no. My medical record with all that PHI (Protected Health Information), no. What’s being protected is my ability to order “free” stuff that is available on my credit card rewards program. This degree of difficulty to gain access does not make sense. It is not at all proportional to the value of what’s being protected.

The second issue with this website’s security is credential creation. I do use a password safe. I do use complex passwords. I do not reuse passwords. I have user accounts with banks, investment companies, retirement accounts, schools, job boards, etc. The list goes on and on. If my credential needs to change at any one of these websites, even those that require a user name separate from my email address, what needs to change is my password. Nothing else.

Imagine if you will a financial institution issuing you a credit card and you create a user credential to have online access to your information. Eventually they send you a new card. Maybe you lost your card, maybe you suspect some fraud and got it replaced, maybe it was about to expire and the replacement card was sent as a routine part of the account management process. Or how about an investment account where you’ve invested in stocks and index funds through your employer’s savings plan but have left the employer, managed the investments yourself for a while and then turned over fund management to an investment management company.

How many NEW user ids do you imagine needing to create for the above scenarios? Maybe a new one each time the credit card company issues you a new card and, for the investments, a new one when leaving the employer and then another new one on giving the investment management company the responsibility to manage the investments. Seems crazy right? You’re still you. The company you’re doing business with is still the same credit card company or same financial services company. You wouldn’t expect to need to create a new user name and password for any of these changes, would you? You’ve probably had some of these changes happen and not had to create new login credentials.

And yet CU Rewards requires a new login id be created whenever a new credit card is issued even though the card is from the same financial institution and is replacing your previous card. After being issued the new card your account information is still accessible at the financial institution using the same login credentials you’ve always used. The new card continues to accumulate points on the same CU Rewards program even automatically transferring the points balance from your old card to the new one. However CU Rewards won’t give you access to your account without creating a new user name and password?!

This, in my opinion, is absolutely TERRIBLE security design. It creates unnecessary barriers to the user and is not at all proportional to the value of what’s being protected. The requirement is 100% unique among all my other online credentials which is an indicator nobody else thinks it is a good process either. There is not a single other business be it bank, credit card company, finance company, mortgage, investment, insurance, medical records, or online retailers that requires a new login credential be created when a new credit card is issued.

CU Rewards your credential practices suck. You need to change them to stop sucking.

Ubuntu server upgrade 16.04 to 18.04 (20.04 pending)

Virtualize, document, and test. The surest way to upgrade success.

For years my server has been running my personal websites and other services without a hitch. It was Ubuntu 16.04. More than four years old at this point. Only a year left on the 16.04 support schedule. Plus 20.04 is out. Time to move to the latest platform without rushing rather than make the transition with support ended or time running out.

With the above in mind I decided to upgrade my 16.04.6 server to 20.04 and get another five years of support on deck. I’m half way there, at 18.04.4, and hovering for the next little while before the bump up to 20.04. The pause is because of a behavior of do-release-upgrade that I learned about while planning and testing the upgrade.

It turns out that do-release-upgrade won’t actually run the upgrade until a version’s first point release is out. A switch, -d, must be used to override that. Right now 20.04 is just that, 20.04. Once it’s 20.04.1 the upgrade will run without the switch. Per “How to upgrade from Ubuntu 18.04 LTS to 20.04 LTS today” the switch, which is intended to enable upgrading to a development release, does the upgrade to 20.04 because it is released.

I’m interested to try out the VPN that is in 20.04, WireGuard, so may try the -d before 20.04.1 gets here. In the meantime let me tell you about the fun I had with the upgrade.

First, as you should always see in any story about upgrade, backup! I did, several different ways. Mostly as experiments to see if I want to change how I’m doing it, rsync. An optional feature of 20.04 that looks to make backup simpler and more comprehensive is ZFS. It’s newly integrated into Ubuntu and I want to try it for backups.

I got my backups then took the server offline to get a system image with Clonezilla. Then I used VBoxManage convertfromraw to turn the Clonezilla disk image into a VDI file. That gave me a clone of the server in VirtualBox to practice upgrading and work out any kinks.

The server runs several websites, a MySQL server for the websites and other things, an SSH server for remote access, NFS, phpmyadmin, DNS, and more. They are either accessed remotely or from a LAN client. Testing those functions required connecting a client to the server. VirtualBox made that a simple trick.

In the end my lab setup was two virtual machines, my cloned server and a client, on a virtual network. DHCP for the client was provided by the VirtualBox Internal Network, the server had a fixed ip on the same subnet as the VirtualBox Internal Network and the server provided DNS for the network.

I ran the 16.04 to 18.04 upgrade on the server numerous times taking snapshots to roll back as I made tweaks to the process to confirm each feature worked. Once I had a final process I did the upgrade on the virtual machine three times to see if I could find anything I might have missed or some clarification to make to the document. Success x3 with no changes to the document!

Finally I ran the upgrade on the production hardware. Went exactly as per the document which of course is a good thing. Uneventful but slower than doing it on the virtual machine, which was expected. The virtual machine host is at least five years newer than the server hardware and has an SSD too.

I’ll continue running on 18.04 for a while and monitor logs for things I might have missed. Once I’m convinced everything is good then I’ll either use -d to get to 20.04 or wait until 20.04.1 is out and do it then.

MySQL backup and restore

Dig in and do it, and repeat. Get the desired result faster by combining research and testing.

Maintenance is important. A car needs oil changes or eventually the engine will be damaged by regular operation. A server needs software updates to fix bugs and protect against threats. Even when those things are done failures can happen that create problems returning to normal operation.

For a car there needs to be a spare ready to go in case of a flat. If there’s not a spare ready for use it will take longer to get the car back in operation when a flat happens. For a computer, programs and data need to be backed up. If a disk drive crashes the information stored there may be lost or too expensive to recover, so just as good as lost.

This website has not been well protected for too long and I knew that needed to change. There’s a server operating system, a web server, WordPress software, and a MySQL database that all operate interdependently to make it work. As the amount of content slowly continues to grow my manual system to back everything up has become too cumbersome and is not done frequently enough to ensure minimal to no loss of data.

That needed to change.

Step one – automate the MySQL backups. Documentation states the “logical” backup method is slow and not recommend for large databases. The alternative “physical” backup entails stopping the database server and copying the files. The licensed MySQL Enterprise Backup performs physical backups and from what I’m able to tell runs clone databases so one can be stopped and the files backed up while the clone continues to run and is available for use.

This is a hobby operation and has limited resources so purchasing a license for Enterprise Backup is out of the question. Taking the whole thing offline to backup probably doesn’t bother anyone except me. Still, I did want to be able to continue to run the server while the databases are being backed up. Enter logical backup.

It didn’t take long to find the command, mysqldump. Confirming that it would backup everything including user names and passwords so all the accounts got restored with all the data took longer.

Despite my best search-fu I was unable to find any documentation that explicitly says “do this” to back up user accounts in addition to system databases and other databases. Let me fill that gap by saying “do this to back up user accounts, system databases, and other databases”. mysqldump -u root -p -h server.yourdomain.org --all-databases -r backup_file.sql. I did find the preceding command as the backup command. Nothing I could find said this backs up user accounts and system databases. I tested it. It does.

With the backup done, the next step is restore. And confirming the restore works as expected. Another case of things that otherwise seem obvious not being declared in the documentation.

Restore from the command line looks like this: mysql -u root -p database < backup_file.sql. But wait, I want to restore all databases. Search-fu failed again to find any explicit instruction how to restore all databases and what database to name on the command line.

Try the command without naming a database to see if all are restored. No, that fails. Then a flash of insight. Create an empty database, name that on the command line, and try the restore again. It works!

$ mysql -u root -p
> create database scratch;
> exit
$ mysql -u root -p scratch < backup_file.sql

Did this a few times and then restored the tables. As far as I’ve been able to determine the restore is an exact replica of the backed up data.

It seems odd that important use cases, complete backup of database server and complete restore of database server aren’t clearly documented. The information is there but important nuggets are left out. The only way to be sure you’ll get what you need is to experiment until you’re able to produce the results you need.

So yes, do the research but also just do the work and inspect the results. When research doesn’t clearly answer the questions backup it up with experimentation. Do both and get a result faster.