GOAL
Question:
What is the impact (if any) of installing virus protection software on the server where MySQL is installed and running?
SOLUTION
Answer:
Antivirus, virus scanners, file indexing, malware scanners, and backup utilities have been known to cause a wide variety of problems with MySQL.
Antivirus programs and other malware scanners may accidentally recognize a virus pattern randomly appearing as binary information within your data.
This would be devastating to your data if the program decided to repair or quarantine this particular file.
Antivirus programs and other scanners like malware or file search/indexing utilities typically scan, index, and monitor changes to the files on the system they have been told to monitor.
MySQL is written with the expectation that all of the files it manages are instantly and constantly available for database operations. This means that should a program decide it wants to scan a MySQL file (data file or log file) and it captures a lock on that file in a brief idle moment, such as one that exists when MySQL flushes its files to disk, then MySQL will interpret that as a file system failure (the file was no longer available) and shutdown with errors. Also since the file on disk is inconsistent with the image of the file buffered in memory at the moment your filesystem stopped responding to requests for the file itself (because the file was blocked for a scan and could receive no more updates), then the on-disk image of that data is corrupt.
Some backups of MySQL instances may be unusable
Just as in the case of scanners interfering with how MySQL writes data to disk, backup applications can also interfere with the normal flow of data between MySQL and its files. Some backup utilities do not lock files but they use ‘snapshot’ technology to avoid those locks. This is normally just fine except that MySQL may still be buffering part or parts of several files before flushing them to disk. As the on-disk image is incomplete, the backup is corrupt and our automated repair-on-restart processes may not be able to correct for the differences. There are several strategies to avoid this problem:
- Perform a safe shutdown of your MySQL instance before taking your complete backup
- If you use no InnoDB data at all, you can force a flush to disk before the backup by using the command FLUSH TABLES WITH READ LOCK. As long as the session that takes this lock remains active, your data on disk will not be changing.
- Use a dump or other backup program to create a static image of your data in a format that is certain to be restorable. Some options include ‘mysqldump’ and MySQL Enterprise Backup. Note: There is a rare possibility that MySQL Enterprise Backup, itself, will interfere with the actual data managed by MySQL. It uses a file-level copy process to generate its hot image of the data which on rare occasions has resulted in blocking mysqld from using a file it needs. For full details of the very low probability of this happening, please review http://bugs.mysql.com/bug.php?id=71516
Some programs and utilities are (or are packaged with) firewalls or other network traffic filtering or management code.
This can interfere with your clients attempts to connect or remain connected to a MySQL instance. As an example, a MySQL customer’s applications were failing at apparently random intervals. They traced the failures within their application to a sudden loss of connectivity with the MySQL database. The database itself was always available as it was still serving other copies of the application and no other problems, shutdowns, or restarts were being recorded into the error log. What the source of the problem turned out to be was the firewall. It had detected that the application had not communicated with MySQL in more than 6 hours so it was closing the connection between the two points due to an idle timer expiring.
As a general rule we STRONGLY ADVISE you to create both file-scanning and port-monitoring exceptions for MySQL within any system maintenance software you may install. Failure to do so for other customers has crashed MySQL and corrupted their data requiring them to restore from backup in order to return to service. In some very rare cases, their backups were also unusable due to the method they used to take their backups. Those situations required extensive support and downtime to recover as much data as possible. You can avoid this situation by both minimizing the interference of system maintenance software to your live MySQL instances and by performing proper backup procedures then occasionally validating your backups by testing if they can be restored properly.
Please read these links to provide additional details for creating a safe backup of MySQL or repairing damaged MySQL instances
MySQL Backup types-
https://dev.mysql.com/doc/refman/5.7/en/backup-types.html
MySQL Backup methods-
https://dev.mysql.com/doc/refman/5.7/en/mysql-enterprise-backup.html
https://dev.mysql.com/doc/refman/5.7/en/innodb-backup.html
https://dev.mysql.com/doc/refman/5.7/en/backup-methods.html
Database repair and recovery information-
https://dev.mysql.com/doc/refman/5.7/en/check-table.html
https://dev.mysql.com/doc/refman/5.7/en/repair-table.html
https://dev.mysql.com/doc/refman/5.7/en/mysqlcheck.html
https://dev.mysql.com/doc/refman/5.7/en/myisam-table-maintenance.html
Leave a Reply