MySQL. Полезности

1) Если делаем mysqldump то на все время пока будет проходить дамп, у нас залочится запись в таблицы. При больших объемах гораздо правильнее снимать дампы со slave или использвать lvm снапшот.
2) Чтобы правильно получить год и неделю, надо групировать по date_format(date,%x-%v). При использовании group by year(date),week(date,3) получаем, что 29-31 декабря 2008 считается как 2008,1 неделя. Все логично, но следует помнить.
3) Статистика по табличкам, рекомендую сохранить как 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();

4) Храним ip как UNSIGNED INTEGER для преобразования в строку используем inet_aton/inet_ntoa
5) Редкоиспользуемые данные выносим в отдельную таблицу с egnine=ARCHIVE
6) Дампы собираем сразу с gzip. mysqldump db |gzip > dump.gz. Распаковываем zcat dump.gz |mysql db
7) Правильно используем mysql из bash скрипта.
-N убирает название колонок таблицы
-B убирает псевдографику, используется таб как разделитель.
8) Забываем слово RAID5/RAID6. Для БД актуально только RAID10/RAID1
9) Если нужно уникальный индекс по большому текстовому полю, делаем еще одно с md5sum(textdata) и строим уникальный индекс по нему.

Лучше использовать SELECT… ORDER BY… LIMIT 1 чем SELECT MAX(…). Разница в скорости может достигать 6000 раз.

Работа с бекапами

Делаем бекап
mysqldump -u USER -pPASSWORD DATABASE > /path/to/file/dump.sql

Создаём структуру базы без данных
mysqldump --no-data - u USER -pPASSWORD DATABASE > /path/to/file/schema.sql

Если нужно сделать дамп только одной или нескольких таблиц
mysqldump -u USER -pPASSWORD DATABASE TABLE1 TABLE2 TABLE3 > /path/to/file/dump_table.sql

Создаём бекап и сразу его архивируем
mysqldump -u USER -pPASSWORD DATABASE | gzip > /path/to/outputfile.sql.gz

Создание бекапа с указанием его даты
mysqldump -u USER -pPASSWORD DATABASE | gzip > `date +/path/to/outputfile.sql.%Y%m%d.%H%M%S.gz`

Заливаем бекап в базу данных
mysql -u USER -pPASSWORD DATABASE < /path/to/dump.sql

Заливаем архив бекапа в базу
gunzip < /path/to/outputfile.sql.gz | mysql -u USER -pPASSWORD DATABASE
или так
zcat /path/to/outputfile.sql.gz | mysql -u USER -pPASSWORD DATABASE

Создаём новую базу данных
mysqladmin -u USER -pPASSWORD create NEWDATABASE

Удобно использовать бекап с дополнительными опциями -Q -c -e, т.е.
mysqldump -Q -c -e -u USER -pPASSWORD DATABASE > /path/to/file/dump.sql, где:

  • -Q оборачивает имена обратными кавычками
  • -c делает полную вставку, включая имена колонок
  • -e делает расширенную вставку. Итоговый файл получается меньше и делается он чуть быстрее

Взято из комментариев с https://habrahabr.ru/post/105954/