patternsqlMinor
What are the concepts that need to be gone through for MySQL DBA?
Viewed 0 times
conceptsthewhatneedaregonemysqlthatforthrough
Problem
I am a MySQL Developer, besides I am handling some DBA activities like
backup, restore, mysql installation, etc at a top level.
Now I wanted to shift to DBA, So I wanted to know in depth, like which and concepts should be gone through as a DBA.
Also, what and all to be monitored on a daily basis.
Please help me in this.
backup, restore, mysql installation, etc at a top level.
Now I wanted to shift to DBA, So I wanted to know in depth, like which and concepts should be gone through as a DBA.
Also, what and all to be monitored on a daily basis.
Please help me in this.
Solution
Storage Engine
You should be very fluent with the use of two main storage engines: MyISAM and InnoDB
You must learn :
MySQL Replication
You should understand such things as
Troubleshooting and Tuning Queries
INFORMATION_SCHEMA
You should learn how to query the INFORMATION_SCHEMA database for table info, row counts, last access info, and things like these.
Third Party Tools
The best tools that you should embrace are the following:
This is not a comprehensive list. However, it should be enough for anyone to be proficient as a Developer/DBA.
Here is Oracle's Certification Exam Descriptions that provide lists of skills within MySQL to have and know: EXAM1 and EXAM2
IMHO Here is a good set of MySQL Books to learn from
You should be very fluent with the use of two main storage engines: MyISAM and InnoDB
You must learn :
- how data and index pages are laid out
- how table and indexes are cached
- how table formats can make significant differences in reads and writes
- how table-level and row-level locking are handled
- how queries that mix both storages engines affect performance
- how to backup data from each storages engine
MySQL Replication
You should understand such things as
- What are the I/O and SQL Threads
- When log-slave-updates is necessary for a slave
- What allows you to write on a read-only slave
- When it's appropriate to use MyISAM/blackhole on a slave for tables that are innodb on the master
- How relay logs are populated
- How to recognize slave errors
- How to filter out/fileter in SQL for specific databases and tables
- Why run backups for slaves rather than masters
Troubleshooting and Tuning Queries
- Learn how to run EXPLAIN on queries
- Learn how to create indexes to properly speed up queries
- Learn how to tune queries
- Learn how to activate the slow log
- Learn how to convert the slow log into MyISAM
INFORMATION_SCHEMA
You should learn how to query the INFORMATION_SCHEMA database for table info, row counts, last access info, and things like these.
Third Party Tools
The best tools that you should embrace are the following:
- MAATKIT
- Percona Toolkit (fork of MAATKIT also from Percona)
- XtraBackup
- MONyog / SQLYog
This is not a comprehensive list. However, it should be enough for anyone to be proficient as a Developer/DBA.
Here is Oracle's Certification Exam Descriptions that provide lists of skills within MySQL to have and know: EXAM1 and EXAM2
IMHO Here is a good set of MySQL Books to learn from
Context
StackExchange Database Administrators Q#6740, answer score: 8
Revisions (0)
No revisions yet.