Whats the difference between Transaction logs and Binary logs?

Question

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.

Answer 1

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)