Planet MySQL Planet MySQL: Meta Deutsch Español Français Italiano 日本語 Русский Português 中文
Showing entries 1 to 10 of 74 10 Older Entries

Displaying posts with tag: Scripts (reset)

Monitoring DML/slow queries with graphite
+1 Vote Up -0Vote Down

pt-query-digest, Anemometer or "Anemomaster" do a great job of analysing your queries and giving you visibility into what's going on with your MySQL servers. However, the place where the query digests are written is just some MySQL tables on some server. Do you have monitoring/alerts on that table? How will you verify a specific query does not exceed some runtime/execution count threshold, and get notified when it does?

At Outbrain we use Graphite to collect almost all of our data. We like it for its simplicity and for the fact it has a "push" strategy as opposed to "pull" strategy: every

  [Read more...]
Bash script: report largest InnoDB files
+1 Vote Up -0Vote Down

The following script will report the largest InnoDB tables under the data directory: schema, table & length in bytes. The tables could be non-partitioned, in which case this is simply the size of the corresponding .ibd file, or they can be partitioned, in which case the reported size is the sum of all partition files. It is assumed tables reside in their own tablespace files, i.e. created with innodb_file_per_table=1.

(
    mysql_datadir=$(grep datadir /etc/my.cnf | cut -d "=" -f 2)
    cd $mysql_datadir
    for frm_file in $(find . -name "*.frm")
    do
        tbl_file=${frm_file//.frm/.ibd}
        table_schema=$(echo $frm_file | cut -d "/" -f 2)
        table_name=$(echo $frm_file | cut -d "/" -f 3 | cut -d "." -f 1)
        if [ -f $tbl_file ]
        then
  [Read more...]
common_schema: 1.3: security goodies, parameterized split(), json-to-xml, query checksum
+1 Vote Up -0Vote Down

common_schema 1.3 is released and is available for download. New and noteworthy in this version:

  • Parameterized split(): take further control over huge transactions by breaking them down into smaller chunks, now manually tunable if needed
  • duplicate_grantee(): copy+paste existing accounts along with their full set of privileges
  • similar_grants: find which accounts share the
  [Read more...]
Super Python: three applications involving IRC bot master, MySQL optimization, and Website stress testing.
+1 Vote Up -0Vote Down

In my ongoing efforts to migrate my fun side projects and coding experiments from SVN to Git I’ve come across some of my favorite Python based apps – which are all available in their respective repos on BitBucket, as follows:

IRC Bot Commander

  • What it does: it’s an IRC bot that takes commands and does your bidding on whichever remote server the bot is installed on.
  • How it does it: the bot runs on whatever server you install it on, then it connects to the IRC server and channel you configured it to connect to and it waits for you to give it commands, then it execs the commands and returns the output to your IRC chat window.
  [Read more...]
common_schema over traditional scripts
+1 Vote Up -0Vote Down

If you are familiar with both openark kit and common_schema, you'll notice I've incorporated some functionality already working in openark kit into common_schema, essentially rewriting what used to be a Python script into SQL/QueryScript.

What was my reasoning for rewriting good code? I wish to explain that, and provide with a couple examples.

I'm generally interested in pushing as much functionality into the MySQL server. When using an external script, one:

  • Needs the right dependencies (OS, Perl/Python version, Perl/Python modules).
  • Needs to provide with connection params,
  • Needs to get acquainted with a lot
  [Read more...]
Simple jQuery: how to validate IPv4 addresses and netmasks
+0 Vote Up -0Vote Down

Unfortunately jQuery doesn’t come with default form validation to check for ip-addresses or subnet masking. So without a long winded explanation here’s the code. Just include this as a separate JS file like the rest of your page’s JS.

// 'ipv4': IPv4 Address Validator
$.validator.addMethod('ipv4', function(value) {
    var ipv4 = /^[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}$/;    
    return value.match(ipv4);
}, 'Invalid IPv4 address');

// 'netmask': IPv4 Netmask Validator
$.validator.addMethod('netmask', function(value) {
    var mask = /^[1-2]{1}[2,4,5,9]{1}[0,2,4,5,8]{1}\.
[0-2]{1}[0,2,4,5,9]{1}[0,2,4,5,8]{1}\.
[0-2]{1}[0,2,4,5,9]{1}[0,2,4,5,8]{1}\.
[0-9]{1,3}$/;    
    return value.match(mask);
}, 'Invalid IPv4 netmask');

You can use it like this.

$("#myform_here").validate({
    rules:{
	ipaddress:{
  [Read more...]
Killing InnoDB idle transactions
+0 Vote Up -0Vote Down

The issue of terminating long-time idle open InnoDB transaction has been discussed recently by many. I wish to add my share, by proposing a quick and clean solution via common_schema.

common_schema 1.2 provides with the innodb_transactions view, which relies on INNODB_TRX - one of the InnoDB Plugin views in INFORMATION_SCHEMA - as well as on PROCESSLIST, and so is able to determine with certainty that a transaction has been idle for a long time.

innodb_transactions offers us with a sql_kill_query column, which produces a 'KILL QUERY 12345' type of value. So we

  [Read more...]
Simple MySQL: using TRIGGERs to keep datetime columns updated without direct SQL calls
+0 Vote Up -0Vote Down

If you’ve ever used non-opensource code, or applications that you don’t have complete control over, then you may have run into situations you need to alter data on a per-row basis but been unable to do so for lack of application SQL access. The solution to this type of problem is to use a MySQL TRIGGER, which allows us to execute arbitrary SQL commands when defined events occur. Why is this useful and how does it work? Well…

For example, I have a freeRADIUS server that uses MySQL as a backend for the user authentication, and one of my server applications (HostBill) provides a freeRADIUS plugin that allows my users to manage their RADIUS accounts; however the default freeRADIUS schema lacks a DATETIME column on the user table. When a user is created (INSERT) or has their password changed (UPDATE)

  [Read more...]
Purging old rows with QueryScript: three use cases
+0 Vote Up -0Vote Down

Problem: you need to purge old rows from a table. This may be your weekly/monthly cleanup task. The table is large, the amount of rows to be deleted is large, and doing so in one big DELETE is too heavy.

You can use oak-chunk-update or pt-archiver to accomplish the task. You can also use server side scripting with QueryScript, offering a very simple syntax with no external scripting, dependencies and command line options.

I wish to present three cases of row deletion, with three different solutions. In all cases we assume some TIMESTAMP column

  [Read more...]
OpenCode: MySQL procedures + python + shell code repositories now public
+1 Vote Up -0Vote Down

I write a fair number of scripts on this site and have posted a lot of code over the years. Generally if I am not pasting the code to be viewed on the webpage then I link to a file that a user can download; which leads to a lot of mish-mash code that doesn’t have a home. I’ve always kept the code files in a private SVN repo over the years but have recently moved them all to BitBucket Git repositories. So here they are: lots of code samples and useful bits of programming to save time.

Generic Shell Scripts: https://bitbucket.org/themattreid/generic-bash-scripts/src
Generic Python Scripts: https://bitbucket.org/themattreid/generic-python-scripts/src
Generic MySQL Stored Procs:

  [Read more...]
Showing entries 1 to 10 of 74 10 Older Entries

Planet MySQL © 1995, 2014, Oracle Corporation and/or its affiliates   Legal Policies | Your Privacy Rights | Terms of Use

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.