Hi,

Need to Track all DDL and DML Statements in the Database Server.

In Oracle, Its so simple using DDL Triggers but any similar functionality available in MYSQL?

__________________
-Murali..

Hi Murali, yes we can able to track the ddl/dml statements executed in mysql. Go thru the details given below.

MySql Daemon(mysqld) has keep tracking whatever (Ddl/ dml) statements executed on mysql server.
Running MySQL server with the binary log enabled makes performance about 1% slower.
However, the benefits of the binary log for restore operations and in allowing you to set
up replication generally outweigh this minor performance decrement.

You can store the log file in specified path.

--log-bin[=base_name]
eg.
mysqld --log-bin=/usr/mysql/data/mysqld.log

When started with the --log-bin[=base_name] option, mysqld writes a log file containing all SQL
commands that update data. If no base_name value is given, the default name is the name of the host
machine followed by -bin and the log file will be available in mysql data directory.

eg. shell>:/usr/mysql/data/mysql.bin-000001

mysqld appends a numeric extension to the binary log basename. The size of the binary log
file depends on the mysql system variables MAX_BINLOG_SIZE. If the size of the binary
log file exceeds the max_binlog_size, the server also created a new binary log file automatically.
The number increases each time the server creates a new log file, thus creating an ordered series of files.

eg. shell>:/usr/mysql/data/mysql.bin-000002

The server creates a new binary log file each time it starts or flushes the logs.


This binary log file not available for SELECT or SHOW statements. To view the content of the binary log file,
we need to use mysqlbinlog tool that will produce the text format of the binary log file.

mysqlbinlog [options] log_file ...
Eg.
shell>:/usr/mysql/data/mysqlbinlog binlog.0000003

You can write the binary log details into sql file and can able to restore when database crashed. This is one of
the most powerful utility. It plays major role in Mysql Replication (Master /Slave Setup).

(eg)
shell>:/usr/local/mysql/data # mysqlbinlog mysql-bin.000001 > restore.sql

To write the restore.sql file in different directory.
shell>:/usr/local/mysql/data # mysqlbinlog mysql-bin.000001 > /usr/tmp/restore.sql

You can extract the log details from the binary log file for a particular date/time.
shell>:/usr/local/mysql/data # mysqlbinlog --start-datetime="2007-07-10 00:00:00" mysql-bin.000241
shell>:/usr/local/mysql/data # mysqlbinlog --start-datetime="2007-07-10 00:00:00" mysql-bin.000241 > \usr\tmp\log_2007_07_10.sql

More details about mysql binary log..
MySQL AB :: MySQL 5.0 Reference Manual :: 5.11.3 The Binary Log

__________________
Keep smiling...

Hi Priyan,

>This binary log file not available for SELECT or SHOW statements. To view >the content of the binary log file,
>we need to use mysqlbinlog tool that will produce the text format of the >binary log file.

Is this mean that the binlog file only contains the DDL and DML Changes?

How to read the BinLog Files as u stated while converting the binlog file to sql, it will be in readable format?

__________________
-Murali..

Hi Priyan,

>This binary log file not available for SELECT or SHOW statements. To view >the content of the binary log file,
>we need to use mysqlbinlog tool that will produce the text format of the >binary log file.

Is this mean that the binlog file only contains the DDL and DML Changes?

How to read the BinLog Files as u stated that the details in the binlog will be in Binary Format ?

May be converting the binlog file to sql, it will be in readable format?

__________________
-Murali..

Binary log file contains all the information about whatever executed in mysql server. It also keep tracking DCL statements.

mysqlbinlog is the utility to view the binary file in the readable text format. If you use that utility it will show the details scroll down to the end of the file.

shell> mysqlbinlog mysql-bin.000240
...
...
If the size of the binary log file is large, we may not able to view the full the file,
So that we can write it into a text /sql file and can view thru notepad or anyother word processors.

eg.
shell> mysqlbinlog mysql-bin.000240 > /www/htdocs/track.txt

...

__________________
Keep smiling...

Great... Thanks

Most useful one.

__________________
-Murali..

Hi Murali,

Before MySQL 5.1.6, the server uses log files as the destination for general query log and slow query log entries. As of MySQL 5.1.6, the server provides more flexible control over the destination for log output, we can able to store the log details in table.

There are two types of log table general_log & slow_log available in mysql database. The general_log table writes whatever happens in the mysql server and another table slow_log log the slow query log informations.


To achieve this we need to modify the config file to send the output log to table .

--log-output=TABLE

We can also log the details in server log file as well as in table.

--log-output=TABLE,FILE


General_log table contains the below given fiedls.

event_time
user_host
thread_id
server_id
command_type
argument


Since the log details are available in table we can able to fetch the log details using select query.

__________________
Keep smiling...