1.一些安装规范
-
MySQL安装方式：二进制安装（源码编译安装、yum 、rpm）
-
MySQL运行用户：
mysql:mysql 注意该用户是虚拟用户，只是用于mysql进程运行使用，不允许登录、不创建家目录 useradd -s /sbin/nologin -M mysql -
MySQL目录规范：
数据以单独分区存放，如/data 解压目录 /opt/mysql/mysql-xx.xx 软连接 ln -s /opt/mysql/mysql-xx.xx /usr/local/mysql 数据目录 /data/mysql/mysql+port/{data,logs} 配置文件 /data/mysql/mysql+port/my+port.cnf -
MySQL版本选择
企业版：Enterprise , 一般不做考虑 社区版：这才是我们需要的，毕竟不要钱 包的选择：使用通用的二进制包 版本号选择： ga,尽量选择版本号最后一位是偶数
2.二进制安装
MySQL版本为5.7.30，系统为centos7最小化安装
2.1环境配置
#安装mysql相关的依赖包
yum -y install libaio libaio-devel numactl ncurses-devel
rpm -e --nodeps mariadb-libs
#创建mysql运行使用用户
useradd -s /sbin/nologin -M mysql
#创建目录
mkdir -p /opt/mysql
mkdir -p /data/mysql/mysql3306/{data,logs}
#上传二进制包
[root@localhost ~]# ls /opt/mysql/
mysql-5.7.30-linux-glibc2.12-x86_64.tar.gz
#解压（或者直接解压到目录，然后mv重命名）
[root@localhost ~]# tar xf /opt/mysql/mysql-5.7.30-linux-glibc2.12-x86_64.tar.gz -C /usr/local/
[root@localhost ~]# ln -s /usr/local/mysql-5.7.30-linux-glibc2.12-x86_64/ /usr/local/mysql
2.2配置配置文件
#使用简易配置文件
vim /data/mysql/mysql3306/my3306.cnf
[mysqld]
user=mysql
basedir = /usr/local/mysql
datadir=/data/mysql/mysql3306/data
socket = /tmp/mysql.sock
port = 3306
pid-file = /data/mysql/mysql3306/mysql.pid
user = mysql
skip-name-resolve
#参考
vim /data/mysql/mysql3306/my3306.cnf
[mysqld]
user=mysql
basedir=/usr/local/mysql
datadir=/data/mysql/mysql3306/data
socket = /data/mysql/mysql3306/mysql.sock
server_id = 1
port = 3306
log_error=/data/mysql/mysql3306/logs/error.log
log_bin=/data/mysql/mysql3306/logs/mysql-bin
binlog_format=row
gtid_mode=on
enforce_gtid_consistency=true
log_slave_updates=1
max_connections=1024
wait_timeout=60
sort_buffer_size=2M
max_allowed_packet=32M
join_buffer_size=2M
innodb_buffer_pool_size=128M
innodb_flush_log_at_trx_commit=1
innodb_log_buffer_size=32M
innodb_log_file_size=128M
innodb_log_files_in_group=2
binlog_cache_size=2M
max_binlog_cache_size=8M
max_binlog_size=512M
expire_logs_days=7
slow_query_log=on
slow_query_log_file=/data/mysql/mysql3306/logs/slow.log
long_query_time=0.5
log_queries_not_using_indexes=1
2.3初始化
#更改mysql相关目录属主
chown -R mysql:mysql /data/*
chown -R mysql:mysql /usr/local/mysql*
#配置环境变量
echo ‘export PATH=/usr/local/mysql/bin:$PATH‘ > /etc/profile.d/mysql.sh
. /etc/profile.d/mysql.sh
#初始化
/usr/local/mysql/bin/mysqld --defaults-file=/data/mysql/mysql3306/my3306.cnf --initialize-insecure --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysql/mysql3306/data
2.4启动
mysqld --defaults-file=/data/mysql/mysql3306/my3306.cnf &
[root@localhost ~]# ss -tanlp | grep 3306
LISTEN 0 80 [::]:3306 [::]:* users:(("mysqld",pid=93803,fd=20))
[root@localhost ~]# mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.30 MySQL Community Server (GPL)
Copyright (c) 2000, 2020, 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 databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)
2.5配置开机自启
service方式
cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld
#修改/etc/init.d/mysqld中的下面两行
basedir=/usr/local/mysql
datadir=/data/mysql/mysql3306/data
[root@localhost ~]# service mysqld start
Starting MySQL. SUCCESS!
[root@localhost ~]# service mysqld status
SUCCESS! MySQL running (99795)
[root@localhost ~]# service mysqld stop
Shutting down MySQL.. SUCCESS!
[root@localhost ~]# ss -tanl
State Recv-Q Send-Q Local Address:Port Peer Address:Port
LISTEN 0 100 127.0.0.1:25 *:*
LISTEN 0 128 *:22 *:*
LISTEN 0 100 [::1]:25 [::]:*
LISTEN 0 128 [::]:22 [::]:*
[root@localhost ~]# service mysqld start
Starting MySQL. SUCCESS!
[root@localhost ~]# ss -tanl
State Recv-Q Send-Q Local Address:Port Peer Address:Port
LISTEN 0 100 127.0.0.1:25 *:*
LISTEN 0 128 *:22 *:*
LISTEN 0 100 [::1]:25 [::]:*
LISTEN 0 80 [::]:3306 [::]:*
LISTEN 0 128 [::]:22 [::]:*
#添加开机自启
[root@localhost ~]# chkconfig --add mysqld
[root@localhost ~]# chkconfig --list | grep mysqld
Note: This output shows SysV services only and does not include native
systemd services. SysV configuration data might be overridden by native
systemd configuration.
If you want to list systemd services use ‘systemctl list-unit-files‘.
To see services enabled on particular target use
‘systemctl list-dependencies [target]‘.
mysqld 0:off 1:off 2:on 3:on 4:on 5:on 6:off
#删除开机自启
[root@localhost ~]# chkconfig --del mysqld
[root@localhost ~]# chkconfig --list | grep mysqld
Note: This output shows SysV services only and does not include native
systemd services. SysV configuration data might be overridden by native
systemd configuration.
If you want to list systemd services use ‘systemctl list-unit-files‘.
To see services enabled on particular target use
‘systemctl list-dependencies [target]‘.
[root@localhost ~]#
centos7方式
vim /usr/lib/systemd/system/mysqld.service
[Unit]
Description=MySQL Server
Documentation=man:mysqld(8)
Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html
After=network.target
After=syslog.target
[Install]
WantedBy=multi-user.target
[Service]
User=mysql
Group=mysql
#PIDFile=/data/mysqldata/mysql.pid
# Disable service start and stop timeout logic of systemd for mysqld service.
TimeoutSec=0
# Execute pre and post scripts as root
PermissionsStartOnly=true
# Needed to create system tables
#ExecStartPre=/usr/bin/mysqld_pre_systemd
# Start main service
ExecStart=/usr/local/mysql/bin/mysqld --defaults-file=/data/mysql/mysql3306/my3306.cnf --pid-file=/data/mysql/mysql3306/data/mysql3306.pid
# Use this to switch malloc implementation
#EnvironmentFile=-/etc/sysconfig/mysql
# Sets open_files_limit
LimitNOFILE = 5000
#Restart=on-failure
#RestartPreventExitStatus=1
#PrivateTmp=false
systemctl daemon-reload
systemctl enable mysqld
3.配置文件参数解释
关于配置文件，没有能完美适应所有场景的“最佳配置”
mysql可配置性太强在这种情况来说非常不好，所以在一些基础配置上针对使用场景略作优化即可。没有十足的把握，不要去随意配置自己不明白的参数。而且mysql很多参数默认的值已经是最佳配置了，一般常见的优化只会在innodb或是日常维护管理相关的参数上。
纸上得来终觉浅，多做测试，然后用于生产环境，才是正道，不要过多纠结于某些参数配置，没有太多意义。
示例参照：
[mysqld]
user=mysql
basedir=/usr/local/mysql
datadir=/data/mysql/mysql3306/data
socket = /data/mysql/mysql3306/mysql.sock
server_id = 1
port = 3306
log_error=/data/mysql/mysql3306/logs/error.log
log_bin=/data/mysql/mysql3306/logs/mysql-bin
binlog_format=row
gtid_mode=on
enforce_gtid_consistency=true
log_slave_updates=1 #让从库从主库复制数据时可以写入到binlog日志，为了让从库作为其他从库的主库
max_connections=1024
wait_timeout=60
sort_buffer_size=2M #connection级参数，默认256k（范围在256k-2m），每个连接第一次需要这个buffer时，一次性分配的内存大小,建议不配保持默认，因为高并发可能导致性能下降
max_allowed_packet=32M #设置一结果集缓存的最大值
join_buffer_size=2M #默认256k,如果多次使用join查询导致cost过高，可调至8-16m(尽量在session级别上调整)
innodb_buffer_pool_size=128M #innodb缓冲区大小
innodb_flush_log_at_trx_commit=1 #控制Innodb事务日志写入的过程，0,1（默认）,2；每次提交都会把log buffer写入log file,并刷到磁盘
innodb_log_buffer_size=32M # redo log 的写缓存，设置大一点能减少写操作,也不能设置过大
innodb_log_file_size=128M #控制事务日志ib_logfile的大小,范围5MB~4G
innodb_log_files_in_group=2 #指定有及格日志组，一般2-3个
binlog_cache_size=2M #binlog缓存在内存的大小
max_binlog_cache_size=8M #binlog 能够使用的最大cache 内存大小，不足会报错ERROR 1197
max_binlog_size=512M #如果在开启了bin-log的机器，如主从等，导入大sql文件时要关闭sql_log_bin,让其不写入bin_log,也就不会让从库执行
expire_logs_days=7 #日志保留天数
slow_query_log=on #启用慢查询日志
slow_query_log_file=/data/mysql/mysql3306/logs/slow.log #慢查询日志路径
long_query_time=0.5 #慢查询阈值，单位秒，执行时间超过这个值的将被记录为慢查询日志中
log_queries_not_using_indexes=1 #没有使用索引的sql也将被记录到慢查询日志中