Rick Baker's Personal Website

Over time some of your MySQL tables may end up fragmented.  If you run any type of diagnostic script like mysqltuner, it will even tell you how many of your tables are fragmented.  You can easily fix this by optimizing the fragmented tables.  The problem is, you might not know which tables are fragmented.

Here’s a quick little query you can run that will give you the tables that are fragmented and how badly fragmented they are:

select TABLE_NAME,Data_free
from information_schema.TABLES
where TABLE_SCHEMA NOT IN ('information_schema','mysql')
and Data_free > 0;

Just in case anyone didn’t catch the optimize bit above. Once you’ve found your fragmentend tables, you can fix them with the following query, replacing %TABLENAME% with the actual table name:

optimize table %TABLENAME%

Ever have a several gig MySQL dump and only need to extract a table or two out of it?  Here’s something you can run from the command line that will split all of the tables into individual files.  You’ll need to know if your dump file has the CREATE TABLE format included or is just the data.  If it’s just the data then you can change where it says CREATE TABLE to LOCK TABLES below.

One thing to note.  If awk ends up giving you an error such as “Program Limit Exceeded”, then you can use gawk instead and it should work without issues.

cat dumpfile.sql | awk 'BEGIN {
output = "comments"; } $data ~ /^CREATE TABLE/ {
 close(output);
 output = substr($3,2,length($3)-2); }
{ print $data >> output }'

I work on so many sites, domains, linux boxes, MySQL servers, etc. that I have like a million and one passwords dancing around in my head. Went to add a database to an old devel box that I haven’t used for about 2 years and I couldn’t remember the MySQL root password. So I had to reset it. If anyone finds themselves in this situation, here’s how you do it if you have root access to the server. I’ll use my normal way of stopping/starting the service, you may need to alter this for your server.

/etc/init.d/mysql stop
mysqld --skip-grant-tables &
mysql (to get to MySQL shell)
mysql> update user set password = ('newpasssword') where user='root';
mysql> flush privileges;
mysql> exit;
/etc/init.d/mysql restart

Recently was asked a question on how to delete records from multiple tables with a single MySQL query. I’ll give an example below with one caveat. If your tables are INNODB and you have foreign keys then this query will fail. For INNODB you should just do a single delete and rely on the ON DELETE capabilities to handle any other deletes.

Anyways, so on to the query. Say you have 3 tables (table1, table2, and table3). Each of them have an entry for a variable that we’ll call $id and you want to delete from all 3 tables at once. Here you go:

delete t1, t2, t3
from table1 as t1
left join table2 as t2 on t2.id = t1.id
left join table3 as t3 on t3.id = t1.id
where t1.id = '$id'

This error or the equally frustrating ‘ Incorrect integer value: ‘ error seems to bite quite a few people in the butt that are running MySQL 5.   If you’re getting one of these errors then MySQL is running in strict mode and you are trying to run a query that isn’t inserting a proper value into one of your fields.  For example, you have an int field and the value you are inserting into that field is blank.

The proper thing to do would be to go through all of your queries and make sure you are always inserting the proper values, ie. some type of integer for an int field.

There isn’t always time for the proper way however.  If you need a quick fix you can run the following MySQL command from your favorite MySQL command prompt, the shell, phpmyadmin, etc.

SET GLOBAL SQL_MODE=''