使用单个命令同步本地和远程MySQL数据库

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.

重新命名该站点提供了查看我的工作流程的机会，而数据库迁移是我解决的第一个问题。 在研究了几种不同的应用程序和方法之后，我发现了一个出色的解决方案，可以在Mac OS X终端中的一行中运行。

This solution does make several assumptions:

此解决方案确实有几个假设：

1. You are using OS X and MAMP as a local server (although it should be relatively simple to move this lesson to another platform and server setup).

   您正在将OS X和MAMP用作本地服务器(尽管将本课程转移到另一个平台和服务器设置应该相对简单)。

2. You have some familiarity with the Terminal.
   您对终端机有些了解。
3. You are accessing the server using ssh with public and private keys.
   您正在使用带有公钥和私钥的ssh访问服务器。
4. You have access to root or admin accounts on both the local and remote MySQL servers.
   您可以访问本地和远程MySQL服务器上的root或admin帐户。
5. You keep backups of your work.
   您保留工作的备份。

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 mysqldump utility, which allows you to export a database en masse

  第一部分是本地mysqldump实用程序的路径，它使您可以批量导出数据库

• the second part is the username and password for the local MySQL server. Often this will be root and root. Note that the password must be prefixed with -p, with no space after it.

  第二部分是本地 MySQL服务器的用户名和密码。 通常这将是root和root 。 请注意，密码必须以-p为前缀，后面没有空格。

• the databasename is exactly that.

  databasename就是那个。

• The ssh part uses the same syntax as the commands for the local database but with the username and ip address of the server.

  The ssh部分使用与本地数据库命令相同的语法，但使用服务器的用户名和ip地址。

• All of this is written in a single line in the Terminal.
  所有这些都在终端的一行中编写。

注意 (Note)

This command exports and syncs the entire database from the local to the remote server, dropping the tables on the remote server and rebuilding them with the records from the local copy: 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 (or a more cautious developer) would require a different approach.

对于相对较小的数据库(最多10MB-大多数博客的大小)和良好的Internet连接，同步过程只需花费几秒钟即可完成； 一个仅需要增量更新的大型数据库(或更加谨慎的开发人员)将需要不同的方法。

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.

最好的部分是，一旦成功运行，此命令将保留在终端历史记录中。 您只需要打开“终端”窗口，然后按向上光标键即可再次运行它。

翻译自: https://thenewcode.com/636/Sync-Local-and-Remote-MySQL-Databases-with-a-Single-Command