Helllo, My question is the following: using php, is it possible to create a log of all the interactions with the MySQL database which in general can be accessed by phpMyadmin. Is there a specific query or an example that I can look at? Thank you for your time.
Add a comment
|
3 Answers
There is simple way to capture SQL log. this log called general log. it captures all sql executed by all clients.
to enable general log.
mysql> SET GLOBAL general_log = 'ON';
where is log located?
mysql> SHOW variables like '%general_log%';
+------------------+--------------------+
| Variable_name | Value |
+------------------+--------------------+
| general_log | ON |
| general_log_file | /tmp/your_path.log |
+------------------+--------------------+
you want change location?
mysql> SET GLOBAL general_log_file = 'what you want';
to turn off log
mysql> SET GLOBAL general_log = 'OFF';
be careful, general log will grow big, after test done, always turn off the log
Use trigger command. First create a log table for the specific table then insert all the trigger outputs there.
Here's the sample:
CREATE TABLE `sales_category` (
`salescatid` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
`salescatname` VARCHAR(128) NOT NULL,
`salescatdesc` VARCHAR(512) NOT NULL,
UNIQUE INDEX `salescatname` (`salescatname`),
UNIQUE INDEX `salescatid` (`salescatid`)
)
COLLATE='latin1_swedish_ci'
ENGINE=InnoDB
2. Create table for log
CREATE TABLE `category_log` (
`action` ENUM('CREATE','UPDATE','DELETE') NULL DEFAULT NULL,
`salescatid` INT(10) UNSIGNED NOT NULL,
`salescatname` VARCHAR(255) NOT NULL,
`salescatdesc` VARCHAR(255) NOT NULL,
INDEX `id` (`salescatid`)
)
COLLATE='latin1_swedish_ci'
ENGINE=InnoDB;
3. Add triggers
//FOR ADD
DELIMITER #
CREATE TRIGGER ai_category
AFTER INSERT ON sales_category
FOR EACH ROW
BEGIN
INSERT INTO category_log(action,salescatid,salescatname,salescatdesc)
VALUES('CREATE',NEW.salescatid,NEW.salescatname,NEW.salescatdesc);
END;#
//FOR UPDATE
DELIMITER #
CREATE TRIGGER au_category
AFTER UPDATE ON sales_category
FOR EACH ROW
BEGIN
INSERT INTO category_log(action,salescatid,salescatname,salescatdesc)
VALUES('UPDATE',NEW.salescatid,NEW.salescatname,NEW.salescatdesc);
END;#
//FOR DELETE
DELIMITER #
CREATE TRIGGER ad_category
AFTER DELETE ON sales_category
FOR EACH ROW
BEGIN
INSERT INTO category_log(action,salescatid,salescatname,salescatdesc)
VALUES('DELETE',OLD.salescatid,OLD.salescatname,OLD.salescatdesc);
END;#
Is this what you are looking for? This is CRUD process but it is done thru SQL commands. If you what to call this I think you should put this in a function.. I am also a beginner so if there's a bug kindly tell me also. Goodluck.
Comments
To enable the query log, put this in /etc/my.cnf in the [mysqld] section
log = /path/to/query.log
remember that this logfile can grow very big on a busy server
update:
as of mysql 5.1.12 you should use
general_log=1
with mysql 5.1.29, the log option is deprecated. to specify the logfile use
general_log_file=/path/to/query.log
instead. See http://dev.mysql.com/doc/refman/5.6/en/query-log.html
Enable Query logging on the database (Note that the string 'table' should be put literally and not substituted by any table name)
SET global general_log = 1;
SET global log_output = 'table';
View the log
select * from mysql.general_log
Disable Query logging on the database
SET global general_log = 0;
Refer: http://www.jovicailic.org/2012/07/how-to-enable-general-query-log-in-mysql-under-linux/
