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 90 Next 30 Older Entries

Displaying posts with tag: Scripting (reset)

Scripting Backups of MySQL with Perl via mysqldump
+0 Vote Up -0Vote Down

MySQL provides you with a nice utility for creating a backup of your databases. From the mysqldump documentation page: “The mysqldump client is a backup program originally written by Igor Romanenko. It can be used to dump a database or a collection of databases for backup or transfer to another SQL server (not necessarily a MySQL server). The dump typically contains SQL statements to create the table, populate it, or both. However, mysqldump can also be used to generate files in CSV, other delimited text, or XML format.”

The mysqldump utility doesn’t provide you with a “hot” or live backup like MySQL Enterprise Backup (http://www.mysql.com/products/enterprise/backup.html) does, and there may be issues with mysqldump locking tables during the

  [Read more...]
Retrieving Data from MySQL via PHP
+0 Vote Up -0Vote Down

So far, we have looked at connecting to a MySQL database via Perl and Python. Next, we will look at connecting via PHP. I am assuming that you have already installed PHP or have the ability to run PHP scripts on your server, and that you have MySQL installed as well.

This example will use the same address table from the previous post, Inserting Data into MySQL with Perl.

Here is the SQL statement that we used to create the table:

SET NAMES latin1;
SET FOREIGN_KEY_CHECKS = 0;

CREATE TABLE `address` (
`name_first` varchar(30) NOT NULL,
`name_last` varchar(30) NOT NULL,
`address_01` varchar(40) NOT NULL,
`address_02` varchar(40) NOT NULL,
`address_city` varchar(30) NOT NULL,









  [Read more...]
Inserting Data into MySQL with Perl
+0 Vote Up -0Vote Down

In the two previous posts, we looked at simply connecting to a MySQL database via Python and Perl. In this post, we will:

- use an insert statement to input data into a MySQL table via Perl
- use a select statement to view the same data to confirm our results

For this example, we will use a table named “address”. Here is the SQL statement that we used to create the table:



SET NAMES latin1;
SET FOREIGN_KEY_CHECKS = 0;

CREATE TABLE `address` (
  `name_first` varchar(30) NOT NULL,
  `name_last` varchar(30) NOT NULL,
  `address_01` varchar(40) NOT NULL,
  `address_02` varchar(40) NOT NULL,
  `address_city` varchar(30) NOT NULL,
  `address_state` varchar(20) NOT NULL,
  `address_postal_code` varchar(12) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

SET FOREIGN_KEY_CHECKS = 1;


We are only going to


  [Read more...]
Connecting to MySQL with Perl
+0 Vote Up -0Vote Down

When I was designing web sites, for a long time I wrote my HTML code the “hard” way – by opening a text editor and manually typing in the code (this was before I purchased Adobe DreamWeaver).

During that time of manual HTML writing, I had a project that required forms on a web page, and I needed a place to store the information. After talking with a few tech friends, I decided to use MySQL as my web site database, and Perl as my scripting language.

I had written complex Bourne shell scripts before, but Perl was something entirely new. With a little help from a buddy of mine, after a few hours I was off and running. I was amazed at how easy it was to connect to a MySQL database with Perl.

This example will show you how to

  [Read more...]
pipe viewer – monitoring / limit the throughput of a pipe
+0 Vote Up -0Vote Down

Pipe viewer is a command line tool which is used to monitor the throughput, display the estimated time of completion or to limit the transfer rate of a pipe (pipeline).

Install pipe viewer on Debian / Ubuntu with the following command.

apt-get install pv

On CentOS / Fedora / RedHat use the yum command to install pipe viewer

yum install pv

To use pipe viewer just insert the pv command between two processes to monitor the throughput of the pipe.

cat logfile.log.1 | pv | gzip -9 > logfile.log.1.gz
9,18MB 0:00:01 [ 9,1MB/s] [   <=>

Or limit the transfer rate of the pipe to a designated number of bytes.

cat logfile.log.1 | pv --rate-limit 100 | gzip -9 > logfile.log.1.gz
 300B 0:00:03 [ 101B/s ] [
  [Read more...]
Win a free book at the February Python Book Contest
+2 Vote Up -0Vote Down

This month is a special month. It’s not because of Valentines day or even the exciting day where we see groundhogs. No, this month is special because I’m have a book contest where you, the reader, get to win something free for doing absolutely nothing more than posting a comment saying that you want one of the several books I have available in the contest.

So without getting into boring details I’ll keep this short. I’ve been reviewing a lot of books lately and I think it’s time to get some books into people’s hands to enjoy themselves. This month the giveaways are all Python oriented.

So, all you have to do is take a look at the following titles and post a comment here saying that you want one of them. At the end of the month two readers will be chosen via a random list sorting python script I’ve whipped up for just this purpose. You

  [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!"
        sys.exit(1)
    try:
        job = queue.get()
        print "I'm
  [Read more...]
check the status / exit code of pipelined commands with PIPESTATUS
+0 Vote Up -0Vote Down

While I was optimizing an old mysql table copy script, I found the PIPESTATUS Shell Variables in the bash manual.
PIPESTATUS is an array with the status / exit codes of the last pipelined commands. ( A pipeline is a sequence of one or more commands separated by the character | (pipe).)

The following example script uses PIPESTATUS to check the exit code of mysqldump and mysql. If one of these commands fails, the script will exit and print an error message.

#!/bin/sh

dbname="testdb"
tables="table1 table2 table3"

for table in $tables
do
       mysqldump $dbname $table | mysql -h $dest_servername $dbname
       for a in  "${PIPESTATUS[@]}"
       do
                  if [ $a -gt 0 ]; then
                                echo -e "n\tError: mysqldump or mysql

  [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.

#!/usr/bin/python
################################################################################
## DATE: 2010-10-26
## AUTHOR: Matt Reid
## MAIL: mreid@kontrollsoft.com
## SITE:
  [Read more...]
dbbenchmark.com – Debian Lenny, MEMORY_ACTIVE bug fix
+1 Vote Up -3Vote Down

Quick solution to an issue that the affected Debian Lenny release: the process used to collect the MEMORY_ACTIVE_BYTES variable has been modified to correct a situation where some systems report an array of memory information instead of the expected single integer value. The bug has been fixed in revision 21 and the current download (revision 22) is available for download or svn update. As usual, you can download the MySQL dbbenchmark script here: [downloads].

Thanks goes to Brian Vowell at Evernote.com for bringing this bug to my attention. The original bug report can be found here: [link]

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.

#!/usr/bin/python
## 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):
        threading.Thread.__init__(self)
        self.tx =
        self.method = method
    def run(self):
  [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()
    argv.append(LVM_BIN)
    argv.append("lvs")
    argv.append("--nosuffix")
    argv.append("--noheadings")
    argv.append("--units")
    argv.append("b")
    argv.append("--separator")
    argv.append(";")
    argv.append("-o")
    argv.append("lv_name,vg_name,lv_size")

    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]

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...]
N900 – control all of your accounts with this script
+1 Vote Up -1Vote Down

If you own a Nokia N900 cellular device you might be interested in the ability to control all of your IM accounts from the command line. For those that do not know, the N900 runs Maemo Linux and is capable of running MySQL embedded if you so choose. Here’s a quick script I wrote to provide that functionality for IM accounts. It’s at the bottom of the page, called “im-connections”.

wiki: http://wiki.maemo.org/N900_Mission_Control#Set_all_SIP_accounts_to_online_or_offline
pastebin: http://pastebin.com/qAC57E1N

Kontrollkit – new version available for download
+0 Vote Up -0Vote Down
Just a quick notice to let everyone know that there is a new version of Kontrollkit available. There are some required bug fixes to the formerly new python backup script and some Solaris compatible changes to the various my.cnf files. You can download the new version here: http://kontrollsoft.com/software-downloads, or here: http://code.google.com/p/kontrollkit/
Reviewed: Python Testing by Daniel Arbuckle
+0 Vote Up -0Vote Down

I’ve recently had the pleasure of reading “Python Testing: An easy and convenient approach to testing your python projects” from Packt Publishing. It’s been a quick read but a solid set of instructions on the different methods for the subject.

The book starts out very quickly with details about the various methods that are available, the means of automation for testing, and of course the environment you’d want to be in for working on the subjects that the book covers. It then, in the second chapter, moves into the guts of testing by describing the basics of doctest via syntax and some simple examples, and then moves on to a real world example via the AVL tree. It’s all very basic

  [Read more...]
Kontrollbase – revision 297 fixes Reporter-CLI “alert_22″ sub-routine
+0 Vote Up -0Vote Down
Quick note to let our users know that there was an XML tag closure error on the “alert_22″ subroutine in the “bin/kontroll-reporter-cli.pl” script. This does not affect the webapp portion of Kontrollbase – only reports generated via the command line reporter script. It is not a fatal error but will cause the XML file to […]
Kontrollkit – new backup script is partition space aware
+0 Vote Up -0Vote Down
I’ve been wanting to write a backup script for a while now that does the following: reads the partition information for the directory that you are backing up into and computes the used/available/percentage-available space. Then it reads the total data size from the MySQL tables and ensures that you have enough space on disk (for [...]
Kontrollkit – new version is available for download!
+0 Vote Up -0Vote Down
Just a quick notice to let everyone know that there is a new version of Kontrollkit available. There are two new scripts included as well as some good updates to the my.cnf files. You can download the new version here: http://kontrollsoft.com/software-downloads kt-mysql-systemcheck – generates a report for point-in-time system status that is useful for troubleshooting MySQL [...]
Is emacs not coloring your Python comments?
+0 Vote Up -1Vote Down

This is a simple matter with a simple solution that might help someone save time and confusion. Emacs wasn’t coloring my comments correctly so I went ahead and had it change them to red-italic. If you are having similar issues you can drop the following into your home directory’s .emacs file. Enjoy. Keep in mind that if you are using emacs in a terminal session as opposed to the X-server gui then you will not see the italics.


(global-font-lock-mode 1)
(custom-set-variables
'(gud-gdb-command-name "gdb --annotate=1")
'(large-file-warning-threshold nil))
(custom-set-faces
'(font-lock-comment-face ((((class color) (background light)) (:foreground "red" :slant italic)))))

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...]
Kontrollbase – scripts being rewritten in Python, request improvements now!
+1 Vote Up -0Vote Down
The time has come for major performance improvements to the reporter, stats-gather, alerter, and client scripts. This means that I will be rewriting the scripts in Python. A couple of reasons for this; to cut down on the number of modules that are required for the installation process (which also makes distributing the client script [...]
MySQL University: Gearman for MySQL
Employee +0 Vote Up -0Vote Down

This Thursday (November 12th, 14:00 UTC), Giuseppe Maxia of the MySQL Community Team will present Gearman for MySQL. Gearman is a client/server infrastructure for generic tasks, usable on distributed servers, with little worry about the details. No matter what language you speak, Gearman can meet your needs in C, PHP, Perl, Ruby, shell scripting, and several more. Gearman can also work in conjunction with MySQL, either using UDFs, or simply through its basic architecture. Giuseppe's talk will show examples of how to use Gearman for remote installation and how to call a complicate data warehousing function written in Perl from any other language, with no knowledge of Perl at

  [Read more...]
MySQL University: Gearman for MySQL
Employee +0 Vote Up -0Vote Down

This Thursday (November 12th, 14:00 UTC), Giuseppe Maxia of the MySQL Community Team will present Gearman for MySQL. Gearman is a client/server infrastructure for generic tasks, usable on distributed servers, with little worry about the details. No matter what language you speak, Gearman can meet your needs in C, PHP, Perl, Ruby, shell scripting, and several more. Gearman can also work in conjunction with MySQL, either using UDFs, or simply through its basic architecture. Giuseppe's talk will show examples of how to use Gearman for remote installation and how to call a complicate data warehousing function written in Perl from any other language, with no knowledge of Perl at

  [Read more...]
MySQL University: Gearman for MySQL
Employee +0 Vote Up -0Vote Down

This Thursday (November 12th, 14:00 UTC), Giuseppe Maxia of the MySQL Community Team will present Gearman for MySQL. Gearman is a client/server infrastructure for generic tasks, usable on distributed servers, with little worry about the details. No matter what language you speak, Gearman can meet your needs in C, PHP, Perl, Ruby, shell scripting, and several more. Gearman can also work in conjunction with MySQL, either using UDFs, or simply through its basic architecture. Giuseppe's talk will show examples of how to use Gearman for remote installation and how to call a complicate data warehousing function written in Perl from any other language, with no knowledge of Perl

  [Read more...]
I’m Offering Pro-Bono Consulting
+1 Vote Up -0Vote Down

I started my company about a year ago, but I’ve been doing consulting for a long time. In fact, my first job in the IT industry was working for a consulting firm. Before that, starting as far back as grade school, I was involved in a lot of volunteer civic and community service activities. I admire companies who get involved in their communities, or even outside of their communities, wherever help is needed.

As part of my business plan, I’ve put in place a policy of accepting one pro-bono consulting project per year. So far, I haven’t gotten any requests for free consulting work, so here’s my public shout out to let you know what types of services are available:

1. Speaking or Training. My specialties are things like advanced Linux administration and SQL, but I’m perfectly capable of delivering

  [Read more...]
Dojo examples from UC2009
+0 Vote Up -0Vote Down

I know, I know, loads of people have been waiting for these…

So here we go, I’ve finally sorted a downloaded version of the Dojo examples from the presentation I provided at the MySQL Users Conference 2009.

There are three examples:

  • The auto-paging table example, which uses the functionality of the Dojo Toolkit and the QueryReadStore to automatically load content from a table.
  • The basic graphing example, which loads data dynamically and plots a graph.
  • And the zooming version of the same basic graph interface
  • There’s a README in the download that contains instructions on getting everything up to speed, although it should be



  [Read more...]
Gentlemen, Slap your Engines!
Employee +0 Vote Up -0Vote Down

Once again, I was unable to attend all of the sessions I wanted to at this year's User Converence, but I was happy to make it to Bob Burgess' talk on bash scripting with mysql. The slides and examples aren't up yet, but when they are (which may be as you read this, check the last link), they would probably also be a great tutorial.


So, I got bore^D^D^D^D inspired later that day to put some of the practices into use, and worked up a script to run mysqlslap in various ways against a server, and then added a couple funcitons to try it out on each storage engine. The script is below in its entirety - bash scripters, please be kind in your comments. No, I didn't write all this just for the pun in the


  [Read more...]
Gentlemen, Slap your Engines!
Employee +0 Vote Up -0Vote Down

Once again, I was unable to attend all of the sessions I wanted to at this year's User Converence, but I was happy to make it to Bob Burgess' talk on bash scripting with mysql. The slides and examples aren't up yet, but when they are (which may be as you read this, check the last link), they would probably also be a great tutorial.


So, I got bore\^D\^D\^D\^D inspired later that day to put some of the practices into use, and worked up a script to run mysqlslap in various ways against a server, and then added a couple funcitons to try it out on each storage engine. The script is below in its entirety - bash scripters, please be kind in your comments. No, I didn't write all this just for the pun


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