一、需求
公司由于安装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