Dump all data from a MySQL database into a tab delimited file

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";

Posting to MySQL with TextPad

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.

Secure PHP Connections to MySQL with SSL

Separating the elements of your LAMP stack makes a lot of security sense. If you host your web server on a different machine than your database server you get a couple of wins. A compromise in your database won't allow an attacker to alter web pages or write files to the web server, and compromise of your web server wouldn't allow attackers to read the text based MySQL data files. By segregating the functionality you effectively sandbox each tier of your web application to prevent the escalation of compromises. The potential downside of separating these elements, however, is that you introduce a new attack vector, namely snooping traffic on the wire as it travels between the database and the web server.

Defending PHP Web Applications from MySQL

MySQL provides a number of features that can be used to greatly increase the security of your PHP application. Because MySQL is often used to store critical data for web applications, it is a common target for attackers. Gaining access to the database often allows access to sensitive data. In most development models PHP is used to provide views into the database layer, and in a sense to shield the database from end users. However, flaws in PHP applications can expose the database to malicious users. By utilizing some of the security features in MySQL you can protect your database from flaws introduced at the PHP layer.

MySQL Database User Security

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.

Connecting To a Remote MySQL Server Securely Using SSH Port Forwarding

Brief instructions on how to set up local port forwarding to allow for a secure MySQL connection by tunneling through an existing SSH session. This allows for encryption as well as the ability to bypass firewalls that allow remote SSH connections but block remote MySQL connections.

by: Justin
Original: September 23, 2004
Updated: May 1, 2008

Connecting Securely

Setting Up a WAMP System

A short guide to setting up an Apache, MySQL and PHP system on your Windows machine including common errors.

Using PHP to Access MySQL Results

This article aims to show you how to retrieve and use MySQL result sets in PHP. Included are a few functions for making data retrieval, and your code, more manageable.

Backing Up and Restoring MySQL Databases

If you've used MySQL for any length of time you've run into situations where you need to back up and restore databases. If you haven't thought about backups, now is the time to do it! You should regularly back up your important data to removable media (CD-ROM, Zip drive, floppy, etc.) so if your computer crashes you can get your data back. MySQL backup is no different. You can actually back up your data quite easily using MySQL. If you want to be really raw about it you can simply copy off all the files in /var/lib/mysql. If you check that directory it will show you all the files your MySQL database is using. This method is clumsy at best, however, and restoring data is a real problem if you have to rebuild a database. Your best option is actually mysqldump. This quick program will allow you to backup and restore your databases with ease and confidence.