mysql主庫清理資料，從庫保留

轉身淚傾城發表於2016-05-13
MySql
因為業務需要，想在mysql主庫清理一些資料，但從庫想要保留，根據網友介紹，可以根據binlog跳過清理的命令 

1.確保主從同步的情況下，主庫開始操作 
mysql> flush logs;                    –重新整理日誌，切換一個新的binlog日誌，比較小，後面修改就會方便些 
Query OK, 0 rows affected (0.21 sec) 

mysql> show master status G 
*************************** 1. row *************************** 
             File: mysql-bin.000039    –這裡的binlog位置後面不會用到 
         Position: 33958 
     Binlog_Do_DB: 
Binlog_Ignore_DB: 
Executed_Gtid_Set: 
1 row in set (0.03 sec) 


2.從庫停止同步    —第1.2步儘量要快速操作 
mysql> stop slave; 
Query OK, 0 rows affected (0.05 sec) 

mysql> show slave status G 
*************************** 1. row *************************** 
               Slave_IO_State: 
                  Master_Host: 192.168.1.196 
                  Master_User: repli 
                  Master_Port: 3306 
                Connect_Retry: 60 
              Master_Log_File: mysql-bin.000039 
          Read_Master_Log_Pos: 488906 
               Relay_Log_File: mysql-relay-bin.000016 
                Relay_Log_Pos: 489119 
        Relay_Master_Log_File: mysql-bin.000039 
             Slave_IO_Running: No 
            Slave_SQL_Running: No 

3.主庫清空資料 

mysql> truncate table t2;   步驟a 
Query OK, 0 rows affected (0.46 sec) 

mysql> show master status g  步驟b 
+——————+———-+————–+——————+——————-+ 
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | 
+——————+———-+————–+——————+——————-+ 
| mysql-bin.000039 |  1942762 |              |                  |                   | 
+——————+———-+————–+——————+——————-+ 
1 row in set (0.06 sec) 

/usr/local/mysql/bin/mysqlbinlog mysql-bin.000039 >000039.sql   步驟c 

4.編輯000039.sql，刪掉truncate語句    
truncate table t2 /*!*/;   —刪掉的內容 

查詢1942762  找不到該位置的話，可能會丟失資料，因此前面的三步abc一定要注意順序！最好sql裡面是包含這個位置資訊的，然後刪掉後面的內容，避免後面日誌應用的時候，重複操作。 

end_log_pos 1942762   將這個位置後面的內容全部幹掉！ 


5.從庫恢復 
/usr/local/mysql/bin/mysql -umydba -p 
source /root/000039.sql 
mysql> change master to master_log_file=`mysql-bin.000039`,master_log_pos=1942762; 
Query OK, 0 rows affected (0.19 sec) 

mysql> start slave; 
Query OK, 0 rows affected (0.01 sec) 

mysql> show slave status G 
*************************** 1. row *************************** 
               Slave_IO_State: Waiting for master to send event 
                  Master_Host: 192.168.1.196 
                  Master_User: repli 
                  Master_Port: 3306 
                Connect_Retry: 60 
              Master_Log_File: mysql-bin.000039 
          Read_Master_Log_Pos: 10009221 
               Relay_Log_File: mysql-relay-bin.000002 
                Relay_Log_Pos: 8066779 
        Relay_Master_Log_File: mysql-bin.000039 
             Slave_IO_Running: Yes 
            Slave_SQL_Running: Yes 
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0 
                   Last_Error: 
                 Skip_Counter: 0 
          Exec_Master_Log_Pos: 10009221 
              Relay_Log_Space: 8066986 
              Until_Condition: None 
               Until_Log_File: 
                Until_Log_Pos: 0 
           Master_SSL_Allowed: No 
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 0 
Master_SSL_Verify_Server_Cert: No 
                Last_IO_Errno: 0 
                Last_IO_Error: 
               Last_SQL_Errno: 0 
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 1 
                  Master_UUID: 701cbadc-ba33-11e5-9091-305a3a78baf2 
             Master_Info_File: /usr/local/mysql/data/master.info 
                    SQL_Delay: 0 
          SQL_Remaining_Delay: NULL 
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates 
           Master_Retry_Count: 86400 
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0 
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
1 row in set (0.00 sec) 

—主從同步，跳過了truncate這個命令 

6.驗證 
主庫： 
mysql> select * from t2; 
Empty set (0.00 sec) 


從庫： 
mysql> select * from t2; 
+—-+ 
| id | 
+—-+ 
|  1 | 
|  2 | 
|  3 | 
|  4 | 
|  5 | 
+—-+ 
5 rows in set (0.00 sec)

相關文章