• This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn more.

Backups Innodb

#1
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!
 
#2
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 :)!
 

Brogan

XenForo moderator
Staff member
#3
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.
 
#8
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!
 
#10
Thanks all, just one final question before i make the switch.

I currently have this setting -

Code:
innodb_data_file_path= ibdata1:2G:autoextend
And I was thinking of changing it to this -

Code:
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?