dongmu5920
2017-09-14 13:34
浏览 45
已采纳

Mysql,数据库/服务器之间的数据迁移(现在迁移,稍后定期更新)

This is somewhat of an abstract question but hopefully pretty simple at the same time. I just have no idea the best way to go about this except for an export/import and I can't do that due to permission issues. So i need some alternatives.

On one server, we'll call it 1.2.3 I have a database with 2 schemas, Rdb and test. These schemas have 27 and 3 tables respectively. This database stores call info from our phone system but we have reader access only so we're very limited in what we can do beyond selecting and joining for data records and info.

I then have a production database server, call it 3.2.1 With my main schemas and I'd like to place the previous 30 tables into one of these production schemas. After the migration is done, I'll need to create a script that will check the data on the first connection and then update the new schema on the production connection, but that's after the bulk migration is done.

I'm wondering if a php script would be the way to go about this initial migration, though. I'm using MySQL workbench and the export wizard fails for the read only database, but if there's another way in the interface then I don't know about it.

It's quite a bit of data, and I'm not necessarily looking for the fastest way but the easiest and most fail safe way.

图片转代码服务由CSDN问答提供 功能建议

这是一个抽象的问题,但希望同时非常简单。 我只是不知道除了导出/导入之外最好的解决方法,由于权限问题我无法做到这一点。 所以我需要一些替代方案。

在一台服务器上,我们称之为 1.2.3 我有一个包含2个模式的数据库, Rdb test 。 这些模式分别有27个和3个表。 这个数据库存储来自我们电话系统的呼叫信息,但我们只有读者访问权限,因此除了选择和加入数据记录和信息之外,我们所做的事情非常有限。

然后我有 一个生产数据库服务器,称之为 3.2.1 使用我的主模式,我想将之前的30个表放入其中一个生产模式中。 迁移完成后,我需要创建一个脚本来检查第一个连接上的数据,然后更新生产连接上的新模式,但这是在批量迁移完成之后。 \ n

我想知道是否有一个PHP脚本是这种初始迁移的方式。 我正在使用MySQL工作台,并且导出向导对于只读数据库失败,但如果界面中有另一种方式,那么我不知道它。

这是相当多的 数据,我不一定寻找最快的方式,但最简单,最安全的方法。

  • 写回答
  • 好问题 提建议
  • 追加酬金
  • 关注问题
  • 收藏
  • 邀请回答

1条回答 默认 最新

  • duanguan5922 2017-09-14 15:06
    已采纳

    For a one time data move, the easiest way is to use the command line tool mysqldump to dump your tables to file, then load the resulting file with mysql. This assumes that you are either shutting down 1.2.3, or will reconfigure your phone system to point to 3.2.1 (or update DNS appropriately). Also, this is much easier if you can get downtime on the phone system to move the data.

    we have reader access only so we're very limited in what we can do beyond selecting and joining for data records

    This really limits your options.

    • Master/Slave replication requires REPLICATION SLAVE privilege, which you probably need a user with SUPER privilege to create a replication user.
    • Trigger based replication solutions like SymetricDS will require a user with CREATE ROUTINE in order to create the triggers
    • An "Extract, Transform, Load" solution like Clover ETL will work best if tables have LAST_CHANGED timestamps. If they don't, then you would need ALTER TABLE privilege.

    Different tools for different goals.

    • Master/Slave replication is generally used for Disaster Recovery, Availability or Read Scaling
    • Hetergenous Replication to replicate some (or all) tables between different environments (could be different RDBMS, or different replica sets) in a continuous, but asynchronous fashion.
    • ETL for bulk, hourly/daily/periodic data movements, with the ability to pick a subset of columns, aggregate, convert timestamp formats, merge with multiple sources, and generally fix whatever you need to with the data.

    That should help you determine really what your situation is - whether it's a one time load with a temporary data sync, or if it's an on-going replication (real-time, or delayed).

    Edit: https://www.percona.com/doc/percona-toolkit/LATEST/index.html Check out the Persona Toolkit. Specifically pt-table-sync and pt-table-checksum. They will help with this.

    评论
    解决 无用
    打赏 举报

相关推荐 更多相似问题