mysql5.7.40数据库部署安装

一、下载mysql安装包

官网下载mysql安装包：MySQL :: Download MySQL Community Server (Archived Versions)

以下操作需要操作截图的看文章最后(不过应该没有创建用户和组的部分-_-)。

二、安装mysql

#将mysql安装包上传至linux服务器；此处默认上传至/data路径下
#解压mysql安装包
tar -zxvf mysql-5.7.40-linux-glibc2.12-x86_64.tar.gz
#如果觉得文件夹名字太长可以选择重命名(这里就先不改了)

三、添加mysql组和mysql用户

groupadd mysql
useradd -r -g mysql mysql

四、安装前的操作

#进入解压路径创建data目录
cd mysql-5.7.40-linux-glibc2.12-x86_64
mkdir data
#创建mysqld进程存放目录run
mkdir run
#修改mysql路径所属用户和组
chown -R mysql.mysql /data/mysql-5.7.40-linux-glibc2.12-x86_64
#/tmp访问权限
chmod 777 /tmp

#在mysql-5.7.40-linux-glibc2.12-x86_64路径下创建my.cnf文件粘贴下方内容(文件中有关路径的内容修改为实际部署路径即可，端口默认3306)
vi my.cnf
====================================================================
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html
# *** DO NOT EDIT THIS FILE. It's a template which will be copied to the
# *** default location during install, and will be replaced if you
# *** upgrade to a newer version of MySQL.

[client]

port = 3306
socket = /tmp/mysql.sock
[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

# These are commonly set, remove the # and set as required.
# basedir = .....
# datadir = .....
# port = .....
# server_id = .....
# socket = .....

basedir = /data/mysql-5.7.40-linux-glibc2.12-x86_64
datadir = /data/mysql-5.7.40-linux-glibc2.12-x86_64/data
port = 3306
socket = /tmp/mysql.sock

lower_case_table_names = 1
max_connections = 3000
open_files_limit = 65535

server_id = 153
log-bin = mysql-bin
binlog_format=mixed
#expire-logs-days=90

relay_log=mysqld-relay-bin
log-slave-updates=ON

auto-increment-increment = 2
auto-increment-offset = 1
log-error = /data/mysql-5.7.40-linux-glibc2.12-x86_64/data/mysql.log

#是否启用慢查询日志，1为启用，0为禁用
slow_query_log = 1
#指定慢查询日志文件的路径和名字，可使用绝对路径指定；默认值是'主机名_slow.log'，位于datadir目录
slow_query_log_file = slow.log
#指定达到多少秒才算慢查询
long_query_time = 3
#SQL语句检测的记录数少于设定值的语句不会被记录到慢查询日志，即使这个语句执行时间超过了long_query_time的阈值
#min_examined_row_limit = 100
#记录没有使用索引的查询语句。！可能导致日志文件激增，谨慎使用。配合log_throttle_queries_not_using_indexes 使用。 
#log_queries_not_using_indexes=on 
#表示每分钟允许记录到slow log的且未使用索引的sql语句次数。配合long_queries_not_using_indexes开启使用。
#log_throttle_queries_not_using_indexes = 10

log_bin_trust_function_creators=1

skip-name-resolve
skip-external-locking

# 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 

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES 

#innodb settings
innodb_file_per_table=1
#70-80 percent of system memory
innodb_buffer_pool_size =5G

#MySQL在完成某些join（连接）需求的时候，为了减少参与join的“被驱动表”的读取次数以提高性能，需要使用到join buffer来协助完成join操作，当join buffer 太小，MySQL不会将该buffer存入磁盘文件而是将join buffer中的结果与需求join的表进行操作，然后清空join buffer中的数据，继续将剩余的结果集写入次buffer中
join_buffer_size = 512M
#MySQL读入缓冲区的大小
read_buffer_size = 64M
#MySQL的随机读缓冲区大小
read_rnd_buffer_size = 512M
#MySQL的顺序读缓冲区大小
sort_buffer_size = 32M

#三个取值：0,1,2，分别代表了off、on、demand
query_cache_type = 1
query_cache_size = 512M
innodb_log_buffer_size = 64M
key_buffer_size = 512M
max_allowed_packet = 128M

#Turn off ssl
skip_ssl
====================================================================

 

五、安装mysql

#在mysql-5.7.40-linux-glibc2.12-x86_64按顺序执行以下内容
./bin/mysqld --defaults-file=/data/mysql-5.7.40-linux-glibc2.12-x86_64/my.cnf --initialize-insecure --user=mysql --basedir=/data/mysql-5.7.40-linux-glibc2.12-x86_64 --datadir=/data/mysql-5.7.40-linux-glibc2.12-x86_64/data
./bin/mysql_ssl_rsa_setup --defaults-file=/data/mysql-5.7.40-linux-glibc2.12-x86_64/my.cnf --user=mysql --basedir=/data/mysql-5.7.40-linux-glibc2.12-x86_64 --datadir=/data/mysql-5.7.40-linux-glibc2.12-x86_64/data

六、创建链接

#修改mysql.server文件
cd support-files
cp mysql.server ../
cd ..
#打开编辑修改一下三块内容
vi mysql.server
====================================================
basedir=/data/mysql-5.7.40-linux-glibc2.12-x86_64
datadir=/data/mysql-5.7.40-linux-glibc2.12-x86_64
mysqld_pid_file_path=/data/mysql-5.7.40-linux-glibc2.12-x86_64/run/mysql.pid
====================================================
#保存后执行以下内容
ln -s /data/mysql-5.7.40-linux-glibc2.12-x86_64/mysql.server /etc/init.d/mysql
service mysql start
ln -s /data/mysql-5.7.40-linux-glibc2.12-x86_64/bin/mysql /usr/bin/mysql
ln -s /data/mysql-5.7.40-linux-glibc2.12-x86_64/bin/mysqldump /usr/bin/mysqldump
ln -s /data/mysql-5.7.40-linux-glibc2.12-x86_64/bin/mysqladmin /usr/bin/mysqladmin

七、添加密码及远程访问权限

#因为安装时默认没有设置密码，所以直接后台登录，输入密码部分直接回车即可
./bin/mysql -uroot -p
#输入密码部分直接回车
#设置新密码然后刷新
set password=password('My9Sql6?a');
grant all privileges on *.* to root@'%' identified by 'My9Sql6?a';
flush privileges;
#添加远程访问权限
use mysql;
update user set host='%' where user = 'root';
flush privileges;
#退出mysql后台
quit;
#重启mysql生效
service mysql restart
#最后远程连接验证即可