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
Category: root crontab
Default Frequency: 4 hours
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 (
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.
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
max_pct_maintain_binlogs- the maximum percentage of disk usage permitted to allow
keep_binlog_hoursof 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:
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:
cron "binary_log_purge" do
command '/engineyard/bin/binary_log_purge -q'
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.
Category: on demand user utility
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.
Please sign in to leave a comment.