Category: Debian

Migrating Databases

Last week I decided to clean up a bit of digital cruft. That is, I moved a few of my websites onto a single VPS, saving quite a bit of monthly server hosting costs.

What I did was that I moved VPSes from Linode (Akamai) to DigitalOcean, but also migrated a full web hotel from One to DigitalOcean (converting email accounts to email forwards).

As this is something that I do very rarely, I decided to document the process here so that I don’t have to look everything up again next time around.

The grunt work was about migrating a number of L*MP services to a LEMP server. There are a couple of tasks involved here, mainly migration of databases and getting WordPress running in a subdirectory using Nginx. The rest of the exercise had to do with the moving of nameservers and waiting for DNS propagation to get certbot to provide certificates for the new location.

Migration of MySQL databases

The migration of a database between machines can be broken down into three stages:

  1. Dumping the old database
  2. Creating a new database and user
  3. Sourcing the database contents into the new database

I choose to do it in these three stages, as I’d like to keep the old database dump as an additional backup. The other option would be to transfer the database contents in a single step, merging steps 1 and 3 into one

Nevertheless, I use mysqldump to dump the database contents, and then bzip2 to reduce the size of the dump. This is efficient since and SQL dump is quite verbose.

mysqldump -u username -p --databases databasename | grep -vE \"^(USE|CREATE DATABASE)\" | bzip2 -c - > dumpname.sql.bz2

This is derived from the answer by Anuboiz over at stack overflow. The resulting file is then transferred to the new server using scp together with the actual website.

The next step is to create a new database and a new database user. Here, I assume MariaDB (using the mysql commands), as my main target is WordPress. For other database engines, e.g. Postgresql, please check the docs for exact grammar, but the SQL commands should be very similar.

sudo mysql
mysql> CREATE DATABASE databasename;
mysql> USE databasename;
mysql> CREATE USER 'username'@'localhost' identified by 'password';
mysql> GRANT CREATE, ALTER, DROP, INSERT, UPDATE, DELETE, SELECT, REFERENCES, RELOAD on databasename.* TO 'username'@'localhost' WITH GRANT OPTION;
mysql> EXIT

Check out this digital ocean tutorial for details on the above commands.

The next step is to read the database contents into the new database. For this, we need to unzip the sql dump, e.g. bunzip2 dumpname.sql.bz2, which will result in a file called dumpname.sql. Please notice that bunzip2 unzips the file and removes the original, zipped, file. If you want to keep the original, use the -k option.

Once you have the dumpname.sql file available, you can read it into the database with the newly created user using the source command as shown below.

mysql -u username -p
enter the password here
mysql> USE databasename;
mysql> SOURCE dumpname.sql;
mysql> EXIT

Now you should have a new database with the old database contents on the new server, with an associated database user. For WordPress sites, make sure that you reflect any changes in the associated wp-config.php file.

WordPress in a subdirectory using Nginx

The other piece of the puzzle that was new to me was to run WordPress from a subdirectory, e.g. example.com/blog/, rather than from the root level, e.g. example.com/.

Removing most of the nginx server configuration, the following parts does the magic:

server {
        root /var/www/thelins.se;
        index index.php index.html;

        server_name thelins.se www.thelins.se;

        ...

        # For root
        location / {
                try_files $uri $uri/ /index.php?$args;
        }

        # For subdirectory
        location /johan/blog/ {
                try_files $uri $uri/ /johan/blog/index.php?$args;
        }

        location ~ \.php$ {
                fastcgi_split_path_info ^(.+\.php)(/.+)$;
                fastcgi_pass unix:/run/php/php7.4-fpm.sock;
                fastcgi_index index.php;
                include fastcgi.conf;
        }

        ...
}

The trick was to ensure that the subdirectory try_files statement refer to the correct index.php. Notice that this has to be done for each WordPress instance, if you happen to have multiple WordPress installations in various subdirectories on the same domain.

Conclusions

Its a bit of hassle to migrate a lot of web sites at once, but the monetary saving from moving the low traffic sites onto a single VPS, and the simplification of the management and monitoring by moving all VPSes to a single provider makes it worth it.

Hibernation

So, another day, another update. Today I managed to get hibernation working on my Dell XPS13 Plus (9320) running Debian.

So, a quick recap. I’m running Debian testing. I set up the system with Guided – Use entire disk and Setup LVM. This leaves me with an encrypted root partition. What I want to do now, is to put a reasonably sized swap file there, and make the system hibernate to it.

So, first, I created a 35 GB (35840MB) large swapfile as /swapfile. I prefer to create a swapfile slightly larger than RAM, to ensure that everything fits and my machine comes with 32GB of RAM. I used the instructions in the excellent Arch Wiki to set this up. I also edited /etc/fstab, commenting out the swap partition setup by the installer and adding the swapfile.

Then, I followed these instructions to find and set the resume and resume_offset in the /etc/defaults/grub file. I changed the GRUB_CMDLINE_LINUX_DEFAULT parameter.

A quick reboot followed by a sudo swapon --show told me that the swap file was active, so time to hibernate!

sudo systemctl hibernate

This resulted in a lovely Sleep verb "hibernate" not supported error message in bright red. Lovely.

After some poking around I had a look at dmesg and found a reference to kernel lockdown. It turns out that you cannot hibernate if kernel lockdown is active. Being more concerned about battery life than some expert hacker stealing my computer and getting to all my data, I decided to try to get rid of it. Turns out that by disabling secure boot in BIOS does the trick.

So, now this works. Let’s see when I can fix the next issue. I’ve got the audio issue discussed earlier and the web cam left to fix.

Debian on the XPS13 Plus (9320)

TL;DR; Still no sound, but I learned a couple of new things…

So, I finally got around to upgrading my laptop. I decided to go for my fourth XPS13, and this time I opted for a maxed out XPS13 Plus. A really nice machine. However, the driver stack isn’t quite there yet. Yes, I should have read up more before buying, but I didn’t and I know it will be sorted out over time.

As a vim user, the touch Esc key will be a challenge. Perhaps this is where I learn to bind to capslock, but I’ve not come to that point yet.

So, after installing using the netinst image with non-free drivers (and my phone over USB tether for networking since the wifi still didn’t work), I had to move to testing for anything to work. Then I installed firmware-iwlwifi, iwlwifi and firmware-sof-signed from non-free. This got me into a graphical desktop and most things work (I could configure the touch pad for tap-to-click, and so on). I run a KDE desktop, so I installed some Plymouth stuff, breeze for SDDM and such, but that shouldn’t affect the issues described here.

My current issues are the sound, the webcam and hibernation. The two latter items aren’t huge problems, but I do need sound. Both the webcam and sound issues are known. Hibernation is mostly about getting around to configuring it with the encrypted disk setup.

So, let’s start by diving into the audio issue. The early boot process looks like this:

[   20.595666] sof-audio-pci-intel-tgl 0000:00:1f.3: DSP detected with PCI class/subclass/prog-if info 0x040100
[   20.595750] sof-audio-pci-intel-tgl 0000:00:1f.3: SoundWire enabled on CannonLake+ platform, using SOF driver
[   20.595778] sof-audio-pci-intel-tgl 0000:00:1f.3: enabling device (0000 -> 0002)
[   20.596001] sof-audio-pci-intel-tgl 0000:00:1f.3: DSP detected with PCI class/subclass/prog-if 0x040100
[   20.596073] sof-audio-pci-intel-tgl 0000:00:1f.3: bound 0000:00:02.0 (ops i915_audio_component_bind_ops [i915])
[   20.602984] sof-audio-pci-intel-tgl 0000:00:1f.3: use msi interrupt mode
[   20.651283] sof-audio-pci-intel-tgl 0000:00:1f.3: hda codecs found, mask 4
[   20.652476] sof-audio-pci-intel-tgl 0000:00:1f.3: firmware: direct-loading firmware intel/sof/sof-adl.ri
[   20.652482] sof-audio-pci-intel-tgl 0000:00:1f.3: Firmware info: version 2:2:0-57864
[   20.652483] sof-audio-pci-intel-tgl 0000:00:1f.3: Firmware: ABI 3:22:1 Kernel ABI 3:23:0
[   20.652488] sof-audio-pci-intel-tgl 0000:00:1f.3: unknown sof_ext_man header type 3 size 0x30
[   20.747032] sof-audio-pci-intel-tgl 0000:00:1f.3: Firmware info: version 2:2:0-57864
[   20.747036] sof-audio-pci-intel-tgl 0000:00:1f.3: Firmware: ABI 3:22:1 Kernel ABI 3:23:0
[   25.758149] sof_sdw sof_sdw: snd_soc_register_card failed -517
[   25.768995] sof_sdw sof_sdw: snd_soc_register_card failed -517
[   25.799027] sof_sdw sof_sdw: snd_soc_register_card failed -517

If I later force a reload of the module, it all works:

sudo modprobe -r snd-sof-pci-intel-tgl; sudo modprobe snd-sof-pci-intel-tgl

Gives:

[  169.407671] sof-audio-pci-intel-tgl 0000:00:1f.3: DSP detected with PCI class/subclass/prog-if info 0x040100
[  169.407784] sof-audio-pci-intel-tgl 0000:00:1f.3: SoundWire enabled on CannonLake+ platform, using SOF driver
[  169.408027] sof-audio-pci-intel-tgl 0000:00:1f.3: DSP detected with PCI class/subclass/prog-if 0x040100
[  169.408133] sof-audio-pci-intel-tgl 0000:00:1f.3: bound 0000:00:02.0 (ops i915_audio_component_bind_ops [i915])
[  169.414240] sof-audio-pci-intel-tgl 0000:00:1f.3: use msi interrupt mode
[  169.428614] sof-audio-pci-intel-tgl 0000:00:1f.3: hda codecs found, mask 4
[  169.428802] sof-audio-pci-intel-tgl 0000:00:1f.3: firmware: direct-loading firmware intel/sof/sof-adl.ri
[  169.428809] sof-audio-pci-intel-tgl 0000:00:1f.3: Firmware info: version 2:2:0-57864
[  169.428810] sof-audio-pci-intel-tgl 0000:00:1f.3: Firmware: ABI 3:22:1 Kernel ABI 3:23:0
[  169.428814] sof-audio-pci-intel-tgl 0000:00:1f.3: unknown sof_ext_man header type 3 size 0x30
[  169.547087] sof-audio-pci-intel-tgl 0000:00:1f.3: Firmware info: version 2:2:0-57864
[  169.547115] sof-audio-pci-intel-tgl 0000:00:1f.3: Firmware: ABI 3:22:1 Kernel ABI 3:23:0
[  169.563645] sof-audio-pci-intel-tgl 0000:00:1f.3: firmware: direct-loading firmware intel/sof-tplg/sof-adl-rt1316-l12-rt714-l0.tplg
[  169.563665] sof-audio-pci-intel-tgl 0000:00:1f.3: Topology: ABI 3:22:1 Kernel ABI 3:23:0
[  169.564019] sof_sdw sof_sdw: ASoC: Parent card not yet available, widget card binding deferred
[  169.605390] sof_sdw sof_sdw: hda_dsp_hdmi_build_controls: no PCM in topology for HDMI converter 3
[  169.627693] input: sof-soundwire HDMI/DP,pcm=5 as /devices/pci0000:00/0000:00:1f.3/sof_sdw/sound/card0/input26
[  169.627738] input: sof-soundwire HDMI/DP,pcm=6 as /devices/pci0000:00/0000:00:1f.3/sof_sdw/sound/card0/input27
[  169.631375] input: sof-soundwire HDMI/DP,pcm=7 as /devices/pci0000:00/0000:00:1f.3/sof_sdw/sound/card0/input28

So, something happens after sof-adl.ri is loaded. This causes the driver to just stop before loading the sof-adl-rtl316-l12-rt714-l0.tplg firmware, causing the snd_soc_register_card to fail. Let’t have a look at the reasons.

First up, the excellent Arch Linux Wiki says to include the two firmware files and a bunch of modules in the initramfs. However, Arch and Debian uses different tools to build the initramfs, so let’s confirm the issue first:

lsinitramfs -l /boot/initrd.img-6.0.0-5-amd64  | grep 'intel/sof'

This call results in a list of nothing. Just to confirm, grepping for firmware or intel returns long lists of files. So, the firmware is not in the early initramfs image. How do I get the firmware files into the initramfs? Apparently I have to write what is known as an initramfs-tools hook script.

There lives a bunch of them over at /usr/share/initramfs-tools/hooks, so I started a very brute force one called intel-sof-firmware and tried to learn from the surrounding scripts and the manpage linked above. The result can be found in a gist here.

Notice that this is happy code. There is way too few sanity checks in there to make this useful to the general public. Your milage may vary.

So, I added the modules to /etc/initramfs/modules, and then updated the early initramfs images with this command:

sudo update-initramfs -k all -u

And then verified that the files made it to the image (I also had a look at the file listing in general to ensure that the image is ok):

lsinitramfs -l /boot/initrd.img-6.0.0-5-amd64  | grep 'intel/sof'

drwxr-xr-x   2 root     root            0 Dec 11 15:51 usr/lib/firmware/intel/sof
drwxr-xr-x   2 root     root            0 Dec 11 15:51 usr/lib/firmware/intel/sof-tplg
-rw-r--r--   1 root     root        28907 Dec 11 15:51 usr/lib/firmware/intel/sof-tplg/sof-adl-rt1316-l12-rt714-l0.tplg
-rw-r--r--   1 root     root       525056 Dec 11 15:51 usr/lib/firmware/intel/sof/sof-adl.ri

It all seems to work, so let’s reboot and see what comes out the other side. Notice – this can brick your computer if you make a mistake. And if something to do with a computer can brick your computer, you know that it will brick your computer. Don’t say I did not tell you.

Side note: I run an encrypted lvm setup, and if you try to fix this problem by removing all modules (go from many to netbook in the initramfs.conf) you will enjoy re-installing your machine. I’m sure you can unbrick it by booting from a USB stick and fixing stuff, but since I’ve not really installed anything, I don’t really care.

Guess what – after a couple of hours digging at this – I still have no sound…

sudo dmesg | grep sof

Results in this (yay, a new error code – that must mean that I’m doing something):

[    1.760559] sof-audio-pci-intel-tgl 0000:00:1f.3: DSP detected with PCI class/subclass/prog-if info 0x040100
[    1.760629] sof-audio-pci-intel-tgl 0000:00:1f.3: SoundWire enabled on CannonLake+ platform, using SOF driver
[    1.760643] sof-audio-pci-intel-tgl 0000:00:1f.3: enabling device (0000 -> 0002)
[    1.760785] sof-audio-pci-intel-tgl 0000:00:1f.3: DSP detected with PCI class/subclass/prog-if 0x040100
[    3.491343] sof-audio-pci-intel-tgl 0000:00:1f.3: bound 0000:00:02.0 (ops i915_audio_component_bind_ops [i915])
[    3.569000] sof-audio-pci-intel-tgl 0000:00:1f.3: use msi interrupt mode
[    3.585475] sof-audio-pci-intel-tgl 0000:00:1f.3: codec #2 probe error, ret: -2
[    3.585859] sof-audio-pci-intel-tgl 0000:00:1f.3: no hda codecs found!
[    3.585990] sof-audio-pci-intel-tgl 0000:00:1f.3: firmware: direct-loading firmware intel/sof/sof-adl.ri
[    3.585996] sof-audio-pci-intel-tgl 0000:00:1f.3: Firmware info: version 2:2:0-57864
[    3.585998] sof-audio-pci-intel-tgl 0000:00:1f.3: Firmware: ABI 3:22:1 Kernel ABI 3:23:0
[    3.586007] sof-audio-pci-intel-tgl 0000:00:1f.3: unknown sof_ext_man header type 3 size 0x30
[    3.702740] sof-audio-pci-intel-tgl 0000:00:1f.3: Firmware info: version 2:2:0-57864
[    3.702756] sof-audio-pci-intel-tgl 0000:00:1f.3: Firmware: ABI 3:22:1 Kernel ABI 3:23:0
[    3.709727] sof-audio-pci-intel-tgl 0000:00:1f.3: firmware: direct-loading firmware intel/sof-tplg/sof-adl-rt1316-l12-rt714-l0.tplg
[    3.709742] sof-audio-pci-intel-tgl 0000:00:1f.3: Topology: ABI 3:22:1 Kernel ABI 3:23:0
[    3.709825] sof-audio-pci-intel-tgl 0000:00:1f.3: error: can't connect DAI HDA0.OUT stream iDisp1
[    3.709920] sof-audio-pci-intel-tgl 0000:00:1f.3: error: failed to add widget id 0 type 27 name : HDA0.OUT stream iDisp1
[    3.710026] sof_sdw sof_sdw: ASoC: failed to load widget HDA0.OUT
[    3.710086] sof_sdw sof_sdw: ASoC: topology: could not load header: -22
[    3.710162] sof-audio-pci-intel-tgl 0000:00:1f.3: error: tplg component load failed -22
[    3.710247] sof-audio-pci-intel-tgl 0000:00:1f.3: error: failed to load DSP topology -22
[    3.710326] sof-audio-pci-intel-tgl 0000:00:1f.3: ASoC: error at snd_soc_component_probe on 0000:00:1f.3: -22
[    3.710447] sof_sdw sof_sdw: ASoC: failed to instantiate card -22
[    3.710692] sof_sdw sof_sdw: snd_soc_register_card failed -22
[    3.710758] sof_sdw: probe of sof_sdw failed with error -22

I suspect I’m missing another driver in the initramfs. Still, the modprobe trick shown above still fixes sound, so I guess I’ll leave it for today…