Mysql баазын лог бичих

log
mysql

#1

Mysql баазын логыг бичих жижигхэн php code бичлээ.

  1. Эхлээд бааз дээрээ 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;
  1. Дараах 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);
    ?>
  1. Дараах байдалтай хадгалагдана.


#2

Ingej bichix shaardlaga ni yu bilee? Mysql -iin ooriix ni general_log -g enable xiivel bux statement -uudiig hadgalaaad yviishd.