Home |  MySQL Buzz |  FAQ |  Feeds |  Submit your blog feed |  Feedback |  Archive |  Aggregate feed RSS 2.0 English Deutsch Español Français Italiano 日本語 Русский Português 中文
Previous 30 Newer Entries Showing entries 31 to 60 of 74 Next 14 Older Entries

Displaying posts with tag: Scripts (reset)

Python for Automation: using pdsh for a menu-driven command execution environment
+1 Vote Up -0Vote Down

I’ve been playing around with some quick system automation scripts that are handy to use when you don’t want / need to setup a chef or puppet action. I like to keep all of my hostnames and login details in a MySQL database (a cmdb actually) but for this example we’ll just use a couple of nested lists. This script executes commands in parallel across the hosts you choose in the menu system via the “pdsh” command, so make sure you have that installed before running. Alternately you can change the command call to use ssh instead of pdsh for a serialized execution, but that’s not as fun or fast. With some customizations here and there you can expand this to operate parallelized jobs for simplifying daily work in database administration, usage reporting, log file parsing, or other system automation as you see fit. Here’s the code. Comments welcome as

  [Read more...]
Simple Python: a job queue with threading
+0 Vote Up -0Vote Down

Every so often you need to use a queue to manage operations in an application. Python makes this very simple. Python also, as I’ve written about before, makes threading very easy to work with. So in this quick program I’ll describe via comments, how to make a simple queue where each job is processed by a thread. Integrating this code to read jobs from a mysql database would be trivial as well; simply replace the “jobs = [..." code with a database call to a row select query.

#!/usr/bin/env python
## DATE: 2011-01-20
## FILE: queue.py
## AUTHOR: Matt Reid
## WEBSITE: http://themattreid.com
from Queue import *
from threading import Thread, Lock

'''this function will process the items in the queue, in serial'''
def processor():
    if queue.empty() == True:
        print "the Queue is empty!"
        job = queue.get()
        print "I'm
  [Read more...]
oak-hook-general-log: your poor man’s Query Analyzer
+0 Vote Up -0Vote Down

The latest release of openark kit introduces oak-hook-general-log, a handy tool which allows for some analysis of executing queries.

Initially I just intended for the tool to be able to dump the general log to standard output, from any machine capable to connect to MySQL. Quick enough, I realized the power it brings.

With this tool, one can dump to standard output all queries using temporary tables; or using a specific index; or doing a full index scan; or just follow up on connections; or… For example, the following execution will only log queries which make for filesort:

oak-hook-general-log --user=root --host=localhost --password=123456
  [Read more...]
openark-kit (rev. 170): new tools, new functionality
+3 Vote Up -0Vote Down

I’m pleased to announce a new release of the openark kit. There’s a lot of new functionality inside; following is a brief overview.

The openark kit is a set of utilities for MySQL. They solve everyday maintenance tasks, which may be complicated or time consuming to work by hand.

It’s been a while since the last announced release. Most of my attention was on mycheckpoint, building new features, writing documentation etc. However my own use of openark kit has only increased in the past few months, and there’s new useful solutions to common problems that have been developed.

I’ve used and improved many tools over this time, but doing the final cut, along with proper documentation, took

  [Read more...]
mycheckpoint (rev 208): aggregation tables, enhanced charting, RPM distribution
+1 Vote Up -0Vote Down

Revision 208 of mycheckpoint, a MySQL monitoring solution, has been released. New and updated in this revision:

  • Aggregation tables: aggregated data makes for fast reports on previously slow queries.
  • Enhanced charting: interactive charts now present time stamps dynamically (see demo); “Zoom in” charts are available (see demo) on mycheckpoint‘s HTTP server.
  • RPM distribution: a “noarch” RPM
  [Read more...]
A simple load test script in Python
+1 Vote Up -0Vote Down

Lately I’ve had to do some environment load testing so I wrote this quick script. It can be modified as needed but the basic idea is that it spawns $x threads (–threads) and then sends two connections (or however many you want with –per-connection=) per thread to the URL (–url=). You can have it wait a configurable time between connections as well (–wait=).

The url is appended with a 32 character randomized string so that any database/caching on the backend of the site isn’t serving data from a warm cache. You can hunt down the string length for 32 and change it to whatever you want. Feel free to change and use as needed, just keep my info at top.

## DATE: 2010-10-26
## AUTHOR: Matt Reid
## MAIL: mreid@kontrollsoft.com
## SITE:
  [Read more...]
Thoughts and ideas for Online Schema Change
+6 Vote Up -0Vote Down

Here’s a few thoughts on current status and further possibilities for Facebook’s Online Schema Change (OSC) tool. I’ve had these thoughts for months now, pondering over improving oak-online-alter-table but haven’t got around to implement them nor even write them down. Better late than never.

The tool has some limitations. Some cannot be lifted, some could. Quoting from the announcement and looking at the code, I add a few comments. I conclude with a general opinion on the tool’s abilities.

“The original table must have PK. Otherwise an error is returned.”

  [Read more...]
mycheckpoint (rev. 190): HTTP server; interactive charts
+2 Vote Up -0Vote Down

Revision 190 of mycheckpoint, a MySQL monitoring solution, has been released. New and updated in this revision:

  • HTTP server: mycheckpoint can now act as a web server. Point your browser and start browsing through HTML reports. See mock up demo.
  • Interactive charts: HTML line charts are now interactive, presenting with accurate data as you move over them. See sample.
  • Enhanced auto-deploy: now auto-recognizing failed upgrades.
  [Read more...]
Easy Python: multi-threading MySQL queries
+2 Vote Up -3Vote Down

There are many times when writing an application that single threaded database operations are simply too slow. In these cases it’s a matter of course that you’ll use multi-threading or forking to spawn secondary processes to handle the database actions. In this simple example for Python multi-threading you’ll see the how simple it is to improve the performance of your python app.

## DATE: 2010-08-30
## AUTHOR: Matt Reid
## WEBSITE: http://themattreid.com
## LICENSE: BSD http://www.opensource.org/licenses/bsd-license.php
## Copyright 2010-present Matt Reid

from __future__ import division
from socket import gethostname;
import threading
import sys
import os
import MySQLdb

class threader(threading.Thread):
    def __init__(self,method):
        self.tx =
        self.method = method
    def run(self):
  [Read more...]
mylvmbackup HOWTO: minimal privileges & filesystem copy
+2 Vote Up -2Vote Down

This HOWTO discusses two (unrelated) issues with mylvmbackup:

  • The minimal privileges required to take MySQL backups with mylvmbackup.
  • Making (non compressed) file system copy of one’s data files.

Minimal privileges

Some just give mylvmbackup the root account, which is far too permissive. We now consider what the minimal requirements of mylvmbackup are.

The queries mylvmbackup issues are:



  [Read more...]
Easy Python: display LVM details in XML
+1 Vote Up -1Vote Down

If you need to work with LVM in your scripts but haven’t found a good method to access details about Logical Volume Groups, here’s a simple Python script that will print the details about any volumes on your system. This could be useful for writing a partition check script for your MySQL data directory (if you’re not using a standard monitoring system like Nagios).

import sys
import os
import commands
import subprocess
import select

def lvm():
    print ""
    LVM_PATH = "/sbin"
    LVM_BIN = os.path.join(LVM_PATH, 'lvm')
    argv = list()

    process = subprocess.Popen(argv,
  [Read more...]
Easy MySQL: how to backup databases to a remote machine
+3 Vote Up -1Vote Down

Here’s a simple answer to a simple question. “How do I run a backup of MySQL to another machine without writing to the local server’s filesystem?” – this is especially useful if you are running out of space on the local server and cannot write a temporary file to the filesystem during backups.

Method one – this writes a remote file.
mysqldump [options] [db_name|--all-databases]| gzip -c | ssh user@host.com "cat > /path/to/new/file.sql.gz"

Method two – this writes directly into a remote mysql server
mysqldump [options] [db_name|--all-databases]| mysql --host=[remote host] –user=root –password=[pass] [db_name]

Another Python MySQL template
+2 Vote Up -2Vote Down

Following up on Matt Reid’s simple python, mysql connection and iteration, I would like to share one of my own, which is the base for mycheckpoint & openark kit scripts.

It is oriented to provide with clean access to the data: the user is not expected to handle cursors and connections. Result sets are returned as python lists and dictionaries. It is also config file aware and comes with built in command line options.

I hope it comes to use: my.py

Easy Python: MySQL connection and iteration
+3 Vote Up -0Vote Down

If you’ve been looking for a simple python script to use with MySQL that you can use to expand upon for your next project, check this one out. It has error handling for the connection, error handling for the sql call, and loop iteration for the rows returned.

import sys
import MySQLdb

my_host = "localhost"
my_user = "user"
my_pass = "password"
my_db = "test"

    db = MySQLdb.connect(host=my_host, user=my_user, passwd=my_pass, db=my_db)
except MySQLdb.Error, e:
     print "Error %d: %s" % (e.args[0], e.args[1])
     sys.exit (1)

cursor = db.cursor()
sql = "select column1, column2 from table";
results = cursor.fetchall()
for row in results:
    column1 = row[0]
    column2 = row[1]
    print "column1: %s, column2: %s"%(column1,column2)

How to: rotate wordpress posts into headline/feature status
+0 Vote Up -0Vote Down

If you’re using the new Arthemia theme for WordPress you might notice that there are two areas of the theme that can have articles promoted to; namely Headline and Featured sections. This is controlled by category association. Basically you have a post and if you want it in the Headline area of the theme you attach the category “headline” to it, similarly for the featured section. Now, let’s say you don’t want to manually change this all the time since it can be time consuming to promote posts to those categories if you want rotating content.

Here’s a simple solution. In this bash script I connect to MySQL and remove the current associations from posts and then randomly choose posts to be promoted to the Headline and Featured categories. This can be

  [Read more...]
Tips for taking MySQL backups using LVM
+6 Vote Up -0Vote Down

LVM uses copy-on-write to implement snapshots. Whenever you’re writing data to some page, LVM copies the original page (the way it looked like when the snapshot was taken) to the snapshot volume. The snapshot volume must be large enough to accommodate all pages written to for the duration of the snapshot’s lifetime. In other words, you must be able to copy the data somewhere outside (tape, NFS, rsync, etc.) in less time than it would take for the snapshot to fill up.

While LVM allows for hot backups of MySQL, it still poses an impact on the disks. An LVM snapshot backup may not go unnoticed by the MySQL users.

Some general guidelines for making life easier with LVM backups follow.

Lighter, longer snapshots

If you’re confident that you have enough space on your snapshot volume, you may take the opportunity to make for a

  [Read more...]
Benchmarking MySQL ACID performance with SysBench
+0 Vote Up -2Vote Down

A couple of question I get a lot from MySQL customers is “how will this hardware upgrade improve my transactions per second (TPS)” and “what level of TPS will MySQL perform on this hardware if I’m running ACID settings?” Running sysbench against MySQL with different values for per-thread and global memory buffer sizes, ACID settings, and other settings gives me concrete values to bring to the customer to show the impact that more RAM, faster CPUs, faster disks, or cnf changes have on the server. Here are some examples for a common question: “If I’m using full ACID settings vs non-ACID settings what performance am I going to get from this server?”

Let’s find out by running sysbench with the following settings (most are self explanatory – if not the man page can explain them):

  • sysbench –test=oltp
  [Read more...]
mycheckpoint (rev. 132): custom monitoring, custom charts, process list dump
+1 Vote Up -0Vote Down

Revision 132 of mycheckpoint has been released. New and updated in this revision:

  • Custom monitoring: monitoring & charting for user defined queries
  • HTML reports for custom monitoring
  • Process list dump upon alert notifications

Custom monitoring & charts

Custom monitoring allows the user to supply with a query, the results of which will be monitored.

That is, mycheckpoint monitors the status variables, replication status, OS metrics. But it cannot by itself monitor one’s application. Which is why a user may supply with such query as:

SELECT COUNT(*) FROM shopping_cart WHERE is_pending=1

Such a query will tell an online store how many

  [Read more...]
EXPLAIN: missing db info
+0 Vote Up -0Vote Down

I’m further developing a general log hook, which can stream queries from the general log.

A particular direction I’m taking is to filter queries by their type of actions. For example, the tool (oak-hook-general-log) can be instructed to only stream out those queries which involve creation of a temporary table; or those which cause for a filesort, or full index scan, etc.

This is done by evaluating of query execution plans on the fly. I suspect the MySQL query analyzer (http://www.mysql.com/why-mysql/white-papers/mysql_wp_queryanalyzer.php) roughly does the same (as a small part of what it does).

It’s almost nothing one cannot do with sed/awk. However, I bumped into a couple of problems:

  • The general log (and the
  •   [Read more...]
    oak-hook-general-log: streaming general log
    +0 Vote Up -0Vote Down

    I’m seeking input on a new openark kit utility I’ve started to implement.

    The tool, oak-hook-general-log, will hook up to a MySQL (>= 5.1) server, and stream the general log into standard output. It looks like this:

    bash$ python src/oak/oak-hook-general-log.py --socket=/tmp/mysql.sock --user=root
    2010-03-21 10:18:42     root[root] @ localhost []       79      1       Query   SELECT COUNT(*) FROM City
    2010-03-21 10:18:48     root[root] @ localhost []       79      1       Query   DELETE FROM City WHERE id=1000
    2010-03-21 10:18:54     root[root] @ localhost []       79      1       Query   SHOW PROCESSLIST
    2010-03-21 10:19:06     root[root] @ localhost []       79     
      [Read more...]
    mk-schema-change? Check out ideas from oak-online-alter-table
    +1 Vote Up -0Vote Down

    In response to Mark Callaghan’s post mk-schema-change.

    I apologize for not commenting on the post itself, I do not hold a Facebook account. Anyway this is a long write, so it may as well deserve a post of its own.

    Some of the work Mark is describing already exists under openark kit’s oak-online-alter-table. Allow me to explain what I have gained there, and how the issue can be further pursued. There is relevance to Mark’s suggestion.

    oak-online-alter-table uses a combination of locks, chunks and triggers to achieve an almost non-blocking ALTER TABLE effect. I had a very short opportunity to

      [Read more...]
    A simple webpage test script in Python
    +2 Vote Up -1Vote Down

    Looking around on Google for a webpage test script returns a lot of results. Some of them are useful, some are not. In particular, for Python, the scripts on the first page of results are minimal and lacking a useful copy and paste / ready to go script that will answer the question “is my webpage available?”. So I decided to write a quick one that will give you the return code and email you as an alert if the page does not return with a 200 code (successful). You can find the script here. Update: the webserver was trying to execute the script as a .py file so I just changed it to .txt – for it to work you will want to change the .txt extension to a .py extension after you download it.

    If you are familiar with Python scripting, this script could easily be modified to post to a form so that you can

      [Read more...]
    mycheckpoint rev. 76: OS monitoring, auto deploy, brief HTML and 24/7 reports
    +2 Vote Up -0Vote Down

    Revision 76 of mycheckpoint comes with quite a few improvements, including:

    • OS monitoring (CPU, load average, memory)
    • Auto-deploy
    • Improved charting
    • Brief HTML reports
    • 24/7 charts

    OS Monitoring

    When monitoring the local machine, mycheckpoint now monitors CPU utilization, load average, memory and swap space.

    This only applies to the Linux operating system; there is currently no plan to work this out for other operating systems.


    mysql> SELECT os_cpu_utilization_percent FROM sv_report_chart_sample;
      [Read more...]
    sar-sql New Alpha Release
    +2 Vote Up -0Vote Down
    I just uploaded a new tarball for sar-sql containing a few bug fixes, overall code improvements. I also added options to get a partial snapshot of SHOW SLAVE STATUS and SHOW MASTER STATUS. I chose only a few columns to avoid over complicating the project.

    I plan one more round of heavy code changes, but no new features until I can stabilize the code enough to release it as beta.

    Feel free to visit the project page in Launchpad to comment on the Blueprints, report new bugs and participate through the Answers section.

    Thank you very much to Patrick Galbraith who provided some ideas on the best way to solve some of the coding issues.

    Enjoy the download.
    On restoring a single table from mysqldump
    +3 Vote Up -0Vote Down

    Following Restore one table from an ALL database dump and Restore a Single Table From mysqldump, I would like to add my own thoughts and comments on the subject.

    I also wish to note performance issues with the two suggested solutions, and offer improvements.

    Problem relevance

    While the problem is interesting, I just want to note that it is relevant in very specific database dimensions. Too small – and it doesn’t matter how you solve it (e.g. just open vi/emacs and copy+paste). Too big – and it would not be worthwhile to restore from mysqldump anyway. I would suggest that the problem is interesting in the whereabouts of a few dozen GB worth of

      [Read more...]
    My MySQL Tool Chest
    +4 Vote Up -0Vote Down
    Every time I need to install or reconfigure a new workstation, I review the set of tools I use. It's an opportunity to refresh the list, reconsider the usefulness of old tools and review new ones. During my first week at Open Market I got one of these opportunities. Here is my short list of free (as in 'beer') OSS tools and why they have a place in my tool chest.

    Testing Environments

    Virtual Box

    Of all the Virtual Machines out there, I consider Virtual Box to be the easiest to use. Since I first looking into it while I was still working at Sun/MySQL, this package has been improved constantly. It's a must have to stage High Availability scenarios or run tools that are not available in your OS of choice.

    MySQL Sandbox

    Did you compile MySQL

      [Read more...]
    Kontrollkit – new version for download
    +0 Vote Up -0Vote Down

    Kontrollkit has been updated and has a new script for optimizing your database schemas. Some of the cnf files have been updated, some of the other files have had link corrections. Overall, a nice update. You can find the download here: http://kontrollsoft.com/software-downloads

    Monitoring Disk Space
    +1 Vote Up -0Vote Down
    Some time back, when a client wanted us to setup MySQL Enterprise Monitor, we were surprised to find out that disk monitoring was not available! We worked hard to come up with a solution. Eventually, we decided to setup a custom agent to monitor the disk. Below is the result of that.

    While this script may not work as-is for everyone, it should at least provide a basis for such a script. This script has been modified to send an email instead of plug directly into the MySQL Enterprise Monitor. But, it hopefully will get our creative juices flowing...

    # This script does a very simple test for checking disk space.
    # Valcora: http://www.valcora.com
    CHECKDISK=`df -h | awk '{print $5}' | grep % | grep -v Use | sort -n | tail -1 | cut -d "%" -f1 -`
    MAIL_SUBJECT="Daily Disk Check"
    if [

      [Read more...]
    Export All To CSV
    +0 Vote Up -0Vote Down
    Last night I was asked by a client to export all of the tables into CSV format. I thought no problem. Then I saw the long list of table names! They wanted every one of them in CSV format! At that point, I started thinking about making a backup and then importing them all as CSV storage engine and then I realized CSV was disabled on the sandbox server! So, I thought well, I could try MySQL Query Browser, but then I figured I would still have to do it on a table-by-table basis. That is when I decided to go the Bash script route!

    This lead to the formation of the brief script you see below. It simply connects to a given database, shows the list of tables, and then dumps out the contents in CSV format to individual files named tablename.csv in the current directory. After just a few minutes of scripting, it was all over and I had a handy little script for

      [Read more...]
    Converting Storage Engine
    +0 Vote Up -0Vote Down
    Have you ever been asked to convert all of the tables in a given database or on a server to a new storage engine? Well, we have! Sometimes, if it is only a few tables, firing off a few "ALTER TABLE ... ENGINE=InnoDB;" is fine. Other times, it is a nightmare if there are lots of tables and/or databases.

    To make our life easier in such times, we created the following Bash script. Save the file as something like "convert_tables.sh" and make sure to make it executable. You can do this in Linux by executing "chmod a+x ./convert_tables.sh." Take a look at the code and we will give you an example of how to run it!

    # Crawl through all of the tables in a database or databases and convert all tables to a given storage engine.
    # Valcora: http://www.valcora.com
    #### Begin Configuration ####
    DBNAMES="mydb1 mydb2"		# Separate list of

      [Read more...]
    Previous 30 Newer Entries Showing entries 31 to 60 of 74 Next 14 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.