MySql常用命令--优化参数以及日常管理

Home

Database

Mysql Tutorial

MySql常用命令--优化参数以及日常管理_MySQL

WBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWB

Jun 01, 2016 pm 12:58 PM

parameterOrderCommonly used

显示插入查询的优化参数：

<code class="hljs asciidoc">show variables like "concurrent_insert";
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| concurrent_insert | AUTO  |
+-------------------+-------+

set GLOBAL concurrent_insert=2;</code>

插入数据时MySQL会对插入的记录进行唯一性校验<br>
这种校验也会降低插入记录的速度。可以在插入记录之前禁用唯一性检查。等到记录插入完毕后再开启。禁用唯一性检查的语句如下：

<code class="hljs asciidoc"><code class="hljs sql">SET UNIQUE_CHECKS=0;</code></code>

<code class="hljs sql">重新开启唯一性检查的语句如下：

<code class="hljs asciidoc"><code class="hljs sql"><code class="hljs sql">SET UNIQUE_CHECKS=1;</code></code></code>

<code class="hljs asciidoc"><code class="hljs sql"><code class="hljs sql"><code class="hljs asciidoc">mysql> show variables like "UNIQUE_CHECKS";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| unique_checks | ON    |
+---------------+-------+</code></code></code></code>

<code class="hljs sql"><code class="hljs sql"><code class="hljs asciidoc">mysql设置最大连接数量

<code class="hljs asciidoc"><code class="hljs sql"><code class="hljs sql"><code class="hljs asciidoc"><code class="hljs lasso">mysql> set GLOBAL max_connections = 1000;
Query OK, 0 rows affected (0.00 sec)</code></code></code></code></code>

<code class="hljs sql"><code class="hljs sql"><code class="hljs asciidoc"><code class="hljs lasso">之前配置了mysql的主从备份<br>
结果发现日志很多，占据了很多的磁盘空间，需要进行额外的磁盘管理<br>
进入mysql的存储区域，查看一下数据部分的空间大小

<code class="hljs asciidoc"><code class="hljs sql"><code class="hljs sql"><code class="hljs asciidoc"><code class="hljs lasso"><code class="hljs lasso">/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</code></code></code></code></code></code>

<code class="hljs sql"><code class="hljs sql"><code class="hljs asciidoc"><code class="hljs lasso"><code class="hljs lasso">显示mysql中数据部分和基本的配置部分所占用的磁盘空间

<code class="hljs asciidoc"><code class="hljs sql"><code class="hljs sql"><code class="hljs asciidoc"><code class="hljs lasso"><code class="hljs lasso"><code class="hljs lasso">/mnt/ssd/data$ sudo du -h -d 1
163G    ./StockData
636K    ./performance_schema
1.7M    ./mysql
56K     ./db_user_msg
242G    .</code></code></code></code></code></code></code>

<code class="hljs sql"><code class="hljs sql"><code class="hljs asciidoc"><code class="hljs lasso"><code class="hljs lasso"><code class="hljs lasso">结果发现这其中很多的空间都被mysql-bin*文件所占用了

<code class="hljs asciidoc"><code class="hljs sql"><code class="hljs sql"><code class="hljs asciidoc"><code class="hljs lasso"><code class="hljs lasso"><code class="hljs lasso"><code class="hljs lasso">/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的磁盘空间</code></code></code></code></code></code></code></code>

<code class="hljs sql"><code class="hljs sql"><code class="hljs asciidoc"><code class="hljs lasso"><code class="hljs lasso"><code class="hljs lasso"><code class="hljs lasso">现在我们要将这些日志清理掉

<code class="hljs asciidoc"><code class="hljs sql"><code class="hljs sql"><code class="hljs asciidoc"><code class="hljs lasso"><code class="hljs lasso"><code class="hljs lasso"><code class="hljs lasso"><code class="hljs asciidoc">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)</code></code></code></code></code></code></code></code></code>

<code class="hljs sql"><code class="hljs sql"><code class="hljs asciidoc"><code class="hljs lasso"><code class="hljs lasso"><code class="hljs lasso"><code class="hljs lasso"><code class="hljs asciidoc">如何删除多余的日志<br>
日志的删除<br>
对于比较繁忙的OLTP系统，由于每天生产日志量大，这些日志如果长时间不清理，将会对磁盘空间带来很大的浪费，因此，定期删除日志是DBA维护Mysql数据库的一个重要工作内容，下面将介绍几种删除日志的常见方法:

<code class="hljs asciidoc"><code class="hljs sql"><code class="hljs sql"><code class="hljs asciidoc"><code class="hljs lasso"><code class="hljs lasso"><code class="hljs lasso"><code class="hljs lasso"><code class="hljs asciidoc"><code class="hljs asciidoc">mysql> show variables like "datadir";
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| datadir       | /var/lib/mysql/ |
+---------------+-----------------+
1 row in set (0.00 sec)
这是mysql中的默认存储位置</code></code></code></code></code></code></code></code></code></code>

<code class="hljs asciidoc"><code class="hljs sql"><code class="hljs sql"><code class="hljs asciidoc"><code class="hljs lasso"><code class="hljs lasso"><code class="hljs lasso"><code class="hljs lasso"><code class="hljs asciidoc"><code class="hljs asciidoc"><code class="hljs smalltalk">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)</code></code></code></code></code></code></code></code></code></code></code>

<code class="hljs asciidoc"><code class="hljs sql"><code class="hljs sql"><code class="hljs asciidoc"><code class="hljs lasso"><code class="hljs lasso"><code class="hljs lasso"><code class="hljs lasso"><code class="hljs asciidoc"><code class="hljs asciidoc"><code class="hljs smalltalk"><code class="hljs lasso">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</code></code></code></code></code></code></code></code></code></code></code></code>

<code class="hljs sql"><code class="hljs sql"><code class="hljs asciidoc"><code class="hljs lasso"><code class="hljs lasso"><code class="hljs lasso"><code class="hljs lasso"><code class="hljs asciidoc"><code class="hljs asciidoc"><code class="hljs smalltalk"><code class="hljs lasso">执行“reset master;”命令，该命令将删除所有二进制日志，新日志的编号从“000001” 开始；

<code class="hljs asciidoc"><code class="hljs sql"><code class="hljs sql"><code class="hljs asciidoc"><code class="hljs lasso"><code class="hljs lasso"><code class="hljs lasso"><code class="hljs lasso"><code class="hljs asciidoc"><code class="hljs asciidoc"><code class="hljs smalltalk"><code class="hljs lasso"><code class="hljs perl">Mysql>reset master;</code></code></code></code></code></code></code></code></code></code></code></code></code>

<code class="hljs sql"><code class="hljs sql"><code class="hljs asciidoc"><code class="hljs lasso"><code class="hljs lasso"><code class="hljs lasso"><code class="hljs lasso"><code class="hljs asciidoc"><code class="hljs asciidoc"><code class="hljs smalltalk"><code class="hljs lasso"><code class="hljs perl">执行“Purge master logs to ‘mysql-bin.’” 命令，该命令将删除“” 编号之前的所有日志，下列中删除了“mysql-bin.000001”之前编号的所有日志;

<code class="hljs asciidoc"><code class="hljs sql"><code class="hljs sql"><code class="hljs asciidoc"><code class="hljs lasso"><code class="hljs lasso"><code class="hljs lasso"><code class="hljs lasso"><code class="hljs asciidoc"><code class="hljs asciidoc"><code class="hljs smalltalk"><code class="hljs lasso"><code class="hljs perl"><code class="hljs vbnet">Mysql>purge master logs to &#39;mysql-bin.000215&#39;;</code></code></code></code></code></code></code></code></code></code></code></code></code></code>

<code class="hljs sql"><code class="hljs sql"><code class="hljs asciidoc"><code class="hljs lasso"><code class="hljs lasso"><code class="hljs lasso"><code class="hljs lasso"><code class="hljs asciidoc"><code class="hljs asciidoc"><code class="hljs smalltalk"><code class="hljs lasso"><code class="hljs perl"><code class="hljs vbnet">执行“purge master logs before ‘yyyy-mm-dd hh24:min:ss’”命令，该命令将删除日期为“yyyy-mm-dd hh24:mi:ss”之前产生的所有日志，下列中删除了日期在“2010-05-22 01:00:00”之前的所有日志

<code class="hljs asciidoc"><code class="hljs sql"><code class="hljs sql"><code class="hljs asciidoc"><code class="hljs lasso"><code class="hljs lasso"><code class="hljs lasso"><code class="hljs lasso"><code class="hljs asciidoc"><code class="hljs asciidoc"><code class="hljs smalltalk"><code class="hljs lasso"><code class="hljs perl"><code class="hljs vbnet"><code class="hljs css">Mysql>purge master logs before &lsquo;2010-05-22 01:00:00&rsquo;;</code></code></code></code></code></code></code></code></code></code></code></code></code></code></code>

<code class="hljs sql"><code class="hljs sql"><code class="hljs asciidoc"><code class="hljs lasso"><code class="hljs lasso"><code class="hljs lasso"><code class="hljs lasso"><code class="hljs asciidoc"><code class="hljs asciidoc"><code class="hljs smalltalk"><code class="hljs lasso"><code class="hljs perl"><code class="hljs vbnet"><code class="hljs css">设置参数 –expire_logs_days=#(days)，此参数的含义是设置日志的过期天数，过来指定的天数后日志将会被自动删除，这样将有利于减少DBA管理日志的工作量。

<code class="hljs asciidoc"><code class="hljs sql"><code class="hljs sql"><code class="hljs asciidoc"><code class="hljs lasso"><code class="hljs lasso"><code class="hljs lasso"><code class="hljs lasso"><code class="hljs asciidoc"><code class="hljs asciidoc"><code class="hljs smalltalk"><code class="hljs lasso"><code class="hljs perl"><code class="hljs vbnet"><code class="hljs css"><code class="hljs asciidoc">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)</code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code>

Statement

The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn

How to Grant Permissions to New MySQL UsersMay 09, 2025 am 12:16 AM

TograntpermissionstonewMySQLusers,followthesesteps:1)AccessMySQLasauserwithsufficientprivileges,2)CreateanewuserwiththeCREATEUSERcommand,3)UsetheGRANTcommandtospecifypermissionslikeSELECT,INSERT,UPDATE,orALLPRIVILEGESonspecificdatabasesortables,and4)

How to Add Users in MySQL: A Step-by-Step GuideMay 09, 2025 am 12:14 AM

ToaddusersinMySQLeffectivelyandsecurely,followthesesteps:1)UsetheCREATEUSERstatementtoaddanewuser,specifyingthehostandastrongpassword.2)GrantnecessaryprivilegesusingtheGRANTstatement,adheringtotheprincipleofleastprivilege.3)Implementsecuritymeasuresl

MySQL: Adding a new user with complex permissionsMay 09, 2025 am 12:09 AM

ToaddanewuserwithcomplexpermissionsinMySQL,followthesesteps:1)CreatetheuserwithCREATEUSER'newuser'@'localhost'IDENTIFIEDBY'password';.2)Grantreadaccesstoalltablesin'mydatabase'withGRANTSELECTONmydatabase.TO'newuser'@'localhost';.3)Grantwriteaccessto'

MySQL: String Data Types and CollationsMay 09, 2025 am 12:08 AM

The string data types in MySQL include CHAR, VARCHAR, BINARY, VARBINARY, BLOB, and TEXT. The collations determine the comparison and sorting of strings. 1.CHAR is suitable for fixed-length strings, VARCHAR is suitable for variable-length strings. 2.BINARY and VARBINARY are used for binary data, and BLOB and TEXT are used for large object data. 3. Sorting rules such as utf8mb4_unicode_ci ignores upper and lower case and is suitable for user names; utf8mb4_bin is case sensitive and is suitable for fields that require precise comparison.

MySQL: What length should I use for VARCHARs?May 09, 2025 am 12:06 AM

The best MySQLVARCHAR column length selection should be based on data analysis, consider future growth, evaluate performance impacts, and character set requirements. 1) Analyze the data to determine typical lengths; 2) Reserve future expansion space; 3) Pay attention to the impact of large lengths on performance; 4) Consider the impact of character sets on storage. Through these steps, the efficiency and scalability of the database can be optimized.

MySQL BLOB : are there any limits?May 08, 2025 am 12:22 AM

MySQLBLOBshavelimits:TINYBLOB(255bytes),BLOB(65,535bytes),MEDIUMBLOB(16,777,215bytes),andLONGBLOB(4,294,967,295bytes).TouseBLOBseffectively:1)ConsiderperformanceimpactsandstorelargeBLOBsexternally;2)Managebackupsandreplicationcarefully;3)Usepathsinst

MySQL : What are the best tools to automate users creation?May 08, 2025 am 12:22 AM

The best tools and technologies for automating the creation of users in MySQL include: 1. MySQLWorkbench, suitable for small to medium-sized environments, easy to use but high resource consumption; 2. Ansible, suitable for multi-server environments, simple but steep learning curve; 3. Custom Python scripts, flexible but need to ensure script security; 4. Puppet and Chef, suitable for large-scale environments, complex but scalable. Scale, learning curve and integration needs should be considered when choosing.

MySQL: Can I search inside a blob?May 08, 2025 am 12:20 AM

Yes,youcansearchinsideaBLOBinMySQLusingspecifictechniques.1)ConverttheBLOBtoaUTF-8stringwithCONVERTfunctionandsearchusingLIKE.2)ForcompressedBLOBs,useUNCOMPRESSbeforeconversion.3)Considerperformanceimpactsanddataencoding.4)Forcomplexdata,externalproc

See all articles