A day in the life of a DBA: Management, optimization, and survival in production

This session was presented at Faculté des sciences et technologies – Université de Lille, France

Transcript

1. Frédéric Descamps Community Manager Oracle MySQL Faculté des sciences et technologies - Université de Lille October 2025 A day in the life of a DBA Management, optimization, and survival in production
2. Who am I? about.me/lefred Copyright @ 2025 Oracle and/or its affiliates. 2
3. • @lefred • @lefredbe.bsky.social • @lefred@fosstodon.org • @lefred14:matrix.org • MySQL Evangelist @Oracle • I have been using MySQL since version 3.20 • devops believer • living in • h�ps://lefred.be Frédéric Descamps Copyright @ 2025 Oracle and/or its affiliates. 3
4. Introduction What is a MySQL DBA today? Copyright @ 2025 Oracle and/or its affiliates. 4
5. • Database administrator • Ensures data availability, performance, and security • Works closely with developers and IT teams • Must master management, monitoring, and optimization tools • Must be able to quickly resolve issues • Must stay up to date with the latest technologies and practices What is a MySQL DBA today? Copyright @ 2025 Oracle and/or its affiliates. 5
6. Morning Monitoring and Checks Copyright @ 2025 Oracle and/or its affiliates. 6
7. • Check server availability (uptime, monitoring, alerts) • Verify backups (integrity, scheduling, restoration) • Analyze logs (errors, security) • Check system dashboards (disks, swap) Monitoring and Checks Copyright @ 2025 Oracle and/or its affiliates. 7
8. • Check server availability (uptime, monitoring, alerts) • Verify backups (integrity, scheduling, restoration) • Analyze logs (errors, security) • Check system dashboards (disks, swap) Monitoring and Checks Copyright @ 2025 Oracle and/or its affiliates. 8
9. • Check server availability (uptime, monitoring, alerts) • Verify backups (integrity, scheduling, restoration) • Analyze logs (errors, security) • Check system dashboards (disks, swap) Monitoring and Checks Copyright @ 2025 Oracle and/or its affiliates. 8
10. • Check server availability (uptime, monitoring, alerts) • Verify backups (integrity, scheduling, restoration) • Analyze logs (errors, security) • Check system dashboards (disks, swap) Monitoring and Checks Copyright @ 2025 Oracle and/or its affiliates. 8
11. • Check server availability (uptime, monitoring, alerts) • Verify backups (integrity, scheduling, restoration) • Analyze logs (errors, security) • Check system dashboards (disks, swap) Monitoring and Checks Copyright @ 2025 Oracle and/or its affiliates. 8
12. During the Day Management and Optimization Copyright @ 2025 Oracle and/or its affiliates. 9
13. Management and optimization • Respond to developer requests (database creation, con�guration adjustments) • Optimize slow queries (analysis, indexing) • Capacity planning (data growth, resources) • System update planning (patches, new versions) Copyright @ 2025 Oracle and/or its affiliates. 10
14. Management and optimization • Respond to developer requests (database creation, con�guration adjustments) • Optimize slow queries (analysis, indexing) • Capacity planning (data growth, resources) • System update planning (patches, new versions) Copyright @ 2025 Oracle and/or its affiliates. 11
15. Management and optimization • Respond to developer requests (database creation, con�guration adjustments) • Optimize slow queries (analysis, indexing) • Capacity planning (data growth, resources) • System update planning (patches, new versions) Copyright @ 2025 Oracle and/or its affiliates. SELECT SELECT schema_name schema_name, , format_pico_time format_pico_time( (total_latency total_latency) ) tot_lat tot_lat, , exec_count exec_count, , format_pico_time format_pico_time( (total_latency total_latency/ /exec_count exec_count) ) latency_per_call latency_per_call, , query_sample_text query_sample_text FROM FROM sys sys. .x$statements_with_runtimes_in_95th_percentile x$statements_with_runtimes_in_95th_percentile AS AS t1 t1 JOIN JOIN performance_schema performance_schema. .events_statements_summary_by_digest events_statements_summary_by_digest AS AS t2 t2 ON ON t2 t2. .digest digest= =t1 t1. .digest digest WHERE WHERE schema_name schema_name NOT NOT in in ( ('performance_schema' 'performance_schema', , 'sys' 'sys') ) ORDER ORDER BY BY ( (total_latency total_latency/ /exec_count exec_count) ) desc desc LIMIT LIMIT 1 1\G \G 11
16. Management and optimization • Respond to developer requests (database creation, con�guration adjustments) • Optimize slow queries (analysis, indexing) • Capacity planning (data growth, resources) • System update planning (patches, new versions) Copyright @ 2025 Oracle and/or its affiliates. SELECT SELECT schema_name schema_name, , format_pico_time format_pico_time( (total_latency total_latency) ) tot_lat tot_lat, , exec_count exec_count, , format_pico_time format_pico_time( (total_latency total_latency/ /exec_count exec_count) ) latency_per_call latency_per_call, , query_sample_text query_sample_text FROM FROM sys sys. .x$statements_with_runtimes_in_95th_percentile x$statements_with_runtimes_in_95th_percentile AS AS t1 t1 JOIN JOIN performance_schema performance_schema. .events_statements_summary_by_digest events_statements_summary_by_digest AS AS t2 t2 ON ON t2 t2. .digest digest= =t1 t1. .digest digest WHERE WHERE schema_name schema_name NOT NOT in in ( ('performance_schema' 'performance_schema', , 'sys' 'sys') ) ORDER ORDER BY BY ( (total_latency total_latency/ /exec_count exec_count) ) desc desc LIMIT LIMIT 1 1\G \G * ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** * 1. 1. row row * ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** * schema_name: piday schema_name: piday tot_lat: tot_lat: 4.29 4.29 h h exec_count: exec_count: 5 5 latency_per_call: latency_per_call: 51.51 51.51 min min query_sample_text: query_sample_text: select select a a. .device_id device_id, , max max( (a a. .value value) ) as as ` `max temp max temp` `, , min min( (a a. .value value) ) as as ` `min temp min temp` `, , avg avg( (a a. .value value) ) as as ` `avg temp avg temp` `, , max max( (b b. .value value) ) as as ` `max humidity max humidity` `, , min min( (b b. .value value) ) as as ` `min humidity min humidity` `, , avg avg( (b b. .value value) ) as as ` `avg humidity avg humidity` ` from from temperature_history a temperature_history a join join humidity_history b humidity_history b on on b b. .device_id device_id= =a a. .device_id device_id where where date date( (a a. .time_stamp time_stamp) ) = = date date( (now now( () )) ) and and date date( (b b. .time_stamp time_stamp) )= =date date( (now now( () )) ) group group by by device_id device_id 11
17. • Meetings with development and IT teams (coordination, planning) are we part of devops? Management and Optimization (2) Copyright @ 2025 Oracle and/or its affiliates. 12
18. Afternoon SSecurity and Automation Copyright @ 2025 Oracle and/or its affiliates. 13
19. • User and permission management (roles, access) • Monitoring for suspicious activities (intrusions, anomalies) • Implementing security policies (encryption, audits) • Automating repetitive tasks (scripts, tools) • Preparing test environments (cloning, sandboxing, data anonymization) SSecurity and Automation Copyright @ 2025 Oracle and/or its affiliates. 14
20. The Unexpected Outages and Incidents Copyright @ 2025 Oracle and/or its affiliates. 15
21. • Identify outages (monitoring, alerts) • Analyze causes (logs, metrics) • Communicate with teams (status, impact) • Implement temporary solutions (workarounds) • Document incidents (reports, lessons learned) Outages and Incidents Copyright @ 2025 Oracle and/or its affiliates. 16
22. Daily Tools The Swiss Army Knife of MySQL DBAs Copyright @ 2025 Oracle and/or its affiliates. 17
23. Daily Tools • Monitoring: MySQL DB System , Grafana, Percona Monitoring and Management (PMM) • Backup: MySQL Enterprise Backup, Clone, MySQL Shell Dump & Load • Performance Analysis: MySQL Performance Schema, pt-query-digest (Percona Toolkit), EXPLAIN • Automation: Ansible, Puppet, Chef • Security: MySQL Enterprise Firewall, audit plugins Copyright @ 2025 Oracle and/or its affiliates. 18
24. Evening Review and Planning Copyright @ 2025 Oracle and/or its affiliates. 19
25. • Review completed tasks (checklist, logs) • Plan tasks for the next day (priorities, urgencies) • Update documentation (changes, procedures) • Check cluster status (replication, integrity) • Check for any overnight changes (deployments, updates) Review and Planning Copyright @ 2025 Oracle and/or its affiliates. 20
26. Weekends Maintenance and Updates Copyright @ 2025 Oracle and/or its affiliates. 21
27. • Software updates (MySQL, OS) ◦ Index rebuilds ◦ Table optimizations ◦ Full backup restore testing ◦ Security audits Maintenance and Updates Overnight or on weekends, the DBA can schedule maintenance tasks that require minimal service interruption, such as: Copyright @ 2025 Oracle and/or its affiliates. 22
28. Long-term Follow-up / Strategies and Implementation Copyright @ 2025 Oracle and/or its affiliates. 23
29. Strategies • Strategy development of a robust backup (retention, frequency) • Implementation of high availability (replication, clustering) • Development of a disaster recovery plan (RTO, RPO) • Adoption of best security practices (updates, audits) • Performance and scalability testing (stress tests, benchmarks) • Consideration and implementation of the cloud (migration, hybrid) • Continuous training (certi�cations, conferences) Copyright @ 2025 Oracle and/or its affiliates. 24
30. Share your to MySQL #mysql #MySQLCommunity Join our slack channel! bit.ly/mysql-slack Copyright @ 2025 Oracle and/or its affiliates. 25
31. Questions ? Copyright @ 2025 Oracle and/or its affiliates. 26