Backups Innodb

[lostincable]

Hi all, when I installed Xenforo I was running MyISAM so all my tables are now MyISAM.

I have enabled Innodb but before converting the required tables to Innodb I wanted to ask how you backup Innodb?

Currently I use automysqlbackup and compress the databse file and moves it offsite.

If I move the required tables to Innodb I end up with a mix of MyISAM and Innodb so can I still backup the same way?

I was reading about files for Innodb need to backed up as well??

Or does that relate to Tables as Files for Innodb and do I need to configure that option?

Any advice appreciated!

 

[lostincable]

Further to this I have been doing a bit of reasearch on Innodb and it seems a fair few people have corruption issues with Innodb from making config changes.

The only way out is to restore a backup, is Innodb that dangerous???

I have been using MyISAM for a long time now so a bit worried if its going to be a night mare !

 

[Paul B]

This is what Kier had to say:

We use MyISAM, InnoDB and Memory table types, depending upon the requirements of each table.
Each table type has its own benefits. For example, MyISAM is held back by table-level locking on writes whereas InnoDB only has to lock the particular row that is being written. However, if you can avoid locking, MyISAM is hugelyfaster for data writes than InnoDB. So it's not as simple as saying that one particular table type is better than another.

I have been using the default mix of MyISAM and InnoDB since the first beta, with no issues at all.
Just back the database up in the normal manner - I use phpMyAdmin, Gzipped.

 

[MattW]

I've had no problems with InnoDB on my server.

When I back up my database each night, I use the following command:

mysqldump --opt --single-transaction

http://www.mysqlperformanceblog.com/2012/03/23/best-kept-mysqldump-secret/

 

[lostincable]

Okay and do you have Innodb setup as one file or files per table?

http://www.electrictoolbox.com/mysql-innodb-one-file-per-table/

 

[MattW]

Okay and do you have Innodb setup as one file or files per table?

http://www.electrictoolbox.com/mysql-innodb-one-file-per-table/

Files per table

innodb_file_per_table=1

 

[HWS]

Files per table

innodb_file_per_table=1

This should really be the standard setting with INNODB.

You might also want to have a look at Percona XtraBackup. It is free and specialized in INNODB:
http://www.percona.com/software/percona-xtrabackup

 

[lostincable]

Files per table

innodb_file_per_table=1

Okay cheers everyone for the advice put my mind at ease wether I should do this or not !

So my plan is -

1) Backup existing database and re import on my test box to verify its ok and I have the latest snapshot

2) Configure my.cnf to enable Innodb including the files per table option as suggested

3) Restart mysql

4) Update the required tables on the XF database to Innodb

5) Export another backup and import it to my test box verifying its ok

From there I can just continue using automysql backup or XtraBackup to dump my backups.

Fingers crossed!

 

[MattW]

This should really be the standard setting with INNODB.

You might also want to have a look at Percona XtraBackup. It is free and specialized in INNODB:
http://www.percona.com/software/percona-xtrabackup

I played around with that when I first moved to Percona, but found it easier to stick with mysqldump as I then move all my backups off site.

 

[lostincable]

Thanks all, just one final question before i make the switch.

I currently have this setting -

innodb_data_file_path= ibdata1:2G:autoextend

And I was thinking of changing it to this -

innodb_data_file_path=inno_data_1:50M;inno_data_2:50M;inno_data_3:50M;inno_data_4:50M

http://xenforo.com/community/threads/changing-tables-from-myisam-to-innodb.14476/#post-189585

But I am worried about splitting it across multiple files and then if it reaches the 50MB limit what happens then?

What settings do others have?