Mysql – Server has antivirus. Do I need to enable external locking on MySQL

centoslinuxMySQLwindows

If you only read tables with other programs, external locking is not required

Assuming the bytes of MySQL files does not match viruses, the antivirus will not change the contents of the file.

However, the antivirus may change file attributes and "do some other things".

Do I need to enable external locking on MySQL to prevent (possible) data corruption?

(I'm using both InnoDB and MyISAM.)

Best Answer

No, external locking is (was) only needed for MyISAM when you tried to execute several processes on the same data directory, such as two server instances, or an instance and a file repair.

Sincerely, I would be surprised if that page manual was accurate and was still fully supported. In any case, it is mostly impossible to do that now, as InnoDB (default engine) disallows 2 instances on the same server at the same time.

In summary, an antivirus scan is not a problem for MySQL, unless it changes the files or moves them away; and if the resources it uses do not interfere with the database execution. I wouldn't run, however, an antivirus software on a production server, unless it was for scanning the network (emails or desktop clients).

Related Solutions

Mysql – How to Prevent an Ever-Growing index file (MyISAM engine) From Causing Disk Fragmentation in Windows (NTFS partition)

When you perform any OS defragmentation or virus scan operations, please make sure mysql is shutdown. No utilities (mysql-based or otherwise) should be running against any table while the server is up. MySQL is not good to run on a PC that is not dedicated to MySQL DB only, especially in the presence of defragmentation or virus scan software running. Temp tables have also been known to get whacked by virus scan software.

At the very least, move the MyISAM table to a folder away from the standard datadir.

You should consider defragmenting the MyISAM table using:

OPTIMIZE TABLE calllog;

or you can do it in stages:

CREATE TABLE calllognew LIKE calllog;
ALTER TABLE calllognew DISABLE KEYS;
INSERT INTO calllognew SELECT * FROM calllog;
ALTER TABLE calllognew ENABLE KEYS;
ALTER TABLE calllog RENAME calllogold;
ALTER TABLE calllognew RENAME calllog;
DROP TABLE calllogold;

You may need to consider upgrading to MySQL 5.5 for the latest MyISAM storage engine code.

If you want to switch to InnoDB, you must configure it so that each table has its own tablespace.

Mysql – Does having Default Database Engine MyISAM and keeping InnoDB tags in the.ini create any problem

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

Oct 28, 2012 : Binlog has bad magic number
Dec 26, 2011 : mysql emits BINLOG rows even though binlog_format=STATEMENT

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 !!!