MySQL Database data correction

MySQL Database data correction

http://blog.itpub.net/22664653/viewspace-717175/

The need for data revisions is often encountered during the course of work, and the operation itself is not difficult. Operation requires the ability to keep rolling back ~ For the requirements that can meet the rollback, I usually execute the backup table and then execute the change! If a correction error is sent or the user requests a rollback, the entire table is completely restored, so there is extra action, as long as we can actually choose to back up the data to be corrected! For example, the following requirements: The table resource usage_type= ' unused ' and user_id=166 records updated to user_id=169! Update 100 article! 1 First Backup table CREATE TABLE test.resource_20120221 as SELECT * from resource where usage_type= ' unused ' and user_id=166 order by ID Limit 100;2 performs a data correction Operation Update resource set user_id=169 where user_id=166 and ID in (select ID from test.resource_20120221); If rollback is required, simply perform update resource set user_id=166 where ID in (select ID from test.resource_20120221); This involves the correction of small batches of data, if a large number of data, it is necessary to use the batch submission Method! Attached: DDL change techniques for M-M architecture L M-M Replication schema, only one provides all or master data Service recommended DDL statements, in the current m-m schema belongs to the standby database server priority execution, there are four advantages: 1 does not immediately affect the data services provided by the database; 2 A log recovery operation that does not block the replication of data between the primary and standby; 3 DDL statement Execution process, if the problem only affects the stability of the repository, and rarely affect the main library, 4 if the data service seamless switching function (such as: heartbeat, autonomous data layer, etc.), after the completion of the library execution, It can reduce the impact to the business and reduce the maintenance time and frequency of the main repository switch, especially when the data capacity is large.

MySQL Database data correction