• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 648
  • Last Modified:

edit mysql DB on local machine

Is it possible to download my mysql DB from my webserver and is there any free programs i can use to run querries on the local copy of the DB and then upload the new DB to my webserver - what is a free local program that will allow me to do this easily?

thanks!
0
Thanx
Asked:
Thanx
  • 6
  • 3
  • 2
  • +1
1 Solution
 
johanntagleCommented:
Just use mysqldump.  Assuming you have the needed privileges:

mysqldump --add-drop-table -u username -p databasename -h hostname > dumpfile

Then import it via

mysql -u username -p databasename < dumpfilename

Once you make your changes, do the same import and export procedure, but this exporting from your local.

Warning - you will be overwriting your whole db this way so be careful. The import and export will also take some time depending on db size and connection speed.

0
 
johanntagleCommented:
If you will also just edit a few tables you can export only those to. Just look at the syntax options of mysqldump. I'm currently mobile so can't look it up myself.
0
 
Dave BaldwinFixer of ProblemsCommented:
If you are familiar with phpMyAdmin, the easiest way would be to install WAMP http://www.wampserver.com/en/ or XAMPP http://www.apachefriends.org/en/xampp.html which includes MySQL along with a web server and PHP with phpmyAdmin.  Then you could import/export your data easily.
0
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
vaibhavjaimanCommented:
Your can use the heidiSQL  for the same,
1)Connect to your mysql db through the provided credentials
2)Export it under your local account
3)Import the .sql under your local MySQLserver
4)Done

0
 
ThanxAuthor Commented:
i know it sounds wird but I have a windows xp machine and you all seam to be referring to linux or apache - I want to download the mysql Db file to my local win xp machine and then edit it in there with some type of software - but i dont know if this is possible - sorry for the confusion!
0
 
johanntagleCommented:
If you have at least mysql client on your windows machine my instructions should work from the command line
0
 
johanntagleCommented:
You may need to specify full path to the mysql client binaries though as it is rarely in the path environment variable when you start the command prompt
0
 
Dave BaldwinFixer of ProblemsCommented:
No, WAMP and XAMPP are for Windows although there are also versions for other OSs.  I have one of each installed on Windows XP machines.  They are probably the easiest way to do this because they install all the software and configure it to come up running.  Installing MySQL by itself would be more difficult.  Though I supposed you could use a text editor to edit your MySQL dumps, I don't recommend it.  You will have enough to learn as it is.
0
 
ThanxAuthor Commented:
well heres what im trying to do -

I have my own hosted VPS and the mysql DB is too large (4.5 million records) and is making my app work slow on search querries  - so i need to remove about a million records but in myphpadmin it seams i can only delete them - i want to remove them to another db file for storage so i can restore them when i could afford a bigger VPS with better resources!

Any ideas how to remove and not delete records in myphadmin?
0
 
ThanxAuthor Commented:
Another thing i wanted to mention is that i need to export the records based on a query where statement because i do not wish to remove records from a certain categeory in my mysql DB.
0
 
johanntagleCommented:
For moving records, how about in phpmyadmin, you do this:

Click on the query tab, then submit the following query:

create table new_table as select * from source_table where <put your filter clause here>

e.g.

create table new_table as select * from source_table where id between 1 and 1000000;

then you delete it from the source table via:

delete from source_table where id between 1 and 1000000;

Then export new_table using phpmyadmin (save as file, enable compression)

once you have it safely backed up and imported to your local db, drop the new_table from your server
0
 
johanntagleCommented:
Btw, 4.5M records is not really that big.  Suggest you determine where your app is really slowing down.  What do you use as search query?  Maybe you can put it as a different question.  Give the structure of the tables concerned, and your search query.
0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

  • 6
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now