4

I'm trying to use a program called MySql Administrator, though I'd also be willing to use the Command Prompt (I am on Windows XP). Typically I start MySql via the XAMPP control panel, and I would like to make it so that MySql monitors changes to at least two of my databases. Because I don't make a lot of changes, just keeping a log turned on all the time wouldn't be an issue.

Ideally, I the changes made to the database will be visible via a log file that updates each time a query is made. If that isn't possible, being able to check via MySql Administrator or via the MySql command line would be fine. I'm not sure how to configure MySql to always start with the log on, though. Is that possible? If so, how is it done? Please, tell me the specific location of the files that need to be edited, also. I've been told to edit my my.cnf, but there are many my.cnf-type files in MySql's subdirectories. So, I need to know what specifically to do, without any of the ambiguity....

Wolfpack'08
  • 3,982
  • 11
  • 46
  • 78
  • Have you read http://dev.mysql.com/doc/en/query-log.html? It's the top hit when I search Google for "MySQL general query log". – eggyal Jul 02 '12 at 00:12
  • Yes, and the information there is very unclear, of course. Actually, I'm hoping to see how to do this because it isn't described clearly in the manual page. – Wolfpack'08 Jul 02 '12 at 00:49
  • "*To specify the initial general query log state explicitly, use [`--general_log[={0|1}]`](http://dev.mysql.com/doc/en/server-system-variables.html#sysvar_general_log). With no argument or an argument of 1, [`--general_log`](http://dev.mysql.com/doc/en/server-system-variables.html#sysvar_general_log) enables the log.*" Both of those links take you to a table which states "**Option-File Format: `general-log`**". The location of the option file into which that should be inserted depends on your system configuration. – eggyal Jul 02 '12 at 00:53
  • Here's a quote from the link: "The default value depends on whether the --general_log option is given. The destination for log output is controlled by the log_output system variable; if that value is NONE, no log entries are written even if the log is enabled." Does this mean that toggling the option at the command line turns the log on until it is turned off, or do I need to start MySQL with the command, every time? I need to set an output file, and it isn't described. There's a link to "log-output", and the commands aren't shown together. Are the entered together, in succession, whenx2? – Wolfpack'08 Jul 02 '12 at 04:03
  • Check out some command line stuff: `mysql> --general-log -> --general-log 1 -> --general-log[1] -> --log-output[=mylog] -> Bye Ctrl-C -- exit! C:\Documents and Settings\user>mysqld --general-log[1]` Five minutes blew by.... Just froze up there. `^C`. – Wolfpack'08 Jul 02 '12 at 05:14
  • Anyway, like I told you, I already read that page. A direct quote is just an insult. – Wolfpack'08 Jul 02 '12 at 07:36
  • I'm sorry if you found it insulting: I was trying to point you to the relevant part of the page. I suspect, if you had mentioned in your question that you had read that page from the manual but were having trouble understanding it (and furthermore, quoted the parts you didn't understand with a specific question about your understanding), you would have received more help. The `--general_log` command line arguments should be passed to the `mysqld` command that starts the server... – eggyal Jul 02 '12 at 07:49
  • ...whilst the `general-log` form can go in the relevant option file instead, to save one providing a command-line argument. However, as mentioned previously the location of that file is entirely system dependent and you would need to provide further details of your system configuration for one to be able to advise further. Alternatively, one can switch on the general query log in an aleady-running MySQL instance with `SET GLOBAL general_log = 'ON'` (also given on the manual page to which I originally linked). – eggyal Jul 02 '12 at 07:50
  • @eggyal If the manual made sense, more people would use the software. The my.ini file (which is easy to find, regardless of the system), is the file that needs to be altered, not the my.cnf file. If you can type `SET GLOBAL general_log = 'ON'` from the MySQL command line, that wasn't made clear by you, with your statements here, or by the manual. I'll have to test it and see if it works. – Wolfpack'08 Jul 03 '12 at 00:39
  • This is the result: `mysql> SET GLOBAL general_log = 'ON' ->`. – Wolfpack'08 Jul 03 '12 at 00:39

1 Answers1

8

You can enable it via MySQL Administrator in the settings->logging tab, but I don't think that this actually enables logging. When I turned it on, restarted MySQL, restarted MySQL Administrator, restarted my system, etc., the log file never came up. I edited the my.ini file, in the base MySQL directory. I found the line log= and altered it, also adding a couple lines after:

log = "you_name_it_query.log"
general-log=1
general-log-file = "you_name_it_query.log"

I was then able to browse the general query log via MySQL administrator.

I also used some programs mentioned here: https://superuser.com/questions/443499/detect-and-monitor-changes-to-mysql-database and here: Compare two MySQL databases for my purpose. There are some logging options in some of the programs mentioned by the asker.

Community
  • 1
  • 1
Wolfpack'08
  • 3,982
  • 11
  • 46
  • 78