首页 / MYSQL / MySql常用命令--优化参数以及日常管理

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

内容导读

互联网集市收集整理的这篇技术教程文章主要介绍了MySql常用命令--优化参数以及日常管理，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含8800字，纯文字阅读大概需要13分钟。

内容图文

| 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
  1. 执行“reset master;”命令，该命令将删除所有二进制日志，新日志的编号从“000001” 开始；
Mysql>reset master;
  1. 执行“Purge master logs to ‘mysql-bin.’” 命令，该命令将删除“” 编号之前的所有日志，下列中删除了“mysql-bin.000001”之前编号的所有日志;
Mysql>purge master logs to ‘mysql-bin.000215‘;
  1. 执行“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’;
  1. 设置参数 –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)

版权声明：本文为博主原创文章，未经博主允许不得转载。

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

标签：mysql   查询优化   优化   

本文系统来源：http://blog.csdn.net/berguiliu/article/details/47169463

内容总结

以上是互联网集市为您收集整理的MySql常用命令--优化参数以及日常管理全部内容，希望文章能够帮你解决MySql常用命令--优化参数以及日常管理所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。

内容备注

版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。

内容手机端

扫描二维码推送至手机访问。

本文链接：https://qyyshop.com/info/453878.html

来源：【匿名】