4

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.

7

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

  • 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:

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

  • I would change "# Why use MyISAM instead of InnoDB in a Slave" to "When it's appropriate to use MyISAM/blackhole on a slave for tables that are innodb on the master" If this is a slave that is meant to be an emergency failover replacement for the master you want the slave to be exact config/hardware as the master. If it's a reporting slave then changing a table to myisam might be appropriate. – atxdba Oct 13 '11 at 2:45
  • Please remember my answer is not a comprehensive list. Time would escape me if I named high availability setups, full pros and cons, and so forth. – RolandoMySQLDBA Oct 13 '11 at 3:06
  • Thanks for providing me valuable information. This will help me a lot. – Phanindra Oct 13 '11 at 7:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.