Заметки/шпаргалка по использованию MySQL

Обслуживание, администрирование:

* Если делаем mysqldump то на все время пока будет проходить дамп, у нас залочится запись в таблицы. При больших объемах гораздо правильнее снимать дампы со slave или использвать lvm снапшот.

* Статистика по таблицам (можно сохранить как view):

SELECT table_name,engine,table_rows,
round(DATA_LENGTH/1024/1024,2) AS data_mb,
round(INDEX_LENGTH/1024/1024,2) AS index_mb,
round((DATA_LENGTH + INDEX_LENGTH)/1024/1024,2) AS total_mb
FROM information_schema.tables where TABLE_SCHEMA = database();

* Дампы собираем сразу с gzip:

mysqldump db |gzip > dump.gz

Распаковываем:

 zcat dump.gz |mysql db

* Правильно используем mysql из bash скрипта.
-N убирает название колонок таблицы
-B убирает псевдографику, используется таб как разделитель.

Конвертировать сразу всю таблицу в UTF-8 можно запросом:

ALTER TABLE table_name CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;

В консоли Linux получить список всех баз, исключив из них мусор можно примерно такой командой:

mysql -u$USER -p$PASSWORD -e "SHOW DATABASES;" | tr -d "| " | egrep -v "Database|information_schema|performance_schema|mysql"

Используя ее, можно написать небольшой скрипт, который переберет все базы и сдампит их в отдельные sql-файлы:

#!/bin/bash
USER="root"
PASSWORD="******"
OUTPUT="/home/mysql/"

databases=$(mysql -u$USER -p$PASSWORD -e "SHOW DATABASES;" | tr -d "| " | egrep -v "Database|information_schema|performance_schema|mysql")

for db in $databases; do
    echo "Dumping database: $db"
    mysqldump -u$USER -p$PASSWORD --databases $db > $OUTPUT$db.sql
done

Использование:
* Если нужно уникальный индекс по большому текстовому полю, делаем еще одно с md5sum(textdata) и строим уникальный индекс по нему.
* Храним ip как UNSIGNED INTEGER для преобразования в строку используем inet_aton/inet_ntoa
* Редко используемые данные выносим в отдельную таблицу с egnine=ARCHIVE