第二十三章 Centos7 下 Mysql 8.0.24编译安装

一、需求

公司由于安装jira服务，mariadb数据库已不能满足jira的使用，因此需安装新的mysql8.0，迁移mariadb的数据来保证jira的正常安装。

二、准备工作

#准备工作
下面是安装要求，已安装的可以跳过。
cmake
boost
gcc
ncurses
openssl
bison
doxygen

三、安装依赖包

[root@dxg-ml software]# yum install -y ncurses-devel libaio-devel autoconf cmake gcc gcc-c++ glibc  bzip2 gmp-devel mpfr-devel libmpc-devel wget glibc-headers openssl-devel

四、cmake安装

1.概述

linux下安装cmake，目前最新的版本为3.20.3。
安装的方式一共有三种：通过软件包仓库安装，通过编译好的版本进行安装，从源码手动编译安装。

2.仓库方式安装

#1.安装cmake
[root@dxg-ml ~]# yum -y install  cmake

#2.查看cmake版本
[root@dxg-ml ~]# cmake -version
cmake version 2.8.12.2

3.下载编译好的安装包安装

先去官网下载：https://cmake.org/download/

#1.下载最新cmake安装包
[root@dxg-ml ~]# cd /data/software
[root@dxg-ml software]# wget https://github.com/Kitware/CMake/releases/download/v3.20.3/cmake-3.20.3-linux-x86_64.tar.gz

#2.解压并进入目录：
[root@dxg-ml software]# tar xf cmake-3.20.3-linux-x86_64.tar.gz  -C /usr/local/
[root@dxg-ml /usr/local]# ll
总用量 42912
drwxr-xr-x   6 root  root      4096 6月  11 10:10 cmake-3.20.3-linux-x86_64

#3.重命名cmake
[root@dxg-ml /usr/local]# mv cmake-3.20.3-linux-x86_64 cmake

#4.配置环境变量
[root@dxg-ml /usr/local]# vim /etc/profile.d/cmake.sh
export PATH=/usr/local/cmake/bin:$PATH

#5.刷新环境变量
[root@dxg-ml /usr/local]# source  /etc/profile

#查看cmake版本
[root@dxg-ml /usr/local]# cmake -version
cmake version 3.20.3

CMake suite maintained and supported by Kitware (kitware.com/cmake).

ps：建议这边不要采用添加软连接方式，会报：-bash: /bin/cmake: 符号连接的层数过多 这个错误

4 手动编译安装

先去官网下载：https://cmake.org/download/

#下载源码包
[root@dxg-ml ~]# cd /data/software
[root@dxg-ml software]# wget https://github.com/Kitware/CMake/releases/download/v3.20.3/cmake-3.20.3.tar.gz

#解压源码包
[root@dxg-ml software]# tar -zxvf cmake-3.20.3.tar.gz -C /data
[root@dxg-ml software]# cd /data/cmake-3.20.3/

#配置
[root@dxg-ml cmake-3.20.3]# ./bootstrap --prefix=/usr/local/cmake
... ...
-- Found Curses: /usr/lib64/libncurses.so  
-- Looking for elf.h
-- Looking for elf.h - found
-- Looking for use_default_colors
-- Looking for use_default_colors - found
-- Looking for a Fortran compiler
-- Looking for a Fortran compiler - /bin/f95
-- Performing Test run_pic_test
-- Performing Test run_pic_test - Success
-- Performing Test run_inlines_hidden_test
-- Performing Test run_inlines_hidden_test - Success
-- Configuring done
-- Generating done
-- Build files have been written to: /opt/software/cmake-3.20.3
---------------------------------------------
CMake has bootstrapped.  Now run gmake.

ps: 加个安装位置即可,如果失败，请根据错误信息自行判断，这里可能遇到的问题是gcc与g++的版本不匹配，因为我是先更新gcc到了9.2.0，g++的软链忘记改，导致了g++是6.3.0的版本，而gcc是9.2.0的版本。

#编译
[root@dxg-ml cmake-3.20.3]# make -j 1

ps:6为CPU核心数，可通过命令lscpu查看CPU核心数，-j可以为并行优化，默认为1,安装时有进度提示.

#安装
[root@dxg-ml cmake-3.20.3]# make install

#配置环境变量 
[root@dxg-ml cmake-3.20.3]# vim /etc/profile.d/cmake.sh
export PATH=/usr/local/cmake/bin:$PATH

#刷新环境变量
[root@dxg-ml cmake-3.20.3]# source  /etc/profile

#查看cmake版本
[root@dxg-ml cmake-3.20.3]# cmake -version
cmake version 3.20.3

CMake suite maintained and supported by Kitware (kitware.com/cmake).

五、升级GCC版本

mysql8.0要求gcc版本要5.5以上，CentOS7默认的gcc版本为4.8.5.所以这里要升级GCC版本。

#版本下载地址
http://ftp.gnu.org/gnu/gcc/

1.查看GCC版本

[root@dxg-ml software]# gcc -v
使用内建 specs。
COLLECT_GCC=gcc
COLLECT_LTO_WRAPPER=/usr/libexec/gcc/x86_64-redhat-linux/4.8.5/lto-wrapper
目标：x86_64-redhat-linux
配置为：../configure --prefix=/usr --mandir=/usr/share/man --infodir=/usr/share/info --with-bugurl=http://bugzilla.redhat.com/bugzilla --enable-bootstrap --enable-shared --enable-threads=posix --enable-checking=release --with-system-zlib --enable-__cxa_atexit --disable-libunwind-exceptions --enable-gnu-unique-object --enable-linker-build-id --with-linker-hash-style=gnu --enable-languages=c,c++,objc,obj-c++,java,fortran,ada,go,lto --enable-plugin --enable-initfini-array --disable-libgcj --with-isl=/builddir/build/BUILD/gcc-4.8.5-20150702/obj-x86_64-redhat-linux/isl-install --with-cloog=/builddir/build/BUILD/gcc-4.8.5-20150702/obj-x86_64-redhat-linux/cloog-install --enable-gnu-indirect-function --with-tune=generic --with-arch_32=x86-64 --build=x86_64-redhat-linux
线程模型：posix
gcc 版本 4.8.5 20150623 (Red Hat 4.8.5-44) (GCC)

2.下载新的GCC版本

[root@dxg-ml ~]# cd /data/software
[root@dxg-ml software]# wget http://ftp.gnu.org/gnu/gcc/gcc-9.2.0/gcc-9.2.0.tar.gz
--2021-06-07 10:53:55--  http://ftp.gnu.org/gnu/gcc/gcc-9.2.0/gcc-9.2.0.tar.gz
正在解析主机 ftp.gnu.org (ftp.gnu.org)... 209.51.188.20, 2001:470:142:3::b
正在连接 ftp.gnu.org (ftp.gnu.org)|209.51.188.20|:80... 已连接。
已发出 HTTP 请求，正在等待回应... 200 OK
长度：124304709 (119M) [application/x-gzip]
正在保存至: “gcc-9.2.0.tar.gz”

100%[=========================================================================================================================================================================>] 124,304,709 11.9MB/s 用时 12s

2021-06-07 10:54:08 (9.94 MB/s) - 已保存 “gcc-9.2.0.tar.gz” [124304709/124304709])

3.解压安装包

[root@dxg-ml software]# tar xf gcc-9.2.0.tar.gz  -C /data

4.执行安装脚本

[root@dxg-ml data]# cd /data/gcc-9.2.0/
[root@dxg-ml /data/gcc-9.2.0]# ./contrib/download_prerequisites
2021-06-11 10:28:27 URL: ftp://gcc.gnu.org/pub/gcc/infrastructure/gmp-6.1.0.tar.bz2 [2383840] -> "./gmp-6.1.0.tar.bz2" [1]
2021-06-11 10:28:33 URL: ftp://gcc.gnu.org/pub/gcc/infrastructure/mpfr-3.1.4.tar.bz2 [1279284] -> "./mpfr-3.1.4.tar.bz2" [1]
2021-06-11 10:28:38 URL: ftp://gcc.gnu.org/pub/gcc/infrastructure/mpc-1.0.3.tar.gz [669925] -> "./mpc-1.0.3.tar.gz" [1]
2021-06-11 10:28:43 URL: ftp://gcc.gnu.org/pub/gcc/infrastructure/isl-0.18.tar.bz2 [1658291] -> "./isl-0.18.tar.bz2" [1]
gmp-6.1.0.tar.bz2: 确定
mpfr-3.1.4.tar.bz2: 确定
mpc-1.0.3.tar.gz: 确定
isl-0.18.tar.bz2: 确定
All prerequisites downloaded successfully.

5.建立临时目录

[root@dxg-ml gcc-9.2.0]# mkdir gcc_9_2_0_build
[root@dxg-ml gcc-9.2.0]# cd gcc_9_2_0_build

6.生成makefile文件

[root@dxg-ml gcc_9_2_0_build]# ../configure --prefix=/usr/local/gcc-9.2.0 --enable-bootstrap --enable-checking=release --enable-languages=c,c++ --disable-multilib

-enable-checking / -disable-checking
表示编译器在编译过程中是否做额外检查。

-enable-languages
表示支持的语言，可以支持ada c c++ d fortran go jit lto objc obj-c++

-disable-multilib
关闭多架构支持，可以支持arm，m68，mips，msp430，powerpc架构。

7.编译安装

[root@dxg-ml gcc_9_2_0_build]# make && make install

8.配置环境变量

#1.配置环境变量
[root@dxg-ml /data/gcc-9.2.0/gcc_9_2_0_build]# vim /etc/profile.d/gcc.sh
export PATH=/usr/local/gcc-9.2.0/bin:$PATH

#2.刷新环境变量
[root@dxg-ml /data/gcc-9.2.0/gcc_9_2_0_build]# source  /etc/profile

9.配置软连接

[root@dxg-ml /data/gcc-9.2.0/gcc_9_2_0_build]# ln -s /usr/local/gcc-9.2.0/include/ /usr/include/gcc

10.验证GCC版本

[root@dxg-ml /data/gcc-9.2.0/gcc_9_2_0_build]# gcc -v
使用内建 specs。
COLLECT_GCC=gcc
COLLECT_LTO_WRAPPER=/usr/local/gcc-9.2.0/libexec/gcc/x86_64-pc-linux-gnu/9.2.0/lto-wrapper
目标：x86_64-pc-linux-gnu
配置为：../configure --prefix=/usr/local/gcc-9.2.0 --enable-bootstrap --enable-checking=release --enable-languages=c,c++ --disable-multilib
线程模型：posix
gcc 版本 9.2.0 (GCC) 

11.替换老版本的gcc动态库

#1.升级后的gcc的动态库去替换老版本的gcc动态库
[root@dxg-ml /data/gcc-9.2.0/gcc_9_2_0_build]# cp /usr/local/gcc-9.2.0/lib64/libstdc++.so.6.0.27 /usr/lib64/
[root@dxg-ml /data/gcc-9.2.0/gcc_9_2_0_build]# cd /usr/local/gcc-9.2.0/lib64/
[root@dxg-ml /data/gcc-9.2.0/gcc_9_2_0_build]# ll libstdc++.so.6*
lrwxrwxrwx 1 root root       19 2021-06-11 13:00 libstdc++.so.6 -> libstdc++.so.6.0.27
-rwxr-xr-x 1 root root 17695592 2021-06-11 13:00 libstdc++.so.6.0.27
-rw-r--r-- 1 root root     2417 2021-06-11 13:00 libstdc++.so.6.0.27-gdb.py

[root@dxg-ml /data/gcc-9.2.0/gcc_9_2_0_build]# cd /usr/lib64
[root@dxg-ml /data/gcc-9.2.0/gcc_9_2_0_build]# rm -rf libstdc++.so.6
[root@dxg-ml /data/gcc-9.2.0/gcc_9_2_0_build]# ln -s libstdc++.so.6.0.27 libstdc++.so.6
[root@dxg-ml /data/gcc-9.2.0/gcc_9_2_0_build]# ll libstdc++.so.6*
lrwxrwxrwx 1 root root       19 2021-06-11 13:52 libstdc++.so.6 -> libstdc++.so.6.0.27
-rwxr-xr-x 1 root root 17695592 2021-06-11 13:00 libstdc++.so.6.0.27
-rw-r--r-- 1 root root     2417 2021-06-11 13:00 libstdc++.so.6.0.27-gdb.py

#2.配置软连接
[root@dxg-ml /data/gcc-9.2.0/gcc_9_2_0_build]# mv /usr/bin/c++ /tmp/
[root@dxg-ml /data/gcc-9.2.0/gcc_9_2_0_build]# mv /usr/bin/cc /tmp/
[root@dxg-ml /data/gcc-9.2.0/gcc_9_2_0_build]# ln -s /usr/local/gcc-9.2.0/bin/c++ /usr/bin/
[root@dxg-ml /data/gcc-9.2.0/gcc_9_2_0_build]# ln -s /usr/local/gcc-9.2.0/bin/gcc /usr/bin/cc

#3.查看gcc+版本
[root@dxg-ml /data/gcc-9.2.0/gcc_9_2_0_build]# g++ -v
使用内建 specs。
COLLECT_GCC=g++
COLLECT_LTO_WRAPPER=/usr/local/gcc-9.2.0/libexec/gcc/x86_64-pc-linux-gnu/9.2.0/lto-wrapper
目标：x86_64-pc-linux-gnu
配置为：../configure --prefix=/usr/local/gcc-9.2.0 --enable-bootstrap --enable-checking=release --enable-languages=c,c++ --disable-multilib
线程模型：posix
gcc 版本 9.2.0 (GCC)

12.Yum方式升级gcc

3.更新gcc版本
centos7 默认的 gcc 版本小于 5.3 无法编译,需要先安装gcc新版才能编译
[root@dxg-ml ~]# yum -y install centos-release-scl
[root@dxg-ml ~]# yum -y install devtoolset-9-gcc devtoolset-9-gcc-c++ devtoolset-9-binutils

#临时生效，退出 shell 或重启会恢复原 gcc 版本
[root@dxg-ml ~]# scl enable devtoolset-9 bash

#永久生效
[root@dxg-ml ~]# echo "source /opt/rh/devtoolset-9/enable" >>/etc/profile

六、Boost安装

Boost不需要手动安装，后面运行cmake脚本的时候会自动下载安装。

也可以手动安装，官网的文档说是需要特定版本，这里的MySQL Server 8.0.19需要的是1.70.0。

七、安装mysql8.0.24

1.下载mysql安装包

官网下载：https://downloads.mysql.com/archives/community/

[root@dxg-ml software]# wget https://downloads.mysql.com/archives/get/p/23/file/mysql-boost-8.0.24.tar.gz

2.解压安装包

[root@dxg-ml software]# tar -zxvf mysql-boost-8.0.24.tar.gz -C /root
[root@dxg-ml software]# cd /root/mysql-8.0.24/

3.建立临时文件夹

[root@dxg-ml ~/mysql-8.0.24]# mkdir bld
[root@dxg-ml ~/mysql-8.0.24]# cd bld

4.创建用户和用户组

[root@dxg-ml ~/mysql-8.0.24]# groupadd mysql
[root@dxg-ml ~/mysql-8.0.24]# useradd -r -g mysql -s /bin/false mysql

5.生成Makefile文件

[root@dxg-ml ~/mysql-8.0.24/bld]# cmake .. 
-DCMAKE_INSTALL_PREFIX=/usr/local/mysql 
-DMYSQL_DATADIR=/usr/local/mysql/data 
-DCMAKE_C_COMPILER=/usr/bin/cc 
-DCMAKE_CXX_COMPILER=/usr/bin/c++ 
-DSYSCONFDIR=/etc 
-DWITH_BOOST=/root/mysql-8.0.24/boost 
-DDEFAULT_CHARSET=utf8 
-DDEFAULT_COLLATION=utf8_general_ci 
-DENABLED_LOCAL_INFILE=ON 
-DWITH_INNODB_MEMCACHED=ON 
-DWITH_INNOBASE_STORAGE_ENGINE=1 
-DWITH_FEDERATED_STORAGE_ENGINE=1 
-DWITH_BLACKHOLE_STORAGE_ENGINE=1 
-DWITH_ARCHIVE_STORAGE_ENGINE=1 
-DWITHOUT_EXAMPLE_STORAGE_ENGINE=1 
-DWITH_PERFSCHEMA_STORAGE_ENGINE=1 
-DFORCE_INSOURCE_BUILD=1 
-DMYSQL_TCP_PORT=3306 

6.编译安装

[root@dxg-ml ~/mysql-8.0.24/bld]# make && make install

7.编译安装问题

1) 内存空间不足

#1.报错信息
[root@dxg-ml ~/mysql-8.0.24/bld]# make && make install
。。。 。。。
[ 51%] Building CXX object sql/CMakeFiles/sql_gis.dir/gis/crosses.cc.o
c++: 致命错误：已杀死 signal terminated program cc1plus
编译中断。
make[2]: *** [sql/CMakeFiles/sql_gis.dir/gis/crosses.cc.o] 错误 1
make[1]: *** [sql/CMakeFiles/sql_gis.dir/all] 错误 2
make: *** [all] 错误 2

#2.解决办法
1.查看交换分区内存
[root@dxg-ml ~/mysql-8.0.24/bld]# free -h
              total        used        free      shared  buff/cache   available
Mem:           1.8G        490M        131M        604K        1.2G        1.2G
Swap:            0B          0B          0B

2.创建临时分区命令
	1）count的大小就是增加的swap空间的大小，64M是块大小，所以空间大小是bs*count=4096MB
[root@dxg-ml ~/mysql-8.0.24/bld]# dd if=/dev/zero of=/swapfile bs=64M count=64
记录了64+0 的读入
记录了64+0 的写出
4294967296字节(4.3 GB)已复制，36.9896 秒，116 MB/秒

	2）把刚才空间格式化成swap格式
[root@dxg-ml ~/mysql-8.0.24/bld]# dd if=/dev/zero of=/swapfile bs=64M count=64
 mkswap /swapfile
正在设置交换空间版本 1，大小 = 4194300 KiB
无标签，UUID=e0842718-f010-4d86-9105-5eb778946884

	3）#修改权限
[root@dxg-ml ~/mysql-8.0.24/bld]# chmod 0600 /swapfile

	4）#使用刚才创建的swap空间
[root@dxg-ml ~/mysql-8.0.24/bld]# swapon /swapfile

	5）查看交换空间大小
[root@dxg-ml ~/mysql-8.0.24/bld]# free -h
              total        used        free      shared  buff/cache   available
Mem:           1.8G        492M         88M        604K        1.2G        1.2G
Swap:          4.0G          0B        4.0G


3.释放空间命令
swapoff -a

2）头文件缺失

#1.报错信息
[root@dxg-ml ~/mysql-8.0.24/bld]# make && make install
。。。 。。。
[ 59%] Building CXX object storage/innobase/CMakeFiles/innobase.dir/buf/buf0buddy.cc.o
[ 60%] Building CXX object storage/innobase/CMakeFiles/innobase.dir/buf/buf0buf.cc.o
/root/mysql-8.0.24/storage/innobase/buf/buf0buf.cc: In function ‘void buf_pool_create(buf_pool_t*, ulint, ulint, std::mutex*, dberr_t&)’:
/root/mysql-8.0.24/storage/innobase/buf/buf0buf.cc:1228:44: error: ‘SYS_gettid’ was not declared in this scope
 1228 |   setpriority(PRIO_PROCESS, (pid_t)syscall(SYS_gettid), -20);
      |                                            ^~~~~~~~~~
make[2]: *** [storage/innobase/CMakeFiles/innobase.dir/buf/buf0buf.cc.o] Error 1
make[1]: *** [storage/innobase/CMakeFiles/innobase.dir/all] Error 2
make: *** [all] Error 2


#2.解决办法
[root@dxg-ml ~/mysql-8.0.24/bld]# vim /root/mysql-8.0.24/storage/innobase/buf/buf0buf.cc 
// 在第一行添加头文件
#include "sys/syscall.h" 

八、编写MySQL配置文件

#1.新建数据库存放目录
[root@dxg-ml ~]# mkdir /usr/local/mysql/etc
[root@dxg-ml ~]# mkdir /usr/local/mysql/log

#2.编写mysql配置文件
[root@dxg-ml etc]# cd /usr/local/mysql/etc
[root@dxg-ml etc]# vim my.cnf
[client]
port    = 13306
socket = /usr/local/mysql/data/mysql.sock

[mysql]
prompt="u@h R:m:s [d]> "
no-auto-rehash

[mysqld]
user    = mysql
port    = 13306
admin_address = 127.0.0.1
basedir = /usr/local/mysql
datadir = /usr/local/mysql/data
socket    = /usr/local/mysql/data/mysql.sock
pid-file = /usr/local/mysql/data/mysqld.pid
character-set-server = utf8mb4
skip_name_resolve = 1
#这个就是用之前的身份认证插件
default-authentication-plugin = mysql_native_password
#保证日志的时间正确
log_timestamps = SYSTEM

#replicate-wild-ignore-table=mysql.%
replicate-wild-ignore-table=test.%
replicate-wild-ignore-table=information_schema.%

# Two-Master configure
#server-1
#auto-increment-offset = 1
#auto-increment-increment = 2

#server-2
#auto-increment-offset = 2
#auto-increment-increment = 2


# semi sync replication settings #
#plugin_dir = /usr/local/mysql/lib/mysql/plugin
#plugin_load = "validate_password.so;rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"
plugin_dir =/usr/local/mysql/lib/plugin #官方版本的路径
plugin_load = "rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so" #官方版本的路径

slave_parallel_workers = 4
slave_parallel_type = LOGICAL_CLOCK

open_files_limit    = 65535
back_log = 1024
max_connections = 1024
max_connect_errors = 1000000
table_open_cache = 1024
table_definition_cache = 1024
table_open_cache_instances = 64
thread_stack = 512K
external-locking = FALSE
max_allowed_packet = 32M
sort_buffer_size = 4M
join_buffer_size = 4M
thread_cache_size = 1536
interactive_timeout = 600
wait_timeout = 600
tmp_table_size = 32M
max_heap_table_size = 32M
slow_query_log = 1
log_timestamps = SYSTEM
slow_query_log_file = /usr/local/mysql/log/slow.log
log-error = /usr/local/mysql/log/error.log
long_query_time = 0.1
log_queries_not_using_indexes =1
log_throttle_queries_not_using_indexes = 60
min_examined_row_limit = 100
log_slow_admin_statements = 1
log_slow_slave_statements = 1
server-id = 3306
log-bin = mysql-bin
sync_binlog = 1
binlog_cache_size = 4M
max_binlog_cache_size = 2G
max_binlog_size = 1G
binlog_expire_logs_seconds=2592000
master_info_repository = TABLE
relay_log_info_repository = TABLE
gtid_mode = on
enforce_gtid_consistency = 1
log_slave_updates
slave-rows-search-algorithms = 'INDEX_SCAN,HASH_SCAN'
binlog_format = row
binlog_row_image=FULL
binlog_checksum = 1
relay_log_recovery = 1
relay-log-purge = 1
key_buffer_size = 32M
read_buffer_size = 8M
read_rnd_buffer_size = 4M
bulk_insert_buffer_size = 64M
myisam_sort_buffer_size = 128M
myisam_max_sort_file_size = 10G
myisam_repair_threads = 1
lock_wait_timeout = 3600
explicit_defaults_for_timestamp = 1
innodb_thread_concurrency = 0
innodb_sync_spin_loops = 100
innodb_spin_wait_delay = 30

#transaction_isolation = REPEATABLE-READ
transaction_isolation = READ-COMMITTED
#innodb_additional_mem_pool_size = 16M
innodb_buffer_pool_size = 2867M
innodb_buffer_pool_instances = 4
innodb_buffer_pool_load_at_startup = 1
innodb_buffer_pool_dump_at_shutdown = 1
innodb_data_file_path = ibdata1:1G:autoextend
innodb_flush_log_at_trx_commit = 1
innodb_log_buffer_size = 32M
innodb_log_file_size = 2G
innodb_log_files_in_group = 3
innodb_max_undo_log_size = 4G
# innodb_undo_directory = /usr/local/mysql/log/undolog
innodb_undo_tablespaces = 95

# 根据您的服务器IOPS能力适当调整
# 一般配普通SSD盘的话，可以调整到 10000 - 20000
# 配置高端PCIe SSD卡的话，则可以调整的更高，比如 50000 - 80000
innodb_io_capacity = 4000
innodb_io_capacity_max = 8000
innodb_flush_sync = 0
innodb_flush_neighbors = 0
innodb_write_io_threads = 8
innodb_read_io_threads = 8
innodb_purge_threads = 4
innodb_page_cleaners = 4
innodb_open_files = 65535
innodb_max_dirty_pages_pct = 50
innodb_flush_method = O_DIRECT
innodb_lru_scan_depth = 4000
innodb_checksum_algorithm = crc32
innodb_lock_wait_timeout = 10
innodb_rollback_on_timeout = 1
innodb_print_all_deadlocks = 1
innodb_file_per_table = 1
innodb_online_alter_log_max_size = 4G
innodb_stats_on_metadata = 0

# some var for MySQL 8
log_error_verbosity = 3
innodb_print_ddl_logs = 1
binlog_expire_logs_seconds = 2592000
#innodb_dedicated_server = 0

innodb_status_file = 1
# 注意: 开启 innodb_status_output & innodb_status_output_locks 后, 可能会导致log-error文件增长较快
innodb_status_output = 0
innodb_status_output_locks = 0

#performance_schema
performance_schema = 1
performance_schema_instrument = '%memory%=on'
performance_schema_instrument = '%lock%=on'

#innodb monitor
innodb_monitor_enable="module_innodb"
innodb_monitor_enable="module_server"
innodb_monitor_enable="module_dml"
innodb_monitor_enable="module_ddl"
innodb_monitor_enable="module_trx"
innodb_monitor_enable="module_os"
innodb_monitor_enable="module_purge"
innodb_monitor_enable="module_log"
innodb_monitor_enable="module_lock"
innodb_monitor_enable="module_buffer"
innodb_monitor_enable="module_index"
innodb_monitor_enable="module_ibuf_system"
innodb_monitor_enable="module_buffer_page"
innodb_monitor_enable="module_adaptive_hash"
#validate_password_policy=LOW

[mysqldump]
quick
max_allowed_packet = 32M

[mysqld_safe]
#malloc-lib=/usr/local/mysql/lib/jmalloc.so
nice=-19
open-files-limit=65535

[root@dxg-ml etc]#  chown  -R mysql.mysql /usr/local/mysql/

九、初始化数据库

[root@dxg-ml local]# cd /usr/local/mysql/bin/

[root@dxg-ml bin]# ./mysqld --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data

#另起一个交互窗口
[root@dxg-ml bin]# tail -f  /usr/local/mysql/log/error.log
... ...
2021-06-15T12:13:30.024286+08:00 6 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: XJxmua9_j%p(
2021-06-15T12:13:30.024435+08:00 6 [Note] [MY-011061] [Server] Creating the system tables.
2021-06-15T12:13:30.690796+08:00 6 [Note] [MY-011061] [Server] Filling in the system tables, part 1.
2021-06-15T12:13:30.719312+08:00 6 [Note] [MY-011061] [Server] Filling in the system tables, part 2.
2021-06-15T12:13:31.376932+08:00 6 [Note] [MY-011061] [Server] Filling in the mysql.help table.
2021-06-15T12:13:31.673091+08:00 6 [Note] [MY-011061] [Server] Creating the system users for internal usage.
2021-06-15T12:13:31.701028+08:00 6 [Note] [MY-011061] [Server] Creating the sys schema.
2021-06-15T12:13:32.761750+08:00 6 [Note] [MY-010456] [Server] Bootstrapping complete
2021-06-15T12:13:32.869396+08:00 0 [Note] [MY-010067] [Server] Giving 0 client threads a chance to die gracefully
2021-06-15T12:13:32.869473+08:00 0 [Note] [MY-010117] [Server] Shutting down slave threads
2021-06-15T12:13:32.869499+08:00 0 [Note] [MY-010118] [Server] Forcefully disconnecting 0 remaining clients
2021-06-15T12:13:32.887735+08:00 0 [Note] [MY-012330] [InnoDB] FTS optimize thread exiting.
2021-06-15T12:13:33.384787+08:00 0 [Note] [MY-013072] [InnoDB] Starting shutdown...
2021-06-15T12:13:33.386629+08:00 0 [Note] [MY-011944] [InnoDB] Dumping buffer pool(s) to /usr/local/mysql/data/ib_buffer_pool
2021-06-15T12:13:33.387044+08:00 0 [Note] [MY-011944] [InnoDB] Buffer pool(s) dump completed at 210615 12:13:33
2021-06-15T12:13:33.659643+08:00 0 [Note] [MY-013084] [InnoDB] Log background threads are being closed...
2021-06-15T12:13:34.339939+08:00 0 [Note] [MY-012980] [InnoDB] Shutdown completed; log sequence number 17762412
2021-06-15T12:13:34.340178+08:00 0 [Note] [MY-012255] [InnoDB] Removed temporary tablespace data file: "ibtmp1"

十、启动数据库

1.命令行界面启动

#1.进入mysql配置目录
[root@dxg-ml local]# cd /usr/local/mysql/bin/

#2.命令行启动mysql服务
[root@dxg-ml bin]# ./mysqld_safe --defaults-file=/usr/local/mysql/etc/my.cnf &

#3.验证服务
[root@dxg-ml bin]# ps -ef |grep 13306
root       694 25542  0 18:54 pts/0    00:00:00 grep --color=auto 13306
mysql    29542 27905  0 18:31 pts/0    00:00:05 /usr/local/mysql/bin/mysqld --defaults-file=/usr/local/mysql/etc/my.cnf --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=/usr/local/mysql/log/error.log --open-files-limit=65535 --pid-file=/usr/local/mysql/data/mysqld.pid --socket=/usr/local/mysql/data/mysql.sock --port=13306

[root@dxg-ml bin]# netstat  -lntp |grep 13306
tcp6       0      0 :::13306                :::*                    LISTEN      29542/mysqld

2.配置init启动

#1.拷贝mysql配置文件到init目录下
[root@dxg-ml bin]# cp /usr/local/mysql/support-files/mysql.server  /etc/init.d/mysqld

#2.修改启动配置文件
[root@dxg-ml bin]# vim /etc/init.d/mysqld
basedir=/usr/local/mysql
datadir=/usr/local/mysql/data

#3.添加执行权限
[root@dxg-ml bin]# chmod +x /etc/init.d/mysqld

#4.启动mmysql
[root@dxg-ml bin]# /etc/init.d/mysqld start
Starting MySQL.. SUCCESS!

#5.验证服务状态
[root@dxg-ml bin]# ps -ef |grep 13306
root       694 25542  0 18:54 pts/0    00:00:00 grep --color=auto 13306
mysql    29542 27905  0 18:31 pts/0    00:00:05 /usr/local/mysql/bin/mysqld --defaults-file=/usr/local/mysql/etc/my.cnf --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=/usr/local/mysql/log/error.log --open-files-limit=65535 --pid-file=/usr/local/mysql/data/mysqld.pid --socket=/usr/local/mysql/data/mysql.sock --port=13306

[root@dxg-ml bin]# netstat  -lntp |grep 13306
tcp6       0      0 :::13306                :::*                    LISTEN      29542/mysqld

3.配置system启动

#1.配置system启动
[root@dxg-ml bin]# 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
ExecStart=/usr/local/mysql/bin/mysqld_safe --defaults-file=/usr/local/mysql/etc/my.cnf
LimitNOFILE = 5000
#Restart=on-failure
#RestartPreventExitStatus=1
#PrivateTmp=false

#2.重新加载启动文件列表
[root@dxg-ml bin]# systemctl daemon-reload

ps:服务用哪种方式启动就需要用哪种方式去停止，错的启动或者停止服务方式会造成不可解决的错误。
#3.停止init进程
[root@dxg-ml bin]# /etc/init.d/mysqld stop

#4.启动mysql服务
[root@dxg-ml bin]# systemctl start mysqld.service

#5.查看服务状态
[root@dxg-ml bin]# systemctl status mysqld.service
● mysqld.service - MySQL Server
   Loaded: loaded (/usrb/systemd/system/mysqld.service; disabled; vendor preset: disabled)
   Active: active (running) since Tue 2021-06-15 14:08:06 CST; 7s ago
     Docs: man:mysqld(8)
           http://dev.mysql.com/doc/refman/en/using-systemd.html
 Main PID: 8146 (mysqld_safe)
   CGroup: /system.slice/mysqld.service
           ├─8146 /bin /usr/local/mysql/bin/mysqld_safe --defaults-file=/usr/local/mysql/etc/my.cnf
           └─9799 /usr/local/mysql/bin/mysqld --defaults-file=/usr/local/mysql/etc/my.cnf --basedir=/usr/local/mysql -...

Jun 15 14:08:06 m01 systemd[1]: Started MySQL Server.
Jun 15 14:08:08 m01 mysqld_safe[8146]: 2021-06-15T06:08:08.871063Z mysqld_safe Logging to '/usr/local/mysql/log/....log'.
Jun 15 14:08:08 m01 mysqld_safe[8146]: 2021-06-15T06:08:08.942365Z mysqld_safe Starting mysqld daemon with datab...l/data
Hint: Some lines were ellipsized, use -l to show in full.

[root@m01 /usr/local/mysql]# ps -ef |grep 13306
mysql      9799   8146 28 14:08 ?        00:00:03 /usr/local/mysql/bin/mysqld --defaults-file=/usr/local/mysql/etc/my.cnf --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data --plugin-dir=/usr/local/mysqlbugin --log-error=/usr/local/mysql/log/error.log --open-files-limit=65535 --pid-file=/usr/local/mysql/data/mysqld.pid --socket=/usr/local/mysql/data/mysql.sock --port=13306
root       9864 124008  0 14:08 pts/0    00:00:00 grep --color=auto 3306
[root@m01 /usr/local/mysql]# netstat  -lntp |grep 13306
tcp        0      0 127.0.0.1:33062         0.0.0.0:*               LISTEN      9799/mysqld         
tcp6       0      0 :::13306                 :::*                    LISTEN      9799/mysqld       
tcp6       0      0 :::33060                :::*                    LISTEN      9799/mysqldd

十一、配置环境变量

#配置环境变量
[root@dxg-ml bin]# vim /etc/profile.d/mysql-8.0.24.sh
export PATH=/usr/local/mysql/bin:$PATH

#重载环境变量
[root@dxg-ml bin]#source /etc/profile

十二、登录数据库

1.首次登录数据库

#登录数据库
[root@dxg-ml bin]# /usr/local/mysql/bin/mysql --socket=/usr/local/mysql/data/mysql.sock
Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 7
Server version: 8.0.24 MySQL Community Server - GPL

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

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>

2.常用命令登录数据库

#登录并配置远程登录
1.可能报错
[root@dxg-ml /usr/local/mysql]# mysql -P 13306 -u root -p
Enter password: 
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)

2.做软连接
[root@dxg-ml /usr/local/mysql]# ln -s /usr/local/mysql/data/mysql.sock /tmp/

3.正常登陆数据库
[root@dxg-ml bin]# mysql -P 13306 -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 8
Server version: 8.0.24

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

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.

root@localhost 18:35:  [(none)]>

#查看数据库提示你必须重置你的密码
root@localhost 18:36:  [(none)]> show databases;
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.

#修改数据库密码
root@localhost 18:36:  [(none)]> ALTER USER 'root'@'localhost' IDENTIFIED BY 'root';
Query OK, 0 rows affected (0.10 sec)

#再次查看数据库
root@localhost 18:38:  [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.09 sec)

#使用数据库
root@localhost 18:38:  [(none)]> use mysql;
Database changed

#这里就可以看到root@localhost这里的密码已经是mysql_native_password方式了
root@localhost 18:39:  [mysql]> select host, user, authentication_string, plugin from user;
+-----------+------------------+------------------------------------------------------------------------+-----------------------+
| host      | user             | authentication_string                                                  | plugin                |
+-----------+------------------+------------------------------------------------------------------------+-----------------------+
| localhost | mysql.infoschema | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password |
| localhost | mysql.session    | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password |
| localhost | mysql.sys        | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password |
| localhost | root             | *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B                              | mysql_native_password |
+-----------+------------------+------------------------------------------------------------------------+-----------------------+
4 rows in set (0.00 sec)

#创建一个远程用户登录
root@localhost 18:42:  [mysql]> create user 'root'@'%' identified by 'root';
Query OK, 0 rows affected (0.24 sec)

root@localhost 18:43:  [mysql]> grant all privileges on *.* to 'root'@'%' with grant option;
Query OK, 0 rows affected (0.06 sec)

root@localhost 18:44:  [mysql]> select host, user, authentication_string, plugin from user;
+-----------+------------------+------------------------------------------------------------------------+-----------------------+
| host      | user             | authentication_string                                                  | plugin                |
+-----------+------------------+------------------------------------------------------------------------+-----------------------+
| %         | root             | *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B                              | mysql_native_password |
| localhost | mysql.infoschema | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password |
| localhost | mysql.session    | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password |
| localhost | mysql.sys        | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password |
| localhost | root             | *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B                              | mysql_native_password |
+-----------+------------------+------------------------------------------------------------------------+-----------------------+
5 rows in set (0.00 sec)

#退出数据库
root@localhost 18:44:  [mysql]> quit
Bye