Better late than never?
Jan. 5th, 2019 11:46 pm![[personal profile]](https://www.dreamwidth.org/img/silk/identity/user.png){kind=small}
pheloniusfriarTo be honest, I have just been lucky beyond fucking belief. I finally implemented a backup for my server's database (honestly, there wasn't much in it, so it wasn't too big of a deal before now, but it is finally starting to have valuable data in it).
But before I get to the part that will drive most anyone away, Happy New Year everyone! In a bit of a strange fluke, I was informed about and got the 3 hour radio slot (normally two shows) on New Year's Eve! I hadn't been on the radio for nearly a year by that point, but I really like doing late night shows, and New Year's Eve or first thing in the new year (after midnight) are really fun to do. I get to play some of the new-to-me music I've found over the course of the year, and do try to put together a show that will at least get your toes tapping, but could be danced to in places, but doesn't drive people away from the station (that it'd be fun to have on in the background of a party). If you'd be up for 3 hours of generally upbeat, or at very least interesting and engaging music (and some hopefully not terrible short talk segments between long sets... there are no commercials, fyi), then this might be worth your time. Stream "on demand" 24/7:
https://cod.ckcufm.com/programs/161/40989.html (first two hours, filling in for Joe Reilly show)
https://cod.ckcufm.com/programs/56/40990.html (last hour, filling in for Meltdown show)
Just click on the "listen now" menu choice.
Now to the business at hand, feel free to skibidi ahead to the video if you're into those sorts of things. I finally wrote a script to do automated backups of my database system. It's not a true "hot" backup, but it's about as close as one can get without specialized tools. The fact I'm running a GNU/Linux with a full-featured Logical Volume Manager (LVM) means I can do an LVM snapshot of the database's Logical Volume (I have it mounted on "/var/lib/mysql", but it is a separate logical volume so I can do this sort of thing [at least I was clever enough to do that years ago when I set up the database configuration]). The trick is to use the "mysql" client, issue a "FLUSH TABLES WITH READ LOCK" command to flush the internal database buffers to disk while preventing access (generally a very fast process, but the lock is why it's not a fully "hot" backup), take an LVM snapshot of the database's logical volume (a very, very fast process as it is a copy-on-write mechanism), and then releasing the locks with an "UNLOCK TABLES" command from the "mysql" client. The trick is that you need to wait until the "FLUSH TABLES WITH READ LOCK" command is finished before doing the LVM snapshot, and you have to keep the "mysql" client open during all of this or it will release the table locks. To accomplish this, I used the Tcl/Expect framework. Once that was done, I mounted the LVM snapshot, fired up a second instance of MariaDB listening on a different socket than the running MariaDB used by the system, and do a "mysqldump" of the snapshot to a compressed file on a backup volume (on a different set of disks, to be copied to an offsite backup storage system from there) before unmounting and destroying the LVM snapshot. All of this last bit is done after locks have been removed, so it doesn't impact database performance. Also, because I'm doing a "mysqldump" of the snapshot (which dumps the databases as SQL text), it can be imported into any release of MariaDB (whereas the snapshot itself can only be loaded into the same version of the database system as created it). Here's the script (assumes a 40GB logical volume called "lvdbase" for the database in a volume group called "my_vg", calling the snapshot "lvdback", and a few paths and mount-points I created on my Slackware 14.2 system):
Edit (2020/04/28): It seems my system would sometimes leave the lvdback logical volume on the disk and caused the script to fail. Hmmm. I had to add the following little bit to the script to clean it up if it didn't get removed (that seems to be the only thing causing failure in the script). The line at the bottom is already in the script and the new check is right before it.
But before I get to the part that will drive most anyone away, Happy New Year everyone! In a bit of a strange fluke, I was informed about and got the 3 hour radio slot (normally two shows) on New Year's Eve! I hadn't been on the radio for nearly a year by that point, but I really like doing late night shows, and New Year's Eve or first thing in the new year (after midnight) are really fun to do. I get to play some of the new-to-me music I've found over the course of the year, and do try to put together a show that will at least get your toes tapping, but could be danced to in places, but doesn't drive people away from the station (that it'd be fun to have on in the background of a party). If you'd be up for 3 hours of generally upbeat, or at very least interesting and engaging music (and some hopefully not terrible short talk segments between long sets... there are no commercials, fyi), then this might be worth your time. Stream "on demand" 24/7:
https://cod.ckcufm.com/programs/161/40989.html (first two hours, filling in for Joe Reilly show)
https://cod.ckcufm.com/programs/56/40990.html (last hour, filling in for Meltdown show)
Just click on the "listen now" menu choice.
Now to the business at hand, feel free to skibidi ahead to the video if you're into those sorts of things. I finally wrote a script to do automated backups of my database system. It's not a true "hot" backup, but it's about as close as one can get without specialized tools. The fact I'm running a GNU/Linux with a full-featured Logical Volume Manager (LVM) means I can do an LVM snapshot of the database's Logical Volume (I have it mounted on "/var/lib/mysql", but it is a separate logical volume so I can do this sort of thing [at least I was clever enough to do that years ago when I set up the database configuration]). The trick is to use the "mysql" client, issue a "FLUSH TABLES WITH READ LOCK" command to flush the internal database buffers to disk while preventing access (generally a very fast process, but the lock is why it's not a fully "hot" backup), take an LVM snapshot of the database's logical volume (a very, very fast process as it is a copy-on-write mechanism), and then releasing the locks with an "UNLOCK TABLES" command from the "mysql" client. The trick is that you need to wait until the "FLUSH TABLES WITH READ LOCK" command is finished before doing the LVM snapshot, and you have to keep the "mysql" client open during all of this or it will release the table locks. To accomplish this, I used the Tcl/Expect framework. Once that was done, I mounted the LVM snapshot, fired up a second instance of MariaDB listening on a different socket than the running MariaDB used by the system, and do a "mysqldump" of the snapshot to a compressed file on a backup volume (on a different set of disks, to be copied to an offsite backup storage system from there) before unmounting and destroying the LVM snapshot. All of this last bit is done after locks have been removed, so it doesn't impact database performance. Also, because I'm doing a "mysqldump" of the snapshot (which dumps the databases as SQL text), it can be imported into any release of MariaDB (whereas the snapshot itself can only be loaded into the same version of the database system as created it). Here's the script (assumes a 40GB logical volume called "lvdbase" for the database in a volume group called "my_vg", calling the snapshot "lvdback", and a few paths and mount-points I created on my Slackware 14.2 system):
#!/usr/bin/expect -f
set db_passwd "<password>"
set backup_timestamp [exec date +%Y%m%d_%H%M%S]
set backup_filepath [file join "/var/backup/" "db_backup-${backup_timestamp}.gz"]
# Take safe snapshot of database
set timeout -1
spawn /usr/bin/mysql -u root -p
match_max 100000
expect -exact "Enter password: "
send -- "${db_passwd}\r"
expect -exact "> "
send -- "FLUSH TABLES WITH READ LOCK;\r"
expect -exact "> "
exec /sbin/lvcreate -L40G -s -n lvdback /dev/my_vg/lvdbase
send -- "UNLOCK TABLES;\r"
expect -exact "> "
send -- "exit\r"
expect eof
# Create portable archive of database backup using new database instance
exec /bin/mount /dev/my_vg/lvdback /mnt/db_backup
exec /usr/bin/rm -f /var/run/mysql/mysql-backup.sock
exec /usr/bin/mysqld_safe --no-defaults --port=3307 --socket=/var/run/mysql/mysql-backup.sock \
--datadir=/mnt/db_backup --pid-file=/var/run/mysql/mysql-backup.pid --log-error=/var/lib/mysql/mysql-backup.err &
# Wait until database has socket set up
while {! [file exists /var/run/mysql/mysql-backup.sock] } {
after 1000
}
exec /usr/bin/mysqldump -u root --password=$db_passwd --all-databases -S /var/run/mysql/mysql-backup.sock \
| /usr/bin/gzip > $backup_filepath
exec /usr/bin/chmod 400 $backup_filepath
spawn /usr/bin/mysqladmin -u root -p -S /var/run/mysql/mysql-backup.sock shutdown
expect -exact "Enter password: "
send -- "${db_passwd}\r"
expect eof
# Clean up database snapshot
exec /bin/umount /mnt/db_backup
exec /sbin/lvremove -f /dev/my_vg/lvdback
There is obviously more error checking, but this is the backbone of the functionality that was needed to do the job. I then had to add the script to the "crontab" on my system. After a little poking, the way Slackware 14.2 does "cron" is pretty convenient if you're okay with their standard framework (the full "cron" system is also available, but I went with easy on this). In particular, there are four directories that one just has to copy executables into: "/etc/cron.hourly", "/etc/cron.daily", "/etc/cron.weekly", and "/etc/cron.monthly". In the "crontab" for "root" (in "/var/spool/cron/crontabs/root"), it has entries that invoke a helper script called "run-parts" that runs all the executables in the appropriate directories at the appropriate intervals. Easy peasy.Edit (2020/04/28): It seems my system would sometimes leave the lvdback logical volume on the disk and caused the script to fail. Hmmm. I had to add the following little bit to the script to clean it up if it didn't get removed (that seems to be the only thing causing failure in the script). The line at the bottom is already in the script and the new check is right before it.
if { [catch { exec /sbin/lvdisplay /dev/vgexp01/lvdback } msg ] == 0} {
exec /sbin/lvremove -f /dev/vgexp01/lvdback
puts "\nRemoved unexpected lvdback logical volume"
}
exec /sbin/lvcreate -L10G -s -n lvdback /dev/vgexp01/lvdbaseI can't say enough about the effect this video had on me this past year (I only saw it for the first time last year). The visuals are so impactful. That there is not a scrap of CGI in it is almost impossible to believe at first... the scale and surreality of it is breathtaking. I find it conjures feelings of crushing loneliness in me, but to me that means that it's good art.
