How to Sync MySQL DB Across 2 PCs on LAN

Posted on 2009-04-22
Last Modified: 2013-12-12

I'm developing dynamic websites running on local PCs under XAMPP (until ready to upload to production server).

PC1 OS:  Win XP MCE v2002 SP3
PC2 OS:  Win XP MCE v2002 SP3
LAN:   MS Windows Network

In general I develop on PC1 and sync the website files across the LAN to PC2.  I use something as simple as SyncToy to sync the website files.  Works fine.

Another user can test the site on PC2 and inform me of required changes.  The user on PC2 might make additions/deletions to the tables in the DB (but the individual DB name and access parameters are duplicated across both machines so that doesn't change)

Not sure where the MySQL database files which pertain to a given site are stored in XAMPP.  Searching in the obvious places doesn't turn them up.  I can 'see' the DBs in localhost/phpMyAdmin but don't know where they are stored on the PC (I know ... sounds really lame).

I can export a given DB, say from PC1 using phpMyAdmin, and import it into PC2 using its phpMyAdmin but this is kludgy and requires me hopping around between PCs.


How can I sync a MySQL DB across two PCs on my LAN?

If I can sync both ways across both PCs great but I'll settle for just being able to conveniently transfer the MySQL DB from PC1 to PC2 without having to go through the phpAdmin export/import dance every time.

There are commercial packages out there pertaining to do this but I'm looking for an open-source (free) solution at the moment.

Thx for your help.

I want a
Question by:qeng
    LVL 6

    Accepted Solution

    There is a free version of Navicat you can use.  Simply install it on your computer then connect to the DB on both computers.  This will allow you to connect/transfer db's to and from both ocmputers without getting up.

    LVL 6

    Assisted Solution

    I should say that its a bit different from PHPMYADMIN but still easy to use.  Just make sure that your computers are not blocking port 3306 if they have any firewalls installed.

    Author Comment

    Thx dianthonym, will give this a try right now.
    For my info, where are MySQL DBs stored in an XAMPP environment.?  It's bugging me not to be able to find them on the HD.
    LVL 6

    Assisted Solution

    I know how you feel...  On my installation of xampp the db files are located at c:\xampp\mysql\data

    As soon as you connect with Navicat you will see the DB's  just make a user on the second computer using phpMyAdmin that has permission to connect from anywhere on your network.  it would look like this username@%  instead of username@localhost.  or at very least make it so that the user can connect only from your primary machine's ip address.  username@192.168.x.x  

    Author Comment

    Am in the process of trying the Navicat approach, but could use your help with the issue of where the heck those MySQL DBs hide.
    The first place I had looked before posting was c:\xampp\mysql\data but as you can see from the attached screen grabs it only contains 2 of the 30 or so DBs presently on my PC.
    If you compare the screen grab which shows the files in c://xampp/mysql/data with the one from http://localhost/phpmyadmin you'll see what I mean.
    Where are those DBs?  I didn't do anything differently when I was creating those sites.  I also do not recall updating XAMPP or doing anything which should/could have cause this.
    Really puzzled.
    Thx for your help by the way.  Much appreciated.


    Author Comment

    Please DO NOT DELETE this question.
    If you'll note, I'm still waiting on a reply from the expert community.  I would like to synchronize some DBs but can't find where they are created/stored on my PC (as ridiculous as that seems).
    LVL 142

    Expert Comment

    by:Guy Hengel [angelIII / a3]
    please check out the mysql's base folder setting, and if there is any innodb tablespace created...

    Author Comment

    I'm not sure what you mean by 'mysql's base folder setting'.
    The DBs do exist since I work with the sites on a regular basis on my PC (localhost) under an XAMPP install.
    I would have thought the DBs would be stored somewhere in the:
    c:/xampp/mysql folder.
    See a screngrab of what that folder contains.  Is that where I should be looking for all of the DBs which pertain to my multiple sites and if so which subfolder because I can't find anything resembling a DB (or the tables) in those subfolders except for one or two of my sites (which makes it even more intriguing ... if there were none I might understand they are elsewhere but why would there be a couple ... where are the 26 or so other DBs???)

    LVL 142

    Assisted Solution

    by:Guy Hengel [angelIII / a3]
    >I'm not sure what you mean by 'mysql's base folder setting'.
    if was referring to the DATADIR setting:
    Typically, DATADIR is /usr/local/mysql/data for a binary installation or /usr/local/var for a source installation. Note that this is the data directory location that was specified at configuration time, not the one specified with the --datadir option when mysqld starts. Use of --datadir at runtime has no effect on where the server looks for option files, because it looks for them before processing any options.

    Author Comment

    Thx angellll,
    Seemed i had to dig a lot to find something quite simple!?
    For my reference:
    The location where the MySQLs are stored is defined in:
    C:\Program Files\MySQL\MySQL Server 5.1\my.ini
    in #SERVER SECTION  under #Path to the database root:
    datadir="C:/Documents and Settings/All Users/Application Data/MySQL/MySQL Server 5.1/Data/"
    That file contains all of the MySQL databases for my sites.
    Not sure why one of those DBs show up in C:\xampp\mysql\data.  Maybe I exported it there at some earlier time.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Suggested Solutions

    I wrote this article to help simplify the process of combining multiple subnets. This can be used for route summarization also but there are other better ways to summarize routes, This article is a result of questions I participate in here at Ex…
    Security is one of the biggest concerns when moving and migrating your data from your on-premise location to the Public Cloud.  Where is your data? Who can access it? Will it be safe from accidental deletion?  All of these questions and more are imp…
    This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.
    The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

    761 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    11 Experts available now in Live!

    Get 1:1 Help Now