Jump to content


Photo

I Want To Keep Mysql Db In Pendrive


  • Please log in to reply
2 replies to this topic

#1 sanko

sanko

    Advanced Member

  • Members
  • PipPipPip
  • 52 posts

Posted 03 May 2012 - 02:24 PM

Hi,

I want to keep mysql database in a pendrive. Do you have any solution ?

Here is my use cases ...

application in Box1 ----will be able to access data from pendrive DB when plugged into usb

application in Box2 ----will be able to access data from pendrive DB when plugged in usb

application in Box3 ----will be able to access data from pendrive DB when plugged in usb

There is no LAN and Boxes are not connected to each other.

I wish for a portable database. do you have a solution ?

#2 peterlaursen

peterlaursen

    Advanced Member

  • Admin
  • PipPipPip
  • 7,869 posts
  • Gender:Male
  • Location:Skagen, Denmark
  • Interests:well ... jazz/folk music, photography, chess, nature, ecology, history, bicycling, Highland Malts ... well, Lowland Malts and Cognac too actually :-) just wonder how I get the time to touch a computer! SQLyog and MONyog? no that's not interest, that's BASIC NEEDS simply!

Posted 06 May 2012 - 01:35 PM

Well it is possible. First: do not try to run MySQL as a service on the boxes involved (I actually tried years back and with the Windows built-in USB-driver it did not work - I did manage to get it working with a 3rd party mounting tool though. But that was on WinXP and such mounting tools will not work on Vista and higher). Run MySQL from the pendrive where also the data are. Download the MySQL server as a .zip package (from here for instance: ftp://ftp.easynet.be/mysql/Downloads/MySQL-5.5/mysql-5.5.23-win32.zip). Here and in the following I will assume that the boxes run Windows (but if it is Linux you just need the .tar.gz package instead and use Linux file system syntax in the command shell)


Don't install anything but just unzip the the .zip package you have downloaded, create a "\MySQL" folder on the pendrive and copy files from the archive here. Next step is to create a my.ini file. You may rename one of the 'templates' ('my-medium.ini' for instance) that ships with the archive. my.ini should be in same folder as the templates. Note that if MySQL is installed on any machine where you use this, you must define a port in my.ini not conflicting with any of the installed MySQL instances on either of the machines. I defined port 3320 for this server instance as this port is not used by any installed MySQL instance.


Now launch the MySQL server from command line with the 'mysqld' command. If the drive letter is G: it would look like

C:\Users\Peter>G:
G:\>cd \MySQL\bin
G:\MySQL\bin>mysqld

Now the server starts and I can connect with SQLyog (or any client) with details: host=localhost, user=root password=<empty>, port=3320 (you should change the passsword when logged in at the first time of course. You may also create more users).

Note that the pendrive may need to be reformatted. NTFS (on Windows) is strongly recommended and most drives are FAT(small ones) or FAT32 (larger ones) formatted when delivered. In this example I did this on a 8GB FAT32-formatted USB2-stick (but I am almost sure that FAT will not work). But files on FAT32 are restricted to 4GB and performance on NTFS will probably be better that on FAT32 (and in general I do not guarantee performance - I experienced reasonable performance for SELECT, but not for INSERT, UPDATE and DDL-statements. But on the other hand not considerable slower than what you will often experience when working with a remote server connected over the Internet).

Like this you will have both the server program and the data on the pendrive. The data are stored in G:\MySQL\Data in my case. But note that if I plug in the drive in to another computer (or even on same computer another time) the driveletter may be another than G:

If you have a database somewhere that you want to transfer you can use SQLyog, connect to both servers and 'copy database to other host' (or you may dump+import the database of course).



Now there is one question I cannot reply to (I have tried to figure out before)! Maybe some other user can or a colleague of mine can? When starting a mysql server from Windows command line like described here, how do I stop the server ('gracefully' I mean - not killing the server process)? If I close the command-line window where the server was started, the server just keeps on running. So I have to kill the server process before unplugging the drive. This can actually cause corrupted tables if the server is in the middle of writing something. At least before killing the process be sure that the server does not have pending operations (wait a few seconds)!
Computers make your grey hair come off ....

Peter Laursen
Webyog

#3 peterlaursen

peterlaursen

    Advanced Member

  • Admin
  • PipPipPip
  • 7,869 posts
  • Gender:Male
  • Location:Skagen, Denmark
  • Interests:well ... jazz/folk music, photography, chess, nature, ecology, history, bicycling, Highland Malts ... well, Lowland Malts and Cognac too actually :-) just wonder how I get the time to touch a computer! SQLyog and MONyog? no that's not interest, that's BASIC NEEDS simply!

Posted 09 May 2012 - 02:50 PM

I figured out how to stop the server gracefully when started from command line. Use the 'mysqladmin' client like (using the parameters from discussion):

G:\MySQL\bin\mysqladmin port=3320 -u root -p shutdown

Computers make your grey hair come off ....

Peter Laursen
Webyog




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users