mysql进阶
1. 二进制格式mysql安装
下载mysql源码包
https://downloads.mysql.com/archives/community/
使用xftp传过来
创建mysql的用户和组
[root@localhost ~]# useradd -r -M -s /sbin/nologin mysql //创建用户和组
[root@localhost ~]# id mysql
uid=994(mysql) gid=991(mysql) groups=991(mysql)
[root@localhost ~]# grep mysql /etc/group
mysql:x:991:
[root@localhost ~]#
解压mysql源码包
[root@localhost ~]# ls
anaconda-ks.cfg mysql-5.7.31-linux-glibc2.12-x86_64.tar.gz
[root@localhost ~]# tar xf mysql-5.7.31-linux-glibc2.12-x86_64.tar.gz -C /usr/local/ //-C 后面加上解压缩的位置
创建一个软链接
[root@localhost ~]# ls /usr/local/
bin games lib libexec sbin src
etc include lib64 mysql-5.7.31-linux-glibc2.12-x86_64 share
[root@localhost ~]# ln -s /usr/local/mysql-5.7.31-linux-glibc2.12-x86_64 /usr/local/mysql
[root@localhost ~]# ll /usr/local/
total 0
drwxr-xr-x. 2 root root 6 Aug 12 2018 bin
drwxr-xr-x. 2 root root 6 Aug 12 2018 etc
drwxr-xr-x. 2 root root 6 Aug 12 2018 games
drwxr-xr-x. 2 root root 6 Aug 12 2018 include
drwxr-xr-x. 2 root root 6 Aug 12 2018 lib
drwxr-xr-x. 2 root root 6 Aug 12 2018 lib64
drwxr-xr-x. 2 root root 6 Aug 12 2018 libexec
lrwxrwxrwx. 1 root root 46 Dec 28 19:04 mysql -> /usr/local/mysql-5.7.31-linux-glibc2.12-x86_64
drwxr-xr-x. 9 7161 31415 129 Jun 2 2020 mysql-5.7.31-linux-glibc2.12-x86_64
drwxr-xr-x. 2 root root 6 Aug 12 2018 sbin
drwxr-xr-x. 5 root root 49 Dec 28 11:58 share
drwxr-xr-x. 2 root root 6 Aug 12 2018 src
[root@localhost ~]#
修改属主和属组
[root@localhost ~]# chown -R mysql.mysql /usr/local/mysql*
[root@localhost ~]# ll /usr/local/
total 0
drwxr-xr-x. 2 root root 6 Aug 12 2018 bin
drwxr-xr-x. 2 root root 6 Aug 12 2018 etc
drwxr-xr-x. 2 root root 6 Aug 12 2018 games
drwxr-xr-x. 2 root root 6 Aug 12 2018 include
drwxr-xr-x. 2 root root 6 Aug 12 2018 lib
drwxr-xr-x. 2 root root 6 Aug 12 2018 lib64
drwxr-xr-x. 2 root root 6 Aug 12 2018 libexec
lrwxrwxrwx. 1 mysql mysql 46 Dec 28 19:04 mysql -> /usr/local/mysql-5.7.31-linux-glibc2.12-x86_64
drwxr-xr-x. 9 mysql mysql 129 Jun 2 2020 mysql-5.7.31-linux-glibc2.12-x86_64
drwxr-xr-x. 2 root root 6 Aug 12 2018 sbin
drwxr-xr-x. 5 root root 49 Dec 28 11:58 share
drwxr-xr-x. 2 root root 6 Aug 12 2018 src
[root@localhost ~]#
添加一个环境变量
[root@localhost ~]# which mysql //找不到mysql命令
/usr/bin/which: no mysql in (/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin)
[root@localhost ~]# echo 'export PATH=/usr/local/mysql/bin:$PATH' > /etc/profile.d/mysql.sh //添加一个环境变量
[root@localhost ~]# source /etc/profile.d/mysql.sh
[root@localhost ~]# echo $PATH
/usr/local/mysql/bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin
[root@localhost ~]# which mysql
/usr/local/mysql/bin/mysql
[root@localhost ~]#
进到mysql目录，设置man帮助文档，创建软链接，设置lib库位置
[root@localhost ~]# cd /usr/local/mysql
[root@localhost mysql]# ls
bin docs include lib LICENSE man README share support-files
[root@localhost mysql]# ln -s /usr/local/mysql/include /usr/include/mysql //把整个目录映射进去
[root@localhost mysql]# vim /etc/man_db.conf //编辑man文档
#MANDATORY_MANPATH /usr/src/pvm3/man
#
MANDATORY_MANPATH /usr/man
MANDATORY_MANPATH /usr/share/man
MANDATORY_MANPATH /usr/local/share/man
MANDATORY_MANPATH /usr/local/mysql/man //添加这一行配置
[root@localhost mysql]# vim /etc/ld.so.conf.d/mysql.conf //设置lib库的位置
/usr/local/mysql/lib
[root@localhost mysql]# ldconfig //重新读取一下配置
建立数据存放目录
[root@localhost ~]# ls /opt/
[root@localhost ~]# mkdir /opt/data
[root@localhost ~]# chown -R mysql.mysql /opt/data
[root@localhost ~]# ll /opt/
total 0
drwxr-xr-x. 2 mysql mysql 6 Dec 28 19:28 data
[root@localhost ~]#
初始化数据库
[root@localhost ~]# mysqld --initialize --user=mysql --datadir=/opt/data //初始化数据库
2020-12-28T11:31:38.522417Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2020-12-28T11:31:38.738285Z 0 [Warning] InnoDB: New log files created, LSN=45790
2020-12-28T11:31:38.778062Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2020-12-28T11:31:38.833613Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: 400cbbae-4900-11eb-afe2-000c29ffb3cc.
2020-12-28T11:31:38.834297Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2020-12-28T11:31:39.563303Z 0 [Warning] CA certificate ca.pem is self signed.
2020-12-28T11:31:39.683255Z 1 [Note] A temporary password is generated for root@localhost: 8ebhwY8r)0u> //生成了一个随机密码，这里是8ebhwY8r)0u>
注意：这里的密码是临时的，每次初始化密码都是不一样的，登录时会用到。
生成配置文件
[root@localhost ~]# vim /etc/my.cnf
[mysqld] //主程序
basedir = /usr/local/mysql //数据库安装的位置
datadir = /opt/data //数据存放的位置
socket = /tmp/mysql.sock //套接字的位置
port = 3306 //端口号
pid-file = /opt/data/mysql.pid //进程PID文件
user = mysql //用户的身份
skip-name-resolve //跳过名称解析
配置服务启动脚本
[root@localhost ~]# cd /usr/local/mysql
[root@localhost mysql]# ls
bin docs include lib LICENSE man README share support-files
[root@localhost mysql]# cd support-files/
[root@localhost support-files]# ls
magic mysqld_multi.server mysql-log-rotate mysql.server
[root@localhost support-files]# file mysql.server //这是一个脚本文件
mysql.server: POSIX shell script, ASCII text executable
[root@localhost support-files]# cp mysql.server /etc/init.d/mysqld //拷贝到mysqld
[root@localhost support-files]# cd
[root@localhost ~]# ll /etc/init.d/mysqld
-rwxr-xr-x. 1 root root 10576 Dec 28 19:49 /etc/init.d/mysqld
[root@localhost ~]# vim /etc/init.d/mysqld //编辑这个文件
# If you change base dir, you must also change datadir. These may get
# overwritten by settings in the MySQL configuration files.
basedir=/usr/local/mysql //数据库装的位置
datadir=/opt/data //数据存放的位置
启动服务
[root@localhost ~]# service mysqld start //启动mysqld
Starting MySQL.Logging to '/opt/data/localhost.localdomain.err'.
SUCCESS!
[root@localhost ~]# ss -antl //端口号已已启动
State Recv-Q Send-Q Local Address:Port Peer Address:Port
LISTEN 0 128 0.0.0.0:22 0.0.0.0:*
LISTEN 0 80 *:3306 *:*
LISTEN 0 128 [::]:22 [::]:*
停止服务
[root@localhost ~]# service mysqld status
SUCCESS! MySQL running (14233)
[root@localhost ~]# service mysqld stop
Shutting down MySQL.. SUCCESS!
[root@localhost ~]# ss -antl
State Recv-Q Send-Q Local Address:Port Peer Address:Port
LISTEN 0 128 0.0.0.0:22 0.0.0.0:*
LISTEN 0 128 [::]:22 [::]:*
[root@localhost ~]#
设置一个临时的密码
//设置密码时候报错
[root@localhost ~]# mysql -uroot -p'8ebhwY8r)0u>'
mysql: error while loading shared libraries: libncurses.so.5: cannot open shared object file: No such file or directory
//解决报错
[root@localhost ~]# yum whatprovides libncurses.so.5 /查询libncurses.so.5包
Updating Subscription Management repositories.
Unable to read consumer identity
This system is not registered to Red Hat Subscription Management. You can use subscription-manager to register.
Last metadata expiration check: 0:23:03 ago on Mon 28 Dec 2020 07:49:11 PM CST.
ncurses-compat-libs-6.1-7.20180224.el8.i686 : Ncurses compatibility libraries
Repo : BaseOS
Matched from:
Provide : libncurses.so.5
[root@localhost ~]# yum -y install ncurses-compat-libs //安装这个包
Updating Subscription Management repositories.
Unable to read consumer identity
This system is not registered to Red Hat Subscription Management. You can use subscription-manager to register.
Last metadata expiration check: 0:16:37 ago on Mon 28 Dec 2020 07:49:11 PM CST.
Dependencies resolved.
======================================================================================
Package Architecture Version Repository Size
======================================================================================
Installing:
ncurses-compat-libs x86_64 6.1-7.20180224.el8 BaseOS 331 k
Transaction Summary
======================================================================================
Install 1 Package
Total size: 331 k
Installed size: 1.2 M
Downloading Packages:
Running transaction check
Transaction check succeeded.
Installed:
ncurses-compat-libs-6.1-7.20180224.el8.x86_64
Complete!
[root@localhost ~]# mysql -uroot -p'8ebhwY8r)0u>' //成功修改密码且登录
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 2
Server version: 5.7.31
Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.
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>
修改密码
mysql> set password = password('123456');
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> quit
Bye
[root@localhost ~]# mysql -uroot -p123456
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 5
Server version: 5.7.31 MySQL Community Server (GPL)
Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.
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>
设置开机自启动
[root@localhost ~]# chkconfig --list //这里是没有自启动服务的
Note: This output shows SysV services only and does not include native
systemd services. SysV configuration data might be overridden by native
systemd configuration.
If you want to list systemd services use 'systemctl list-unit-files'.
To see services enabled on particular target use
'systemctl list-dependencies [target]'.
[root@localhost ~]# chkconfig --add mysqld //添加自启动服务
[root@localhost ~]# chkconfig mysqld on //设置开机自启
[root@localhost ~]# chkconfig --list
Note: This output shows SysV services only and does not include native
systemd services. SysV configuration data might be overridden by native
systemd configuration.
If you want to list systemd services use 'systemctl list-unit-files'.
To see services enabled on particular target use
'systemctl list-dependencies [target]'.
mysqld 0:off 1:off 2:on 3:on 4:on 5:on 6:off
[root@localhost ~]#
reboot重启之后，可以看到3306端口已启动
[root@localhost ~]# ss -antl
State Recv-Q Send-Q Local Address:Port Peer Address:Port
LISTEN 0 128 0.0.0.0:22 0.0.0.0:*
LISTEN 0 128 [::]:22 [::]:*
LISTEN 0 80 *:3306 *:*
[root@localhos t ~]#
2. mysql配置文件
mysql的配置文件时my.cnf
创建一个.my.cnf文件
[root@localhost ~]# vim .my.cnf
[client]
user=root //用户
password=123456 //密码
用mysql可以直接登录
[root@localhost ~]# mysql
Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 3
Server version: 5.7.31 MySQL Community Server (GPL)
Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.
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>
[root@localhost ~]# mysql -uroot -e 'show databases;'
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
mysql常用配置文件参数：
| 参数 | 说明 |
|---|---|
| port = 3306 | 设置监听端口 |
| socket = /tmp/mysql.sock | 指定套接字文件位置 |
| basedir = /usr/local/mysql | 指定MySQL的安装路径 |
| datadir = /data/mysql | 指定MySQL的数据存放路径 |
| pid-file = /data/mysql/mysql.pid | 指定进程ID文件存放路径 |
| user = mysql | 指定MySQL以什么用户的身份提供服务 |
| skip-name-resolve | 禁止MySQL对外部连接进行DNS解析 使用这一选项可以消除MySQL进行DNS解析的时间。 若开启该选项，则所有远程主机连接授权都要使用IP地址方 式否则MySQL将无法正常处理连接请求 |
3. mysql数据库备份与恢复
3.1 数据库常用备份方案
数据库备份方案：
- 全量备份
- 增量备份
- 差异备份
| 备份方案 | 特点 |
|---|---|
| 全量备份 | 全量备份就是指对某一个时间点上的所有数据或应用进行的一个完全拷贝。 数据恢复快。 备份时间长 |
| 增量备份 | 增量备份是指在一次全备份或上一次增量备份后，以后每次的备份只需备份 与前一次相比增加和者被修改的文件。这就意味着，第一次增量备份的对象 是进行全备后所产生的增加和修改的文件；第二次增量备份的对象是进行第一次增量 备份后所产生的增加和修改的文件，如此类推。 没有重复的备份数据 备份时间短 恢复数据时必须按一定的顺序进行 |
| 差异备份 | 备份上一次的完全备份后发生变化的所有文件。 差异备份是指在一次全备份后到进行差异备份的这段时间内 对那些增加或者修改文件的备份。在进行恢复时，我们只需对第一次全量备份和最后一次差异备份进行恢复。 |
3.2 mysql备份工具mysqldump
创建一个数据，方便等下演示
mysql> create database school;
Query OK, 1 row affected (0.00 sec)
mysql> use school;
Database changed
mysql> create table student(id int not null primary key auto_increment,name varchar(50),age tinyint); //创建一个student表
Query OK, 0 rows affected (0.02 sec)
mysql> insert student(name,age) values('tom',20),('jerry',15),('zhanm'y's'q'kgsan','23'); //插入三条数据
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from student;
+----+----------+------+
| id | name | age |
+----+----------+------+
| 1 | tom | 20 |
| 2 | jerry | 15 |
| 3 | zhangsan | 23 |
+----+----------+------+
3 rows in set (0.00 sec)
mysql>
备份所有数据库
[root@localhost ~]# mysqldump -uroot -p5201314 --all-databases > all.sql //备份到all.sql里
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@localhost ~]# ls
all.sql anaconda-ks.cfg mysql-5.7.31-linux-glibc2.12-x86_64.tar.gz pass
[root@localhost ~]# file all.sql
all.sql: UTF-8 Unicode text, with very long lines
[root@localhost ~]#
不小心删除school库
[root@localhost ~]# mysql -uroot -p5201314 -e 'drop database school;'
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@localhost ~]# mysql -uroot -p5201314 -e 'show databases;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
[root@localhost ~]#
恢复数据
[root@localhost ~]# mysql -uroot -p5201314 < all.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@localhost ~]# mysql -uroot -p5201314 -e 'show databases;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| school |
| sys |
+--------------------+
[root@localhost ~]# mysql -uroot -p5201314 -e 'select * from school.student;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+----+----------+------+
| id | name | age |
+----+----------+------+
| 1 | tom | 20 |
| 2 | jerry | 15 |
| 3 | zhangsan | 23 |
+----+----------+------+
[root@localhost ~]#
4. mysql破解密码
步骤
- 修改配置文件，添加一行‘’跳过授权表‘’
[root@localhost ~]# vim /etc/my.cnf
[mysqld]
basedir = /usr/local/mysql
datadir = /opt/data
socket = /tmp/mysql.sock
port = 3306
pid-file = /opt/data/mysql.pid
user = mysql
skip-name-resolve
skip-grant-tables //加入这一行配置
- 重启服务
[root@localhost ~]# service mysqld restart
Shutting down MySQL.. SUCCESS!
Starting MySQL. SUCCESS!
[root@localhost ~]#
- 登陆数据库并修改密码（update）
mysql> show databases; //查看库
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)
mysql> use mysql; //进到mysql库
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show tables;
+---------------------------+
| Tables_in_mysql |
+---------------------------+
| columns_priv |
| db |
| engine_cost |
| event |
| func |
| general_log |
| gtid_executed |
| help_category |
| help_keyword |
| help_relation |
| help_topic |
| innodb_index_stats |
| innodb_table_stats |
| ndb_binlog_index |
| plugin |
| proc |
| procs_priv |
| proxies_priv |
| server_cost |
| servers |
| slave_master_info |
| slave_relay_log_info |
| slave_worker_info |
| slow_log |
| tables_priv |
| time_zone |
| time_zone_leap_second |
| time_zone_name |
| time_zone_transition |
| time_zone_transition_type |
| user |
+---------------------------+
31 rows in set (0.00 sec)
mysql> select * from userG //查看user表
*************************** 1. row ***************************
Host: localhost
User: root
Select_priv: Y
Insert_priv: Y
Update_priv: Y
Delete_priv: Y
Create_priv: Y
Drop_priv: Y
Reload_priv: Y
Shutdown_priv: Y
Process_priv: Y
File_priv: Y
Grant_priv: Y
References_priv: Y
Index_priv: Y
Alter_priv: Y
Show_db_priv: Y
Super_priv: Y
Create_tmp_table_priv: Y
Lock_tables_priv: Y
Execute_priv: Y
Repl_slave_priv: Y
Repl_client_priv: Y
Create_view_priv: Y
Show_view_priv: Y
Create_routine_priv: Y
Alter_routine_priv: Y
Create_user_priv: Y
Event_priv: Y
Trigger_priv: Y
Create_tablespace_priv: Y
ssl_type:
ssl_cipher:
x509_issuer:
x509_subject:
max_questions: 0
max_updates: 0
max_connections: 0
max_user_connections: 0
plugin: mysql_native_password
authentication_string: *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9
password_expired: N
password_last_changed: 2020-12-28 23:55:49
password_lifetime: NULL
account_locked: N
*************************** 2. row ***************************
Host: localhost
User: mysql.session
Select_priv: N
Insert_priv: N
Update_priv: N
Delete_priv: N
Create_priv: N
Drop_priv: N
Reload_priv: N
Shutdown_priv: N
Process_priv: N
File_priv: N
Grant_priv: N
References_priv: N
Index_priv: N
Alter_priv: N
Show_db_priv: N
Super_priv: Y
Create_tmp_table_priv: N
Lock_tables_priv: N
Execute_priv: N
Repl_slave_priv: N
Repl_client_priv: N
Create_view_priv: N
Show_view_priv: N
Create_routine_priv: N
Alter_routine_priv: N
Create_user_priv: N
Event_priv: N
Trigger_priv: N
Create_tablespace_priv: N
ssl_type:
ssl_cipher:
x509_issuer:
x509_subject:
max_questions: 0
max_updates: 0
max_connections: 0
max_user_connections: 0
plugin: mysql_native_password
authentication_string: *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE
password_expired: N
password_last_changed: 2020-12-28 19:31:39
password_lifetime: NULL
account_locked: Y
*************************** 3. row ***************************
Host: localhost
User: mysql.sys
Select_priv: N
Insert_priv: N
Update_priv: N
Delete_priv: N
Create_priv: N
Drop_priv: N
Reload_priv: N
Shutdown_priv: N
Process_priv: N
File_priv: N
Grant_priv: N
References_priv: N
Index_priv: N
Alter_priv: N
Show_db_priv: N
Super_priv: N
Create_tmp_table_priv: N
Lock_tables_priv: N
Execute_priv: N
Repl_slave_priv: N
Repl_client_priv: N
Create_view_priv: N
Show_view_priv: N
Create_routine_priv: N
Alter_routine_priv: N
Create_user_priv: N
Event_priv: N
Trigger_priv: N
Create_tablespace_priv: N
ssl_type:
ssl_cipher:
x509_issuer:
x509_subject:
max_questions: 0
max_updates: 0
max_connections: 0
max_user_connections: 0
plugin: mysql_native_password
authentication_string: *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE
password_expired: N
password_last_changed: 2020-12-28 19:31:39
password_lifetime: NULL
account_locked: Y
3 rows in set (0.00 sec)
mysql> update user set authentication_string =password('5201314') where User='root' andd Host='localhost'; //设置密码为5201314
Query OK, 1 row affected, 1 warning (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 1
mysql> select * from userG
*************************** 1. row ***************************
Host: localhost
User: root
Select_priv: Y
Insert_priv: Y
Update_priv: Y
Delete_priv: Y
Create_priv: Y
Drop_priv: Y
Reload_priv: Y
Shutdown_priv: Y
Process_priv: Y
File_priv: Y
Grant_priv: Y
References_priv: Y
Index_priv: Y
Alter_priv: Y
Show_db_priv: Y
Super_priv: Y
Create_tmp_table_priv: Y
Lock_tables_priv: Y
Execute_priv: Y
Repl_slave_priv: Y
Repl_client_priv: Y
Create_view_priv: Y
Show_view_priv: Y
Create_routine_priv: Y
Alter_routine_priv: Y
Create_user_priv: Y
Event_priv: Y
Trigger_priv: Y
Create_tablespace_priv: Y
ssl_type:
ssl_cipher:
x509_issuer:
x509_subject:
max_questions: 0
max_updates: 0
max_connections: 0
max_user_connections: 0
plugin: mysql_native_password
authentication_string: *E63DA3A990D28A0A591AAE74CA62817DEF2DD5B7
password_expired: N
password_last_changed: 2020-12-28 23:55:49
password_lifetime: NULL
account_locked: N
*************************** 2. row ***************************
Host: localhost
User: mysql.session
Select_priv: N
Insert_priv: N
Update_priv: N
Delete_priv: N
Create_priv: N
Drop_priv: N
Reload_priv: N
Shutdown_priv: N
Process_priv: N
File_priv: N
Grant_priv: N
References_priv: N
Index_priv: N
Alter_priv: N
Show_db_priv: N
Super_priv: Y
Create_tmp_table_priv: N
Lock_tables_priv: N
Execute_priv: N
Repl_slave_priv: N
Repl_client_priv: N
Create_view_priv: N
Show_view_priv: N
Create_routine_priv: N
Alter_routine_priv: N
Create_user_priv: N
Event_priv: N
Trigger_priv: N
Create_tablespace_priv: N
ssl_type:
ssl_cipher:
x509_issuer:
x509_subject:
max_questions: 0
max_updates: 0
max_connections: 0
max_user_connections: 0
plugin: mysql_native_password
authentication_string: *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE
password_expired: N
password_last_changed: 2020-12-28 19:31:39
password_lifetime: NULL
account_locked: Y
*************************** 3. row ***************************
Host: localhost
User: mysql.sys
Select_priv: N
Insert_priv: N
Update_priv: N
Delete_priv: N
Create_priv: N
Drop_priv: N
Reload_priv: N
Shutdown_priv: N
Process_priv: N
File_priv: N
Grant_priv: N
References_priv: N
Index_priv: N
Alter_priv: N
Show_db_priv: N
Super_priv: N
Create_tmp_table_priv: N
Lock_tables_priv: N
Execute_priv: N
Repl_slave_priv: N
Repl_client_priv: N
Create_view_priv: N
Show_view_priv: N
Create_routine_priv: N
Alter_routine_priv: N
Create_user_priv: N
Event_priv: N
Trigger_priv: N
Create_tablespace_priv: N
ssl_type:
ssl_cipher:
x509_issuer:
x509_subject:
max_questions: 0
max_updates: 0
max_connections: 0
max_user_connections: 0
plugin: mysql_native_password
authentication_string: *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE
password_expired: N
password_last_changed: 2020-12-28 19:31:39
password_lifetime: NULL
account_locked: Y
3 rows in set (0.01 sec)
mysql>
- 改配置文件，删除skip-grant-tables
[root@localhost ~]# vim /etc/my.cnf
[mysqld]
basedir = /usr/local/mysql
datadir = /opt/data
socket = /tmp/mysql.sock
port = 3306
pid-file = /opt/data/mysql.pid
user = mysql
skip-name-resolve
[root@localhost ~]#
- 重启服务
[root@localhost ~]# service mysqld restart
Shutting down MySQL.. SUCCESS!
Starting MySQL. SUCCESS!
[root@localhost ~]#
- 验证密码是否成功
[root@localhost ~]# mysql -uroot -p5201314
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 2
Server version: 5.7.31 MySQL Community Server (GPL)
Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.
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> quit
Bye
大功告成！！！