其他
Linux 7.7 源码安装 MySQL 8.0.26
作者 | 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-releaseRed Hat Enterprise Linux Server release 7.7 (Maipo)[root@jiekexu-test ~]# free -m total used free shared buff/cache availableMem: 7802 1248 2081 15 4472 4968Swap: 8191 0 8191[root@jiekexu-test ~]# df -h Filesystem Size Used Avail Use% Mounted ondevtmpfs 3.8G 0 3.8G 0% /devtmpfs 8.0G 0 8.0G 0% /dev/shmtmpfs 3.9G 13M 3.8G 1% /runtmpfs 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% /hometmpfs 781M 32K 781M 1% /run/user/0/dev/sr0 4.2G 4.2G 0 100% /mnt/dvd0.下载 MySQL 8.0.26
如下链接，选择相关的版本和系统。
https://dev.mysql.com/downloads/mysql/8.0.htmlMD5: 100a0e9336ef106a5fe90e6803b570661、RHEL7 关闭防火墙
systemctl stop firewalld.service或者systemctl stop firewalldsystemctl disable firewalld.service或者systemctl disable firewalldsystemctl 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 ~]# sestatusSELinux status: disabled3、/etc/hosts 解析(示例如下)
[root@jiekexu-test ~]# cat /etc/hosts127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4::1 localhost localhost.localdomain localhost6 localhost6.localdomain6
192.168.75.135 jiekexu-test4、安装所需 yum 包
-- 挂载本地光盘镜像[root@jiekexu-test yum.repos.d]# cd /mnt/dvd/[root@jiekexu-test dvd]# lltotal 0[root@jiekexu-test dvd]# [root@jiekexu-test dvd]# [root@jiekexu-test dvd]# mount /dev/sr0 /mnt/dvdmount: /dev/sr0 is write-protected, mounting read-only[root@jiekexu-test dvd]# [root@jiekexu-test dvd]# df -h Filesystem Size Used Avail Use% Mounted ondevtmpfs 3.8G 0 3.8G 0% /devtmpfs 8.0G 0 8.0G 0% /dev/shmtmpfs 3.9G 13M 3.8G 1% /runtmpfs 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% /hometmpfs 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 repobaseurl=file:///mnt/dvd/gpgcheck=0EOF
测试 yum 源yum repolist
[root@jiekexu-test dvd]# yum repolistLoaded plugins: langpacks, product-id, search-disabled-repos, subscription-managerThis 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 fastrepo id repo name status!rhel7 jiekexu repo 5,229repolist: 5,229
安装依赖包yum -y groupinstall "DeveLopment tools"yum -y install ncurses ncurses-devel openssl-devel bison gcc gcc-c++ make5、清理系统环境
CentOS 7 或 RHEL7 版本的系统默认自带安装了 MariaDB，需要先清理。
-- 查询已安装的mariadbrpm -qa |grep mariadb或yum list installed | grep mariadb[root@jiekexu-test app]# rpm -qa |grep mariadbmariadb-server-5.5.64-1.el7.x86_64mariadb-libs-5.5.64-1.el7.x86_64mariadb-5.5.64-1.el7.x86_64[root@jiekexu-test app]# yum list installed | grep mariadbmariadb.x86_64 1:5.5.64-1.el7 @anaconda/7.7mariadb-libs.x86_64 1:5.5.64-1.el7 @anaconda/7.7mariadb-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-managerThis 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 Resolutionrhel7 | 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 MRemoving 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 MDownloading packages:Running transaction checkRunning transaction testTransaction test succeededRunning 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/mysql3306mkdir -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 mysqlgroupadd mysqluseradd -g mysql mysqlchown -R mysql:mysql /mysqlpasswd mysql[root@jiekexu-test app]# cat /etc/group | grep mysqlmysql:x:27:[root@jiekexu-test app]# cat /etc/passwd | grep mysqlmysql:x:27:27:MariaDB Server:/var/lib/mysql:/sbin/nologin
[root@jiekexu-test app]# groupadd mysqlgroupadd: group 'mysql' already exists[root@jiekexu-test app]# [root@jiekexu-test app]# [root@jiekexu-test app]# useradd -g mysql mysqluseradd: 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 mysqluseradd: 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 mysqlChanging password for user mysql.New password: Retype new password: passwd: all authentication tokens updated successfully.[root@jiekexu-test app]# cat /etc/passwd | grep mysqlmysql:x:10002:54331::/home/mysql:/bin/bash[root@jiekexu-test app]# cat /etc/group | grep mysqlmysql:x:54331:2、上传软件包并解压安装程序包
mysql 用户操作：
cd /mysql/app
md5 值验证，保证下载到的软件包无破损无木马。
[mysql@jiekexu-test app]$ md5sum mysql-8.0.26-linux-glibc2.12-x86_64.tar.xz100a0e9336ef106a5fe90e6803b57066 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.xzmv mysql-8.0.26-linux-glibc2.12-x86_64 mysql8.0.263、配置 mysql 用户环境变量
cat >> /home/mysql/.bash_profile << "EOF"MYSQL_HOME=/mysql/app/mysql8.0.26PATH=$PATH:$HOME/.local/bin:$HOME/bin:$MYSQL_HOME/binEOF
source ~/.bash_profilewhich mysql4、创建参数文件
由于是二进制文件安装，数据库参数文件需要自己配置，以下是简单的参数配置。其他参数可依照个人需求添加。
vim /mysql/conf/my3306.cnf
[mysqld]server_id = 100default-storage-engine= InnoDBbasedir=/mysql/data/mysql3306datadir=/mysql/data/mysql3306/data/socket=/mysql/data/mysql3306/socket/mysql.socklog-error=/mysql/data/mysql3306/log/mysqld.logpid-file=/mysql/data/mysql3306/pid/mysqld.pid
port=3306default-time_zone='+8:00'# default_authentication_plugin=mysql_native_password # 加此参数可远程登陆
transaction_isolation=READ-COMMITTEDmax_connections=1500
back_log=500wait_timeout=1800max_user_connections=800innodb_buffer_pool_size=1024Minnodb_log_file_size=512Minnodb_log_buffer_size=40M
slow_query_log=ONlong_query_time=5
# log settings #slow_query_log = ONslow_query_log_file = /mysql/data/mysql3306/slowlog/slow3306.loglog_error = /mysql/data/mysql3306/errlog/err3306.loglog_error_verbosity = 3log_bin = /mysql/data/mysql3306/binlog/mysql_binlog_bin_index = /mysql/data/mysql3306/binlog/mysql_binlog.indexgeneral_log_file = /data/mysql/mysql3306/generallog/general.loglog_queries_not_using_indexes = 1log_slow_admin_statements = 1expire_logs_days = 90binlog_expire_logs_seconds = 2592000 #30dlong_query_time = 2min_examined_row_limit = 100log_throttle_queries_not_using_indexes = 1000innodb_flush_log_at_trx_commit=15、数据库初始化
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 password2021-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 -pEnter 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.sockEnter password: Welcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 8Server version: 8.0.26
Copyright (c) 2000, 2021, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.
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 1mysql> 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]$ mysql -uroot -p -P 3306 -S /mysql/data/mysql3306/socket/mysql.sockEnter password: Welcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 8Server version: 8.0.26 MySQL Community Server - GPLCopyright (c) 2000, 2021, Oracle and/or its affiliates.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql> show databases;+--------------------+| Database |+--------------------+| information_schema || mysql || performance_schema || sys |+--------------------+4 rows in set (0.00 sec)
三、如果忘记 root 密码，则需要重置
在 配置文件中添加如下一行，重启 MySQL 登录则不需要 root 密码。
vim /mysql/conf/my3306.cnf
skip-grant-tables
[mysql@jiekexu-test conf]$ ps -ef | grep mysqldmysql 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.cnfmysql 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 -urootERROR 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.sockWelcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 8Server 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 itsaffiliates. Other names may be trademarks of their respectiveowners.
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 statementmysql> 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.sockEnter password: Welcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 8Server 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 itsaffiliates. Other names may be trademarks of their respectiveowners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> exitBye[mysql@jiekexu-test conf]$ mysql -h 192.168.75.135-uroot -p -P 3306 -S /mysql/data/mysql3306/socket/mysql.sockEnter password: Welcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 9Server 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 itsaffiliates. Other names may be trademarks of their respectiveowners.
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.sockEnter password: Welcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 8Server 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 itsaffiliates. Other names may be trademarks of their respectiveowners.
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 = 12249basedir = /mysql/app/mysql8.0.26datadir = /mysql/data/mysql3306/data/socket = /mysql/data/mysql3306/socket/mysql3306.sockpid_file = /mysql/data/mysql3306/pid/mysqld3306.pidport = 3306default-time_zone = '+8:00'character_set_server = utf8mb4explicit_defaults_for_timestamp = 1autocommit = 1transaction_isolation = READ-COMMITTEDsecure_file_priv = "/mysql/data/mysql3306/tmp/"max_allowed_packet = 64Mlower_case_table_names = 1default_authentication_plugin = mysql_native_passwordsql_mode = 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION'
# connection #back_log = 500interactive_timeout = 300wait_timeout = 300lock_wait_timeout = 300max_user_connections = 800skip_name_resolve = 1max_connections = 3000max_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 = ONslow_query_log_file = /mysql/data/mysql3306/slowlog/slow3306.loglog_error = /mysql/data/mysql3306/log/mysqld3306.loglog_error_verbosity = 3log_bin = /mysql/data/mysql3306/binlog/mysql_binlog_bin_index = /mysql/data/mysql3306/binlog/mysql_binlog.index# general_log_file = /data/mysql/mysql57_3306/generallog/general.loglog_queries_not_using_indexes = 1log_slow_admin_statements = 1#log_slow_slave_statements = 1#expire_logs_days = 15binlog_expire_logs_seconds = 2592000long_query_time = 2min_examined_row_limit = 100log_throttle_queries_not_using_indexes = 1000#log_bin_trust_function_creators = 1log_slave_updates = 1mysqlx_port = 3306mysqlx_socket = /mysql/data/mysql3306/socket/mysqlx.sock
# innodb settings #innodb_buffer_pool_size = 1Ginnodb_buffer_pool_instances = 16innodb_log_buffer_size = 100Minnodb_buffer_pool_load_at_startup = 1innodb_buffer_pool_dump_at_shutdown = 1innodb_lru_scan_depth = 4096innodb_lock_wait_timeout = 20innodb_io_capacity = 5000innodb_io_capacity_max = 10000innodb_flush_method = O_DIRECTinnodb_log_file_size = 1Ginnodb_log_files_in_group = 2innodb_purge_threads = 4innodb_thread_concurrency = 200innodb_print_all_deadlocks = 1innodb_strict_mode = 1innodb_sort_buffer_size = 32Minnodb_write_io_threads = 16innodb_read_io_threads = 16 innodb_file_per_table = 1innodb_stats_persistent_sample_pages = 64innodb_autoinc_lock_mode = 2innodb_online_alter_log_max_size = 1Ginnodb_open_files = 4096innodb_buffer_pool_dump_pct = 25innodb_page_cleaners = 16innodb_undo_log_truncate = 1innodb_max_undo_log_size = 2Ginnodb_purge_rseg_truncate_frequency = 128innodb_flush_log_at_trx_commit = 1
# replication settings #master_info_repository = TABLErelay_log_info_repository = TABLEsync_binlog = 1binlog_format = ROWgtid_mode = ONenforce_gtid_consistency = ONrelay_log_recovery = 1relay_log = /mysql/data/mysql3306/relaylog/relay.logrelay_log_index = /mysql/data/mysql3306/relaylog/mysql_relay.indexslave_parallel_type = LOGICAL_CLOCKslave_parallel_workers = 16binlog_gtid_simple_recovery = 1slave_preserve_commit_order = 1binlog_rows_query_log_events = 1slave_transaction_retries = 10log_timestamps = systemreport_host = 120.98.XX.XXreport_port = 3306本次分享到此结束啦~
❤️ 关注我的公众号，来一起玩耍吧！！！
——————————————————————--—--————
公众号：JiekeXu DBA之路
墨天轮：https://www.modb.pro/u/4347
CSDN ：https://blog.csdn.net/JiekeXu
腾讯云：https://cloud.tencent.com/developer/user/5645107
————————————————————————----———