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 |
+------------------+-----------+--------------+------------------+-------------------+
| 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: 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: 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: 0
1 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)
