Wednesday, July 27, 2011

Convert YouTube Videos to MP3

I often find songs on YouTube that I can't obtain elsewhere, especially obscure remixes of popular songs. While it is possible to listen to them directly on YouTube this gets tiresome quickly since you can't add the songs into a GrooveShark or iTunes playlist or play them on your iPod.
I've used several services online that will convert a YouTube video to MP3 format and allow you to download it. These services are covered in banner ads, encode MP3s in low quality and frequently don't work at all.
While browsing, I discovered a free program called "YouTube to MP3", it's completely free and allows you to extract only the audio from a YouTube video. This program allows you to select the bitrate, and format. You select the YouTube video you want to download by either clicking the paste from clipboard button or manually typing the URL in. Another nice feature this program has is downloading several songs at the same time.

The program runs on Mac and Windows and can be downloaded from here:

Friday, July 8, 2011

Backup a MySQL Database Nightly

I run several websites, which use MySQL, and I've discovered the importance of backing up my databases regularly. I've had tables become corrupt, and hard drives go without warning. It's important to have a backup plan so you can restore your website quickly if something goes wrong.

The easiest way to backup a MySQL database is to use the mysqldump program, which is included with MySQL server. The mysqldump program allows you to dump a database or table to an SQL file which can be quickly restored using the MySQL command line client. You can ensure your backup is executed on a regular basis using the crontab.

Here is a sample shell script for backing up a MySQL database:


DATE=`date +%Y-%m-%d`
cd /PATH_TO_BACKUP/db_backup
mkdir $DATE
/usr/local/mysql/bin/mysqldump -u root DATABASENAME > /PATH_TO_BACKUP/db_backup/$DATE/DATABASENAME.sql

The first line stores the current date as a string of the form YYYY-MM-DD, the next two lines change to the directory where you want the backup to be stored and creates a folder named the current date. The next line creates the actual backup using mysqldump, using the root@localhost account. The final line compresses the backup using the gzip utility, this is optional, but it is a good idea if you have a large database.

You can add this script to your crontab and have it execute daily by first typing:

sudo crontab -e

Then adding the following line into your crontab:

1 0 * * * sh /PATH_TO_BACKUP_SCRIPT/

This will execute the script at one minute past midnight every night, but you can easily change this to suit your needs.

We can easily restore from our backup in a few easy steps. First open Terminal and navigate to the folder where our backup is stored. Then type:

gunzip -c DATABASENAME.sql.gz | mysql -u root DATABASENAME

This will decompress our backup, recreate the table structure and import all the data that was stored in the tables. Make sure you have created the database in MySQL prior attempting to restore.