MySQL target database pre-configuration
To set the MySQL general log table
- To add the required parameters to server configuration file, go to the %MYSQL_HOME directory, open my.cnf (for UNIX) or my.ini (for Windows) in a text editor, and then add the following parameters under
- Restart the MySQL database.
- To change the definition of the
mysql.general_log table, use the following command to change the storage engine to MyISAM: - To view the definition of the mysql.general_log table, use the following SQL command:
- To verify that the database is logging data, use the following command:
[mysqld]:
general_log=1
log_output=TABLE
mysql> SET GLOBAL general_log = 'OFF';
mysql> ALTER TABLE mysql.general_log ENGINE = MyISAM;
mysql> show create table mysql.general_log;
The structure of the log table is displayed. For example:
+-------------+-----------------------------------------------------------------
-----------------------------------------+
| Table | Create Table-----------------------------------------+
| general_log | CREATE TABLE `general_log` (
`event_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`user_host` mediumtext NOT NULL,
`thread_id` int(11) NOT NULL,
`server_id` int(11) NOT NULL,
`command_type` varchar(64) NOT NULL,
`argument` mediumtext NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='General log' |
+-------------+--------------------------------------------
mysql> select * from mysql.general_log;
Logging data is displayed. For example:
+---------------------+------------------------------------+-----------+--------
---+--------------+----------------------------------+
| event_time | user_host | thread_id | server_
id | command_type | argument |
+---------------------+------------------------------------+-----------+--------
---+--------------+----------------------------------+
| 2009-07-29 16:44:23 | root[root] @ localhost [127.0.0.1] | 1 |
0 | Connect | root@localhost on mysql |
| 2009-07-29 16:44:23 | root[root] @ localhost [127.0.0.1] | 1 |
0 | Query | select @@version_comment limit 1 |
| 2009-07-29 16:44:37 | root[root] @ localhost [127.0.0.1] | 1 |
0 | Query | show create table general_log |
| 2009-07-29 16:45:19 | root[root] @ localhost [127.0.0.1] | 1 |
0 | Query | set global general_log='OFF' |
| 2009-07-29 16:46:18 | root[root] @ localhost [127.0.0.1] | 1 |
0 | Query | select * from mysql.general_log |
+---------------------+------------------------------------+-----------+--------
---+--------------+----------------------------------+
5 rows in set (0.00 sec)