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 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 7 3 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 Setting log file size to 0 52428800 Setting log file size to 0 52428800 Setting log file size to 0 52428800 ibbackup: Full backup restoration successful! The first data file is ibbackup: /home/heikki/ibdatab/ibdata1 ibbackup: and the restored log files are at ibbackup: /home/heikki/iblogsb/ ibbackup: Now start mysqld on the restored backup, for example, using ibbackup: the backup-my.cnf file you specified when you called ibbackup: ibbackup --restore, and let InnoDB do a normal crash recovery, ibbackup: rolling back possible incomplete transactions. ibbackup: Then you have an operational restored database! heikki@hundin:~/ib/back> Starting mysqld on the restored backup: heikki@hundin:~/mysql-max-3.23.49-pc-linux-gnu-i686/bin> mysqld --defaults-file= /home/heikki/my2.cnf 020308 17:25:30 InnoDB: Database was not shut down normally. InnoDB: Starting recovery from log files... InnoDB: Starting log scan based on checkpoint at InnoDB: log sequence number 0 624631308 InnoDB: Last MySQL binlog file offset 0 128760128, file name ./hundin-bin.006 020308 17:25:30 InnoDB: Flushing modified pages from the buffer pool... 020308 17:25:30 InnoDB: Started mysqld: ready for connections .............................. > Thanks > Anne --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
