Category: Ubuntu

Reduce the size of MySQL ibdata1 on OSX

So I finally figured out why my TimeMachine backups were becoming bigger and bigger, 3-4GB backing up every day when I come home from work… It seems that my MySQL database file (/usr/local/mysql/data/ibdata1) keeps getting larger and larger, unnecessarily, even if I deleted databases, tables, etc. It seems even if I only update a few rows in a table in a small database, the ginormous idbdata1 file grows and then get marks as a candidate for backup into TimeMachine. Ugh.

I did some digging and found this interesting tutorial on how to clean up InnoDB storage files.  Here I’ll explain what I specifically did on my OSX 10.6.5 machine with MySQL v5.1.38.

  1. If you’re not a cowboy, stop MySQL, backup all files, then start MySQL again (I used the System pref to stop/start MySQL, feel free to use the command-line instead):

    $ sudo cp -R /usr/local/mysql/data /usr/local/mysql/data.bak
    

  2. Export all data from MySQL:
    $ mysqldump -u root -p --all-databases > alldatabases.sql
    
  3. Drop databases in MySQL (except “mysql” and “information_schema”):
    $ mysql -u root -p
    mysql> show databases;
    mysql> drop database XXXX;
    

    or use this great one-liner to delete all databases, modded a bit so it would work for me:

    # measure twice, cut once... make sure we are deleting what we should be deleting
    $ mysql -u root -p  -e "show databases" | grep -v Database | grep -v mysql | grep -v information_schema | awk '{print "drop database " $1 ";select sleep(0.1);"}'
    # now delete them
    $ mysql -u root -p  -e "show databases" | grep -v Database | grep -v mysql | grep -v information_schema | awk '{print "drop database " $1 ";select sleep(0.1);"}' | mysql -uroot -ppassword
    
  4. Stop MySQL again
  5. Add the following to the [mysqld] section in /etc/my.cnf:
    [mysqld]
    innodb_file_per_table
    
  6. Remove the files:
    $ sudo rm /usr/local/mysql/data/ibdata1
    $ sudo rm /usr/local/mysql/data/ib_logfile0
    $ sudo rm /usr/local/mysql/data/ib_logfile1
    
  7. Start MySQL again
  8. Reload the databases from the sql dump-file:
    $ mysql -u root -p < alldatabases.sql
    
  9. Verify that your database(s) are working properly
  10. Delete the backup
    $ sudo rm -rf /usr/local/mysql/data.bak
    
  11. Done!

After this modification my TimeMachine backups are much more reasonably-sized. Very nice!

Furthermore, I noticed the same infamously large ibdata1 file on our continuous-integration build server at work: it was 40GB! I applied the same modifications as above. That server runs Ubuntu 10.10, the MySQL files are located in /var/lib/mysql/data, but otherwise the steps are pretty much the same. Even build-server bongo seems snappier now, and the total size of the MySQL data folder is 1GB (insane that there was 39GB of “dead” data in the ibdata1-file…)

Very happy.

AirPrint on Ubuntu

How to print from iPhone/iPad (iOS 4.2) via Ubuntu

I just noticed that with the latest 10.6.5 update, Apple at the last minute disabled support for printing from iOS 4.2 devices to shared printers in OSX. Instead of trying to overwrite new drivers with prerelease versions, I thought that there must be someone, somewhere out there who has figured out how to do this using the fine, free tools available to us. I like open standards, open tools, open source.

From what I understand AirPrint supports printing two ways:

  • Via officially-supported HP ePrint printers that advertise themselves on your subnet
  • Via shared printers from Mac/Windows (this is the part that apparently got axed, at least in the latest 10.6.5 update… don’t know how Windows will fare)

I don’t have a new HP ePrint printer, but I do have a wonderful Ubuntu 10.04 server running avahi (open source Bonjour/mDNS responder)… I wonder if there’s a way to have my server act as an AirPrint device and then send the print job to my networked printer?

Enter this post. Excellent! Set up a Bonjour service, point it to a shared printer, done!

Here’s what I had to do in Ubuntu  to get printing to work, YMMV:

  • Install my printer (networked HP Color LaserJet 1515n) using the graphical configuration utility (System->Administration->Printing). Make sure the printer is shared.
  • Updated my /etc/cups/cupsd.conf configuration to allow network access (default Ubuntu configuration is for localhost only):
    # Only listen for connections from the local machine.
    #Listen localhost:631
    #Listen /var/run/cups/cups.sock
    Port 631
    ServerAlias *
    

    and

    # Restrict access to the server...
    <Location />
    Order allow,deny
    Allow @LOCAL
    </Location>
    
  • Tested remote access to Ubuntu’s cups web GUI from my laptop (to make sure machines other than the Ubuntu server had access to cups): http://172.16.0.50:631/printers/
  • Tested that I could print remotely from my laptop to the new shared printer on Ubuntu (simple “Add Printer” in OSX and then print test page)
  • Configured avahi with a new printer service /etc/avahi/services/printer.service. As mentioned in the original link, rp and adminurl are the most important configuration bits.
    <?xml version="1.0" standalone='no'?><!--*-nxml-*-->
    <!DOCTYPE service-group SYSTEM "avahi-service.dtd">
    <service-group>
      <name>HP 1515n</name>
      <service>
        <type>_ipp._tcp</type>
        <subtype>_universal._sub._ipp._tcp</subtype>
        <port>631</port>
        <txt-record>txtver=1</txt-record>
        <txt-record>qtotal=1</txt-record>
        <txt-record>rp=printers/hp-cp1515n</txt-record>
        <txt-record>ty=HP 1515n</txt-record>
        <txt-record>adminurl=http://172.16.0.50:631/printers/hp-cp1515n</txt-record>
        <txt-record>note=HP Color LaserJet cp1515n</txt-record>
        <txt-record>priority=0</txt-record>
        <txt-record>product=virtual Printer</txt-record>
        <txt-record>printer-state=3</txt-record>
        <txt-record>printer-type=0x801046</txt-record>
        <txt-record>Transparent=T</txt-record>
        <txt-record>Binary=T</txt-record>
        <txt-record>Fax=F</txt-record>
        <txt-record>Color=T</txt-record>
        <txt-record>Duplex=T</txt-record>
        <txt-record>Staple=F</txt-record>
        <txt-record>Copies=T</txt-record>
        <txt-record>Collate=F</txt-record>
        <txt-record>Punch=F</txt-record>
        <txt-record>Bind=F</txt-record>
        <txt-record>Sort=F</txt-record>
        <txt-record>Scan=F</txt-record>
        <txt-record>pdl=application/octet-stream,application/pdf,application/postscript,image/jpeg,image/png,image/urf</txt-record>
        <txt-record>URF=W8,SRGB24,CP1,RS600</txt-record>
      </service>
    </service-group>
    
  • Printed a page from iOS simulator
  • Printed a page from iPhone 3GS with iOS 4.2.1:
  • Done!

I’m not sure if all printers will work out of the box with this configuration, but since my printer supports PostScript I assume it can rasterize pretty much anything iOS will send it. In any case, I didn’t have to configure any filters or print settings. It just worked. Hopefully Apple won’t further cripple AirPrinting by also “patching” iOS so that only HP ePrint devices are supported and it no longer recognizes Bonjour services with subtype _universal._sub._ipp._tcp. We’ll see what happens!

Now all I need is an iPad. And a reason to print.

Updated 2010-11-21: repaired some mangled XML due to WordPress’s less-than-nice HTML-parsing

Updated 2010-11-23: added post subtitle, added screenshot from iPhone