I have two mysql databases on two machines. Let's say first is production db and the second one is identical clone. My php app uses production db for default. But I need to have both dbs identical at the same time. It means I need solution for cases when production db is unavailable (for example connection error) so I just manually set second one and my app runs as usual. I would like to make it "failure resistant".
我在两台机器上有两个mysql数据库。假设首先是生产数据库，第二个是相同的克隆。我的php应用程序使用生产数据库默认。但我需要同时让两个dbs相同。这意味着我需要解决生产数据库不可用时的情况（例如连接错误），所以我只需手动设置第二个，我的应用程序照常运行。我想让它“失败”。
How should I do this? I think of making dump every minute but it is not a good solution when db is complex with many data...
我该怎么做？我想每分钟都进行一次转储，但是当db与许多数据复杂时，这不是一个好的解决方案......
1 个解决方案
#1
1
These answers assume that standard replication is not an option for you, for whatever reason:
这些答案假设标准复制不适合您，无论出于何种原因：
The following options are existing methods for manual data-sync that are well known, and would be good when combined when wrapped into a scripting language like bash etc. for a cronjob as needed with some logic to specify specific tables as needed, guarantee it is safe to run them in light of load, etc. on a production box.
以下选项是现有的手动数据同步方法，这些方法是众所周知的，当组合成bash等脚本语言时，如果需要，可以根据需要使用某些逻辑来指定特定的表，保证它是合适的。根据生产箱上的负载等安全运行它们。
Option 1: pt-table-sync
选项1：pt-table-sync
The pt-table-sync tool from the Percona MySQL toolkit allows for master-master, master-slave sync on demand in an existing replication scheme. Or you can use it to sync two servers that do not have any relationship.
Percona MySQL工具包中的pt-table-sync工具允许在现有复制方案中按需进行主 - 主，主 - 从同步。或者您可以使用它来同步两个没有任何关系的服务器。
来自Percona的文件
Following the example, for one way sync'ing.
以下示例，单向同步。
pt-table-sync --execute h=sourcehost1,u=msandbox,p=msandbox h=desthost d=yourdb t=yourtables
pt-table-sync --execute h = sourcehost1，u = msandbox，p = msandbox h = desthost d = yourdb t = yourtables
Additionally the following features exist:
此外，还存在以下功能：
- Dry Run Mode (--dry-run) - Program will connect, plan the sync, analyze conflicts and tell you how it would resolve the sync. This is key to making sure you use this powerful tool the right way.
- 空运行模式（--dry-run） - 程序将连接，计划同步，分析冲突并告诉您如何解决同步问题。这是确保以正确方式使用此强大工具的关键。
- Conflict analysis - see how the data compare - feed this back into your script to catch potential issues, or don't perform the sync to save time if there isn't a difference.
- 冲突分析 - 查看数据如何比较 - 将其反馈到脚本中以捕获潜在问题，或者如果没有差异则不执行同步以节省时间。
As I understand, a master-slave relationship need not exist necessarily - but the sync is more efficient if it does exist since more efficient checksum algorithms can be used for comparing the data.
据我所知，主从关系不一定存在 - 但如果确实存在，则同步更有效，因为可以使用更有效的校验和算法来比较数据。
Option 2: Hot/Streaming Backups with XtraDb Alternatively, you could use something like the free Percona XtraBackup in it's host streaming mode to keep a backup file in sync, and restore to your dev box as needed.
选项2：使用XtraDb进行热/流备份或者，您可以在其主机流模式下使用免费的Percona XtraBackup之类的东西来保持备份文件同步，并根据需要恢复到您的开发框。
XtraBackup makes MySQL hot backups for all versions of Percona Server, MySQL, MariaDB, and Drizzle. It performs streaming, compressed, and incremental MySQL backups.
XtraBackup为所有版本的Percona Server，MySQL，MariaDB和Drizzle提供MySQL热备份。它执行流式，压缩和增量MySQL备份。
Option C: LVM Snapshots LVM snapshots are probably not the best option for a production box if you need to take them at any sort of frequency due to the brief locking/IO issues, but nonetheless here you go: MySQL Backups Using LVM Snapshots
选项C：LVM快照如果由于短暂的锁定/ IO问题需要以任何频率获取LVM快照，LVM快照可能不是生产盒的最佳选择，但是在这里你可以：使用LVM快照进行MySQL备份
All of these tools move data either one way, or bi-directionally - as such a thorough understanding of how to employ them is critical to avoid data loss.
所有这些工具都以单向或双向方式移动数据 - 因为彻底了解如何使用它们对于避免数据丢失至关重要。
#1
1
These answers assume that standard replication is not an option for you, for whatever reason:
这些答案假设标准复制不适合您，无论出于何种原因：
The following options are existing methods for manual data-sync that are well known, and would be good when combined when wrapped into a scripting language like bash etc. for a cronjob as needed with some logic to specify specific tables as needed, guarantee it is safe to run them in light of load, etc. on a production box.
以下选项是现有的手动数据同步方法，这些方法是众所周知的，当组合成bash等脚本语言时，如果需要，可以根据需要使用某些逻辑来指定特定的表，保证它是合适的。根据生产箱上的负载等安全运行它们。
Option 1: pt-table-sync
选项1：pt-table-sync
The pt-table-sync tool from the Percona MySQL toolkit allows for master-master, master-slave sync on demand in an existing replication scheme. Or you can use it to sync two servers that do not have any relationship.
Percona MySQL工具包中的pt-table-sync工具允许在现有复制方案中按需进行主 - 主，主 - 从同步。或者您可以使用它来同步两个没有任何关系的服务器。
来自Percona的文件
Following the example, for one way sync'ing.
以下示例，单向同步。
pt-table-sync --execute h=sourcehost1,u=msandbox,p=msandbox h=desthost d=yourdb t=yourtables
pt-table-sync --execute h = sourcehost1，u = msandbox，p = msandbox h = desthost d = yourdb t = yourtables
Additionally the following features exist:
此外，还存在以下功能：
- Dry Run Mode (--dry-run) - Program will connect, plan the sync, analyze conflicts and tell you how it would resolve the sync. This is key to making sure you use this powerful tool the right way.
- 空运行模式（--dry-run） - 程序将连接，计划同步，分析冲突并告诉您如何解决同步问题。这是确保以正确方式使用此强大工具的关键。
- Conflict analysis - see how the data compare - feed this back into your script to catch potential issues, or don't perform the sync to save time if there isn't a difference.
- 冲突分析 - 查看数据如何比较 - 将其反馈到脚本中以捕获潜在问题，或者如果没有差异则不执行同步以节省时间。
As I understand, a master-slave relationship need not exist necessarily - but the sync is more efficient if it does exist since more efficient checksum algorithms can be used for comparing the data.
据我所知，主从关系不一定存在 - 但如果确实存在，则同步更有效，因为可以使用更有效的校验和算法来比较数据。
Option 2: Hot/Streaming Backups with XtraDb Alternatively, you could use something like the free Percona XtraBackup in it's host streaming mode to keep a backup file in sync, and restore to your dev box as needed.
选项2：使用XtraDb进行热/流备份或者，您可以在其主机流模式下使用免费的Percona XtraBackup之类的东西来保持备份文件同步，并根据需要恢复到您的开发框。
XtraBackup makes MySQL hot backups for all versions of Percona Server, MySQL, MariaDB, and Drizzle. It performs streaming, compressed, and incremental MySQL backups.
XtraBackup为所有版本的Percona Server，MySQL，MariaDB和Drizzle提供MySQL热备份。它执行流式，压缩和增量MySQL备份。
Option C: LVM Snapshots LVM snapshots are probably not the best option for a production box if you need to take them at any sort of frequency due to the brief locking/IO issues, but nonetheless here you go: MySQL Backups Using LVM Snapshots
选项C：LVM快照如果由于短暂的锁定/ IO问题需要以任何频率获取LVM快照，LVM快照可能不是生产盒的最佳选择，但是在这里你可以：使用LVM快照进行MySQL备份
All of these tools move data either one way, or bi-directionally - as such a thorough understanding of how to employ them is critical to avoid data loss.
所有这些工具都以单向或双向方式移动数据 - 因为彻底了解如何使用它们对于避免数据丢失至关重要。