I've got a FreeBSD system (named 'db1') running mysql 4.1.14 with innodb. I am running a dump onto another system in preparation for setting up a third system as a slave. (I can't use the "flush tables with read lock" because the db1 has a single tablespace file and I need to have per-database tablespace files on the slave.)
When I run the dump all the databases on db1 get a read lock and updates hang. The system has roughly 55GB of data in it and the dump will take a long period of time, longer than we can have the applications hang on inserts.
From the documentation I was not expecting read locks at all due to
innodb features. I read elsewhere a brief read lock is issued, but these don't seem to go away. From the manual here: http://mysql.he.net/doc/refman/4.1/en/mysqldump.html ################# --single-transaction This option issues a BEGIN SQL statement before dumping data from the server. It is useful only with transactional tables such as InnoDB and BDB, because then it dumps the consistent state of the database at the time when BEGIN was issued without blocking any applications. ################# Here is the relevant part of my dump script: mysqldump -uroot -h $host --single-transaction --master-data \ --flush-logs --all-databases 2>> $backuplog | gzip --fast > $dumpfile Version info: [EMAIL PROTECTED] [ /usr/local/mysql ] (10:19 PM - Fri Nov 17) $ ./libexec/mysqld --version ./libexec/mysqld Ver 4.1.14-log for unknown-freebsd5.3 on i386 (Source distribution) [EMAIL PROTECTED] [ / ] (10:17 PM - Fri Nov 17) $ mysqldump --version mysqldump Ver 10.7 Distrib 4.1.5-gamma, for portbld-freebsd5.3 (i386) Sample readlock state from 'show processlist \G' *************************** 9. row *************************** Id: 4537 User: user Host: 10.0.0.1:60214 db: replication_test Command: Query Time: 93 State: Waiting for release of readlock Info: UPDATE "blah" SET blah="blah" I know the versions are slightly off between the mysqldump client and the server but would 4.1.5 -> 4.1.14 make that big of a difference? Thanks in advance, Jacob -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
