One of the significant challenges of developing a site locally is the need to sync local database changes to a remote server, often repeatedly. For years, my solution was to manually copy changes from one database to the other, which absorbed a significant amount of time.
Rebranding this site provided the opportunity to review my workflow processes, and database migration was one of the first problems I addressed. After looking at several different apps and approaches, I found an excellent solution that can be run in a single line in the Terminal of Mac OS X.
This solution does make several assumptions:
- You are usingOS X and MAMP as a local server (although it should be relatively simple to move this lesson to another platform and server setup).
- You have some familiarity with the Terminal.
- You are accessing the server using ssh with public and private keys.
- You have access to root or admin accounts on both the local and remote MySQL servers.
In the Terminal, the command is:
/Applications/MAMP/Library/bin/mysqldump -u username -ppassword databasename | ssh user@ipaddress "mysql -u root -ppassword databasename"
A quick explanation:
- the first part is the path to the local
mysqldumputility, which allows you to export a database en masse
- the second part is the username and password for the local MySQL server. Often this will be
root. Note that the password must be prefixed with
-p, with no space after it.
databasenameis exactly that.
The sshpart uses the same syntax as the commands for the local database but with the username and ip address of the server .
- All of this is written in a single line in the Terminal.
This command exports and syncs the entire database from the local to the remote server: obviously, this needs to be approached carefully, and always with backups at hand.
For relatively small databases (up to 10MB – the size of most blogs) and a decent internet connection, the sync process shouldn’t take more than a few seconds to complete; a large database that needed only incremental updates would require a different approach.
The best part is that once run successfully, this command remains in the Terminal history; you only need open the Terminal window and press the up cursor key to run it again.
转载本站任何文章请注明：转载至神刀安全网，谢谢神刀安全网 » Sync Local and Remote MySQL Databases with a Single Command