mysql复制环境清理二进制日志

淡淡的烟草味﹌ 2022-08-21 02:20 198阅读 0赞

1.查看slave上正在读取的日志  
mysql> show slave status \\G;  
\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 1. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*  
               Slave\_IO\_State: Waiting for master to send event  
                  Master\_Host: 192.168.1.100  
                  Master\_User: replication  
                  Master\_Port: 3306  
                Connect\_Retry: 60  
              Master\_Log\_File: master-bin.000007  
          Read\_Master\_Log\_Pos: 19406147  
               Relay\_Log\_File: slave-relay-bin.000004  
                Relay\_Log\_Pos: 19406311  
        Relay\_Master\_Log\_File: master-bin.000007  
             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: 19406147  
              Relay\_Log\_Space: 34404863  
              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: caa6d2e9-a550-11e5-8385-0800276ce4c8  
             Master\_Info\_File: /var/lib/mysql/master.info  
                    SQL\_Delay: 0  
          SQL\_Remaining\_Delay: NULL  
      Slave\_SQL\_Running\_State: Slave has read all relay log; waiting for the slave I/O thread to update it  
           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  
1 row in set (0.00 sec)  
ERROR:   
No query specified  
  
2.查看主服务器的日志列表  
mysql> show master logs ;  
\+-------------------+-----------+  
| Log\_name          | File\_size |  
\+-------------------+-----------+  
| master-bin.000001 |      1108 |  
| master-bin.000002 |       168 |  
| master-bin.000003 |       168 |  
| master-bin.000004 |       257 |  
| master-bin.000005 | 201612588 |  
| master-bin.000006 |  14998335 |  
| master-bin.000007 |  19436640 |  
\+-------------------+-----------+  
7 rows in set (0.00 sec)  
master正在使用的日志：  
mysql> show master status \\G;  
\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 1. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*  
             File: master-bin.000007  
         Position: 19425086  
     Binlog\_Do\_DB:   
 Binlog\_Ignore\_DB:   
Executed\_Gtid\_Set:   
1 row in set (0.00 sec)  
  
\[root@mysqlmaster mysql\]\# ls  
auto.cnf           master-bin.000005  performance\_schema  
ibdata1            master-bin.000006  RPM\_UPGRADE\_HISTORY  
ib\_logfile0        master-bin.000007  RPM\_UPGRADE\_MARKER-LAST  
ib\_logfile1        master-bin.index   test  
master-bin.000001  mysql              wangwei  
master-bin.000002  mysqlmaster.err    zabbix  
master-bin.000003  mysqlmaster.pid  
master-bin.000004  mysql.sock  
\[root@mysqlslave mysql\]\# ls  
auto.cnf        performance\_schema       slave-bin.000006  
ibdata1         relay-log.info           slave-bin.index  
ib\_logfile0     RPM\_UPGRADE\_HISTORY      slave-relay-bin.000003  
ib\_logfile1     RPM\_UPGRADE\_MARKER-LAST  slave-relay-bin.000004  
master.info     slave-bin.000001         slave-relay-bin.index  
mysql           slave-bin.000002         test  
mysqlslave.err  slave-bin.000003         wangwei  
mysqlslave.pid  slave-bin.000004         zabbix  
mysql.sock      slave-bin.000005  
  
3.确定日志清理  
删除二进制日志的方法：  
PURGE \{MASTER | BINARY\} LOGS TO 'log\_name'  
PURGE \{MASTER | BINARY\} LOGS BEFORE 'date'  
例如删除master-bin.000002之前的日志  
PURGE MASTER LOGS TO 'master-bin.000002';  
日志已经被清理  
mysql> show master logs ;  
\+-------------------+-----------+  
| Log\_name          | File\_size |  
\+-------------------+-----------+  
| master-bin.000002 |       168 |  
| master-bin.000003 |       168 |  
| master-bin.000004 |       257 |  
| master-bin.000005 | 201612588 |  
| master-bin.000006 |  14998335 |  
| master-bin.000007 |  19619864 |  
\+-------------------+-----------+  
6 rows in set (0.00 sec)  
操作系统下日志也被删除  
\[root@mysqlmaster mysql\]\# ls  
auto.cnf           master-bin.000004  mysqlmaster.err          test  
ibdata1            master-bin.000005  mysqlmaster.pid          wangwei  
ib\_logfile0        master-bin.000006  mysql.sock               zabbix  
ib\_logfile1        master-bin.000007  performance\_schema  
master-bin.000002  master-bin.index   RPM\_UPGRADE\_HISTORY  
master-bin.000003  mysql              RPM\_UPGRADE\_MARKER-LAST  
  
如果slave也开启二进制日志，按照同样的方法清理  
可以通过设置参数expire\_logs\_day来清理日志  
set GLOBAL expire\_logs\_days=5;  
但是设置了 不会马上生效 （日志轮换可以触发清理）  
需要执行：  
1.执行 flush logs;  
可以放在配置文件中  
reset master; 清空所有日志日志序列重新开始主从复制谨慎使用