• mysql8学习笔记3--MySQL基本文件结构及启动参数


    MySQL基本文件结构

    Data目录为初始化的数据文件存放路径
    Data目录里为每一个数据库创建了一个文件夹
    Ibdata1和ib_logfile0/1为三个专为innodb存放数据和日志的共享文件
    [root@localhost mysql_data]# ll -h
    total 165M
    -rw-r-----. 1 mysql mysql   56 Jun 27 03:12 auto.cnf
    -rw-r-----. 1 mysql mysql 2.4K Jun 27 05:05 binlog.000001
    -rw-r-----. 1 mysql mysql   16 Jun 27 03:18 binlog.index
    -rw-------. 1 mysql mysql 1.7K Jun 27 03:12 ca-key.pem
    -rw-r--r--. 1 mysql mysql 1.1K Jun 27 03:12 ca.pem
    -rw-r--r--. 1 mysql mysql 1.1K Jun 27 03:12 client-cert.pem
    -rw-------. 1 mysql mysql 1.7K Jun 27 03:12 client-key.pem
    -rw-r-----. 1 mysql mysql 5.7K Jun 27 03:12 ib_buffer_pool
    -rw-r-----. 1 mysql mysql  12M Jun 27 05:05 ibdata1
    -rw-r-----. 1 mysql mysql  48M Jun 27 05:05 ib_logfile0
    -rw-r-----. 1 mysql mysql  48M Jun 27 03:12 ib_logfile1
    -rw-r-----. 1 mysql mysql  12M Jun 27 03:18 ibtmp1
    drwxr-x---. 2 mysql mysql  187 Jun 27 03:18 #innodb_temp
    -rw-r-----. 1 mysql mysql  710 Jun 27 03:58 localhost.localdomain.err
    -rw-r-----. 1 mysql mysql    6 Jun 27 03:18 localhost.localdomain.pid
    drwxr-x---. 2 mysql mysql  143 Jun 27 03:12 mysql
    -rw-r-----. 1 mysql mysql  24M Jun 27 05:05 mysql.ibd
    drwxr-x---. 2 mysql mysql 4.0K Jun 27 03:12 performance_schema
    -rw-------. 1 mysql mysql 1.7K Jun 27 03:12 private_key.pem
    -rw-r--r--. 1 mysql mysql  452 Jun 27 03:12 public_key.pem
    -rw-r--r--. 1 mysql mysql 1.1K Jun 27 03:12 server-cert.pem
    -rw-------. 1 mysql mysql 1.7K Jun 27 03:12 server-key.pem
    drwxr-x---. 2 mysql mysql   28 Jun 27 03:12 sys
    -rw-r-----. 1 mysql mysql  10M Jun 27 05:04 undo_001
    -rw-r-----. 1 mysql mysql  10M Jun 27 05:05 undo_002
    MySQL启动相关参数
    • basedir = /usr/local/mysql
    • 代表MySQL安装路径
    • datadir = /usr/local/mysql/data
    • 代表MySQL的数据文件路径
    • port = 3306
    • 指定MySQL的侦听端口
    • log-error=/usr/local/mysql/data/M00006.err
    • 记录MySQL启动日志和运行错误日志
    • bind-address(默认是*)
    • *代表接受所有来自IPV4、IPV6主机网卡的TCP/IP连接
    • 0.0.0.0代表接受所有来自IPV4主机网卡的TCP/IP的连接
    • 指定的IP如127.0.0.1,代表只接受此地址请求的TCP/IP
    character-set-server(默认是latin1)
    • 指定MySQL的字符集
    • collation-server(默认是latin1_swedish_ci)
    • 指定MySQL的排序规则
    • default-storage-engine(默认是InnoDB)
    • 指定MySQL的默认存储引擎
    • default-time-zone
    • 指定默认时区,如果没有指定则和系统默认时区一致
    • open-files-limit(默认5000)
    • 指定Mysqld运行过程中可以打开的文件数,避免出现” Too many open files”报错
    • pid-file=/usr/local/mysql/data/M00006.pid
    • 指定Mysqld进程对应的程序ID文件,默认是在数据文件目录里MySQL启动相关参数
    • Skip-grant-tables
    • 指定避开MySQL内部的权限表启动服务
    • Tmpdir
    • 指定临时表

    现在以字符集、err日志路径和监听端口配置为例:

    当前在my.cnf里没配置这三个参数,所以都是默认值

    [root@localhost mysql_data]# /etc/init.d/mysql start
    Starting MySQL.............. SUCCESS! 
    [root@localhost mysql_data]# netstat -ntlp|grep LISTEN|grep mysql #3306
    tcp6       0      0 :::3306                 :::*                    LISTEN      73923/mysqld        
    tcp6       0      0 :::33060                :::*                    LISTEN      73923/mysqld        
    [root@localhost mysql_data]# mysql -uroot -p
    Enter password: 
    Welcome to the MySQL monitor.  Commands end with ; or g.
    Your MySQL connection id is 9
    Server version: 8.0.13 MySQL Community Server - GPL
    
    Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
    
    Oracle is a registered trademark of Oracle Corporation and/or its
    affiliates. Other names may be trademarks of their respective
    owners.
    
    Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.
    
    mysql> show variables like 'log_error';#log_error日志路径
    +---------------+-----------------------------+
    | Variable_name | Value                       |
    +---------------+-----------------------------+
    | log_error     | ./localhost.localdomain.err |
    +---------------+-----------------------------+
    1 row in set (0.01 sec)
    
    mysql> show variables like 'character_set_server';#字符集
    +----------------------+---------+
    | Variable_name        | Value   |
    +----------------------+---------+
    | character_set_server | utf8mb4 |
    +----------------------+---------+
    1 row in set (0.00 sec)
    
    mysql> show variables like 'collation_server';#排列顺序
    +------------------+--------------------+
    | Variable_name    | Value              |
    +------------------+--------------------+
    | collation_server | utf8mb4_0900_ai_ci |
    +------------------+--------------------+
    1 row in set (0.00 sec)
    
    mysql>

    修改my.cnf后

    [root@localhost mysql_data]# vim /etc/my.cnf
    [root@localhost mysql_data]# /etc/init.d/mysql restart
    Shutting down MySQL..... SUCCESS! 
    Starting MySQL.Logging to '/mysql8/mysql_data/LogError.log'.
    .. SUCCESS! 
    [root@localhost mysql_data]# cat /etc/my.cnf
    [mysqld]
    basedir=/mysql8/mysql
    datadir=/mysql8/mysql_data
    log_error=/mysql8/mysql_data/LogError.log
    character-set-server=utf8
    collation-server=utf8_unicode_ci
    port=3307
    [root@localhost mysql_data]# netstat -ntlp|grep mysql
    tcp6       0      0 :::3307                 :::*                    LISTEN      74678/mysqld        
    tcp6       0      0 :::33060                :::*                    LISTEN      74678/mysqld        
    [root@localhost mysql_data]# mysql -uroot -p
    Enter password: 
    Welcome to the MySQL monitor.  Commands end with ; or g.
    Your MySQL connection id is 8
    Server version: 8.0.13 MySQL Community Server - GPL
    
    Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
    
    Oracle is a registered trademark of Oracle Corporation and/or its
    affiliates. Other names may be trademarks of their respective
    owners.
    
    Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.
    
    mysql> show variables like 'log_error';
    +---------------+---------------------------------+
    | Variable_name | Value                           |
    +---------------+---------------------------------+
    | log_error     | /mysql8/mysql_data/LogError.log |
    +---------------+---------------------------------+
    1 row in set (0.02 sec)
    
    mysql> show variables like 'character_set_server';
    +----------------------+-------+
    | Variable_name        | Value |
    +----------------------+-------+
    | character_set_server | utf8  |
    +----------------------+-------+
    1 row in set (0.01 sec)
    
    mysql>  show variables like 'collation_server%';
    +------------------+-----------------+
    | Variable_name    | Value           |
    +------------------+-----------------+
    | collation_server | utf8_unicode_ci |
    +------------------+-----------------+
    1 row in set (0.00 sec)
    
    mysql> exit
    Bye
    [root@localhost mysql_data]# cat /mysql8/mysql_data/LogError.log 
    2020-06-27T12:22:32.020948Z 0 [System] [MY-010116] [Server] /mysql8/mysql/bin/mysqld (mysqld 8.0.13) starting as process 74678
    2020-06-27T12:22:32.028951Z 0 [Warning] [MY-013242] [Server] --character-set-server: 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
    2020-06-27T12:22:32.029524Z 0 [Warning] [MY-013244] [Server] --collation-server: 'utf8_unicode_ci' is a collation of the deprecated character set UTF8MB3. Please consider using UTF8MB4 with an appropriate collation instead.
    2020-06-27T12:22:33.057709Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
    2020-06-27T12:22:33.133121Z 0 [System] [MY-010931] [Server] /mysql8/mysql/bin/mysqld: ready for connections. Version: '8.0.13'  socket: '/tmp/mysql.sock'  port: 3307  MySQL Community Server - GPL.
    2020-06-27T12:22:33.159759Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Socket: '/tmp/mysqlx.sock' bind-address: '::' port: 33060
    [root@localhost mysql_data]# 
  • 相关阅读:
    spring in action小结4.1
    spring in action小结3 运行时值注入
    python-__init__.py 与模块对象的关系
    Python-常用库扩展
    Qt-优化布局结构
    Python-文件修改器
    C语言-数据结构(一)
    Python-PyQt4学习笔记
    Python-PyQt4学习资料汇总
    Linux-查看C语言手册及man的特殊用法
  • 原文地址:https://www.cnblogs.com/laonicc/p/13199683.html
Copyright © 2020-2023  润新知