To get all tables with columns columnA
or ColumnB
in the database YourDatabase
:
SELECT DISTINCT TABLE_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME IN ('columnA','ColumnB')
AND TABLE_SCHEMA='YourDatabase';
(Note: For mysql-5.6+ this won't work. There's a solution that applies to mysql-5.6+ if you scroll down or click here.)
If you don't want or cannot restart the MySQL server you can proceed like this on your running server:
- Create your log tables on the
mysql
database
CREATE TABLE `slow_log` (
`start_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
ON UPDATE CURRENT_TIMESTAMP,
`user_host` mediumtext NOT NULL,
`query_time` time NOT NULL,
`lock_time` time NOT NULL,
`rows_sent` int(11) NOT NULL,
`rows_examined` int(11) NOT NULL,
`db` varchar(512) NOT NULL,
`last_insert_id` int(11) NOT NULL,
`insert_id` int(11) NOT NULL,
`server_id` int(10) unsigned NOT NULL,
`sql_text` mediumtext NOT NULL,
`thread_id` bigint(21) unsigned NOT NULL
) ENGINE=CSV DEFAULT CHARSET=utf8 COMMENT='Slow log'
CREATE TABLE `general_log` (
`event_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
ON UPDATE CURRENT_TIMESTAMP,
`user_host` mediumtext NOT NULL,
`thread_id` bigint(21) unsigned NOT NULL,
`server_id` int(10) unsigned NOT NULL,
`command_type` varchar(64) NOT NULL,
`argument` mediumtext NOT NULL
) ENGINE=CSV DEFAULT CHARSET=utf8 COMMENT='General log'
- Enable Query logging on the database
SET global general_log = 1;
SET global log_output = 'table';
select * from mysql.general_log
- Disable Query logging on the database
SET global general_log = 0;
Best Answer
For those blessed with MySQL >= 5.1.12, you can control this option globally at runtime:
SET GLOBAL log_output = 'TABLE';
SET GLOBAL general_log = 'ON';
mysql.general_log
If you prefer to output to a file instead of a table:
SET GLOBAL log_output = "FILE";
the default.SET GLOBAL general_log_file = "/path/to/your/logfile.log";
SET GLOBAL general_log = 'ON';
I prefer this method to editing .cnf files because:
my.cnf
file and potentially permanently turning on logging/var/log /var/data/log
/opt /home/mysql_savior/var
For more information, see MySQL 5.1 Reference Manual - Server System Variables - general_log