Not the answer you need?
Register and ask your own question!
Many Forum changes were implemented on Tue 22 Sep. Read about new Ranks, Scoring, and Reactions.
Email [email protected] for any comments or concerns.

Any way to dump out changes made to a few tables?

sql_ersql_er ContributorInactive User Role Beginner
Hi,

I need to dump out a few tables from mysql database. After that I would like to dump out the changes only for those tables. What would be the best way to do it?

The reason for doing this is that the initial data and the subsequent delta would go into a totally different system [i.e. non-relational database], so replication would not be an option I believe.

Please advise.

Thank you!

Comments

  • sterinsterin Mentor Inactive User Role Contributor
    mysqldump with --tables to get the initial dump.

    And for the delta you need to have binary logging running on the source and unfortunately the binlog will give you changes performed on all tables so you will have to filter it through grep or something to only give you the changes to the table that you want.
    Or to avoid the filtering part, if you can live with it, you can instead have a copy of the entire database and then you can configure binlog to only store changes to this database (although there can be a catch here depending on your code, read up on it in the manual).

    Or if this is a continuous ongoing thing you can set up replication where you specify which database that should be replicated to the secondary server.
  • yogesh77yogesh77 Contributor Inactive User Role Beginner
    take full dump of selected tables and setup a replication server only for selected tables to replicate selected updates.
  • revinrevin Contributor Current User Role Beginner
    I would suggest adding a timestamp column t_column (DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP). From this column you can base your deltas i.e. select * from t where t_column > ${last_delta_was_taken}

    Hope this helps.
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.