Install MySQL on OSX

If you need a local instance of a mysql database for experiments and don’t really care about security as it’s not any production system here’s a quick way to install a mysql database on your OSX (tested with 10.9+)

1. Download the latest OSX native package installation from the
   download page. It should be a dmg
   file. http://dev.mysql.com/downloads/mysql/
2. Install it as any other OSX application. It will create under
   /usr/local/mysql-x.y.z with a symlink to /usr/local/mysql the
   entire directory structure that will be needed by mysql. From now
   on I will refer as /usr/local/mysql directory.
3. sudo rm -r /usr/local/mysql/data
4. sudo mkdir /usr/local/mysql/data
5. sudo chown -R <youruser> /usr/local/mysql/data
6. /usr/local/mysql/scripts/mysql_install_db

If everything worked out fine you should be able to start it with

$ mysqld_safe &

to stop it

$ mysqladmin -u root shutdown

I’ve created a couple of aliases and exports in my ~/.profile to ease the tasks

#MySQL
 export PATH=$PATH:/usr/local/mysql/bin
 alias mysql-start="mysqld_safe &"
 alias mysql-stop="mysqladmin -u root shutdown"

Piping a cyphered gzipped sql file into MySQL

$ gpg -d /path/to/file.sql.gz.gpg | gunzip | mysql -u root mydb

and the other way round

$ mysqldump -u root mydb | gzip | gpg -e -r my@id.com > mydb.sql.gz.gpg

Replace accordingly 🙂

Changing $PATH in Mac OS X

by Stuck in Customs via flickr

I’ve started few days ago using Mac OS X. While I still prefer Linux I appreciate the fact that I have the bash power within and I don’t have to go for cygwin anymore. However cygwin is a great tool!

The first thing I had to do was changing the PATH environment variable in my shell for having the newly installed XAMPP binaries. Here is how: edit your ~/.profile file (create it if not exists) and add/change the line export PATH=$PATH:/path/to/your/bins.

Easy! 🙂

MySQL add 3 years to a date

It may happens if you are silly like me, that you just inserted a lot of records and then realized that you have inserted a mistaken date. In my case the date was a 3 years earlier than the required.

In order to fix it rapidly with mysql you can use the date_add function. Following a quick ad self explaining statement

update <table> set <field>=date_add(<field>,interval 3 year) where id>3289;

Import CSV into MySQL

It happens (often to me) that as an external (consultant) member, for company policies I cannot have even read access to production DB tables.

This will come to be very unproductive when I have to test for the integrity of some records or for the motivation of the behavior of an application.

The solution I’ve found every time is to create on my local MySQL database the same table structure of the production environment and then ask to someone who has access to DB tables to make me an export in order to import it in my tables.

Generally this export come out as CSV file but with semicolon (;) as field separator.

MySQL offer a very cool command for import large amount of data into table: load data statement.

Starting from the point that your user has privileges for doing this operation, the command is like the following

load data local infile ‘path/to/your/file.txt‘ into table your_table fields terminated by ‘;’;