Syncing MySQL Database Between A Mac And PC

I’ve been a long time Mac hater. Recently, though, I broke down and bought a Mac laptop.

Being a geek on many levels, I immediately had to set it up as a development server environment. I won’t go into the full story, but suffice it to say, that after dabbling with MAMP, I decided to go back to the discrete installs. I mean, most of it is done already, anyway, and then you don’t have to deal with paying for MAMP Pro to get around starting your server on port 80. If you know what I mean, then I’ll assume you’ve been there.

Although I originally wanted to put the database itself under revision control, talking to the guys in the IT department (and my boss), and doing research on the internet, I decided to go with something like How to Sync A Local & Remote WordPress Blog Using Version Control.

Okay, the general layout:

  • Windows 7 PC development environment on desktop PC running MySQL – Master
  • Mac OS X – Mavericks development environment on laptop also running MySQL – Slave
  • Git – both systems

The concept in a nutshell:

  • PC – Run script that dumps your database to a folder that is under Git control
  • PC – Script then adds and commits changes
  • Mac – Run script that PULLS over the changes
  • Mac – Script restores database

This post assumes you have access and control of your systems, such that you can write, execute, and read where necessary. I will gloss over that stuff, if I touch it at all. Also, this is not a secure method. Use at your own risk. There are ways to get around the password in plain text issue, but that (among other things) is beyond the scope of this post.

PC script:

mysqldump -u USER -pPASSWORD DBNAME > "C:\Program Files (x86)\Apache Software Foundation\Apache2.2\htdocs\dBExchange\DBNAME.sql"
"C:\Program Files (x86)\Git\cmd\git" add "C:\Program Files (x86)\Apache Software Foundation\Apache2.2\htdocs\dBExchange\DBNAME.sql"
git commit "C:\Program Files (x86)\Apache Software Foundation\Apache2.2\htdocs\dBExchange\DBNAME.sql" -m "auto commit"

Let’s dissect this:
mysqldump -u USER -pPASSWORD DBNAME > "C:\Program Files (x86)\Apache Software Foundation\Apache2.2\htdocs\dBExchange\DBNAME.sql"

  • mysqldump: command for dumping your database
  • -u USER: the user that is running this command–root for instance
  • -pPASSWORD: user password (preceeded with p for password)
  • DBNAME: name of the database that you want to dump
  • >: Your sending the output to a file
  • “C:\Program Files (x86)\Apache Software Foundation\Apache2.2\htdocs\dBExchange\DBNAME.sql”: output file location and name (in quotes)

"C:\Program Files (x86)\Git\cmd\git" add "C:\Program Files (x86)\Apache Software Foundation\Apache2.2\htdocs\dBExchange\DBNAME.sql"

  • “C:\Program Files (x86)\Git\cmd\git”: location of git (just in case it’s not in your environment)
  • add: command to add a file to the repository
  • “C:\Program Files (x86)\Apache Software Foundation\Apache2.2\htdocs\dBExchange\DBNAME.sql”: Location of file that’s being added. Should be the same as the location and file that you just dumped

git commit "C:\Program Files (x86)\Apache Software Foundation\Apache2.2\htdocs\dBExchange\DBNAME.sql" -m "auto commit"

  • git: I didn’t put the pathway to git here, so I must have it in my environment. If you don’t, specify the full pathway, like I did in the last code snippet
  • commit: command to commit a file to the repository (put the file into the repo)
  • “C:\Program Files (x86)\Apache Software Foundation\Apache2.2\htdocs\dBExchange\DBNAME.sql”: location and name of what you want to commit
  • -m: message (to follow)
  • “auto commit”: the message I chose to add–all commits by this script will have this message

I saved the script in the dbExchange folder as export.bat (which makes it executable). I then sent a link to the desktop. That’s all there is to the PC script.

Mac script:

#!/bin/bash
cd /Library/WebServer/Documents/dBExchange
git pull origin
mysql -u USER -pPASSWORD DBNAME < /Library/WebServer/Documents/dBExchange/DBNAME.sql

Dissection reveals:
#!/bin/bash

  • This lets your system know you’re running BASH script (script to be interpreted by BASH)
  • This may be the system default, but it won’t hurt to put it in there

cd /Library/WebServer/Documents/dBExchange

  • Change to git (folder) directory

git pull origin

  • git: call to git. It’s in my environment, so no need to use pathway. Your settings may vary
  • pull origin: Pull from remote (get data from master repo)

mysql -u USER -pPASSWORD DBNAME < /Library/WebServer/Documents/dBExchange/DBNAME.sql

  • mysql: Same idea as PC script, but instead of mysqldump you use mysql
  • <: Notice the director is in the opposite direction–less than as opposed to greater than
  • /Library/WebServer/Documents/dBExchange/DBNAME.sql: pathway and name of file to reload the database with
  • Note: This will completely overwrite this database. Be sure it’s what you want to do!

I saved the script in the dbExchange folder as import.command (which makes it executable). I then made an alias to the command file and copied it to the desktop. That’s all there is to the Mac script.

Use: Double-click on the Windows desktop shortcut. Go to your Mac and make sure you are connected via network to the folder where the SQL file was dumped. Double-click on the alias file on the Mac desktop. It’s done! Note: The different processes, Git, mysqldump, mysql, may take time to finish. How much can vary and depends on database size, your computers, network speed, etc. Either way, be patient, it’ll be over before you know it!

=)

This entry was posted in Computer Stuff, Mac, MySQL and tagged , , , . Bookmark the permalink.

Leave a Reply

Your email address will not be published.

Enter Captcha Here : *

Reload Image