显示插入查询的优化参数：
show variables like "concurrent_insert";
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| concurrent_insert | AUTO |
+-------------------+-------+
set GLOBAL concurrent_insert=2;
插入数据时MySQL会对插入的记录进行唯一性校验
这种校验也会降低插入记录的速度。可以在插入记录之前禁用唯一性检查。等到记录插入完毕后再开启。禁用唯一性检查的语句如下：
SET UNIQUE_CHECKS=0;
重新开启唯一性检查的语句如下：
SET UNIQUE_CHECKS=1;
mysql> show variables like "UNIQUE_CHECKS"; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | unique_checks | ON | +---------------+-------+
mysql设置最大连接数量
mysql> set GLOBAL max_connections = 1000; Query OK, 0 rows affected (0.00 sec)
之前配置了mysql的主从备份
结果发现日志很多，占据了很多的磁盘空间，需要进行额外的磁盘管理
进入mysql的存储区域，查看一下数据部分的空间大小
/mnt/ssd/data$ ls auto.cnf mysql-bin.000139 mysql-bin.000149 mysql-bin.000159 mysql-bin.000169 mysql-bin.000179 mysql-bin.000189 mysql-bin.000199 mysql-bin.000209 mysql-bin.index db_user_msg mysql-bin.000140 mysql-bin.000150 mysql-bin.000160 mysql-bin.000170 mysql-bin.000180 mysql-bin.000190 mysql-bin.000200 mysql-bin.000210 performance_schema ibdata1 mysql-bin.000141 mysql-bin.000151 mysql-bin.000161 mysql-bin.000171 mysql-bin.000181 mysql-bin.000191 mysql-bin.000201 mysql-bin.000211 StockData ib_logfile0 mysql-bin.000142 mysql-bin.000152 mysql-bin.000162 mysql-bin.000172 mysql-bin.000182 mysql-bin.000192 mysql-bin.000202 mysql-bin.000212 ib_logfile1 mysql-bin.000143 mysql-bin.000153 mysql-bin.000163 mysql-bin.000173 mysql-bin.000183 mysql-bin.000193 mysql-bin.000203 mysql-bin.000213 mysql mysql-bin.000144 mysql-bin.000154 mysql-bin.000164 mysql-bin.000174 mysql-bin.000184 mysql-bin.000194 mysql-bin.000204 mysql-bin.000214 mysql-bin.000135 mysql-bin.000145 mysql-bin.000155 mysql-bin.000165 mysql-bin.000175 mysql-bin.000185 mysql-bin.000195 mysql-bin.000205 mysql-bin.000215 mysql-bin.000136 mysql-bin.000146 mysql-bin.000156 mysql-bin.000166 mysql-bin.000176 mysql-bin.000186 mysql-bin.000196 mysql-bin.000206 mysql-bin.000216 mysql-bin.000137 mysql-bin.000147 mysql-bin.000157 mysql-bin.000167 mysql-bin.000177 mysql-bin.000187 mysql-bin.000197 mysql-bin.000207 mysql-bin.000217 mysql-bin.000138 mysql-bin.000148 mysql-bin.000158 mysql-bin.000168 mysql-bin.000178 mysql-bin.000188 mysql-bin.000198 mysql-bin.000208 mysql-bin.000218
显示mysql中数据部分和基本的配置部分所占用的磁盘空间
/mnt/ssd/data$ sudo du -h -d 1 163G ./StockData 636K ./performance_schema 1.7M ./mysql 56K ./db_user_msg 242G .
结果发现这其中很多的空间都被mysql-bin*文件所占用了
/mnt/ssd/data$ sudo du -c -h mysql-bin* 1.1G mysql-bin.000135 ...... 491M mysql-bin.000218 4.0K mysql-bin.index 80G total 结果显示mysql-bin占用了大约80G的磁盘空间
现在我们要将这些日志清理掉
mysql> show master logs; +------------------+------------+ | Log_name | File_size | +------------------+------------+ | mysql-bin.000135 | 1073742116 | | mysql-bin.000136 | 1073742153 | ...... | mysql-bin.000216 | 1073747783 | | mysql-bin.000217 | 1073742128 | | mysql-bin.000218 | 514734902 | +------------------+------------+ 84 rows in set (0.01 sec) 这应该是主从备份一周以内的日志文件 查看一下目前正在起作用的日志文件是哪一个 mysql> show master status; +------------------+-----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+-------