MyLVMbackup: Adding keep_mount
So for my work, I needed yet another addition to the whole slew of options that mylvmbackup already has. This time, I added a keep_mount option. The dpatch can be found here. The package with this patch and my previous work on mylvmbackup (which added support for a defaults_file instead of plaintext username/password credentials in the mylvmbackup.conf) can be found in Kumina’s Debian repository.
WARNING: These two patches are my first steps into the wonderous world of Perl. I only checked if they did what I wanted them to do, not if it still worked as required without this extra code. Your mileage may vary, so be sure to test!
Also, if you use this, please let me know! I’d appreciate it.
Tags: backup, configuration, debian, etch, lenny, mylvmbackup, mysql, package
Comments Off
MyLVMbackup: Making it cooperate with Debian
For making a consistent backup of MySQL data, mylvmbackup seems to be a great tool. It’s written in Perl and does what you would expect: Lock the tables, flush them all to disk, make a LVM snapshot and release the tables, recover the InnoDB log,make the backup from the snapshot, remove the snapshot. Minimal downtime and still a consistent backup. Although it’s fairly new, it looks good.
There’s even a Debian package for it, but that doesn’t really contain everything I need. First of all, I need a package that runs on Etch. There’s a dependency in the Debian package (which is for Lenny) on an asciidoc of a version that’s available only in Lenny. I don’t really see why that dependency is there at all, but when removing the version, the package still works as expected.
Also, I made two patches of which one is not needed at all, but I left it in there anyway, because it doesn’t break anything. It simply adds the –innodb-file-per-table option to the invocation of the mysqld_safe daemon, but that’s not needed since it only affects MySQL when you change data. But since mysqld_safe is used to recover from logs, you won’t be changing data. I left it in there anyway. You can find the dpatch file here.
The second one might be more interesting. The mysql-server-5.0 package from Debian creates a file /etc/mysql/debian.cnf with login details for the debian-sys-maint user. This user also has enough permissions to do all the things necessary for manipulating the database when we want to make a backup with mylvmbackup. Normally, mylvmbackup needs you to give it user details for a privileged user in /etc/mylvmbackup.conf, which is a bit double, since the debian.cnf exists. So my patch adds a –defaults_file option which allows you to mention the debian.cnf. No need for more passwords in plaintext on the filesystem! You can either give this option no the commandline or in the mylvmbackup.conf in the [mysql] section. It only supports Debian style debian.cnf, though, since it searched for a [client] section in the file you mention. You can find the patch here.
A working package can be found in Kumina’s Debian repository for both etch and lenny. Please let me know if this works for you! It’s my first Perl, though, so be warned. If stuff breaks… Well… Sorry… So be sure to test it.
MySQL: Backslash is a forbidden character in passwords
Just so you know. My co-worker Bart spent a while figuring that out.
Security is hard
Just got an email form one of our customers asking if it was possible to use an encrypted password in PHP for connecting to MySQL. So that instead of doing something like:
$connection = mysql_connect(host,user,pass);
He wanted to do something like:
$connection = mysql_connect(host,user,encrypted_pass);
Which is of course not very useful (since you could use the encrypted string just like any other normal password, so there’s no added security). We come across these kinds of notions quite often, people want to use encryption for security, but the way they use it makes it kind of useless.
A few years ago we had a customer who wanted a fully secured machine, from boot onwards. This so he could sell appliances without giving his customers easy access to the operation system and application. He wanted an encrypted hard disk. But if you use standard x86 based hardware, you have no way to store an initial secret. Even if you’d embed the password somewhere in the bootloader, it’s still somewhere on that machine.
Security is hard to do well. I wish people would start by simply applying best practises, like setting safe file permissions. Encryption is often not very useful if you want parts of an application to actually access the data without the user entering the password of that encrypted data.
Shrinking a MySQL ibdata1 file
MySQL’s InnoDB engine stores all tables by default in one large file which is located (in Debian) under /var/lib/mysql/ibdata1. This file allocates some diskspace (in Debian it defaults to 10M chunks), but never unallocates the disk space. So if you make a few large tables, your partition might get full. Removing those large tables doesn’t help a thing, since the ibdata1 file is never shrunken by MySQL’s InnoDB engine. I needed to shrink it, preferably with as little as possible downtime.
It is nigh impossible. I’ve tried several things. First, I added the option innodb_file_per_table. The idea was to optimize each table, so it would be rewritten into a separate file. Then delete the ibdata1 file and it’s logs and restart MySQL. This worked in a simple test setup, but failed miserably in the production environment. I have no idea what went wrong, but even when I restored the old ibdata1 and it’s logs, it wouldn’t work anymore. Chaos and mayhem all around. Joy.
So I resorted to the only other solution I could find: Make a dump of the complete database and remove all files in /var/lib/mysql, then restart MySQL and restore the dump. Not a very practical solution, but the only one that seems to work reliably.
NFS file locking
Sometimes you need file locking over NFS. In my case, I needed it for running MySQL tables via NFS. Even though you can tell MySQL to not do external locking, this setting is ignored when you use InnoDB. That’s not a very well documented feature. So you need to make sure locking is available.
I did most of my debugging with a small perl script that I found on the HP forums. Simply edit the location and you’re ready to go. My problem was that I had no trouble at all getting it to work in another setup, but I couldn’t find at first what was different in the production setup. In the end it was very obvious (as it usually is): The network was way more complicated.
The setup I’m talking about is in a datacentre that houses several of our customers. They’re all daughter-companies from one large one, so they’re okay with sharing some infrastructure. But each company has it’s own domain name and prefers of course to use that one. Now, we name our virtual machines (we do everything virtualised) after their specific function, so a database master would be called something like dbm1.customera.com. Another customer with another database master would call the machine dbm1.customerb.com. But when we’re working in that network, it’s annoying to use those full names, so we have an internal DNS that resolves things like dbm1.custa and dbm1.custb. Just for convenience.
But that convenience is what made NFS locking break. Since the machine would report it’s hostname as being dbm1.customera.com, while it’s DNS name would be dbm1.custa. Apparantly, NFS can’t handle that very well.
Luckily, there’s an easy solution: Add a specific hostname to the NFS stat daemon. In Debian, you do that by editing /etc/default/nfs-common (after you installed the nfs-common package) and adding the following line: STATDOPTS="-n dbm1"
That worked for me. Now, I have working InnoDB tables on an NFS share.
MySQL says: “ERROR 1030 (HY000): Got error -1 from storage engine”
In my case, this meant it was not allowed to write data (an INSERT) to a InnoDB table because innodb_force_recovery was set too high. Took me a while to figure out, so I hope this helps someone.
Solution is to simply disable the innodb_force_recovery. You should use that setting only for making an emergency dump anyway.
More about my wanderings through MySQL+InnoDB+NFS land soon.
Tags: a_bit_strange, credentials, debian, fix, lenny, mysql, permissions
1 comment
MySQL on Lenny and permissions
Ok, had something strange that I think is worth a share. I just upgraded a virtual machine to Lenny and installed MySQL and phpMyAdmin and apache and the like. Created a user for a Django project I’m working on and it couldn’t connect. I tried resetting the password several times, didn’t help. Then, out of frustration, I tried connecting without a password and… it worked!
Apparantly, by default, the MySQL in Lenny created access rules that allow all local users to access the database without a password. These have priority over the specific user controls. A bit unexpected and I suspect others to run into this. Could make you search for a problem if you’re installing a shared webhosting machine with a local MySQL database.
Solution:
delete from mysql.user where User='';
MySQL on S3… not yet
Ok, I’ve been playing with EC2 and S3. Using quite a long and complicated script to create an AMI (which is Amazon’s term for a disk image). The script is coming along nicely, using debootstrap to create a Debian etch image.
Yesterday and today (well, only in the evening today), I’ve been trying to get MySQL working on an s3fs drive, but I’m afraid that’s not going to work. Well, part of it might be because I’m trying to set it up from home and not from an EC2 instance, but still. MySQL just refuses to start with a weird message:
Starting MySQL database server: mysqld . . . . . . . . . . . . . . failed!
invoke-rc.d: initscript mysql, action "start" failed.
After which it hangs, altough it seems like MySQL did start. Maybe it’s something with the initscript. I realised in the end that I don’t really need MySQL running in such a setup. Making dumps every so often should be enough for what we have in mind. So I’m going to script an automatic recovery of the database, based on a dump every 5 minutes. That should work without losing any speed.
