插入数据时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$ lsauto.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.indexdb_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_schemaibdata1 mysql-bin.000141 mysql-bin.000151 mysql-bin.000161 mysql-bin.000171 mysql-bin.000181 mysql-bin.000191 mysql-bin.000201 mysql-bin.000211 stockdataib_logfile0 mysql-bin.000142 mysql-bin.000152 mysql-bin.000162 mysql-bin.000172 mysql-bin.000182 mysql-bin.000192 mysql-bin.000202 mysql-bin.000212ib_logfile1 mysql-bin.000143 mysql-bin.000153 mysql-bin.000163 mysql-bin.000173 mysql-bin.000183 mysql-bin.000193 mysql-bin.000203 mysql-bin.000213mysql mysql-bin.000144 mysql-bin.000154 mysql-bin.000164 mysql-bin.000174 mysql-bin.000184 mysql-bin.000194 mysql-bin.000204 mysql-bin.000214mysql-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.000215mysql-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.000216mysql-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.000217mysql-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 1163g ./stockdata636k ./performance_schema1.7m ./mysql56k ./db_user_msg242g .
结果发现这其中很多的空间都被mysql-bin*文件所占用了
/mnt/ssd/data$ sudo du -c -h mysql-bin*1.1g mysql-bin.000135......491m mysql-bin.0002184.0k mysql-bin.index80g 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 |+------------------+-----------+--------------+------------------+-------------------+| mysql-bin.000218 | 514734902 | | | |+------------------+-----------+--------------+------------------+-------------------+1 row in set (0.00 sec)
如何删除多余的日志
日志的删除
对于比较繁忙的oltp系统，由于每天生产日志量大，这些日志如果长时间不清理，将会对磁盘空间带来很大的浪费，因此，定期删除日志是dba维护mysql数据库的一个重要工作内容，下面将介绍几种删除日志的常见方法: 先去获得mysql中数据存储的位置：
mysql> show variables like datadir;+---------------+-----------------+| variable_name | value |+---------------+-----------------+| datadir | /var/lib/mysql/ |+---------------+-----------------+1 row in set (0.00 sec)这是mysql中的默认存储位置
再去查看master中日志的存储位置：
mysql> show variables like log%;+----------------------------------------+-------------------------------+| variable_name | value |+----------------------------------------+-------------------------------+| log_bin | on || log_bin_basename | /mnt/ssd/data/mysql-bin || log_bin_index | /mnt/ssd/data/mysql-bin.index || log_bin_trust_function_creators | off || log_bin_use_v1_row_events | off || log_error | /var/log/mysql/error.log || log_output | file || log_queries_not_using_indexes | off || log_slave_updates | off || log_slow_admin_statements | off || log_slow_slave_statements | off || log_throttle_queries_not_using_indexes | 0 || log_warnings | 1 |+----------------------------------------+-------------------------------+13 rows in set (0.00 sec)
查询slave节点的更新状态：
mysql> show slave status \g*************************** 1. row *************************** slave_io_state: waiting for master to send event master_host: 25.25.2.6 master_user: gpx_sync master_port: 3306 connect_retry: 10 master_log_file: mysql-bin.000218 read_master_log_pos: 514734902 relay_log_file: mysqld-relay-bin.000669 relay_log_pos: 236 relay_master_log_file: mysql-bin.000218 slave_io_running: yes slave_sql_running: yes replicate_do_db: replicate_ignore_db: replicate_do_table: stockdata.t_day_stock replicate_ignore_table: replicate_wild_do_table: replicate_wild_ignore_table: last_errno: 0 last_error: skip_counter: 0 exec_master_log_pos: 514734902 relay_log_space: 132818854 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: 0master_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: e61e54d8-1e08-11e5-9160-44a842112d25 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: 01 row in set (0.00 sec)这里面我们获得的有用的信息是： master_log_file: mysql-bin.000218 read_master_log_pos: 514734902 relay_log_file: mysqld-relay-bin.000669 relay_log_pos: 236 relay_master_log_file: mysql-bin.000218 slave_io_running: yes slave_sql_running: yes slave_sql_running_state: slave has read all relay log; waiting for the slave i/o thread to update it
执行“reset master;”命令，该命令将删除所有二进制日志，新日志的编号从“000001” 开始； mysql>reset master;
执行“purge master logs to ‘mysql-bin.’” 命令，该命令将删除“” 编号之前的所有日志，下列中删除了“mysql-bin.000001”之前编号的所有日志; mysql>purge master logs to 'mysql-bin.000215';
执行“purge master logs before ‘yyyy-mm-dd hh24:min:ss’”命令，该命令将删除日期为“yyyy-mm-dd hh24:mi:ss”之前产生的所有日志，下列中删除了日期在“2010-05-22 01:00:00”之前的所有日志 mysql>purge master logs before ‘2010-05-22 01:00:00’;
设置参数 –expire_logs_days=#(days)，此参数的含义是设置日志的过期天数，过来指定的天数后日志将会被自动删除，这样将有利于减少dba管理日志的工作量。 gpx@dell:~$ sudo vim /etc/mysql/my.cnf[mysqld]expire_logs_days = 3这样，3天前的日志都会被删除，系统自动删除查看系统中对于二进制日志自动删除的过期时间mysql> show variables like expire%;+------------------+-------+| variable_name | value |+------------------+-------+| expire_logs_days | 3 |+------------------+-------+1 row in set (0.00 sec)