教你Mysql的配置与密码忘记改咋办

Mysql基础应用

一、安装mysql模拟密码遗忘修改密码登录

1、安装mysql

[root@centos01 ~]# mount /dev/cdrom /mnt/
mount: /dev/sr0 写保护，将以只读方式挂载
[root@centos01 ~]# rm -rf /etc/yum.repos.d/CentOS-*
[root@centos01 ~]# yum -y install nucrses-devel cmake
[root@centos01 ~]# groupadd mysql
[root@centos01 ~]# useradd -M -s /sbin/nologin -g mysql mysql
[root@centos01 ~]# umount /mnt/

2）切换光盘

3）解压配置mysql

[root@centos01 ~]# tar zxf /mnt/mysql-5.5.22.tar.gz -C /usr/src/
[root@centos01 ~]# cd /usr/src/mysql-5.5.22/
[root@centos01 mysql-5.5.22]# cmake
-DCMAKE_INSTALL_PREFIX=/usr/local/mysql -DDEFAULT_CHARSET=utf8
-DDEFAULT_COLLATION=utf8_general_ci -DWITH_EXTRA_CHARSETS=all -DSYSCONFDIR=/etc
[root@centos01 mysql-5.5.22]# chown -R mysql:mysql /usr/local/mysql/
[root@centos01 mysql-5.5.22]# cp support-files/my-medium.cnf /etc/my.cnf
cp：是否覆盖"/etc/my.cnf"？y
[root@centos01 mysql-5.5.22]# chmod +x /etc/init.d/mysqld
[root@centos01 mysql-5.5.22]# chkconfig --add mysqld
[root@centos01 mysql-5.5.22]# chkconfig --level 35 mysqld on
[root@centos01 ~]# echo "PATH=$PATH:/usr/local/mysql/bin" >> /etc/profile
[root@centos01 ~]# source /etc/profile
root@centos01 ~]# mysql
mysql
mysqlaccess
mysqlaccess.conf
mysqladmin
mysqlbinlog
mysqlbug
mysqlcheck
mysql_client_test
mysql_config
mysql_convert_table_format
mysqld
mysqld_multi
mysqld_safe
mysqldump

5）初始化mysql

[root@centos01 ~]# /usr/local/mysql/scripts/mysql_install_db --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data
Installing MySQL system tables...
OK
Filling help tables...
OK

6）启动mysql

[root@centos01 ~]# netstat -anptu | grep
3306
tcp       
0      0 0.0.0.0:3306            0.0.0.0:*               LISTEN      1317/mysqld

7）模拟密码丢失修改密码密码

mysql> update mysql.user set password=PASSWORD('12345') where user='root‘；

二、安装mysql8.0然后创建用户设置密码修改root密码锁定解锁密码修改密码

1）安装msyql

[root@centos02 ~]# ls 
anaconda-ks.cfg                 root  视频  下载
initial-setup-ks.cfg            公共  图片  音乐
mysql-8.0.32-el7-x86_64.tar.gz  模板  文档  桌面

2）解压移动mysql

[root@centos02 ~]# tar zxf ./mysql-8.0.32-el7-x86_64.tar.gz -C /usr/src/
[root@centos02 ~]# mv /usr/src/mysql-8.0.32-el7-x86_64/ /usr/local/mysql
[root@centos02 ~]# mkdir /usr/local/mysql/data
[root@centos02 ~]# echo "PATH=$PATH:/usr/local/mysql/bin" >> /etc/profile
[root@centos02 ~]# source /etc/profile

3）创建mysql用户和组

[root@centos02 ~]# groupadd mysql
[root@centos02 ~]# useradd -M -s /sbin/nologin -g mysql mysql
[root@centos02 ~]# chown -R mysql:mysql /usr/local/mysql/

4）修改配置文件

[root@centos02 ~]# vim /etc/my.cnf
[mysqld]
user=mysql
basedir=/usr/local/mysql/
datadir=/usr/local/mysql/data/
socket=/tmp/mysql.sock
[mysql]
socket=/tmp/mysql.sock

5）生成服务控制文件

[root@centos02 ~]# cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld
[root@centos02 ~]# chmod +x /etc/init.d/mysqld 
[root@centos02 ~]# chkconfig --add mysqld
[root@centos02 ~]# chkconfig --level 35 mysqld on

6）初始化mysql

[root@centos02~]# mysqld --initialize-insecure
[root@centos02~]# systemctl start mysqld
[root@centos02~]# netstat -anptu | grep 3306
tcp6       0     
0 :::33060               
:::*                    LISTEN      1133/mysqld         
tcp6       0     
0 :::3306                
:::*                    LISTEN      1133/mysqld

7）修改root密码

mysql> alter user root@'localhost' identified by 'pwd@1234';

8）创建用户设置密码

mysql> create user 'stz'@'localhost' identified by 'pwd@123';

9）锁定用户

mysql> alter user 'stz'@'localhost' account lock;

10）解锁用户

mysql> alter user 'stz'@'localhost' account unlock;

11）模拟root用户密码忘记修改密码

[root@centos02~]# systemctl stop mysqld
[root@centos02~]# mysqld_safe --skip-grant-tables --skip-networking &
mysql -uroot -paaa
mysql> flush privileges;
mysql> alter user root@'localhost' identified by '12345';
[root@centos02~]# systemctl start mysql
[root@centos02~]# mysql -S /tmp/mysql.sock -uroot -p12345