You are here
Advanced MySQL DBA Workshop - draft
With a partner we are planning and Advanced MySQL DBA workshop. As the name says it should be a workshop. Its planned duration is 2 days. So within 2 days we can cover 4 to 8 topics more deeply.
Possible exercises during the workshop
- Set-up a Master-Master replication with 2 Slaves
- Load balance on master with MySQL Proxy and on Slaves with LVS.
- Design a little schema and load with data from foodmart
- Do a backup with XtraDB and LVM
- Do a PITR and and InnoDB crash recovery (from some samples)
- Find some discrepancies between master/slave
- Run some synthetic benchmark and monitor
Introduction Admin Who we are? Tasks of a DBA Discussion for focus Planning and evaluating Collecting information Business plan, amount of users, expected traffic, peak and average traffic, requirements, amount of data, in what time range Information Life Cycle Management Plan architecture DB vs. NO-SQL vs. no database Storage Engines MyISAM/Aria InnoDB/XtraDB MySQL Cluster (NDB) Spider SE / InfiniDB / Infobright / ... Architectures Scale-out High availability (HA) Evaluate OS and Hardware Operating System Server (CPU, RAM, Network) single-thread performance I/O systems RAID SAN Discussion about your personal evaluation / experience Implement logical model into physical modelling UML to ER MySQL-Workbench Normalization vs de-normalization Schema optimizing Database standards Processes Naming conventions Maintenance and Operations Installing the Software The database Package types Compile your self Multi instance set-up's (myenv?) HA / fail-over components MySQL - Proxy LVS Load Balancer Heartbeat/DRBD/Corosync ??? Upgrade / downgrade Dump Binary upgrade Replication Configure and reconfigure the database Changeable and not changeable parameters Change and impact: how to measure Managing the database storage structure InnoDB File per table Log files The new barracuda file format Log files Binary Logs Other logs Interacting with the software vendor Report a bug with MySQL Report a bug with other providers Open a support case with MySQL Migration from/to MySQL Migration tool kit mysqldump JDBC/ODBC Stored Programs Load and unload data (ETL) Tools? SELECT INTO OUTFILE LOAD DATA INFILE INSERT vs multi row INSERT vs. LOAD DATA INFILE Operating a MySQL Database Automate Everything Backup/Restore (see further down) Maintenance task Monitoring (see further down) Data protection Backup/restore/recovery Point-in-Time-Recovery (PITR) LVM snapshots InnoDB crashes Recovery with the Percona recovery tools? Xtrabackup Maintaining database changes from development to test to production repository (VCS) mysqldump --nodata FromDual Backup/Restore/Development/Production cycle Managing users and security Role concepts Scripts to monitor users/security Data Integrity Master/Slave - find discrepancies with the Percona tools Monitoring Health / Availability Key indicators Parse the error log Some scripts which assist you in monitoring Performance vmstat / iostat / top mytop / innotop MySQL Enterprise Monitoring FromDual MySQL performance monitor Load simulation/generation Apache J/Meter Performance Management and Tuning First measure, then act, then measure again. Predicting trends Plan growth and changes (capacity planning) Profiling is everything The FromDual Performance Tuning Balance The FromDual Database Health Check Application tuning The slow query log / profile Execution plans SESSION status diff
The first run of this workshop should take place in November. Please let us know what you think about the contents, so we can adapt it to your needs...