Frequently Asked Question

1. Error Log

• Location: Configured in my.cnf (or my.ini on Windows) under the log_error setting. Default locations include, please include all the files:
  • Linux: /var/log/mysql/ or /var/lib/mysql/
  • Windows: \.err
• Configuration:

  [mysqld]
  log_error = /path/to/error.log
  

2. General Query Log

• Location: Configured in my.cnf under the general_log_file setting.
  • Default: /var/lib/mysql/mysql.log
• Configuration:

  [mysqld]
  general_log = 1
  general_log_file = /path/to/general.log

3. Slow Query Log

• Location: Configured in my.cnf under the slow_query_log_file setting.
  • Default: /var/lib/mysql/slow.log
• Configuration:

  [mysqld]
  slow_query_log = 1
  slow_query_log_file = /path/to/slow.log
  long_query_time = 2  # Log queries taking longer than 2 seconds

4. Binary Log

• Only neccesary uppon OneClickDBA request
• Location: Configured in my.cnf under the log_bin setting.
• • Default: /var/lib/mysql/.bin
• Configuration:

  [mysqld]
  log_bin = /path/to/binlog
  binlog_format = ROW
  

5. Relay Log (Replication)

• Only neccesary uppon OneClickDBA request
• Location: Configured in my.cnf under the relay_log setting.
  • Default: /var/lib/mysql/-relay-bin.
• Configuration:

  [mysqld]
  relay_log = /path/to/relay-log
  

6. Performance Schema Data

• Tables: Inside the performance_schema database.
• Queries:
  • Slowest queries:

    SELECT * 
    FROM performance_schema.events_statements_summary_by_digest
    ORDER BY SUM_TIMER_WAIT DESC LIMIT 10;
    

  • Active connections:

    SELECT * 
    FROM performance_schema.threads 
    WHERE PROCESSLIST_STATE IS NOT NULL;
    

7. Diagnostic Reports

• Tools: 
  • SHOW ENGINE INNODB STATUS; – Provides details about the InnoDB engine.
  • SHOW PROCESSLIST; – Lists active database connections.
  • EXPLAIN ANALYZE – Just in case there is a query performance problem.

8. MySQL Configuration File

• Files:
  • /etc/my.cnf or /etc/mysql/my.cnf (Linux)
  • C:\ProgramData\MySQL\my.ini (Windows)

9. Transaction Logs (Redo and Undo Logs)

• Only neccesary uppon OneClickDBA request
• Files: Found in the datadir (e.g., /var/lib/mysql/ib_logfile*).

10. Replication Logs

• Only neccesary uppon OneClickDBA request
• Description: For replication issues, include:
  • Master logs (log_bin)
  • Slave logs (relay_log)
  • SHOW SLAVE STATUS; output.

11. User Privileges

• Only neccesary uppon OneClickDBA request
• Commands:
  • List user accounts:

    SELECT user, host FROM mysql.user;
    

  • Check grants:

    SHOW GRANTS FOR 'username'@'host';
    

12. Crash Dumps

• Only neccesary uppon OneClickDBA request
  
• Location: Core dumps are system-dependent, often in /var/core/ or /tmp/.
• Configuration:
  • Enable core dumps:

    ulimit -c unlimited
    

  • In my.cnf:

    [mysqld]
    core-file
    

13. Backup Logs

• Only neccesary uppon OneClickDBA request
• Tools: Logs from backup tools like mysqldump, Percona XtraBackup, or custom scripts.

14. System Logs

• Location:
  • /var/log/messages or /var/log/syslog (Linux)
  • Event Viewer Logs (Windows)

15. Custom Debug Logs

• Only neccesary uppon OneClickDBA request
• Commands: Enable debugging for specific issues:

  mysqld --debug=d,info,error,query