whird.jpope.org cover image

Script for mysql backups

Here is my quick mysql backup script that I have used for a couple of years. Posting it here for reference sake... #!/bin/bash ## if run daily (say, via cron) you will end up with a tar file that contains ## a mysqldump of each database on your server. ## the script will also delete backups that are older than two weeks ## ## jpope <jpope at jpope dot org> ## licensed under WTFPL http://www.wtfpl.net/ ## EDIT OPTIONS ## workingdir=/path/to/mysql/backups dte=$(date +%Y%m%d) dbuser=username dbpass=userpassword dbhost=localhost tmpfile=/tmp/databaselist ## END EDIT OPTIONS ## dblist=$(mysql -u$dbuser -p$dbpass -h$dbhost -e"select distinct t.TABLE_SCHEMA \ from information_schema.tables t WHERE t.TABLE_SCHEMA NOT IN ('information_schema','mysql','performance_schema');" \ | grep -v "^+" | sed "s,\t,.," | grep -v TABLE_SCHEMA > $tmpfile) cd $workingdir mkdir $dte cd $dte databases=$( cat $tmpfile | wc -l ) i=1 ## backup databases $dblist while [ $i -le $databases ] do db=$(cat $tmpfile | sed -…

GNU FM ClientCode table

Check the edit at the bottom first... Earlier I asked: On a !gnufm install, should the ClientCodes table be populated? Mine is empty and I imagine that is what is causing this: http://u.jpope.org/2w Jeremy Pope (jpope)'s status on Tuesday, 08-Jan-13 12:12:38 CST - micro.jpope.org Which resulted in the response: We're discussing ways to populate it on install, or just add the data to source and scrap table, we've been inserting it manually up to now Jonas (kabniel)'s status on Tuesday, 08-Jan-13 19:39:29 UTC Which resulted in me writing this little insert statement to populate the ClientCodes table using the stated wiki entry: insert into ClientCodes (`code`,`name`,`url`,`free`) values ('amk','Amarok 1.4.x plugin','http://amarok.kde.org/','y'), ('amp','Aimp2','http://www.aimp.ru/','n'), ('amy','Anomaly (Chrome extension)','Chrome webstore entry','y'), ('ark','Amarok','http://amarok.kde.org/','y'), ('ass','Last.fm player','http://www.last.fm/download','y'), ('atu','aTunes','http:…

StatusNet Subscription Hacking

Some time back, @sazius changed his StatusNet instance from 'SSL always' to 'SSL sometimes'. My hunch was right. I had problems subscribing to @jezra, switched from always SSL to sometimes. Worked fine. Hope doesn't break smth else:) sazius (sazius)'s status on Friday, 30-Mar-12 18:53:19 UTC - status.saz.im This change caused me to not receive any notices from him aside from direct @-replies. I tried to fix this a few different times without success. Until today. I should also note that I tried to resub (via the browser) to him however, every different way I tried, I would just get a "Could not set up the remote subscription" error message. I also tried running the "two-step" but, it would only return an error as well. Well, crap. Off to the command line then... Before proceeding with this post though, I will mention that it's a real brain dump in the realm of $ cat /home/jpope/brain > /home/jpope/blog and may not make much sense. I will also say that it'll make more sense for those used to…

MySQL Took Over My Hard Drive

Yeah, so it's probably due to misconfiguration on my part but, I ran across an issue where the MySQL binary logs soaked up 83% of the disk space on my server. Of course, at first I didn't realize that was the issue. After looking through various system logs, I moved on to the MySQL logs since I was having issues shutting down and starting MySQL. In the error log found in /var/lib/mysql, I noted that it said something about not having disk space. Wait a minute, the last time I checked I was using well under 5GB of my 20GB partition on my server. A quick 'df -h' confirmed that my root partition was completely full. :/ So to figure out what was using all of my hard drive space, I used this command that will list any files it finds that is over 50MB. # cd / # find . -type f -size +50000k -exec ls -lh {} \; | awk '{ print $9 ": " $5 }' And that showed me that over 2 dozen files were in the /var/lib/mysql dir, all with filenames like mysql-bin.000001 mysql-bin.000002 mysql-bin.000003 mysql-bin.000004 ... mysql-bi…

Remote StatusNet Groups

As some of you may know, I've recently moved from identi.ca to my own StatusNet instance at micro.jpope.org. While it has mostly been painless, there have been a few issues. One issue that I've had was concerning remote groups (i.e. groups located on identi.ca or elsewhere). I'd subscribe to the group and end up not being able to post to those groups. This particular issue seems to not only be limited to myself either (see issue 3507 and possibly related issue 3092). The fix for me was stated in issue 3092. If it turns out that I can't post to a group, I'll add an entry to the group_alias table for that group. Oddly enough, it only affects some groups and not all. At the moment I can't tell why this is but, at least there is a workaround, even if it's slightly manual. Here's a few queries that I am now using to help 'fix' the problem on my instance. EDIT: I have updated the queries slightly. A few quick notes: First things first, I'm using phpMyAdmin for ease of use. You should be able to create the views fr…