Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2004
    Posts
    145

    Unanswered: Binary Log per Database

    Hello,
    Is it possible to have a separate binary log per database? We have two production db's on the same server and the current binary log gets to be about a gig every few days.
    GG
    Code:
    On Error Goto Hell
    
    Hell:
        Msgbox("An error occurred, but was sent to Hell. You may continue.")

  2. #2
    Join Date
    Jan 2004
    Posts
    145
    Hello, just revisiting this. SQL Server handles each transaction log separately per database. The binary log seems to be MySql's version of the transaction log, but I haven't found a way to generate a separate log per database.
    GG
    Code:
    On Error Goto Hell
    
    Hell:
        Msgbox("An error occurred, but was sent to Hell. You may continue.")

  3. #3
    Join Date
    Jan 2004
    Posts
    145
    I've found a solution, but it's not as easy as SQL Server. That being said it will have it's advantages. Anyway what I'm going to do is create separate mysql instances using mysqld_multi. This will allow me to specify different data and logging directories and files per instance. Basically each instance has it's own section in the my.cnf file where you can specify any option you like. In addition you can specify global options that all instances will use.
    GG
    Code:
    On Error Goto Hell
    
    Hell:
        Msgbox("An error occurred, but was sent to Hell. You may continue.")

  4. #4
    Join Date
    Jan 2009
    Location
    NYC
    Posts
    11
    what are you trying to do?
    mysqlbinlog command offers the --database option to filter the binlog to show entries for that database.

    http://dev.mysql.com/doc/refman/5.0/en/mysqlbinlog.html

  5. #5
    Join Date
    Jan 2004
    Posts
    145
    Alex,
    Currently we have two websites using the same MySql server, separate DB's, as a backend. I couldn't find a way, other than what I previously suggested, to have a separate binlog for each database on that server. As far as I can tell both are forced to use the same binlog. My initial goal was to have a seperate physical binlog file for each database.

    I'm curious if you have an answer but I'll probably go with my solution because it offers other benefits like separate logging/data directories and files, separate user accounts, etc and the ability to start and stop without disrupting other websites. It will be easier to analyze the logs since I won't have to determine which db is creating the activity.
    GG
    Code:
    On Error Goto Hell
    
    Hell:
        Msgbox("An error occurred, but was sent to Hell. You may continue.")

  6. #6
    Join Date
    Jan 2009
    Location
    NYC
    Posts
    11
    Whatever floats your boat as long as it's not on windows.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •