Linux

Linux

by Kris on Linux, Tips and Tweaks

Fear not for I have returned

It’s been a while since I last updated and the truth is I just don’t have time to write proper blog posts. Keeping that in mind, but still wishing to keep this up-to-date. I’ve decided to post quick tips and snippets that I find useful. Here’s the first:

I’ve been doing some database work recently and I wanted to unify the character set and collation for all databases and tables. Changing the database collation was easy to do in bulk. The tables less so and I was not manually changing 13,014 tables, eventually I came up with this:

mysql --batch --skip-column-names --user=username --password=password --execute="select CONCAT('alter table ',TABLE_SCHEMA,'.',TABLE_NAME,' convert to character set utf8 collate utf8_general_ci;') from information_schema.TABLES WHERE TABLE_SCHEMA != 'information_schema' AND TABLE_SCHEMA != 'mysql' AND TABLE_SCHEMA != 'performance_schema';" | mysql --user=username --password=password

That should generate a bunch of statements that look like this:

alter table database_name.table_name convert to character set utf8 collate utf8_general_ci;

For every table in every database except the information_schema, mysql and performance_schema databases. I just pipe these right back in to MySQL but you can output them to a text file and run that later if you prefer. This snippet should also to be useful for any mass/multiple/bulk table edits or changes.

 
by Kris on Linux

By your command

It’s been a while since my last update, but as with any good IT guy I’ve been both too busy and too lazy to write anything. Anyway, I thought I’d make a note of some commands more for my own use that anyone else’s, but if someone else does find one useful then that’s a bonus.

Split a file at a word or pattern into multiple files:

awk '/Pattern to split at/{n++}{print > "split" n ".txt"}' FILE

Notes: Replace FILE with the file name you wish to run the command against.

Take a screenshot via SSH:

DISPLAY=:0.0 import -window root /path/to/directory/screenshot.png

Notes: None.

Search for something that looks like an e-mail address:

egrep -io '([[:alnum:]_.-]+@[[:alnum:]_.-]+?\.[[:alpha:].]{2,6})'

Run a query on multiple tables in a database matching a pattern

mysql -uUSER -pPASSWORD -D DATABASE_NAME -e "show tables" -s | egrep "SEARCH_PATTERN" | xargs -I "@@" mysql -uUSER -pPASSWORD -D DATABASE_NAME -e "DROP TABLE @@;"

Notes: Replace the markers where indicated (USER, PASSWORD etc) and obviously change the example query.

Command line screencast

mkfifo /tmp/fifo;(nc -q0 -k -l -p 5000 < /tmp/fifo > /dev/null &);script -f /tmp/fifo

Notes: Run nc ADDRESS 5000 to connect and watch.

Empty all log files

for file in $(find /var/log -type f); do > $file; done;

Notes: None.

Watch MySQL queries

watch -n 1 mysqladmin --user=USER --password=PASSWORD processlist

Notes: Replace the markers where indicated (USER, PASSWORD etc).

Find potential duplicate files

find -not -empty -type f -printf "%s\n" | sort -rn | uniq -d | xargs -I{} -n1 find -type f -size {}c -print0 | xargs -0 md5sum | sort | uniq -w32 --all-repeated=separate

Notes: None.

 
by Kris on Linux, Tips and Tweaks

Mounting a case-insensitive filesystem on Linux

Recently I had a web developer who was coding on a Linux server, he was used to working on Windows servers and was having problems dealing with the case sensitivity of the EXT3 filesystem. I looked into it and in addition to using the Apache mod_speling module, also decided to go a step further. As root or using sudo do the following:

  1. Create a file of the size you wish the filesystem to be, 1GB in this example: 
    dd if=/dev/zero of=fat32.disk bs=1K count=1M
  2. Format the filesystem, fat32 in this example but you can use NTFS or another case-insensitive file system: 
    mkfs.vfat fat32.disk
  3. Mount the filesystem and test it, you may wish to use uid= and gid= to mount it as a specific user and you’ll probably want to adjust the umask to something less permissive: 
    mount /media/fat32.disk /var/www/fat32 -t vfat -o loop,owner,group,umask=000
  4. Add this to /etc/fstab so that it gets mounted after a reboot: 
    /media/fat32.disk /var/www/fat32 vfat loop,owner,group,umask=000 0 0

I wouldn’t use this for production systems, in that situation I’d recommend a dedicated FAT32 or NTFS partition. If you do have to run this on a production system it should be fine for 99% of setups, it just feels like a quick hack to me.