Web Hosting Forums

Results 1 to 3 of 3

This is a discussion on How do I edit MySQL database locally? in the Hosting Talk & Chit-chat forum
I have a mysql dump of a database I need to do a lot of editing work on. It's a dump of my blog, and ...

  1. #1
    Aletia Customer
    Join Date
    Oct 2001
    Posts
    611

    How do I edit MySQL database locally?

    I have a mysql dump of a database I need to do a lot of editing work on. It's a dump of my blog, and every post needs to be edited. I need to add titles, check spelling, and create new posts in between the posts that are already there. I hate to do this in phpMyAdmin, as it is very time consuming. I'd rather upload the finished database all at once. Is there a program that I can download and make all the changes in locally? I have edited a database with MS Access, and I think you can edit MySQL with Access, but I'm not sure how to do it. Is there an similar program that will let me edit MySQL easily on my local machine?

  2. #2
    Community Leader jason's Avatar
    Join Date
    Sep 2001
    Location
    Rochester, NY
    Posts
    5,884
    You can import MySQL tables into Access and edit them that way. I've done it before, but I haven't done much editing this way. I'm not sure if you'll run into any problems from different or unsupported data types on either platform or whatnot.

    First, you'll need to have your tables on a MySQL server. You can do this either by installing a MySQL server on your local machine and importing your dump files or by linking to your JPC server (you'll need to add your local machine to the access hosts list in CPanel, there are other threads on how to do this if you aren't familiar with it). AFAICT, you won't be able to import the MySQL dump files directly into Access (although you could maybe dump them from MySQL in CSV format and import those files). Whatever you choose, it is probably best to work with a backup or non-live version of your data, or have a backup ready that you can quickly reimport in case you corrupt your live data.

    To import tables from a MySQL server you'll need the MySQL connector for ODBC. Once you have that installed, go to Start > Settings > Control Panel > (Administrative Tools >) Data Sources (ODBC), click on System DSN, and then click the Add button. Pick a unique name for the connection, then fill in the rest of the info as you would in a script on your site (if you are connecting to your JPC server, use your domain name as the MySQL host). Leave the port, "command on connect" and options as they are (probably blank). Next, open a new or existing Access database. Go to File > Get External Data > Link Tables and select "ODBC Databases" from "Files of Type:." Click on Machine Data Source and then select the entry you just created in the ODBC setup. Finally, select the table(s) you'd like to link to. After a few seconds they'll appear as tables in your Access DB, denoted by a globe icon (to show they are linked to an outside source). You can now edit these tables and your changes will be reflected in the MySQL DB.

    If you wnat to import a CSV file into Access, open the database and select File > Get External Data > Import and change "files of type" to "Text Files" an d select your CSV file. Repeat this for each table you wnat to import.

    Hope this helps.

    --Jason
    Jason Pitoniak
    Interbrite Communications
    www.interbrite.com www.kodiakskorner.com

  3. #3
    Aletia Customer
    Join Date
    Oct 2001
    Posts
    611
    Thanks for the detailed information. I guess I got nervous, because I didn't wait long for an answer before I just re-installed my application and started over. I'm manually re-entering my posts and making my edits as I go along. When it's complete, I'll replace the existing database with the new one.

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •