merging data?

[Ralph Smith]

I have two identical databases on two different servers and I need to add the data in tables from
one server to the tables in the other server. Is there a way to do that in mysql?

thanks,
Ralph

[Aggro]

Post by Ralph Smith
I have two identical databases on two different servers and I need to add the data in tables from
one server to the tables in the other server. Is there a way to do that in mysql?

In realtime or do you want to do it just one time?

If only one time, you could for example use "mysqldump > filename.sql"
to dump the data from other database and then use "mysql < filename.sql"
to import it to the other (You need to add username and other parameters
to those commands also.).

If you are same unique id values in both databases, you need to fix that
also. How to fix it depends again on what you want to do when identical
id values are found. Keep old, overwrite or insert with new id.

If you want to do it in real time, I suggest that you red the MySQL
manual. If I remember correctly there are some ways to do it, but I'm
not familiar with them.

[Ralph Smith]

Post by Aggro

Post by Ralph Smith
I have two identical databases on two different servers and I need to add the data in tables from
one server to the tables in the other server. Is there a way to do that in mysql?

In realtime or do you want to do it just one time?
If only one time, you could for example use "mysqldump > filename.sql"
to dump the data from other database and then use "mysql < filename.sql"
to import it to the other (You need to add username and other parameters
to those commands also.).
If you are same unique id values in both databases, you need to fix that
also. How to fix it depends again on what you want to do when identical
id values are found. Keep old, overwrite or insert with new id.
If you want to do it in real time, I suggest that you red the MySQL
manual. If I remember correctly there are some ways to do it, but I'm
not familiar with them.

I thought about mysqldump but that would overwrite the data in one of the databases. I really need
to append data in tables from one database into identical tables of a database on a different
server. Or maybe there is some way to synchronize 2 databses? I don't need to do it in real time,
just occasionally.

thanks,
Ralph

[Aggro]

Post by Ralph Smith
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:


mysql> create database a;
Query OK, 1 row affected (0.01 sec)

mysql> use a
mysql> create table table1(id int unsigned auto_increment primary key,
name text);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into table1(name) values('test1'),('test2'),('test3');
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0

mysql> create database b;
Query OK, 1 row affected (0.00 sec)

mysql> use b
Database changed
mysql> create table table1(id int unsigned auto_increment primary key,
name text);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into table1(name) values('Jack'),('Jill'),('Bill');
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0

mysql> select * from a.table1;
+----+-------+
| id | name |
+----+-------+
| 1 | test1 |
| 2 | test2 |
| 3 | test3 |
+----+-------+
3 rows in set (0.00 sec)

mysql> select * from b.table1;
+----+------+
| id | name |
+----+------+
| 1 | Jack |
| 2 | Jill |
| 3 | Bill |
+----+------+
3 rows in set (0.00 sec)

mysql> insert into a.table1(name) select name from b.table1;
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0

mysql> select * from a.table1;
+----+-------+
| id | name |
+----+-------+
| 1 | test1 |
| 2 | test2 |
| 3 | test3 |
| 4 | Jack |
| 5 | Jill |
| 6 | Bill |
+----+-------+
6 rows in set (0.00 sec)