MySQL进阶（一）

MySQL进阶(一)

目录

• MySQL进阶(一)
  • 二进制安装MySQL
  • mysql配置文件
  • 数据库密码破解

二进制安装MySQL

#首先下载官网的mysql软件包
[root@liu ~]# wget https://downloads.mysql.com/archives/get/p/23/file/mysql-5.7.38-linux-glibc2.12-x86_64.tar.gz
[root@liu ~]# ls
anaconda-ks.cfg  mysql-5.7.37-linux-glibc2.12-x86_64.tar.gz
#创建一个系统用户mysql，指定其为不可登录用户
[root@liu ~]# useradd -M -s /sbin/nologin -r mysql
#解压下载的软件包到/usr/local下
[root@liu ~]# tar xf mysql-5.7.37-linux-glibc2.12-x86_64.tar.gz -C /usr/local/
[root@liu ~]# cd /usr/local
[root@liu local]# ls
apache  apr-util  etc    include  lib64    mysql-5.7.37-linux-glibc2.12-x86_64  share
apr     bin       games  lib      libexec  sbin                                 src
#为该目录做一个软链接
[root@liu local]# ln -sv mysql-5.7.37-linux-glibc2.12-x86_64/ mysql
'mysql' -> 'mysql-5.7.37-linux-glibc2.12-x86_64/'
[root@liu local]# ll
lrwxrwxrwx.  1 root root  36 Jul 26 20:57 mysql -> mysql-5.7.37-linux-glibc2.12-x86_64/
drwxr-xr-x.  9 root root 129 Jul 26 20:54 mysql-5.7.37-linux-glibc2.12-x86_64
#修改软链接和原目录的属主属组，防止权限问题出现
[root@liu local]# chown -R mysql.mysql mysql
[root@liu local]# chown -R mysql.mysql mysql-5.7.37-linux-glibc2.12-x86_64
[root@liu local]# ll
lrwxrwxrwx.  1 mysql mysql  36 Jul 26 20:57 mysql -> mysql-5.7.37-linux-glibc2.12-x86_64/
drwxr-xr-x.  9 mysql mysql 129 Jul 26 20:54 mysql-5.7.37-linux-glibc2.12-x86_64
#进入mysql目录下，查看文件
[root@liu local]# cd mysql
[root@liu mysql]# ls
bin  docs  include  lib  LICENSE  man  README  share  support-files
#添加环境变量
[root@liu mysql]# echo 'export PATH=$PATH:/usr/local/mysql/bin' > /etc/profile.d/mysql.sh
#刷新环境变量
[root@liu mysql]# source /etc/profile.d/mysql.sh 
#查看当前环境变量，是否添加成功
[root@liu mysql]# echo $PATH
/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/usr/local/apache/bin/:/root/bin:/usr/local/mysql/bin
#配置头文件软链接
[root@liu mysql]# ln -s /usr/local/mysql/include/ /usr/include/mysql
[root@liu mysql]# ll /usr/include/mysql/
lrwxrwxrwx. 1 root root     25 Jul 26 21:16 include -> /usr/local/mysql/include/
#配置lib库文件
[root@liu mysql]# vim /etc/ld.so.conf.d/mysql.conf
/usr/local/mysql/lib
#配置man手册
[root@liu mysql]# vim /etc/man_db.conf 
MANDATORY_MANPATH                       /usr/local/mysql/man
#建立存放mysql数据的目录
[root@liu mysql]# mkdir /opt/data 
[root@liu mysql]# chown -R mysql.mysql /opt/data/
[root@liu mysql]# ll -d /opt/data/
drwxr-xr-x. 2 mysql mysql 6 Jul 26 21:22 /opt/data/
#初始化数据库
[root@liu mysql]# mysqld --initialize --user=mysql --datadir=/opt/data/
***省略部分输出***
#最后一行会显示密码
2022-07-26T13:24:14.596251Z 1 [Note] A temporary password is generated for root@localhost: TofufFe1rr_p
#添加配置文件
[root@liu mysql]# 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		#禁止域名连接
sql-mode = 			#由于版本较低，添加mode防止报错	STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
#配置服务启动脚本
[root@liu support-files]# cp -a mysql.server /etc/init.d/mysqld
[root@liu support-files]# vim /etc/init.d/mysqld 
***省略部分输出***
basedir=/usr/local/mysql
datadir=/opt/data
***省略部分输出***
#启动mysql服务
[root@liu ~]# service mysqld start
Starting MySQL.Logging to '/opt/data/liu.err'.
. SUCCESS! 
#设置mysql服务开机自启
[root@liu ~]# chkconfig --add mysqld
[root@liu ~]# chkconfig mysqld on
[root@liu ~]# 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@liu ~]# mysql -uroot -p'TofufFe1rr_p'
mysql: error while loading shared libraries: libncurses.so.5: cannot open shared object file: No such file or directory
#使用yum搜索该依赖库文件
[root@liu ~]# yum whatprovides libncurses.so.5
Last metadata expiration check: 3:20:12 ago on Tue 26 Jul 2022 06:43:42 PM CST.
ncurses-compat-libs-6.1-9.20180224.el8.i686 : Ncurses compatibility libraries
Repo        : base
Matched from:
Provide    : libncurses.so.5
#安装
[root@liu ~]# yum -y install ncurses-compat-libs
Installed:
  ncurses-compat-libs-6.1-9.20180224.el8.x86_64                                                     
Complete!
#再次登录即可成功登录
[root@liu ~]#mysql -uroot -p'TofufFe1rr_p'
mysql> 
#修改初始密码
mysql> set password = password('123456');
Query OK, 0 rows affected, 1 warning (0.00 sec)

配置mysql免输密码登录

[root@liu ~]# vim .my.cnf
[client]	
user=root	#登录用户名
password=123456		#用户密码

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@liu ~]# rm -f .my.cnf 
#进入mysql配置文件内添加一行：skip-grant-tables
[root@liu ~]# vim /etc/my.cnf 
***省略部分输出***
skip-name-resolve
skip-grant-tables
***省略部分输出***
#重启mysql服务
[root@liu ~]# service mysqld restart
Shutting down MySQL.. SUCCESS! 
Starting MySQL. SUCCESS! 
#再次登录mysql，不需要指定账户密码
[root@liu ~]# mysql
mysql> 
#重新设置密码
mysql> update mysql.user set authentication_string=password('654321')where User = 'root';
Query OK, 1 row affected, 1 warning (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 1
#删除此前在配置文件中添加的内容，再重启服务即可重新验证登录
[root@liu ~]# vim /etc/my.cnf 
[root@liu ~]# service mysqld restart
Shutting down MySQL.. SUCCESS! 
Starting MySQL. SUCCESS! 
[root@liu ~]# mysql -uroot -p654321
mysql>