За чистый и ясный код!

Статьи на тему программирования под веб, используя PHP, MySQL, Jquery и многое другое

Итоги уходящего 2009

Декабрь30
Отличная поисковая оптимизация сайта с помощью уникального ресурса.

До конца года осталось почти сутки и половинка, я думаю самое время подвести итог уходящего 2009-го.

Начну по порядку с начала года

  1. В феврале первый раз с семьёй увидели свою новую квартиру
  2. Устроился на работу в хорошую фирму Стелла Системз, группа Диалог
  3. Начал изучать два фреймверка на PHP — ZendFramework и Kohana
  4. Начал писать различные статьи, понял что мне это нравится и …
  5. Открыл свой блог, правда времени пипец как мало на его развитие.
  6. Сыну уже почти год и семь месяцев, дочке будет 11.
  7. Начал делать ремонт в новой квартире — это просто ПИЗДЕЦ (извините, но на русском так будет понятней)
  8. Очень всерьез занялся подготовкой к получению Зенд сертификата

В общем год считаю очень хорошим.

ЗЫ Надеюсь в первом квартале переедем на новое место )) Люблю свою любимую половинку!

Статья просмотренна 18744 раз, зашло посетителей 5671

Dump MySQL, создание резервной копии БД

Декабрь12
Вашему вниманию качественная раскрутка сайта петербург.

Предисловие

Рано или поздно у всех стоит вопрос о создании копии БД. В сети есть очень много разных программ скриптов и прочего, но они хороши тогда, когда БД маленькая, ну как сейчас у нас, но если БД весит 1-2 ГГб, что делать???? Тут путь один, использовать родную командную утилиту mysqldump — тут столько букв, что я решил описать, по моему мнению, значимые параметры.

Синтаксис

В чем её преимущество — она быстрая, с кучей параметров, скорость её работы всегда будет выше, чем у phpmyadmin и им подобным.

shell> mysqldump [options] db_name [tables]
shell> mysqldump [options] --databases db_name1 [db_name2 db_name3...]
shell> mysqldump [options] --all-databases

где
[options] — это опции которые нужны для разных случаев, об этом ниже
db_name — база(ы) данных

Создание дампа

maxnag@svn:~/maxnag> mysqldump -uroot -ppassword maxnag_dev > maxnag_dev.sql

где -uroot — это параметр u означает юзер root, название этого юзера.

-ppassword — это параметр p означает пароль пользователя root

maxnag_dev — название БД, которая дампируетса.

> maxnag_dev.sql — знак больше говорит что весь поток дампа будет записан в файл maxnag_dev.sql по пути ~/maxnag, можно прописать любой путь.

Это самый простой способ получения дампа, для большинства случаев он подходит.

Восстановление дампа

maxnag@svn:~/maxnag> mysql -uroot -ppassword maxnag_dev < maxnag_dev.sql
или
maxnag@svn:~/maxnag> mysqldump -uroot -ppassword maxnag_dev < maxnag_dev.sql

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

Параметры и примеры

Теперь самое интересное. Это параметры и примеры.

Дамп только «скелета» таблиц

Для получение дампа только скелета таблиц используется параметр —no-data

maxnag@svn:~/maxnag> mysqldump -uroot -ppassword --no-data maxnag_dev > maxnag_dev.sql

Создание дампа нескольких БД

—databases, -B Параметр позволяет указать имена нескольких баз данных, для которых необходимо создать дамп.

maxnag@svn:~/maxnag> mysqldump -uroot -ppassword -B maxnag_dev mybloog > twoDB.sql

Создание дампа с исключением некоторых таблиц

—ignore-table=db_name.tbl_name Позволяет игнорировать таблицу tbl_name базы данных db_name при создании дампа. Если из дампа необходимо исключить несколько таблиц, необходимо использовать несколько параметров «—ignore-table», указывая по одной таблице в каждом из параметров.

Создание дампа с выборочными таблицами

maxnag@svn:~/maxnag> mysqldump -uroot -ppassword maxnag_dev --tables user user_data > page.sql

Обратите внимание, что сначала идет название таблицы, а потом перечисляться нужные таблицы.

Создание дампа триггеров

—triggers Создается дамп триггеров. Этот параметр включен по умолчанию. для его отключения следует использовать параметр —skip-triggers.

maxnag@svn:~/maxnag> mysqldump -uroot -ppassword test --tables user --triggers > user.sql

Кроме таблицы user в дампе содержится еще и триггеры, кусок дампа ниже

DELIMITER ;;
/*!50003 SET SESSION SQL_MODE="" */;;
/*!50003 CREATE */ /*!50017 DEFINER=`root`@`%` */ /*!50003 TRIGGER `test_user_pass2` BEFORE INSERT ON `user` FOR EACH ROW BEGIN
SET NEW.name = LEFT(NEW.name,1);
SET NEW.otch = LEFT(NEW.otch,1);
SET NEW.pass = md5(NEW.pass
END */;;

/*!50003 SET SESSION SQL_MODE="" */;;
/*!50003 CREATE */ /*!50017 DEFINER=`root`@`%` */ /*!50003 TRIGGER `test_user_pass` BEFORE UPDATE ON `user` FOR EACH ROW BEGIN
SET NEW.name = LEFT(NEW.name,1);
SET NEW.otch = LEFT(NEW.otch,1);
SET NEW.pass = md5(NEW.pass);
END */;;

DELIMITER ;

Создание дампа процедур и ф-ций

—routines, -R — с этим ключем идет их создание.

Создание дампа планировщика заданий

—events, -E с этим ключем идет их создание.

Создание дампа в виде XML

—xml, -X Представляет дамп базы данных в виде XML

maxnag@svn:~/maxnag> mysqldump -uroot -ppassword test --tables user -X> user.xml

вот часть содержимого

<table_data name="user">
    <row>
	<field name="id">1</field>
	<field name="fam">Нагайченко</field>
	<field name="name">М</field>
	<field name="otch">В</field>
	<field name="pass">5f4dcc3b5aa765d61d8327deb882cf99</field>
	<field name="login">maxnag</field>
    </row>
</table_data>

Создание дампа с разбиением его на части

Ну это нужно, если у вас дамп несколько ГГб и естественно вытащить такой, даже в архиве проблематично, то его можно разбить на части

maxnag@svn:~/maxnag> mysqldump -uroot -ppassword maxnag_dev | split -b 300K - maxnag.sql

Обратите внимаение, что -b, означает что мы указываем на какой объем следует делить, после этого параметра идет размер, он может быть

SIZE may have a multiplier suffix:<br>
b 512, kB 1000, K 1024, MB 1000*1000, M 1024*1024,<br>
GB 1000*1000*1000, G 1024*1024*1024, and so on for T, P, E, Z, Y.<br>

потом идет ТИРЕ, а не знак больше. И в файловой системе получается файл разбитый по указанному объему, для объединения используется команда линукса cat

Ну и напоследок, создание дампа с архивацией

maxnag@svn:~/maxnag> mysqldump -uroot -ppassword maxnag_dev | gzip > maxnag.sql.gz

B получаем дамп уже в архиве.

————————

Все параметры можно чередовать. Это наиболее часто используемые мной параметры.

Статья просмотренна 50485 раз, зашло посетителей 17262

Синхронизация БД с помощью триггеров (trigger MySQL)

Декабрь3

На одном из проектов, над которым я работаю по-ночам )), стала необходимость в перестройке БД (она была денормализована), приведение её к нормальным формам, а также переписыванию скриптов. Останавливать работающий проект низя — он коммерческий.

Как же быть? Точнее — необходимо было держать новую БД актуальной, чтобы когда, все будет готово и оттестировано просто сделать переключение на новую БД и радоваться своей работе.

Я принял такое решение, создать копию БД продакшена, поработать с ней, привести к нормализованному виду и потом синхронизировать. Так я и сделал.

Теперь пришло время подумать как же я буду синхронизировать БД старую и новую? Ведь хочется, что бы при добавлении/редактировании/удалении контента в старой БД, такие же действия происходили с ним в новой БД.

На помощь пришли триггеры MySQL, краткий экскурс что это такое я дал на странице своего блог Работа trigger (триггерами) в MySQL.

И так, есть таблица новостей, в ней есть поля:

mysql> DESCRIBE lib_news;
+---------------+--------------+------+-----+---------+----------------+
| Field         | Type         | Null | Key | Default | Extra          |
+---------------+--------------+------+-----+---------+----------------+
| id            | int(11)      | NO   | PRI | NULL    | auto_increment |
| dt            | int(11)      | NO   |     | 0       |                |
| title         | varchar(250) | NO   |     |         |                |
| descr         | text         | NO   |     | NULL    |                |
| text          | text         | NO   |     | NULL    |                |
| source        | varchar(250) | NO   |     |         |                |
| source_url    | varchar(250) | NO   |     |         |                |
| keywords      | text         | NO   |     | NULL    |                |
| img1          | varchar(20)  | NO   |     |         |                |
| img2          | varchar(20)  | NO   |     |         |                |
| img3          | varchar(20)  | NO   |     |         |                |
| img4          | varchar(20)  | NO   |     |         |                |
| img5          | varchar(20)  | NO   |     |         |                |
| first         | tinyint(1)   | NO   |     | 0       |                |
| fl            | tinyint(1)   | NO   | MUL | 0       |                |
| kol           | int(11)      | NO   |     | 0       |                |
| f_yandex      | tinyint(1)   | NO   |     | 0       |                |
| f_lenta       | tinyint(1)   | YES  |     | 0       |                |
| f_lenta_email | tinyint(1)   | YES  |     | 0       |                |
| comp_tp       | int(11)      | YES  |     | 0       |                |
+---------------+--------------+------+-----+---------+----------------+
20 rows in set (0.02 sec)

А вот поля из аналогичной таблицы в новой БД, разница в 2-х полях.

поле дата `dt` в старой это INT(11), в новой TIMESTAMP;
поле ИД компании comp_tp INT(11), в новой id_comp_tp SMALLINT(4);

mysql> DESCRIBE urbanus_lib_news;
+---------------+--------------+------+-----+-------------------+----------------+
| Field         | Type         | Null | Key | Default           | Extra          |
+---------------+--------------+------+-----+-------------------+----------------+
| id            | int(11)      | NO   | PRI | NULL              | auto_increment |
| id_comp_tp    | smallint(4)  | NO   |     | 0                 |                |
| dt            | timestamp    | NO   |     | CURRENT_TIMESTAMP |                |
| title         | varchar(250) | YES  |     | NULL              |                |
| descr         | text         | YES  |     | NULL              |                |
| text          | text         | YES  |     | NULL              |                |
| source        | varchar(250) | YES  |     | NULL              |                |
| source_url    | varchar(250) | YES  |     | NULL              |                |
| keywords      | text         | YES  |     | NULL              |                |
| img1          | varchar(20)  | YES  |     | NULL              |                |
| img2          | varchar(20)  | YES  |     | NULL              |                |
| img3          | varchar(20)  | YES  |     | NULL              |                |
| img4          | varchar(20)  | YES  |     | NULL              |                |
| img5          | varchar(20)  | YES  |     | NULL              |                |
| first         | tinyint(1)   | NO   |     | 0                 |                |
| fl            | tinyint(1)   | NO   | MUL | 0                 |                |
| kol           | int(11)      | NO   |     | 0                 |                |
| f_yandex      | tinyint(1)   | NO   |     | 0                 |                |
| f_lenta       | tinyint(1)   | NO   |     | 0                 |                |
| f_lenta_email | tinyint(1)   | NO   |     | 0                 |                |
+---------------+--------------+------+-----+-------------------+----------------+
20 rows in set (0.02 sec)

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

Ниже привожу триггеры которые и делают синхронизацию.

DROP TRIGGER IF EXISTS `lib_news_ai`;;
CREATE TRIGGER `lib_news_ai` AFTER INSERT ON `lib_news`
FOR EACH ROW
BEGIN
	INSERT INTO `newdb`.`new_lib_news` SET
		`id` = NEW.id, `dt` = FROM_UNIXTIME(NEW.`dt`), `id_comp_tp` = NEW.`comp_tp`, `title` = NEW.`title`,
		`descr` = NEW.`descr`, `text` = NEW.`text`, `source` = NEW.`source`, `source_url` = NEW.`source_url`,
		`keywords` = NEW.`keywords`, `img1` = NEW.`img1`, `img2` = NEW.`img2`, `img3` = NEW.`img3`,
		`img4` = NEW.`img4`, `img5` = NEW.`img5`, `first` = NEW.`first`, `fl` = NEW.`fl`, `kol` = NEW.`kol`,
		`f_yandex` = NEW.`f_yandex`, `f_lenta` = NEW.`f_lenta`, `f_lenta_email` = NEW.`f_lenta_email`
	ON DUPLICATE KEY UPDATE
		`id` = NEW.id, `dt` = FROM_UNIXTIME(NEW.`dt`), `id_comp_tp` = NEW.`comp_tp`, `title` = NEW.`title`,
		`descr` = NEW.`descr`, `text` = NEW.`text`, `source` = NEW.`source`, `source_url` = NEW.`source_url`,
		`keywords` = NEW.`keywords`, `img1` = NEW.`img1`, `img2` = NEW.`img2`, `img3` = NEW.`img3`,
		`img4` = NEW.`img4`, `img5` = NEW.`img5`, `first` = NEW.`first`, `fl` = NEW.`fl`, `kol` = NEW.`kol`,
		`f_yandex` = NEW.`f_yandex`, `f_lenta` = NEW.`f_lenta`, `f_lenta_email` = NEW.`f_lenta_email`;
END;;

DROP TRIGGER IF EXISTS `lib_news_au`;;
CREATE TRIGGER `lib_news_au` AFTER UPDATE ON `lib_news`
FOR EACH ROW
BEGIN
	INSERT INTO `newbd`.`new_lib_news` SET
		`id` = NEW.id, `dt` = FROM_UNIXTIME(NEW.`dt`), `id_comp_tp` = NEW.`comp_tp`, `title` = NEW.`title`,
		`descr` = NEW.`descr`, `text` = NEW.`text`, `source` = NEW.`source`, `source_url` = NEW.`source_url`,
		`keywords` = NEW.`keywords`, `img1` = NEW.`img1`, `img2` = NEW.`img2`, `img3` = NEW.`img3`,
		`img4` = NEW.`img4`, `img5` = NEW.`img5`, `first` = NEW.`first`, `fl` = NEW.`fl`, `kol` = NEW.`kol`,
		`f_yandex` = NEW.`f_yandex`, `f_lenta` = NEW.`f_lenta`, `f_lenta_email` = NEW.`f_lenta_email`
	ON DUPLICATE KEY UPDATE
		`id` = NEW.id, `dt` = FROM_UNIXTIME(NEW.`dt`), `id_comp_tp` = NEW.`comp_tp`, `title` = NEW.`title`,
		`descr` = NEW.`descr`, `text` = NEW.`text`, `source` = NEW.`source`, `source_url` = NEW.`source_url`,
		`keywords` = NEW.`keywords`, `img1` = NEW.`img1`, `img2` = NEW.`img2`, `img3` = NEW.`img3`,
		`img4` = NEW.`img4`, `img5` = NEW.`img5`, `first` = NEW.`first`, `fl` = NEW.`fl`, `kol` = NEW.`kol`,
		`f_yandex` = NEW.`f_yandex`, `f_lenta` = NEW.`f_lenta`, `f_lenta_email` = NEW.`f_lenta_email`;
END;;

DROP TRIGGER IF EXISTS `lib_news_ad`;;
CREATE TRIGGER `lib_news_ad` AFTER DELETE ON `lib_news`
FOR EACH ROW
BEGIN
	DELETE FROM `newbd`.`new_lib_news` WHERE `id` = OLD.id;
END;;

Как видно из св-в триггеров, любая новая запись в старой БД таблицы новостей будет записана в новую БД с изменениями которые необходимы. При редактировании также происходит обновление записей в 2-х БД. При удалении — удаление, соответственно.

Может возникнуть вопрос, почему я не использую простые запросы INSERT INTO или UPDATE. Ответ прост, вдруг по каким-то загадочным причинам в новой БД будет существовать запись с таким же ИД, который будет добавляться со старой БД, возникнет ошибка и ничего не будет, а используя конструкцию INSERT INTO ….. ON DUPLICATE KEY UPDATE (можно об этом почитать тут) я избегаю ошибки, даже если запись с таким же ИД есть, она будет обновлена. Или наоборот записи в новой БД нет, а в старой сделали исправление записи, следовательно эта запись добавится в новую БД.

Еще может возникнуть вопрос, а как же те записи, которые были добавлены в старую БД в момент реконструкции новой? Ну этот вопрос я  не буду рассматривать в данной статье, об этом я еще не думал, но скорее всего этим займутся или EVENT или PROCEDURE.

Всем удачи.

`newdb`.`new_lib_news`

Статья просмотренна 32383 раз, зашло посетителей 10545

Работа с trigger (триггерами) в MySQL

Декабрь2

Предисловие

Триггеры это особые процедуры которые срабатывают при изменении данных в таблицы операторами INSERT, UPDATE и DELETE.

Триггеры могут срабатывать как до BEFORE так и после AFTER изменения таблицы. Получается, что триггер может быть в 6 состояниях.

INSERT (BEFORE | AFTER)
UPDATE (BEFORE | AFTER)
DELETE (BEFORE | AFTER)

Что необходимо для работы

Cервер БД версии 5.0.2 и выше
привилегии на использование этой ф-ции (TRIGGER) начиная с версии 5.1.6, до этого было SUPER, но т.к. все локально используют пользователя root, то опасаться нечего

Добавление триггера

Для того чтобы понять работу триггера необходим пример.

Задание

Необходимо при добавлении записи в табл user, пароль преобразовывать в хеш md5(), также имя и отчество преобразовывать в инициалы.

Решение

mysql> DELIMITER //
mysql> CREATE TRIGGER `test_user_pass` BEFORE INSERT ON `test`.`user`
    -> FOR EACH ROW
    -> BEGIN
    ->  SET NEW.name = LEFT(NEW.name,1);
    ->  SET NEW.otch = LEFT(NEW.otch,1);
    ->  SET NEW.pass = md5(NEW.pass);
    -> END//
Query OK, 0 rows affected (0.09 sec)

mysql> DELIMITER ;

Теперь вставляем туда запись

mysql> INSERT  INTO `user` SET `fam`='Нагайченко', `name`='Максим', `otch` = 'Валерьевич', `pass` = 'password', `login` = 'maxnag';
Query OK, 1 row affected (0.00 sec)

Что теперь в таблице

mysql> SELECT * FROM `user`;
+----+-------------+------+------+----------------------------------+--------+
| id | fam         | name | otch | pass                             | login  |
+----+-------------+------+------+----------------------------------+--------+
|  1 | Нагайченко  | M    | В    | 5f4dcc3b5aa765d61d8327deb882cf99 | maxnag |
+----+-------------+------+------+----------------------------------+--------+

1 row in set (0.00 sec)

Как видно всего несколькими строчками можно убрать целые методы, которые мы использовали при регистрации нового пользователя. Теперь еще надо создать триггер на UPDATE таблицы, с таким же телом, чтобы пользователь не смог записать полное имя, отчество и пароль не в МД5();

Создание триггера на BEFORE UPDATE

mysql> DELIMITER //
mysql> CREATE TRIGGER `test_user_pass2` BEFORE UPDATE ON `test`.`user`
    -> FOR EACH ROW
    -> BEGIN
    ->  SET NEW.name = LEFT(NEW.name,1);
    ->  SET NEW.otch = LEFT(NEW.otch,1);
    ->  SET NEW.pass = md5(NEW.pass);
    -> END//
Query OK, 0 rows affected (0.09 sec)

mysql> DELIMITER ;

Обновление записи

mysql> UPDATE `user` SET `fam`='Иванов', `name`='Иван', `otch` = 'Иванович', `pass` = 'пароль', `login` = 'ivan' WHERE id=1;
Query OK, 1 row affected (0.00 sec)

Итог

mysql> SELECT * FROM `user`;
+----+-------------+------+------+----------------------------------+--------+
| id | fam         | name | otch | pass                             | login  |
+----+-------------+------+------+----------------------------------+--------+
|  1 | Иванов      | И    | И    | e242f36f4f95f12966da8fa2efd59992 | ivan   |
+----+-------------+------+------+----------------------------------+--------+

1 row in set (0.00 sec)

Могут возникнуть вопросы, что такое NEW в теле триггера,
NEW — для доступа к новым записям
OLD — для доступа к старым записям

Напимер, если я обновил фамилию, то новое значение мне доступно через NEW.fam, а к старому OLD.fam

Изменение триггера

А вот команды по изменению триггера я не нашел и был удивлен, почитал форумы, так и есть — её просто нет ((

Alter trigger

Удаление триггера

Для удаления триггера используется, как обычно оператор DROP, пример

DROP TRIGGER [IF EXISTS] [schema_name.]trigger_name

где
schema_name — название БД,
trigger_name — название триггера

Список созданных триггеров

Показать триггер можно с помощью команды

SHOW TRIGGERS [{FROM | IN} db_name]
    [LIKE 'pattern' | WHERE expr]

Это полная часть команды, в основном пользуются командами:

SHOW TRIGGERS [FROM db_name] [LIKE 'pattern']
или
SHOW TRIGGERS

Это полная часть команды, в основном пользуются командами:

SHOW TRIGGERS [FROM db_name] [LIKE 'pattern']
или
SHOW TRIGGERS

Вот что мы увидем, когда выполним команду

mysql> SHOW TRIGGERS\G;
*************************** 1. row ***************************
             Trigger: test_user_pass
               Event: INSERT
               Table: user
           Statement: BEGIN
                          SET NEW.name = LEFT(NEW.name,1);
                          SET NEW.otch = LEFT(NEW.otch,1);
                          SET NEW.pass = md5(NEW.pass);
                      END
              Timing: BEFORE
             Created: NULL
            sql_mode: STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
             Definer: root@localhost
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: utf8_unicode_ci
1 row in set (0.01 sec)
Хороший ассортимент выбора книг, электроники, бытовой техники, посуды, подарки, сувениры и много другое Вы можете сделать на сайте компании E5.RU. Купить фильмы на dvd в фирменной упаковке и сделать хороший подарок родным и близким, Вы можете там же.

Статья просмотренна 81140 раз, зашло посетителей 37367

  

Облако тегов

cli csv dump events form Kohana locale models MySQL mysqldump orm PHP tools trigger validate газ газовый счетчик итоги кеширование переменные

Облако тегов плагина WP Cumulus для WordPress требует для просмотра Flash Player 9 или выше.

Я на твиттере!

  • у твиттера тоже бывают перерывы...

Календарь

Декабрь 2009
Пн Вт Ср Чт Пт Сб Вс
« Ноя   Янв »
 123456
78910111213
14151617181920
21222324252627
28293031  

Сейчас на сайте