mysql 8.0.12修改密码_CentOS Linux 7.5 编译安装 MySQL 8.0.12 及修改密码问题

CentOS Linux 7.5 编译安装 MySQL 8.0

1、编译安装MySQL8.0

#版本信息

#cat /etc/RedHat-release

CentOS Linux release 7.5.1804 (Core)

#安装依赖包

yum -y install wget cmake gcc gcc-c++ ncurses ncurses-devel libaio-devel openssl openssl-devel

rpm -qa |grep libaio

yum install libaio libaio-devel

#下载源码包

wget https://dev.mysql.com/get/Downloads/MySQL-8.0/mysql-boost-8.0.12.tar.gz

#创建用户和建立数据目录并赋予用户目录权限

groupadd mysql

useradd -r -g mysql -s /sbin/nologin mysql

mkdir -p /usr/local/mysql

mkdir -p /data/mysql

chown -R mysql.mysql /usr/local/mysql

chown -R mysql.mysql /data/mysql

chmod -R 755 /usr/local/mysql/

chmod -R 755 /data/mysql/

#解压并开始安装

tar -zxf mysql-boost-8.0.12.tar.gz

#配置

cd mysql-8.0.12

cmake . -DCMAKE_INSTALL_PREFIX=/usr/local/mysql \

-DMYSQL_DATADIR=/data/mysql \

-DSYSCONFDIR=/etc \

-DMYSQL_TCP_PORT=3306 \

-DWITH_BOOST=~/mysql-8.0.12/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

#配置参数说明：

CMAKE_INSTALL_PREFIX ：指定基础安装目录

MYSQL_DATADIR ：指定数据目录

SYSCONFDIR=/etc ：指定配置文件扫描目录

MYSQL_TCP_PORT ：指定端口号

WITH_BOOST ：指定boost所在目录

DEFAULT_CHARSET ：指定默认字符集

DEFAULT_COLLATION ：指定默认排序规则

ENABLED_LOCAL_INFILE=ON ：是否为load data infile启用local

WITH_INNODB_MEMCACHED=ON :是否生成memcached共享库

#编译&安装

make && make install

#初始化数据库

mysqld --initialize-insecure --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysql

#建立配置文件

cat > /etc/my.cnf <

[client]

port=3306

socket=/tmp/mysql.sock

default-character-set=utf8

#user=root

#password=123

[mysqld]

server-id=1

#skip-grant-tables

port=3306

user=mysql

max_connections=200

socket=/tmp/mysql.sock

basedir=/usr/local/mysql

datadir=/data/mysql

pid-file=/data/mysql/mysql.pid

init-connect='SET NAMES utf8'

character-set-server=utf8

default-storage-engine=INNODB

log_error=/data/mysql/mysql-error.log

slow_query_log_file=/data/mysql/mysql-slow.log

[mysqldump]

quick

max_allowed_packet=16M

EOF

#配置环境变量

echo "PATH=/usr/local/mysql/bin:$PATH" >> /etc/profile

source /etc/profile

#配置启动脚本

cp ../mysql-8.0.12/support-files/mysql.server /etc/init.d/mysqld

vim /etc/init.d/mysqld

chmod +x /etc/init.d/mysqld

/etc/init.d/mysqld start

#centos7系统自启动脚本

cat /usr/lib/systemd/system/mysqld.service

[Unit]

Description=mysql server daemon

Documentation=man:mysql(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

Type=notify

TimeoutSec=0

PermissionsStartOnly=true

ExecStart=/usr/local/mysql/bin/mysqld $MYSQLD_OPTS

LimitNOFILE = 10000

Restart=always

RestartPreventExitStatus=1

Environment=MYSQLD_PARENT_PID=1

PrivateTmp=false

#启动MySQL

systemctl start mysqld.service

#修改root密码：使用WITH指定认证方式

mysql -uroot

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '你的密码';

ALTER USER 'root'@'localhost' IDENTIFIED WITH caching_sha2_password BY '你的密码';

#查看密码字段和加密方式

select host, user, authentication_string, plugin from user;

2、yum存储库安装MySQL

wget https://dev.mysql.com/get/mysql80-community-release-el7-1.noarch.rpm

yum localinstall mysql80-community-release-el7-1.noarch.rpm

yum repolist enabled|grep "mysql.*-community.*"

yum repolist all|grep mysql

yum install mysql-community-server

systemctl enable mysqld

systemctl start mysqld

netstat -lntup|grep 3306

#查看随机生成的密码

grep 'temporary password' /var/log/mysqld.log

mysql -uroot -p

#修改密码,密码必须要由大小写字母、数字和特性字符组成

alter user 'root'@'localhost' identified by 'PJY@123.com';

select version();

MySQL 8.0 修改密码问题

MySQL5.7和之前的用户修改密码方式：

mysql -uroot -e "Set password=password(‘123’);"

mysql -uroot -p123.com -e "use mysql;update user set authentication_string=password('456') where user='root';"

update mysql.user set authentication_string=password("123") where user='root';

以上三种方法在MySQL8.0以后版本中将不能使用，如果使用了将会导致在正确修改密码是报如下错误：

mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '123';

ERROR 1396 (HY000): Operation ALTER USER failed for 'root'@'localhost'

如遇上以上问题请使用update语句先清空authentication_string字段，然后再修改密码即可

update user set authentication_string='' where user='root';

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '你的密码';

所以特别提醒童鞋们：

MySQL8.0后请使用alter修改用户密码，因为在MySQL8.0以后的加密方式为caching_sha2_password，如果使用update修改密码会给user表中root用户的authentication_string字段下设置newpassowrd值，当再使用alter user 'root'@'localhost' identified by 'newpassword'修改密码时会一直报错，必须清空后再修改，因为authentication_string字段下只能是MySQL加密后的43位字符串密码，其他的会报格式错误，所以在MySQL8.0以后能修改密码的方法只能是：ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '你的密码';