MySQL шпаргалки

Часто, когда разрабатываешь сайт, замечаешь, как на одни и те же грабли наступают разработчики при проектировании базы данных.
Сегодня я решил опубликовать свои шпаргалки, на самые часто встречающиеся ошибки при работе с MySQL.

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


Делаем бекап
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 делает расширенную вставку. Итоговый файл получается меньше и делается он чуть быстрее
Для просмотра списка баз данных можно использовать команду:
mysqlshow -u USER -pPASSWORD
А так же можно посмотреть список таблиц базы:
mysqlshow -u USER -pPASSWORD DATABASE
Для таблиц InnoDB надо добавлять --single-transaction, это гарантирует целостность данных бекапа.
Для таблиц MyISAN это не актуально, ибо они не поддерживают транзакционность.

Подробнее

Общие факты

  • Полезно под каждую базу на боевом сервере создавать своего пользователя
  • Кодировка базы может быть любой, если она UTF8
  • В большинстве случаев лучше использовать движок InnoDB
  • В php лучше забыть про сильно устаревшее расширение mysql и по-возможности использовать pdo или mysqli
  • Новую копию MySQL всегда можно настроить и оптимизировать
  • Без особой нужды не стоит открывать MySQL наружу. Вместо этого можно сделать проброс портов
Пример, проброс портов:
ssh -fNL LOCAL_PORT:localhost:3306 REMOTE_USER@REMOTE_HOST

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

Числа

  • На 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»
  • Самая правильная кодировка для вашей БД UTF8

Даты

Не забывайте, что
  • DATE, TIME, DATETIME — выводятся в виде строк, поэтому поиск и сравнение дат происходит через преобразование
  • TIMESTAMP — хранится в виде UNIX_TIMESTAMP, и можно указать автоматически обновлять колонку
  • Сравнивая типы данных DATETIME и TIMESTAMP, не забывайте делать преобразование типов
Пример преобразования типов:
SELECT * FROM table WHERE `datetime` = DATE(`timestamp`)

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

  • Для перечислений правильно использовать тип ENUM
  • Правильно пишется так: ENUM('мама', 'мыла', 'раму')
  • Можно ставить значение по-умолчанию, как и для любой строки
  • В базе поле с перечислением хранится как число, поэтому скорость работы — потрясающе высокая
  • Количество перечислений ~ 65 тысяч
dev.mysql.com/doc/refman/4.1/en/storage-requirements.html
help.scibit.com/mascon/masconMySQL_Field_Types.html

Отладка

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

Пожалуйста, сообщите мне, если вы заметили неточность или есть желание поделиться советом или шпаргалкой.

一步一步地会到目的

I do not smoke or drink



Search for Extra-Terrestrial Intelligence at Home
Search for Extra-Terrestrial Intelligence at Home

User signature graphic BOINC Stars

Power by BOINC


International Space Station
Прямая трансляция переговоров космонавтов МКС с ЦУП.
Только голос | Голос и Видео
Говорят они не всегда, только по необходимости, так что не удивляйтесь, если на канале будет тишина.

Ubuntu 14.04 (Trusty Tahr) Daily Build

>>> Старые релизы Ubuntu <<<

Оглавление

Cisco Systems

Связь через консольный кабель
Сброс пароля на Catalyst 2950
Создание VLAN на Catalyst 2950
Создание и управление стеками коммутаторов

FreeBSD 8.2 - Сервер с "нуля"

Установка
Начальная настройка
Сборка ядра (c Kernel NAT)
Named
OpenVPN

Ubuntu Server

Настраиваем WEB-сервер
Apache2 + PHP5 + MySQL + phpMyAdmin
Пример для виртуальных хостов

VSFTPd + SSL Не закончено!
Установка TFTP сервера (tftpd)
Установка Midnight Commander
Установка Deluge с Web-интерфейсом
Бэкапим MySQL базы
MySQL шпаргалки
Резервное копирование - м1
Простой шлюз на Ubuntu Server
Сетевое радио на Icecast2
Объединение HDD дисков
Управление автозагрузкой
Настройка сети вручную
Защита от брутфорса
ssh, ftp, http: fail2ban

OpenVPN Installation Ubuntu

Ubuntu Desktop

Настройка Ubuntu Desktop 10.10
Раскукоживаем Менеждер обновлений
Раскладка - флажки вместо текста
QutIM - клиент ICQ, Jabber, MAgent, IRC
Температура железа на панеле

Горячие клавиши в Ubuntu Unity

Драйвера NVIDIA в Ubuntu
Радио лоток в области уведомлений
SSHFS - Монтируем удаленную FS
Резервное копирование Ubuntu Desk
Загрузочная флешка Ubuntu Server
Загрузочная USB из под Windows
Как найти нужный PPA-репозиторий
Видеонаблюдение под Ubuntu 10.04 для "чайников"
Видеонаблюдение легально!

Linux

Полезные команды Linux
Конфиг железа в Ubuntu
Мониторинг хостов в сети
Разное на одном листе
inotify - это подсистема ядра Linux, которая позволяет получать уведомления об изменениях в файловой системе.

Полезное под Windows

FTPS - защищённое соединение
PuTTY - Как сохранить настройки
VirtualBox - Клонирование виртуальных машин.

Скриптовые языки

AWK - Язык обработки структурированных текстов

Чит-листы или Шпаргалки

pdf css_cheat-sheet ver.1
pdf css_cheat-sheet ver.2
pdf php_cheat-sheet ver.1
pdf php_cheat-sheet ver.2
pdf javascript_cheat-sheet ver.1
pdf mysql_cheat-sheet ver.1
pdf html_cheat-sheet ver.1
pdf regular_expressions ver.1
pdf regular_expressions ver.2
pdf mod_rewrite_cheat-sheet ver.1
pdf mod_rewrite_cheat-sheet ver.2
pdf sql_server_cheat-sheet ver.1
pdf microformats_cheat-sheet ver.1
pdf seo_cheat-sheet ver.0.4
pdf wordpress_designer ver.1

Последовательное соединение проводников
Последовательное соединение проводников

Параллельное соединение проводников
Параллельное соединение проводников


Gedit style schemes

xml darktango.xml
xml emacs-darkslategrey.xml

HTML заметки

Разные валидные фичи
10 преступлений с HTML тегами
Заметки по установке
MaxSite CMS версия 0.42

Горизонтальное CSS Меню
.htacces паролим сайт
Противоугонный пакет

Коллекция jQuery plagins

Quovolver [?]
Custom Animation Banner [?]
Style my tooltips [?]


ipv6.nic.ru

QR Coder.ru
Генератор QR-кодов


2:5020/1209.12@FidoNet

Logo WebMoney
WMIDwmid 358066815673
pursewmzZ452007172362
wmrR568219491953
wmeE770110075404

Logo Yandex.Money
 Yndex purse4100178599238



Мои телефоны:
  1. Билайн AMPS/DAMPS - модели ERICSSON DH318,
    ERICSSON DH618
  2. Билайн GSM - Siemens C25 корпус "Хамелион" (тариф "Серебрянный")
  3. Мегафон GSM - SAMSUNG SGH-T400
  4. Мегафон GSM / AIS GSM Thailand - Qtek s100
  5. Мегафон GSM - SAMSUNG SGH-i900 WiTu 8Gb
  6. Мегафон GSM - HTC Wildfire S
  7. Мегафон GSM - HTC One S
  8. Мегафон GSM / Теле2 GSM - ASUS Zenfone 2 ZE550ML
  9. Мегафон GSM - Xiaomi Redmi Note 4 64Gb+4Gb