Migrating MySQL DB to External Drive

[arichardson]

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

[Nobbie]

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?

[arichardson]

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

[Nobbie]

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

[arichardson]

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]

Thanks again Nobbie for your instructions; they worked like a charm! I appreciate your help.