Mysql Binlog文件过期周期设置

Mysql Binlog文件过期周期设置

问题描述：随着mysql在业务中使用且并发量越来越大，这时会发现服务器的磁盘空间逐渐变小，你也许会认为是数据量变大所导致的，其实部分原因也在于mysql的默认配置。比如：binlog这项配置，这是属于一个mysql自带的数据备份的配置，如果非业务中的需要可以将此配置修改为备份一天的数据即可。
以下内容是修改配置：

# 因为mysql存储数据的权限默认是root用户，因此需要切换权限
1.sudo su
# 此处是存放binlog文件的地方
2.cd /var/lib/mysql
# 查看binlog文件，没修改配置之前可以看到很多个binlog文件
3.root@VM-16-5-ubuntu:/var/lib/mysql#  ls
 binlog.002165	 binlog.002186	 binlog.002207	 binlog.002228	 binlog.002249	 binlog.002270	 binlog.002291	 
 binlog.002145	 binlog.002166	 binlog.002187	 binlog.002208	 binlog.002229	 binlog.002250	 binlog.002271	 binlog.002292	    
 binlog.002146	 binlog.002167	 binlog.002188	 binlog.002209	 binlog.002230	 binlog.002251	 binlog.002272	 binlog.002293	   
 binlog.002147	 binlog.002168	 binlog.002189	 binlog.002210	 binlog.002231	 binlog.002252	 binlog.002273	 binlog.002294	    
 binlog.002148	 binlog.002169	 binlog.002190	 binlog.002211	 binlog.002232	 binlog.002253	 binlog.002274	 binlog.002295	   
 binlog.002149	 binlog.002170	 binlog.002191	 binlog.002212	 binlog.002233	 binlog.002254	 binlog.002275	 binlog.002296	    
 binlog.002150	 binlog.002171	 binlog.002192	 binlog.002213	 binlog.002234	 binlog.002255	 binlog.002276	 binlog.002297	     
 binlog.002151	 binlog.002172	 binlog.002193	 binlog.002214	 binlog.002235	 binlog.002256	 binlog.002277	 binlog.002298	      
 binlog.002152	 binlog.002173	 binlog.002194	 binlog.002215	 binlog.002236	 binlog.002257	 binlog.002278	 binlog.002299	      
 binlog.002153	 binlog.002174	 binlog.002195	 binlog.002216	 binlog.002237	 binlog.002258	 binlog.002279	 	     
 binlog.002154	 binlog.002175	 binlog.002196	 binlog.002217	 binlog.002238	 binlog.002259	 binlog.002280	 	     
 binlog.002155	 binlog.002176	 binlog.002197	 binlog.002218	 binlog.002239	 binlog.002260	 binlog.002281	 
 binlog.002156	 binlog.002177	 binlog.002198	 binlog.002219	 binlog.002240	 binlog.002261	 binlog.002282	 
 binlog.002157	 binlog.002178	 binlog.002199	 binlog.002220	 binlog.002241	 binlog.002262	 binlog.002283	 
 binlog.002158	 binlog.002179	 binlog.002200	 binlog.002221	 binlog.002242	 binlog.002263	 binlog.002284	 
 binlog.002159	 binlog.002180	 binlog.002201	 binlog.002222	 binlog.002243	 binlog.002264	 binlog.002285	 
 binlog.002160	 binlog.002181	 binlog.002202	 binlog.002223	 binlog.002244	 binlog.002265	 binlog.002286	
 binlog.002161	 binlog.002182	 binlog.002203	 binlog.002224	 binlog.002245	 binlog.002266	 binlog.002287	
 binlog.002162	 binlog.002183	 binlog.002204	 binlog.002225	 binlog.002246	 binlog.002267	 binlog.002288	
 binlog.002163	 binlog.002184	 binlog.002205	 binlog.002226	 binlog.002247	 binlog.002268	 binlog.002289	
 binlog.002164	 binlog.002185	 binlog.002206	 binlog.002227	 binlog.002248	 binlog.002269	 binlog.002290	
# 进入mysql
4.mysql -u用户名 -p密码
# 查看binlog默认的设置
5.show variables like '%expire%';
+--------------------------------+---------+
| Variable_name                  | Value   |
+--------------------------------+---------+
| binlog_expire_logs_seconds     | 2592000 |
| disconnect_on_expired_password | ON      |
| expire_logs_days               | 0       |
+--------------------------------+---------+
# 修改binlog配置（设为24小时）
6.set global binlog_expire_logs_seconds=60*60*24;
# 再次查看修改后的配置
7.show variables like '%expire%';
+--------------------------------+-------+
| Variable_name                  | Value |
+--------------------------------+-------+
| binlog_expire_logs_seconds     | 86400 |
| disconnect_on_expired_password | ON    |
| expire_logs_days               | 0     |
+--------------------------------+-------+
# 更新配置
8.flush logs;
# 退出mysql
9.\q
# 查看发现binlog文件减少
10.root@VM-16-5-ubuntu:/var/lib/mysql# ls
 binlog.002272	 binlog.002281	 binlog.002290	 binlog.002299	 binlog.002300
 binlog.002264	 binlog.002273	 binlog.002282	 binlog.002291	 binlog.002301 
 binlog.002265	 binlog.002274	 binlog.002283	 binlog.002292	 	  
 binlog.002266	 binlog.002275	 binlog.002284	 binlog.002293	 
 binlog.002267	 binlog.002276	 binlog.002285	 binlog.002294	 
 binlog.002268	 binlog.002277	 binlog.002286	 binlog.002295	 
 binlog.002269	 binlog.002278	 binlog.002287	 binlog.002296	 
 binlog.002270	 binlog.002279	 binlog.002288	 binlog.002297	 
 binlog.002271	 binlog.002280	 binlog.002289	 binlog.002298	 

按照以上操作就完成对mysql binlog文件的配置，如需设置为长期有效，需要去mysql中的conf文件去修改