Jason,
----- Original Message ----- From: "Jason Davis" <[EMAIL PROTECTED]>
Newsgroups: mailing.database.myodbc
Sent: Saturday, December 18, 2004 1:23 AM
Subject: ibbackup and mysql slaves
Hello, I have a mysql master and slave server. I use ibbackup to backup the master nightly. I have the latest version of ibbackup and mysql version 4.0.21. Every time I run ibbackup the slave stops replicating. I have to run the following commands twice...
SET GLOBAL SQL_SLAVE_SKIP_COUNTER =1; SLAVE START;
I have a script do this for me after ibbackup runs.
Is there a way I can fix this?
please post the .err log from the master and the slave.
Are you using the innobackup Perl script to back up the master?
http://www.innodb.com/manual.php#bugs.innobackup " A Bug in MySQL 4.0.22 and 4.1.7 (Bug #6732)
Warning: a CRITICAL bug in MySQL 4.0.22 and 4.1.7 causes the 2nd run of the innobackup Perl script to freeze the whole mysqld server. The bug was introduced in FLUSH TABLES WITH READ LOCK. The bug does not affect pure ibbackup.
This bug has been fixed in innobackup version 1.1.0. The bug fix has a little flaw in that there is a short time period in the backup run during which MyISAM table operations may cause innobackup to report a wrong binlog position to the user. The time period is between COMMIT and FLUSH TABLES WITH READ LOCK SQL statements which are issued by mysql_lockall subroutine of the innobackup script. This bug fix flaw affects only MySQL versions 4.0.22 and 4.1.7.
A Bug in MySQL 4.0.21 (Bug #5949)
A bug in MySQL version 4.0.21 affects innobackup backup operation. During a backup run innobackup issues the following SQL commands to MySQL client to temporarily lock all database tables:
USE mysql;
DROP TABLE IF EXISTS ibbackup_binlog_marker;
CREATE TABLE ibbackup_binlog_marker(a INT) TYPE=INNODB;
SET AUTOCOMMIT=0;
INSERT INTO ibbackup_binlog_marker VALUES (1);
FLUSH TABLES WITH READ LOCK;
COMMIT;
A bug in MySQL server (mysqld) may cause the commit to fail in a server while MySQL client reports success to innobackup script. If this happens, the locking of all database tables fails and the backup created by innobackup may be broken! In this case both BEGIN and COMMIT are labeled with error code 1223 ('Can't execute the query because you have a conflicting read lock') in the MySQL binlog, though they execute without any errors in the MySQL client.
This bug has been fixed in MySQL version 4.0.22. So this problem can be solved simply by upgrading from MySQL version 4.0.21 to version 4.0.22.
"
If it is one of the bugs above that is biting your slave, you can fix it by:
A) upgrading to 4.1.8;
or
B) waiting for 4.0.23 to be released;
or
C) upgrading to innobackup-1.1 and MySQL-4.0.22.
If the problem is that the internal data dictionary in the slave is out-of-sync of the .frm files in the slave, you can fix it by the advice at:
http://dev.mysql.com/doc/mysql/en/InnoDB_troubleshooting_datadict.html
Thanks, jd
Best regards,
Heikki Tuuri
Innobase Oy
Foreign keys, transactions, and row level locking for MySQL
InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables
http://www.innodb.com/order.php
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
