With mysqldump you can only safely use --single-transaction
if all your tables are InnoDB, otherwise your backup is inconsistent.
If you have the requirement for a hybrid backup, then you need the lock-tables
on all tables in the backup (default), which will be safe for all engines. It's also worth mentioning that the default options will make sure your backup is safe, you don't need to turn any special flag on.
Note: If you do have a hybrid mix, perhaps look at xtrabackup. It will only be locking during the MyISAM phase of the backup.
Binary Logs
As for your binary logs, the bad magic number is making reference to an encoding done at the beginning of the a binary log. It is a base64 constant that looks something like : 0xfe 0x62 0x69 0x6e
. This is referred to the binlog magic number. If this encoding is not in this format, it would be reported as corrupt.
You should erase all binary logs and start fresh.
In order to clear them, run this in the mysql client
mysql> RESET MASTER;
mysql> SHOW MASTER STATUS;
This will erase all binary logs and show you the first binary log.
I have written posts on this issue before
InnoDB Options
It is OK for the InnoDB options to be around. Keep in mind that every time mysql is restarted, InnoDB's infrastructure is validated
- mobiledoc_InnoDb_data1 exist in the innodb_data_file_path (
D:/eClinicalWorks/mysql/data
)
- mobiledoc_InnoDb_data1 must be at least 300M
- innodb_data_home_dir has ib_logfile0/ib_logfile1
- innodb_log_file_size=256M (checks if ib_logfile0/ib_logfile1 are 256M)
- innodb_buffer_pool_size=256M (allocates the 256M in RAM)
- innodb_log_buffer_size=8M (allocates the 8M in RAM)
If none of your tables are InnoDB but you want to keep the present InnoDB settings and corresponding files, I have a great suggestion:
Add this under the [mysqld] section of my.ini
[mysqld]
skip-innodb
then run
C:\> net stop mysql
C:\> net start mysql
This will make mysqld startup faster because all the steps for InnoDB initialization/validation are bypassed. The memory InnoDB is configured to use (264M [256 + 8]) will not be allocated.
Give it a Try !!!
Best Answer
Software that uses MyISAM tables is often a sign that you are dealing with amateur quality software. There have been very few valid reasons to use MyISAM for several years, now... ACID and intelligent crash recovery and foreign key constraints are just a few things that come to mind that MyISAM will never dream of being able to provide.
Turning off InnoDB because you aren't using it is also an exercise in futility and short-sighted at best. It is essentially just tinkering with your server for no good reason. Leave that setting, and virtually all of the other settings, at their default values unless you have a specific reason to change them. Tweaking parameters requires discipline and deliberate, well-informed action.
Oracle has officially deprecated the ability to do this, anyway. As of now, in 5.6.23, disabling InnoDB still works, but results in a warning that it is a capability that will subsequently be removed... which should tell you something about whether it's a good idea. http://dev.mysql.com/doc/refman/5.6/en/innodb-turning-off.html