MySQL 配置参数相关

WinstonShaco
382 阅读3分钟
补充点：
MySQL 5.7安装不生成临时密码
[root@localhost bin]# ./mysqld --help -verbose|less
  #创建一个超级用户，随机产生的密码展示在日志上
  --initialize        Create the default database and exit. Create a super user
                      with a random expired password and store it into the log.
  #创建一个超级用户，密码为空（不安全）
  --initialize-insecure 
                      Create the default database and exit. Create a super user
                      with empty password.

1、MySQL 的配置文件

  • 文件名称：my.cnf（my.ini），是一个文本文件
前文安装时展示过，这里就不赘述
  • 可以有多个配置文件，存在参数替换原则
[root@localhost bin]# mysql --help --verbose | less 
Default options are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf /usr/local/mysql/etc/my.cnf ~/.my.cnf
即说明/etc/my.cnf | /etc/mysql/my.cnf | /usr/local/mysql/etc/my.cnf | ~/.my.cnf 都是配置文件，优先级从左到右依次递减。
  • 标签
[mysqld] ：MySQL 服务器启动时用到的参数
[mysql]：用mysql 命令登录之后用到的参数
[client]：所有客户端登录之后会用到的参数
2、MySQL的配置参数

  • 从作用域分为 会话参数（session） 和 全局参数（global）

  • 在线修改会话参数，只对当前会话有效

  • 从类型上分为 可修改 和 只读参数

  • 只读参数修改只能通过修改配置文件，重启生效

  • 可修改参数可以在线修改

  • 所有参数的修改都不能持久化

可以看到所有参数的默认值
# 可以看到所有参数的默认值
[root@localhost bin]# mysql --help --verbose 
# 可以查看配置的值
(root@localhost)[(none)]> show variables;
……
513 rows in set (0.00 sec)
# 查看当前会话参数的语法
(root@localhost)[(none)]> show variables like 'log_error';
+---------------+-------------+
| Variable_name | Value       |
+---------------+-------------+
| log_error     | ./error.log |
+---------------+-------------+
1 row in set (0.01 sec)

(root@localhost)[(none)]> show variables like 'datadir%';
+---------------+--------+
| Variable_name | Value  |
+---------------+--------+
| datadir       | /data/ |
+---------------+--------+
1 row in set (0.00 sec)
# 查看全局参数的语法
(root@localhost)[(none)]> show global variables like 'long%';
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
1 row in set (0.01 sec)
**修改参数**
1、可修改的参数
#修改会话级别的参数 只会对当前会话生效
(root@localhost)[(none)]> set session long_query_time=5;
Query OK, 0 rows affected (0.04 sec)

(root@localhost)[(none)]> show global variables like 'long%';
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
1 row in set (0.00 sec)

(root@localhost)[(none)]> show  variables like 'long%';
+-----------------+----------+
| Variable_name   | Value    |
+-----------------+----------+
| long_query_time | 5.000000 |
+-----------------+----------+
1 row in set (0.01 sec)
2、不可修改的参数，想要修改需要停机更改配置文件，来达到目的
(root@localhost)[(none)]> show  variables like 'datadir';
+---------------+--------+
| Variable_name | Value  |
+---------------+--------+
| datadir       | /data/ |
+---------------+--------+
1 row in set (0.00 sec)

(root@localhost)[(none)]> set global datadir = '/data/q/';
ERROR 1238 (HY000): Variable 'datadir' is a read only variable
3、查看参数 performance_schema库
    5.7新增的表 variables_by_thread 来查看每一个会话中的值
# 与参数相关的表 
(root@localhost)[performance_schema]> show tables like '%variables%';
+--------------------------------------------+
| Tables_in_performance_schema (%variables%) |
+--------------------------------------------+
| global_variables                           |
| session_variables                          |
| user_variables_by_thread                   |
| variables_by_thread                        |
+--------------------------------------------+
4 rows in set (0.00 sec)
# 可以看到当前参数有两个会话正在用
(root@localhost)[performance_schema]> select * from variables_by_thread where variable_name = 'long_query_time';
+-----------+-----------------+----------------+
| THREAD_ID | VARIABLE_NAME   | VARIABLE_VALUE |
+-----------+-----------------+----------------+
|        28 | long_query_time | 5.000000       |
|        29 | long_query_time | 10.000000      |
+-----------+-----------------+----------------+
2 rows in set (0.00 sec)
    查看show processlist 中与ID 与线程号对应关系，PROCESSLIST_ID 对应的就是show processlist中的 id=3
(root@localhost)[(none)]> show processlist;
+----+------+-----------+--------------------+---------+------+----------+------------------+
| Id | User | Host      | db                 | Command | Time | State    | Info             |
+----+------+-----------+--------------------+---------+------+----------+------------------+
|  3 | root | localhost | performance_schema | Sleep   |  107 |          | NULL             |
|  4 | root | localhost | NULL               | Query   |    0 | starting | show processlist |
+----+------+-----------+--------------------+---------+------+----------+------------------+
2 rows in set (0.02 sec)

(root@localhost)[performance_schema]> select * from  threads limit 1\G
*************************** 1\. row ***************************
          THREAD_ID: 1
               NAME: thread/sql/main
               TYPE: BACKGROUND
     PROCESSLIST_ID: NULL
   PROCESSLIST_USER: NULL
   PROCESSLIST_HOST: NULL
     PROCESSLIST_DB: NULL
PROCESSLIST_COMMAND: NULL
   PROCESSLIST_TIME: 2501
  PROCESSLIST_STATE: NULL
   PROCESSLIST_INFO: NULL
   PARENT_THREAD_ID: NULL
               ROLE: NULL
       INSTRUMENTED: YES
            HISTORY: YES
    CONNECTION_TYPE: NULL
       THREAD_OS_ID: 2085
1 row in set (0.00 sec)

(root@localhost)[performance_schema]> select * from  threads where thread_id = 28\G
*************************** 1\. row ***************************
          THREAD_ID: 28
               NAME: thread/sql/one_connection
               TYPE: FOREGROUND
     PROCESSLIST_ID: 3
   PROCESSLIST_USER: root
   PROCESSLIST_HOST: localhost
     PROCESSLIST_DB: performance_schema
PROCESSLIST_COMMAND: Query
   PROCESSLIST_TIME: 0
  PROCESSLIST_STATE: Sending data
   PROCESSLIST_INFO: select * from  threads where thread_id = 28
   PARENT_THREAD_ID: NULL
               ROLE: NULL
       INSTRUMENTED: YES
            HISTORY: YES
    CONNECTION_TYPE: Socket
       THREAD_OS_ID: 4173
1 row in set (0.00 sec)
avatar
文章
阅读
粉丝