Ubuntu Linux下安装MySQL5.7

MySQL通用Linux二进制安装包用于在Linux系统上安装MySQL。可以根据我们的需求，定制安装MySQL,比如可以设置数据存储目录，日志存储目录等。

准备

如果系统之前安装过mysql，版本可能是比较旧的，需要在安装之前确认下。可以使用如apt,yum包管理工具，将mysql卸载。并注意/etc/my.cnf，mysql数据存放目录，将数据备份好，然后再删除这些文件或目录。

mysql需要依赖libaio库，在安装mysql前确认已经安装好libaio库

• centos:

  yum search libaio  # search for info
  yum install libaio # install library

• ubuntu:

  apt-cache search libaio # search for info
  apt-get install libaio1 # install library

下载MySQL二进制安装包

下载64位mysql5.7.27 linux通用二进制安装包

wget --no-check-certificate https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.27-linux-glibc2.12-x86_64.tar.gz

二进制安装包清单

目录	目录中的文件
bin	mysqld服务端程序,mysql客户端程序，等其他工具程序
docs	mysql文档
man	man手册
include	header头文件
share	用于数据库安装的错误消息、字典和SQ
support-files	各种支持文件

创建用户组、用户

groupadd mysql
useradd -r -g mysql -s /bin/false mysql

此用户仅仅用于mysql服务，而不用于系统登录，所以使用useradd -r和-s /bin/false命令选项来创建没有登录权限的用户。

解压MySQL安装包

解压mysql安装包到/user/local目录下，并设置软连接，使得mysql的basedir为/usr/local/mysql

tar zxvf /path/to/mysql-5.7.27-linux-glibc2.12-x86_64.tar.gz -C /usr/local
cd /usr/local
ln -s mysql-5.7.26-linux-glibc2.12-x86_64 mysql

/path/to改为mysql安装包所在目录

将bin目录添加到PATH环境变量中

export PATH=$PATH:/usr/local/mysql/bin
echo "export PATH=$PATH:/usr/local/mysql/bin" >> ~/.bashrc
echo "export PATH=$PATH:/usr/local/mysql/bin" >> /etc/profile
source /etc/profile

mysql目录、文件规划

名称	目录或文件	软链	实际目录
数据目录datadir	/data/mysql/data	否
参数配置my.cnf	/usr/local/mysql/etc/my.cnf	否
日志log目录	/usr/local/mysql/log	是	/data/mysql/log
错误日志log-error	/usr/local/mysql/log/mysql_error.log	否
慢查询日志slow_query_log_file	/usr/local/mysql/log/mysql_slow_query.log	否
二进制日志log-bin	/usr/local/mysql/binlogs/mysql-bin	是	/data/mysql/binlogs/mysql-bin
套接字socket文件	/usr/local/mysql/run/mysql.sock	否
pid文件(进程id)	/usr/local/mysql/run/mysql.pid	否

默认情况下socket文件， pid文件是存在datadir的，建议还是分开一个目录，取名run目录好一点

创建所需目录并设置权限

mkdir -p /usr/local/mysql/{etc,run}
mkdir -p /data/mysql/{data,binlogs,log}
chown -R mysql.mysql /data/mysql
chown -R mysql.mysql /usr/local/mysql/{etc,run}

配置my.cnf

文件放在/usr/local/mysql/etc/my.cnf，放此目录，会自动加载该配置文件的

[client]
port = 3306
socket = /usr/local/mysql/run/mysql.sock

[mysqld]

##########################
#GENERAL
##########################
user = mysql
port = 3306
server_id = 1
skip-name-resolve
bind-address= 0.0.0.0
default_storage_engine = InnoDB
socket = /usr/local/mysql/run/mysql.sock
pid-file = /usr/local/mysql/run/mysqld.pid
max_allowed_packet=32M
max_connections = 1024
open_files_limit = 65535
#default-time-zone='+08:00'
explicit_defaults_for_timestamp=true

##########################
# character set
##########################
character_set_server = utf8mb4
collation-server = utf8mb4_general_ci
init_connect='SET NAMES utf8mb4'

##########################
# DATA STORAGE 
##########################
basedir= /usr/local/mysql
datadir = /data/mysql/data

##########################
# INNODB
##########################
innodb_file_per_table = 1
innodb_buffer_pool_size = 512M
innodb_log_buffer_size = 16M
innodb_log_file_size = 128M
innodb_tmpdir= /data/mysql/tmp

##########################
# MyISAM
##########################
key_buffer_size = 64M

##########################
# Logging
##########################
# mysql log timezone use system timezone
log_timestamps=SYSTEM
log_error = /data/mysql/log/mysql_error.log

##########################
# log bin
##########################
log-bin = /data/mysql/binlogs/mysql-bin
sync-binlog = 1
binlog_format = row
binlog_row_image = FULL
expire_logs_days = 15
master_info_repository = TABLE
relay_log_info_repository = TABLE


##########################
# log relay
##########################
relay-log = /data/mysql/binlogs/relay-bin
relay_log_recovery = on
max_relay_log_size = 1G
log_slave_updates = 1

##########################
# gtid
##########################
gtid_mode = on
enforce_gtid_consistency = on
replicate_ignore_db=mysql
replicate_ignore_db=information_schema
replicate_ignore_db=performation_schema
replicate_ignore_db=sys

##########################
# General logs (only enable for debugging – it use too much I/O)
##########################
#general-log = on
#general-log-file = /data/mysql/log/general-query.log

##########################
# Slow query logs (optional)
##########################
slow_query_log = on
long_query_time= 3
slow_query_log_file = /data/mysql/log/slow-query.log

初始化mysql server

查看初始化参数

mysqld --verbose --help |more

• 初始化数据库

  cd /user/local/mysql
  mkdir mysql-files
  chown mysql:mysql mysql-files
  chmod 750 mysql-files
  bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysql/data

在日志文件理会提示一个临时密码，记录这个密码

grep 'temporary password' /data/mysql/log/mysql_error.log

• 生成ssl

mysql_ssl_rsa_setup --basedir=/usr/local/mysql --datadir=/data/mysql/data

启动mysql服务

mysqld_safe --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysql/data --defaults-file=/usr/local/mysql/etc/my.cnf &

或

mysqld_safe --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysql/data &

可以不用指定–defaults-file，会加载到$MYSQL_HOME/etc/my.cnf这个配置文件

修改密码

mysql -uroot -p
输入上一步查到的临时密码

set password=password('123456');
grant all privileges on *.* to root@'%' identified by '123456' with grant option;
flush privileges;

mysql作为系统服务

非systemctrl管理（不推荐）

#Next command is optional
cp support-files/mysql.server /etc/init.d/mysqld

修改/etc/init.d/mysqld的basedir,datadir等配置
chomod 755 /etc/init.d/mysqld

下面介绍使用 systemctrl来管理mysql服务

systemctl管理（强烈推荐）

创建 /etc/systemd/system/mysqld.service

[Unit]
Description=MySQL Server
Documentation=man:mysqld(5.7)
Documentation=https://dev.mysql.com/doc/refman/5.7/en/using-systemd.html
After=network.target
After=syslog.target

[Install]
WantedBy=multi-user.target

[Service]
User=mysql
Group=mysql
#PIDFile=/usr/local/mysql/run/mysqld.pid
#ExecStart=
ExecStart=/usr/local/mysql/bin/mysqld_safe --defaults-file=/usr/local/mysql/etc/my.cnf
LimitNOFILE=65535

设置开机启动

systemctl daemon-reload
systemctl enable mysqld

启动、停止、查看日志

systemctrl start mysqld
systemctrl stop mysqld
journalctl -u mysqld

附录

单机多实例的安装配置见https://www.jb51.net/article/103843.htm，需要注意版本差异

参考

https://lalitvc.wordpress.com/2019/03/04/mysql-5-7-binary-install-on-linux/
https://dev.mysql.com/doc/refman/5.7/en/binary-installation.html
https://dev.mysql.com/doc/refman/5.7/en/postinstallation.html
https://dev.mysql.com/doc/refman/5.7/en/using-systemd.html#systemd-mysql-configuration
https://dev.mysql.com/doc/refman/5.7/en/mysqld-safe.html#option_mysqld_safe_malloc-lib
https://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html
https://blog.csdn.net/zml3721/article/details/79090983
https://www.jianshu.com/p/0d628b2f7476
https://www.jb51.net/article/103843.htm
https://blog.pythian.com/manage-multiple-mysql-binary-installations-with-systemd/
https://www.linuxidc.com/Linux/2017-10/147829.htm
https://blog.csdn.net/l1028386804/article/details/87996449