Justin.MadIrish.net

Although MySQL (http://www.mysql.com) has released a suite of GUI tools, developers remain less than entheusiastic about options for configuring and maintaining MySQL servers via graphical user interface. For years the popular choice has been PHPMyAdmin (www.phpmyadmin.net), which is a web based interface. While PHPMyAdmin is a powerful tool, I find it rather slow and sort of unintuitive. I've been searching for a good way to interact with my MySQL databases for some time. I use Oracle's SQL Developer (http://www.oracle.com/technology/products/database/sql_developer/index.h...) for a while and find it to be a great tool. SQL Developer is free, and because it's written in Java it can run anywhere.

With the latest version of SQL Developer you can now connect to MySQL databases by setting up connections in the same way that you set up Oracle connections. You do have to install the MySQL connectors though, and that part can be tricky.

This is a simple script that will dump all the data out of a MySQL database. The results are written to a file called output.tab. The script is written in Perl and requires the DBI package to be installed.

The file is run from the command line using. You must have perl installed on the machine running the script.

!/usr/bin/perl
use DBI;

$database = 'db_name';
$hostname = 'localhost';
$port = 3306;
$user = 'user_name';
$password = 'password';

$dsn = "DBI:mysql:database=$database;host=$hostname;port=$port";
my $dbh = DBI->connect($dsn, $user, $password)
    or die "Couldn't connect to databse: " . DBI->errstr;

my $sth = $dbh->prepare('show tables');
$sth->execute();

my $filename = 'output.tab';
open FILE, "> $filename"  or die "Can't open $filename : $!";
while (my ($id) = $sth->fetchrow_array()) {
    my $sql_query = $dbh->prepare("select * from $id");
    $sql_query->execute();
    print FILE "\n" . $id  . "\n";
    

      
justin's blog
Add new comment
Read more
    
  

I learned how to do something pretty nifty today and figured I’d share. I own a copy of TextPad - meaning I actually paid for it because it’s just so darned useful. I’d highly recommend it for anyone who has to do any work with text files on a windows machines. The fact that you can record macros alone makes it well worth the cost in my book.

However, you can do other cool stuff with TextPad. For instance, if you have a MySQL database running locally (for development for instance) and you’re writing up a data model you can actually hook TextPad into MySQL and execute the text file into the MySQL database. This means that you can document the data model as you actually create it. This is wonderful news for anyone that has had to spec up a data model, then create it, then reverse engineer a text document that describes the model.

All too often, especially on small setups, database security is ignored. When you’ve got a single machine hosting your entire LAMP application stack it’s easy to use the same username and password for all your applications. The worst example of this sort of habit is to simply use the root user and password for all your applications connecting to the MySQL server. This is often compounded by the fact that many applications aren’t designed with strict security or efficient design in mind so the database username and password show up on every single script that calls the database, or worse, every single time the database is called.