homepage Welcome to WebmasterWorld Guest from
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member

Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderator: open

Databases Forum

Switching to innodb file per table
A way to convert a set of databases using innodb_file_per_table

10+ Year Member

Msg#: 4457402 posted 2:45 pm on May 24, 2012 (gmt 0)


I'm currently facing an issue with my huuuge mysql database. When I started my project, I didn't set the innodb_file_per_table in my.cnf
Now the ibdata1 file is ... hum 800 Mo and as you can guess, the whole thing is sluggish. More: I met the dreaded "Row size too large" bad guy...

So, I intend to convert to innodb_file_per_table and baraccuda. My concern is that I'd like to convert only one DB (the big one), and not the other ones.

I'm not an expert so I ask for wise advises.

1) Backup of all DBs
2) Set the flags in my.cnf

innodb_file_per_table = 1

3) Restart mysql
4) Copy the big DB into a new version of it using a PHP script with INSERT ... SELECT commands
5) Wipe the obsolete big DB

Is that right? This DB contains 120,000 tables. Don't laugh; it's a search engine with more than 15,000,000 websites. It's gonna be long (days!), but time and downtime is not important.
Will the other DBs remain ok?

For the moment, show variables like "%innodb_file%"; says:

| Variable_name | Value |
| innodb_file_format | Antelope |
| innodb_file_format_check | ON |
| innodb_file_format_max | Antelope |
| innodb_file_per_table | OFF |

Best regards to all,




10+ Year Member

Msg#: 4457402 posted 8:44 am on May 29, 2012 (gmt 0)

Still backing up...

After some searching, I've found a good tuto here: [dba.stackexchange.com...]

* backup all DB,
* wipe them all except mysql schema,
* stop mysql,
* delete ibdata1, ib_logfile0 and ib_logfile1,
* make changes to my.cnf,
* restat mysql,
* restore all the DBs.

Long days of fun.

Global Options:
 top home search open messages active posts  

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved