Ralph Smith wrote:
I thought about mysqldump but that would overwrite the data in one of the databases.
It doesn't overwrite if you make sure that you dump only the
insert-queries (I think there was some parameter to enable this) or
manually edit the file to contain only insert queries.
Insert queries never overwrite anything. They will either create a row
or fail.
If you are using the same unique index in both databases, the insert
query will fail when it encounters such a query. There are solutions and
workarounds for this also, depending on what should be done when that
happens.
One solution is also to dump the whole database and then create a new
database on the server where the data should be copied. By importing the
data there, you have both of the databases on the same server, which
could make merging more easy (basicly it can be done with a single query
if you are using auto_increment).
Example:
mysqlcreate database a;
Query OK, 1 row affected (0.01 sec)
mysqluse a
mysqlcreate table table1(id int unsigned auto_increment primary key,
name text);
Query OK, 0 rows affected (0.01 sec)
mysqlinsert into table1(name) values('test1'),('test2'),('test3');
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysqlcreate database b;
Query OK, 1 row affected (0.00 sec)
mysqluse b
Database changed
mysqlcreate table table1(id int unsigned auto_increment primary key,
name text);
Query OK, 0 rows affected (0.01 sec)
mysqlinsert into table1(name) values('Jack'),('Jill'),('Bill');
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysqlselect * from a.table1;
+----+-------+
| id | name |
+----+-------+
| 1 | test1 |
| 2 | test2 |
| 3 | test3 |
+----+-------+
3 rows in set (0.00 sec)
mysqlselect * from b.table1;
+----+------+
| id | name |
+----+------+
| 1 | Jack |
| 2 | Jill |
| 3 | Bill |
+----+------+
3 rows in set (0.00 sec)
mysqlinsert into a.table1(name) select name from b.table1;
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysqlselect * from a.table1;
+----+-------+
| id | name |
+----+-------+
| 1 | test1 |
| 2 | test2 |
| 3 | test3 |
| 4 | Jack |
| 5 | Jill |
| 6 | Bill |
+----+-------+
6 rows in set (0.00 sec)
