Merging deltas from one table to another?

OK, a Drupal site I deal with has two copies... a production site and a
test site. After new changes are developed, they're put on the test
site. Once the test site is deemed to be OK, that entire site and
database will be copied over to the production site.

The issue is, while that work is taking place, changes are still
happening on the production site, like new user registrations and some
form input. What I'm thinking is, we can dump the affected tables and
then import them into the test site, which started life as a mirror copy
of the production site. If I were to:

mysqldump -h db_server -h user -pPASSWORD database table_1 table_2 >
/tmp/db.sql

On the production side, and then:

mysql -h test_db_server -h user -pPASSWORD database < /tmp/db.sql

Will that result in what I want? Let's say they both sytart off with
three users, and after the production site is copied to test, a fourth
user is added on the production site. Will the above commands give me a
test site with the same four users, or will they actually do something
other than what I want, like duplicate the pre-existing users? Is there
any particular special command to merge only the deltas?

Thanks...

--
************************************************************ ***********
* John Oliver http://www.john-oliver.net/ *
* *
************************************************************ ***********

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Merging deltas from one table to another?

On Mon, 8 Mar 2010 14:14:09 -0800 John Oliver said:

> OK, a Drupal site I deal with has two copies... a production site and a
> test site. After new changes are developed, they're put on the test
> site. Once the test site is deemed to be OK, that entire site and
> database will be copied over to the production site.
>
> The issue is, while that work is taking place, changes are still
> happening on the production site, like new user registrations and some
> form input. What I'm thinking is, we can dump the affected tables and
> then import them into the test site, which started life as a mirror copy
> of the production site. If I were to:
>
> mysqldump -h db_server -h user -pPASSWORD database table_1 table_2 >
> /tmp/db.sql

Get the table layout on the production box:
mysqldump --add-drop-table ...
>
> On the production side, and then:
>
> mysql -h test_db_server -h user -pPASSWORD database < /tmp/db.sql
....

--
Don Read don_read@att.net
It's always darkest before the dawn. So if you are going to
steal the neighbor's newspaper, that's the time to do it.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Merging deltas from one table to another?

--00504502af92cc9a8d048180b88c
Content-Type: text/plain; charset=ISO-8859-1

I think mysqldump does not provide this option.
You can extract the data into a file and use "load data infile 'file_name
replace into table_name"
or
replace into table_name select * from old_table_name;
For the above to work, there need to be primary key or unique index. If
primary key or unique index is not present, it would just insert the data.

Extrating data into a file will take lot of time if the data set is too
huge.

regards
anandkl



On Thu, Mar 11, 2010 at 3:32 AM, Don Read wrote:

> On Mon, 8 Mar 2010 14:14:09 -0800 John Oliver said:
>
> > OK, a Drupal site I deal with has two copies... a production site and a
> > test site. After new changes are developed, they're put on the test
> > site. Once the test site is deemed to be OK, that entire site and
> > database will be copied over to the production site.
> >
> > The issue is, while that work is taking place, changes are still
> > happening on the production site, like new user registrations and some
> > form input. What I'm thinking is, we can dump the affected tables and
> > then import them into the test site, which started life as a mirror copy
> > of the production site. If I were to:
> >
> > mysqldump -h db_server -h user -pPASSWORD database table_1 table_2 >
> > /tmp/db.sql
>
> Get the table layout on the production box:
> mysqldump --add-drop-table ...
> >
> > On the production side, and then:
> >
> > mysql -h test_db_server -h user -pPASSWORD database < /tmp/db.sql
> ...
>
> --
> Don Read don_read@att.net
> It's always darkest before the dawn. So if you are going to
> steal the neighbor's newspaper, that's the time to do it.
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=anandkl@gmail.com
>
>

--00504502af92cc9a8d048180b88c--

RE: Merging deltas from one table to another?

Not sure if this well help, but SQLYog (the best mysql GUI EVER) has an
option that will take two databases and create a schema difference (ALTER
statements, etc.) as well as I think data maybe?

> -----Original Message-----
> From: Ananda Kumar [mailto:anandkl@gmail.com]
> Sent: Wednesday, March 10, 2010 10:43 PM
> To: Don Read
> Cc: John Oliver; mysql@lists.mysql.com
> Subject: Re: Merging deltas from one table to another?
>
> I think mysqldump does not provide this option.
> You can extract the data into a file and use "load data
> infile 'file_name
> replace into table_name"
> or
> replace into table_name select * from old_table_name;
> For the above to work, there need to be primary key or unique
> index. If
> primary key or unique index is not present, it would just
> insert the data.
>
> Extrating data into a file will take lot of time if the data
> set is too
> huge.
>
> regards
> anandkl
>
>
>
> On Thu, Mar 11, 2010 at 3:32 AM, Don Read wrote:
>
> > On Mon, 8 Mar 2010 14:14:09 -0800 John Oliver said:
> >
> > > OK, a Drupal site I deal with has two copies... a
> production site and a
> > > test site. After new changes are developed, they're put
> on the test
> > > site. Once the test site is deemed to be OK, that entire site and
> > > database will be copied over to the production site.
> > >
> > > The issue is, while that work is taking place, changes are still
> > > happening on the production site, like new user
> registrations and some
> > > form input. What I'm thinking is, we can dump the
> affected tables and
> > > then import them into the test site, which started life
> as a mirror copy
> > > of the production site. If I were to:
> > >
> > > mysqldump -h db_server -h user -pPASSWORD database
> table_1 table_2 >
> > > /tmp/db.sql
> >
> > Get the table layout on the production box:
> > mysqldump --add-drop-table ...
> > >
> > > On the production side, and then:
> > >
> > > mysql -h test_db_server -h user -pPASSWORD database < /tmp/db.sql
> > ...
> >
> > --
> > Don Read don_read@att.net
> > It's always darkest before the dawn. So if you are going to
> > steal the neighbor's newspaper, that's the time to do it.
> >
> > --
> > MySQL General Mailing List
> > For list archives: http://lists.mysql.com/mysql
> > To unsubscribe:
> http://lists.mysql.com/mysql?unsub=anandkl@gmail.com
> >
> >
>


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org