MySQL tips and tricks

A bunch of MySQL tip

http://souptonuts.sourceforge.net/readme_mysql.htm

The MySQL Tips file presents a logs of MySQL tips.

Source: Thanks to Le blog de Xarli (fr) for Trucs et astuces MySQL (fr).

Extract a database from a MySQL dump

Provide the name of the database to extract:

DB_NAME="my_database"

Provide the path to the MySQL dump file:

DUMP_FILE="/path/to/a-mysql-dump.sql"

Extract the database from the dump:

command sed -n "0,/^-- Current Database:/p;/^-- Current Database: \`${DB_NAME}\`/,/^-- Current Database: \`/p;/TIME_ZONE=@OLD_TIME_ZONE/,/^-- Dump completed/p" \
   > "${DB_NAME}.sql"

The database dump is now available in the file listed by :

echo "${DB_NAME}.sql"

Delete the duplicate entries from a table

This SQL request list all the duplicate entries of a table:

SELECT COUNT(field1), field1 [ , field2 ]
  FROM table
  GROUP BY field1 [ , field2 ]
  HAVING COUNT(field1) > 1

Note: Replace field2 (optionnal field) by as many additionnal fields needed to check the duplicate entries.

Delete the duplicate entries with:

DELETE
    FROM table
    LEFT OUTER JOIN (
        SELECT MIN(id) as id, field1, field2
            FROM table
            GROUP BY field1, field2
        ) AS table_1
            ON table.id = table_1.id
    WHERE table_1.id IS NULL

Source: Thanks to RootLabs (fr) for MySQL : Comment nettoyer une table de ces doublons ? (fr).

Purge binary logs

The MySQL binary logs can under some conditions use a lot of disk space. It is possible to purge them with the 'PURGE BINARY LOGS TO' request. If the MySQL server is not a replication master, purge all binary up to the last five with:

command mysql --execute="PURGE BINARY LOGS
    TO '$(command find '/var/log/mysql'  -type f -name 'mysql-bin.[0-9]*' \
              | command sort \
              | command head -n -5 \
              | command tail -n 1 \
              | command xargs basename)';"

Setting up a search engine

The Sphinx open-source search server ease the setup of a fast search engine.

Source : Thanks to Guillaume Pousséo.

Diagnose #1025 errors for InnoDB tables

The error #1025 raise when altering a InnoDB table. Get more information on the error source with this SQL query:

SHOW ENGINE INNODB STATUS;

Source : Thanks to snoyes on irc.freenode.net#mysql

Fix "Incorrect key file for table" errors

If the following error is raised when executing SQL query:

Could not execute query [Native Error: Incorrect key file for table '/tmp/#sql_2258_0.MYI'; try to repair it

The /tmp folder has low available disk space. To fix the problem, clean up the /tmp folder file system contents.

Create the maintenance user 'debian-sys-maint'

If the user debian-sys-maint does not exists (for example, after loading a SQL dump from another Linux distribution), create it:

SYSMAINT_PWD=$(/bin/grep --max-count=1 "password" /etc/mysql/debian.cnf | /bin/sed -e 's/^password[ =]*//g')
echo "ALTER TABLE user CHANGE password Password varchar(41) collate utf8_bin NOT NULL default '';
REPLACE INTO user SET
  host='localhost', user='debian-sys-maint', password=password('${SYSMAINT_PWD}'),
  Select_priv='Y', Insert_priv='Y', Update_priv='Y', Delete_priv='Y',
  Create_priv='Y', Drop_priv='Y', Reload_priv='Y', Shutdown_priv='Y',
  Process_priv='Y',  File_priv='Y', Grant_priv='Y', References_priv='Y',
  Index_priv='Y', Alter_priv='Y', Show_db_priv='Y', Super_priv='Y',
  Create_tmp_table_priv='Y', Lock_tables_priv='Y', Execute_priv='Y',
  Repl_slave_priv='Y', Repl_client_priv='Y';" \
  | /usr/bin/mysql --user=root --password mysql

Uninstall totally (purge) a MySQL server

These commands completely purge a MySQL server in order to reinstall it from scratch:

Purge PHPMyAdmin:

command apt-get --purge remove phpmyadmin

Purge MySQL server:

command apt-get --purge remove mysql-server* mysql-server-core*

Delete data files:

command rm -r /var/lib/mysql
command rm /etc/mysql/debian.cnf

The MySQL server can now be reinstalled from scratch.