Move large MySql tables to a different HDD

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
mysql-sort-tables
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
run-sql-script
6. Wait for the script completion (table creation and row import)
Check how the new table is growing in size during the script
mysql-growing file
Operation succeeded – see image
mysql-operation-ok
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

How to sell your images

Here are some sites where Brian AI can help you organize and upload images, for sale. See the examples below with images uploaded by me using Brian AI

dreamstime_portofolioDreamstime

getty_images_istock_portofolio-800Getty Images and iStock

picfair_portofolioPicFair

adobe_fotolia_portofolio-800Adobe & Fotolia

123rf-800123RF

See upload and organize instructions on User Manual of Brian AI. Try the application for free for 30 days !

Enjoy and a lot of sales !

Dorin

How to get from Heatrow Airport to Hotels

20180822_084457
I had a difficult time in London so here are the tricks
– for Airport Hotels (Hoppa Bus – 5 GBP per person at the driver, or 9 GBP 2 ways, valid 2 month)
search for your terminal and hotel here (there are a lot of pages)
https://www.heathrow.com/file_source/Heathrow/Static/PDF/Transport_and_directions/Hotel_Hoppa_timetable.pdf
search for the bus station location on the last page on this link (timetable also included)
https://www.nationalexpress.com/media/1325/heathrow_hotel_hoppa_timetable.pdf

indide_20180822_095305

– for London Central Hotels get the train
See all options on this link
https://www.heathrow.com/transport-and-directions

20180822_084012

If you love pictures, then you should try our Artificial Intelligence Organizer and more, on this link brianai.com

Enjoy your stay in London !

Bitnami