Heikki, So if I understand this correctly, the following procedure could be used to create a hot backup from a slave which could be used to create a new slave server:
1) Create a hot backup of the innodb tables on theslave with ibbackup (with --suspend-at-end) 2) Create a hot backup of the MyISAM tables with the same kind of procedure as mysqlhotcopy once ibbackup suspends near the end of the backup (flush tables with read lock, copy the *.frm|*.MYI|*.MYD files) 3) Before unlocking the tables from the read lock get the master log file and log file position from the slave and save this information for setting up the new slave (from the slave, show slave status and store the values of Master_Log_File and Read_Master_Log_Pos) 4) unlock the tables, allow ibbackup to finish it's backup 5) Resume normal operation Personally I would much rather be able to setup new slaves from another Slave than having to take snapshots of the master, as this is disruptive in a mixed InnoDB/MyISAM environment (We are doing somewhere in the vicinity of 500 queries/sec in peak times). Regards, Mark Steele Implementation Director CDT Inc. [EMAIL PROTECTED] -----Original Message----- From: Heikki Tuuri [mailto:[EMAIL PROTECTED] Sent: March 5, 2004 1:28 PM To: [EMAIL PROTECTED] Subject: Re: slave hotbackup question Mark, http://www.innodb.com/manual.php#advanced.slave describes how to set up a new slave from a hot backup of a master. If you take a hot backup of a slave, and want to use it to set up a new slave, then the important coordinate is the MASTER binlog position that mysqld prints when you start mysqld on that new hot backup (do not mix this with the binlog position of the slave's own binlog). The new slave should start replication from that master binlog position. Relay logs etc. are not needed in the backup. Only 1) .frm files, 2) .MYI and .MYD files, 3) ibdata files, 4) ib_logfiles (these are generated in the ibbackup --apply-log phase), 5) .ibd files if you are using >= MySQL-4.1.1, 6) default charset files in database directories if you are using >= 4.1, and have worked with exotic charsets. Best regards, Heikki Innobase Oy InnoDB - transactions, row level locking, and foreign keys for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables http://www.innodb.com/order.php Register now for the 2004 MySQL Users Conference! http://www.mysql.com/events/uc2004/index.html ............... From: Mark Steele Date: March 3 2004 6:53pm Subject: slave hotbackup question Hi folks, I have a question regarding backups taken from a running slave. I have a slave replicating to a master server, and do a hot backup using the following script: ... My question is, suppose I would like to use one of these backups to create a new slave server, what additional information would I need to include in the backup to be able to start up a new slave properly? (relay logs/info files, etc...) Ideally, I would like these snapshots to be taken off the running server while it is in use, is there any way to do this? Regards, Mark Steele Implementation Director CDT Inc. [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
