Am I doing something wrong, or does the innodb engine design preclude
loading a server with a readonly database snapshot?
I'm talking about 4.1.x (or maybe 4.x) and linux lvm snapshots (lvm2)
specifically about MySQL-4.1.12 (mysql-4.1.12-2.FC4.1.x86_64.rpm)
tested on an AMD-64 + FC4 (linux 2.6.11 (or .12, maybe)
kludges used debug build (--with-debug=full) variation of the rpm
I believe a common backup strategy (works for myisam) is the following:
----------------------------------------------------------------------
-flush tables with read lock
-lvmcreate -s (snapshot)
-unlock tables
-mount snapshot (readonly)
-mysqld_safe --defaults-file=path-to/my.cnf2 --err-log=/tmp/elog2
where my.cnf2 specifies the snapshot mount point as the datadir
-mysqldump --defaults-file=path-to/my.cnf2
--databases --opt testdb >testdb.dump
(..time passes..)
Then after completion of the dump, umount and lvremove the snapshot
-------------------------------------------------------------------
This seems to me to be a reasonably useful recipe -- does anyone have
issues with this statement?
For innodb I have made what I think is appropriate adjustments to the
config and procedure, but cannot get it to work.
The first problem is that mysqld fails to start because it cannot open
ibdata1 (..) for read-write (on the ro snapshot mount).
I can get some encouragement of sorta-almost getting it to work by:
-kludging the code in innobase/os/os0file.c to open the data files with
O_RDONLY and changing the locking to F_RDLCK.
-copying the ib_logfile[01] files to r/w disk and adjusting the
innodb_log* config
After the above, I can actually get the cnf2-server to load and even
respond to some simple select queries.
I did no extensive testing, but instead tried to run mysqldump. It seems
to proceed nicely -- but after a while fails with
..
InnoDB: Warning: we did not need to do crash recovery, but log scan
InnoDB: progressed past the checkpoint lsn 0 93795910 up to lsn 0
93795920
050811 9:53:24
InnoDB: Error: Write to file /mnt/scratch/mysql/ibdata1 failed at offset
0 1048576.
InnoDB: 16384 bytes should have been written, only -1 were written.
..
050811 9:53:24 InnoDB: Assertion failure in thread 46912496362752 in
file fil0fil.c line 3924
InnoDB: Failing assertion: ret
InnoDB: We intentionally generate a memory trap.
..
<server terminates>
Now I didn't try to debug mysqldump any further, because it seems there
may be pervasive assumptions that the ibdata files are writable (I guess
stemming from the "multi-user concurrency", or something like that).
Shame though, eh?
Q? Perhaps there are some further mods similar to the flavor of my
kludges, that allow innodb to run in a fully-readonly mode?
Q? Perhaps the opt_readonly is aready part of the solution?
Q? Perhaps there's a better way (preferably w/o buying InnoDB Hot
Backup).
Q? I do kinda feel that innodb (any database) really ought to allow a
readonly mode. Am I unreasonable?
SIDE ISSUE
**********
It *is* possible to get to get the dump to work by mounting the snapshot
read-write! No kludging, no moving of the logfiles required.
Q? Am I wrong to be squeamish about mounting lvm snapshots read-write?
Q? Has anybody done such -- and verified that the dumps are valid?
Q? Even if that works and is safe, am I out-of line asking for readonly?
-------------------------------------------------------------------------
Thanks all, for your forbearance!
...jim
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
