Counting your slow queries by seconds

| No TrackBacks
I needed to check the performance of a database after some changes. This gets me some raw numbers on the performance of the slow queries log in MySQL:

 tail -n 10000 log-slow-queries.log|grep  Query|awk '{print $3}'|awk -F . '{print $1}'|sort -n|uniq -c|awk '{print $2" "$1}'

It outputs seconds taken, and number of times that amount has shown up in the logs.
Enhanced by Zemanta

Inspecting a particular line in a large file

| No TrackBacks
I had a mysqldump that wasn't playing nice and would error out 745,273 lines into the dump. The error log wasn't useful so to look at the exact line, I did this:

sed -n "745273 p" dump.sql

The syntax is:

sed -n "linenumber p" file.txt

How to rename a database in mysql 5.5

| No TrackBacks
In mysql 5.5 the RENAME DATABASE functionality went away, but the manual does show that RENAME TABLE works:

RENAME TABLE current_db.tbl_name TO other_db.tbl_name;

While is is good, it's a pain when there lots of tables, and leaves lots of room for typos and human error. The best way to guard against this is scripting.

I like to do this as a multi part process, but that gives me time to verify everything along the way (I'm paranoid about my databases).

Part 1: Create the new database

You need to create a database to rename the tables into:

mysql> CREATE DATABASE newdb;

Part 2 - getting the list of tables

From the unix command line you'll have to tell mysql to display the tables. I pipe a command into mysql and use head -n -1 to show everything except for the first line.

echo "show tables;"|mysql --defaults-file=/etc/mysql/credentials.cnf dbname|head -n -1 >~/tables.txt
Now review your tables.txt to make sure that you have the list of tables from the database. If you are satisfied that this worked, then it time for part 3.

Part 3: Creating the script:

This is as simple as:

cat ~/tables.txt |xargs -L 1|awk '{print "RENAME TABLE dbname."$1" TO newdb."$1";"}'  >~/rename.txt

I'm using xargs to enforce one table per line and awk to do the formatting.

Part 4: Performing the rename

Now, look at ~/rename.txt. You will see that this generated a series of mysql rename commands, for example:

RENAME TABLE dbname.settings TO newdb.settings;
Now in MySQL make sure you have newdb created, and you can then either paste the contents of rename.txt to your mysql or if you want you can pipe it into mysql:

cat ~/rename.txt|mysql --defaults-file=/etc/mysql/credentials.cnf

You'll now have moved all your tables from dbname to newdb.

Now go do the other housekeeping, such as granting access to newdb and locking it down properly.
Enhanced by Zemanta

Easily dual heading a laptop and a desktop

| No TrackBacks
There are times when I need to use my netbook and desktop together. I've used a KVM setup, but sometimes I want to offload some of my screen space on to the netbook, then it gets annoying. There is a great solution for this called x2vnc

This makes the netbook a second monitor on my desktop. The setup is simple.

  1. Install a VNC server of your choice on your laptop.  I use tightvnc
  2. Configure your vnc security, and start your VNC server
  3. I keep my netbook to the right of my screen so I use this command to start:

    x2vnc -east -resurface

  4. Enter your vnc password and now you can scroll your mouse to the right of the screen and just use it as normal.

It's a really transparent and simple way to chain the screens together.

Enhanced by Zemanta
Recently I had to update a few hundred servers in a way that couldn't be easily scripted or reduced into a patch file.

I had two options:

1) Login to each server, paste the one liner to make the cronjob changes, then check each one and adjust as needed


2) Automate

I went with #2, and it turned out to be pretty simple. You need a couple of tools

1) Ubuntu (I'm on 10.04 LTS desktop)
2) Keyboardcast (apt-get install keyboardcast)

Now I generated a list of server names that I had to change. I found that my desktop could easily handle batches of about 40 at a time, so that's what I used.

I created a file called servers.txt with each server name on a seperate line:

Now I started up keyboardcast and set it to be always on top (note this is very useful later) I opened a terminal window and started up my 40 sessions:

cat servers.txt|xargs -L 1 -I $$ gnome-terminal -x ssh user@$$

Note that I have a ssh config file that provides the useful stuff so I don't need to login - public key authentication is very useful in these cases.

Once the ssh sessions have opened, go to your keyboardcast window, and select all your new sessions, enter your commands, and then exit

Pretty simple, but such a timesaver.

Enhanced by Zemanta

Disabling IPv6 in Firefox

| No TrackBacks
Recently I had an issue where a site was restricting itself to a single IPv4 address. Unfortunately the new server that uses it also supported IPv6. When firefox hit it, the server reported that it wouldn't work with the new server.

I used my favourite site, to check, and it reported an IPv6 address.

I then entered about:config in the address bad and searched for:


I set this to true, then checked again, and got the IPv4 address. Now the site worked for my server.
Enhanced by Zemanta

InnoDB table recovery

| No TrackBacks
Recently I had a problem where an InnoDB table was corrupted. It was reporting a 2013 error and actually dropping all the connections to the database. This was all that was being reported:

    mysql> select * corrupted_table;
    ERROR 2013 (HY000): Lost connection to MySQL server during query

The logs weren't actually reporting anything. Oddly, select count(*) from corrupted_table; was actually returning the correct figure. It was just about two thirds of the way through the table where the error occurred. The usual option is to use:

    innodb_force_recovery = 4

In this case, that didn't work. The table just stopped at the two-thirds corruption, but instead of crashing the innodb_force_recovery option just made it stop.

Truncating the table wasn't an option. Finally I decided to move a backup of the database to another server to try more risky fixes.

My backup software of choice for mysql is Percona Xtrabackup. I like it as InnoDB tables don't get locked during the backup. It's most useful for those several hundred gigabyte sized databases.

I grabbed the backup, moved it to the other server. I unpacked the tgz, and then applied the logs:

    innobackupex-1.5.1 --ibbackup=xtrabackup_51 --apply-log `pwd`

Innobackupex suggests the --copy-back step but this can be (a) slow and (b) expensive on diskspace with a big backup.

I skipped --copy-back by instead moving the database files with the applied logs to /var/lib/mysql and after fixing ownerships I started mysql.

I then checked to see if the table had the corruption. Select count(*)  returned the same value. I then did the select * from test and to my surprise that didn't fail. Something in the restore fixed the broken table.

mysqldump then became my friend, and after dumping the now fixed table, and dropping the broken one, was able to get a fully functional table back again.

The tl;dr is: If nothing else works, try restoring a corrupted table from a backup and see if that fixed the innodb corruption.
Enhanced by Zemanta
I have a ssh shortcut I use a lot, and I made a script for it. It got annoying to open a teminal, then run my script. I added it to my ubuntu panel by using a custom appliction launcher then use this as the command to run:

gnome-terminal -x /path/to/script
Now it opens a new session, and runs my ssh command, getting me straight into my dev machine.
Enhanced by Zemanta