MySQL Шпаргалки

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

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

Сброс настроек VoIP LinkSys PAP2T

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

Как и у большинства устройств, у нее тоже есть возможность удалить настройки, вот как это делается.

Если вам надо сбросить настройки Linksys PAP2T для этого следуем инструкции:

1. Подключаем телефонный аппарат к Linksys PAP2T.

2. Поднимаем трубку и вводим «****».

3. Вводим «73738#».

4. Дальше нажимаем «1″ для подтверждения.

5. Linksys PAP2T перезагрузить и загрузится со сброшенными настройками!

Для того что-бы узнать IP-адрес надо:

1. Подключаем телефонный аппарат к Linksys PAP2T.

2. Поднимаем трубку и вводим «****».

3. Вводим «110#».

4. Слушаем IP

Хранение данных

Где хранить данные для крупных ВЕБ проектов?

1. SQL Server . Достаточно распространенное решение – в этом случае база данных используется как хранилище данных. Если использовать только базу данных – существует огромная вероятность того, что при падении сервера с базой данных – завалится все! Тоесть, чтобы быть более отказоустойчивыми Вам прийдется использовать такие страшные вещи как репликация, кластеризация и т.д. При усложнении решения – цена растет. Собственно, если Вы собираетесь хранить видео – база не самый лучший выбор – дорого и порблем не оберешся.

2. Сетевая шара. Наверное самое дешевое и легкое решение – есть сетевой диск, или сетевой ресурс, куда все могут доступаться. Проблема опять же , в том что система не особо отказоустойчева – если кто-то прольет кофе на физический девайс – все, привет вашему сервису:)

3. DFS ( Distributed File System) – вот тут уже интереснее. Windows 2003 – 2008 уже содержат в себе такую фичу. Это P2P решение. Все Ваши фаловые хранилища соедененны в одну ферму, когда файл записывается на один из серверов —
он паралельно синхронизируется на другой сервак. Потому, если одна из машин отвалится, то все файлы будут доступны на другом сервере. Вообщем то это решение чаще используется на Linux системах.

4.NAS (Network Attached Storage) -это уже больше хардварное решение – это такая себе «гроздь» хардов, к которым можно доступаться как с розшаренному хранилищу данных. Цена одного такого девайса очень колеблется, и может быть как недорогой так и очень дорогой. Все зависит от разширяемости, и других параметров.

5.DAS (Direct Attached Storage) – так же хардварное решение, очень похожее на NAS. Этот девайс вставляется в сервер, и воспринимается сервером как локальный диск. Ограниченны мы только возможностями нашего сервера менеджить максимальные размеры хранилища. Это для домашних компьютеров – 100 ТБ это много, для больших видео хостингов – семочки:)

6.SAN (Storage Attached Network) – самое дорогое решение, которое простым смертным не по корману:) Собственно это целый шкаф хардов, у которых даже опреационки то нету, воспринимается это все счастье как виртуальные диски, подключенны они через fiber channel.

VirtualBox linux debian установка windows на linux в консоли

Настройка виртуальной машины на Linux Debian

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

И так, я решил использовать в качестве оболочки виртуальной машины VirtualBox
И так, с помощью apt-get install virtualbox я установил его, затем добавил пользователя vbox и создал группу vboxusers

Далее, идет настройка самого гостевого хоста:

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

1)      Создать виртуальную машину с именем Windows1
 vboxmanage createvm --name “windows1” --register
2)      Даем этой машине 512Мб оперативы, говорим грузиться с dvd разрешаем ACPI и подключаем сеть в режиме nat
 vboxmanage modifyvm “windows1” --memory 512 --acpi on --boot1 dvd --nic1 nat
3)      Создаем диск объемом в 10 гигабайт
 vboxmanage createvdi --filename "Windows_1.vdi" --size 10000
4)      Разрешаем подключаться к консоли по протоколу RDP
 vboxmanage modifyvm "windows1" --vrde on
5)      Добавляем один IDE контроллер
 VBoxManage storagectl "windows1" --name "IDE Controller" --add ide
6)      Добавляем основной жесткий диск 10 гигабайт
 VBoxManage storageattach "windows1" --storagectl "IDE Controller" --port 0 --device 0 --type hdd --medium /home/vbox/Windows_1.vdi
7)      Добавляем dvd image с операционной системой
 VBoxManage storageattach "windows1" --storagectl "IDE Controller" --port 1 --device 0 --type dvddrive --medium /home/ISO/windows_xp_prof_sp3_rus.iso
8)      Запускаем виртуальную машину
 vboxheadless -s “windows1”

9)      Подключаемся по RDP (mstsc.exe /v 192.168.0.10) на адрес сервера linux порт 3389

10)   Устанавливаем Windows как обычно

11)   Не забываем установить VirtualBox Guest submissions, для этого:

  • Выключаем виртуалку: VBoxManage controlvm “windows1” poweroff
  •  Подтыкаем диск с драйверами
  1. vboxmanage storageattach "windows1" --storagectl "IDE Controller" --port 1 --device 0 --medium none
  2. vboxmanage storageattach "windows1" --storagectl "IDE Controller" --port 1 --device 0 --type dvddrive --medium /usr/share/virtualbox/VBoxGuestAdditions.iso
12)  Готово!

Sendmail DKIM SPF FreeBSD или борьба со СПАМ

Небольшой рассказ о том, как настроить sendmail для подписывания писем ключом DKIM, и настройка записей  SPF для вашего домена. Борьба со спамом, что бы ваши письма не попадали в СПАМ.

Очень большое количество СПАМа заставляет придумывать все новые и новые технологии  борьбы с ним. По мимо различных списков «черных хостов» и «открытых релеев», компании Гугл и Яху, и еще кто-то придумали два метода идентификации писем SPF и DKIM. Большинство крупных почтовых провайдеров поддерживают эту технологию, и для гарантированной доставки контента пользователям я решил внедрить это и на нашем серваке.

Технология SPF позволяет указывать явно с каких почтовых серверов ожидать письма с вашего домена. То есть, если спамер решит слать письмо от вашего имени, то принимающая сторона увидев несовпадение IP адреса отправителя с IP в SPF может отклонить такое письмо, или добавить ему балов в SpamAssasin

SPF реализуется очень легко, в домене с которого вы шлете письма надо лишь добавить строчку типа ТХТ вида

"v=spf1 a mx a:usa.pupkin.tv ip4:96.31.22.22 include:_spf.google.com ~all"

Читать далее «Sendmail DKIM SPF FreeBSD или борьба со СПАМ»