Нотатки / шпаргалка з використання 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