mysql主库清理数据,从库保留

  1. 云栖社区>
  2. 博客>
  3. 正文

mysql主库清理数据,从库保留

转身泪倾城 2016-05-13 10:20:07 浏览1750
展开阅读全文
因为业务需要,想在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)

网友评论

登录后评论
0/500
评论
转身泪倾城
+ 关注