如何进行MySQL数据库mysql-bin日志的管理与维护

2024-04-02 19:04:59 895人浏览泡泡鱼

摘要

如何进行Mysql数据库mysql-bin日志的管理与维护，相信很多没有经验的人对此束手无策，为此本文总结了问题出现的原因和解决方法，通过这篇文章希望你能解决这个问题。在Mysql中，如果启用mysql-b

如何进行Mysql数据库mysql-bin日志的管理与维护，相信很多没有经验的人对此束手无策，为此本文总结了问题出现的原因和解决方法，通过这篇文章希望你能解决这个问题。

在Mysql中，如果启用mysql-bin日志记录，则Mysql的变更修改都会被记录到日志文件中：

双击代码全选
1 2 3 4 5 6 7 8 9 10 11 12 13	`mysql> show` `global` `variables` `like` `‘%bin%’;` `+———————————+———————-+` `\| Variable_name \| Value \|` `+———————————+———————-+` `\| binlog_cache_size \| 32768 \|` `\| innodb_locks_unsafe_for_binlog \|` `OFF` `\|` `\| log_bin \|` `ON` `\|` `\| log_bin_trust_function_creators \|` `OFF` `\|` `\| max_binlog_cache_size \| 18446744073709547520 \|` `\| max_binlog_size \| 104857600 \|` `\| sync_binlog \| 0 \|` `+———————————+———————-+` `7` `rows` `in` `set` `(0.00 sec)`

　　如果使用了配置文件，则可以修改 /etc/my.cnf 把里面的log-bin这一行注释掉，重启mysql服务即可关闭bin日志的记录。

　　一个客户的数据库，积累了大量的bin日志：

双击代码全选
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246	`mysql> show` `binary` `logs;` `+——————+———–+` `\| Log_name \| File_size \|` `+——————+———–+` `\| mysql-bin.000064 \| 3175623 \|` `\| mysql-bin.000065 \| 104857696 \|` `\| mysql-bin.000066 \| 30124812 \|` `\| mysql-bin.000067 \| 104857882 \|` `\| mysql-bin.000068 \| 104857829 \|` `\| mysql-bin.000069 \| 104857910 \|` `\| mysql-bin.000070 \| 29627738 \|` `\| mysql-bin.000071 \| 104857777 \|` `\| mysql-bin.000072 \| 104857857 \|` `\| mysql-bin.000073 \| 104857728 \|` `\| mysql-bin.000074 \| 104857739 \|` `\| mysql-bin.000075 \| 104857775 \|` `\| mysql-bin.000076 \| 104857686 \|` `\| mysql-bin.000077 \| 104857875 \|` `\| mysql-bin.000078 \| 104857927 \|` `\| mysql-bin.000079 \| 104857827 \|` `\| mysql-bin.000080 \| 104857856 \|` `\| mysql-bin.000081 \| 104857762 \|` `\| mysql-bin.000082 \| 104857797 \|` `\| mysql-bin.000083 \| 104857860 \|` `\| mysql-bin.000084 \| 104857751 \|` `\| mysql-bin.000085 \| 104857925 \|` `\| mysql-bin.000086 \| 104857791 \|` `\| mysql-bin.000087 \| 104857838 \|` `\| mysql-bin.000088 \| 104857749 \|` `\| mysql-bin.000089 \| 104857836 \|` `\| mysql-bin.000090 \| 104857988 \|` `\| mysql-bin.000091 \| 104857807 \|` `\| mysql-bin.000092 \| 104857691 \|` `\| mysql-bin.000093 \| 104857834 \|` `\| mysql-bin.000094 \| 104857647 \|` `\| mysql-bin.000095 \| 104857814 \|` `\| mysql-bin.000096 \| 104857830 \|` `\| mysql-bin.000097 \| 104858049 \|` `\| mysql-bin.000098 \| 104857862 \|` `\| mysql-bin.000099 \| 104858089 \|` `\| mysql-bin.000100 \| 104857760 \|` `\| mysql-bin.000101 \| 104857650 \|` `\| mysql-bin.000102 \| 104857794 \|` `\| mysql-bin.000103 \| 104857656 \|` `\| mysql-bin.000104 \| 104857961 \|` `\| mysql-bin.000105 \| 104857725 \|` `\| mysql-bin.000106 \| 104857804 \|` `\| mysql-bin.000107 \| 104857925 \|` `\| mysql-bin.000108 \| 104857717 \|` `\| mysql-bin.000109 \| 104857678 \|` `\| mysql-bin.000110 \| 104857675 \|` `\| mysql-bin.000111 \| 104857897 \|` `\| mysql-bin.000112 \| 104857947 \|` `\| mysql-bin.000113 \| 104857651 \|` `\| mysql-bin.000114 \| 104858066 \|` `\| mysql-bin.000115 \| 104857766 \|` `\| mysql-bin.000116 \| 104857719 \|` `\| mysql-bin.000117 \| 104857732 \|` `\| mysql-bin.000118 \| 104857733 \|` `\| mysql-bin.000119 \| 104857814 \|` `\| mysql-bin.000120 \| 104857763 \|` `\| mysql-bin.000121 \| 104857680 \|` `\| mysql-bin.000122 \| 104857742 \|` `\| mysql-bin.000123 \| 104858011 \|` `\| mysql-bin.000124 \| 104857708 \|` `\| mysql-bin.000125 \| 104857821 \|` `\| mysql-bin.000126 \| 104857877 \|` `\| mysql-bin.000127 \| 104857735 \|` `\| mysql-bin.000128 \| 104857781 \|` `\| mysql-bin.000129 \| 104857743 \|` `\| mysql-bin.000130 \| 104857858 \|` `\| mysql-bin.000131 \| 104857860 \|` `\| mysql-bin.000132 \| 104857809 \|` `\| mysql-bin.000133 \| 104857711 \|` `\| mysql-bin.000134 \| 104857744 \|` `\| mysql-bin.000135 \| 104857802 \|` `\| mysql-bin.000136 \| 104857672 \|` `\| mysql-bin.000137 \| 104857933 \|` `\| mysql-bin.000138 \| 104857758 \|` `\| mysql-bin.000139 \| 104857657 \|` `\| mysql-bin.000140 \| 104857664 \|` `\| mysql-bin.000141 \| 104857715 \|` `\| mysql-bin.000142 \| 104857881 \|` `\| mysql-bin.000143 \| 104857958 \|` `\| mysql-bin.000144 \| 104857917 \|` `\| mysql-bin.000145 \| 104857771 \|` `\| mysql-bin.000146 \| 104857731 \|` `\| mysql-bin.000147 \| 104857770 \|` `\| mysql-bin.000148 \| 104857864 \|` `\| mysql-bin.000149 \| 104857726 \|` `\| mysql-bin.000150 \| 104857885 \|` `\| mysql-bin.000151 \| 104857817 \|` `\| mysql-bin.000152 \| 104857909 \|` `\| mysql-bin.000153 \| 104857665 \|` `\| mysql-bin.000154 \| 104857647 \|` `\| mysql-bin.000155 \| 104857880 \|` `\| mysql-bin.000156 \| 104858010 \|` `\| mysql-bin.000157 \| 104857821 \|` `\| mysql-bin.000158 \| 104857649 \|` `\| mysql-bin.000159 \| 104857930 \|` `\| mysql-bin.000160 \| 104857856 \|` `\| mysql-bin.000161 \| 104857673 \|` `\| mysql-bin.000162 \| 104857969 \|` `\| mysql-bin.000163 \| 104857848 \|` `\| mysql-bin.000164 \| 104857656 \|` `\| mysql-bin.000165 \| 104857721 \|` `\| mysql-bin.000166 \| 104857758 \|` `\| mysql-bin.000167 \| 104857934 \|` `\| mysql-bin.000168 \| 104857753 \|` `\| mysql-bin.000169 \| 104857833 \|` `\| mysql-bin.000170 \| 104858059 \|` `\| mysql-bin.000171 \| 104857766 \|` `\| mysql-bin.000172 \| 104857865 \|` `\| mysql-bin.000173 \| 104857741 \|` `\| mysql-bin.000174 \| 104857922 \|` `\| mysql-bin.000175 \| 104857776 \|` `\| mysql-bin.000176 \| 104857720 \|` `\| mysql-bin.000177 \| 104857647 \|` `\| mysql-bin.000178 \| 104857977 \|` `\| mysql-bin.000179 \| 104857736 \|` `\| mysql-bin.000180 \| 104857725 \|` `\| mysql-bin.000181 \| 104857883 \|` `\| mysql-bin.000182 \| 104857810 \|` `\| mysql-bin.000183 \| 104857707 \|` `\| mysql-bin.000184 \| 104857799 \|` `\| mysql-bin.000185 \| 104857977 \|` `\| mysql-bin.000186 \| 104857804 \|` `\| mysql-bin.000187 \| 104857861 \|` `\| mysql-bin.000188 \| 104857665 \|` `\| mysql-bin.000189 \| 104857872 \|` `\| mysql-bin.000190 \| 104857870 \|` `\| mysql-bin.000191 \| 104857891 \|` `\| mysql-bin.000192 \| 104857742 \|` `\| mysql-bin.000193 \| 104857830 \|` `\| mysql-bin.000194 \| 104857873 \|` `\| mysql-bin.000195 \| 104857750 \|` `\| mysql-bin.000196 \| 104857669 \|` `\| mysql-bin.000197 \| 104857867 \|` `\| mysql-bin.000198 \| 104857702 \|` `\| mysql-bin.000199 \| 104857974 \|` `\| mysql-bin.000200 \| 104857866 \|` `\| mysql-bin.000201 \| 104857742 \|` `\| mysql-bin.000202 \| 104857688 \|` `\| mysql-bin.000203 \| 104857654 \|` `\| mysql-bin.000204 \| 104857895 \|` `\| mysql-bin.000205 \| 104857709 \|` `\| mysql-bin.000206 \| 104857922 \|` `\| mysql-bin.000207 \| 104858019 \|` `\| mysql-bin.000208 \| 104857790 \|` `\| mysql-bin.000209 \| 104857655 \|` `\| mysql-bin.000210 \| 104857996 \|` `\| mysql-bin.000211 \| 104857956 \|` `\| mysql-bin.000212 \| 104857659 \|` `\| mysql-bin.000213 \| 104857835 \|` `\| mysql-bin.000214 \| 104857643 \|` `\| mysql-bin.000215 \| 104857999 \|` `\| mysql-bin.000216 \| 104857770 \|` `\| mysql-bin.000217 \| 104857672 \|` `\| mysql-bin.000218 \| 104857753 \|` `\| mysql-bin.000219 \| 104857795 \|` `\| mysql-bin.000220 \| 104858032 \|` `\| mysql-bin.000221 \| 104857861 \|` `\| mysql-bin.000222 \| 104857896 \|` `\| mysql-bin.000223 \| 104857694 \|` `\| mysql-bin.000224 \| 104857747 \|` `\| mysql-bin.000225 \| 104857861 \|` `\| mysql-bin.000226 \| 104857783 \|` `\| mysql-bin.000227 \| 104857680 \|` `\| mysql-bin.000228 \| 104857882 \|` `\| mysql-bin.000229 \| 104857688 \|` `\| mysql-bin.000230 \| 104857756 \|` `\| mysql-bin.000231 \| 104857744 \|` `\| mysql-bin.000232 \| 104857759 \|` `\| mysql-bin.000233 \| 104857794 \|` `\| mysql-bin.000234 \| 104857863 \|` `\| mysql-bin.000235 \| 104858031 \|` `\| mysql-bin.000236 \| 104857902 \|` `\| mysql-bin.000237 \| 104857715 \|` `\| mysql-bin.000238 \| 104857746 \|` `\| mysql-bin.000239 \| 104857839 \|` `\| mysql-bin.000240 \| 104857845 \|` `\| mysql-bin.000241 \| 104857771 \|` `\| mysql-bin.000242 \| 104857692 \|` `\| mysql-bin.000243 \| 104857806 \|` `\| mysql-bin.000244 \| 104857927 \|` `\| mysql-bin.000245 \| 104857729 \|` `\| mysql-bin.000246 \| 104857961 \|` `\| mysql-bin.000247 \| 104857687 \|` `\| mysql-bin.000248 \| 104857872 \|` `\| mysql-bin.000249 \| 104857881 \|` `\| mysql-bin.000250 \| 104858045 \|` `\| mysql-bin.000251 \| 104857817 \|` `\| mysql-bin.000252 \| 104857901 \|` `\| mysql-bin.000253 \| 104858075 \|` `\| mysql-bin.000254 \| 104857730 \|` `\| mysql-bin.000255 \| 104857756 \|` `\| mysql-bin.000256 \| 104857927 \|` `\| mysql-bin.000257 \| 104857892 \|` `\| mysql-bin.000258 \| 104857738 \|` `\| mysql-bin.000259 \| 104857914 \|` `\| mysql-bin.000260 \| 104857774 \|` `\| mysql-bin.000261 \| 104857892 \|` `\| mysql-bin.000262 \| 104857941 \|` `\| mysql-bin.000263 \| 104857845 \|` `\| mysql-bin.000264 \| 104857732 \|` `\| mysql-bin.000265 \| 104857881 \|` `\| mysql-bin.000266 \| 104857782 \|` `\| mysql-bin.000267 \| 104857927 \|` `\| mysql-bin.000268 \| 104857881 \|` `\| mysql-bin.000269 \| 104857791 \|` `\| mysql-bin.000270 \| 104857749 \|` `\| mysql-bin.000271 \| 104857777 \|` `\| mysql-bin.000272 \| 104857879 \|` `\| mysql-bin.000273 \| 104857767 \|` `\| mysql-bin.000274 \| 104858094 \|` `\| mysql-bin.000275 \| 104858023 \|` `\| mysql-bin.000276 \| 104857726 \|` `\| mysql-bin.000277 \| 104857810 \|` `\| mysql-bin.000278 \| 104857868 \|` `\| mysql-bin.000279 \| 104857861 \|` `\| mysql-bin.000280 \| 104857695 \|` `\| mysql-bin.000281 \| 104857974 \|` `\| mysql-bin.000282 \| 104857873 \|` `\| mysql-bin.000283 \| 104857787 \|` `\| mysql-bin.000284 \| 104857778 \|` `\| mysql-bin.000285 \| 104857901 \|` `\| mysql-bin.000286 \| 104857999 \|` `\| mysql-bin.000287 \| 104857860 \|` `\| mysql-bin.000288 \| 104857698 \|` `\| mysql-bin.000289 \| 104857773 \|` `\| mysql-bin.000290 \| 104857751 \|` `\| mysql-bin.000291 \| 104857741 \|` `\| mysql-bin.000292 \| 104857926 \|` `\| mysql-bin.000293 \| 104857693 \|` `\| mysql-bin.000294 \| 104857684 \|` `\| mysql-bin.000295 \| 104857892 \|` `\| mysql-bin.000296 \| 104857697 \|` `\| mysql-bin.000297 \| 104857985 \|` `\| mysql-bin.000298 \| 104857656 \|` `\| mysql-bin.000299 \| 104857683 \|` `\| mysql-bin.000300 \| 104857695 \|` `\| mysql-bin.000301 \| 104857646 \|` `\| mysql-bin.000302 \| 104857812 \|` `\| mysql-bin.000303 \| 92771646 \|` `+——————+———–+` `240` `rows` `in` `set` `(0.00 sec)`