You are here:

Mysqldump Specific Table From All Databases

by Brett on January 22, 2013

I recently had a task where I needed to export a specific table that was in a few hundred different databases. However, mysqldump does not have a way to specify that a specific table should be dumped out of every database. See the supported formats below:

mysqldump [options] db_name [tbl_name ...]
mysqldump [options] --databases db_name ...
mysqldump [options] --all-databases

I was hoping for a command like: mysqldump --all-databases 'table_name'.

mysqldump does have an --ignore-table option but in my case there were too many different tables to list and I didn’t want to go there.

My next thought was to build a quick PHP script that would loop through every database, check if the desired table exists and then mysqldump it. Before I had the chance to start on this approach I realized I could accomplish this with a one line shell command. The approach I took was the following:

mysql -s -N -e "select TABLE_SCHEMA from information_schema.tables where TABLE_NAME='users'" | xargs -I % sh -c 'mysqldump % users | mysql -uUSERNAME -pPASSWORD -hHOST %'

In the example above, I got a list of all databases (TABLE_SCHEMA) that contained a “users” table. I piped that output to xargs which runs mysqldump on the specific database and users table. Last I piped mysqldump to send the output to another server so that it could be imported in the same step.

{ 1 comment }

Automatically Allocate IP Address to AWS Instances

by Brett on December 31, 2012

I recently had a task where I needed to quickly start up 50 spot instances that all required an Elastic IP (EIP) address. I initially worked out the steps in the web console and determined I needed to accomplish the following:

  1. Request 50 spot instances based on an existing AMI
  2. Allocate 50 new EIPs
  3. Associate each EIP with one of the newly running spot instances.

Requesting 50 spot instances from the web console was quick and painless. However, the EIP allocation and association quickly become tiresome. As the Amazon web console only allows allocating and associating 1 EIP at a time. To repeat the following steps 50 times did not seem like a good use of time: requesting a new EIP, determining the appropriate instance ID to associate with the EIP and then assigning the EIP

Instead I decided to quickly put together a few commands to achieve the goal using the AWS API. First I issued a request for 50 instances with a command like the following

ec2-request-spot-instances ami-1d2b34e5 --price .15 -n50 -s subnet-c1f234ae -t m2.4xlarge --kernel aki-88aa75e1

Of course the above command will need to be modified for each specific case. The specific AMI ID, max bid price, number of instances, subnet, instance type and kernel will all need their respective values modified.

Then I waited until all of the instances were running with a command like the following:

ec2-describe-instances --filter "instance-state-code=16" | grep 'spot' | grep -E '10\.0\.1\.[0-9]{1,3}\s+vpc' | wc -l

The above command lists all of the running instances (instance-stat-code=16), limits it to only spot instances and then limits the output to a specific VPC that has an internal address in the 10.0.1.* range.

Once the above command displayed 50 I was ready to start allocating and associating IP addresses. I accomplished this with a combinations of commands. I needed to use ec2-allocate-address to request a new EIP and ec2-describe-instances to get a list of instances that need an EIP. Last, ec2-associate-address needed to be used to associate the new EIP with a specific instance ID. The command to accomplish this looked like the following:

for((i=0;i<50;i++)); do ec2-associate-address -a `ec2-allocate-address -d vpc | cut -f5` -i `ec2-describe-instances --filter "instance-state-code=16" | grep 'spot' | grep -E 'monitoring-[a-Z]+\s+10\.0\.1' | cut -f2 | head -n1`; done

The above runs the ec2-associate-address command 50 times. It then runs two sub commands one which requests a new EIP address in the vpc (ec2-allocate-address -d vpc) and one which gets the next running spot instance that does not have an EIP (ec2-describe-instances –filter “instance-state-code=16″…).

Last, the new EIPs can be listed with a command like the following:

ec2-describe-instances | grep 'spot' | cut -f17

This worked beautifully for my goal of quickly firing up 50 spot instances and assigning an EIP address. As always, there is room for improvement. If automating something like this on a regular basis, I would suggest taking the one line command and doing more validation on the output of each command. The above assumes that everything is in a good state and that no issues occur in requesting or assigning the EIPs.

Let me know if you find this useful!

{ Be the first to comment }

HydraIRC / Freenode – Auto Connect, Identify, Join

by Brett on June 27, 2012

I often join IRC channels where other developers hang out. I’ve found this to be very beneficial in keeping up to speed with changing technologies. I generally stick to two servers Freenode and OFTC. Freenode is by far my favorite as it seems to be the standard server for other developers to join and create channels about their products.

Over the years, I’ve found this extremely beneficial. I can generally get immediate responses about bugs, feature requests and up-coming enhancements in software I’m using. Also, if I need a feature added to a piece of software I can code it up and send it straight to the developers. I do highly recommend all developers participate in IRC channels as a huge amount of quality information is being shared and it’s easy to find someone who is more specialized in a specific software development topic.

That said, I’ve used HydraIRC for the last year and it’s been growing on me. I was previously using mIRC which is also a good product.

Automated Connection To Two Servers

The very first task I had after installing HydraIRC was to set up an automated script to connect to my favorite servers and favorite channels. The scripting piece was quite simple and consisted of the following steps:

  1. Open HydraIRC and click Options –> Prefs, then click Scripts
  2. In the Command Profiles box type the name “OnStartup” (this is case-sensitive)
  3. In the Commands window type the commands to join the server. For me this was:
/server irc.freenode.net:6667
/newserver irc.oftc.net:6667

HydraIRC Preferences On Startup Script

With those two command in-place HydraIRC will automatically connect to both Freenode and OFTC when I first start the application. Sweet! As a developer doesn’t it always feel good to save a little time?

Join Channels After Connect

Now, we just need to create two more scripts to connect to specific channels after we join the servers. This consists of the following (similar steps):

  1. Open HydraIRC and click Options –> Prefs, then click Scripts
  2. In the Command Profiles box type the name “irc.freenode.net_OnLoggedIn” (again case-sensitive) or irc.oftc.net_OnLoggedIn. Basically, the format is serverName_OnLoggedIn.
  3. In the commands window type:
/join #php
/join #java
/join #html
/join #apache
(and whatever other channels you like)

Identify After Connect But Before Channel Join

With the above in place OFTC was working perfectly but Freenode was telling me:

Cannot join channel – you need to be identified with the service

To resolve this, the command: /msg NickServ identify needs be sent to the server (it’s like logging in).

The trouble is, doing the commands in the following order does not work:

/msg NickServ identify myPass
/join #php
/join #java

This is because HydraIRC does not wait for the response to the identify command but instead immediately runs the next join command. This means HydraIRC tries to join the channels before the server has finished identifying my nick. Darn!

With a little more thought I did come to a solution that has worked very well. Basically, I needed to execute:

/msg NickServ identify myPass
(pause)
/join #php
/join #java

But, wait, HydraIRC does not have a (pause). The work around was to instead use ping which I’ve seen used for pausing in other scripts that do not support a true pause/wait (i.e. Dos Batch Script).

I modified my irc.freenode.net_OnLoggedIn profile to have the following commands and now when I fire up HydraIRC. I automatically connect to my favorite servers, log into nickserv, and join my favorite channels, all while sipping on a little coffee!

/msg nickserv identify myPass
/ping localhost
/ping localhost
/ping localhost
/ping localhost
/ping localhost
/ping localhost
/ping localhost
/ping localhost
/ping localhost
/ping localhost
/ping localhost
/ping localhost
/ping localhost
/ping localhost
/ping localhost
/ping localhost
/ping localhost
/ping localhost
/ping localhost
/ping localhost
/join #php

I love automation!

What other handy scripts do you use in your IRC client? Please do share!

 

{ 3 comments }

Mercurial Hook for Syntax Checking (PHP)

October 8, 2010

For those unfamiliar with Mercurial, it is an awesome Source Control Management (SCM) tool. One of my favorite features of Mercurial is that the repositories are distributed which allows each machine to have a full copy of the project’s history. Being distributed has many advantages such as faster committing, branching, tagging, merging, etc. since it [...]

Read the full article →

Java Live Messenger (MSN) Robot

September 2, 2010

I recently had a project to setup an Instant Messenger Robot for Windows Live Messenger. A IM robot can have many purposes such as: Keeping track of when contacts are online/offline and when they were last seen. Broadcasting a message to all contacts. Automatically answering common questions. Notifying contacts about new events. A newer site [...]

Read the full article →

UltraMon Breaks After Remote Desktop Connection (RDP)

April 6, 2010

I use the application UltraMon to help manage my multiple monitor setup. Overall this application is awesome as it makes moving applications between monitors a breeze and supports a separate task bar on each monitor, among other things. However, I have had this issue for a while where UltraMon will not move applications between monitors [...]

Read the full article →

How to Setup BlackBerry (bb) with a Different Ringtone for each Email / Contact

April 2, 2010

The BlackBerry has the ability to setup ringtones for each application and also use different ringtones for each contact / email. These features are great for giving full control over notifications for email, phone call, SMS, MMS, IM, etc. With this article I will explain how to setup both application level notifications, contact/email level notifications [...]

Read the full article →

Putty with Tango Look & Feel (using regedit script)

March 23, 2010

I ran into a powershell script that Tomas Restrepo created to set putty up with a better color scheme. The color theme he used was based on Tango. This inspired me to make a few updates to my default configuration for putty. I used the powershell script to update my color theme and then updated [...]

Read the full article →

Format Credit Card with X’s and Dashes using PHP (credit card masking)

March 22, 2010

I recently had a project where I needed to accomplish the following two tasks: Replace all but the last four digits of a credit card with X’s Format the credit card with dashes in the appropriate places There are many different approaches that can be taken to accomplish the above two tasks. The simplest approach [...]

Read the full article →

Update .htaccess with Dynamic DNS IP Address to Prevent Password Protection

March 9, 2010

I was working on a password protected site that needed to allow one specific user access without requiring a login/password to access it. The site was already using .htaccess to password protect the entire site so the quickest solution was to use the following type of setup in the htaccess file: Order deny,allow Deny from [...]

Read the full article →

← Previous Entries