MySQL tools

These include yahoo-specific and publicly-available tools used at yahoo.

• Tool suites
• Configuration
• High Availability
• Analysis
• Backup & Recovery
• Monitoring
• User Management
  • Auth plugins
• Miscellaneous
• Unsupported and Deprecated Tools

Tool suites

• percona_toolkit - collection of useful db administration tools
  • pt-table-sync - used to check or fix sync between master and slave.
  • pt-online-schema-change - used to make non-disruptive schema changes!
  • pt-stalk - event driven, data collection tool (or service)
  • pt-sift - used to browse files created by pt-stalk
  • pt-mext - used on status variable data samples to process rates of change and display them in columns
  • pt-kill - query/connection killer. match based on command, user, query run time, etc. e.g.

             # kill all processes except those for users root, event_scheduler, replicant
             # (will run every 30s by default until killed with ^C, pt-kill --stop, or sentinel file)
             pt-kill --ask-pass \
                --match-all --ignore-user '(root|event_scheduler|replicant)' \
                --victims all --print \
                --kill

             # daemonize and wake up every 15 seconds
             # kill queries with SQL matching the given string/regex
             pt-kill --print -u root -p$pwr  \
             --match-info 'FROM target_creative WHERE creative_id=' \
             --victims all --kill \
             --daemonize --interval 15 --log /home/crockerm/kill-target_creative.log

Configuration

• MysqlConfig
• MysqlConfigMulti

High Availability

• MySQLStatus - recommended tool suite for managing mysql server rotations
• YInst:yapache_mod_mysql_status
• MySQLTreeFailover

Analysis

• Tempest Tools - slow log monitoring, server analysis, tuning recommendations
• YInst:mysql_captool - MySQL Capacity Analysis

Backup & Recovery

• MySQLXtraBackup - The recommended backup tool for most systems since 2012
• YInst:mysql_backup - mysqldump-based backup script, stops slave.
• YInst:mysql_snapshot_backup - lvm snapshot backups

other pages (unreviewed):

• MySQLStandardBackup - topic overview, covering tools for different scenarios
• MySQLBackups
• Binlog purge/backup - yinst package to perform scheduled purges of binary logs, with optional backup, and optionally checking slave state to prevent purging logs that slaves haven't processed yet.

Monitoring

• YInst:ysar_mysql51
• ymon metrics and status checks: YInst:ymon_mysql51, YInst:ymon_mysql51_repl (MySQLStatus also provides some ymon checks)

User Management

• MysqlPrivilegeTools -- YInst:mysql_privilege_tools and YInst:mysql_privilege_manager
• MySQLKeydb

Auth plugins

Authentication plugins support was added in MySQL 5.5. These should probably have their own page:

• YInst:mysql_ykeydb_auth_plugin - server auth plugin for keydb/ykeykey
• MySQL55BYAuthPluginImplementation - proposed bouncer auth plugin, would require server and client-side components. Not approved as of 2013-Q2, do not use. BUG:6335087

Miscellaneous

• YInst:mysql_logrotate - rotate mysqld error, slow, and general logs
• UnmapFile - Mark a file for removal from the Linux buffer cache
• MySQL_Partition_mgmt - YInst:mysql_partition_mgmt - cron job to create date-based partitions (does not manage dropping old partitions?)

Unsupported and Deprecated Tools

• maatkit - Migrate to percona_toolkit, which is a workalike. Unmaintained since 2011.
• MySQLGadgets - unmaintained since 2008; try mysqladmin, ysar_mysql51, or percona_toolkit for a supported alternative
• YInst:mytop and mytop41 - top-like view of mysql threads; unmaintained since 2007
• YDBSlow - concept gui query/schema analysis tool proposed at Tech Pulse 2009; try Tempest? and pt-query-digest (percona_toolkit) meanwhile.
• MySQLProxy -- deprecated as of 2012. Use MySQLStatus instead.