Mysql баазын логыг бичих жижигхэн php code бичлээ.
- Эхлээд бааз дээрээ mysql_log гэсэн table үүсгэнэ.
CREATE TABLE `mysql_log` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`action` varchar(10) NOT NULL,
`table_id` int(11) NOT NULL,
`table_name` varchar(50) DEFAULT NULL,
`data` mediumtext,
`date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;- Дараах php кодыг ажиллуулна. / Нэг ажилуулсан бол дахиж ажилуулах шаардлага байхгүй/. Энэхүү кодыг ажилуулсанаар бүх table дээр insert, update, delete хийх үед mysql_log table дээр хадгалагдах trigger-үүд үүсч байгаа.
<?php
/**
* Created by Ankhbaatar.
*/
$db['host'] = 'localhost';
$db['username'] = 'username';
$db['password'] = 'password';
$db['database'] = 'database';
$link=mysqli_connect($db['host'], $db['username'], $db['password'], $db['database']);
mysqli_set_charset($link,"utf8");
// buh table-iin
$query_table_list=mysqli_query($link, 'show tables');
while($table = mysqli_fetch_row($query_table_list)) {
if(in_array($table[0], ['mysql_log', 'logs', 'user_sessions'])) { continue; }
// table-iin baganiin medeelel
$query=mysqli_query($link, 'SHOW COLUMNS FROM '.$table[0]);
$fields=[];
while ($row=mysqli_fetch_object($query)){
$fields[]=$row->Field;
}
$data='';
foreach ($fields as $field){ $data .="'\"".$field."\"', '=>\"', ifnull(new.".$field.", 'NULL'), '\" ', "; }
//insert trigger
$query = "CREATE TRIGGER `trigger_" . $table[0] . "_insert_log`
AFTER INSERT ON `" . $table[0] . "`
FOR EACH ROW
INSERT INTO `mysql_log`
(`id`, `action`, `table_name`, `table_id`, `data`, `date`)
VALUES (NULL, 'insert', '" . $table[0] . "', new.id, concat(".$data." ' '), CURRENT_TIMESTAMP); ";
mysqli_query($link, $query);
//edit update
$query = "CREATE TRIGGER `trigger_" . $table[0] . "_update_log`
AFTER UPDATE ON `" . $table[0] . "`
FOR EACH ROW
INSERT INTO `mysql_log`
(`id`, `action`, `table_name`, `table_id`, `data`, `date`)
VALUES (NULL, 'update', '" . $table[0] . "', new.id, concat(".$data." ' '), CURRENT_TIMESTAMP); ";
mysqli_query($link, $query);
$data='';
foreach ($fields as $field){ $data .="'\"".$field."\"', '=>\"', ifnull(old.".$field.", 'NULL'), '\" ', "; }
//delete trigger
$query = "CREATE TRIGGER `trigger_" . $table[0] . "_delete_log`
BEFORE DELETE ON `" . $table[0] . "`
FOR EACH ROW
INSERT INTO `mysql_log`
(`id`, `action`, `table_name`, `table_id`, `data`, `date`)
VALUES (NULL, 'delete', '" . $table[0] . "', OLD.id, concat(".$data." ' '), CURRENT_TIMESTAMP); ";
mysqli_query($link, $query);
}
mysqli_close($link);
?>- Дараах байдалтай хадгалагдана.
