MySql is by default installing everything in C:. As you need more space for C: or MySql you can move the whole database or only a few large files on a HDD with more free space. I choose to move only a few files because I intend to move some files on a removable external HDD, that can be accessed only when the external HDD is present and use MySql as it is without external HDD.
Step 1. Find what files are huge and need to be moved
2 of the files have more than 10 GB
The location of the tables on your disk can be found in the registry or in MySql Benchmark at {Management}{Server Status} as described in [1]
2. Save your database using MySql Benchmark {Management}{Data Export}{… Folder} in order to have individual access to tables in the dumped folder (change the dump folder on the empty HDD, also)
3. Rename actual table names with old at the end (we will truncate / delete them at the end if the new tables are OK)
ALTER TABLE `searchengineresults` RENAME TO `searchengineresults_old` ;
4. Update the create table sql file created at dumping your database, by including the DATA DIRECTORY parameter as described in [2]
CREATE TABLE `searchengineresults` ….. DATA DIRECTORY=’d:/mysql/tables’;
(you have to edit huge 7 GB text files – this a challenge – let me find a simple way
I am now editing a 1 Gb file with TextPad)
For files larger than 1 GB use the TableSpace solution from [2]
5. Run the updated sql from MySql Workbench
6. Wait for the script completion (table creation and row import)
Check how the new table is growing in size during the script
Operation succeeded – see image
7. Deal with very large tables with a different method – classic method – why id did not work
Rename old table
Create new table using
Insert old data into new table with the instruction
insert into searchenginedetails
select * from searchenginedetails_old
It did not work because I run it from MySql Workbench who gave the warning “Timeout error at connection with mysqld server”
Then I did not receive the server from MySql Server and the resulted table was corrupted
select count(*) from searchenginedetails –> resulted in null or zero
8.
9
10
11.
12. Deal with very large tables with a different method (use copy TABLE SPACE method) – see [2]
(if you find empty spaces in the article it means that I am writing it now – just comment what you need from me right now 🙂 )
These are the files and tables of brianai.com – Artificial Intelligence for 200.000 personal images, all local. Try the app at BrianAI on our Exquisite View shop
Biography
[1] Move all database
[2] Move only one table
