如果两个数据库都具有相同的模式，我可以将两个数据库合并到一个Mysql中吗？ [英] Can I merge two databases into one in Mysql if they both have the same schema?

问题描述

我在MySQL中有两个已经有数据的数据库。它们具有完全相同的模式。我想把这两个数据库合并成一个。我试过：

  mysqldump -u root -p  - 数据库database1 database2 database3> database1_database2_da 
  

但是，当我尝试打开 database1_database2_da ，我只是从数据库中的一个数据库中得到数据，但并不是全部。我也想提到这两个数据库的记录从1开始，因为它们是自动生成的。你认为有没有办法合并这两个数据库，而不会丢失任何数据？

解决方案

在每个数据库上运行 mysqldump > - no-create-info 选项以避免编写架构信息。然后使用 - no-data 选项在一个数据库上运行一次。如果您将所有这些文件顺序加载到同一个目标数据库中，这应该可以工作，除非两个数据库或重复主键之间的模式有任何差异。

  mysqldump -u root -p --no-create-info database1> database1.sql 
 mysqldump -u root -p --no-create-info database2> database2.sql 
 mysqldump -u root -p --no-data database1> schema.sql 
  

创建新数据库后，运行

  mysql -uroot -p -Ddatabase3< schema.sql 
 mysql -uroot -p -Ddatabase3< database1.sql 
 mysql -uroot -p -Ddatabase3< database2.sql 
  

这也可以正常工作。没有Windows框在ATM上测试

 键入schema.sql database1.sql database2.sql | mysql -uroot -p -Ddatabase3 
  

I have two databases in MySQL that already have data. they have exactly the same schema. I would like to merge those two databases into one only. I tried:

mysqldump -u root -p --databases database1 database2 database3 > database1_database2_da

However, when I try to open database1_database2_da, I only end up with data from one of the database, but not all of them. I also want to mention that the two databases have their records starting at 1, since they are automatically generated. Do you think there is a way to merge those two databases without losing data from any of them?

解决方案

Run mysqldump on each database with the --no-create-info option to avoid writing schema information. Then run once on one database with the --no-data option. If you load all of these files sequentially into the same target database, this should work, barring any differences in schema between the two databases or duplicate primary keys.

mysqldump -u root -p --no-create-info database1 > database1.sql
mysqldump -u root -p --no-create-info database2 > database2.sql
mysqldump -u root -p --no-data database1 > schema.sql

After creating a new database, run

mysql -uroot -p -Ddatabase3 < schema.sql
mysql -uroot -p -Ddatabase3 < database1.sql
mysql -uroot -p -Ddatabase3 < database2.sql

This may also work. Don't have a Windows box to test on ATM

type schema.sql database1.sql database2.sql | mysql -uroot -p -Ddatabase3

这篇关于如果两个数据库都具有相同的模式，我可以将两个数据库合并到一个Mysql中吗？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！