Заметка по работе с MySQL

13.02.2016

Суббота, утро. Хочется немного отвлечься от работы =)

Решил собрать самое основное и запилить статью о том: как делать бэкапы, как эти бэкапы разворачивать, на какой информации стоит стороить оптимизацию Вашей MySQL и т.д.

Backup (экспорт и импорт)

Экспорт данных из MySQL

1) Бэкап всех баз данных (структура и данные)

mysqldump -u USER -pPASSWORD DATABASE > /path/to/file/dump.sql

2) Бэкап структуры (только структура, без данных)

mysqldump --no-data - u USER -pPASSWORD DATABASE > /path/to/file/schema.sql

3) Бэкап только одной или нескольких таблиц

mysqldump -u USER -pPASSWORD DATABASE TABLE1 TABLE2 TABLE3 > /path/to/file/dump_table.sql

4) Бэкап + архивирование

mysqldump -u USER -pPASSWORD DATABASE | gzip > /path/to/outputfile.sql.gz

5) Бэкап с указанием его даты в названии файла дампа

mysqldump -u USER -pPASSWORD DATABASE | gzip > `date +/path/to/outputfile.sql.%Y%m%d.%H%M%S.gz`

Импорт данных в MySQL

1) Заливаем бекап в базу данных

mysql -u USER -pPASSWORD DATABASE < /path/to/dump.sql

2) Заливаем архив бекапа в базу данных

gunzip < /path/to/outputfile.sql.gz | mysql -u USER -pPASSWORD DATABASE

или так

zcat /path/to/outputfile.sql.gz | mysql -u USER -pPASSWORD DATABASE

3) Удобно делать бекап с дополнительными опциями, например: -Q -c -e

mysqldump -Q -c -e -u USER -pPASSWORD DATABASE > /path/to/file/dump.sql

-Q - оборачивает имена обратными кавычками

-c - делает полную вставку, включая имена колонок

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

Полезное

1) Создаём новую базу данных

mysqladmin -u USER -pPASSWORD create NEWDATABASE

2) Список баз данных пользователя

mysqlshow -u USER -pPASSWORD

3) Список таблиц базы данных

mysqlshow -u USER -pPASSWORD DATABASE

4) Дополнительные ключики

Для таблиц InnoDB надо добавлять –single-transaction, это гарантирует целостность данных бекапа.

Для таблиц MyISAM это не актуально, ибо они не поддерживают транзакционность.

Работа с данными

Числа

На 32-битных системах практически нет смысла ставить для типа INTEGER свойство UNSIGNED, так как такие большие числа в php могут не поддерживаться.

На 64-битных системах, php поддерживает большие числа, вплоть до MySQL BIGINT со знаком.

Связанные таблицы Foreign keys должны иметь полное сходство по структуре ключей. Т.е. если у нас на одной таблице для поля указано «INTEGER UNSIGNED DEFAULT 0 NOT NULL» то и на другой должно быть указано аналогично.

Для хранения булевых значений, нужно использовать TINYINT(1)

А деньги лучше хранить в DECIMAL(10, 2), где первое число обозначает количество всех знаков, включая запятую, а второе — количество знаков после запятой. Итого, у нас получится что DECIMAL(10,2) может сохранить 9999999,99

Строки

В старых версиях (до 5.0.3) VARCHAR была ограничена 255 символами, но сейчас можно указывать до 65535 символов.

Помните, что тип TEXT ограничен только 64 килобитами, поэтому что бы сохранять «Войну и Мир» пользуйтесь LONGTEXT

Самая правильная кодировка для вашей БД - UTF-8

Даты

Не забывайте, что:

DATE, TIME, DATETIME — выводятся в виде строк, поэтому поиск и сравнение дат происходит через преобразование

TIMESTAMP — хранится в виде UNIX_TIMESTAMP, и можно указать автоматически обновлять колонку

Сравнивая типы данных DATETIME и TIMESTAMP, не забывайте делать преобразование типов, например:

SELECT * FROM table WHERE `datetime` = DATE(`timestamp`)

Перечисления

Для перечислений правильно использовать тип ENUM

Правильно пишется так: ENUM(‘мама’, ‘мыла’, ‘раму’)

Можно ставить значение по-умолчанию, как и для любой строки.

В базе поле с перечислением хранится как число, поэтому скорость работы — потрясающе высокая.

Количество перечислений ~ 65 тысяч.

Отладка

Если запросы тормозят, то можно включить лог для медленных запросов в /etc/mysql/my.cnf

А потом оптимизировать запросы через EXPLAIN

И наблюдать за запросами удобно через утилиту mytop.

Тэги:

mysql

Меню