Rick Baker's Personal Website

Ever have a several gig MySQL dump and only need to extract a table or two out of it?  Here’s something you can run from the command line that will split all of the tables into individual files.  You’ll need to know if your dump file has the CREATE TABLE format included or is just the data.  If it’s just the data then you can change where it says CREATE TABLE to LOCK TABLES below.

One thing to note.  If awk ends up giving you an error such as “Program Limit Exceeded”, then you can use gawk instead and it should work without issues.

cat dumpfile.sql | awk 'BEGIN {
output = "comments"; } $data ~ /^CREATE TABLE/ {
 close(output);
 output = substr($3,2,length($3)-2); }
{ print $data >> output }'

I work on so many sites, domains, linux boxes, MySQL servers, etc. that I have like a million and one passwords dancing around in my head. Went to add a database to an old devel box that I haven’t used for about 2 years and I couldn’t remember the MySQL root password. So I had to reset it. If anyone finds themselves in this situation, here’s how you do it if you have root access to the server. I’ll use my normal way of stopping/starting the service, you may need to alter this for your server.

/etc/init.d/mysql stop
mysqld --skip-grant-tables &
mysql (to get to MySQL shell)
mysql> update user set password = ('newpasssword') where user='root';
mysql> flush privileges;
mysql> exit;
/etc/init.d/mysql restart

Recently was asked a question on how to delete records from multiple tables with a single MySQL query. I’ll give an example below with one caveat. If your tables are INNODB and you have foreign keys then this query will fail. For INNODB you should just do a single delete and rely on the ON DELETE capabilities to handle any other deletes.

Anyways, so on to the query. Say you have 3 tables (table1, table2, and table3). Each of them have an entry for a variable that we’ll call $id and you want to delete from all 3 tables at once. Here you go:

delete t1, t2, t3
from table1 as t1
left join table2 as t2 on t2.id = t1.id
left join table3 as t3 on t3.id = t1.id
where t1.id = '$id'

I just had to give some assistance to a friend who needed to do a remote desktop session to a Windows work computer, but had to go through a couple of hops to get to it. I’ll give the setup below.

He has a linux machine at home (HOME), and a linux machine at work (WORK). (WORK) has access to another Linux machine (WORK2) on the network that the Windows machine (WINDOWS) is on, but not to the Windows machine itself.

In the code below you’ll substitute machine names (given above in parenthesis) with the actual IP or hostname of each machine. (Note: this should be all on one line)

ssh -t -L HOME:3389:WORK:3389 user@WORK
ssh -L WORK:3389:WINDOWS:3389 user@WORK2

At that point he just had to remote desktop to HOME and he ended up with a session on WINDOWS.

So, over the weekend I decided to upgrade my home debian fileserver.   A couple of new drives running software Raid 1, more memory and a wireless card.  With wireless I figured I could get the fileserver out from behind my desk and stick it into the garage.  Being in a small town, my selection of hardware is pretty much limited to a Walmart and Staples.  The only card I could come up with was a the Netgear WG311 rev3.

Started out trying to use madwifi, only to find out that doesn’t work with rev3 of the card.  So, I switched to ndiswrapper and just downloaded the windows xp driver.  All good so far, it detected my card and I had my wlan0 interface.

I try to keep my wireless network secure unlike my neighbors who freely broadcast so I run WPA and do not broadcast.   So this was going to take a bit more work.   Installed wpa_supplicant and fought with this for some time.  Finally I was able to manually bring up my wireless using a wpa_supplicant.conf file but I could never get it to come up with the standard ‘ifup wlan0′.  So after trial and error I came up with this in my network/interfaces file

auto wlan0
iface wlan0 inet static
address 192.168.0.10
gateway 192.168.0.1
dns-nameservers 192.168.0.1
netmask 255.255.255.0
wpa-driver wext
wpa-ap-scan 2
wpa-ssid MySSID
wpa-proto WPA
wpa-pairwise TKIP
wpa-group TKIP
wpa-key-mgmt WPA-PSK
wpa-psk xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx  xxxxxxxx

This finally worked and I now had a wireless connection that would come up on boot.  So after struggling with this over the course of Saturday and most of Sunday I happily moved the server out to the garage, booted up only to find a signal strength of 7/100.  Needless to say that wasn’t going to cut it and locally I couldn’t purchase any type of range extender.   So, after all that I’m back with my debian box in it’s original location.

I figured I’d post this just in case anyone is struggling with this card and the proper configuration.  You’ll find all sorts of solutions via google,  others might work for you, but the above is what finally worked for me.

Working on our hosting webserver the other day and came across an issue that took me a while to figure out.  When trying to restart apache I was getting “No space left on device”, yet we had plenty of hard drive space.  It turns out that we actually had no semaphores available.  Using the following little script run from bash cleared it up.

ipcs -s|awk '{print ipcrm sem  }' > SCRIPT
ipcs -s -t | cut -f 1 -d " " | egrep "^[0-9]+$" | xargs ipcrm sem

I routinely work on small linux boxes that basically run off of a small flashcard as the hard drive. We use these for out client-side network monitoring boxes. The problem with these is they can very quickly run out of drive space, if there is anything being written to the drive at all.

Here’s a very useful command that can be ran from the command line. This will give you a list, starting from your current directory, of files and their modification dates in descending order. So anything recently modified will be at the bottom of the list. Comes in quite handy.

find . -type f -printf '%TY-%Tm-%Td %TT %p\n' | sort