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; 清空所有日志日志序列重新开始主从复制谨慎使用
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; 清空所有日志日志序列重新开始主从复制谨慎使用