| View previous topic :: View next topic |
| Author |
Message |
morodoch
Guru
Joined: 22 Sep 2005
Posts: 523
Location: England
|
 Posted: Thu Oct 13, 2005 2:29 pm Post subject: mysql backups using LVM2 snapshots? |
 |
|
I used to use LVM snapshots to do on-line backups of a couple of database servers. This worked well - it meant no downtime on the server.
It took a while for LVM2 to support snapshots, but it's been available for some time now, albeit marked as experimental.
Anyway, I've been trying to back up a server running 2.6.11-hardened-r15 using LVM2 snapshots, but it seems to break mysql in a couple of different ways.
The setup I'm using is as follows:
kernel 2.6.11-hardened-r15
mysql 4.0.25-r2
master - AMD opteron running in 64 bit mode
slave - pentium3
both master & slave are running the same kernel & mysql versions
I'm trying to back up on the slave, as I'm not brave enough to break the master database 
So here's what I do, all on the slave:
1. open a mysql session, and lock the tables read-only | Code: | | flush tables with read lock |
2. create a snapshot using | Code: | | lvcreate -pr -L500M -s -nbackup-snapshot "/path/to/logicalvolume" |
3. unlock the tables
4. mount the snapshot using | Code: | | mount -oro /dev/datavg/backup-snapshot /mnt/backup-snapshot |
5. back up the snapshot using tar
6. unmount & remove the snapshot | Code: | | umount /mnt/backup-snapshot| |
| Code: | | lvremove /dev/datavg/backup-snapshot |
I think this should work, but sooner or later mysql either dies altogether, or the slave stops replicating, with this in the mysqld error log:
Slave stopping (this can be recovered using /etc/init.d/mysql restart):
| Code: |
051013 15:01:17 /usr/sbin/mysqld: Incorrect information in file: './test/pbltest.frm'
ERROR: 1033 Incorrect information in file: './test/pbltest.frm'
051013 15:01:17 Slave: Error 'Incorrect information in file: './test/pbltest.frm'' on query. Default database: 'test'. Query: 'insert into pbltest values(3,4)', Error_code: 1033
051013 15:01:17 Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log 'balti-bin.035' position 4587886
|
Anyone got any ideas? Or have a working setup for this? Is it likely to be a mysql problem, an LVM2 one, or maybe a kernel problem? |
|
| Back to top |
|
 |
morodoch
Guru
Joined: 22 Sep 2005
Posts: 523
Location: England
|
| Posted: Thu Oct 13, 2005 2:45 pm Post subject: I guess it's not a mysql problem... |
|
|
As I was trying out the LVM snapshot, I had one of my users complain about apparent CVS differences (the CVS files are on the same partition as the mysql data files).
When I looked in the CVS file it was half full of nulls!
removing the snapshot fixed this problem.
I guess this means that creating a LVM2 snapshot affects the *original* copy of the file! Aargh! |
|
| Back to top |
|
|
morodoch
Guru
Joined: 22 Sep 2005
Posts: 523
Location: England
|
| Posted: Tue Oct 18, 2005 1:57 pm Post subject: |
|
|
FWIW, this problem has gone away after changing both the kernel & the device-mapper packages.
I've switched to gentoo-sources from hardened-sources, and updated the device-mapper to sys-fs/device-mapper-1.01.03.
I reckon it was the device-mapper package which was causing the problem, but I don't have time to check out these changes individually at the moment. |
|
| Back to top |
|
|
|
|
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
|
|
Other Things Gentoo
