Welcome to WebmasterWorld Guest from 54.197.94.141

Forum Moderators: open

Message Too Old, No Replies

Switching to innodb file per table

A way to convert a set of databases using innodb_file_per_table

   
2:45 pm on May 24, 2012 (gmt 0)

10+ Year Member



Hello,

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
innodb_file_format=Barracuda

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,

Marino
8:44 am on May 29, 2012 (gmt 0)

10+ Year Member



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.