Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Coming from a SQL Server shop, I now work with MySQL and I was curious.

What are the differences between MySQL's binary log and MSSQL's transaction log?

By the outlook thus far, it seems that there is only one binary log per MySQL instance as opposed to a transaction log per database as in MSSQL.

share|improve this question
    
It would be awesome to see a comparison between the two. –  RMacy Dec 14 '11 at 21:37

1 Answer 1

up vote 2 down vote accepted

Answering only the MySQL Part of the Question

A binary log records completed SQL statements. You can have many binary logs. Under default settings, binary logs rotate at the 1G mark (see expire_logs_days and max_binlog_size).

You can see binary logs by running one of the following:

SHOW BINARY LOGS;
SHOW MASTER LOGS;

Ther current master log is always the last one in the list. To see just the last binary log, which is the current one, run this:

SHOW MASTER STATUS;

When it comes to InnoDB storage engine and transactions

  • There is a metadata file (ibdata1, which holds, by default, data pages, index pages, table metadata and MVCC information), also known as the InnoDB tablespace file.
  • You can have more than one ibdata file (see innodb_data_file_path)
  • There are redo logs (ib_logfile0 and ib_logfile1)
  • You can have more than two redo logs (see innodb_log_files_in_group)
  • You can spread data and indexes across multiple ibdata files if innodb_file_per_table is disabled
  • You can separate data and index pages from ibdata into separate tablespace files (see innodb_file_per_table and StackOverflow Post on how to set this up)
share|improve this answer
    
Ah I see, thank you for the response Rolando –  RMacy Dec 14 '11 at 19:58

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.