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/