MySql common commands-optimization parameters and daily management-MySQL

Display the optimization parameters of the insert query: showvariableslike displays the optimization parameters of the insert query:

show variables like "concurrent_insert";+-------------------+-------+| Variable_name     | Value |+-------------------+-------+| concurrent_insert | AUTO  |+-------------------+-------+set GLOBAL concurrent_insert=2;

When data is inserted, MySQL checks the uniqueness of the inserted records.
This validation also reduces the insert record speed. You can disable the uniqueness check before inserting a record. Enable this function after the record is inserted. The statement for disabling the uniqueness check is as follows:

SET UNIQUE_CHECKS=0;

The statement to re-enable the uniqueness check is as follows:

SET UNIQUE_CHECKS=1;

mysql> show variables like "UNIQUE_CHECKS";+---------------+-------+| Variable_name | Value |+---------------+-------+| unique_checks | ON |+---------------+-------+

Mysql sets the maximum number of connections

mysql> set GLOBAL max_connections = 1000;Query OK, 0 rows affected (0.00 sec)

Mysql master-slave backup is configured previously.
It is found that there are many logs, occupying a lot of disk space and additional disk management is required.
Go to the mysql storage area and check the space of the data section.

/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

Displays the disk space occupied by the data and basic configurations in mysql.

/mnt/ssd/data$ sudo du -h -d 1163G ./StockData636K ./performance_schema1.7M ./mysql56K ./db_user_msg242G .

It is found that many of these spaces are occupied by the mysql-bin * file.

/Mnt/ssd/data $ sudo du-c-h mysql-bin * 1.1G mysql-bin.000135 ...... 491 M mysql-bin.0002184.0K mysql-bin.index80G total results show mysql-bin occupies approximately 80 GB of disk space

Now we need to clear these logs

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) this should be the log file of the master-slave backup for less than one week to check which mysql> show master status is currently working log file; + metric + ----------- + -------------- + ---------------- + metric + | File | Position | Binlog_Do_DB | metric | + metric + ----------- + -------------- + metric + | mysql-bin.000218 | 514734902 | | + ------------------ + ----------- + -------------- + ------------------ + ------------------- + 1 row in set (0.00 sec)

How to Delete redundant logs
Log deletion
For busy OLTP systems, due to the large amount of logs produced every day, these logs will be a great waste of disk space if they are not cleaned up for a long time, regular log deletion is an important task for DBA to maintain the Mysql database. The following describes several common methods to delete logs:

Obtain the data storage location in mysql first:

Mysql> show variables like "datadir "; + --------------- + bytes + | Variable_name | Value | + ----------------- + --------------- + | datadir |/var/lib/mysql/| + --------------- + ----------------- + 1 row in set (0.00 sec) this is the default storage location in mysql.

Check the storage location of logs in the 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)

Query the update status of the slave node:

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 runtime: 514734902 Relay_Log_File: mysqld-relay-bin.000669 Relay_Log_Pos: 236 runtime: mysql-bin.000218 runtime: Yes runtime: Runtime: StockData. t_day_stock failed: Failed: 0 Last_Error: Skip_Counter: 0 failed: 514734902 Relay_Log_Space: 132818854 Until_Condition: None Until_Log_File: Failed: 0 failed: No condition: Failed: Master_SSL_Cert: Failed: failed: master_SSL_Key: Keys: No keys: 0 Last_IO_Error: Keys: 0 Last_ SQL _Error: Keys: Master_Server_Id: 1 Master_UUID: e61e54d8-1e08-11e5-9160-44a842112d25 Master_Info_File:/var/lib/mysql/master.info SQL _Delay: 0 keys: NULL layout: Slave has read all relay log; waiting for the slave I/O thread to update it Master_Retry_Count: 86400 Master_Bind: usage: Master_SSL_Crl: Master_SSL_Crlpath: usage: Auto_Position: 01 row in set (0.00 sec) Here we get useful information: Master_Log_File: mysql-bin.000218 Read_Master_Log_Pos: 514734902 Relay_Log_File: mysqld-relay-bin.000669 Relay_Log_Pos: 236 rows: 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

Run the "reset master;" command to delete all binary logs. the number of the new log starts from "000001;

Mysql>reset master;

Run the Purge master logs to 'MySQL-bin. 'command to delete all logs prior to the' number and delete all logs numbered before the mysql-bin.000001 below;

Mysql>purge master logs to 'mysql-bin.000215';

Run the "purge master logs before 'yyyy-mm-dd hh24: min: SS'" command to delete the date "yyyy-mm-dd hh24: mi: all logs generated before "ss" are deleted from the following logs before "01:00:00"

Mysql>purge master logs before ‘2010-05-22 01:00:00’;

Set the parameter-expire_logs_days = # (days). This parameter indicates the log expiration days. after the specified number of days, the log is automatically deleted, this will reduce the workload for DBA to manage logs.

Gpx @ dell :~ $ Sudo vim/etc/mysql/my. cnf [mysqld] expire_logs_days = 3. logs generated three days ago are deleted, the system automatically deletes the binary log to view the Expiration Time. mysql> show variables like "expire % "; + ------------------ + ------- + | Variable_name | Value | + ------------------ + ------- + | expire_logs_days | 3 | + ------------------ + ------- + 1 row in set (0.00 sec)