Archive for the ‘perl’ Category

Splitting a MySQL Dump File Into Smaller Files Via Perl

Сентябрь 14th, 2011

I was trolling the MySQL forums web site and noticed a post regarding someone who was trying to load a 50-gigabyte MySQL dump file. The author of the post had stated that the loading of the file had been running for days, and was wondering if there was a better way to import the file. They did not have access to anything else (i.e. – original database) but this file.

I have had to restore several databases in the past from a single large MySQL dump file – which led me to start backing up each database individually. These databases are for my own personal use and are not updated that often, so I don’t need to have point-in-time recovery – and so a MySQL dump works just fine. If I had a production system, I would invest in the MySQL Enterprise Backup and the MySQL Enterprise Monitor applications.

But, I have had problems when I tried to restore a relatively small MySQL instance of 21 databases with a total file size of less than 900 megabytes. So, I wrote a Perl script to simply split the large MySQL dump file into smaller files, which I could then load one at a time to isolate the problem. I am sure that there are many other ways of troubleshooting this person’s problem, but I thought I would dig out this script and post it here. This script is also handy for splitting any text file that you have into smaller files. All you would have to do is to change the keywords that causes a new file to be created.

The script opens the MySQL dump file, and starts outputting each line to a new file, looking for the keywords “CREATE DATABASE”. When the script sees those keywords, it closes the last file and opens a new one, and then prints that line with the keywords out to the new file until the keyword appears again. The script names each new file with a sequential number – as in database_01.sql, database_02.sql, etc. The first file that this script creates (named database_00.sql) contains the header information from the MySQL dump file, so you may ignore this file.

I tested this script on an 850-megabyte MySQL dump file, and it worked perfectly (and executed in less than one minute). To make sure that your new files contain the same amount of data as the original file, you can use the Unix command “wc” to show you the size of each file, and then you can compare the sum of these sizes to the size of your MySQL dump file. I first ran the script, and it produced the following 22 files:

sh-3.2# /usr/bin/perl split_file.pl
2011-09-13 21:24:23 - Creating file database_00.sql
2011-09-13 21:24:23 - Creating file database_01.sql
2011-09-13 21:24:23 - Creating file database_02.sql
2011-09-13 21:24:23 - Creating file database_03.sql
2011-09-13 21:24:23 - Creating file database_04.sql
2011-09-13 21:24:23 - Creating file database_05.sql
2011-09-13 21:24:23 - Creating file database_06.sql
2011-09-13 21:24:23 - Creating file database_07.sql
2011-09-13 21:24:23 - Creating file database_08.sql
2011-09-13 21:24:23 - Creating file database_09.sql
2011-09-13 21:24:23 - Creating file database_10.sql
2011-09-13 21:24:23 - Creating file database_11.sql
2011-09-13 21:24:23 - Creating file database_12.sql
2011-09-13 21:24:23 - Creating file database_13.sql
2011-09-13 21:24:23 - Creating file database_14.sql
2011-09-13 21:24:24 - Creating file database_15.sql
2011-09-13 21:24:24 - Creating file database_16.sql
2011-09-13 21:24:24 - Creating file database_17.sql
2011-09-13 21:24:24 - Creating file database_18.sql
2011-09-13 21:24:24 - Creating file database_19.sql
2011-09-13 21:24:24 - Creating file database_20.sql
2011-09-13 21:25:14 - Creating file database_21.sql

Here are the sizes of the individual database*.sql files, as well as the original source file named all_data.sql:

sh-3.2# wc database*sql
      21      71      844 database_00.sql
      33     109      868 database_01.sql
     395   18313  1134377 database_02.sql
     578  101269  5280603 database_03.sql
     553   14095   366305 database_04.sql
     553   10050   263748 database_05.sql
       8      21      139 database_06.sql
      70    2783    37976 database_07.sql
     547   11055   410751 database_08.sql
     320   10888   426911 database_09.sql
      74     337     6727 database_10.sql
     282   12624   325737 database_11.sql
     255    1863    42550 database_12.sql
      44     152     1337 database_13.sql
      57   82921 13048515 database_14.sql
     737   73955   908495 database_15.sql
      44     258     2972 database_16.sql
     191  535786  8777550 database_17.sql
     129    2325    36632 database_18.sql
       8      21      139 database_19.sql
  1225 28094331 826795680 database_20.sql
      312  134027 5401957 database_21.sql
  6436 29107254 863270813 total

sh-3.2# ls -l all_data.sql
-rw-rw-rw- 1 tonydarnell admin 863270813 Sep 13 20:23 all_data.sql

As you can see, the total size of the database*.sql files is 863270813, and the size of our original MySQL dump file (all_data.sql) is the same size – 863270813.

After splitting the main MySQL dump file, you can now restore/import each file individually, and if you have any problems, they will be easier to troubleshoot.

Here is the script:

#!/usr/bin/perl -w

# Need to explicitly load the functions in POSIX
use POSIX 'strftime';

# file name of the file you want to split
$filename = "all_data.sql";

# Note that if you pass no argument to localtime, it assumes the current time
$time = strftime '%Y-%m-%d %H:%M:%S', localtime;

$count = 0;
$filecount = 0;

# syntax for new file names
# if you have more than 100 files, you could modify this statement to place a leading
# zero if the file is greater than 10 but less than 100
if ($filecount < 10) {

     $new_filename = "database_0" . $filecount . ".sql";

}

else

{

     $new_filename = "database_" . $filecount . ".sql";

}

print "$time - Creating file $new_filename\n";
open(OUTPUT, ">$new_filename") || die "Can't redirect stdout";

open(LINE ,"$filename") || die ("\nERROR - could not open file: $filename\n");
while (<LINE>)

{
     
          if ($_ =~ "^CREATE DATABASE" && $count > 1)
          
          {

               # close the old file
               close(OUTPUT);

               $filecount++;
          
               # syntax for new file names
               if ($filecount < 10) {
               
                    $new_filename = "database_0" . $filecount . ".sql";
               
               }
               
               else
               
               {
               
                    $new_filename = "database_" . $filecount . ".sql";
               
               }
               
               $time = strftime '%Y-%m-%d %H:%M:%S', localtime;
               print "$time - Creating file $new_filename\n";

               open(OUTPUT, ">$new_filename") || die "Can't redirect stdout";
          
               print OUTPUT $_;

          }
          
          else
          
          {
          
               print OUTPUT $_;
          
          }

$count++;

# end while
}

close (LINE);
close (OUTPUT);

 

—————————————–

Tony Darnell is a Principal Sales Consultant for MySQL, a division of Oracle, Inc. MySQL is the world’s most popular open-source database program.

Tony may be reached at info [at] ScriptingMySQL.com and on LinkedIn.


PlanetMySQL Voting: Vote UP / Vote DOWN
 

Posted in mysql, perl, Scripting

Комментарии отключены

 

MySQL – Use mysqldump and FTP the Backup Files Offsite via Perl

Сентябрь 8th, 2011

In an earlier post (Scripting Backups of MySQL with Perl via mysqldump), I showed you how to use Perl to script a mysqldump backup of selected databases. Reminder – using mysqldump to backup your databases is not as safe or effective as using MySQL Enterprise Backup.

A blog reader sent me an email asking me how they could automate sending the backup files via FTP to another server (preferably one that is off-site). This was easily accomplished by adding a series of FTP commands to the original script.

As in the first post, we use a text file that contains a list of the databases that we want to backup. See this post for more information on using the script to backup your files – I don’t want to duplicate the entire post here.

For the FTP part of this script, you will need to edit the script to make sure that you change the following to match your systems:

# this is your MySQL user name and password
$mysql_user_name = "root";
$mysql_user_password = "my_password";

# this is your FTP site name, user name and password
$ftp_site = "my_hostname.com";
$ftp_user_name = "my_user_name";
$ftp_password = "my_password";
# create this folder on your FTP backup site under your root/login directory
$ftp_backup_directory = "backups";

# set the local directory where you will keep the backup files
$backup_folder = '/Users/tonydarnell/cgi/mysql/mysqlbak';

The script will create a folder on the local server under the $backup_folder directory, with the naming convention of YYYY-MM-DD-HHMM, and place the backup files for each database in this directory. It will then create the same folder on your FTP server under the $ftp_backup_directory, backup each database, and then send all of those files to your FTP server to be placed in the same directory. The script does a backup for each database individually, compresses the file, and then sends each file via FTP before it backs up the next database.

For example, on your local server, it will create a directory such as “2011-09-07-1756″ under the $backup_folder directory (in this example – /Users/tonydarnell/cgi/mysql/mysqlbak). The script will also create the same directory “2011-09-07-1756″ on the FTP server under the $ftp_backup_directory named “backups”. This $ftp_backup_directory should be created under the root directory (or the default login directory) for your FTP user prior to running the script.

Of course, it will be up to you to remove the older backups from the local server and FTP server. I also have quite a few print statements showing you each step of the process. Feel free to comment out these lines if you don’t want the script to print anything.

Here is a sample output from the script, backing up one database named cbgc:

# /usr/bin/perl mysql_backup_ftp.pl
Backing up cbgc...
FTP - 2011-09-07-2008_cbgc.sql.Z... Goodbye.
FTP Server ready.
User u36379513-777 logged in
CWD command successful
Type set to I
Opening BINARY mode data connection for 2011-09-07-2008_cbgc.sql.Z
Transfer complete
Finished backing up cbgc...

Here is the script:


#!/usr/bin/perl

use Net::FTP;
use warnings;
use File::Basename;

# this is your MySQL user name and password
$mysql_user_name = "root";
$mysql_user_password = "my_password";

# this is your FTP site name, user name and password
$ftp_site = "myhostname.com";
$ftp_user_name = "my_user_name";
$ftp_password = "my_password";
# create this folder on your FTP backup site
$ftp_backup_directory = "backups";

# set the directory where you will keep the backup files
$backup_folder = '/Users/tonydarnell/cgi/mysql/mysqlbak';

# we use a config file to keep a list of all of the databases that we want to backup
# You may use a comment to bypass any database that you don't want to backup
# example config file
# # unwanted_database (commented - will not be backed up)
# database_name1
# database_name2

# the config file is a text file with a list of the databases to backup
# this should be in the same location as this script, but you can modify this
# if you want to put the file somewhere else
my $config_file = dirname($0) . "/mysql_bak.config";

# retrieve a list of the databases from the config file
my @databases = removeComments(getFileContents($config_file));

# change to the directory of the backup files.
chdir($backup_folder) or die("Cannot go to folder '$backup_folder'");

# grab the local time variables
my ($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst) = localtime(time);
$year += 1900;
$mon++;
#Zero padding
$mday = '0'.$mday if ($mday<10);
$mon = '0'.$mon if ($mon<10);
$hour = "0$hour" if $hour < 10;
$min = "0$min" if $min < 10;

# create the name of the backup folder that will contain all of the backup files
my $folder = "$year-$mon-$mday-$hour$min";
mkdir($folder) or die("Cannot create a folder called '$folder'");

    # login to the FTP server and create the folder to store the backup files
    $ftp = Net::FTP->new("$ftp_site", Debug => 0)
    or die "Cannot connect to $ftp_site: $@\. ", $ftp->message;

    # optional - comment or remove this line to supress print statement
    print $ftp->message;
    $ftp->login("$ftp_user_name","$ftp_password")
    or die "Cannot login with $user_name. ", $ftp->message;

    # optional - comment or remove this line to supress print statement
    print $ftp->message;
    $ftp->cwd("/$ftp_backup_directory")
    or die "Cannot change working directory to $ftp_backup_directory. ", $ftp->message;

    # optional - comment or remove this line to supress print statement
    print $ftp->message;
    $ftp->mkdir("$folder")
    or die "Cannot create directory $folder. ", $ftp->message;

    # optional - comment or remove this line to supress print statement
    print $ftp->message;
    $ftp->quit;
    # end FTP

# backup each database contained in the @databases array
foreach my $database (@databases) {
    next if ($database eq '');
    chomp($database);

    my $table = '';
    # Get just 1 table in the database - if there is a ' '(space) in the db name
    if(index($database,' ')+1) {
     my @parts = split(' ',$database);
     $database = $parts[0];
     $table = $parts[1];
    }

    print "Backing up $database...\n";

    # we add the year, month, day and time of day to the backup filename
    my $file = "$year-$mon-$mday-$hour$min\_$database";
    $file .= '_' . $table if($table ne '');
    $file .= ".sql";

    # perform a mysql dump on each database
    # change the path of mysqldump to match your system's location
    # and add/change any of the backup options - this is just an example
    # make sure that you change the root password above to match the correct password
    `/usr/local/mysql/bin/mysqldump -R --triggers -u $mysql_user_name --password=$mysql_user_password $database $table | compress > $folder/$file.Z`;

    # start FTP
    print "FTP - $file.Z... ";

    # optional - comment or remove this line to supress print statement
    print $ftp->message;
    $ftp = Net::FTP->new("$ftp_site", Debug => 0)
    or die "Cannot connect to $ftp_site: $@\. ", $ftp->message;

    # optional - comment or remove this line to supress print statement
    print $ftp->message;
    $ftp->login("$ftp_user_name","$ftp_password")
    or die "Cannot login with $user_name. ", $ftp->message;

    # optional - comment or remove this line to supress print statement
    print $ftp->message;
    $ftp->cwd("/$ftp_backup_directory/$folder")
    or die "Cannot change working directory to $ftp_backup_directory. ", $ftp->message;

    # optional - comment or remove this line to supress print statement
    print $ftp->message;
    $ftp->binary()
    or die "Change to binary mode failed. ", $ftp->message;

    # optional - comment or remove this line to supress print statement
    print $ftp->message;
    $ftp->put("$folder/$file.Z")
    or die "Put failed - $folder. ", $ftp->message;

    # optional - comment or remove this line to supress print statement
    print $ftp->message;
    $ftp->quit;
    # FTP end

    print "Finished backing up $database...\n\n";
}

exit;

# this subroutine simply creates an array of the list of the databases
sub getFileContents {
    my $file = shift;
    open (FILE,$file) || die("Can't open '$file': $!");
    my @lines=<FILE>;
    close(FILE);

    return @lines;
}

# remove any commented tables from the @lines array
sub removeComments {
    my @lines = @_;

    @cleaned = grep(!/^\s*#/, @lines); #Remove Comments
    @cleaned = grep(!/^\s*$/, @cleaned); #Remove Empty lines

    return @cleaned;
}

 

—————————————–

Tony Darnell is a Principal Sales Consultant for MySQL, a division of Oracle, Inc. MySQL is the world’s most popular open-source database program.

Tony may be reached at info [at] ScriptingMySQL.com and on LinkedIn.


PlanetMySQL Voting: Vote UP / Vote DOWN
 

Posted in mysql, perl, Scripting

Комментарии отключены

 

Perl TCP Listener for Detecting Available Ports for MySQL Enterprise Monitor

Сентябрь 7th, 2011

I recently visited a client for the purpose of installing and demonstrating MySQL Enterprise Monitor.

If you are unfamiliar with the MySQL Enterprise Monitor – from the MySQL web site: The MySQL Enterprise Monitor continuously monitors your MySQL servers and alerts you to potential problems before they impact your system. Its like having a “Virtual DBA Assistant” at your side to recommend best practices to eliminate security vulnerabilities, improve replication, optimize performance and more. As a result, the productivity of your developers, DBAs and System Administrators is improved significantly.

The MySQL Enterprise Monitor is a distributed web application that is deployed within the safety of your firewall. It is comprised of a centralized Service Manager and lightweight Service Agent this is installed on each monitored MySQL server.

For this demo, we had to install the Enterprise Monitor Application on one server, and then the Enterprise Monitor Agent on another server that was hosting the MySQL database that we wanted to monitor. In this case, the servers that we were using for the install were in separate data centers, and both had firewalls restricting port access. We wanted to install the Monitor in one data center, and the Agent in another. Since both applications use various TCP/IP ports, during the install we were having problems finding openings in the firewalls to allow the programs to “talk to each other”.

So, we needed to know which ports were available on each server – without having to get a network admin involved. One easy way of checking to see if you can reach another server via a particular port is to simply telnet to that server on that particular port, and a successful telnet connection to port 3306 would look like this:

sh-3.2# telnet 192.168.1.2 3306
Trying 192.168.1.2...
Connected to 192.168.1.2.
Escape character is '^]'.

However, for this to work, you must have telnet enabled on the target machine (which is usually not a problem). For those cases where you don’t have telnet enabled, you can use a Perl script to act as a “TCP listener” – which will open a TCP socket and listen to a particular port. And of course, for this script to work, you need to have Perl installed on that machine as well.

I wrote this TCP listener script for a different application, but for this example, it works really well. The script simply opens up a TCP port, and waits for something to connect on that port. When a connection is made, it prints the current date/time and the information that it received via that port.

Here is the Perl script:

#!/usr/bin/perl

use IO::Socket;
use IO::Socket::INET;
use POSIX 'strftime'; # Need to explicitly load the functions in POSIX

# Note that if you pass no argument to localtime, it assumes the current time
my $DateTime = strftime '%Y-%m-%d-%H:%M:%S', localtime;

# ----------------------------------------------------------------------------------
print "\nStarting.... $DateTime\n\n";
# ----------------------------------------------------------------------------------

$count = 1;
$limit = 100;

while ($count <= $limit)

{
     my $sock = new IO::Socket::INET (
     LocalPort => '3306',
     Proto => 'tcp',
     Listen => SOMAXCONN,
     ReusePort => 1
     );
     
     die "Could not create socket: $!\n" unless $sock;

     my $new_sock = $sock->accept();
     while(<$new_sock>) {
     
      s/ //g;
      chop $_;
      my $DateTime = strftime '%Y-%m-%d-%H:%M:%S', localtime;
      print "$count -- $DateTime -- $_ \n";
     }
     
     close($sock);

$count++;

}

The reason that I have a "while ($count <= $limit)" loop is because for some reason, after the script received a connection, it would time out after a couple of minutes and quit working. With the while loop, if it drops the connection, the connection will be restarted automatically until the $limit variable value is reached.

Remember – you need to have Perl installed on the target machine for this to work. Once you have this script running, you can connect to that machine on the port specified via several methods. This is what it looks like when you connect via telnet: (I manually typed in “hello” and “goodbye” so you could see the output from the TCP listener script)

# telnet 192.168.1.2 3306
Trying 192.168.1.2...
Connected to 192.168.1.2.
Escape character is '^]'.
hello
goodbye
^]
telnet>

And this is what the output looks like on the server that you connected to:

# /usr/bin/perl tcp_listen.pl

Starting.... 2011-09-06-14:26:20

-- 2011-09-06-14:26:24 -- hello
-- 2011-09-06-14:26:27 -- goodbye

You can also connect via http – as http://192.168.1.2:3306. Note that your browser will not display anything, as it is waiting for the Perl script to respond. But you will see something like this output from the script:

sh-3.2# /usr/bin/perl tcp_listen_test.pl

Starting.... 2011-09-06-14:27:45

-- 2011-09-06-14:27:50 -- GET/HTTP/1.1
-- 2011-09-06-14:27:50 -- Host:192.168.1.2:3306
-- 2011-09-06-14:27:50 -- User-Agent:Mozilla/5.0(Macintosh;IntelMacOSX10.6;rv:6.0.1)Gecko/20100101Firefox/6.0.1
-- 2011-09-06-14:27:50 -- Accept:text/html,application/xhtml+xml,application/xml;q=0.9,*/*;q=0.8
-- 2011-09-06-14:27:50 -- Accept-Language:en-us,en;q=0.5
-- 2011-09-06-14:27:50 -- Accept-Encoding:gzip,deflate
-- 2011-09-06-14:27:50 -- Accept-Charset:ISO-8859-1,utf-8;q=0.7,*;q=0.7
-- 2011-09-06-14:27:50 -- Connection:keep-alive
........

This script is handy for testing the connectivity availability of any port from one machine to the next, to make sure that the port is not being blocked by a firewall. Or, if you need a TCP listener, it works great for that as well. But, you won’t be able to use this script on a port that is already being used by another application.

Here are the default ports commonly used by MySQL Enterprise Monitor and the Agent:

Service Port #
Bundled MySQL 13306
Proxy  4040
Tomcat Server 18080
Tomcat SSL 18443
Tomcat Shutdown 18005
Tomcat SSL 18443

 

—————————————–

Tony Darnell is a Principal Sales Consultant for MySQL, a division of Oracle, Inc. MySQL is the world’s most popular open-source database program.

Tony may be reached at info [at] ScriptingMySQL.com and on LinkedIn.


PlanetMySQL Voting: Vote UP / Vote DOWN
 

Posted in mysql, perl, Scripting

Комментарии отключены

 

Convert .csv File to MySQL Database via Perl

Сентябрь 2nd, 2011

Have you ever had a spreadsheet file or a large .csv file that you wanted to manipulate, but you want more power than a spreadsheet program could offer?

Before I started using MySQL, I would usually throw the .csv file into a desktop database program, like FileMaker. FileMaker would allow you to import the .csv file and it would automatically create the column headers for you. Recently, I was given a spreadsheet with 27,000 rows in it. I still use FileMaker for some databases, but I wanted the power of MySQL to manipulate the information contained in this file. So, I could have easily just typed out the database column names manually into a MySQL “create table” statement, guessed at the types and sizes of the columns and then imported the .csv file. Instead, I decided to write a Perl script to do the dirty work for me. Plus, this spreadsheet had 45 columns of varying lengths. Ouch.

Please keep in mind that this was a quick hack. I did some testing on some small data, and then crunched the big spreadsheet. Everything seemed to work out okay, but I did get a bunch of Perl errors “Use of uninitialized value…”. So, I just turned off “warnings” and proceeded. Feel free to show me how to correct these errors – I just didn’t want to take the time. I am not an expert at writing Perl code anyway.

The script is fairly simple. The .csv file has to have header information in order to be able to create the column names, the delimiter must be double-quotes and a comma – “,” – and so each line must look something like this:

"Name","Address","City","State","Zip","Phone","Amount1","Amount2","Amount3"

There are times when you will have a .csv file that doesn’t have the full “,” delimiter – such as when a column is a number, the “s are left off and only a comma is used as the delimiter – and I could have incorporated that functionality into the script, but my .csv file had the full delimiter for both numbers and text.

I created a test .csv file, and this is the data that I used to initially test the script:

"Name","Address","City","State","Zip","Phone","Amount1","Amount2","Amount3"
"Tony Davidson","100 Main Street","Suwanee","Georgia","30024","6785552222","440.03","4522.00","82837"
"Jack Smith","5000 Washington Ave.","Chattanooga","California","31069","(770)5552941","400","456.787","2828"
"Paul Davis","P.O. Box AB123","Jackson","Georgia","31069","(770)555-6532","325.1","837.3","3848"
"Jacky Smith-Davidson","400 Metropolitan Ave","Macon","Georgia","30024-2032","770-555-8987","503.920","392.4","292833"
"Davd Jackson","100 Oak Drive","Savannah","Tennessee","40024","770) 555- 8941","5492.0","15674.01","43"

The script only determines three data types – varchar, integer and decimal. If your data doesn’t fit into these three types, or if you want to use different data types, then the program probably won’t work for you. The script calculates the longest length for each column, so if you will be changing and increasing the length of the data in the columns, you will want to edit the length of the columns in the “create table” statement that is created.

Once the script was run against the above data, two files were created – mysql_create_table.sql, which is the SQL that you can use to create the actual table – and mysql_data_values.sql, which is the “insert into” file that you can use to load the data into the table.

Here is the SQL statement that was created by the script (mysql_create_table.sql), which can use used to create the table for the data above:

CREATE TABLE `Addresses` (
`Name` varchar (20),
`Address` varchar (20),
`City` varchar (11),
`State` varchar (10),
`Zip` varchar (10),
`Phone` varchar (14),
`Amount1` decimal (7,3),
`Amount2` decimal (8,3),
`Amount3` int (6)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

And here is the “insert” data (mysql_data_values.sql) that is created that you can use to load the database:

insert into Addresses (Name, Address, City, State, Zip, Phone, Amount1, Amount2, Amount3)
values ('Tony Davidson', '100 Main Street', 'Suwanee', 'Georgia', '30024', '6785552222', '440.03', '4522.00', '82837');
insert into Addresses (Name, Address, City, State, Zip, Phone, Amount1, Amount2, Amount3)
values ('Jack Smith', '5000 Washington Ave.', 'Chattanooga', 'California', '31069', '(770)5552941', '400', '456.787', '2828');
insert into Addresses (Name, Address, City, State, Zip, Phone, Amount1, Amount2, Amount3)
values ('Paul Davis', 'P.O. Box AB123', 'Jackson', 'Georgia', '31069', '(770)555-6532', '325.1', '837.3', '3848');
insert into Addresses (Name, Address, City, State, Zip, Phone, Amount1, Amount2, Amount3)
values ('Jacky Smith-Davidson', '400 Metropolitan Ave', 'Macon', 'Georgia', '30024-2032', '770-555-8987', '503.920', '392.4', '292833');
insert into Addresses (Name, Address, City, State, Zip, Phone, Amount1, Amount2, Amount3)
values ('Davd Jackson', '100 Oak Drive', 'Savannah', 'Tennessee', '40024', '770) 555- 8941', '5492.0', '15674.01', '43');

Here is the MySQL command that you can use to import your .sql file into the MySQL database:

mysql -u -p database_name < mysql_data_values.sql

You will want to edit the script and edit/enter your relevant information – such as .csv file name, output file names and storage engine.

I tried this on my 27,000 line .csv file, and it appeared to work. If the data in the .csv file had been a bit cleaner, I could state that it positively did work, but there was so much data that it was difficult to check. The theory is that with this script, you can take a unwieldy .csv file, import it into MySQL, do your work, and then export it again as a .csv file. And, you can always write a script to automatically execute the creation of your table and the importing of the data.

So, feel free to give this a shot and let me know if it worked for you or not.


#!/usr/bin/perl

use File::Copy;
#use warnings;

# edit these next lines to match your preferences
$TABLE_NAME = "Addresses";
$DATABASE_ENGINE = "InnoDB";
$DEFAULT_CHARSET = "latin1";

# enter the file name that you want to convert to the SQL statements and values
$filename = "test.csv";

# create your output files
open(TABLE, ">mysql_create_table2.sql") || die "Can't redirect stdout";
open(VALUES, ">mysql_data_values2.sql") || die "Can't redirect stdout";

# count how many lines are processed
$count = 0;

# set to be blank
$Columns_Values = "";

# open the file to be processed
open FILE, "$filename" or die $!;

# assign the first line which contains headers to the variable $columns
my $columns = <FILE>;

# remove carriage return (for Windows, if you have a CR and a LF, you will need to chop twice)
chop $columns;
# uncomment the next line for Windows files
#chop $columns;

# check to see if the field contains a ' - and if so, add a slash \ in front
$columns =~ s/'/\\'/g;

# ...remove the first " and then "," will be our delimiter
$columns =~ s/\"//;
# ...remove the last " from the end of the line so that "," will be our delimiter
chop $columns;

# remove spaces, add an underscore _
$columns =~ s/ /_/g;
#print "$columns\n";

# split first line into individual field names
@Field_Names = split("\",\"",$columns);

# total number of field names
$Field_Names_Count = $#Field_Names;

# add one to the $Field_Names_Count
$Field_Names_Count_Plus_One = $Field_Names_Count + 1;

# start the field count at zero
$field_count = 0;

# create the column names (values) for the "insert into" part of the SQL statement
if ($count == 0)

{

$column_count = 0;

   while ($column_count <= $Field_Names_Count)
   
   {
      if ($column_count < $Field_Names_Count)
   
      {
         $Columns_Values = $Columns_Values . $Field_Names[$column_count] . ", ";
      }
      
      
      if ($column_count == $Field_Names_Count)
   
      {
         $Columns_Values = $Columns_Values . $Field_Names[$column_count];
      }

      $column_count++;
   }
   
# end if ($count == 0)
}

$count = 0;

# continue to parse the rest of the file which contains the data
while (<FILE>)

{

# remove the carriage return
chomp $_;

# remove the first " and then...
$_ =~ s/\"//;

# ...remove the last " from the end of the line so that "," will be our delimiter
chop $_;

# split the first line into what will be used as the column names
@Field_Values = split("\",\"",$_);

while ($field_count <= $Field_Names_Count )

{

   # check to see if the field contains a ' - and if so, add a slash \ in front
   $Field_Values[$field_count] =~ s/'/\\'/g;

         # if a field is blank, set it to zero, and then remove the zero later
         if (length($Field_Values[$field_count]) < 1)
         
         {
            $Field_Values[$field_count] = "0";
         }

         # check to see if the field value contains any alphabet characters
         if ( $Field_Values[$field_count] =~ m/[a-zA-Z]/)
         
         {
               $type[$field_count] = "varchar";
               
               # find the longest length of the data in the column
               if ($length[$field_count] < 'length($Field_Values[$field_count])')
            
               {
                  $length[$field_count] = length($Field_Values[$field_count]);
               }
         }
   
   
   # once a field has been designated as a varchar, we don't need to test it any further
   # as we aren't going to change a varchar field back to a number or decimal field
   if ($type[$field_count] ne "varchar")
   
   {
         # check to see if the field value does not contain any alphabet characters
         if ( $Field_Values[$field_count] =~ m/[^a-zA-Z]/)
   
         {
            # if the field was already determined to be a decimal, then keep it a decimal
            # if not, then set it to be a number
            if ($type[$field_count] ne "decimal")
            
            {
               $type[$field_count] = "int";
               
               # find the longest length of the data in the column
               if ($length[$field_count] lt 'length($Field_Values[$field_count])')
               {
                  $length[$field_count] = length($Field_Values[$field_count]);
               }
            }
         }
   
         # if the field contains numbers and a period
         if ( $Field_Values[$field_count] =~ m/[0-9.]/)
   
         {
               @count_periods = split("\\.",$Field_Values[$field_count]);
               $number_of_periods = $#count_periods;
            
            
            # if there are two periods in the field, then it is a varchar
            if ($number_of_periods > 1)
            
            {
   
            $type[$field_count] = "varchar";
            
         
               # check for the length of the field to make sure we have the highest field length
               if ($length[$field_count] < 'length($Field_Values[$field_count])')
               {
                  $length[$field_count] = length($Field_Values[$field_count]);
               }
   
   
                  # set these values to be zero - in case the previous field contained a decimal number
                  $decimal_length1[$field_count] = "";
                  $decimal_length2[$field_count] = "";
               }
   
            # if there is only one period in the field, then it is a decimal with X number of decimal places
            if ($number_of_periods == 1)
            
            {
               $type[$field_count] = "decimal";
               
               # split the number to find out the length of each side of the decimal
               # example 1234.56 = 4,2
               @split_decimal_number = split("\\.",$Field_Values[$field_count]);
               
               # find the length of each side of the decimal and keep the highest value
               # this is for the number to left of the decimal
               if ($decimal_length1[$field_count] lt length($split_decimal_number[0]))
               
               {
                  $decimal_length1[$field_count] = length($split_decimal_number[0]);
               }
               
               # find the length of each side of the decimal and keep the highest value
               # this is for the number to right of the decimal
               if ($decimal_length2[$field_count] lt length($split_decimal_number[1]))
               
               {
                  $decimal_length2[$field_count] = length($split_decimal_number[1]);
               }
                           
            # end if ($number_of_periods == 1)
            }
   
         # end if ( $Field_Values[$field_count] =~ m/[0-9.]/)
         }
                  
         # if the field contains anything else besides a 0-9 or a period (.)
         if ( $Field_Values[$field_count] =~ m/[^0-9.]/)
         
         {
               $type[$field_count] = "varchar";
   
               # find the longest length of the data in the column
               if ($length[$field_count] lt 'length($Field_Values[$field_count])')
            
               {
                  $length[$field_count] = length($Field_Values[$field_count]);
               }
   
         # end if ( $Field_Values[$field_count] =~ m/[^0-9.]/)         
         }
   
   # end if ($type[$field_count] ne "varchar")
   }
   
   else
   
   {         
   
               # check for the length of the field to make sure we have the highest field length
               if ($length[$field_count] < length($Field_Values[$field_count]))
            
               {
                  $length[$field_count] = length($Field_Values[$field_count]);
               }
   
   
   # end else
   }
   
   
   # uncomment this line if you want to see the data being processed - as well as another line below
   # print "$Field_Values[$field_count] $type[$field_count] $length[$field_count] $decimal_length1[$field_count] $decimal_length2[$field_count] | ";
   
   
         # if a field is blank, we set it to zero earlier, now we remove the zero
         if (length($Field_Values[$field_count]) < 1)
         
         {
            $Field_Values[$field_count] = "";
         }

   
      # create the syntax needed for the "insert into" statement    
      if ($field_count == 0)
      
      {
         print VALUES "insert into $TABLE_NAME ($Columns_Values) \nvalues ('$Field_Values[$field_count]'";
      }
      
         if ($field_count > 0 && $field_count < $Field_Names_Count_Plus_One)
         
         {
            print VALUES ", '$Field_Values[$field_count]'";
         }
         
      $field_count++;
      # end while ($field_count < $Field_Names_Count_Plus_One )
      }
   
         # check for last entry and then start over on next line
         if ($field_count == $Field_Names_Count_Plus_One)
         
         {
            $field_count = 0;
            $count++;
         
            # close the print statement for the column values
            print VALUES ");\n";
         }
   
   # uncomment this line if you want to see the data being processed
   # print "\n";

# end while (<FILE>)
}

# print the create table statement
print TABLE "\n\nCREATE TABLE `$TABLE_NAME` (\n";

$count_columns = 0;

# loop through the columns and print the type and length for each
while ($count_columns < $Field_Names_Count_Plus_One)

{
   # make sure that we don't have a blank field value
   if (length($Field_Names[$count_columns]) > 0)
   
   {
      if ($type[$count_columns] =~ "decimal")
      
      {
         $decimal_field_length = $decimal_length1[$count_columns] + $decimal_length2[$count_columns];
         print TABLE " `$Field_Names[$count_columns]` $type[$count_columns] ($decimal_field_length,$decimal_length2[$count_columns])";
      }
      
      else
      
      {
         print TABLE " `$Field_Names[$count_columns]` $type[$count_columns] ($length[$count_columns])";
      }
   
      if ($count_columns < $Field_Names_Count)
      
      {
         print TABLE ",\n";
      }
      
      if ($count_columns == $Field_Names_Count_Plus_One)
      
      {
         print TABLE "\n\n";
      }
      
   # end if (length($Field_Names[$count_columns]) > 0)
   }

$count_columns++;

# end while ($count_columns < $Field_Names_Count_Plus_One)
}

# print an output to show how many lines were processed
print "Processed $column_count columns and $count lines.\n";

print TABLE "\n) ENGINE=$DATABASE_ENGINE DEFAULT CHARSET=$DEFAULT_CHARSET\n";

print TABLE "\n\n";

close(FILE);

exit;

 

—————————————–

Tony Darnell is a Principal Sales Consultant for MySQL, a division of Oracle, Inc. MySQL is the world’s most popular open-source database program.

Tony may be reached at info [at] ScriptingMySQL.com and on LinkedIn.


PlanetMySQL Voting: Vote UP / Vote DOWN
 

Posted in mysql, perl, Scripting

Комментарии отключены

 

Want to hack Maatkit and Aspersa? We’re hiring

Сентябрь 1st, 2011

As announced on the Maatkit and Aspersa mailing lists, Daniel and I have created a new toolkit that represents the union of the two, and will be focusing efforts on this Percona Toolkit moving forward. The goal is to make them simpler and significantly more powerful, and to create more tools. The tools will continue to be open-source, but will be developed primarily to meet our MySQL support and consulting staff’s needs.

If you’re interested in challenging software engineering in Perl and shell, then please apply online. You can work online from anywhere, but I strongly prefer someone in the Americas timezones.

Further Reading:


PlanetMySQL Voting: Vote UP / Vote DOWN
 

Posted in Aspersa, perl, SQL, tools

Комментарии отключены

 

Scripting Backups of MySQL with Perl via mysqldump

Август 26th, 2011

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 does, and there may be issues with mysqldump locking tables during the backup process. So for critical data, you should consider MySQL Enterprise Backup.

There are too many options when using mysqldump to backup your data for me to discuss here, so this post will only provide you with a script to perform a basic mysqldump of your databases. For more information on mysqldump, see the mysqldump documentation page.

You run mysqldump from the command line. And while running a backup from the command line is easy, it is easier to have a script that will do this process for you at a regular interval. This script reads a list of the tables that you want to backup from a file, and then backs up each table separately into a named directory. I have been using this script on my home MySQL server for a couple of years now. Since my data isn’t critical, I have a cron job that runs once a day to backup my data.

You will still have to remember to clean out the directory where the backups are stored. For me, I use a simple Unix command to find any backup directories over seven days old (via the -ctime +7 option), and delete them. I run this command as a cron job and it runs once a week. Here is the command:

find /usr/local/backups/mysqlbak -depth 1 -ctime +7 -print -exec rm -rf {} \; 2>/dev/null >/usr/local/backups/mysqlbak/rm.txt

I use a text file named mysql_bak.config that contains a list of the database tables that I want to backup. I place this file in the same directory as the Perl script, but you may modify the Perl script to place this file anywhere on your system (as long as your user has permissions to access this file). Instead of using a list of databases from a file, you could use a mysql command “show databases” to find a list of your databases and incorporate that command into the Perl script. However, since my list of databases do not change often, I can use a static list.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| address            |
| cbgc               |
| twtr               |
+--------------------+

You may also tell the Perl script to ignore any database with a comment (#) before the database name. Using the list above, my backup list file looks like this:

information_schema
# address
cbgc
twtr

For this example, I don’t want to backup the address database. The rest of the databases will be backed up individually, and placed in a directory. The backup directory will be created by the Perl script with this naming convention: year-month-day-time, like this:

# pwd
/usr/local/backups/mysqlbak
# ls -lt
drwxr-xr-x 10 root staff 340 Aug 19 00:03 2011-08-19-0003
drwxr-xr-x 10 root staff 340 Aug 18 00:03 2011-08-18-0003
drwxr-xr-x 10 root staff 340 Aug 17 00:03 2011-08-17-0003
drwxr-xr-x 10 root staff 340 Aug 16 00:03 2011-08-16-0003

Inside each directory is a list of the databases that were backed up:

# pwd
/usr/local/backups/mysqlbak
# cd 2011-08-19-0003
# ls -l
total 318136
-rw-r--r-- 1 root staff 104043 Aug 19 00:03 cbgc.sql.Z
-rw-r--r-- 1 root staff 539 Aug 19 00:03 information_schema.sql.Z
-rw-r--r-- 1 root staff 159574979 Aug 19 00:04 twtr.sql.Z

And here is the Perl script. You will have to change parts of the script to match your system configuration.

#!/usr/bin/perl
# Perform a mysqldump on all the databases specified in the dbbackup.config file

use warnings;
use File::Basename;

# example config file
# You may use a comment to bypass any database that you don't want to backup
# # Unwanted_DB (commented - will not be backed up)
# twtr
# cbgc

# set the directory where you will keep the backup files
# make sure that the directory exists
$backup_folder = '/usr/local/backups/mysqlbak';

# the config file is a text file with a list of the databases to backup
# this should be in the same location as this script, but you can modify this
# if you want to put the file somewhere else
my $config_file = dirname($0) . "/mysql_bak.config";

# retrieve a list of the databases from the config file
my @databases = removeComments(getFileContents($config_file));

# change to the directory of the backup files.
chdir($backup_folder) or die("Cannot go to folder '$backup_folder'");

# grab the local time variables
my ($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst) = localtime(time);
$year += 1900;
$mon++;
#Zero padding
$mday = '0'.$mday if ($mday<10);
$mon = '0'.$mon if ($mon<10);

$hour = "0$hour" if $hour < 10;
$min = "0$min" if $min < 10;

# create the name of the backup folder that will contain all of the backup files
my $folder = "$year-$mon-$mday-$hour$min";
mkdir($folder) or die("Cannot create a folder called '$folder'");

# backup each database contained in the @databases array
foreach my $database (@databases) {
next if ($database eq '');
chomp($database);

my $table = '';
# Get just 1 table in the database - if there is a ' '(space) in the db name
if(index($database,' ')+1) {
my @parts = split(' ',$database);
$database = $parts[0];
$table = $parts[1];
}

# you may comment out this print statement if you don't want to see this information
print "Backing up $database ... ";

my $file = $database;
$file .= '_' . $table if($table ne '');
$file .= ".sql";

# perform a mysqldump on each database
# change the path of mysqldump to match your system's location
# make sure that you change the root password to match the correct password
`/usr/local/mysql/bin/mysqldump -R --triggers -u root --password=password $database $table | compress > $folder/$file.Z`;

# you may comment out this print statement if you don't want to see this information
print "Done\n";
}
# you may comment out this print statement if you don't want to see this information
print "Done\n\n";

exit;

# this subroutine simply creates an array of the list of the databases

sub getFileContents {
my $file = shift;
open (FILE,$file) || die("Can't open '$file': $!");
my @lines=<FILE>;
close(FILE);
return @lines;
}

# remove any commented tables from the @lines array

sub removeComments {
my @lines = @_;
@cleaned = grep(!/^\s*#/, @lines); #Remove Comments
@cleaned = grep(!/^\s*$/, @cleaned); #Remove Empty lines
return @cleaned;
}

 

—————————————–

Tony Darnell is a Principal Sales Consultant for MySQL, a division of Oracle, Inc. MySQL is the world’s most popular open-source database program.

Tony may be reached at info [at] ScriptingMySQL.com and on LinkedIn.


PlanetMySQL Voting: Vote UP / Vote DOWN
 

Posted in mysql, perl, Scripting

Комментарии отключены

 

Inserting Data into MySQL with Perl

Август 6th, 2011

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 insert one address line with our data. You can modify this script to loop the process and insert multiple rows of data.



- - - - START SCRIPT - - - - (do not include this line in the script)
#!/usr/bin/perl

# DBI is the standard database interface for Perl
# DBD is the Perl module that we use to connect to the MySQL database
use DBI;
use DBD::mysql;

$database = "scripting_mysql";

#----------------------------------------------------------------------
# insert the values into the database
#----------------------------------------------------------------------

# invoke the ConnectToMySQL sub-routine to make the database connection
$connection = ConnectToMySql($database);

# set the value of your SQL query
$query = "insert into address (name_first, name_last, address_01, address_02, address_city, address_state, address_postal_code)
			values ('John', 'Smith', '100 Main Street', 'Suite 500', 'Buffalo', 'NY', '14201') ";

# prepare your statement for connecting to the database
$statement = $connection->prepare($query);

# execute your SQL statement
$statement->execute();

#----------------------------------------------------------------------

#----------------------------------------------------------------------
# retrieve the values from the database
#----------------------------------------------------------------------

# set the value of your SQL query
$query2 = "select name_first, name_last, address_01, address_02, address_city, address_state, address_postal_code from address where name_last = 'Smith'";

# prepare your statement for connecting to the database
$statement = $connection->prepare($query2);

# execute your SQL statement
$statement->execute();

# we will loop through the returned results that are in the @data array
# even though, for this example, we will only be returning one row of data

   while (@data = $statement->fetchrow_array()) {
      $name_first = $data[0];
      $name_last = $data[1];
      $address_01 = $data[2];
      $address_02 = $data[3];
      $address_city = $data[4];
      $address_state = $data[5];
      $address_postal_code = $data[6];

print "RESULTS - $name_first, $name_last, $address_01, $address_02, $address_city, $address_state, $address_postal_code\n";

}

#----------------------------------------------------------------------

# exit the script
exit;

#--- start sub-routine ------------------------------------------------
sub ConnectToMySql {
#----------------------------------------------------------------------

my ($db) = @_;

# open the accessDB file to retrieve the database name, host name, user name and password
open(ACCESS_INFO, "<..\/accessAdd") || die "Can't access login credentials";

# assign the values in the accessDB file to the variables
my $database = ;
my $host = ;
my $userid = ;
my $passwd = ;

# assign the values to your connection variable
my $connectionInfo="dbi:mysql:$db;$host";

# close the accessDB file
close(ACCESS_INFO);

# the chomp() function will remove any newline character from the end of a string
chomp ($database, $host, $userid, $passwd);

# make connection to database
my $l_connection = DBI->connect($connectionInfo,$userid,$passwd);

# the value of this connection is returned by the sub-routine
return $l_connection;

}

#--- end sub-routine --------------------------------------------------

- - - - STOP SCRIPT - - - - (do not include this line in the script)

Now you are ready to run the script, connect to the database, and retrieve the MySQL server version information. Here is what the script looks like when I run it from the command line:

 

—————————————–

Tony Darnell is a Principal Sales Consultant for MySQL, a division of Oracle, Inc. MySQL is the world’s most popular open-source database program.

Tony may be reached at info [at] ScriptingMySQL.com and on LinkedIn.


PlanetMySQL Voting: Vote UP / Vote DOWN
 

Posted in mysql, perl, Scripting

Комментарии отключены

 

Connecting to MySQL with Perl

Июль 27th, 2011

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 use Perl to connect to a MySQL database and simply retrieve the database version using an SQL statement. This script has the same functionality as my previous post Connecting to MySQL with Python.

For this example, we will assume that you have installed Perl and MySQL. You will also need to install the DBI and the DBD::mysql modules in Perl. These modules allow you to connect to the MySQL database.

In this script, we are going to use a text file to store our database connection information, as well as a sub-routine to help retrieve our database connection information and to create the connection string for connecting to the database.

Here is the script. (The first line of the script must be the path of your Perl executable – #!/usr/bin/perl.)


- - - - START SCRIPT - - - - (do not include this line in the script)
#!/usr/bin/perl

# DBI is the standard database interface for Perl
# DBD is the Perl module that we use to connect to the <a href=http://mysql.com/>MySQL</a> database
use DBI;
use DBD::mysql;

# invoke the ConnectToMySQL sub-routine to make the database connection
$connection = ConnectToMySql($Database);

# set the value of your SQL query
$query = "SELECT VERSION()";

# prepare your statement for connecting to the database
$statement = $connection->prepare($query);

# execute your SQL statement
$statement->execute();

# retrieve the values returned from executing your SQL statement
@data = $statement->fetchrow_array();

# print the first (and only) value from the @data array
# we add a \n for a new line (carriage return)
print "$data[0] \n";

# exit the script
exit;

#--- start sub-routine ------------------------------------------------
sub ConnectToMySql {
#----------------------------------------------------------------------

my ($db) = @_;

# open the accessDB file to retrieve the database name, host name, user name and password
open(ACCESS_INFO, "<..\/accessDB") || die "Can't access login credentials";

# assign the values in the accessDB file to the variables
my $database = <ACCESS_INFO>;
my $host = <ACCESS_INFO>;
my $userid = <ACCESS_INFO>;
my $passwd = <ACCESS_INFO>;

# assign the values to your connection variable
my $connectionInfo="dbi:mysql:$db;$host";

# close the accessDB file
close(ACCESS_INFO);

# the chomp() function will remove any newline character from the end of a string
chomp($database);
chomp($host);
chomp($userid);
chomp($passwd);

# make connection to database
my $l_connection = DBI->connect($connectionInfo,$userid,$passwd);

# the value of this connection is returned by the sub-routine
return $l_connection;

}

#--- end sub-routine --------------------------------------------------

- - - - STOP SCRIPT - - - - (do not include this line in the script)

In the sub-routine “ConnectToMySql” above, we are using a text file (named accessDB) to store our MySQL database name, host name, user name and password (we will call this our access file). In the previous post, we hard-coded the database name, host name, user name and password in the script itself. For this script, we will create a text file to store this information, and we will place the text file in the parent directory (from where the script is stored). I have my sample script in the /cgi-bin/blog/ directory, so we would place the access file in the parent directory /cgi-bin .

The purpose of this access file is to hide the connection information, but it also allows you to quickly change the information if you have a need to switch to a different database, host or user name. And you can use this same file for multiple scripts, but I would have separate scripts for each database name.

Here is a sample access file – which contains (in this order), the database name, host name, user name and password. To match the file name in the sub-routine, we are naming the file “accessDB”. This file should be saved as a text file and make sure that it has the correct read permissions (for Unix, the permission is 644).


- - - - START TEXT FILE - - - - (do not include this line in the file)
my_database_name
192.168.1.1
mysql_user
password
- - - - END TEXT FILE - - - - (do not include this line in the file)

Again, place this file in the parent directory of where your script is located. This prevents anyone from seeing the file in case you didn’t protect the directory contents from being listed via a web page.

We use the sub-routine to extract the database name, host name, user name and password from the file after reading the file into the program. You will place the sub-routine at the end of your Perl script.

Now you are ready to run the script, connect to the database, and retrieve the MySQL server version information. Here is what the script looks like when I run it from the command line:

It is a fairly easy script, but it is limited to displaying only one line of output. I will cover multiple lines of output in a future post.

 

—————————————–

Tony Darnell is a Principal Sales Consultant for MySQL, a division of Oracle, Inc. MySQL is the world’s most popular open-source database program.

Tony may be reached at info [at] ScriptingMySQL.com and on LinkedIn.


PlanetMySQL Voting: Vote UP / Vote DOWN
 

Posted in mysql, perl, php, Python, Scripting

Комментарии отключены

 

Excluding databases from mysqldump

Декабрь 6th, 2010
A question that came up during the MySQL track at the UKOUG conference in Birmingham was "Can I exclude only a few databases from mysqldump? Let's say that I have 50 databases, and I want to dump all of them, except a few."
As many know, mysqldump has an option to ignore specific tables. SO if you have 1,000 tables in a databases, you can tell mysqldump to dump all the tables except a few ones.
There is no corresponding option to exclude one or more databases.
However, if you know your command line tools, the solution is easy:
First, we get the list of all databases:
mysql -B -N -e 'show databases'
information_schema
employees
five
four
mysql
one
performance_schema
six
test
three
two
-B forces batch mode (no dashes box around the data), while -N gets the result without the headers.
Now, let's say that we want to exclude databases four, five, and six. And since we want to avoid unpleasant side effects, also information_schema and performance_schema.
Thus, we pipe the previous data through a filter. I use Perl, but sed or grep could get the job done.
mysql -B -N -e 'show databases' | \
  perl -ne 'print unless /four|five|six|_schema/' 
employees
mysql
one
test
three
two
Now that we have the list of databases that we need, we can tell mysqldump to backup the databases from such list. All we need is converting the vertical list into a horizontal one using xargs

mysql -B -N -e 'show databases' | \
  perl -ne 'print unless /four|five|six|_schema/' \
  xargs echo mysqldump -B 
mysqldump -B employees mysql one test three two
That's it. The last line is the resulting command. Once you are sure that it is what you want, remove the "echo" after xargs, and the command will be executed.

PlanetMySQL Voting: Vote UP / Vote DOWN
 

Posted in backup, mysql, mysqldump, perl, tips

Комментарии отключены

 

innotop version 1.8.0 released

Ноябрь 6th, 2010

I’ve just uploaded the new release of innotop to Google Code. Short version of the changelog: works on MySQL 5.1 with the InnoDB plugin; more efficient; supports Percona/MariaDB USER_STATISTICS data; fixes a bunch of small annoying bugs.

Longer version:

2010-11-06: version 1.8.0

   Changes:
   * Don't re-fetch SHOW VARIABLES every iteration; it's too slow on many hosts.
   * Add a filter to remove EVENT threads in SHOW PROCESSLIST (issue 32).
   * Add a timestamp to output in -n mode, when -t is specified (issue 37).
   * Add a new U mode, for Percona/MariaDB USER_STATISTICS (issue 39).
   * Add support for millisecond query time in Percona Server (issue 39).
   * Display a summary of queries executed in Query List mode (issue 26).

   Bugs fixed:
   * Made config-file reading more robust (issue 41).
   * Hostname parsing wasn't standards compliant (issue 30).
   * MKDEBUG didn't work on some Perl versions (issue 22).
   * Don't try to get InnoDB status if have_innodb != YES (issue 33).
   * Status text from the InnoDB plugin wasn't parsed correctly (issue 36).
   * Transaction ID from InnoDB plugin wasn't subtracted correctly (issue 38).
   * Switching modes and pressing ? for help caused a crash (issue 40).

Related posts:

  1. Getting ready for a new innotop release
  2. Version 0.1.132 of innotop released
  3. innotop version 1.4.3 released
  4. Version 1.6.0 of the innotop monitor for MySQL released
  5. Version 0.1.149 of innotop released


PlanetMySQL Voting: Vote UP / Vote DOWN
 

Posted in Innotop, perl, SQL, tools

Комментарии отключены

 
« Older Entries
Newer Entries »