腾讯云mysql的5.7_云服务器（腾讯云）从零开始部署记录（4）之mysql5.7配置

前言，此配置为我在云服务器操作的，具体的看各自实际情况操作配置

1、查找mysql的配置文件my.cnf

#yum安装的mysql配置文件目录一般为:/etc/my.cnf

#使用命令查找，如下：

[root@VM_0_6_centos ~]# find / -name my.cnf

/etc/my.cnf

#查询mysql安装目录，如下：

[root@VM_0_6_centos ~]# which mysql

/usr/bin/mysql

#找到安装目录，则使用以下命令查看配置文件默认路径，一般是第一个，注：前面的目录/usr/bin/mysql为上一个命令结果

[root@VM_0_6_centos ~]# /usr/bin/mysql --verbose --help |grep -A 1 'Default options'

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

#找到mysql配置文件，使用命令查看一下my.cnf的内容，可见其中已经配置了部分内容

----------------------------my.cnf内容分割线---------------------------

[root@VM_0_6_centos ~]# vi /etc/my.cnf

# For advice on how to change settings please see

# http://dev.mysql.com/doc/refman/5.7/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 leading # to turn on a very important data integrity option: logging

# changes to the binary log between backups.

# 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 = 2

datadir=/var/lib/mysql

socket=/var/lib/mysql/mysql.sock

# Disabling symbolic-links is recommended to prevent assorted security risks

symbolic-links=0

log-error=/var/log/mysqld.log

pid-file=/var/run/mysqld/mysqld.pid

----------------------------my.cnf内容分割线---------------------------

linux命令小注备忘：

vi /etc/my.cnf #vi为编辑命令，后面跟文件名(可带路径)

i #vi打开文件后，按一下i，文件最后出现-- INSERT --字样，表示当前为可编辑状态

Esc #退出可编辑状态

Shift + ： #按Shift键后，再按冒号键(字母L右边那个)，此操作后，文档最后出现可输入行，行开始为：

q! #q后面跟一个英文叹号，表示强制退出，不保存本次所有编辑修改的内容

wq! #表示保存修改内容

------------------------------------------------

Ctrl + c #退出命令操作

2、修改配置，开启相关的log，部分(未完整版)

# For advice on how to change settings please see

# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html

#client部分

[client]

port = 3507

socket = /var/lib/mysql/mysql.sock #复制下方的即可

host = localhost

user = root

password = '12345678' #密码

[mysqld]

port = 3507 #修改新端口，不使用3306默认端口

character_set_server=utf8 #配置默认编码为utf8

init_connect='SET NAMES utf8'

#

# 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 leading # to turn on a very important data integrity option: logging

# changes to the binary log between backups.

# 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

datadir=/var/lib/mysql #原有，勿动，修改比较麻烦，为数据存储目录

socket=/var/lib/mysql/mysql.sock #原有，勿动

# Disabling symbolic-links is recommended to prevent assorted security risks

symbolic-links=0 #原有，勿动

log-error=/var/log/mysqld.log #原有，勿动，错误日志

pid-file=/var/run/mysqld/mysqld.pid #原有，勿动

# 禁止MySQL对外部连接进行DNS解析，使用这一选项可以消除MySQL进行DNS解析的时间。但需要注意，如果开启该选项，

# 则所有远程主机连接授权都要使用IP地址方式，否则MySQL将无法正常处理连接请求

skip-name-resolve

#最大连接数

max_connections = 1000

#达到最大错误数禁止连接

max_connect_errors = 6000

#用于标识该语句最初是从哪个server写入的

server-id = 1

#开启binlog

log_bin = /var/lib/mysql/binlog/mysql-bin #存储位置

binlog_format = mixed #混合模式

expire_logs_days = 30 #超过30天的自动删除

sync-binlog = 100 #执行N次写入后，与硬盘同步

#一个事务，在没有提交的时候，产生的日志，记录到Cache中；

#等到事务提交需要提交的时候，则把日志持久化到磁盘。默认binlog_cache_size大小32K

binlog_cache_size = 8M

# 这个值(默认8)表示可以重新利用保存在缓存中线程的数量，当断开连接时如果缓存中还有空间，

# 那么客户端的线程将被放到缓存中，

# 如果线程重新被请求，那么请求将从缓存中读取,如果缓存中是空的或者是新的请求，

# 那么这个线程将被重新创建,如果有很多新的线程，

# 增加这个值可以改善系统性能.通过比较Connections和Threads_created状态的变量，

# 可以看到这个变量的作用。(–>表示要调整的值)

# 根据物理内存设置规则如下：

# 1G —> 8

# 2G —> 16

# 3G —> 32

# 大于3G —> 64

thread_cache_size = 64

#慢查询相关

slow-query-log = 1

long_query_time = 5 #超过5s为慢查询

slow-query-log-file = /var/lib/mysql/slowlogs/mysql-slow.log

#不区分大小写

lower_case_table_names = 1

#有存储过程和函数需要配置

log_bin_trust_function_creators=1

#开启兼容性，未设置mysqldump备份报错

show_compatibility_56 = 1

3、上面配置为另外一台服务器配置，直接复制过来使用，先关闭mysql，再重启报错，错误记录：

#修改my.cnf

#关闭，然后重启，结果报错，注：下面提示的方式查看报错详情不够明确，直接将错误log拉出来查看即可

[root@VM_0_6_centos ~]# systemctl stop mysqld

[root@VM_0_6_centos ~]# systemctl start mysqld

Job for mysqld.service failed because the control process exited with error code. See "systemctl status mysqld.service" and "journalctl -xe" for details.

[root@VM_0_6_centos ~]# systemctl start mysqld

Job for mysqld.service failed because the control process exited with error code. See "systemctl status mysqld.service" and "journalctl -xe" for details.

#从配置文件上找到错误log的目录，打开log查看(通过FTP下载到本地看)

log-error=/var/log/mysqld.log

#错误内容如下：

----------------------------------

2019-03-18T08:52:51.184941Z 0 [Note] /usr/sbin/mysqld (mysqld 5.7.25-log) starting as process 22720 ...

mysqld: File '/var/lib/mysql/binlog/mysql-bin.index' not found (Errcode: 13 - Permission denied)

2019-03-18T08:52:51.186680Z 0 [ERROR] Aborting

----------------------------------

#可见错误是因为logbin目录的问题，查看配置文件logbin的配置

log_bin = /var/lib/mysql/binlog/mysql-bin

#经过查询，发现是因为没有创建binlog目录，创建后还是不行，原来是没有文件夹权限，需要进行授权binlog文件夹

[root@VM_0_6_centos ~]# cd /var/lib/mysql #进入binlog上级目录

[root@VM_0_6_centos mysql]# chown -R mysql:mysql binlog #执行授权binlog目录

[root@VM_0_6_centos mysql]# systemctl start mysqld #重启成功