MySQL Tools Reference

MySQL tools are administrative tools that are available for your instances to assist you in managing and maintaining your application and database. Some of these tools are installed on database instances or specific types of database instances (replica vs. master) while others will be installed on all instances in your environment. Additional information about these tools is found below.

Binary log purge

Executable: /engineyard/bin/binary_log_purge
Configuration: /etc/engineyard/binlogpurge.yml
Category: root crontab
Default Frequency: 4 hours

Description

The binary logs are the built-in MySQL method for replicating data between servers. Since these files contain a record of all writes against a server, they can provide a valuable history of database activity.

The binary log purge tool uses the environment's DNA information about known replicas to determine the furthest behind replica. The tool will then purge one log at a time pausing 120 seconds (log_purge_sleep) and will remove up to 10 logs (max_files_purged) leaving 2 files (keep) before the furthest behind replica's position. When master-bin.000001 exists, the log purge tool will not start removing logs until your disk space is 50 percent (disk_purge_threshold) consumed.

If disk space usage does not exceed 70 percent (max_pct_maintain_binlogs) and you have at least 10GB (min_gb_maintain_binlogs) of available space, the script will attempt to maintain 24 hours (keep_binlog_hours) of binary logs. These settings can be helpful for maintaining enough binary logs to support the ability to start a replica based on an older valid snapshot.

Customization

The binary log purge tool provides several options for customization that can be modified with a custom Chef cookbook that modifies /etc/engineyard/binlogpurge.yml. If an option is not present in this file, the script will use the default.

  • keep - the number of log files prior to the furthest behind replica that will be maintained by the master.
  • disk_purge_threshold - the percentage of disk usage for when the tool will purge the first binary log (default: 50 percent).
  • log_purge_sleep - the length of time the process will wait between removing files (default: 120 seconds).
  • max_files_purged - the maximum number of files the process will remove in a run (default: 10).
  • keep_binlog_hours - the number of hours the server will attempt to maintain binary logs for (default: 24).
  • min_gb_maintain_binlogs - the minimum amount of space that must be available on /db in order to maintain all binary logs for keep_binlog_hours (default: 10).
  • max_pct_maintain_binlogs - the maximum percentage of disk usage permitted to allow keep_binlog_hours of binary logs to be maintained (default: 70 percent).

The default settings and run frequency of this tool will meet the needs of most applications. To run this script manually use:

sudo /engineyard/bin/binary_log_purge

The most common change to make with this is to increase the frequency of the cron schedule in the case where an application generates more than 60 binary logs in a 24 hour period. You can do this using a custom Chef cookbook that modifies the existing cron configuration. For example, a Chef snippet that would modify the schedule to every two hours would be:

if @node[:instance_role][/^db/]
cron "binary_log_purge" do
  minute  '0'
  hour    '*/2'
  day     '*'
  month   '*'
  weekday '*'
  command '/engineyard/bin/binary_log_purge -q'
end
end

Special features

If configured correctly, the binary log purge tool is capable of reading the position of one remote replica. To make this possible you must create an SSH tunnel that connects in both directions and the port of your remote database must be connected to port 13306 on your Engine Yard database instance. The root database password for your remote host must also match the root password of your Engine Yard database. If this is not configured, the purge tool will ignore the position of your remote replica and print a warning, but then continue log removal based on the position of your local replicas.

Mytop

Executable:mytop
Configuration:~/.my.cnf
Category: on demand user utility
URL: http://jeremy.zawodny.com/mysql/mytop/

Description

Often referred to as a top clone for MySQL, this tool connects to the running server and uses server status and process list information to display activity against the server in real time. This tool is installed and configured for your use automatically on both master and replica databases. The default configuration uses the application database user 'deploy' (usually) to monitor the server; with the limited privileges of this user, it only sees other server activity associated with this user account.

Within the console, here are some of the most frequently used options:

  • '?' - provides help about options that are available.
  • 'e' - explains a query.
  • 'f' - shows full query info.
  • 'k' - kills a thread.

If you have feedback or questions about this page, add a comment below. If you need help, submit a ticket with Engine Yard Support.

Comments

Article is closed for comments.