mysql进阶

最新推荐文章于 2024-11-09 01:36:38 发布

MD_雨落心尘.wyn

最新推荐文章于 2024-11-09 01:36:38 发布

阅读量267

点赞数

文章标签： mysql 数据库

本文链接：https://blog.youkuaiyun.com/qq_58733916/article/details/126005336

版权

这篇博客详细介绍了如何进行MySQL的二进制格式安装，包括创建用户和组、解压、初始化数据库等步骤，并提到了数据库密码的破解方法。还讨论了MySQL配置文件的查找次序及常用配置参数，以及在忘记密码时如何通过修改配置文件来重置密码。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

mysql进阶

文章目录

mysql进阶

1.二进制格式mysql安装

//下载二进制格式的mysql软件包

[root@localhost ~]# wget https://downloads.mysql.com/archives/get/p/23/file/mysql-5.7.38-linux-glibc2.12-x86_64.tar.gz
--2022-07-26 18:58:08--  https://downloads.mysql.com/archives/get/p/23/file/mysql-5.7.38-linux-glibc2.12-x86_64.tar.gz
Resolving downloads.mysql.com (downloads.mysql.com)... 23.13.191.247, 2600:140b:2:99d::2e31, 2600:140b:2:99c::2e31
Connecting to downloads.mysql.com (downloads.mysql.com)|23.13.191.247|:443... connected.
HTTP request sent, awaiting response... 302 Moved Temporarily
Location: https://cdn.mysql.com/archives/mysql-5.7/mysql-5.7.38-linux-glibc2.12-x86_64.tar.gz [following]
--2022-07-26 18:58:09--  https://cdn.mysql.com/archives/mysql-5.7/mysql-5.7.38-linux-glibc2.12-x86_64.tar.gz
Resolving cdn.mysql.com (cdn.mysql.com)... 104.123.73.43
Connecting to cdn.mysql.com (cdn.mysql.com)|104.123.73.43|:443... connected.
HTTP request sent, awaiting response... 200 OK
Length: 674830866 (644M) [application/x-tar-gz]
Saving to: ‘mysql-5.7.38-linux-glibc2.12-x86_64.tar.gz’

mysql-5.7.38-linux-glibc2.12-x86 100%[=======================================================>] 643.57M   655KB/s    in 26m 4s  

2022-07-26 19:24:14 (421 KB/s) - ‘mysql-5.7.38-linux-glibc2.12-x86_64.tar.gz’ saved [674830866/674830866]

//创建用户和组

[root@localhost ~]# groupadd -r mysql
[root@localhost ~]# useradd -M -s /sbin/nologin -g mysql mysql
[root@localhost ~]#

//解压软件至/usr/local/

[root@localhost ~]# ls
anaconda-ks.cfg  a.txt  mysql-5.7.38-linux-glibc2.12-x86_64.tar.gz
[root@localhost ~]# tar xf mysql-5.7.38-linux-glibc2.12-x86_64.tar.gz -C /usr/local/
[root@localhost ~]# ls /usr/local/
bin  etc  games  include  lib  lib64  libexec  mysql-5.7.38-linux-glibc2.12-x86_64  sbin  share  src

//设置软链接

[root@localhost ~]# cd /usr/local/
[root@localhost local]# ln -sv mysql-5.7.38-linux-glibc2.12-x86_64/ mysql
'mysql' -> 'mysql-5.7.38-linux-glibc2.12-x86_64/'
[root@localhost local]# ll
total 0
drwxr-xr-x. 2 root root   6 May 19  2020 bin
drwxr-xr-x. 2 root root   6 May 19  2020 etc
drwxr-xr-x. 2 root root   6 May 19  2020 games
drwxr-xr-x. 2 root root   6 May 19  2020 include
drwxr-xr-x. 2 root root   6 May 19  2020 lib
drwxr-xr-x. 3 root root  17 Jun 27 15:02 lib64
drwxr-xr-x. 2 root root   6 May 19  2020 libexec
lrwxrwxrwx. 1 root root  36 Jul 26 21:01 mysql -> mysql-5.7.38-linux-glibc2.12-x86_64/
drwxr-xr-x. 9 root root 129 Jul 26 20:48 mysql-5.7.38-linux-glibc2.12-x86_64
drwxr-xr-x. 2 root root   6 May 19  2020 sbin
drwxr-xr-x. 5 root root  49 Jun 27 15:02 share
drwxr-xr-x. 2 root root   6 May 19  2020 src

//修改目录/usr/local/mysql的属主属组

[root@localhost local]# chown -R mysql.mysql mysql
[root@localhost local]# chown -R mysql.mysql mysql-5.7.38-linux-glibc2.12-x86_64
[root@localhost local]# ll
total 0
drwxr-xr-x. 2 root  root    6 May 19  2020 bin
drwxr-xr-x. 2 root  root    6 May 19  2020 etc
drwxr-xr-x. 2 root  root    6 May 19  2020 games
drwxr-xr-x. 2 root  root    6 May 19  2020 include
drwxr-xr-x. 2 root  root    6 May 19  2020 lib
drwxr-xr-x. 3 root  root   17 Jun 27 15:02 lib64
drwxr-xr-x. 2 root  root    6 May 19  2020 libexec
lrwxrwxrwx. 1 mysql mysql  36 Jul 26 21:01 mysql -> mysql-5.7.38-linux-glibc2.12-x86_64/
drwxr-xr-x. 9 mysql mysql 129 Jul 26 20:48 mysql-5.7.38-linux-glibc2.12-x86_64
drwxr-xr-x. 2 root  root    6 May 19  2020 sbin
drwxr-xr-x. 5 root  root   49 Jun 27 15:02 share
drwxr-xr-x. 2 root  root    6 May 19  2020 src

//添加环境变量

[root@localhost local]# ls mysql
bin  docs  include  lib  LICENSE  man  README  share  support-files
[root@localhost local]# echo 'export PATH=$PATH:/usr/local/mysql/bin/' > /etc/profile.d/mysql.sh
[root@localhost local]# source /etc/profile.d/mysql.sh
[root@localhost local]# echo $PATH
/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin:/usr/local/mysql/bin/
[root@localhost local]# ln -s /usr/local/mysql/include /usr/include/mysql
[root@localhost local]# ll /usr/include/ | grep mysql
lrwxrwxrwx. 1 root root 24 Jul 26 22:14 mysql -> /usr/local/mysql/include
[root@localhost local]# vim /etc/ld.so.conf.d/mysql.conf
/usr/local/mysql/lib
[root@localhost local]# vi /etc/man_db.conf
......
MANDATORY_MANPATH                       /usr/man
MANDATORY_MANPATH                       /usr/share/man
MANDATORY_MANPATH                       /usr/local/share/man
MANDATORY_MANPATH                       /usr/local/mysql/man
......
[root@localhost local]# ldconfig

//建立数据存放目录

[root@localhost local]# cd
[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 Jul 26 22:24 data

//初始化数据库

[root@localhost ~]# mysqld --initialize --user=mysql --datadir=/opt/data/
2022-07-26T14:29:55.127779Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2022-07-26T14:29:55.546313Z 0 [Warning] InnoDB: New log files created, LSN=45790
2022-07-26T14:29:55.607512Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2022-07-26T14:29:55.676421Z 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: 6b63a9c6-0cef-11ed-871d-000c291bd2af.
2022-07-26T14:29:55.678490Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2022-07-26T14:29:56.040261Z 0 [Warning] A deprecated TLS version TLSv1 is enabled. Please use TLSv1.2 or higher.
2022-07-26T14:29:56.040290Z 0 [Warning] A deprecated TLS version TLSv1.1 is enabled. Please use TLSv1.2 or higher.
2022-07-26T14:29:56.041154Z 0 [Warning] CA certificate ca.pem is self signed.
2022-07-26T14:29:56.259801Z 1 [Note] A temporary password is generated for root@localhost: HVgkK1)q3qU/

//请注意，这个命令的最后会生成一个临时密码，此处密码是HVgkK1)q3qU/
//再次注意，这个密码是随机的，你的不会跟我一样，一定要记住这个密码，因为一会登录时会用到

[root@localhost ~]# echo 'HVgkK1)q3qU/' > password
[root@localhost ~]# ls
anaconda-ks.cfg  a.txt  mysql-5.7.38-linux-glibc2.12-x86_64.tar.gz  password

//生成配置文件

[root@localhost ~]# vim /etc/my.cnf
[root@localhost ~]# cat /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
sql-mode = STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

//配置服务启动脚本

[root@localhost ~]# cp -a /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld
[root@localhost ~]# sed -ri 's#^(basedir=).*#\1/usr/local/mysql#g' /etc/init.d/mysqld
[root@localhost ~]# sed -ri 's#^(datadir=).*#\1/opt/data#g' /etc/init.d/mysqld

//启动mysql

[root@localhost ~]# service mysqld start
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         Process         
LISTEN         0              128                          0.0.0.0:22                        0.0.0.0:*                            
LISTEN         0              80                                 *:3306                            *:*                            
LISTEN         0              128                             [::]:22                           [::]:*                   
[root@localhost ~]# ps -ef | grep mysqld
root       11028       1  0 22:45 pts/2    00:00:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/opt/data --pid-file=/opt/data/mysql.pid
mysql      11228   11028  0 22:45 pts/2    00:00:00 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/opt/data --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=localhost.localdomain.err --pid-file=/opt/data/mysql.pid --socket=/tmp/mysql.sock --port=3306
root       11264    1860  0 22:47 pts/2    00:00:00 grep --color=auto mysqld

//设置 mysql 服务开机自启

[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

//下载 libncurses.so.5 ，并使用临时密码登录 mysql

[root@localhost ~]# mysql -uroot -p
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
Last metadata expiration check: 1:14:01 ago on Tue 26 Jul 2022 09:40:19 PM CST.
ncurses-compat-libs-6.1-7.20180224.el8.i686 : Ncurses compatibility libraries
Repo        : baseos
Matched from:
Provide    : libncurses.so.5

ncurses-compat-libs-6.1-9.20180224.el8.i686 : Ncurses compatibility libraries
Repo        : baseos
Matched from:
Provide    : libncurses.so.5

[root@localhost ~]# yum install -y ncurses-compat-libs
Last metadata expiration check: 1:14:21 ago on Tue 26 Jul 2022 09:40:19 PM CST.
Dependencies resolved.
......

Installed:
  ncurses-compat-libs-6.1-9.20180224.el8.x86_64                                                                                   

Complete!

//修改密码
//使用临时密码登录

[root@localhost ~]# mysql -uroot -p'HVgkK1)q3qU/'
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.38

Copyright (c) 2000, 2022, 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> set password = password('wyn123');
Query OK, 0 rows affected, 1 warning (0.01 sec)

2. mysql配置文件

mysql的配置文件为/etc/my.cnf

配置文件查找次序：若在多个配置文件中均有设定，则最后找到的最终生效

/etc/my.cnf --> /etc/mysql/my.cnf --> --default-extra-file=/PATH/TO/CONF_FILE --> ~/.my.cnf

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将无法正常处理连接请求

[root@localhost ~]# vim .my.cnf
[client]
user=root
password=wyn123

[root@localhost ~]# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.38 MySQL Community Server (GPL)

Copyright (c) 2000, 2022, 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>

3. 数据库密码破解

1、编辑mysql配置文件
vim /etc/my.cnf
添加一行： skip-grant-tables

[root@localhost ~]# rm -f .my.cnf
[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
......

2、重启服务
service mysqld restart

[root@localhost ~]# service mysqld restart
Shutting down MySQL.. SUCCESS! 
Starting MySQL. SUCCESS!

3、验证登入
mysql

[root@localhost ~]# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.38 MySQL Community Server (GPL)

Copyright (c) 2000, 2022, 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>

4、修改密码
use msyql;
update user set authentication_string = Password(‘123456’) where Host = ‘localhost’ and User = ‘root’;

mysql> update mysql.user set authentication_string=password('123456') where User = 'root';
Query OK, 1 row affected, 1 warning (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 1

5、删除/etc/my.cnf中的skip-grant-tables

[root@localhost ~]# vim /etc/my.cnf
[root@localhost ~]# cat /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
sql-mode = STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

6、重启mysql服务

[root@localhost ~]# service mysqld restart
Shutting down MySQL.. SUCCESS! 
Starting MySQL. SUCCESS!

7、用新的密码去验证登入

[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 2
Server version: 5.7.38 MySQL Community Server (GPL)

Copyright (c) 2000, 2022, 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>