The InnoDB Forums - InnoDB Hot Backup

using innodb hotbackup to convert to innodb_file_per_table ? (no replies)

tsstur — Thu, 02 Sep 2010 07:25:06 +0300

Question,
Can I use the innodb hotbackup restore to convert a really huge database (250+gig) to innodb_file_per_table instead of using mysqldump ?
if so, this would save us ~2 days downtime.

is this possible?

Did not receive email instructions after buying license (1 reply)

kevin-stanton — Tue, 31 Aug 2010 22:11:54 +0300

I purchased the 1 year license and did not receive the instructions via email to download ibbackup for linux. Can you please assist? I've checked my spam filter for anything @oracle.com and see nothing. I've also emailed the sales team. Last time we ordered the license the email came right away.

Thanks.

ibbackup , big data backup question (1 reply)

Infin1ty — Wed, 25 Aug 2010 05:32:54 +0300

We have an ibdata1 (only one) that is about 910GB in a live production system which we cannot shutdown for a copy process.
The situation is that we are running out of space at the moment in that server, we have a new server up and running with new disks and more space.
We have tried using mysqldump and import them later, it takes too much time.
Copying the ibdata1 (the whole mysql data dir and log dir as well) takes us around 11hours, which means we must shutdown our system to around 11hours.
We have a lot of writes to the database, we increase the ibdata file around 5GB avg per day.
I was thinking of using the ibbackup tool for that procedure, and wish to know if i can count on it or my only option is to shutdown the server.
This is what i was thinking regarding the routine to do it:
1. Shutdown everything that relates to working with mysql.
2. FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS and write it down.
Then compress the whole mysql data directory without the ibdata file (just the MyISAM tables).
Start ibbackup with --suspend-at-end and point the data through my2.cnf file to an nfs mount on the new server.
ibbackup --suspend-at-end
UNLOCK TABLES;
3. Allow everone to use the mysql db as normal use.
4. When ibbackup creates the 'ibbackup_suspend' file, Shutdown the system again (so none works against mysql)
Then FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS and write it down.
Compressing the whole mysql data dir again just for MyISAM tables.
Delete the 'ibbackup_suspend' wait for backup to finish
UNLOCK TABLES;
5. Start system again.
6. ibbackup --apply-log
7. add skip-slave-start in [mysqld]
8. then start the mysqld on the new server and change master to using the binlog file name using the one i took in 4th note and also the log pos took in the 4th note.
9. START SLAVE;

Will that work?
Then after that they both equal i'm shutting down first the old mysql db, then the new, making him a MASTER and telling all the system to use it.

Anything i missed?

2-part issue (1 reply)

nubmoore — Mon, 16 Aug 2010 13:14:26 +0300

1) I used ibbackup to take a backup, moved the files to a newly installed mysql server. I applied the logs, moved the db folder, data and logfiles to where the mysql server expects them to be.

I start mysql and sees the new db, giving me the current position as expected. However, when I run mysql, select the db, then do "show tables", nothing is returned.

2) I have 2 mysql servers, each with a different database using innodb. I need to move both of these databases to a single, new server. Will ibbackup work for this? As far as I can tell, it doesn't seem like this would work since the restore doesn't apply the logs to an existing db, only to the backup. Am I missing something?

innobackup corrupt (1 reply)

suzie — Mon, 16 Aug 2010 13:21:46 +0300

I've been intermittently receiving errors when running InnoDB Hot Backup.
A few weeks ago I repeatedly received errors that one particular table was corrupt, but
within about 5 days the issue resolved on it's own. Then again last night I
received errors that another table is corrupt, but the table appears to be fine
and I have no reason to believe that it is actually corrupt. I'm pasting a
portion of the error message I am receiving. I thought it strange that this time
the error message had some temporary tables listed in the error as well....

Any ideas on what is going on here? This has happened randomly in the past month, each
time resolving on it's own after a few days. I cannot trust this backup method with these
errors. In the mean time I am using mysqldump to run backups in addition to innobackup.

ibbackup: Re-reading page at offset 0 15745024 in /data/db/Order.ibd
ibbackup: Re-reading page at offset 0 1574100807 1:04:19 InnoDB: Page dump in ascii and hex (16384 bytes):
len 16384; hex 0b00000014000a026c6f73742b666f756e64000007c000001c0012012373716c313662625f3938625f302e696264626408c00000540012012373716c313662625f3938625f312e66726d726d0dc00000380012012373716c313662625f3936335f382e696264726d0fc000001c0012012373716c313662625f3936335f392e696264626410c000001c0012012373716c313662625f3938395f352e66726d6d6413c000001c0012012373716c313662625f3938395f362e696264726d14c000001c0012012373716c313662625f3938395f372e66726d646416c000001

asc lost+found #sql16bb_98b_0.ibdbd T #sql16bb_98b_1.frmrm 8 #sql16bb_963_8.ibdrm #sql16bb_963_9.ibdbd #sql16bb_989_5.frmmd #sql16bb_989_6.ibdrm #sql16bb_989_7.frmdd #sql16bb_989_8.frmmd #sql16bb_989_9.frmdd #sql16bb_98d_0.ibddd #sql16bb_989_5.ibdmd #sql16bb_98d_3.frmdm# #sql16bb_98d_4.ibddm #sql16bb_879_2.frmmd 4 #sql16bb_879_2.ibddm #sql16bb_879_3.frmdd# #sql16bb_879_4.ibdrm$ #sql16bb_87a_0.frmrm' #sql16bb_87a_1.ibdbd( #sql16bb_87a_2.frmmd+ #sql16bb_87a_3.ibdbd- p #sql16bb_87a_4.ibdmd1 T #sql16bb_980_1.ibdmd6 #sql16bb_980_4.frmmm7 #sql16bb_980_4.ibddm8 #sql16bb_979_0.frmdd9 #sql16bb_979_0.ibdmm< #sql16bb_979_2.frmdd= #sql16bb_979_2.ibddd@ #sql16bb_979_4.frmbd #sql16bb_983_8.frmmm #sql16bb_983_8.ibddd l #sql16bb_983_9.ibdbdD P #sql16bb_97f_1.frmbdG 4 #sql16bb_97f_2.ibdbdI #sql16bb_97f_3.ibdmdK #sql16bb_97f_4.ibdmmG #sql16bb_8cc_10.ibdm #sql16bb_8ce_15.frmd #sql16bb_8ce_15.ibddP #sql16bb_626_13.frmmR p #sql16bb_626_14.frmmT T #sql16bb_626_15.frmdV 8 #sql16bb_626_16.frmdG #sql16bb_64b_2a.ibd Z #sql16bb_626_12.frm [ #sql16bb_626_12.ibd S #sql16bb_626_14.ibd U #sql16bb_626_15.ibd _ #sql16bb_51f_4.ibd h #sql16bb_517_9.frm i X #sql16bb_517_9.ibd f #sql16bb_517_3.frm h #sql16bb_517_4.frm #sql2100_1ea7_3ca.frm q 4 #sql2100_1ea7_3ca.ibd #sql2100_1ea7_fe.frmP #sql2100_1ea6_40.frmh #sql2100_1ea7_341.frm i #sql2100_1ea7_341.ibd #sql2100_1ea7_f4.frmQ #sql2100_1ea6_40.ibdn #sql2100_1ea7_3d1.frm o ` #sql2100_1ea7_3d1.ibd o @ #sql2100_1ea7_3cc.ibdsqls #sql2100_1ea7_3ad.ibd d #sql2100_1ea7_22e.frmsqle #sql2100_1ea7_22e.ibd100s l
;InnoDB: End of page dump
100807 1:04:19 InnoDB: Page checksum 4192873710, prior-to-4.0.14-form checksum 3968187153
InnoDB: stored checksum 184549376, prior-to-4.0.14-form stored checksum 0
InnoDB: Page lsn 1852047360 130023424, low 4 bytes of lsn at page end 0
InnoDB: Page number (if stored to page already) 335546882,
InnoDB: space id (if created with >= MySQL-4.1.1 and stored already) 1650614073
5024 in /data/db/Order.ibd
ibbackup: Re-reading page at offset 0 15745024 in /data/db/Order.ibd
ibbackup: Error: page at offset 0 15745024 in /data/db/Order.ibd seems corrupt!
innobackup: Error: ibbackup child process has died at /home/mysql/bin/innobackup line 391.

Can't create table './mysql/ibbackup_binlog_marker.frm' (errno: 121) (3 replies)

andreas.fey — Mon, 16 Aug 2010 13:24:24 +0300

Hello,

After doing several hot backups successfully, the sollowing error message drops randomly:

innobackup: Starting to lock all tables...
innobackup: Error: mysql child process has died: ERROR 1005 (HY000) at line 7: Can't create table './mysql ibbackup_binlog_marker.frm' (errno: 121)
 while waiting for reply to MySQL request: 'CREATE TABLE ibbackup_binlog_marker(a INT) ENGINE=INNODB;' at innobackup.pl line 390

There are several hints to solve this issue (creating a new table and exchanging .frm files), but I would prefer to have the script just working; Does anybody know whats the problem here?

need help (no replies)

umar — Wed, 14 Jul 2010 08:37:39 +0300

Dear All!

I have a database server with a alot of different databases.

But I want only 1 database backup and that database has many InnoDB and MyISAM tables with millions of records. I don't want to lock any table during backup. Can innodb hotbackup do that?.

Please help.

Regards,

Umar

can't order hotbackup online (1 reply)

ivy — Mon, 16 Aug 2010 13:26:43 +0300

I tried to order a 1-year license online for the InnoDB Hotbackup but I keep getting an error saying that the credit card number should be 13-19 numbers. I'm trying to use a Visa card with 16 numbers so I'm not sure what's wrong. Could there be a bug in the online ordering system?

tmp tables (3 replies)

suzie — Thu, 26 Aug 2010 06:34:35 +0300

There is a known bug when using ibbackup/innobackup on a database that has
temporary tables(see below). Is there a way around this bug or any known fix?
If I recover a database that has temporary tables when the backup
is taken, will the database I recover be corrupt?

I am getting these errors:

100707 12:29:51 InnoDB: Error: trying to open a table, but could not
InnoDB: open the tablespace file './tmp/#sql168e_1db_2.ibd'!
InnoDB: Have you moved InnoDB .ibd files around without using the
InnoDB: commands DISCARD TABLESPACE and IMPORT TABLESPACE?
InnoDB: It is also possible that this is a temporary table #sql...,
InnoDB: and MySQL removed the .ibd file for this.

Thanks,
Suzie

********************************************************************************************************
7.3 Known ibbackup and innobackup bugs

If you take a backup when there are TEMPORARY tables in the database, and you use those temporary tables to update or insert into normal tables, then the MySQL binlog application to a backup can fail. That is, you may not be able to roll forward the backup using the MySQL binlog. The reason is that TEMPORARY tables are not copied to the backup. And, actually we cannot copy them to the backup, because the names of temporary table files #sql….frm do not correspond to the logical table names that MySQL writes to the binlog. This problem will be removed in the future, when MySQL will implement so-called 'row-level binlogging'.

MySQL versions < 4.1.2 do not prevent CREATE TABLE or RENAME TABLE during the locked phase. Therefore, there is a small chance that the .frm files of newly created empty tables do not correspond to the binlog position of the backup.
**********************************************************************************************************

innobackup - MySQL server has gone away (1 reply)

soonyu — Mon, 10 May 2010 09:08:36 +0300

I have this problem with innobackup when the script start backup myisam file

innobackup: Error: mysql child process has died: ERROR 2006 (HY000) at line 19:
MySQL server has gone away

Currently mysql setting as below
wait_timeout=3
interactive_timeout=3

and innobackup setting as below
my $mysql_response_timeout = 20800;
my $mysql_keep_alive_timeout = 2;

Is this because the mysql been disconnect because connection de-active in mysql for more than 3 second?

If i add this line in innobackup script, will this prevent this from happen ?

mysql_send "SET wait_timeout=28800;";

under this sub route ?
sub mysql_lockall

any advise?

Cheers

Can't start server on innobackup's output (no replies)

beandog — Tue, 04 May 2010 22:40:39 +0300

I created a directory called /root/backup. I then ran

./innobackup --user=root /etc/my.cnf /root/backup

Everything looks good. I then ran

./innobackup --apply-logs /etc/my.cnf /root/backup/2010-05-04_13-13-47

Everything still looks good. I then ran (as root):

cat /etc/my.cnf /root/backup/2010-05-04_13-13-47 > /root/backup/2010-05-04_13-13-47/my.cnf
/usr/libexec/mysqld --defaults-file=/root/backup/2010-05-04_13-13-47/my.cnf

I get the following output:
100504 13:34:44 InnoDB: Operating system error number 13 in a file operation.
InnoDB: The error means mysqld does not have the access rights to
InnoDB: the directory.
InnoDB: File name /root/backup/2010-05-04_13-13-47/ibdata1
InnoDB: File operation call: 'create'.
InnoDB: Cannot continue operation.

How can the root user not have permission to edit that file? More importantly, why is it trying to create that ibdata1 file when it already exists?

Ben Dilts

omit certain table during backup ? (1 reply)

tsstur — Mon, 26 Apr 2010 11:23:36 +0300

Hello,
Is it possible to omit a certain table during the backup of a MySQL database?
from the help file, there dosn't seem to be an omit-table or skip-table option.

is this possible?

thanks.
tom.

cannot open the database from innodb hot backup (no replies)

Coway — Mon, 12 Apr 2010 18:38:36 +0300

I backed up a small size of 50G db using innodb. After applying logs, I failed in opening the database. It stuck forever in opening tables, which showed from mytop. The restored database is just unusable.

Here is the log during the backup and restore processes.

System: redhat 5.4 64bit + MySQL 5.1.42 + innodb plugin 1.0.6.

No barracuda format was used. Using innodb plugin is to get fast indexing or some performance gain if there is any.

innodb temporary tables were created by applications. MySQL support suspects it may be the cause.

TABLE: name mss/#sql-1d26_8, id 0 510, columns 20, indexes 3, appr.rows 20335
COLUMNS: SecurityCheckID: DATA_INT DATA_UNSIGNED DATA_BINARY_TYPE DATA_NOT_NULL len 4; TagName: DATA_VARCHAR prtype 524559 len 60; CheckName: DATA_VARCHAR prtype 524303 len 40; CheckDescription: DATA_VARCHAR prtype 524303 len 255; CheckDetailDescription: DATA_BLOB len 10; ConsequenceName: DATA_VARCHAR prtype 524303 len 40; ConsequenceDescription: DATA_VARCHAR prtype 524303 len 255; DateEntered: DATA_INT DATA_BINARY_TYPE len 8; DateReported: DATA_INT DATA_BINARY_TYPE len 8; DateChanged: DATA_INT DATA_BINARY_TYPE len 8; VulnStatus: DATA_FIXBINARY DATA_BINARY_TYPE DATA_NOT_NULL len 1; Obsolete: DATA_FIXBINARY DATA_BINARY_TYPE DATA_NOT_NULL len 1; Discoverer: DATA_VARCHAR prtype 524303 len 255; ItemAffected: DATA_VARCHAR prtype 524303 len 255; ReplacedBy: DATA_INT DATA_BINARY_TYPE len 4; XForceFlag: DATA_INT DATA_BINARY_TYPE len 1; Severity: DATA_INT DATA_UNSIGNED DATA_BINARY_TYPE len 1; DB_ROW_ID: DATA_SYS prtype 256 len 6; DB_TRX_ID: DATA_SYS prtype 257 len 6; DB_ROLL_PTR: DATA_SYS prtype 258 len 7;
INDEX: name PRIMARY, id 0 1109, fields 1/19, uniq 1, type 3
root page 3, appr.key vals 20335, leaf pages 809, size pages 865
FIELDS: SecurityCheckID DB_TRX_ID DB_ROLL_PTR TagName CheckName CheckDescription CheckDetailDescription ConsequenceName ConsequenceDescription DateEntered DateReported DateChanged VulnStatus Obsolete Discoverer ItemAffected ReplacedBy XForceFlag Severity
INDEX: name TagName, id 0 1110, fields 1/2, uniq 1, type 2
root page 4, appr.key vals 16345, leaf pages 64, size pages 97
FIELDS: TagName SecurityCheckID
INDEX: name Index_3, id 0 1111, fields 1/2, uniq 2, type 0
root page 5, appr.key vals 8, leaf pages 15, size pages 16
FIELDS: Severity SecurityCheckID
--------------------------------------
TABLE: name mss/#sql-1d4f_3, id 0 511, columns 20, indexes 3, appr.rows 22863
COLUMNS: SecurityCheckID: DATA_INT DATA_UNSIGNED DATA_BINARY_TYPE DATA_NOT_NULL len 4; TagName: DATA_VARCHAR prtype 524559 len 60; CheckName: DATA_VARCHAR prtype 524303 len 40; CheckDescription: DATA_VARCHAR prtype 524303 len 255; CheckDetailDescription: DATA_BLOB len 10; ConsequenceName: DATA_VARCHAR prtype 524303 len 40; ConsequenceDescription: DATA_VARCHAR prtype 524303 len 255; DateEntered: DATA_INT DATA_BINARY_TYPE len 8; DateReported: DATA_INT DATA_BINARY_TYPE len 8; DateChanged: DATA_INT DATA_BINARY_TYPE len 8; VulnStatus: DATA_FIXBINARY DATA_BINARY_TYPE DATA_NOT_NULL len 1; Obsolete: DATA_FIXBINARY DATA_BINARY_TYPE DATA_NOT_NULL len 1; Discoverer: DATA_VARCHAR prtype 524303 len 255; ItemAffected: DATA_VARCHAR prtype 524303 len 255; ReplacedBy: DATA_INT DATA_BINARY_TYPE len 4; XForceFlag: DATA_INT DATA_BINARY_TYPE len 1; Severity: DATA_INT DATA_UNSIGNED DATA_BINARY_TYPE len 1; DB_ROW_ID: DATA_SYS prtype 256 len 6; DB_TRX_ID: DATA_SYS prtype 257 len 6; DB_ROLL_PTR: DATA_SYS prtype 258 len 7;
INDEX: name PRIMARY, id 0 1112, fields 1/19, uniq 1, type 3
root page 3, appr.key vals 22863, leaf pages 809, size pages 865
FIELDS: SecurityCheckID DB_TRX_ID DB_ROLL_PTR TagName CheckName CheckDescription CheckDetailDescription ConsequenceName ConsequenceDescription DateEntered DateReported DateChanged VulnStatus Obsolete Discoverer ItemAffected ReplacedBy XForceFlag Severity
INDEX: name TagName, id 0 1113, fields 1/2, uniq 1, type 2
root page 4, appr.key vals 21441, leaf pages 64, size pages 97
FIELDS: TagName SecurityCheckID
INDEX: name Index_3, id 0 1114, fields 1/2, uniq 2, type 0
root page 5, appr.key vals 4, leaf pages 15, size pages 16
FIELDS: Severity SecurityCheckID
--------------------------------------

corupt .ibd file after rebuild of underlying raid controller (1 reply)

jerome — Mon, 12 Apr 2010 14:49:47 +0300

Hello there,

after a rebuild of the underlying raid-controller, ibbackup refuses to backup the database. The database is running and stable, but the backup does crash every time:

ibbackup: Re-reading page at offset 0 920748032 in /data/3306/wkw_forum16/posting.ibd
ibbackup: Re-reading page at offset 0 920748032 in /data/3306/wkw_forum16/posting.ibd
ibbackup: Re-reading page at offset 0 920748032 in /data/3306/wkw_forum16/posting.ibd
ibbackup: Re-reading page at offset 0 920748032 in /data/3306/wkw_forum16/posting.ibd
ibbackup: Re-reading page at offset 0 920748032 in /data/3306/wkw_forum16/posting.ibd
ibbackup: Re-reading page at offset 0 920748032 in /data/3306/wkw_forum16/posting.ibd
ibbackup: Re-reading page at offset 0 920748032 in /data/3306/wkw_forum16/posting.ibd
ibbackup: Re-reading page at offset 0 920748032 in /data/3306/wkw_forum16/posting.ibd
ibbackup: Re-reading page at offset 0 920748032 in /data/3306/wkw_forum16/posting.ibd
ibbackup: Re-reading page at offset 0 920748032 in /data/3306/wkw_forum16/posting.ibd
ibbackup: Re-reading page at offset 0 920748032 in /data/3306/wkw_forum16/posting.ibd
ibbackup: Re-reading page at offset 0 920748032 in /data/3306/wkw_forum16/posting.ibd
ibbackup: Re-reading page at offset 0 920748032 in /data/3306/wkw_forum16/posting.ibd
ibbackup: Re-reading page at offset 0 920748032 in /data/3306/wkw_forum16/posting.ibd
ibbackup: Re-reading page at offset 0 920748032 in /data/3306/wkw_forum16/posting.ibd
ibbackup: Re-reading page at offset 0 920748032 in /data/3306/wkw_forum16/posting.ibd
ibbackup: Error: page at offset 0 920748032 in /data/3306/wkw_forum16/posting.ibd seems corrupt!

On the same machine are other databases and tables without any problems. Is there any way to fix this, without reimporting the whole database? Does anyone else experience this problem?

Kind regards,

Jérôme

Can not find a valid check point (1 reply)

linalong — Mon, 12 Apr 2010 17:38:18 +0300

When I did back up, I encounter such error. For the other data base, using the same command, I don't have such error.

Contents of /nobackup/ses/klocwork/backup/source.cfn:
innodb_data_home_dir got value step-lnx64-001.8082/data
innodb_data_file_path got value ibdata1:10M:autoextend
datadir got value step-lnx64-001.8082/data
innodb_log_group_home_dir got value step-lnx64-001.8082/data
innodb_log_files_in_group got value 2
innodb_log_file_size got value 5242880

Contents of /nobackup/ses/klocwork/backup/target.cfn:
innodb_data_home_dir got value /nobackup/ses/klocwork/backup/stages
innodb_data_file_path got value ibdata1:10M:autoextend
datadir got value /nobackup/ses/klocwork/backup/stages
innodb_log_group_home_dir got value /nobackup/ses/klocwork/backup/stages
innodb_log_files_in_group got value 2
innodb_log_file_size got value 5242880

ibbackup: Error: could not find a valid checkpoint from step-lnx64-001.8082/data/ib_logfile0,
ibbackup: Run 'ibbackup --help' for help.
innodb backup failed

Ibbackup freezes MySQL? (1 reply)

marcus — Mon, 12 Apr 2010 14:56:02 +0300

Every early morning when we run ibbackup (without innobackup, we have our own perl script), there are 1-2 minutes when MySQL seems to freeze. It does not accept any connections during this period and all of the currently connected threads seem to freeze.

Red Hat Enterprise Linux AS release 4 (Nahant Update 5)
Linux db1 2.6.9-55.ELsmp #1 SMP Fri Apr 20 16:36:54 EDT 2007 x86_64 x86_64 x86_64 GNU/Linux
MySQL-server-community-5.0.41-0.rhel4
InnoDB Hot Backup version 3.0.0; Copyright 2002-2005 Innobase Oy

The time stamps below are from today's run. It looks pretty much the same every day, but the connection issues occur on different times, but always 1-2 consecutive minutes, and never during more than one period every day.

Our backup script launches at 05:45.
At 05:50 it is done with the log, and starts copying the data files.
At 07:39 ibbackup is done, and our perl scripts starts to backup the frm files.
At 07:40 backup is done, and starts to scp to a remote server
At 08:16 scp finishes.

At 06:44 - 06:46 we get connection errors from our webservers.

I ran a script this morning that connected at 06:00 and ran a SHOW FULL PROCESSLIST; once per second dumping the result to a text file The script kept running until 08:00. At 06:44 it is as if everything freezes.

A few connections are stuck in unauthenticated user/Connect/login. Some normal queries are stuck in executing, some Sending data, etc. It looks like a normal processlist does on that server. However, it doesn't change 2 minutes, except for the Time value increasing, of course.

After about 2 minutes things go back to normal, and the queries proceed, new connections are being allowed, etc.

I'm a bit clueless. If it weren't for my PROCESSLIST-script running on a different server, connecting via a socket, it was almost as if the server lost network connection for a while.

It never happens other that while we are running ibbackup.

Any ideas on how to troubleshoot this further?

Marcus

too many open files (2 replies)

Coway — Mon, 12 Apr 2010 18:41:01 +0300

During the apply-log step, I am experiencing the

InnoDB: Doing recovery: scanned up to log sequence number 5556 1228336622
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 73 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 100324 19:08:12 InnoDB: Operating system error number 24 in a file operation.
InnoDB: Error number 24 means 'Too many open files'.
InnoDB: Some operating system error numbers are described at
InnoDB: [dev.mysql.com]
InnoDB: File name /dbbackup/atl-prd-consdb-01a/2010-03-17_13-38-14/mss/fw_daily_connection_summary#P#FW_DAILY_CONNECTION_SUMMARY_01Jul2008#SP#FW_DAILY_CONNECTION_SUMMARY_01Jul2008sp1.ibd
InnoDB: File operation call: 'open'.
InnoDB: Cannot continue operation.
innobackup: Error:
innobackup: ibbackup failed at /home/apps/mysql/scripts/innobackup line 391.

I still got this error even I added this in /etc/security/limits.conf

mysql soft nofile 65536

show variables displays:

| open_files_limit | 65536
| innodb_open_files | 45000

The total number of *.ibd files in the backup is 5000.

Can somebody explain why the error still happens?

Thanks,

Coway

ETA for hotbackup usage with innodb plugin (1 reply)

misaochankun — Fri, 21 May 2010 23:24:05 +0300

Is there any idea when we will be able to use the hot backup with the innodb plugin?
We'd like to take advantage of the plugin's performance, but can not move on until we can back it up too.

The disk usage of ibdata file is not as expected (6 replies)

shridharperka — Mon, 15 Mar 2010 16:07:05 +0200

Hi

I have observed the disk usage of ibdata file which is not as expected.

There is one table XYZ in our database. I inserted 1.5 million records into the table and the ibdata file size grew to 1.8GB. But when I deleted all the records, the size remained same.

Next time I inserted 2 million records and the size of ibdata this time was 2.0GB. When I deleted all the records it became 2.3GB.

Again I inserted 3 million records, the size was 2.6GB.

I feel this is an inappropriate usage of disk space. Can you please explain the scenario and any ways to optimise the disk usage?

Thanks.

Hotbackup Windows x64 (1 reply)

mogorman — Wed, 03 Mar 2010 23:17:54 +0200

Hi

Is Innodb Hot backup compatible with Windows 2008 64bit
I have successfully run a backup on this o/s but I am keen to know if there are any incompatibilities

Regards

Martin

license issue (3 replies)

linalong — Mon, 12 Apr 2010 17:10:14 +0300

Hi,
We bought license with the host name step-lnx64-001.cisco.com. But when we use it, it has error:

step-lnx64-001:/nobackup/ses/klocwork/backup> /auto/klocwork_data/ibbackup/ibbackup1 source.cfn target.cfn
InnoDB Hot Backup version 3.0.0; Copyright 2002-2005 Innobase Oy
License A18206 is granted to Cisco Systems
(--apply-log works in any computer regardless of the hostname)
Licensed for use in a computer whose hostname is 'step-lnx64-001.cisco.com'
Expires 2011-2-1 (year-month-day) at 00:00
See [www.innodb.com] for further information
Type ibbackup --license for detailed license terms, --help for help

--- Error: the hostname of this computer is 'step-lnx64-001'.
Please contact Heikki.Tuuri@innodb.com for a license renewal.
step-lnx64-001:/nobackup/ses/klocwork/backup> uname -a
Linux step-lnx64-001 2.6.9-42.7.ELsmp #1 SMP Tue Sep 5 18:25:41 EDT 2006 x86_64 x86_64 x86_64 GNU/Linux

When we ordered the license, We tried host name step-lnx64-001 instead of step-lnx64-001.cisco.com. But the web didn't let us go through. In cisco, step-lnx64-001.cisco.com and step-lnx64-001 are same. I sent email to Heikki.Tuuri@innodb.com to ask the license correction. But no response. Please reply as soon as possible. Thanks.

Do we lose data during backup/restore with innodb? (3 replies)

shridharperka — Wed, 03 Mar 2010 13:57:26 +0200

Hi

While taking a backup, we have data in the log files which is not written into the data file. During backup it creates ibbackup_logfile to log events during backup. But what happens to the data in the ib_logfile* during backup?

When we restore this data we apply only ibbackup_logfile which has events during backup. So are we losing data that was there in the log files while backup was taken?

Please explain. You can do a favor by explaining the entire backup/restore process, how the data flows and how it is preserved, if it is.

Thanks & Regards
Shridhar

ibbackup: Error: file /var/lib/mysql/ibdata1 size is 1052688 pages, but in my.cnf was 1052672 pages (no replies)

rsank — Tue, 16 Feb 2010 08:58:34 +0200

Hello,
I am using the evaluation version (3.00) of ibbackup. I had the following problem with my database, the ibdata1 file went over the limit imposed by the OS. Mandriva Linux 2007 apparently has a default ext3 file size limit of 16Gb, which I never knew.

The size of my ibdata1 is now 17247252480. I had to create a second ibdata file and made the following change to my.cnf:
innodb_data_file_path = ibdata1:16448M;ibdata2:200M:autoextend

The database is now running ok and inserts are not failing any more.

I am trying to run ibbackup with the same innodb_data_file_path as listed above, but I get the following error:

ibbackup: Error: file /var/lib/mysql/ibdata1 size is 1052688 pages, but in my.cnf was 1052672 pages

I am stuck. All my calculations bring me back to 16448M as the size of ibdata1. How can I fix this?

Thanks in advance for your help.

version check fails with Perl 5.10 (no replies)

snoyes — Thu, 04 Feb 2010 15:30:07 +0200

With Perl 5.10, the following is reported:

Version string '♣' contains invalid data; ignoring: '♣' at innobackup-1.5.2 line 1273.

Line 1273 is:

if ($^V lt $perl_version) {

The reason is the change in Perl 5.10 that treats $^V as a version object instead of as a v-string.

The 22 lines of version checking can be replaced with just one line:

require $required_perl_version_old_style;

error while loading shared libraries: libz.so.1 (4 replies)

soonyu — Mon, 01 Feb 2010 09:25:03 +0200

error while loading shared libraries: libz.so.1: cannot open shared object file: No such file or directory

I just purchase innodbbackup but when run face this problem

ANyone face this before?

cannot download the evaluation copy of InnoDB Hot Backup (7 replies)

lolworld — Thu, 17 Dec 2009 09:52:14 +0200

I got the email for downloading the evaluation copy of InnoDB Hot Backup, upon downloading, just get a 436KB "ibbackupA17787" file!? Any suggestion.

installing innodb hot backup on restore server (no replies)

gunderjj — Mon, 16 Nov 2009 22:31:51 +0200

I have a production box I have innodb hot backup installed on and I have made a backup and copy the compressed backup over to a server I want to restore it on and try to run --apply-log, but innodb did not seem to install properly. I know when intalled innodb hot backup is tied to the server name. What binary do I get to run on a server where I just want to apply logs? After I apply logs do I just copy these files to the appropriate folder or do I type restore?

innodb hotbackup leaves /var/tmp file (1 reply)

csross — Fri, 06 Nov 2009 00:57:12 +0200

I have hotbackup running on a solaris 8 server every hour. The hotbackup version is older, 3.0-beta. I have a script that mounts an nfs or ufs drive for the output of the backup.

I noticed in /var/tmp 4 identical files with every backup

aaaxwaqvp is mysql Ver 14.12 Distrib 5.0.22, for sun-solaris2.8 (sparc) using EditLine wrapper
The other file is the END-USER LICENSE AGREEMENT.

-rw-r--r-- 1 root other 84 Nov 3 10:00 aaaxwaqvp
-rw-r--r-- 1 root other 0 Nov 3 10:00 baaywaqvp
-rw-r--r-- 1 root other 7524 Nov 3 10:00 caazwaqvp
-rw-r--r-- 1 root other 0 Nov 3 10:00 daaAwaqvp

Is there something that I can do to have the program automatically delete these files after the backup is done?

Thank you
Christine

Resotring on another host (1 reply)

groc — Thu, 22 Oct 2009 11:23:33 +0300

Hello,
I am trying to copy an innodb db from source db server to target db server in order to setup target db as a replication slave.

On the source, I have created innodb backups using ibbackup my.cnf backup.cnf. I then ran ibbackup --apply-log backup.cnf on the source. After that I copied the database files over to the destination, followed by the frm files. When I start the destination server, I am getting an error [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed. Not sure on how to fix this issue? I have innodb enabled on the destination.

Thanks.

InnoDB Hot Backup Problem (1 reply)

Bluebox — Tue, 20 Oct 2009 09:18:49 +0300

We have a client we are using InnoDB Hot Backup on.

Customer has no MyISAM tables.
Database being backed up is running on a read only slave (the only writes come in on the replication setup)
Read load is not inconsiderable, but is manageable.
Tables range from a few MB to over 100GB, approx 225GB total
innodb log files are 2x1.5GB

We can get a clean backup for a couple of days, then the backup fails with the following error.

091014 9:13:44 ibbackup: Switching to log file 0, lsn 3825 195144704
ibbackup: Error: log scan was only able to reach to 3825 1970383872,
ibbackup: but a checkpoint was at 3825 2870678389
ibbackup: This means that the database server has overwritten
ibbackup: a part of the circular InnoDB log files before ibbackup
ibbackup: was able to read it.
ibbackup: To solve this problem either
ibbackup: 1) re-run the backup when the database has less load, or
ibbackup: 2) re-configure the database with bigger InnoDB log files.
innobackup: Error: ibbackup child process has died at /usr/local/bin/innobackup.pl line 357.
Problems running InnoDB Hot Backup. See /var/log/mysql_innobackup.log for details.

Restarting MySQL seems to clear up the problem for a couple more days (not verified, only done this once. We are not able to restart mysql without coordinating with our customer for obvious reasons.)

Backup fails anywhere from 2 to 10 hours after starting. When it succeeds, it does so within about 5 hours.

We are supposing that there is some query in the log that is taking a long time to process, and is clogging up the backup, but don't know for sure.

Are there any steps we can take to get some more verbose errors from InnoDB Hot Backup?