Linux 7.7 源码安装 MySQL 8.0.26

JiekeXu之路

发布于 2021-10-12 14:47:04

2.3K0

发布于 2021-10-12 14:47:04

文章被收录于专栏：JiekeXu之路JiekeXu之路

作者 | 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

本文参与腾讯云自媒体分享计划，分享自微信公众号。

原始发表：2021-09-24，如有侵权请联系 cloudcommunity@tencent.com 删除

云数据库 SQL Server

本文分享自 JiekeXu之路微信公众号，前往查看

如有侵权，请联系 cloudcommunity@tencent.com 删除。

本文参与腾讯云自媒体分享计划，欢迎热爱写作的你一起参与！

登录后参与评论

0 条评论

热度