Here is a quick shell script to convert all tables in a database to InnoDB. No dependancies other than a command line prompt on a Un*x like system and the standard MySQL tools: for T in…
Post: Quick tip: how to convert tables to InnoDB
July 30, 2008 By 5 Comments
…tip. Suppose you have a bazillion tables to convert from MyISAM to InnoDB, but they are mixed in with other tables that are already InnoDB, or are another …000 tables in a bunch of different databases, all named things like user_123_456_friends. I wanted to add an index to them — but not to the…
Post: Unexpected problem with triggers and mysqldump
February 11, 2013 By 8 Comments
Some time ago, I had to convert all tables of a database from MyISAM to InnoDB on a new server. The plan was to take a logical dump on the master… > data.sql Just to check that everything is fine, let’s reimport the data in a new database: mysql> CREATE DATABASE sakila2; Query OK, 1…
Post: On Character Sets and Disappearing Tables
June 21, 2012 By 1 Comment
…to convert everything to ASCII. Or perhaps we have some other tables with ASCII identifiers and we’re doing JOINs against tables in this database…a new table, but you’ll need to go take a look inside the InnoDB data directory for this database to…
Post: Innodb row size limitation
April 7, 2011 By 12 Comments
…a given row on a single database page. Innodb has a limit for the maximum row size, which is slightly less than half a database…converting from a native language character set to…a BLOB instead of a TEXT column for storage. You can find more information about this functions here. Split the table in a…
Post: A recovery trivia or how to recover from a lost ibdata1 file
June 3, 2011 By 8 Comments
… tables With all this in place, recovery the data was just another Bash script: #!/bin/bash CURRENT_ID=1 mysql -u root “create database…. With a modified schema, the recovery completed. The customer was able to download the tables in MyISAM format and then he just convert them back to InnoDB.
Post: Lost innodb tables, xfs and binary grep
November 9, 2010 By 12 Comments
… DATABASE X on the wrong machine (production master) All slaves followed and dropped their copy of the data The important tables were all InnoDB Having a… the tool for the task. In the case number one, I have just converted number 837492636 to it’s binary representation 0x31EB1F9C and…
Post: How Percona does a MySQL Performance Audit
November 24, 2008 By 17 Comments
…databases Many people assume their application is difficult or somehow different, and that we need all…untuned to a system that’s already had a lot of expert attention. I use “tuning” in a generic … in Locked status, which might be an indication that it’s time to convert MyISAM tables to InnoDB. (But…
Post: How to recover deleted rows from an InnoDB Tablespace
February 20, 2012 By 4 Comments
…to explain three of them: A) INNODB_SYS_INDEXES Percona Server has some extra tables in INFORMATION_SCHEMA that can help us to…table_defs.h so it will be necessary to recompile the tool. To help us in the task of converting the schema definition to a…
Post: Recovering Innodb table Corruption
July 4, 2008 By 22 Comments
…in Innodb tablespace got corrupted. In such cases Innodb will typically print something like this: InnoDB: Database page corruption on disk or a failed InnoDB… all your data in MyISAM table so all you have to do is to drop old table and convert new table back to Innodb after restarting without innodb…
