Отличия типов datetime и timestamp в MySQL

На днях я столкнулся с тем, что многие разработчики не знают в чём отличие типов данных DATETIME и TIMESTAMP в MySQLе, а так же как хранить дату и время, если необходимо учитывать разные часовые пояса для разных пользователей веб-приложения. Поэтому хочу дать ниже разъяснения с пояснениями.

DATETIME
Хранит время в виде целого числа вида YYYYMMDDHHMMSS, используя для этого 8 байтов. Это время не зависит от временной зоны. Оно всегда отображается при выборке точно так же, как было сохранено, независимо от того какой часовой пояс установлен в MySQL. Даю пример:

mysql> CREATE TABLE `dt1` ( col datetime NOT NULL );
mysql> SET @@SESSION.time_zone='+00:00';
mysql> SELECT now();
+---------------------+
| now() |
+---------------------+
| 2009-06-04 18:13:56 |
+---------------------+
 
mysql> INSERT INTO dt1 VALUES(now());
 
mysql> SET @@SESSION.time_zone='+01:00';
mysql> SELECT now();
+---------------------+
| now() |
+---------------------+
| 2009-06-04 19:14:20 |
+---------------------+
 
mysql> INSERT INTO dt1 VALUES(now());
 
mysql> SET @@SESSION.time_zone='+00:00';
mysql> SELECT * FROM dt1;
+---------------------+
| col |
+---------------------+
| 2009-06-04 18:14:10 |
| 2009-06-04 19:14:27 |
+---------------------+

TIMESTAMP
Хранит 4-байтное целое число, равное количеству секунд, прошедших с полуночи 1 января 1970 года по усреднённому времени Гринвича (т.е. нулевой часовой пояс, точка отсчёта часовых поясов). При получении из базы отображается с учётом часового пояса. Часовой пояс может быть задан в операционной системе, глобальных настройках MySQL или в конкретной сессии. Запомните, что сохраняется всегда количество секунд по UTC (универсальное координированное время, солнечное время на меридиане Гринвича), а не по локальному часовому поясу. Пример:

mysql> CREATE TABLE tm1 (col TIMESTAMP NOT NULL);
mysql> SET @@SESSION.time_zone = '+00:00';
mysql> SELECT now();
+---------------------+
| now() |
+---------------------+
| 2009-06-04 18:24:54 |
+---------------------+
mysql> INSERT INTO tm1 VALUES(now());
mysql> SELECT * FROM tm1;
+---------------------+
| col |
+---------------------+
| 2009-06-04 18:25:08 |
+---------------------+
 
mysql> SET @@SESSION.time_zone = '+01:00';
mysql> SELECT now();
+---------------------+
| now() |
+---------------------+
| 2009-06-04 19:25:21 |
+---------------------+
mysql> INSERT INTO tm1 VALUES(now());
mysql> SELECT * FROM tm1;
+---------------------+
| col |
+---------------------+
| 2009-06-04 19:25:08 |
| 2009-06-04 19:25:26 |
+---------------------+
 
mysql> SET @@SESSION.time_zone = '+00:00';
mysql> SELECT * FROM tm1;
+---------------------+
| col |
+---------------------+
| 2009-06-04 18:25:08 |
| 2009-06-04 18:25:26 |
+---------------------+

Ещё одно отличие! TIMESTAMP по умолчанию NOT NULL, а его значение по умолчанию равно NOW().

mysql> INSERT INTO dt1 VALUES(NULL);
ERROR 1048 (23000): COLUMN 'col' cannot be NULL
mysql> INSERT INTO tm1 VALUES(NULL);
Query OK, 1 ROW affected (0.00 sec)
mysql> SELECT * FROM tm1;
+---------------------+
| col |
+---------------------+
| 2009-06-04 18:25:08 |
| 2009-06-04 18:25:26 |
| 2009-06-04 18:32:50 |
+---------------------+


Дополнение.
Для тех, кого смущает использование функции NOW().

mysql> SET @@SESSION.time_zone = '+00:00';
mysql> INSERT INTO dt1 VALUES('2009-06-04 22:00:00');
mysql> SET @@SESSION.time_zone = '+01:00';
mysql> SELECT * FROM dt1;
+---------------------+
| col |
+---------------------+
| 2009-06-04 22:00:00 |
+---------------------+
 
mysql> SET @@SESSION.time_zone = '+00:00';
mysql> INSERT INTO tm1 VALUES('2009-06-04 22:00:00');
mysql> SET @@SESSION.time_zone = '+01:00';
mysql> SELECT * FROM tm1;
+---------------------+
| col |
+---------------------+
| 2009-06-04 23:00:00 |
+---------------------+

Источник

Восстановление root-пароля в MySQL

Сменить утерянный пароль достаточно просто и требуется проделать следующие шаги:
1. Остановить mysql-сервер

$> /usr/local/etc/rc.d/mysql-server stop

2. Запустить mysqld с опцией —skip-grant-tables

$> mysqld_safe --skip-grant-tables &

3. Соединиться с mysql-сервером

$> mysql -u root

4. Установить пароль root

mysql> USE mysql;
mysql> UPDATE USER SET password=PASSWORD('root_password') WHERE USER='root' AND host='localhost';
mysql> FLUSH privileges;
mysql> quit

5. Остановить сервер

$> killall mysqld_safe

6. Запустить сервер и проверить новый пароль.

Хранение файлов в MySQL и их быстрая раздача

Думаю у многих возникала необходимость хранить файлы, связанные с записью в таблице. Это может быть картинка к новости, аватар, загруженный пользователем файл — да все, что угодно. Обычно в этому случае поступают просто — файл ложится в файловую систему, а ссылка на него — в запись БД

Но у такого классического похода множество недостатков:

  • файлы не удаляются при удалении соответствующей записи БД;
  • проблемы при одновременной попытке обновления файла;
  • нарушение синхронизации между БД и файловой системой при откате транзакции;
  • при резервном копировании и восстановлении информации в БД может возникнуть рассинхронизация с файловой системой;
  • файлы не подчиняются ограничениям доступа, наложенным с помощью БД.

Больше о проблемах, возникающих при хранении файлов отдельно от БД можно почитать в презентации SQL Antipatterns, раздел Phantom Files, страница 60. Кстати, автор презентации предлагает решение — хранить файлы прямо в БД, в поле типа BLOB. Правда следует замечание, что это должно быть взвешенное решение в каждом конкретном случае. Ведь при таком способе хранения файлов вебсервер должен при каждом запросе вызывать некий скрипт, который будет извлекать файл из БД и отдавать пользователю, что неминуемо отрицательно скажется на производительности.
Для поиска решения данной проблемы был проведен мозговой штурм и придумано несколько вариантов решения проблемы:

1. Перед удалением записи делать SELECT с тем же условием и получать имена файлов, которые надо удалить. Проблема в том, что если удаляемых файлов много, эта операция может занять некоторое время и по хорошему на это время надо блокировать таблицу на чтение и запись, а во многих случаях это недопустимо.
2. Перед удалением устанавливать у удаляемых записей метку «подлежит удалению», получить все записи с этой меткой и удалить файлы, связанные с этими записями, и наконец удалить все записи с этой меткой. Запросы, работающие с этой таблицей следует доработать, чтобы они не выбирали записи с установленным флагом. Недостатки — необходимость правки множества запросов, к тому же у нас в проекте записи на удаление отбираются достаточно сложным SELECT, которые нельзя переделать в один UPDATE.
3. Первые два способа пытаются решить проблему «потерянных» файлов при удалении записей в БД, которая возникает при «классическом» способе хранения файлов, однако они не решают остальных проблем такого подхода, поэтому мы попытались придумать решения, использующие положительные моменты хранения файлов прямо в БД и избавиться от недостатков, присущих этому подходу.
4. Использовать триггеры. К сожалению, MySQL не имеет в своем языке поддержки команд работы с файлами, такие команды пришлось бы реализовывать самостоятельно, ковыряясь в исходниках MySQL. Из минусов — файлы должны храниться на том же хосте, что и БД, необходимость доработки MySQL, таких готовых решений мы не нашли.
5. Хранить файлы в БД, но отдавать их напрямую вебсервером, без участия PHP. Реализовать это можно, написав модуль к вебсерверу (nginx например) который позволял бы отдавать файлы напрямую из MySQL или применив драйвер файловой системы MySQLfs. Такой подход решает все перечисленные выше проблемы, но его недостаток — дополнительные накладные расходы на хранение файлов в MySQL.
6. Специализированный Storage Engine для MySQL, хранящий записи как файлы.

Остановимся более подробно а последнем пункте. Ведь что собой представляет файловая система — это специализированная БД, которая по ключу «имя файла» позволяет получить запись — его содержимое. То есть можно реализовать свой механизм хранения данных для MySQL, в котором каждая запись будет иметь три поля:

CREATE TABLE `data_storage`.`files` (
`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`path` VARCHAR( 255 ) ,
`data` BLOB
) ENGINE = FILES

Вставлять данные в такую таблицу можно только в поле `data`, при этом они просто сохраняются в файл, уникальное имя ему при этом генерируется автоматически (используя в качестве префикса поле `id`) — например 764533, а в поле `path` автоматически подставляется правильный путь, по которому MySQL положил наши данные — например ‘/mnt/storage/mysqldata/76/45/33/764533_myfile.jpg’. Таким образом к данным, сохраненным в такой таблице можно обращаться как к простым файлам, и при этом MySQL будет поддерживать целостность данных. Таким образом этот способ хранения файлов лишен практически всех недостатков классического подхода (кроме ограничения доступа, но и его можно сделать используя простой скрипт и заголовок X-Accel-Redirect nginx) и в то же время никак не уменьшает производительность при отдаче файлов клиентам.
Проблема за малым — не удалось найти готовой реализации такого движка хранения данных для MySQL, хотя идея общем то простая. Возможно кто-то из хабролюдей подскажет ссылку на готовую реализацию такого storage engine, идея ведь плавает на поверхности, и ее точно кто-то уже мог реализовать.

Автор: Sheder
Источник: shedar.habrahabr.ru

Резервное копирование баз MySQL

Резервное копирование баз MySQL. Гораздо более сложной представляется задача создания копии такой динамичной структуры, как база данных MySQL.

Вообще, почти все хостинг-провайдеры производят резервное копирование всех файлов пользователей. Однако, не стоит забывать о том, что провайдеры делают backup, в основном, для себя, на случай аварии у себя. Именно по этой причине пользователи в условиях хостинга могут, конечно, рассчитывать на восстановление в случае удаления каких-то данных по вине самого пользователя, но вовсе не факт, что провайдер сделает восстановление MySQL-базы сразу по получению запроса. Лучше делать для себя копию и в случае чего ее использовать. Можно даже периодически копировать этот свой backup на другую, не провайдерскую машину — так надежнее, на всякий случай.

Сделать копию всех статических HTML- и прочих документов просто. Так же несложно периодически «откладывать в сторонку» и копии скриптов. Гораздо более сложной представляется задача создания копии (далее backup) такой динамичной структуры, как база данных MySQL. Основные трудности, которые возникают перед администратором размещенного на хостинге сайта, обычно бывают такие:

  1. Отсутствие физического доступа к файлам базы данных. Как правило, провайдеры хостинга предоставляют возможность работы с базой данных только через скрипты или специальный mysql-клиент, но не дают прав на доступ непосредственно к файлам, в которых содержатся данные из MySQL-базы.
  2. Отсутствие у администратора знаний о том, как вообще надо делать backup. Обычно такая задача возникает только, когда «клюнул жареный петух». То есть, в случае аварии, вторжения хакеров или в других внештатных ситуациях. Веб-мастеры просто не готовы к немедленному backup и начинают судорожно изучать документацию по MySQL, а время идет…
  3. В случае, если веб-мастер не владеет в достаточной мере навыками работы со специализированными утилитами из пакета MySQL, могут возникать трудности, связанные с ограничениями, налагаемыми хостинг-провайдером на пользовательские аккаунты. Например, если база очень большая и ее размер превышает лимит на доступную пользователю память (RAM), backup сделать будет сложно. Нужно пользоваться тонкими настройками утилит резервного копирования, что иногда тоже вызывает трудности на практике.

Итак, данная статья предназначена для того, чтобы облегчить работу по созданию резервных копий MySQL-баз. Если Вы — веб-мастер, и работаете с MySQL, наверняка, информация, содержащаяся в данной статье, будет Вам полезна.

Как сделать копию базы MySQL

Существует программа mysqldump, позволяющая быстро и просто производить операции по созданию резервных копий баз MySQL. Также mysqldump дает возможность делать очень тонкие настройки для управления процессом создания резервных копий баз данных или отдельных таблиц. Можно сказать, что mysqldump — это основной инструмент, которым Вам придется пользоваться в том случае, если Вы будете делать backup MySQL.

Сразу возьмем простую задачу, которую будем решать с помощью mysqldump, и разберемся, что к чему. Есть хостинг, есть база данных DBNAME, которую выделил Вам хостинг-провайдер. Есть хост HOST, на котором размещен сервер MySQL, логин LOGIN к нему, порт PORT, на котором работает сервер, а также пароль PASS. Имея все эти данные, можно сделать dump (дамп, копию) базы DBNAME так (выполняем в unix shell):

> mysqldump -uLOGIN -PPORT -hHOST -pPASS DBNAME > dump.txt

После выполнения данной команды в файле dump.txt у нас будет копия MySQL-базы DBNAME. Это произойдет только в том случае, конечно, если все параметры Вы зададите верно, в соответствии с настройками своего хостинга. Сразу нужно сказать, что программа mysqldump производит вывод результатов прямо Вам на STDIN, то есть, на экран. Нужно перенаправлять вывод в какой-либо файл. Например, как в данном случае — » > dump.txt «. Если этого не сделать, а база большая, Вы получите на экран все те мегабайты информации, которые в ней содержатся.

Немного расскажем о том, что же делает mysqldump. Эта программа создает сценарий восстановления Ваших данных. То есть, вывод mysqldump — это не какие-то абстрактные и нечитаемые двоичные данные, а осмысленный текст сценария. Например, если в Вашей базе была таблица test, в которой было поле test2 с типом данных integer и одна-единственная запись «1111», то mysqldump создаст примерно такой сценарий:

# MySQL dump 8.14
#
# Host: HOST Database: DBNAME
#--------------------------------------------------------
# Server version 3.23.39-log
 
#
# Table structure for table 'test'
#
 
CREATE TABLE test (
 test2 int(11) default NULL
) TYPE=MyISAM;
 
#
# Dumping data for table 'test2'
#
 
INSERT INTO test2 VALUES ('1111');

Таким образом, mysqldump «опишет» все Ваши таблицы и создаст INSERT-команды для восстановления данных в таблицах. Итак, мы перенаправляем вывод mysqldump в текстовый файл, который потом будем использовать для восстановления. Рассмотрим и этот процесс — воссоздание базы из резервной копии.

Для восстановления будем пользоваться стандартной программой mysql, которая входит в комплект поставки MySQL наряду с mysqldump. Допустим, у нас имеется backup в файле dump.txt. Нам нужно восстановить его в рабочую базу. Например, мы случайно удалили нашу базу данных, а теперь пытаемся исправить эту незадачу. Делаем так :

> mysql -uLOGIN -PPORT -hHOST -pPASS DBNAME < dump.txt

То есть, заставляем mysql-клиент соединиться с сервером и выполнить сценарий, который у нас имеется. После выполнения этой команды в Вашей базе появятся таблицы и данные из резервной копии. Учитывайте то, что данные будут просто восстанавливаться по сценарию из dump.txt. То есть, если таблицы, которые упоминаются в дампе базы, уже существуют и имеют другую структуру, тут явно возникнет ошибка. Просто посмотрите на сценарий и на рабочую базу и представьте, что Вы вручную выполняете команды из сценария. Если уверены, что все будет хорошо — смело восстанавливайте.

Рассмотрим более тонкие настройки mysqldump:

-databases позволяет сделать так, что mysqldump включит в сценарий восстановления команды

CREATE DATABASE /*!33333 IF NOT EXISTS*/ DBNAME и USE DBNAME

. Это позволит создавать рабочие базы «с нуля». То есть, без использования —databases подразумевается, что пользователь восстанавливает одну базу данных и явно указывает, куда нужно помещать восстанавливаемые данные. Если же backup создается с целью сделать полностью рабочую копию данных, например, на другом MySQL-сервере, то нужно использовать этот ключ;

-all-databases позволяет сделать копии всех баз данных, которые существуют на данном MySQL-сервере. Если же нужно сделать копии только некоторых баз, нужно просто указать их через пробел при вызове mysqldump из командной строки (см. выше);

Ключ -help. Программа mysqldump имеет множество версий. Посмотреть, какие возможности поддерживаются конкретно Вашей версией, можно с помощью этого ключа;

-add-drop-table — ключ, который заставит mysqldump добавлять в итоговый сценарий команду drop table перед созданием таблиц. Это позволит избежать некоторых ошибок при восстановлении базы из резервной копии. Конечно, нужно учитывать то, что таблицы, находящиеся в рабочей копии (если таблицы с таким же именем существуют в backup), перед восстановлением из резервной копии будут удалены из основной базы и пересозданы из backup;

-no-data. С помощью этого ключа можно быстро сделать копию структуры таблицы/баз без самих данных. Например, Вы создали сложную таблицу и хотели бы сохранить на будущее ее структуру, а сами данные, которые находятся в этой таблице, Вам в резервной копии не нужны;

-result-file=… — этот ключ можно использовать для перенаправления вывода в файл. Можно использовать обычное unix-перенаправление командой «>», а можно — вот этот ключ. Кому что нравится;

Кроме перечисленных ключей mysqldump имеет и еще некоторое количество очень полезных возможностей, которые Вы можете применять по обстоятельствам. Полная документация по mysqldump доступна на странице http://www.mysql.com/doc/m/y/mysqldump.html.

Еще один очень полезный совет по использованию mysqldump в хостинговой среде. Как правило, при использовании хостинга на пользователя налагаются некоторые ограничения. Например, нельзя занять больше некоторого количества физической памяти (RAM, ОЗУ). mysqldump по умолчанию помещает все полученные от MySQL-сервера данные в память, а потом записывает все это на диск. Соответственно, если провайдер дает Вам занять, например, 30Мб памяти, а база, копию которой Вы делаете с помощью mysqldump, занимает 50Мб, конечно, тут возникнет ошибка — mysqldump не сможет отработать корректно и завершится аварийно, о чем Вам сообщит. Чтобы «заставить» mysqldump писать данные сразу на диск, а не хранить их, пусть даже и временно, в памяти, используйте ключ —quick. Это решит проблему.

Автоматизация резервного копирования

Теперь подумаем, как бы нам автоматизировать процесс создания резервных копий базы данных. Итак, существует программа — cron. Она позволяет запускать процессы в указанное пользователем время или с определенной периодичностью. Сразу оговоримся — cron в общем случае существует только под Unix, так что, если Вы используете для хостинга ОС Windows, проконсультируйтесь со своим хостинг-провайдером о том, как лучше запускать процессы в нужное время. Да и вообще, пожалуй, этот пункт будет интересен только unix-пользователям.

В unix shell запускаем crontab -e и создаем такое правило запуска процесса создания копий базы:

0 0 * * * mysqldump -uLOGIN -PPORT -hHOST -pPASS DBNAME
	| gzip -c &gt; `date "+%Y-%m-%d"`.gz

Эта команда, запускаясь из cron в полночь (00:00) каждых суток, делает дамп Вашей базы DBNAME и архивирует его архиватором gzip в файл-архив с именем, соответствующим текущей дате. Например, если мы делаем dump 3 января 2002 года, имя файла с архивом будет 2002-01-03.gz. Для того, чтобы получить файлы, по именам которых можно удобно узнать дату их создания, мы используем команду date, которая является стандартной для всех unix-систем. Эта команда позволяет задавать произвольный формат вывода даты, что мы и использовали — date «+%Y-%m-%d». Мы поместили эту команду в обратные одинарные кавычки (backticks), что в unix shell заставляет вставить в команду (утрируя) результат выполнения другой команды.

Сохраняем правило для cron и ждем результатов. Итак, каждый день мы будем иметь на диске заархивированную копию нашей базы данных. Можно быстро найти нужный архив по его названию и восстановить то, что испортилось, например. Кстати, если Вы хотите автоматизировать удаление старых архивов, попробуйте воспользоваться cron и командой find, которая обычно есть в unix. Запуская периодически

find ~/каталог-с-архивами -name "*.gz" -mtime +7

, Вы будете удалять архивы, которые «старше» семи дней. Прочитайте документацию по find — она доступна по команде man find в unix shell.

Если у Вас есть машина, постоянно подключенная к интернет, можно так же по cron копировать созданный Вами backup на нее. Конечно, провайдерская хостинг-машина — это очень надежная штука. Однако, как говорится, «береженого Бог бережет». Старая как мир истина в определенных условиях может и Вам помочь. Используйте для копирования на другую машину команды ftp и scp. Добавьте их запуск в cron. Если Ваша машина поддерживает соединение по протоколу ssh, используйте secure copy клиент для копирования файлов — scp. Читайте документацию по этой команде в man-странице man scp. Примерный запуск: scp 2002-01-03.gz : — закачиваем файл 2002-01-03.gz на машину your.host.ru авторизовавшись там под логином login.

Дополнительные возможности

Скрипт mysqlhotcopy, написанный на языке Perl, поможет Вам упростить создание копий баз данных и отдельных таблиц. Использование этого скрипта позволяет производить резервное копирование еще более гибко. Описание mysqlhotcopy читайте тут.