Monday, April 29, 2013

"Tools" of the trade

I figured it might be worth creating a list of the top tools of the trade that we all use.
First and foremost this is to say thanks to everyone who helps create these tools.
Second it is to allow others who do not use these to see and learn how these can be used and why.
  • MySQL command line client
    • This is a given I know but it is the best access to MySQL. 
    • mysql -p --prompt="\u@\h [\d]>\_ Master >"
  • Xtrabackup
    • ./xtrabackup --defaults-file=/etc/my.cnf --backup --stats --target-dir=~/backups/  --prepare  --export --user=root --innodb_data_home_dir=/var/lib/mysql/ --innodb_data_file_path=/var/lib/mysql/ 
  • mysqlbinlog 
  • mysqltuner 
    • A good overall view into what you could have going on with your database. 
  • Percona Toolkit 
    • exampes:
      • pt-query-digest 
        • example: pt-query-digest --ask-pass /var/lib/mysql/mysql-slow.log
      • pt-table-checksum
        • example: pt-table-checksum --ask-pass
      • pt-table-sync
        • example: pt-table-sync --no-check-triggers --ask-pass --no-check-triggers --execute --print
      • pt-show-grants 
        • pt-show-grants  --ask-pass
  • MySQL Utilities 
    • exampes:
      • python mysqldiskusage --server=root:password@localhost
      • python mysqlindexcheck --server= root:password@localhost ps_helper.schema_index_statistics
      • python mysqlprocgrep --server= root:password@localhost --match-user=root
    • This might make some people question but... Knowledge is power. This is the best location to learn about MySQL. 
    • If you have to interview a MySQL DBA candidate, pick some common active authors and ask if they know who that person is. It will allow you to understand if they research the latest trends and information around MySQL or are just content with their experience.  
  • MySQL Sandbox 
  • mytop 
    • inspired by top but with a focus on MySQL
  • innotop
    • innotop is a 'top' clone for MySQL 
    • This might also make some question. I add this because it is a quick and good way for people to get started and create a my.cnf that is better than the default installed version. If nothing else you can use it just to compare with what you think it should be. 
You might notice I am not a fan of the GUI. No offense to those GUI tools available but why GUI if you do not need it. But that is just my opinion and here is a list of GUI tools just to be fair. After all schema map is helpful and easily done with MySQL Workbench

Other tools for review: