Top Nav

Archive | MySQL

Mysqldump TRIGGERS Only

Here’s the command to dump the triggers and stored procedures for a database:



Revoke MySQL DROP Privilege On Plesk Database User

Had a request from client to add database user on a Plesk server but to remove the DROP DATABASE privilege so that they could not accidentally delete their database. By default, database users created through Plesk do have the DROP DATABASE privilege. Plesk does not currently allow fine-grained control over database privileges so the only way to implement this change is to modify the privileges directly from a MySQL command line. After some testing we found these command will have the desired effect:

Of course replace “testdb” and “testdbuser” with your actual database and username.



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

And here’s an article that shows the use of 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:



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.


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.