I Want To Keep Mysql Db In Pendrive
Posted 03 May 2012 - 02:24 PM
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 ?
Posted 06 May 2012 - 01:35 PM
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)!
Posted 09 May 2012 - 02:50 PM
G:\MySQL\bin\mysqladmin port=3320 -u root -p shutdown
2 user(s) are reading this topic
0 members, 2 guests, 0 anonymous users