MySQL/MariaDB DBA Interview Questions and Answers - Part 2
Q21. How a Galera cluster works?
answer:
Q22. What are the different types of replication in MySQL?
answer:
MySQL supports various replication topologies, including master-slave replication, master-master replication, and group replication. Each has its own benefits and use cases.
Q23. What is the purpose of the binary log in MySQL?
answer:
The binary log contains a record of all changes made to the MySQL database. It is used for various purposes, including replication, point-in-time recovery, and auditing.
Q24. How does MySQL Group Replication (GR) work?
answer:
Q25. What is the between MySQL Group Replication and Galera Cluster and Percona XtraDB Cluster?
answer:
Q26. How to setup MySQL replication from AWS MySQL RDS to Amazon Aurora MySQL?
answer:
Q27. What are the advantages of the Percona XtraDB Cluster (PXC)?
answer:
Q28. What is the use of ProxySQL/HAProxy?
answer:
Q29. How to use the Percona tool kit and what utilities do you know in Percona tool kit?
answer:
Q30. What is the difference between IST (Incremental State Transfer) and SST (Snapshot State Transfer) in PXC (Percona XtraDB Cluster)?
answer:
Q31. How do you restore a MySQL database from a backup?
answer:
To restore a MySQL database from a backup, you can use the MySQL command-line client or a graphical tool like phpMyAdmin. The process typically involves creating an empty database, importing the backup file, and ensuring data consistency.
answer:
Q33. What is the best way to do DDL changes without locking the table?
answer:
Q34. What are the different storage engines supported by MySQL?
answer:
MySQL supports various storage engines, including InnoDB, MyISAM, MEMORY, CSV, ARCHIVE, and more. InnoDB is the default storage engine from MySQL version 5.5 onwards.
Q35. What is the purpose of the InnoDB storage engine?
answer:
InnoDB is designed for high-performance and reliability, offering features such as transactions, row-level locking, foreign key constraints, and crash recovery.
Q36. How do you monitor the performance of a MySQL server?
answer:
You can monitor a MySQL server using various tools, including MySQL Enterprise Monitor, Performance Schema, EXPLAIN statement, slow query logs, and third-party monitoring solutions.
Q37. What is a deadlock in MySQL?
answer:
A deadlock occurs when two or more transactions wait indefinitely for each other to release resources, resulting in a state where none of the transactions can proceed. MySQL automatically detects and resolves deadlocks by rolling back one of the involved transactions.
Q38. What is the purpose of the ANALYZE TABLE statement?
answer:
The ANALYZE TABLE statement is used to analyze and update index statistics for a table. It helps the MySQL optimizer make better decisions when creating query execution plans.
Q39. How do you secure a MySQL server? what are the best practices for securing a MySQL database?
answer:
- Using strong passwords and avoiding default or easily guessable usernames.
- Limiting network access to the database server.
- Regularly applying security patches and updates.
- Using SSL/TLS for encrypted connections.
- Implementing firewall rules to restrict access.
- Applying the principle of least privilege.
Q40. What is the purpose of the mysqlcheck utility?
answer:
The mysqlcheck utility is used to check, repair, optimize, and analyze MySQL tables. It performs maintenance tasks to ensure data integrity and improve performance.
No comments:
Post a Comment