如何在同一台服务器上复制MySQL数据库 [英] How to duplicate a MySQL database on the same server
问题描述
我有一个大的MySQL数据库,让它调用 live_db
,我想在同一台机器上复制提供一个测试系统( test_db
),包括表结构和数据。
在定期间,我想用 live_db
的内容更新 test_db
如果可能增量。
I have a large MySQL database, lets call it live_db
, which I want to replicate on the same machine to provide a test system to play around with (test_db
), including table structure and data.
In regular intervals I want to update the test_db
with the content of the live_db
; if possible incremental.
MySQL中有一些内置的机制吗?我认为主从复制不是我想要的东西,因为应该可以更改 test_db
中的数据。
Is there some built-in mechanism in MySQL to do that? I think that master-slave replication is not the thing I want since it should be possible to alter data in the test_db
. These changes do not have to be preserved, though.
尊敬的,
CGD
推荐答案
mysql
命令行客户端将接受来自标准输入的SQL语句流。因此,您可以在命令行上将 mysqldump
的输出直接转换为 mysql
。这样做作为cron作业将定期用更新的实时数据覆盖您的测试数据:
The mysql
command line client will accept a stream of SQL statements from standard input. You can therefore pipe the output of mysqldump
directly into mysql
on the command line. Doing this as a cron job will regularly overwrite your test data with updated live data:
mysql --user=username --password=passwd -e 'DROP DATABASE test_db;'
mysql --user=username --password=passwd -e 'CREATE DATABASE test_db;'
mysqldump --user=username --password=passwd live_db | mysql --user=username --password=passwd test_db
请注意,由于您的数据很大将需要很长时间。
Note that since your data is large, it will take a long time.
这篇关于如何在同一台服务器上复制MySQL数据库的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!