Migrating MySQL DB to External Drive

Problems with the Windows version of XAMPP, questions, comments, and anything related.

Migrating MySQL DB to External Drive

Postby arichardson » 10. July 2017 05:07

Greetings,

I am running Xampp 7.1.6-0 with PHP Admin on a Windows 10 machine, from my C: drive. I am compiling a large MySQL DB using the Xampp Stack; I estimate the final DB to be between 1.2-1.5GB - this is for my dissertation research. My C: is 500GB, and has only about 250GB remaining, and I still need to upload 9 more months of CSVs to the DB. I'm using a php script stored on C: in htdocs to automate this process by unzipping archive zip files I am transferring from my external drive, to htcdocs.

I have a 2TB external hard drive; what would be the best method to move this DB to my external drive, without messing up Xampp installation? (I would like to continue the import process, from the external drive, using xampp and the php script I have on c: drive).

Any feedback is greatly appreciated, as I am a new xampp user - introduced to it from my graduate assistant.

Thanks,

Anthony
arichardson
 
Posts: 4
Joined: 10. July 2017 04:56
XAMPP version: 7.1.6-0
Operating System: Windows 10

Re: Migrating MySQL DB to External Drive

Postby Nobbie » 10. July 2017 10:21

arichardson wrote:I would like to continue the import process, from the external drive, using xampp and the php script I have on c: drive


Edit your PHP Importscript and apply the external drive as input instead of htdocs - what else?

P.S.: Your questions are somewhat confusing, as you also ask for "what would be the best method to move this DB to my external drive", that is a completely different approach as "I would like to continue the import process, from the external drive"?! Would you like to import your FILES FROM the external drive, or do you want to import your files TO the DB on the external drive?
Nobbie
 
Posts: 9615
Joined: 09. March 2008 13:04

Re: Migrating MySQL DB to External Drive

Postby arichardson » 10. July 2017 14:24

Thanks for your reply Nobbie. My apologies for the confusion, let me clarify.

1) The Xampp stack, with PHP Admin and MySQL Instance live on my C: directory; this MySQL instance has grown to 151 GB.

2) My 2TB external drive (D:) is currently serving as storage device, to house my zipped CSV files. I have been moving the zipped files to the C: Xampp/htdocs directory, so that they could be executed by the PHP script and imported into the MySQL instance on the c: directory.

I did not foresee the data set to be this large, but it is an IoT related dataset and I now must change directions on where the MySQL instance lives.

I now want the 151 GB MySQL instance to "live" on the D: drive, where I can then resume the import process on a device that can hold all the data. Does that make sense?

Thanks,

Anthony
arichardson
 
Posts: 4
Joined: 10. July 2017 04:56
XAMPP version: 7.1.6-0
Operating System: Windows 10

Re: Migrating MySQL DB to External Drive

Postby Nobbie » 10. July 2017 15:38

arichardson wrote:I now want the 151 GB MySQL instance to "live" on the D: drive,


So you dont want to import the ZIP file from the drive D:, instead you want to store the MySQL Database (to which the data is imported from the ZIP files) onto that drive D:.

Therefore shutdown MySQL, copy the whole data folder (c:/xampp/mysql/data) to the drive D: (for example to D:/data) edit my.ini (in c:/xampp/mysql/bin), look for datadir entry /(i.e. datadir = "/xampp/mysql/data") and change the value to "D:/data". Then restart MySQL. Do not run MySQL as a service (see the Xampp Control, leave the checkbox empty in front of the Services Apache, MySQL etc.).
Nobbie
 
Posts: 9615
Joined: 09. March 2008 13:04

Re: Migrating MySQL DB to External Drive

Postby arichardson » 10. July 2017 18:12

Thank you again for your quick reply Nobbie.

Yes, I still want to import the many zip files I have on the D: drive TO the MySQL instance that will move to the D: drive (per your last instructions). To answer your original question "do you want to import your files TO the DB on the external drive?"

Yes, I want to import the CSVs which are native to the D: drive, into a MySQL instance on the D: drive - this MySQL instance currently lives on the C: in the Xampp folder as described earlier (the PHP script is also on the C: drive).
arichardson
 
Posts: 4
Joined: 10. July 2017 04:56
XAMPP version: 7.1.6-0
Operating System: Windows 10

Re: Migrating MySQL DB to External Drive

Postby arichardson » 14. July 2017 18:12

Thanks again Nobbie for your instructions; they worked like a charm! I appreciate your help.
arichardson
 
Posts: 4
Joined: 10. July 2017 04:56
XAMPP version: 7.1.6-0
Operating System: Windows 10


Return to XAMPP for Windows

Who is online

Users browsing this forum: No registered users and 40 guests