MySQL database maintenance

Explain how to perform database maintenance tasks in MySQL?

MySql maintains a variety of logs that help to maintain the database. By default all logs are created in the mysqld data directory. These logs can also be flushed. Mysql also offers a variety of back up strategies. Back up can be used to recover data. Different tools can be used to repair the corrupted tables, flush the cache etc.Different recovery mechanisms are used to recover from data loss and crashes.

Explain the log files that MySQL maintains. i.e. error log, query log, binary log.

1. Error Log : Contains the information related to the errors encountered. The errors while that occurred while the server was running are recorded. It also logs when the mysqld started and stopped. If mysqld notices some table that needs to be repaired or checked, it writes a message on error log.

2. Query log : A query log could either be a General query log or the slow query log it records general things that mysql is doing. Any statement if received from the client is recorded in this log. The server writes information to this log when clients connect or disconnect. The slow query log records information of queries that took more than long_query_time seconds to execute or didn't use indexes.

3. Binary log : This log contains information of the statements that have updated any data or could update. For example, a DELETE statement with no matching rows. It also logs information about how long each statement took that updated data.
Post your comment