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 61 to 74

Displaying posts with tag: Scripts (reset)

Finding All MyISAM Tables
+0 Vote Up -0Vote Down
Below is a small Bash script to crawl through all the tables in a MySQL database and look for any and all MyISAM tables. We developed this script when we realized that a team of developers were ignoring our requests to create all tables as InnoDB. We set the default storage engine to be InnoDB, however, the team would declare the Storage Enginge to be MyISAM which caused MySQL to ignore our default declaration.

Yes, we could run a "SHOW TABLE STATUS," but that would require staring at a lot of table entries in some databases. Naturally, we could also query the Information Schema database as well. All of those methods are fine. For our purposes, we wanted a fairly simple script that would do the scanning for us lazy types and yet work on pretty well all versions of MySQL.

To solve the problem, we developed this simple script to find any and all

  [Read more...]
DBA Scripts
+0 Vote Up -0Vote Down
Over the years the DBA's have created simple Bash and PHP scripts to make their life easier. We feel that sharing these with the MySQL community may help our fellow database administrators and make their life a little easier from time to time!

With that in mind, we will periodically post a new script that we hope will be of some value. Some of the scripts are meant to perform regular maintenance activity, routine tasks, and/or query the database schema for certain table types, and such.

Some of the scripts may work as-is, while others may need to be modified to suit your needs. Either way, it should provide a starting point for accomplishing your work.

Naturally, we can make no guarantees as to the effectiveness of these scripts. It should go without saying that you need to always test them on a non-critical server before you attempt to run them

  [Read more...]
Monolith DBA Toolkit 0.4.3 Released
+0 Vote Up -0Vote Down

The Monolith Toolkit of scripts for DBA routines. 0.4.3 has been released. You can download it here: http://code.google.com/p/monolith-toolkit/

Some information on the toolkit and what it contains:

  • mt-backup-parallel -> runs mysql backups in parallel super fast, has lots of reporting features
  • mt-check-replication -> script to report on replication status for slave servers
  • mt-rhcluster-check-filesystems -> reports on redhat cluster filesystems (for mysql active/passive clustering)
  • mt-rhcluster-script-wrapper -> wrapper script for running any of these scripts on a redhat cluster, chooses the active node to run the script on
  • mt-connections-log -> logs connections to mysql to disk, reports on threshold overages
  • mt-flush-tables-sequence -> runs through
  [Read more...]
Updated: Monolith Toolkit - MySQL DBA tools 0.4.2
+0 Vote Up -0Vote Down

Updated the release, sure it’s only been a matter of hours but I added the standard cnf files for 2,4,8 and 16GB server installations. Also added the rhcluster-wrapper script that can be used to run just about anything on the active database node when using mysql in an active/passive setup.

Download here:  http://code.google.com/p/monolith-toolkit/

Monolith Toolkit - MySQL DBA tools released!
+0 Vote Up -0Vote Down

Today I decided to package all of my various scripts together into a useful toolkit. Some are perl, some are shell script. These scripts are, in general, one off scripts that I wrote in order to get things done that weren’t available at the time. Now they’re nicely organized and will receive updates at the google code repo.

So far the toolkit includes the following scripts:

  • mt-backup-parallel -> the parallel backup script I wrote about in my last post
  • mt-check-replication -> script to report on replication status for slave servers
  • mt-check-rhcluster-filesystems -> reports on redhat cluster filesystems (for mysql active/passive clustering)
  • mt-connections-log -> logs connections to mysql to disk, reports on threshold overages
  • mt-flush-tables-sequence -> runs
  [Read more...]
Parallel mysqldump backup script available. Testers wanted.
+0 Vote Up -0Vote Down

Large databases, long mysqldump times, long waits for globally locked tables. These problems basically never go away when you rely on mysqldump with –all-databases or a list of databases, as it dumps schemas serially. I’m not going to explain serial vs parallel processing here since that’s a larger topic. Suffice to say that in these days of multi-core / multi-cpu servers we only make use of one processor’s core when we serially export databases using mysqldump. So, I have a new script that attempts to alleviate those issues and now I need testers to provide feedback/improvements.


In order to keep some sanity when dealing with hundreds of database servers, the script takes care of the following:

  • low global locking time requirements: solved by parallel tasks / forked processes
  • backup file checking: with
  •   [Read more...]
    MySQL, PHP, XML = mysql-dba.com
    +0 Vote Up -0Vote Down

    This is a basic heads up post, perhaps even blatant self marketing. So, please continue reading.

    If anyone recalls the website http://mysql-dba.com they would know that it’s based on the planet.py codebase that is written in python. I originally wrote a simple php script that utilized the lastRSS.php class for parsing feeds on the backend for archival purposes to be used at a later date. I say archival and later date because the site itself did not utilize any of the relational data storage to run the site. The site’s python code and cache was updated by cron scripts every 15 minutes and new data was scp’d from my dev server to my webhost’s servers.  This process eventually was quite randomly run since my development server rack in the garage at home gets really hot during the summer months

      [Read more...]
    Introducing wordpress-scripts 0.1 (0.2 out)
    +0 Vote Up -0Vote Down

    Update: I’ve been suffering some ungly and stupid bugs today, so I’ve fixed them and released version 0.2. It also includes a new script wp-update-home.

    I’ve just published some scripts that help me manage my personal wordpress installations, and publish some plugins I’m working on.

    Warning: these are early versions which I use for small tasks. If you find
    a bug or have suggestions, contact me at jbernal@warp.es

    Download version 0.1

      [Read more...]
    One backup script that does it all.
    +0 Vote Up -0Vote Down

    This integrates with Monolith, but the database update function can be stripped out for use without Monolith. The idea is that this script is a wrapper for mysqldump that does backup file consistency checking, email reporting, file based logging and directory pruning.

    I used to have one script for daily, weekly, and monthly all running out of /etc/cron.daily /etc/cron.weekly /etc/cron.monthly - respectively. But maintaining 3 scripts is foolish if one can do everything. So I added some variables to check day of week and day of month to achieve this.

    Enjoy the code. Script Link here.

    Project: RSS Feed Storage Using InnoDB #2
    +0 Vote Up -0Vote Down

    So far so good. I have a bit over two hundred RSS entries logged in the database for testing purposes. Today I changed the table structure for for the title and description to support longer entries. Here is the pertinent code.

    Add feed function:

    function add_feed($item,$site_id) {
    $each_title = $item['title'];
    $each_link = $item['link'];
    $each_desc = $item['description'];
    if(isset($item['guid'])) { $each_guid = $item['guid'];} else { $each_guid="";}
    if(isset($item['pubDate'])) { $each_pubDate = $item['pubDate'];} else { $each_pubDate="";}
    //print "\n$each_desc\n";
    INSERT INTO `extrabigassfries`.`feed_items` (
    `id` ,
    `rss_site_id` ,
    `item_title` ,
    `item_link` ,
    `item_description` ,
    `item_guid` ,
    `item_pubDate` ,
    VALUES (

      [Read more...]
    Project: RSS Feed Storage Using InnoDB
    +0 Vote Up -0Vote Down

    I’ve been coding a couple of scripts that run on 5 minute intervals to grab RSS/Atom feed data from http://mysql-dba.com and import that into a MySQL database. It idea is to create a search function for the site that will look at all past data from the aggregated feeds. Since there are multiple pollers running at different intervals I decided to use Innodb for the read/write nature of the poller/processing scripts.

    This is very simple so far - and as such I felt it should be documented from the start unlike many of my other projects. Here’s the feed table that is storing the information from the RSS feeds.

    mysql> show create table feed_items\G
    *************************** 1. row ***************************
    Table: feed_items
    Create Table: CREATE TABLE `feed_items` (
    `id` bigint(20) NOT NULL

      [Read more...]
    Rotating General Query & Slow Logs
    +0 Vote Up -0Vote Down

    Sometimes you need to have the general query log on and even though it causes more disk I/O than you may want, it’s good for troubleshooting. This log can and probably will fill up your disks rather quickly. Then there’s the slow query log - setting log_slow_queries and log_queries_not_using_indexes will write out the queries that take longer than long_query_time to execute, as well as any query not using an index.

    So, since MySQL does not apply the expire_logs_days value to these logs - only to the binary log (log_bin), we need another solution. There are probably a bunch of custom scripts out there that do this, but big surprise - we have one as well. This was originally written by Jim Wood until I got my hands on it and made some changes. The changes are listed in the head of the script. This little guy will rotate the

      [Read more...]
    Schema for DBA duties
    +0 Vote Up -0Vote Down

    I’m starting up a schema to hold stored procedures that automate common DBA tasks. SQL file for import is here: opsmonitor.sql

    More SPs will be added in time… whenever I get the time to write them that is.

    UPDATE: Jeff Stoner will be heading up this project to contain his scripts, of which the ones currently listed in the first link are written by himself.

    Bad internets!
    +0 Vote Up -0Vote Down

    So I have a constant battle with Charter Cable - high speed internet at home. Constantly causing all kinds of problems. So I decided to make a little script that runs from cron every minute that will track my outages in a single table database. Then I’ll be graphing the outages in real-time via jpGraph [as seen here]. Here’s the simple code.

    The database table create script for router_uptime

    CREATE TABLE `response` (
    `id` bigint(32) NOT NULL auto_increment,
    `state` tinyint(1) NOT NULL,
    `ip_address` varchar(15) NOT NULL,
    `Creation_time` datetime NOT NULL,
    PRIMARY KEY (`id`)

    The Script

    #-(0)> cat check_ping

      [Read more...]
    Previous 30 Newer Entries Showing entries 61 to 74

    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.