mysql配置文件构成以及具体的配置demo

喜欢ヅ旅行 2024-04-19 00:56 12阅读 0赞

## 一、前言 ##

在了解完`mysql`的各项配置参数，并且设计出适合自己（`2`核`8G`机器）的配置后，博主开始了正式的配置，不过在正式的配置过程中还是遇到了不少问题，这里记录一下配置文件的构成以及具体的配置`demo`。

关于`mysql`的配置参数相关，可以参考上一篇文章：[mysql配置参数调优(8GB内存和64GB内存)][mysql_8GB_64GB]

## 二、mysql配置文件位置 ##

大家应该都知道，一般我们配置的`my.cnf`都会放在`/etc`下面，但是新安装的`mysql`是需要我们自己找到配置文件的。

### 1、查看mysql配置文件位置 ###

mysql --help | grep 'Default options' -A 1                  //输入这条命令
    Default options are read from the following files in the given order:
    /etc/my.cnf /etc/mysql/my.cnf /usr/etc/my.cnf ~/.my.cnf             //根据优先级，首先读取/etc/my.cnf,然后读取/etc/mysql/my.cnf 等

可以看到`mysql`读取配置文件的顺序，我们可以去这些路径下找到我们的配置文件

### 2、本地配置文件位置 ###

关于寻找配置文件的文章很多，这里就不一一细说了。参考：[mysql 查看当前使用的配置文件my.cnf的方法][mysql _my.cnf] ，这里列出来我本地的配置文件位置，奇葩的位置。

**（1）** 首先是/etc/my.cnf 是不存在  
**（2）** cat /etc/mysql/my.cnf

发现下面有句话：

!includedir /etc/mysql/conf.d/
    !includedir /etc/mysql/mariadb.conf.d/

意思就是具体的配置分散在`xxconf.d`文件夹下。这里的`conf.d`文件夹下是没什么东西，因为本地是mariadb数据库，所以配置文件在`mariadb.conf.d`

**（3）** 配置文件组成

/etc/mysql/mariadb.conf.d# ls
    50-client.cnf  50-mysql-clients.cnf  50-mysqld_safe.cnf  50-server.cnf

这里可以根据文件名判断，原来咱们`mysql`配置文件的`[client]`，`[mysqld]`，`[mysqld_safe]`都在一个文件里面的，但是我这边默认的是分开配置了，这样总感觉不大好管理的样子，因此决定新建`/etc/my.cnf`，在`my.cnf`里面统一配置就完事了。

### 3、宏观上查看配置 ###

mysqladmin variables -p 
    //输入数据库用户密码即可

这个命令列出来的是所有的参数，看着挺乱的，建议还是从配置文件看起吧，咱们只需要设置那些影响比较大的参数就可以，其他参数用`mysql`默认的就足够了。

## 三、mysql配置文件具体构成以及配置demo ##

### 1、my.cnf的构成 ###

`mysql`读取配置文件的时候，如果咱们设置了参数，那设置的这部分参数的值就会改变。至于没有设置到的部分，采用的依旧是`mysql`默认的参数。配置文件里面主要分为：

[client]  ：客户端参数，可以放一些共同使用的参数，比如编码格式等
    [mysqld]：服务端参数，就是咱们上面列出来的这些参数，当然，还有mysql本身的一些配置
    [mysqldump] : 导出文件时候使用的，是逻辑备份，备份时是执行的sql语句
    [mysql]：mysql命令行工具设置
    [mysqld_safe] ：mysqld_safe程序调用mysqld程序来启动mysql服务,[mysqld_safe]会覆盖mysqld部分中的参数
    [mysqlhotcopy] ：mysqlhotcopy使用lock tables、flush tables和cp或scp来快速备份数据库.它是备份数据库或单个表最快的途径,完全属于物理备份,但只能用于备份MyISAM存储引擎和运行在数据库目录所在的机器上.
    [myisamchk]：使用myisamchk实用程序来获得有关你的数据库桌表的信息、检查和修复他们或优化他们

以上我们在查看配置文件的时候，经常出现的几个分类，并不是强制要求全部配置它们，大家只需要知道这些分类都代表什么意思就好了。我们在需要修改的那些参数都在`[mysqld]`下面。

**\[mysqld\_safe\]解释：** 实际上的`mysqld_safe`是一个启动脚本，用它启动实质是启动`mysqld`守护进程，如果`mysqld` 不存在了，自动把`mysqld`拉起来。`mysqld_safe`可以读取`my.cnf`文件的`[mysqld]`，`[mysqld_safe]`，`[server]`，`[safe_mysqld]`中配置,咱们在`[mysqld_safe]`指定`sock`文件位置就行。

### 2、配置demo ###

在`/etc`下面新建`my.cnf`，然后添加配置内容即可。

[client]
    port=3306
    default-character-set=utf8mb4
    socket          = /var/run/mysqld/mysqld.sock  #根据自己sock文件的位置设置
    
    [mysqld]
    character_set_server = utf8mb4
    max_allowed_packet=32M
    #interactive_timeout = 2880
    
    #新定义的
    key_buffer_size=64M 
    table_open_cache=2048	
    sort_buffer_size=4M
    net_buffer_length=16384
    read_buffer_size=1M 
    read_rnd_buffer_size=512k
    myisam_sort_buffer_size=128M
    myisam_max_sort_file_size=10G
    thread_cache_size=64
    query_cache_size=0 
    tmp_table_size=32M 
    explicit_defaults_for_timestamp=ON
    max_connections=5000 
    max_connect_errors=500000
    open_files_limit=65535	
    expire_logs_days=10
    
    #innodb相关
    innodb_file_per_table=1	#这里设置为on的话，值为1
    innodb_data_file_path=ibdata1:12M:autoextend	 #格式不要错
    innodb_buffer_pool_size =2G	
    innodb_log_file_size=256M	
    innodb_log_buffer_size=32M	
    innodb_flush_log_at_trx_commit=2  		
    sync_binlog=1000		
    innodb_lock_wait_timeout=30	 		
    back_log=	500		
    
    [mysqldump]
    # 不要在将内存中的整个结果写入磁盘之前缓存.
    quick
    max_allowed_packet = 32M
    
    [mysql]
    auto-rehash  # 开启tab补齐功能
    
    [mysqlhotcopy]
    interactive-timeout
    
    [mysqld_safe]
    # 增加每个进程的可打开文件数量.
    # 警告: 确认你已经将全系统限制设定的足够高!
    # 打开大量表需要将此值设b
    open-files-limit = 65535
    socket          = /var/run/mysqld/mysqld.sock

这个配置文件在博主服务器是正常工作的，查看各项参数也都是咱们配置的大小。

## 四、运行配置文件出现的问题 ##

### 1、innodb\_data\_file\_path报错： ###

Could not open or create the system tablespace. If you tried to add new data files to the system tablespace, and it failed here, you should now edit innodb_data_file_path in my.cnf back to what it was

#### （1）出错原因 ####

`innodb_data_file_path`设置的不匹配。本来是打算设置`innodb_data_file_path=ibdata1:64M:autoextend` ，但是根据报错信息来看，设置的过于大了，和本来的`ibdata1`的大小不一致。

#### （2）查看ibdata1的大小： ####

进入：`/var/lib/mysql` ，进入之后会发现一个`ibdata1`文件，可以通过 `ls -ll` 查看文件大小。

这个文件大小`mysql`官方在`5.6.7`之前规定是`10M`，`5.6.7`之后是规定`12M`。如果想要像`mysql`服务器一样设置比较大一点的`ibdata1`，那必须的要在建表之初就修改`my.cnf`的`innodb_data_file_path`参数。如果你是建表之后修改，那就会报错，改为`12M`就不报错。

#### （3）innodb\_file\_per\_table选项 ####

在新版的`Mysql`中已经通过`innodb_file_per_table`这个选项来解决`ibdata1`不够大的问题了，开启该选项后，每个`InnoDB`表的索引和数据都会按`*.ibd`命名存储到各个数据库中，这个参数默认不开启。不过咱们已经给他开启过了，所以大部分情况还是可以顶得住的，嫌麻烦的话，这个`innodb_file_per_table`设置打开，然后就不用管`innodb_data_file_path`的设置了，用默认的就行。如果不嫌麻烦的话，  
参考：https://www.cnblogs.com/MYSQLZOUQI/p/3654644.html 大概就是先导表导数据，然后设置`mysql`参数，设置完再把表导入进去。

### 2、mysql的auto-rehash ###

#### （1）prompt=\[\\h\]\[\\u\]@\\d\\r:\\m:\\s ####

这个参数博主看到有人是配置了的，不过个人感觉没必要设置，设置完进去`mysql`会自动带上`host`和`user`:，效果十分恐怖，例如：`3: >host][ljf]@(none)`，很丑的。。

#### （2）auto-rehash ####

开启`tab`补齐功能，设置自动补齐的话，启动要带上参数，例如：`mysql –uroot -p --auto-rehash` 经过试验，这个参数没啥用，只能自动补全表名，如果表名很长，表很多的话，可以试试。

### 3、设置成功 ###

MariaDB [hx]> show global variables like 'innodb_buffer_pool_size';
    +-------------------------+------------+
    | Variable_name           | Value      |
    +-------------------------+------------+
    | innodb_buffer_pool_size | 2147483648 |
    +-------------------------+------------+

缓冲区已经是`2G`了，代表新配置的参数生效。

**end**

[mysql_8GB_64GB]: https://blog.csdn.net/LJFPHP/article/details/100751502
[mysql _my.cnf]: https://blog.csdn.net/fdipzone/article/details/52705507