Re: slave hotbackup question
Mark, - Alkuperäinen viesti - Lähettäjä: "Mark Steele" <[EMAIL PROTECTED]> Vastaanottaja: "'Heikki Tuuri'" <[EMAIL PROTECTED]> Kopio: <[EMAIL PROTECTED]> Lähetetty: Monday, March 08, 2004 6:25 PM Aihe: RE: slave hotbackup question > 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) yes, this is what the innobackup Perl script http://www.innodb.com/manual.php#innobackup does. > 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) InnoDB internally stores in the slave the master binlog position up to which the slave was able to process queries. InnoDB prints out this information in crash recovery. You should use the master binlog file position that mysqld prints out when you start it on the backup you have taken (mysqld at the first start-up does a mock-up 'crash recovery'). Since COMMITs can happen also during the locked phase, there is a theoretic possibility that SHOW MASTER STATUS does not print the right position for the backup. > 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). I can understand this. > Regards, > > Mark Steele > Implementation Director > CDT Inc. > [EMAIL PROTECTED] 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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: slave hotbackup question
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]
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]
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:
#!/usr/bin/perl
use strict;
use DBI;
use File::Copy;
use POSIX ":sys_wait_h";
my $dbh =
DBI->connect("DBI:mysql:dbname=mysql;mysql_socket=/tmp/mysql.sock","user
","password") || die DBI->errstr();
## Create the backup folder
my ($d,$m,$y) = (localtime)[3..5];
my $date = sprintf("%d-%02d-%02d",$y+1900,$m+1,$d);
my $ibbackup_pid;
print "Creating backup folder : /storage/backups/backup-$date\n";
mkdir("/storage/backups/backup-$date") || die "couldn't create backup
dir: $!";
mkdir("/storage/backups/backup-$date/sql") || die "couldn't create
backup dir: $!";
## Parse the /etc/my.cnf
print "Parsing my.cnf\n";
open(F,"/etc/my.cnf") || die;
my $found = 0;
my %cfg;
while() {
chomp;
## Junk until we have start of [mysqld] section
if (!$found) {
next if (!/\[mysqld\]/i);
$found = 1;
next;
}
## We aren't interested in anything not relating to the [mysqld]
section
## stop reading when it's finished.
last if ($found && /^\s*\[/);
## Skip comments and empty lines
next if (/^(?:(?:#\|;).*|\s*)$/);
## Get rid of trailing space or ;
s/(?:|\s*)$//;
## Handle the set-variable=var=something
if (/^set-variable=(.*)$/) {
my ($var,$val) = split(/\s*=\s*/,$1);
$cfg{'set-variable'}{$var} = $val;
next;
}
## Split name/value pairs
my ($var,$val) = split(/\s*=\s*/);
$cfg{$var} = $val;
}
## At this point %cfg contains the relevant [mysqld] configuration
information.
## Create the backup config file
print "Creating /storage/backups/backup-$date/my-backup.cnf\n";
open(FILE, ">/storage/backups/backup-$date/my-backup.cnf") || die
"Failed to open file /storage/backups/backup-$date/my-backup.cnf: $!";
print FILE <<"(EOF)";
# This MySQL options file was generated by Mark's backup script.
[mysqld]
datadir=/storage/backups/backup-$date/sql
innodb_data_home_dir=/storage/backups/backup-$date/sql
innodb_data_file_path=$cfg{'innodb_data_file_path'}
innodb_log_group_home_dir=/storage/backups/backup-$date/sql
innodb_log_files_in_group=$cfg{'set-variable'}{'innodb_log_files_in_grou
p'}
innodb_log_file_size=$cfg{'innodb_log_file_size'}
(EOF)
close(FILE);
## Start the backup...
my $pid = undef;
if (defined($pid = fork)) {
if ($pid) {
# parent process
$ibbackup_pid = $pid;
} else {
# child process
print "Child process started\n";
print "Command: ibbackup --suspend-at-end --compress
/etc/my.cnf /storage/backups/backup-$date/my-backup.cnf\n";
exec("ibbackup --suspend-at-end --compress /etc/my.cnf
/storage/backups/backup-$date/my-backup.cnf") || die "Failed to exec
ibbackup: $!";
}
} else {
die "failed to fork ibbackup child process: $!";
}
print "Waiting for child to create
/storage/backups/backup-$date/sql/ibbackup_suspended\n";
while(1) {
sleep 5;
$dbh->do("SELECT UNIX_TIMESTAMP()");
last if -e
"/storage/backups/backup-$date/sql/ibbackup_suspended";
}
print "Starting backup of MyISAM tables\n";
## ibbackup is waiting for us to get rid of the suspended file.
## start backing up the files.
print "Locking tables\n";
$dbh->do("DROP TABLE IF EXISTS ibbackup_binlog_marker");
$dbh->do("CREATE TABLE ibbackup_binlog_marker(a INT) TYPE=INNODB");
$dbh->{AutoCommit} = 0;
$dbh->do("INSERT INTO ibbackup_binlog_marker VALUES (1)");
$dbh->do("FLUSH TABLES WITH READ LOCK");
$dbh->commit();
opendir(DIR,$cfg{'datadir'}) || die "can't open directory: $!";
while (my $f = readdir(DIR)) {
next if (!-d "$cfg{'datadir'}/$f" || $f =~ /^\.{1,2}$/);
mkdir("/storage/backups/backup-$date/sql/$f") || die "couldn't
create folder: /storage/backups/backup-$date/$f : $!";
my @list = glob("$cfg{'datadir'}/$f/*.{frm,MYD}");
my @listidx = glob("$cfg{'datadir'}/$f/*.MYI");
for (@list) {
$dbh->do("SELECT UNIX_TIMESTAMP()");
print "Copying $_\n";
copy("$_","/storage/backups/backup-$date$_") || die
"couldn't copy file $_: $!";
}
for (@listidx) {
print "Copying INDEX $_\n";
$dbh->do("SELECT UNIX_TIMESTAMP()");
open(FIDX,"<$_") || die "couldn't open index... ack!";
my $buff;
my $length = read(FIDX,$buff,2048);
#die "Can't read index header from $_\n" if ($length <
1024);
close(FIDX);
open(FIDX,">/storage/backups/backup-$date$_") || die
"couldn't open file for copy: $_: $!";
if (syswrite(FIDX,$buff) != length($buff)) {
die "Error when writing data to
/storage/backups/backup-$date$_: $!\n";
Re: HOTBACKUP
Arnoldus, you should add log-bin to the [mysqld] section of my.cnf, so that mysqld writes the binlog. Please see the MySQL online manual about this option. Best regards, Heikki Tuuri Innobase Oy http://www.innodb.com Foreign keys, transactions, and row level locking for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables Order MySQL technical support from https://order.mysql.com/ - Original Message - From: ""Arnoldus Th.J. Koeleman"" <[EMAIL PROTECTED]> Newsgroups: mailing.database.myodbc Sent: Friday, February 13, 2004 8:34 PM Subject: HOTBACKUP > --=_NextPart_000_0041_01C3F235.BEE2A9E0 > Content-Type: text/plain; > charset="us-ascii" > Content-Transfer-Encoding: 7bit > > I was reading the manual for Hot Backup for InnoDB and i was missing one > fundamental thing in the doc and that is setting the log-bin > > The document only speaks about > > The my.cnf files must contain the following parameter values: > > innodb_data_home_dir=... > > innodb_data_file_path=... > > innodb_log_group_home_dir=... > > innodb_log_files_in_group=... > > innodb_log_file_size=... > > > > And for recovering purpose log-bin in the [mysqld] section am I correct > for POINT IN TIME RECOVERING > > > --=_NextPart_000_0041_01C3F235.BEE2A9E0-- > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
HOTBACKUP
I was reading the manual for Hot Backup for InnoDB and i was missing one fundamental thing in the doc and that is setting the log-bin The document only speaks about The my.cnf files must contain the following parameter values: innodb_data_home_dir=... innodb_data_file_path=... innodb_log_group_home_dir=... innodb_log_files_in_group=... innodb_log_file_size=... And for recovering purpose log-bin in the [mysqld] section am I correct for POINT IN TIME RECOVERING
HOTBACKUP
I was reading the manual for Hot Backup for InnoDB and i was missing one fundamental thing in the doc and that is setting the log-bin The document only speaks about The my.cnf files must contain the following parameter values: innodb_data_home_dir=... innodb_data_file_path=... innodb_log_group_home_dir=... innodb_log_files_in_group=... innodb_log_file_size=... And for recovering purpose log-bin in the [mysqld] section am I correct for POINT IN TIME RECOVERING
HOTBACKUP INNODB
Title: Message I was reading the manual for Hot Backup for InnoDB and i was missing one fundamental thing in the doc and that is setting the log-bin The document only speaks about The my.cnf files must contain the following parameter values:innodb_data_home_dir=... innodb_data_file_path=... innodb_log_group_home_dir=... innodb_log_files_in_group=... innodb_log_file_size=... And for recovering purpose log-bin in the [mysqld] section am I correct for POINT IN TIME RECOVERING Arnoldus Th.J. KoelemanPrincipal Engineer NetNumber Inc.650 Suffolk Streetsuite 307Lowell MA, 01854 [EMAIL PROTECTED] tel: mobile: 978-848-2836978-397-1434 Powered by Plaxo Want a signature like this? Add me to your address book...
Re: HOTBACKUP
No, InnoDB hot backup only backs up the table data, not the definitions. You need to back those up separately. This is all explained here: http://www.innodb.com/manual.php#Backing_up_myisam_and_innodb There's also a perl script that can help you with the backup. Even though it says MyISAM, you also need this for the InnoDB table definitions. Good luck, Ware Arnoldus Th.J. Koeleman wrote: >I am implementing Hotbackup for innodb . > >My question is when the tables in the database directory so *.frm are >deleted by mistake will they be recovered by the hotbackup script? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
HOTBACKUP
I am implementing Hotbackup for innodb . My question is when the tables in the database directory so *.frm are deleted by mistake will they be recovered by the hotbackup script?
Making hotbackup
I am running a mysql database and want to perform a hotbackup on linux. How will this be done since the ibbackup tool is not delivered in the standard package
Re: Recover mysql database from hotbackup. This is Urgent!!!
Anne, - Original Message - From: "Anne" <[EMAIL PROTECTED]> Newsgroups: mailing.database.mysql Sent: Monday, January 27, 2003 7:35 PM Subject: Recover mysql database from hotbackup. This is Urgent!!! > Hi, > > We are performing daily hotbackup of the mysql database using the > script which uses innodb command. Some one has by mistake removed some > of the data in one of the table, Now we have to recover the database > using the backup file. I browse through the mysql and innodb web site, > I'm some what confused. > > What is the correct procedure to recover the database using the backup > file and binary log file. I really appreciate if anyone can help me > out. I'm new to mysql. maybe best to find the person who set up your MySQL installation, or someone else familiar with MySQL. If you mix data files or ib_logfiles, you may easily destroy your current production database. I guess you cannot shut down your production mysqld server? We assume you have taken a backup with the command ibbackup my.cnf yourbackupmy.cnf The basic procedure of restoring a table is this: 1. Run ibbackup --restore yourbackupmy.cnf That produces ibdata1, ..., ib_logfile0, ib_logfile1 to the directories specified in yourbackupmy.cnf 2. Start another mysqld server instance on those backup files. You have to specify a port number different from your production mysqld server. Below is an example of a my.cnf which uses a nonstandard port 3308: # Example mysql config file. # Copy this file to c:\my.cnf to set global options # # One can use all long options that the program supports. # Run the program with --help to get a list of available options # This will be passed to all mysql clients [client] socket=/home/heikki/slaveMySQLheikki port=3308 # Here is entries for some specific programs # The following values assume you have at least 32M ram # The MySQL server [mysqld] port=3308 socket=/home/heikki/slaveMySQLheikki basedir=/home/heikki/slave datadir=/home/heikki/slave/data set-variable = key_buffer=30M set-variable = max_allowed_packet=1M innodb_data_file_path = slavedata/ibdata1:10M:autoextend innodb_data_home_dir =/home/heikki innodb_log_group_home_dir = /home/heikki/slavedata innodb_log_arch_dir = /home/heikki/slavedata set-variable = innodb_log_files_in_group=3 set-variable = innodb_log_file_size=30M .. Edit that to suit your backup and use it: mysqld --defaults-file=my3308.cnf to start mysqld on the backup ibdata1 etc. files. If last night's copy is not ok for you, but you want the very latest copy of the table, and have the binlogs, pipe in the binlogs: " The position is the MySQL binlog byte position from the moment when InnoDB Hot Backup finished the copying of your data files. Then you can apply the binlog file(s) starting from that position to the restored database: mysqlbinlog --position=128760127 hundin-bin.006 | mysql --defaults-file=my3308.cnf " Then dump the table from the backup server: mysqldump --defaults-file=my3308.cnf --opt yourdatabase yourtable > dumpfile.sql Drop the table from the production server, and import the dump file: mysql yourdatabase < dumpfile.sql As you see, this all is quite complex. Best if you find someone familiar with MySQL to do the task. Regards, Heikki Innobase Oy sql query .. Restoring a backup: heikki@hundin:~/ib/back> ibbackup --restore --use-memory 200 /home/heikki/my2.cn f ibbackup version 0.33; Copyright 2002 Innobase Oy; license serial n:o A111; licensed for use in a computer whose hostname is 'hundin', license expires 2002-4-1 (year-month-day) at 00:00. See http://www.innodb.com for further information. Contents of /home/heikki/my2.cnf: innodb_data_home_dir got value /home/heikki/ibdatab innodb_data_file_path got value ibdata1:128M;ibdata2:1000M;ibdata3:2000M innodb_log_group_home_dir got value /home/heikki/iblogsb innodb_log_files_in_group got value 3 innodb_log_file_size got value 52428800 InnoDB: Doing recovery: scanned up to log sequence number 0 307366912 InnoDB: Doing recovery: scanned up to log sequence number 0 308677632 InnoDB: Doing recovery: scanned up to log sequence number 0 309988352 InnoDB: Doing recovery: scanned up to log sequence number 0 311299072 InnoDB: Doing recovery: scanned up to log sequence number 0 312609792 InnoDB: Doing recovery: scanned up to log sequence number 0 313920512 InnoDB: Doing recovery: scanned up to log sequence number 0 315231232 InnoDB: Doing recovery: scanned up to log sequence number 0 316541952 InnoDB: Doing recovery: scanned up to log sequence number 0 317852672 ... InnoDB: Doing recovery: scanned up to log sequence number 0 624561152 InnoDB: Doing recovery: scanned up to log sequence number 0 624631105 InnoDB: Starting an apply batch of log records to the database... InnoDB: Progress in percents: 0 1 2 3 4 5 6 7
