We help IT Professionals succeed at work.

edit mysql DB on local machine

686 Views
Last Modified: 2012-05-11
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!
Comment
Watch Question

Top Expert 2012

Commented:
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.

Top Expert 2012

Commented:
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.
Dave BaldwinFixer of Problems
CERTIFIED EXPERT
Most Valuable Expert 2014

Commented:
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.
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

Author

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!
Top Expert 2012

Commented:
If you have at least mysql client on your windows machine my instructions should work from the command line
Top Expert 2012

Commented:
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
Dave BaldwinFixer of Problems
CERTIFIED EXPERT
Most Valuable Expert 2014

Commented:
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.

Author

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?

Author

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.
Top Expert 2012
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Top Expert 2012

Commented:
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.
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.