[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1403
  • Last Modified:

How to Sync MySQL DB Across 2 PCs on LAN

Background:

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.


Question:

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
0
qeng
Asked:
qeng
  • 5
  • 3
  • 2
4 Solutions
 
dianthonymCommented:
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.  
http://navicat.com

0
 
dianthonymCommented:
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.
0
 
qengAuthor Commented:
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.
0
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
dianthonymCommented:
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  
0
 
qengAuthor Commented:
dianthonym,
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.

c-xampp-mysql-data.jpg
http-localhost-phpmyadmin.jpg
0
 
qengAuthor Commented:
angellll,
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).
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
please check out the mysql's base folder setting, and if there is any innodb tablespace created...
0
 
qengAuthor Commented:
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???)

c-xampp-mysql.jpg
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>I'm not sure what you mean by 'mysql's base folder setting'.
if was referring to the DATADIR setting:
http://dev.mysql.com/doc/refman/5.1/en/option-files.html
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.
0
 
qengAuthor Commented:
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.
0

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

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