22 May 2009

Creating a MySQL Database on an External Drive - Things I will forget [002]

The harddrive on my laptop is filling up and I have a client who's database runs into the gigs (per dataset). So I created a MySQL database on an external harddrive a while ago, but now need to do it again (the harddrive is flaky) and have forgotten how to do it. I seem to be looking in the wrong places, so maybe if I write this blog other people searching for the same thing will find it and be able to use it. I will be able to do it next time without searching too!

Well, its actually quite easy. I first tried to create a separate configuration file like I did the previous occasion, but this means not being able to store the config file on the disk: mysql requires that the config file not be world writeable, but by default the external hardrive is mounted with all files world read/writeable. So rather than mess about with the fstab and change the way it is mounted, I opted for specifying the options on the command line.

So this is what I had to do:

First I created I directory where the MySQL database would reside, all further commands are run from that directory.

I then created a data directory store the databases:

mkdir data

I the ran mysql_install_db to initialize the MySQL database tables:
mysql_install_db --ldata=data

Then I could start the database (remember to shutdown your other mysql database if it is running, else specify a different port for this databse to run on):

mysqld --pid-file=./mysqld.pid --socket=./mysqld.sock --datadir=./data

I put this into a 'start.sh' script.

The socket file will be created in the data directory (data/mysqld.sock.
You can then connect to the database like this:

mysql --user=root --socket=./data/mysqld.sock

If you want access to the MYSQL tables use --user=root, else create your own users etc. If you want to use the MySWL Administrator and MySQL Query Browser graphical front ends, make sure you specify the socket file:

Shutdown the database using the following command:

sudo mysqladmin shutdown -S ./data/mysqld.sock

It bothers me that I have to shut it down using sudo, I'll look for a work around when it starts to really irritate me.

Now to wait for the 8 Gig of data to load.

On a side note: I don't think Acid Mothers Temple is the best band to listen to while programming, my brother might disagree..

 <<Blog as memory>>


Anonymous said...

Thank you very much Brian ... I've been working for days trying to figure out how to mount my external so MySQL would write to it .. this works!

jack said...

can you explain about running "mysql_install_db"
from where ? command prompt or with mysql command prompt


Brian Silberbauer said...

Hi Jack, I ran it from the command line - http://dev.mysql.com/doc/refman/5.0/en/mysql-install-db.html

raphe said...

I may be having the same issue as Jack: when I enter the mysql_install_db command, I get:

-bash: mysql_install_db: command not found

on osx.

I think I've installed mysql correctly (5.5.2). ie, I can get into the 'normal' mysql prompt.