系统配置:
系统:centos7 64位 40G系统盘 500G挂载
目的:安装mysql8.0 并修改datadir(数据存放文件路径),修改错误日志存放路径,开启二进制日志,配置读写记录日志
遇到的问题:
1、更改datadir路径后无法启动
解决:将原来的路径下的东西mv到新的路径下面,默认路径是 /var/lib/mysql 要转移到的路径是 /home/database/mysql/data
命令: mv /var/lib/mysql /home/database/mysql/data
2、启动后无法链接,报错:无法链接/var/lib/mysql/mysql.cock
解决:在/etc/my.cnf mysql的配置文件中加入一下配置
[client]
socket=/home/database/mysql/data/mysql/mysql.sock
配置文件案例
```[mysql.server] user=mysql
For advice on how to change settings please see
http://dev.mysql.com/doc/refman/8.0/en/server-configuration-defaults.html
[mysqld] #
Remove leading # and set to the amount of RAM for the most important data
cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
innodb_buffer_pool_size = 128M
#
Remove the leading "# " to disable binary logging
Binary logging captures changes between backups and is enabled by
default. It's default setting is log_bin=binlog
disable_log_bin
#
Remove leading # to set options mainly useful for reporting servers.
The server defaults are faster for transactions and fast SELECTs.
Adjust sizes as needed, experiment to find the optimal values.
join_buffer_size = 128M
sort_buffer_size = 2M
read_rnd_buffer_size = 2M
#
Remove leading # to revert to previous value for default_authentication_plugin,
this will increase compatibility with older clients. For background, see:
https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_default_authentication_plugin
default-authentication-plugin=mysql_native_password
监听端口配置
port=3306
数据库文件的目录
datadir=/home/database/mysql/data/mysql
缓存目录,用于存储临时表和临时文件
tmpdir=/home/database/mysql/tmp
设置密码认证插件,mysql8.0提供了mysql_native_password和caching_sha2_password两种密码认证,默认为caching_sha2_password
default-authentication-plugin=mysql_native_password
back-log 是操作系统在监听队列中所能保持的连接数,队列保存了在 MySQL 连接管理器线程处理之前的连接.如果你有非常高的连接率并且出现 “connection refused” 报错,你就应该增加此处的值。
back-log=50
最大链接数量
max-connections=100
如果某个用户发起的连接error超过该数值,则该用户的下次连接将被阻塞,直到管理员执行flush hosts ;
max-connect-errors=100
链接超时时间
connect-timeout=30
等待链接关闭时间
wait-timeout=28800
配置二进制日志文件的名称前缀。在未指定绝对路径的情形下,缺省位置保存在数据目录下。
log-bin=mysql-bin
设置binglog最大的体积,达到这个体积binlog会轮转一次,默认为1G
max-binlog-size=1G
参数log_output指定了慢查询输出的格式,默认为FILE,你可以将它设为TABLE,然后就可以查询mysql架构下的slow_log表了
log-output=FILE
是否开启满查询日志
slow-query-log=1
设置慢查询时间
long-query-time=1
设置一般日志,开启这个配置会将所有达到服务器的SQL语句记录下来。默认关闭。
general-log=1 general-log-file=/home/database/mysql/log/mysql.log
客户端默认链接的socket
socket=/home/database/mysql/data/mysql/mysql.sock
日志文件配置
log-error=/home/database/mysql/log/mysql_error.log pid-file=/var/run/mysqld/mysqld.pid [client] socket=/home/database/mysql/data/mysql/mysql.sock