二进制安装MySQL 8.0

1. 二进制安装MySQL 8.0

参考MySQL 8.0官方手册：https://dev.mysql.com/doc/refman/8.0/en/binary-installation.html

1.1 卸载之前安装或镜像自带的MySQL或Mariadb

for i in $(rpm -qa|egrep 'mysql|mariadb');do rpm -e --nodeps $i;done
rm -rf /var/lib/mysql && rm -rf /etc/my.cnf && rm -rf /usr/share/mysql && rm -rf /etc/my.cnf.d

1.2 安装依赖包

很多镜像都自带了这些依赖

yum -y install libaio numactl-libs

1.3 下载二进制包并解压

MySQL社(mian)区(fei)版下载地址：https://dev.mysql.com/downloads/mysql/

wget -P /apps https://dev.mysql.com/get/Downloads/MySQL-8.0/mysql-8.0.23-linux-glibc2.12-x86_64.tar.xz
cd /apps
tar xf mysql-8.0.23-linux-glibc2.12-x86_64.tar.xz

嫌解压后的文件夹名称太长的话可创建软连接

ln -s /apps/mysql-8.0.23-linux-glibc2.12-x86_64 /apps/mysql

1.4 创建组和用户

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

递归修改MySQL文件夹的属主和属组

chown -R mysql.mysql /apps/mysql

1.5 配置环境变量

echo 'PATH=/apps/mysql/bin:$PATH' > /etc/profile.d/mysql.sh
source /etc/profile.d/mysql.sh

1.6 创建数据库目录并新建配置文件

配置文件根据自己需求和主机性能来写

mkdir -p /data/mysql/{data,log,binlog,conf,tmp}

cat > /data/mysql/conf/my.cnf <<EOF
[mysqld]
lower_case_table_names =  1 
user = mysql 
server_id =  1 
port =  3306 
default-time-zone =  '+08:00' 
enforce_gtid_consistency = ON 
gtid_mode = ON
binlog_checksum = none 
default_authentication_plugin = mysql_native_password 
datadir =  /data/mysql/data 
pid-file =  /data/mysql/tmp/mysqld.pid 
socket =  /data/mysql/tmp/mysqld.sock 
tmpdir =  /data/mysql/tmp/ 
skip-name-resolve = ON 
open_files_limit =  65535 
table_open_cache =  2000
#################innodb######################## 
innodb_data_home_dir =  /data/mysql/data 
innodb_data_file_path = ibdata1:512M;ibdata2:512M:autoextend 
innodb_buffer_pool_size = 12000M 
innodb_flush_log_at_trx_commit =  1 
innodb_io_capacity =  600 
innodb_lock_wait_timeout =  120 
innodb_log_buffer_size = 8M 
innodb_log_file_size = 200M 
innodb_log_files_in_group =  3 
innodb_max_dirty_pages_pct =  85 
innodb_read_io_threads =  8 
innodb_write_io_threads =  8 
innodb_thread_concurrency =  32 
innodb_file_per_table 
innodb_rollback_on_timeout 
innodb_undo_directory =  /data/mysql/data 
innodb_log_group_home_dir =  /data/mysql/data 
###################session########################### 
join_buffer_size = 8M 
key_buffer_size = 256M 
bulk_insert_buffer_size = 8M 
max_heap_table_size = 96M 
tmp_table_size = 96M 
read_buffer_size = 8M 
sort_buffer_size = 2M 
max_allowed_packet = 64M 
read_rnd_buffer_size = 32M 
############log set################### 
log-error =  /data/mysql/log/mysqld.err 
log-bin =  /data/mysql/binlog/binlog 
log_bin_index =  /data/mysql/binlog/binlog.index 
max_binlog_size = 500M 
slow_query_log_file =  /data/mysql/log/slow.log 
slow_query_log =  1 
long_query_time =  10 
log_queries_not_using_indexes = ON 
log_throttle_queries_not_using_indexes =  10 
log_slow_admin_statements = ON 
log_output = FILE,TABLE 
master_info_file =  /data/mysql/binlog/master.info
EOF

1.7 安装后的设置和测试

参考MySQL 8.0官方手册：https://dev.mysql.com/doc/refman/8.0/en/postinstallation.html

排错请查看错误日志：

tail -f /data/mysql/log/mysqld.err

1.7.1 初始化数据目录

使用--initialize-insecure，不会root生成密码。

mysqld --defaults-file=/data/mysql/conf/my.cnf  --initialize-insecure  --user=mysql

1.7.2 启动mysqld

mysqld_safe --defaults-file=/data/mysql/conf/my.cnf
pgrep mysql

1.7.3 登录MySQL并设置密码

首次登录无需密码（如报错请检查以前的配置文件是否删除干净）

mysql -S /data/mysql/tmp/mysqld.sock

设置密码

ALTER USER 'root'@'localhost' IDENTIFIED BY 'password';

刷新权限列表

flush privileges;

查看登录主机，用户，密码信息

select host,user,authentication_string from mysql.user;

1.7.4 将登录的sock软链接到tmp目录

ln -s /data/mysql/tmp/mysqld.sock /tmp/mysql.sock

1.8 使用systemd管理MySQL服务

参考MySQL官方手册：

• https://dev.mysql.com/doc/mysql-secure-deployment-guide/8.0/en/secure-deployment-post-install.html
• https://dev.mysql.com/doc/refman/8.0/en/using-systemd.html

service配置文件

cat > /usr/lib/systemd/system/mysqld.service <<EOF
[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

Type=notify

TimeoutSec=0

ExecStart=/apps/mysql-8.0.23-linux-glibc2.12-x86_64/bin/mysqld --defaults-file=/data/mysql/conf/my.cnf $MYSQLD_OPTS

EnvironmentFile=-/etc/sysconfig/mysql

LimitNOFILE = 10000

Restart=on-failure

RestartPreventExitStatus=1

Environment=MYSQLD_PARENT_PID=1

PrivateTmp=false
EOF

如启动过mysql需要先将所有mysql进程kill掉，否则会占用sock套接字导致无法使用systemctl启动

pgrep mysql
kill [MYSQLPID]

重新加载systemctl服务,并启动mysqld

systemctl daemon-reload
systemctl start mysqld