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>>