Top Nav

Archive | MySQL

Scripting CHECK / REPAIR TABLES

Here’s a one-liner that will check all tables in a database:

http://codeinthehole.com/writing/checking-all-mysql-tables/

And here’s an article that shows the use of mysqlcheck:

http://www.thegeekstuff.com/2011/12/mysqlcheck/

mysqlcheck, unlike myisamchk, can be run when the mysql daemon is running

This command will check, repair and optimize all tables in all databases:

 

0

MySQL Error: Column count of mysql.proc is wrong. Expected 20, found 16. The table is probably corrupted;

Encountered this error when dropping a table on a database that had recently been migrated from 5.0 to 5.5:

The migration used a “mysqldump –all-databases” so it included the “mysql” database. Apparently the “mysql.proc” table changed between the versions. The fix on the new server is to run:

I didn’t figure this out … instead Daniel Tillett gets the credit.

1

MySQL – Access Denied On Grants

Interesting problem where we got access denied errors when trying to grant privileges on MySQL server:

But we’re logged in as root so it should work. Turns out the problem is the mysql database needs to be upgraded. Try this:

Notice that it lists the individual privileges instead of saying all privileges. Now upgrade the mysql database by running mysql_upgrade:

Next restart mysql server, login and try showing grants again:

Notice now that it shows “ALL PRIVILEGES” and your grant statements will work.

 

 

0

Convert MyISAM Tables to InnoDB

The command to convert a single table from MyISAM to InnoDB looks like:

This is described suscintly by Major.IO:

http://major.io/2007/10/03/convert-myisam-tables-to-innodb/

Kevin van Zonneveld goes further in his blog:

http://kvz.io/blog/2010/04/27/convert-all-tables-to-innodb-in-one-go/

and provides some scripts to convert all tables instead of doing one at a time.

I like the following:

This variation shows the table name before starting the conversion so I can see progress. The query output is pipped right back in to mysql to execute.

0

MySQL Monitoring With myq_gadgets

Great set of utilities for monitoring MySQL performance:

https://github.com/jayjanssen/myq_gadgets/

Here are the steps to install:

The tools will be in the myq_gadgets-master folder.

There’s no installer so you can just start using the tools immediately.

For instructions see the enclosed README.txt.

Also this presentation demonstrates many of the features:

http://www.percona.com/live/mysql-conference-2013/sites/default/files/slides/myq_gadgets%20talk.pdf

0