MySQL installation configuration doesn't mention InnoDB binlogs

leftfoot's Avatar

leftfoot

28 Mar, 2014 07:35 PM

I have noticed that the MySQL installation instructions make no mention of InnoDB or its binary logs. While database type is primarily an installer decision, MySQL has changed its default database type from MyISAM to InnoDB for newer versions of MySQL. If no prior MySQL installation on the selected Cascade DB server has been done then a default InnoDB setup will not include any binary logs. Repo (ib_logfile) files are included.

KnowledgeBase and Discussions search for "ib_logfile" fail to find anything. So is there no recommendation, then, on using MySQL binary log files when InnoDB is chosen (explicitly or by default)?

  1. Support Staff 1 Posted by Tim on 28 Mar, 2014 07:55 PM

    Tim's Avatar

    Hi there,

    I checked 2 possible pages that you may have found and they both appear to mention that InnoDB is required. The pages I checked are:

    http://help.hannonhill.com/kb/database/setting-up-the-database-mysql
    http://www.hannonhill.com/kb/Database-Config/index.html

    You'll notice they both contain a line in Step 2:

    IMPORTANT: Make sure that your database is configured to use the InnoDB storage engine.

    I agree that it is pretty easy to miss, so perhaps we need to make this stand out more. Let me know if you've found other instructions on our site that don't include that information.

    As far as binary logging is concerned, we don't have any information about it on these pages as you mentioned. I'm certainly willing to add more information, it's just not something that has come up often (not sure if that's good or bad!).

    We use READ-COMMITTED isolation level on a very specific part of the application and due to this, I don't believe statement-level binary logging is compatible with the application if you happen to be using statement-based replication. In the past, we've advised others to use row-level binary logging with the software and this seems to work fine for replication purposes.

    Does that answer your question? Let me know if I can help any further.

    Thanks!

  2. 2 Posted by leftfoot on 28 Mar, 2014 08:25 PM

    leftfoot's Avatar

    While MySQL binary logs are mentioned a lot with reference to
    "replication", they also are mentioned relative to backups and recovery,
    especially MySQL server crashes.

    My primary interest at this point is in data backup. By default, MySQL
    Repo (ib_logfile...) files are created and are recommended for "crash
    recovery" but not so much for "normal" mysqldump and mysqlimport though
    the MySQL manual does mention them being used if they exist.

    However, having used MySQL bin logs in past local applications, and
    knowing that they are "disk hogs" - I am wondering about the
    effectiveness of using them and whether I should consider changing our
    Cascade CMS default MySQL configuration to make use of the bin logs in
    addition the Repo logs. Will this provide a more comprehensive backup
    and restore "landscape" or "scope" (without replication)?

  3. Support Staff 3 Posted by Tim on 31 Mar, 2014 02:24 PM

    Tim's Avatar

    I believe that the majority of our clients are running a mysqldump operation nightly to create a full backup. Then, if anything goes wrong and they have to restore a backup, they know that they'll lose whatever changes have been made since the last backup. This isn't necessarily a major problem because it's a maximum of a day's worth of work being lost.

    If you aren't performing nightly backups, I would consider making use of the bin logs as they should allow for you to perform point-in-time recovery. This way you could minimize the amount of work lost should your database ever run into problems. Like you mentioned, it does require a lot of disk space.

    I'll be curious to see if any other clients can chime in here based on their own MySQL environments.

  4. 4 Posted by leftfoot on 31 Mar, 2014 03:08 PM

    leftfoot's Avatar

    -----BEGIN PGP SIGNED MESSAGE-----
    Hash: SHA1

    Right - I was thinking of providing "point-in-time" recovery past a
    weekly DB dump, which "we" don't do right now. I do know that using
    such bin logs needs to be carefully planned as they can get to be
    quite huge and must be properly "trimmed".

  5. 5 Posted by leftfoot on 31 Mar, 2014 09:47 PM

    leftfoot's Avatar

    One thing to note is that MySQL, as of a certain version (5.1?), uses
    the InnoDB engine by default and no additional MySQL configuration is
    required. Referring to the "MySQL Configuration" section might be
    confusing, then, if someone believes that they are REQUIRED to use all
    of the parameters shown in that MySQL document page "to enable InnoDB".
    Where a version of MySQL does NOT use InnoDB as its "default engine",
    THEN something needs to be done (MySQL command-line option, MySQL DB
    setting, MySQL configuration file) to (force) the use of InnoDB.

    In "our" MySQL configuration files, running MySQL-5.1.71, there are NO
    InnoDB parameters at all and ALL of the Cascade CMS tables have an
    engine type of InnoDB:

    use `cascade703`;

    select table_name,Engine,table_rows from information_schema.tables WHERE
    table_schema=DATABASE();
    +-------------------------------+--------+------------+
    | table_name | Engine | table_rows |
    +-------------------------------+--------+------------+
    | DATABASECHANGELOG | InnoDB | 288 |
    | DATABASECHANGELOGLOCK | InnoDB | 1 |
    | QRTZ_BLOB_TRIGGERS | InnoDB | 0 |
    ...

    | cxml_workflowtriggerparameter | InnoDB | 0 |
    | cxml_xml | InnoDB | 11913 |
    +-------------------------------+--------+------------+
    93 rows in set (2.27 sec)

  6. Ryan Griffith closed this discussion on 02 May, 2014 05:01 PM.

Comments are currently closed for this discussion. You can start a new one.

Keyboard shortcuts

Generic

? Show this help
ESC Blurs the current field

Comment Form

r Focus the comment reply box
^ + ↩ Submit the comment

You can use Command ⌘ instead of Control ^ on Mac