From: Aron Roberts (aron@socrates.berkeley.edu)
Date: Tue Sep 16 2003 - 12:05:15 PDT
In the message "[Webnet] Question: Exporting a MySQL Table", dated
2003-09-16, Preetam Mukherjee wrote:
>Would anyone happen to know of any sql queries that would export a
>table in MySQL to a flat text, or other similar format, file?
At 11:46 -0700 2003-09-16, Jukka-Pekka Vainio wrote:
>You can use OUTFILE flag in your select statement
>See
>http://www.mysql.com/doc/en/SELECT.html
Although this may be somewhat more involved, the command-line
'mysqldump' utility, which is part of a standard MySQL installation,
can also do this. Here's the page describing this command from the
MySQL documentation:
http://www.mysql.com/doc/en/mysqldump.html
Below is a simple Perl script which invokes mysqldump to export a
MySQL table to a text file. You'll need to assign values to each of
the relevant variables, below, such as the name of your MySQL
database and table, and your database username and password. (I've
pieced together the script below from a longer script and haven't
tested it after doing so, so it's possible it might need a bit of
tweaking ...)
The resultant text file that is exported is actually a SQL command
file, which can be used to re-create the database table from scratch,
should you ever need to do so. This is ideal for storing portable,
compressable backup copies of your tables. However, if you want just
your table's data -- sans structure -- exported into a delimited text
file or a similar format, so that you can use that data in another
program or script, it would be far simpler to use the INTO OUTFILE
option to the SELECT command, as Jukka-Pekka Vainio suggests.
Aron Roberts
Workstation Software Support Group
P.S. The script below is suited for being be run at periodic
intervals, such as once nightly, via 'crontab' or a similar
scheduling utility. With a few additional lines of code, it could
easily be extended to replicate a database table to another copy of
MySQL running on another host.
--
#!/usr/bin/perl
# export_mysql_table.pl
#
# Exports a MySQL database table as a SQL command file
# (a plain text file capable of re-creating this table)
#
# Aron Roberts <aron@socrates.berkeley.edu>
# 2002-05-23
# ---------------------------------------------------------------
# Declare variables
# ---------------------------------------------------------------
$TRUE = 1;
$FALSE = 0;
$debug = $FALSE;
# Name of the directory in which to export copies of your table
$db_export_dir = "FILL_ME_IN";
# Name of the MySQL database and table to export
$database_name = "FILL_ME_IN";
$table_name = "FILL_ME_IN";
# Filename for the exported table
# (This name will automatically have the current date and
# the ".sql" extension appended to it.)
$sql_filename = "FILL_ME_IN" . &iso8601Date() . ".sql";
# Database user name
$user_name = "FILL_ME_IN";
# Database user's password
# *** STORED IN PLAINTEXT HERE ***
# (Don't use for any confidential data!)
# (Make sure that the filesystem permissions for this script
# permit access only to authorized parties.)
$passwd = "FILL_ME_IN";
# Full path on your computer to the 'mysqldump' utility
$mysqldump = "FILL_ME_IN";
# ---------------------------------------------------------------
# Change to the database export directory
# ---------------------------------------------------------------
if (! chdir( $db_export_dir ) ) {
print "Could not change to directory $db_export_dir\n";
exit -1;
}
# ---------------------------------------------------------------
# Export the MySQL table as a text file
# (The text file is actually a SQL command file capable of
# re-creating the original table)
# ---------------------------------------------------------------
$mysqldump_command =
"$mysqldump --user=$user_name --password=$passwd --opt
$database_name $table_name > $sql_filename";
$mysqldump_output = `$mysqldump_command`;
if ($debug) {
print "'mysqldump' command = " . $mysqldump_command . "\n";
print "'mysqldump' command output = " . $mysqldump_output . "\n";
}
# ---------------------------------------------------------------
exit 0;
# ---------------------------------------------------------------
sub iso8601Date {
# Return the current date in ISO 8601 YYYY-MM-DD format
# From Jukka Korpela
# <http://www.cs.tut.fi/~jkorpela/iso8601.html>
($sec, $min, $hour, $mday, $mon, $year, $wday, $yday, $isdst) =
localtime(time);
$date = sprintf "%4d-%02d-%02d", (1900 + $year), $mon+1, $mday;
return ($date)
}
-----------------------------------------------------------------------
The following was automatically added to this message by the list server:
Webnet information is available at <URL:http://webnet.berkeley.edu/>.
This archive was generated by hypermail 2.1.5 : Tue Sep 16 2003 - 12:07:08 PDT