作者 | JiekeXu
来源 | JiekeXu DBA之路（ID: JiekeXu_IT）
大家好，我是 JiekeXu,很高兴又和大家见面了,今天和大家一起来看看 Linux 7.7 源码安装 MySQL 8.0.26 ，欢迎点击上方蓝字关注我，标星或置顶，更多干货第一时间到达！
MySQL8.0 推出也已经好几年了，之前安装过 MySQL 5.7，在安装上两者没有太大的区别，就是远程连接这块，需要注意下就好。DB-Engines 发布了 2021 年 9 月份的数据库排行榜 MySQL 反而又降低了 25.69 分，国产数据库的崛起，对 Oracle 和 MySQL 有一定的冲击，不过这也不影响他的流行和使用。
（图源：http://db-engines.com/en/ranking）
一、系统准备
--环境介绍
[root@jiekexu-test ~]# cat /etc/redhat-release
Red Hat Enterprise Linux Server release 7.7 (Maipo)
[root@jiekexu-test ~]# free -m
total used free shared buff/cache available
Mem: 7802 1248 2081 15 4472 4968
Swap: 8191 0 8191
[root@jiekexu-test ~]# df -h
Filesystem Size Used Avail Use% Mounted on
devtmpfs 3.8G 0 3.8G 0% /dev
tmpfs 8.0G 0 8.0G 0% /dev/shm
tmpfs 3.9G 13M 3.8G 1% /run
tmpfs 3.9G 0 3.9G 0% /sys/fs/cgroup
/dev/mapper/rhel_jiekexu--test-root 36G 19G 18G 52% /
/dev/sda1 1014M 184M 831M 19% /boot
/dev/mapper/rhel_jiekexu--test-home 18G 2.1G 16G 12% /home
tmpfs 781M 32K 781M 1% /run/user/0
/dev/sr0 4.2G 4.2G 0 100% /mnt/dvd
0.下载 MySQL 8.0.26
如下链接，选择相关的版本和系统。
https://dev.mysql.com/downloads/mysql/8.0.html
MD5: 100a0e9336ef106a5fe90e6803b57066
1、RHEL7 关闭防火墙
systemctl stop firewalld.service或者systemctl stop firewalld
systemctl disable firewalld.service或者systemctl disable firewalld
systemctl status firewalld
[root@jiekexu-test ~]# systemctl status firewalld
? firewalld.service - firewalld - dynamic firewall daemon
Loaded: loaded (/usr/lib/systemd/system/firewalld.service; disabled; vendor preset: enabled)
Active: inactive (dead)
Docs: man:firewalld(1)
2、关闭 selinux
getenforce
cp /etc/selinux/config /etc/selinux/config_`date +"%Y%m%d_%H%M%S"`&& sed -i 's/SELINUX\=enforcing/SELINUX\=disabled/g' /etc/selinux/config
[root@jiekexu-test ~]# getenforce
Disabled
[root@jiekexu-test ~]# sestatus
SELinux status: disabled
3、/etc/hosts 解析(示例如下)
[root@jiekexu-test ~]# cat /etc/hosts
127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4
::1 localhost localhost.localdomain localhost6 localhost6.localdomain6
192.168.75.135 jiekexu-test
4、安装所需 yum 包
-- 挂载本地光盘镜像
[root@jiekexu-test yum.repos.d]# cd /mnt/dvd/
[root@jiekexu-test dvd]# ll
total 0
[root@jiekexu-test dvd]#
[root@jiekexu-test dvd]#
[root@jiekexu-test dvd]# mount /dev/sr0 /mnt/dvd
mount: /dev/sr0 is write-protected, mounting read-only
[root@jiekexu-test dvd]#
[root@jiekexu-test dvd]# df -h
Filesystem Size Used Avail Use% Mounted on
devtmpfs 3.8G 0 3.8G 0% /dev
tmpfs 8.0G 0 8.0G 0% /dev/shm
tmpfs 3.9G 13M 3.8G 1% /run
tmpfs 3.9G 0 3.9G 0% /sys/fs/cgroup
/dev/mapper/rhel_jiekexu--test-root 36G 15G 21G 43% /
/dev/sda1 1014M 184M 831M 19% /boot
/dev/mapper/rhel_jiekexu--test-home 18G 2.1G 16G 12% /home
tmpfs 781M 28K 781M 1% /run/user/0
/dev/sr0 4.2G 4.2G 0 100% /mnt/dvd
配置 yum 源
cd /etc/yum.repos.d/
cat >> /etc/yum.repos.d/redhat.repo << "EOF"
[rhel7]
name=jiekexu repo
baseurl=file:///mnt/dvd/
gpgcheck=0
EOF
测试 yum 源
yum repolist
[root@jiekexu-test dvd]# yum repolist
Loaded plugins: langpacks, product-id, search-disabled-repos, subscription-manager
This system is not registered with an entitlement server. You can use subscription-manager to register.
Repodata is over 2 weeks old. Install yum-cron? Or run: yum makecache fast
repo id repo name status
!rhel7 jiekexu repo 5,229
repolist: 5,229
安装依赖包
yum -y groupinstall "DeveLopment tools"
yum -y install ncurses ncurses-devel openssl-devel bison gcc gcc-c++ make
5、清理系统环境
CentOS 7 或 RHEL7 版本的系统默认自带安装了 MariaDB，需要先清理。
-- 查询已安装的mariadb
rpm -qa |grep mariadb
或
yum list installed | grep mariadb
[root@jiekexu-test app]# rpm -qa |grep mariadb
mariadb-server-5.5.64-1.el7.x86_64
mariadb-libs-5.5.64-1.el7.x86_64
mariadb-5.5.64-1.el7.x86_64
[root@jiekexu-test app]# yum list installed | grep mariadb
mariadb.x86_64 1:5.5.64-1.el7 @anaconda/7.7
mariadb-libs.x86_64 1:5.5.64-1.el7 @anaconda/7.7
mariadb-server.x86_64 1:5.5.64-1.el7 @anaconda/7.7
-- 卸载mariadb包，文件名为上述命令查询出来的 lib 文件
rpm -e --nodeps mariadb-libs-5.5.64-1.el7.x86_64 或者
yum -y remove mariadb-libs.x86_64
[root@jiekexu-test app]# yum -y remove mariadb-libs.x86_64
Loaded plugins: langpacks, product-id, search-disabled-repos, subscription-manager
This system is not registered with an entitlement server. You can use subscription-manager to register.
Resolving Dependencies
--> Running transaction check
---> Package mariadb-libs.x86_64 1:5.5.64-1.el7 will be erased
--> Processing Dependency: libmysqlclient.so.18()(64bit) for package: perl-DBD-MySQL-4.023-6.el7.x86_64
--> Processing Dependency: libmysqlclient.so.18()(64bit) for package: 2:postfix-2.10.1-7.el7.x86_64
--> Processing Dependency: libmysqlclient.so.18(libmysqlclient_18)(64bit) for package: perl-DBD-MySQL-4.023-6.el7.x86_64
--> Processing Dependency: libmysqlclient.so.18(libmysqlclient_18)(64bit) for package: 2:postfix-2.10.1-7.el7.x86_64
--> Processing Dependency: mariadb-libs(x86-64) = 1:5.5.64-1.el7 for package: 1:mariadb-server-5.5.64-1.el7.x86_64
--> Processing Dependency: mariadb-libs(x86-64) = 1:5.5.64-1.el7 for package: 1:mariadb-5.5.64-1.el7.x86_64
--> Running transaction check
---> Package mariadb.x86_64 1:5.5.64-1.el7 will be erased
---> Package mariadb-server.x86_64 1:5.5.64-1.el7 will be erased
---> Package perl-DBD-MySQL.x86_64 0:4.023-6.el7 will be erased
---> Package postfix.x86_64 2:2.10.1-7.el7 will be erased
--> Finished Dependency Resolution
rhel7 | 2.8 kB 00:00:00
Dependencies Resolved
=============================================================================================================================================================================================
Package Arch Version Repository Size
=============================================================================================================================================================================================
Removing:
mariadb-libs x86_64 1:5.5.64-1.el7 @anaconda/7.7 4.4 M
Removing for dependencies:
mariadb x86_64 1:5.5.64-1.el7 @anaconda/7.7 49 M
mariadb-server x86_64 1:5.5.64-1.el7 @anaconda/7.7 58 M
perl-DBD-MySQL x86_64 4.023-6.el7 @anaconda/7.7 323 k
postfix x86_64 2:2.10.1-7.el7 @anaconda/7.7 12 M
Transaction Summary
=============================================================================================================================================================================================
Remove 1 Package (+4 Dependent packages)
Installed size: 124 M
Downloading packages:
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
Erasing : 1:mariadb-server-5.5.64-1.el7.x86_64 1/5
Erasing : 1:mariadb-5.5.64-1.el7.x86_64 2/5
Erasing : perl-DBD-MySQL-4.023-6.el7.x86_64 3/5
Erasing : 2:postfix-2.10.1-7.el7.x86_64 4/5
Erasing : 1:mariadb-libs-5.5.64-1.el7.x86_64 5/5
Verifying : 1:mariadb-libs-5.5.64-1.el7.x86_64 1/5
Verifying : 2:postfix-2.10.1-7.el7.x86_64 2/5
Verifying : 1:mariadb-5.5.64-1.el7.x86_64 3/5
Verifying : perl-DBD-MySQL-4.023-6.el7.x86_64 4/5
Verifying : 1:mariadb-server-5.5.64-1.el7.x86_64 5/5
Removed:
mariadb-libs.x86_64 1:5.5.64-1.el7
Dependency Removed:
mariadb.x86_64 1:5.5.64-1.el7 mariadb-server.x86_64 1:5.5.64-1.el7 perl-DBD-MySQL.x86_64 0:4.023-6.el7 postfix.x86_64 2:2.10.1-7.el7
Complete!
[root@jiekexu-test app]# yum list installed | grep mariadb
[root@jiekexu-test app]# rpm -qa |grep mariadb
二、安装 MySQL
- 创建数据库用户，创建实例所需目录(本手册中3306为一个实例，如部署多实例请按照下面目录结构创建目录)
root 用户操作：
mkdir -p /mysql/data/mysql3306
mkdir -p /mysql/app/
mkdir -p /mysql/conf/
mkdir -p /mysql/data/mysql3306/data/
mkdir -p /mysql/data/mysql3306/pid/
mkdir -p /mysql/data/mysql3306/socket/
mkdir -p /mysql/data/mysql3306/log/
mkdir -p /mysql/data/mysql3306/binlog/
mkdir -p /mysql/data/mysql3306/relaylog/
mkdir -p /mysql/data/mysql3306/slowlog/
mkdir -p /mysql/data/mysql3306/tmp/
检查系统原有的 MySQL 用户和组，删除 mysql 用户重新添加
userdel mysql
groupadd mysql
useradd -g mysql mysql
chown -R mysql:mysql /mysql
passwd mysql
[root@jiekexu-test app]# cat /etc/group | grep mysql
mysql:x:27:
[root@jiekexu-test app]# cat /etc/passwd | grep mysql
mysql:x:27:27:MariaDB Server:/var/lib/mysql:/sbin/nologin
[root@jiekexu-test app]# groupadd mysql
groupadd: group 'mysql' already exists
[root@jiekexu-test app]#
[root@jiekexu-test app]#
[root@jiekexu-test app]# useradd -g mysql mysql
useradd: user 'mysql' already exists
[root@jiekexu-test app]# userdel mysql
[root@jiekexu-test app]# cat /etc/passwd | grep mysql
[root@jiekexu-test app]# useradd -g mysql mysql
useradd: group 'mysql' does not exist
[root@jiekexu-test app]# groupadd mysql
[root@jiekexu-test app]# useradd -g mysql mysql
[root@jiekexu-test app]# chown -R mysql:mysql /mysql
[root@jiekexu-test app]# passwd mysql
Changing password for user mysql.
New password:
Retype new password:
passwd: all authentication tokens updated successfully.
[root@jiekexu-test app]# cat /etc/passwd | grep mysql
mysql:x:10002:54331::/home/mysql:/bin/bash
[root@jiekexu-test app]# cat /etc/group | grep mysql
mysql:x:54331:
2、上传软件包并解压安装程序包
mysql 用户操作： cd /mysql/app md5 值验证，保证下载到的软件包无破损无木马。
[mysql@jiekexu-test app]$ md5sum mysql-8.0.26-linux-glibc2.12-x86_64.tar.xz
100a0e9336ef106a5fe90e6803b57066 mysql-8.0.26-linux-glibc2.12-x86_64.tar.xz
[mysql@jiekexu-test app]$ MD5: 100a0e9336ef106a5fe90e6803b57066
解压软件包并重命名
tar xvf mysql-8.0.26-linux-glibc2.12-x86_64.tar.xz
mv mysql-8.0.26-linux-glibc2.12-x86_64 mysql8.0.26
3、配置 mysql 用户环境变量
cat >> /home/mysql/.bash_profile << "EOF"
MYSQL_HOME=/mysql/app/mysql8.0.26
PATH=$PATH:$HOME/.local/bin:$HOME/bin:$MYSQL_HOME/bin
EOF
source ~/.bash_profile
which mysql
4、创建参数文件
由于是二进制文件安装，数据库参数文件需要自己配置，以下是简单的参数配置。其他参数可依照个人需求添加。
vim /mysql/conf/my3306.cnf
[mysqld]
server_id = 100
default-storage-engine= InnoDB
basedir=/mysql/data/mysql3306
datadir=/mysql/data/mysql3306/data/
socket=/mysql/data/mysql3306/socket/mysql.sock
log-error=/mysql/data/mysql3306/log/mysqld.log
pid-file=/mysql/data/mysql3306/pid/mysqld.pid
port=3306
default-time_zone='+8:00'
# default_authentication_plugin=mysql_native_password # 加此参数可远程登陆
transaction_isolation=READ-COMMITTED
max_connections=1500
back_log=500
wait_timeout=1800
max_user_connections=800
innodb_buffer_pool_size=1024M
innodb_log_file_size=512M
innodb_log_buffer_size=40M
slow_query_log=ON
long_query_time=5
# log settings #
slow_query_log = ON
slow_query_log_file = /mysql/data/mysql3306/slowlog/slow3306.log
log_error = /mysql/data/mysql3306/errlog/err3306.log
log_error_verbosity = 3
log_bin = /mysql/data/mysql3306/binlog/mysql_bin
log_bin_index = /mysql/data/mysql3306/binlog/mysql_binlog.index
general_log_file = /data/mysql/mysql3306/generallog/general.log
log_queries_not_using_indexes = 1
log_slow_admin_statements = 1
expire_logs_days = 90
binlog_expire_logs_seconds = 2592000 #30d
long_query_time = 2
min_examined_row_limit = 100
log_throttle_queries_not_using_indexes = 1000
innodb_flush_log_at_trx_commit=1
5、数据库初始化
mysql用户操作：
mysqld --defaults-file=/mysql/conf/my3306.cnf --initialize --user=mysql --basedir=/mysql/app/mysql8.0.26 --datadir=/mysql/data/mysql3306/data/
6、启动 MySQL
mysqld_safe --defaults-file=/mysql/conf/my3306.cnf --user=mysql &
7、第一次登录 MySQL
第一次登录 MySQL 时，需要到错误日志下找初始化密码，我这里的密码为 /(So*6sXqmj3 ，使用 socket 加密码登录进去后，无法查询任何东西，提示先要修改 root 密码。
[mysql@jiekexu-test ~]$ cat /mysql/data/mysql3306/log/mysqld.log | grep password
2021-09-10T09:46:27.796502Z 6 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: /(So*6sXqmj3
[mysql@jiekexu-test ~]$
[mysql@jiekexu-test ~]$ mysql -uroot -p
Enter password:
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)
[mysql@jiekexu-test ~]$ mysql -uroot -p -P 3306 -S /mysql/data/mysql3306/socket/mysql.sock
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.26
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> select version();
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.
mysql>
8、修改 root@localhost 用户密码
mysql> alter user root@'localhost' identified by 'root';
Query OK, 0 rows affected (0.01 sec)
mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.26 |
+-----------+
1 row in set (0.00 sec)
9、配置 root 可以远程登录
使用如下语句创建 root 用户是无法通过 navicat 等客户端登录的，由于从 MySQL8 开始，身份验证插件发生改变，默认的 “caching_sha2_password” 不允许远程登录，故需将此插件修改为 “mysql_native_password” 便可登录。
mysql>create user root@’%’ identified by ‘root’;
mysql>grant all privileges on . to root@’%’ with grant option;
mysql>flush privileges;
使用 navicat 等客户端登录提示错误 1251
使用语句 “ALTER USER ‘root’@’%’ IDENTIFIED WITH mysql_native_password BY ‘root’;” 修改插件后测试正常，可以连接。
mysql> select user,host,plugin from mysql.user;
+------------------+-----------+-----------------------+
| user | host | plugin |
+------------------+-----------+-----------------------+
| root | % | caching_sha2_password |
| mysql.infoschema | localhost | caching_sha2_password |
| mysql.session | localhost | caching_sha2_password |
| mysql.sys | localhost | caching_sha2_password |
| root | localhost | caching_sha2_password |
+------------------+-----------+-----------------------+
5 rows in set (0.00 sec)
mysql> updates user set plugin='mysql_native_password' where user='root';
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'updates user set plugin='mysql_native_password' where user='root'' at line 1
mysql> ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY 'root';
Query OK, 0 rows affected (0.01 sec)
mysql> select user,host,plugin from mysql.user;
+------------------+-----------+-----------------------+
| user | host | plugin |
+------------------+-----------+-----------------------+
| root | % | mysql_native_password |
| mysql.infoschema | localhost | caching_sha2_password |
| mysql.session | localhost | caching_sha2_password |
| mysql.sys | localhost | caching_sha2_password |
| root | localhost | caching_sha2_password |
+------------------+-----------+-----------------------+
5 rows in set (0.00 sec)
当然还有一种方法，编辑 my.cnf 文件，更改默认的身份认证插件。比如说：
vim /data/mysql/mysql_3306/my_3306.cnf
# 在[mysqld]中添加如下的代码 default_authentication_plugin=mysql_native_password
这个需要重启服务才生效。
10.修改配置文件重启 MySQL
-- 关闭 MySQLmysqladmin -uroot -p -h 127.0.0.1 -P 3306 shutdown[mysql@jiekexu-test log] ps -ef | grep mysql root 70514 6312 0 15:51 pts/2 00:00:00 su - mysqlmysql 70515 70514 0 15:51 pts/2 00:00:00 -bashroot 80114 4056 0 18:12 pts/1 00:00:00 su - mysqlmysql 80115 80114 0 18:12 pts/1 00:00:00 -bashmysql 82575 70515 0 18:37 pts/2 00:00:00 ps -efmysql 82576 70515 0 18:37 pts/2 00:00:00 grep --color=auto mysql-- 启动 MySQL[mysql@jiekexu-test log] mysqld_safe --defaults-file=/mysql/conf/my3306.cnf &[1] 82594[mysql@jiekexu-test log] 2021-09-10T10:37:39.049156Z mysqld_safe Logging to '/mysql/data/mysql3306/log/mysqld.log'.2021-09-10T10:37:39.074538Z mysqld_safe Starting mysqld daemon with databases from /mysql/data/mysql3306/data[mysql@jiekexu-test log] ps -ef | grep mysql root 70514 6312 0 15:51 pts/2 00:00:00 su - mysqlmysql 70515 70514 0 15:51 pts/2 00:00:00 -bashroot 80114 4056 0 18:12 pts/1 00:00:00 su - mysqlmysql 80115 80114 0 18:12 pts/1 00:00:00 -bashmysql 82594 70515 0 18:37 pts/2 00:00:00 /bin/sh /mysql/app/mysql8.0.26/bin/mysqld_safe --defaults-file=/mysql/conf/my3306.cnfmysql 82903 82594 2 18:37 pts/2 00:00:01 /mysql/app/mysql8.0.26/bin/mysqld --defaults-file=/mysql/conf/my3306.cnf --basedir=/mysql/data/mysql3306 --datadir=/mysql/data/mysql3306/data --plugin-dir=/usr/local/mysql/lib/plugin --log-error=/mysql/data/mysql3306/log/mysqld.log --pid-file=/mysql/data/mysql3306/pid/mysqld.pid --socket=/mysql/data/mysql3306/socket/mysql.sock --port=3306mysql 83006 70515 0 18:38 pts/2 00:00:00 ps -efmysql 83007 70515 0 18:38 pts/2 00:00:00 grep --color=auto mysql[mysql@jiekexu-test log]
三、如果忘记 root 密码，则需要重置
在 配置文件中添加如下一行，重启 MySQL 登录则不需要 root 密码。
vim /mysql/conf/my3306.cnf
skip-grant-tables
[mysql@jiekexu-test conf]$ ps -ef | grep mysqld
mysql 13100 62624 0 15:11 pts/5 00:00:00 /bin/sh /mysql/app/mysql8.0.26/bin/mysqld_safe --defaults-file=/mysql/conf/my3306.cnf
mysql 14816 13100 9 15:27 pts/5 00:00:01 /mysql/app/mysql8.0.26/bin/mysqld --defaults-file=/mysql/conf/my3306.cnf --basedir=/mysql/data/mysql3306 --datadir=/mysql/data/mysql3306/data --plugin-dir=/usr/local/mysql/lib/plugin --log-error=/mysql/data/mysql3306/errlog/err3306.log --pid-file=/mysql/data/mysql3306/pid/mysqld.pid --socket=/mysql/data/mysql3306/socket/mysql.sock --port=3306
[mysql@jiekexu-test conf]$ kill 14816
重启 MySQL
[mysql@jiekexu-test conf]$ mysqld_safe --defaults-file=/mysql/conf/my3306.cnf &
[1] 13100
[mysql@jiekexu-test conf]$ 2021-09-24T07:11:30.280687Z mysqld_safe Logging to '/mysql/data/mysql3306/errlog/err3306.log'.
2021-09-24T07:11:30.308423Z mysqld_safe Starting mysqld daemon with databases from /mysql/data/mysql3306/data
[mysql@jiekexu-test conf]$
[mysql@jiekexu-test conf]$ mysql -uroot
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)
[mysql@jiekexu-test conf]$ mysql -uroot -S /mysql/data/mysql3306/socket/mysql.sock
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.26 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>
-- 修改密码
需要先刷新权限不然会报错无法执行 alter 语句。ERROR 1290 (HY000)
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'root123';
ERROR 1290 (HY000): The MySQL server is running with the --skip-grant-tables option so it cannot execute this statement
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'root123';
Query OK, 0 rows affected (0.02 sec)
mysql> ALTER USER 'root'@'%' IDENTIFIED BY 'root123';
Query OK, 0 rows affected (0.00 sec)
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)
mysql> shutdown;
Query OK, 0 rows affected (0.00 sec)
参数文件中注释掉 #skip-grant-tables 然后启动，可正常登录。
[mysql@jiekexu-test conf]$ mysqld_safe --defaults-file=/mysql/conf/my3306.cnf &
[1] 24737
[mysql@jiekexu-test conf]$ 2021-09-24T08:35:30.943299Z mysqld_safe Logging to '/mysql/data/mysql3306/errlog/err3306.log'.
2021-09-24T08:35:30.967091Z mysqld_safe Starting mysqld daemon with databases from /mysql/data/mysql3306/data
[mysql@jiekexu-test conf]$
[mysql@jiekexu-test conf]$ mysql -uroot -p -P 3306 -S /mysql/data/mysql3306/socket/mysql.sock
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.26 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> exit
Bye
[mysql@jiekexu-test conf]$ mysql -h 192.168.75.135-uroot -p -P 3306 -S /mysql/data/mysql3306/socket/mysql.sock
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 8.0.26 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.
--这里禁止修改 authentication_string 字段
mysql> select user,authentication_string from mysql.user;
+------------------+------------------------------------------------------------------------+
| user | authentication_string |
+------------------+------------------------------------------------------------------------+
| root | *FAAFFE644E901CFAFAEC7562415E5FAEC243B8B2 |
| mysql.infoschema | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| mysql.session | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| mysql.sys | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| root | $A$005$M_F>KVC1'3G#n~u6/VHFq2vMJX.z6I1ZW7Fr62UWwKGAs2SVTjfBNFrxs4 |
+------------------+------------------------------------------------------------------------+
5 rows in set (0.00 sec)
mysql>
还有一种办法,只能去修改 mysql 的 user 表，将加密字段authentication_string 置空，然后使用空密码登录，但不能修改 authentication_string 为其他值，使用密码登录。
use mysql;
mysql> update user set authentication_string='' where user='root';
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2 Changed: 2 Warnings: 0
mysql> shutdown;
Query OK, 0 rows affected (0.00 sec)
-- update user set authentication_string='root' where user='root'; 亲测这种修改方法不生效,无法登录。
[mysql@jiekexu-test conf]$ mysql -uroot -p -P 3306 -S /mysql/data/mysql3306/socket/mysql.sock
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.26 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> select user,authentication_string from mysql.user;
+------------------+------------------------------------------------------------------------+
| user | authentication_string |
+------------------+------------------------------------------------------------------------+
| root | |
| mysql.infoschema | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| mysql.session | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| mysql.sys | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| root | |
+------------------+------------------------------------------------------------------------+
5 rows in set (0.00 sec)
四、附件 生产环境组复制中 my3306 参数文件配置如下：
[mysqld]
# basic settings #
server_id = 12249
basedir = /mysql/app/mysql8.0.26
datadir = /mysql/data/mysql3306/data/
socket = /mysql/data/mysql3306/socket/mysql3306.sock
pid_file = /mysql/data/mysql3306/pid/mysqld3306.pid
port = 3306
default-time_zone = '+8:00'
character_set_server = utf8mb4
explicit_defaults_for_timestamp = 1
autocommit = 1
transaction_isolation = READ-COMMITTED
secure_file_priv = "/mysql/data/mysql3306/tmp/"
max_allowed_packet = 64M
lower_case_table_names = 1
default_authentication_plugin = mysql_native_password
sql_mode = 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION'
# connection #
back_log = 500
interactive_timeout = 300
wait_timeout = 300
lock_wait_timeout = 300
max_user_connections = 800
skip_name_resolve = 1
max_connections = 3000
max_connect_errors = 1000
#table cache performance settings
#table_open_cache = 1024
#table_definition_cache = 1024
#table_open_cache_instances = 16
#session memory settings #
#read_buffer_size = 16M
#read_rnd_buffer_size = 32M
#sort_buffer_size = 32M
#tmp_table_size = 64M
#join_buffer_size = 128M
#thread_cache_size = 256
# log settings #
slow_query_log = ON
slow_query_log_file = /mysql/data/mysql3306/slowlog/slow3306.log
log_error = /mysql/data/mysql3306/log/mysqld3306.log
log_error_verbosity = 3
log_bin = /mysql/data/mysql3306/binlog/mysql_bin
log_bin_index = /mysql/data/mysql3306/binlog/mysql_binlog.index
# general_log_file = /data/mysql/mysql57_3306/generallog/general.log
log_queries_not_using_indexes = 1
log_slow_admin_statements = 1
#log_slow_slave_statements = 1
#expire_logs_days = 15
binlog_expire_logs_seconds = 2592000
long_query_time = 2
min_examined_row_limit = 100
log_throttle_queries_not_using_indexes = 1000
#log_bin_trust_function_creators = 1
log_slave_updates = 1
mysqlx_port = 3306
mysqlx_socket = /mysql/data/mysql3306/socket/mysqlx.sock
# innodb settings #
innodb_buffer_pool_size = 1G
innodb_buffer_pool_instances = 16
innodb_log_buffer_size = 100M
innodb_buffer_pool_load_at_startup = 1
innodb_buffer_pool_dump_at_shutdown = 1
innodb_lru_scan_depth = 4096
innodb_lock_wait_timeout = 20
innodb_io_capacity = 5000
innodb_io_capacity_max = 10000
innodb_flush_method = O_DIRECT
innodb_log_file_size = 1G
innodb_log_files_in_group = 2
innodb_purge_threads = 4
innodb_thread_concurrency = 200
innodb_print_all_deadlocks = 1
innodb_strict_mode = 1
innodb_sort_buffer_size = 32M
innodb_write_io_threads = 16
innodb_read_io_threads = 16
innodb_file_per_table = 1
innodb_stats_persistent_sample_pages = 64
innodb_autoinc_lock_mode = 2
innodb_online_alter_log_max_size = 1G
innodb_open_files = 4096
innodb_buffer_pool_dump_pct = 25
innodb_page_cleaners = 16
innodb_undo_log_truncate = 1
innodb_max_undo_log_size = 2G
innodb_purge_rseg_truncate_frequency = 128
innodb_flush_log_at_trx_commit = 1
# replication settings #
master_info_repository = TABLE
relay_log_info_repository = TABLE
sync_binlog = 1
binlog_format = ROW
gtid_mode = ON
enforce_gtid_consistency = ON
relay_log_recovery = 1
relay_log = /mysql/data/mysql3306/relaylog/relay.log
relay_log_index = /mysql/data/mysql3306/relaylog/mysql_relay.index
slave_parallel_type = LOGICAL_CLOCK
slave_parallel_workers = 16
binlog_gtid_simple_recovery = 1
slave_preserve_commit_order = 1
binlog_rows_query_log_events = 1
slave_transaction_retries = 10
log_timestamps = system
report_host = 120.98.XX.XX
report_port = 3306