1. MySQL 基础
2021-02-16 本文已影响0人
随便写写咯
1 MySQL基于yum源安装
1.1 包源
- yum/apt源: 红帽或者Ubuntu操作系统自带的MySQL都不是最新的版本, 如果要用最新版本, 需要去MySQL官网下载MySQL提供的repo仓库源. 安装后才能下载最新的版本
- MySQL官网: 官网包含所有的版本, 包括最新版
- 默认情况下: Centos 6 提供MySQL , CentOS 7 提供 MariaDB, CentOS 8 两个都有
1.2 客户端包和服务器端包
MySQL客户端由: mysql包提供
MySQL服务器端由: mysql-server包提供, 其中包含MySQL客户端
1.3 安装方法
- yum/apt
- 二进制安装
- 源码编译安装
1.4 yum 安装 mysql-server 官网最新版
CentOS-8, yum安装最新版MySQL
MySQL官网提供了rpm包, 其中包含了repo源文件和gpgkey文件. 只需要根据需要下载对应的rpm包即可. 下载后, 需要安装rpm包, 即可把MySQL源文件添加到yum源中, 之后既可以下载最新版的MySQL
- 步骤
- 从MySQL官网下载rpm包: https://dev.mysql.com/downloads/
- 这里使用 RHEL 8.0 版本: mysql80-community-release-el8-1.noarch.rpm
- yum安装下载好的rpm包, mysql80-community-release-el8-1.noarch.rpm, 之后MySQL源会被自动添加到yum源
[22:05:21 root@centos8-3 ~]#yum -y install mysql80-community-release-el8-1.noarch.rpm
[22:07:10 root@centos8-3 ~]#vim /etc/yum.repos.d/mysql-community.repo
[mysql80-community]
name=MySQL 8.0 Community Server
baseurl=http://repo.mysql.com/yum/mysql-8.0-community/el/8/$basearch/
enabled=1
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql
[22:07:02 root@centos8-3 ~]#rpm -ql mysql80-community-release-el8-1.noarch.rpm
warning: mysql80-community-release-el8-1.noarch.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY
/etc/pki/rpm-gpg/RPM-GPG-KEY-mysql
/etc/yum.repos.d/mysql-community-source.repo
/etc/yum.repos.d/mysql-community.repo
- 查看mysql80-community提供哪些包
[15:47:28 root@centos8-3 ~]#yum list --disablerepo=* --enablerepo=mysql80-community
Available Packages
mysql-community-client.x86_64 8.0.21-1.el8 mysql80-community
mysql-community-client-debuginfo.x86_64 8.0.21-1.el8 mysql80-community
mysql-community-common.x86_64 8.0.21-1.el8 mysql80-community
mysql-community-debuginfo.x86_64 8.0.21-1.el8 mysql80-community
mysql-community-debugsource.x86_64 8.0.21-1.el8 mysql80-community
mysql-community-devel.x86_64 8.0.21-1.el8 mysql80-community
mysql-community-libs.x86_64 8.0.21-1.el8 mysql80-community
mysql-community-libs-debuginfo.x86_64 8.0.21-1.el8 mysql80-community
mysql-community-server.x86_64 8.0.21-1.el8 mysql80-community
mysql-community-server-debug.x86_64 8.0.21-1.el8 mysql80-community
mysql-community-server-debug-debuginfo.x86_64 8.0.21-1.el8 mysql80-community
mysql-community-server-debuginfo.x86_64 8.0.21-1.el8 mysql80-community
mysql-community-test.x86_64 8.0.21-1.el8 mysql80-community
mysql-community-test-debuginfo.x86_64 8.0.21-1.el8 mysql80-community
mysql-ref-manual-8.0-en-html-chapter.noarch 1-20200617 mysql80-community
mysql-ref-manual-8.0-en-pdf.noarch 1-20200617 mysql80-communit
- yum安装对应的包即可
这里需要先禁用mysql模块,否则直接安装mysql-community-server会报错找不到包
关于这个问题的官方解释是：基于EL8的系统像RHEL8和Oracle Linux 8默认情况下启用MySQL模块。 除非禁用此模块，否则它将屏蔽MySQL存储库提供的软件包。
yum module disable mysql
yum -y install mysql-community-server.x86_64
systemctl enable --now mysqld
ss -ntl
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 *:3306 *:*
LISTEN 0 128 [::]:22 [::]:*
LISTEN 0 70 *:33060 *:*
[16:30:47 root@centos8-3 ~]#ll /var/lib/mysql
total 188868
-rw-r----- 1 mysql mysql 56 Feb 16 14:43 auto.cnf
-rw-r----- 1 mysql mysql 156 Feb 16 14:44 binlog.000001
-rw-r----- 1 mysql mysql 16 Feb 16 14:44 binlog.index
-rw------- 1 mysql mysql 1676 Feb 16 14:43 ca-key.pem
-rw-r--r-- 1 mysql mysql 1112 Feb 16 14:43 ca.pem
-rw-r--r-- 1 mysql mysql 1112 Feb 16 14:43 client-cert.pem
-rw------- 1 mysql mysql 1676 Feb 16 14:43 client-key.pem
-rw-r----- 1 mysql mysql 196608 Feb 16 14:44 '#ib_16384_0.dblwr'
-rw-r----- 1 mysql mysql 8585216 Feb 16 14:43 '#ib_16384_1.dblwr'
-rw-r----- 1 mysql mysql 5865 Feb 16 14:43 ib_buffer_pool
-rw-r----- 1 mysql mysql 12582912 Feb 16 14:44 ibdata1
-rw-r----- 1 mysql mysql 50331648 Feb 16 14:44 ib_logfile0
-rw-r----- 1 mysql mysql 50331648 Feb 16 14:43 ib_logfile1
-rw-r----- 1 mysql mysql 12582912 Feb 16 14:44 ibtmp1
drwxr-x--- 2 mysql mysql 187 Feb 16 14:44 '#innodb_temp'
drwxr-x--- 2 mysql mysql 143 Feb 16 14:43 mysql
-rw-r----- 1 mysql mysql 25165824 Feb 16 14:44 mysql.ibd
srwxrwxrwx 1 mysql mysql 0 Feb 16 14:44 mysql.sock
-rw------- 1 mysql mysql 6 Feb 16 14:44 mysql.sock.lock
drwxr-x--- 2 mysql mysql 8192 Feb 16 14:43 performance_schema
-rw------- 1 mysql mysql 1676 Feb 16 14:43 private_key.pem
-rw-r--r-- 1 mysql mysql 452 Feb 16 14:43 public_key.pem
-rw-r--r-- 1 mysql mysql 1112 Feb 16 14:43 server-cert.pem
-rw------- 1 mysql mysql 1680 Feb 16 14:43 server-key.pem
drwxr-x--- 2 mysql mysql 28 Feb 16 14:43 sys
-rw-r----- 1 mysql mysql 16777216 Feb 16 14:44 undo_001
-rw-r----- 1 mysql mysql 16777216 Feb 16 14:44 undo_002
#如果我们把数据目录中的所有文件都删除, 下次重启服务后, 如果能启动成功, 那么会在数据目录中生成新的数据文件.
#停止mysqld服务
[23:29:24 root@centos8-3 ~]#systemctl stop mysqld.service
[23:29:37 root@centos8-3 ~]#ss -ntl
State Recv-Q Send-Q Local Address:Port Peer Address:Port
LISTEN 0 128 0.0.0.0:5355 0.0.0.0:*
LISTEN 0 128 0.0.0.0:111 0.0.0.0:*
LISTEN 0 128 0.0.0.0:22 0.0.0.0:*
LISTEN 0 128 [::]:5355 [::]:*
LISTEN 0 128 [::]:111 [::]:*
LISTEN 0 128 [::]:22 [::]:*
#删除整个数据目录
[23:30:10 root@centos8-3 ~]#rm -rf /var/lib/mysql
[23:30:17 root@centos8-3 ~]#ll /var/lib/mysql
ls: cannot access '/var/lib/mysql': No such file or directory
[23:30:21 root@centos8-3 ~]#systemctl start mysqld.service #重启失败, 因为数据目录需要手动创建
Job for mysqld.service failed because the control process exited with error code.
See "systemctl status mysqld.service" and "journalctl -xe" for details.
[23:30:29 root@centos8-3 ~]#mkdir /var/lib/mysql
[23:31:15 root@centos8-3 ~]#ll -d /var/lib/mysql
drwxr-xr-x 2 root root 6 Aug 16 23:31 /var/lib/mysql
[23:31:18 root@centos8-3 ~]#systemctl start mysqld.service #重启还是失败, 因为数据目录的要求是755权限, 并且以mysql为所有者和所属组
Job for mysqld.service failed because the control process exited with error code.
See "systemctl status mysqld.service" and "journalctl -xe" for details.
[23:31:24 root@centos8-3 ~]#chown -R mysql /var/lib/mysql/
[23:31:37 root@centos8-3 ~]#ll -d /var/lib/mysql
drwxr-xr-x 2 mysql root 6 Aug 16 23:31 /var/lib/mysql
[23:31:38 root@centos8-3 ~]#chgrp -R mysql /var/lib/mysql/
[23:31:43 root@centos8-3 ~]#ll -d /var/lib/mysql
drwxr-xr-x 2 mysql mysql 6 Aug 16 23:31 /var/lib/mysql
[23:31:46 root@centos8-3 ~]#systemctl start mysqld.service
[23:31:52 root@centos8-3 ~]#ss -ntl
State Recv-Q Send-Q Local Address:Port Peer Address:Port
LISTEN 0 128 0.0.0.0:5355 0.0.0.0:*
LISTEN 0 128 0.0.0.0:111 0.0.0.0:*
LISTEN 0 128 0.0.0.0:22 0.0.0.0:*
LISTEN 0 80 *:3306 *:*
LISTEN 0 128 [::]:5355 [::]:*
LISTEN 0 128 [::]:111 [::]:*
LISTEN 0 128 [::]:22 [::]:*
# 总结, mysql数据目录的权限为755, 并且以mysql为属主和属组
MySQL8.0在yum安装MySQL官方提供的新版本时, 会自动生成随机root口令, 保存到/var/log/mysqld.log下, 需要用这个随机口令来做安全加固.
重置密码有复杂度要求. 并且, 用了默认密码登录后, 需要重置root密码才能执行操作, 或者安装后, 运行加固脚本.
而基于CentOS8自带的MySQL8.0.21, yum安装后无需密码即可登录, 并且无需重置密码就可以操作
- 数据库安全加固
mysql_secure_installation 脚本, 可以用来对MySQL进行安全加固, 适用于不同的版本
- 设置root密码
- 删除匿名用户
- 禁止root用户远程登录, 早期的版本, 如果把任意一个远程主机的主机名修改成了和数据库主机名一样的名字, 那么root就可以在对应的主机登录, 因此要禁止root远程登录. 因为早期的版本允许root在localhost和本机hostname主机登录
- 测试数据库是为了测试用
[23:29:39 root@centos8-3 ~]#which 'mysql_secure_installation'
/usr/bin/mysql_secure_installation
运行 mysql_secure_installation 脚本即可
补充: MySQL5.7开始, 匿名用户, root远程登录和testdb默认都是没有的
对于root密码, 新版的8.0有密码复杂度要求, 而自带的yum版本以及8.0以前的版本没有复杂度要求
- 登录MySQL
[23:57:32 root@centos8-3 ~]#mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 18
Server version: 8.0.23 MySQL Community Server - GPL
Copyright (c) 2000, 2021, 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> select user, host, authentication_string from mysql.user;
+------------------+-----------+------------------------------------------------------------------------+
| user | host | authentication_string |
+------------------+-----------+------------------------------------------------------------------------+
| mysql.infoschema | localhost | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| mysql.session | localhost | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| mysql.sys | localhost | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| root | localhost | $A$005$Tr{w<f@L^ 96J[]NiaoinbMJQKt/NrJYBq5goulu8LsPoyWxiSjn5Yp8zA |
+------------------+-----------+------------------------------------------------------------------------+
4 rows in set (0.00 sec)
- 如何实现批量的MySQL安装加固
- 在MySQL8.0以前, 该脚本是一个交互式脚本, 意味着, 脚本运行时需要手动输入指令, 不利于批量自动化管理
- 可以利用多行重定向
- 或者将多行重定向写到脚本里
- 而在8.0以后, 该脚本变成了可执行程序, 无法执行重定向批量加固, 需要使用mysqladmin或者mysql -e进行非交互式批量加固
# 加固脚本, 适用于加固文件是脚本而不是可执行程序
/usr/local/mysql/bin/mysql_secure_installation <<EOF
y
hahahaha
hahahaha
y
y
y
y
EOF
# 如果是二进制程序, 那么就需要用mysqladmin命令, 去修改root密码, 删除账户和测试数据库
# 不过从5.7版本开始, 已经没有test数据库, root远程登录以及匿名用户, 所以只需要修改root密码即可
mysqladmin -uroot -p'y1Y(?N%nafoj' password ZZyy522927.
2 MySQL基础操作
2.1 客户端指令
通过help命令查看, 而服务器端指令通过help contents查看
2.1.1 客户端命令 - system
system 或 (\!) 可以执行Linux服务器本地Shell命令.
MySQL[(none)]> system hostname
centos8-3.linux
[(none)]> system id
uid=0(root) gid=0(root) groups=0(root)
mysql> \! ls
anaconda-ks.cfg mysql_secure.sh Sys_Init.sh
2.1.2 客户端命令 - status
查看数据库当前状态, 连接用户等运行状态
mysql> status
--------------
mysql Ver 8.0.21 for Linux on x86_64 (Source distribution)
Connection id: 9
Current database:
Current user: root@localhost
SSL: Not in use
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server version: 8.0.21 Source distribution
Protocol version: 10
Connection: Localhost via UNIX socket
Server characterset: utf8mb4
Db characterset: utf8mb4
Client characterset: utf8mb4
Conn. characterset: utf8mb4
UNIX socket: /var/lib/mysql/mysql.sock
Binary data as: Hexadecimal
Uptime: 21 min 37 sec
Threads: 2 Questions: 7 Slow queries: 0 Opens: 118 Flush tables: 3 Open tables: 36 Queries per second avg: 0.005
--------------
2.1.3 修改客户端提示符
# 临时修改
mysql> prompt \r:\m:\s (\u@\h) [\d]>\_
PROMPT set to '\r:\m:\s (\u@\h) [\d]>\_'
07:20:49 (root@localhost) [(none)]> use 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
07:20:54 (root@localhost) [mysql]>
# 永久修改
[19:24:22 root@mysql8021 ~]#vim /etc/my.cnf
#
# This group is read both both by the client and the server
# use it for options that affect everything
#
[client-server]
#
# include all files from the config directory
#
!includedir /etc/my.cnf.d
[mysql]
prompt="\\r:\\m:\\s (\u@\h) [\d]>\_"
2.1.4 mysql -e 非交互式登录mysql进行操作
[19:31:44 root@centos8-3 ~]#mysql -uroot -pcentos -e 'show databases'
+--------------------+
| Database |
+--------------------+
| database |
| information_schema |
| mysql |
| performance_schema |
| sqldb |
+--------------------+
[19:33:15 root@centos8-3 ~]#echo 'show databases' | mysql -uroot -pcentos
Database
database
information_schema
mysql
performance_schema
sqldb
[19:33:30 root@centos8-3 ~]#echo 'show databases' > test.sql
[19:34:19 root@centos8-3 ~]#mysql -uroot -pcentos < test.sql
Database
database
information_schema
mysql
performance_schema
sqldb
2.1.5 修改客户端配置
MySQL有多种客户端, 如mysql/mysqladmin...可以连接到数据库
可以在/etc/my.cnf或者/etc/my.cnf.d目录中的文件, 定义客户端参数, 比如定义提示符或者使用的用户和密码
- 案例:
修改mysql所有客户端用户名和密码, 这样之后利用mysql客户端登录或者mysqladmin管理mysql就不需要输入用户名和密码了
[19:30:05 root@mysql8021 ~]#vim /etc/my.cnf
#
# This group is read both both by the client and the server
# use it for options that affect everything
#
[client-server]
#
# include all files from the config directory
#
!includedir /etc/my.cnf.d
[mysql]
prompt="\\r:\\m:\\s (\u@\h) [\d]>\_"
[client] # client表示所有mysql的客户端, 如果想单独定义, 那么就单独写, 比如[mysqladmin],[mysql]
user=root
password=ZZyy522927.
[19:28:58 root@mysql8021 ~]#mysqladmin ping
mysqld is alive
2.1.6 mysqladmin指令
可以用来对数据库进行非交互管理, 比如重置root密码, 创建数据库, 删除数据库等
- 案例: 利用mysqladmin客户端创建数据库
[12:03:00 root@centos8-3 ~]#mysqladmin create db1
(root@localhost) [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| db1 |
| information_schema |
| mysql |
| performance_schema |
+--------------------+
2.1.7 如何非交互式管理mysql
- mysql - e
[12:07:11 root@centos8-3 ~]#mysql -e "show databases"
+--------------------+
| Database |
+--------------------+
| db1 |
| information_schema |
| mysql |
| performance_schema |
+--------------------+
- 多行重定向
+--------------------+
[12:07:16 root@centos8-3 ~]#mysql << EOF
> show databases;
> EOF
Database
db1
information_schema
mysql
performance_schema
- 标准输入重定向
[12:09:53 root@centos8-3 ~]#echo 'show databases;' | mysql
Database
db1
information_schema
mysql
performance_schema
[12:09:59 root@centos8-3 ~]#echo 'show databases;' > showdb.txt
[12:10:12 root@centos8-3 ~]#mysql < showdb.txt
Database
db1
information_schema
mysql
performance_schema
- mysqladmin命令
- 案例1: 删库
mysqladmin drop db1
- 案例2: 修改密码
password [new-password] Change old password to new-password in current format
old-password [new-password] Change old password to new-password in old format
[12:12:30 root@centos8-3 ~]#mysqladmin -uroot -p000000 password linux # mysqladmin只能修改root密码
- 案例3: mysqladmin ping
检查数据库是否正常ping, 需要指定用户名和密码
[12:19:56 root@centos8-3 ~]#mysqladmin -uroot -plinux ping
mysqld is alive
[12:20:00 root@centos8-3 ~]#systemctl stop mysqld
[12:20:54 root@centos8-3 ~]#mysqladmin -uroot -plinux ping
mysqladmin: connect to server at 'localhost' failed
error: 'Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)'
Check that mysqld is running and that the socket: '/var/lib/mysql/mysql.sock' exists!
[12:20:56 root@centos8-3 ~]#systemctl start mysqld
[12:21:00 root@centos8-3 ~]#mysqladmin -uroot -plinux ping
mysqld is alive
- 案例4: mysqladmin status
# 查询数据库运行状态
[19:44:41 root@mysql8021 ~]#mysqladmin status
Uptime: 3779 Threads: 2 Questions: 172 Slow queries: 0 Opens: 304 Flush tables: 3 Open tables: 222 Queries per second avg: 0.045
- 案例5: mysqladmin shutdown
mysqladmin shutdown: 安全的关闭数据库,不会造成数据丢失,建议关闭时使用
mysqladmin只能关数据库,开启数据库还是要使用sytemctl start
数据库初始化脚本中的修改密码, 也可以用mysqladmin来完成, mysqladmin password NEW_PASSWORD
初始化脚本修改的是 mysql root的密码. mysqladmin只能修改root的密码
2.1.8 mysql单进程, 多线程机制
pstree: 显示进程和对应线程个数,不显示具体的每个pid
pstree -p: 显示进程和对应具体的线程pid
[12:24:24 root@centos8-3 ~]#mysqladmin processlist #显示mysql线程信息
+----+-------------+-----------+----+---------+------+--------------------------+------------------+----------+
| Id | User | Host | db | Command | Time | State | Info | Progress |
+----+-------------+-----------+----+---------+------+--------------------------+------------------+----------+
| 1 | system user | | | Daemon | | InnoDB purge worker | | 0.000 |
| 2 | system user | | | Daemon | | InnoDB purge coordinator | | 0.000 |
| 3 | system user | | | Daemon | | InnoDB purge worker | | 0.000 |
| 4 | system user | | | Daemon | | InnoDB purge worker | | 0.000 |
| 5 | system user | | | Daemon | | InnoDB shutdown handler | | 0.000 |
| 10 | root | localhost | | Query | 0 | Init | show processlist | 0.000 |
#如何判断一个进程是多线程
1.
[12:24:27 root@centos8-3 ~]#ps aux | grep mysql
mysql 1934 0.1 4.8 1317588 89460 ? Ssl 12:20 0:00 /usr/libexec/mysqld --basedir=/usr #Ssl多线程
root 2004 0.0 0.0 12108 1096 pts/0 S+ 12:25 0:00 grep --color=auto mysql
2.
[12:26:36 root@centos8-3 ~]#cat /proc/1934/status | grep -i thread #-i 忽略大小写, 1934-对应的进程id.
Threads: 31
Speculation_Store_Bypass: thread vulnerable
3.
[12:28:02 root@centos8-3 ~]#pstree | grep mysql #pstree -p | grep mysql
|-mysqld---30*[{mysqld}]
2.2 服务器端配置
2.2.1 配置文件
服务器端(mysqld): 工作特性有多种配置方式
- 命令行选项
- 配置文件: 类ini格式, 集中式的配置, 能够为mysql的各应用程序提供配置信息
服务器端配置文件:
/etc/my.cnf #Global选项, 可以配置服务器和客户端的设置, 建议所有配置都放在这里,便于管理
/etc/mysql/my.cnf #Global选项
~/.my.cnf #针对用户设置
2.2.2 手动指定配置文件位置
默认mysql会到如下路径读取配置文件
Default options are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf /usr/local/mysql/etc/my.cnf ~/.my.cnf
如果想指定配置文件路径, 比如/opt/my.cnf, 那么需要使用mysqld命令加--default-files=/opt/my.cnf, 不能使用systemd或者service去启动mysql了
nohup mysqld --default-file=/opt/my.cnf &
默认情况, 8.0版本的服务器端配置是放在/etc/my.cnf.d/mysql-server文件或者/etc/my.cnf下
[19:49:20 root@mysql8021 ~]#vim /etc/my.cnf.d/mysql-server.cnf
#
# This group are read by MySQL server.
# Use it for options that only the server (but not clients) should see
#
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/en/server-configuration-defaults.html
# Settings user and group are ignored when systemd is used.
# If you need to run mysqld under a different user or group,
# customize your systemd unit file for mysqld according to the
# instructions in http://fedoraproject.org/wiki/Systemd
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock #用于本地客户端和mysqld通信, 可以和数据分开放, 只有mysql服务启动时才会生成sock文件
log-error=/var/log/mysql/mysqld.log #记录mysql服务启动后的日志, 如果服务没起来日志不会记录, 需要看/var/log/messages或者查服务状态:systemctl status mysqld
pid-file=/run/mysqld/mysqld.pid #该文件存放了mysql的主进程的pid, 随着服务重启发生变化
2.2.3 修改数据存放目录
默认的数据文件夹可以在配置文件中修改
修改前需要关闭服务-修改配置文件-创建新的数据目录-重启服务器
但是这时服务是起不来的, 因为新的文件目录的权限不对.
mysql默认的数据目录权限如下
[12:48:32 root@centos8-3 ~]#ll -d /var/lib/mysql
drwxr-xr-x 5 mysql mysql 277 Aug 17 12:21 /var/lib/mysql
#想要修改默认目录, 新建目录的权限需要为755, 所属主和所属组需要为mysql,修改完权限后就可以正常启动mysql服务了
[12:49:50 root@centos8-3 ~]#mkdir /data/mysql
[12:50:07 root@centos8-3 ~]#vim /etc/my.cnf
#datadir=/var/lib/mysql
datadir=/data/mysql
[12:51:35 root@centos8-3 ~]#systemctl restart mysqld
Job for mysqld.service failed because the control process exited with error code.
See "systemctl status mysqld.service" and "journalctl -xe" for details.
Aug 17 12:51:40 centos8-3.linux mysql-prepare-db-dir[2295]: chown: changing ownership of '/data/mysql': Operation not permitted
Aug 17 12:51:40 centos8-3.linux mysql-prepare-db-dir[2295]: Cannot change ownership of the database directories to the 'mysql'
Aug 17 12:51:40 centos8-3.linux mysql-prepare-db-dir[2295]: user. Check that you have the necessary permissions and try again.
[12:52:18 root@centos8-3 ~]#chown mysql /data/mysql/
[12:52:34 root@centos8-3 ~]#chgrp mysql /data/mysql/
[12:52:38 root@centos8-3 ~]#systemctl restart mysqld
[12:52:44 root@centos8-3 ~]#ss -ntl
State Recv-Q Send-Q Local Address:Port Peer Address:Port
LISTEN 0 128 0.0.0.0:111 0.0.0.0:*
LISTEN 0 128 0.0.0.0:22 0.0.0.0:*
LISTEN 0 128 0.0.0.0:5355 0.0.0.0:*
LISTEN 0 128 [::]:111 [::]:*
LISTEN 0 128 [::]:22 [::]:*
LISTEN 0 80 *:3306 *:*
LISTEN 0 128 [::]:5355 [::]:*
#重启服务后会在新的目录下生成默认的mysql文件
[12:52:46 root@centos8-3 ~]#ll /data/mysql
total 122936
-rw-rw---- 1 mysql mysql 16384 Aug 17 12:52 aria_log.00000001
-rw-rw---- 1 mysql mysql 52 Aug 17 12:52 aria_log_control
-rw-rw---- 1 mysql mysql 972 Aug 17 12:52 ib_buffer_pool
-rw-rw---- 1 mysql mysql 12582912 Aug 17 12:52 ibdata1
-rw-rw---- 1 mysql mysql 50331648 Aug 17 12:52 ib_logfile0
-rw-rw---- 1 mysql mysql 50331648 Aug 17 12:52 ib_logfile1
-rw-rw---- 1 mysql mysql 12582912 Aug 17 12:52 ibtmp1
-rw-rw---- 1 mysql mysql 0 Aug 17 12:52 multi-master.info
drwx------ 2 mysql mysql 4096 Aug 17 12:52 mysql
-rw-rw---- 1 mysql mysql 16 Aug 17 12:52 mysql_upgrade_info
drwx------ 2 mysql mysql 20 Aug 17 12:52 performance_schema
-rw-rw---- 1 mysql mysql 24576 Aug 17 12:52 tc.log
但是此时,虽然服务能起来,但是数据库无法用之前的密码连接,因为换了新的目录,生成的数据文件都是新的,原来的数据文件并没有迁移到新的目录,因此密码信息也没有(密码信息是存在mysql数据库的user表里ibd文件),所以此时重启后就相当于是重新安装的数据库. 需要暂停服务,将之前的目录拷贝到新的目录,全部覆盖
[12:54:38 root@centos8-3 ~]#mysql
(root@localhost) [(none)]>
[12:58:48 root@centos8-3 ~]#systemctl stop mysqld
[12:58:48 root@centos8-3 ~]#\cp -av /var/lib/mysql/* /data/mysql
[12:58:48 root@centos8-3 ~]systemctl start mysqld
[13:01:28 root@centos8-3 ~]#mysql -u -p
Enter Password:
(root@localhost) [(none)]>
2.2.4 socket地址
2.2.4.1 连接mysql两种方式
网络连接:走tcp端口网络socket, 程序间跨网络通信
本地连接: 走本地socket文件, 本地程序间通信
当需要维护系统时, 可以关闭远程连接功能,关闭3306端口号socket,使用本地socket文件进行连接数据库
通过localhost本地连接数据库时都是走的本地socket文件
(root@localhost) [(none)]> status
...
Connection: Localhost via UNIX socket
Server characterset: latin1
Db characterset: latin1
Client characterset: utf8
Conn. characterset: utf8
UNIX socket: /var/lib/mysql/mysql.sock
...
2.2.4.2 关闭3306网络连接
[mysqld]
#datadir=/var/lib/mysql
datadir=/data/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mysql/mysqld.log
pid-file=/run/mysql/mysqld.pid
skip-networking=1 #1 or true or on or 只写skip-networking, 启动跳过网络连接
#重启服务
systemctl restart mysqld
[13:12:58 root@centos8-3 ~]#systemctl restart mysqld
[13:13:12 root@centos8-3 ~]#ss -ntl
State Recv-Q Send-Q Local Address:Port Peer Address:Port
LISTEN 0 128 0.0.0.0:111 0.0.0.0:*
LISTEN 0 128 0.0.0.0:22 0.0.0.0:*
LISTEN 0 128 0.0.0.0:5355 0.0.0.0:*
LISTEN 0 128 [::]:111 [::]:*
LISTEN 0 128 [::]:22 [::]:*
LISTEN 0 128 [::]:5355 [::]:*
[13:13:14 root@centos8-3 ~]#mysql
(root@localhost) [(none)]>
#用于维护期间,本地登录
#此时远程客户端是无法登陆mysql的
[root@CentOS-8 /data]#mysql -uroot -p -h10.0.0.83
ERROR 2003 (HY000): Can't connect to MySQL server on '10.0.0.83' (111)
注意: 用户想要基于本地socket文件登录, 需要有能在'localhost'登录MySQL的账号, 否则是无法基于socket登录的
使用-S指定本地socker文件位置, 通过socket登录
mysql -uroot -p000000 -S /tmp/mysql.sock
3 二进制安装MySQL
3.1 案例: 在CentOS7上利用二进制通用包安装MySQL 5.6.48
3.1.1 准备用户和安装依赖包, 卸载Centos7自带的mariadb-libs
yum -y remove mariadb-libs
yum -y install perl-Getopt-Long libaio perl-Data-Dumper &> /dev/null
groupadd -r -g 306 mysql
useradd -r -g 306 -u 306 -d /data/mysql mysql
#Note that useradd will not create a home directory for such a user,
regardless of the default setting in /etc/login.defs (CREATE_HOME)
#用-r指定了创建系统账户，那么即使用了-d 指定了家目录路径，用户家目录也不会自动创建, 家目录还需之后手动创建
3.1.2 准备数据目录，建议使用逻辑卷
mkdir /data/mysql
chown -R mysql:mysql /data/mysql/
3.1.3 准备二进制程序
tar xf mysql-VERSION-linux-x86_64.tar.gz -C /usr/local #官方源码编译时指定的解压路径就是/usr/local,因此二进制安装时需要解压到这个目录, 也可以解压到其他目录比如/usr/local/src但是下面的软连接目录也要对应更改, 但是/usr/local/mysql是必须固定的
cd /usr/local
ln -sv mysql-VERSION mysql #创建软连接指向mysql程序，之后用软连接执行命令,便于版本切换
chown -R mysql:mysql /usr/local/mysql/
3.1.4 准备配置文件
cd /usr/local/mysql
\cp -b support-files/my-default.cnf /etc/my.cnf #配置范例, 二进制包自带的. 将模板文件替换成如下. CentOS7上默认安装了mariadb-libs包, 里面有my.cnf文件, 如果没有把包卸载了, 那么需要这里\cp直接覆盖
# mysql-5.6.48通用包的配置文件包含sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES, 需要注释掉, 否则启动mysql会报错
vim /etc/my.cnf
[mysqld]
datadir=/data/mysql
innodb_file_per_table=on #在mysql5.5以上版本是默认值， 可以不加
skip_name_resolve=on #禁止主机名解析，建议使用
user=mysql
log-error=/data/mysql/mysql.log
pid-file=/data/mysql/mysql.pid
socket=/tmp/mysql.sock # socket文件要放在tmp目录下, 否则安全加固脚本无法运行, 如果无需加固, 则放在/data/mysql下也行
[client]
socket=/tmp/mysql.sock
3.1.5 创建数据库文件
数据库文件需要手动创建,因为修改了数据目录,但是数据并不会自动生成, 需要对MySQL进行初始化
cd /usr/local/mysql
./scripts/mysql_install_db --datadir=/data/mysql --user=mysql #官方提供的脚本,运行后会生成数据文件,但是必须进入到/usr/local/mysql目录里执行,在别的路径执行会出现问题, --datadir指定新的数据路径, --user指定新建的文件属主和属组
#这一步如果本机没有安装perl-Data-Dumper包, 会提示如下报错
[20:02:04 root@mysql-4 /usr/local/mysql]#./scripts/mysql_install_db --datadir=/data/mysql --user=mysql
FATAL ERROR: please install the following Perl modules before executing ./scripts/mysql_install_db:
Data::Dumper
#如果没有安装libaio.so.1, 会提示如下报错
[20:05:50 root@mysql-4 /usr/local/mysql]#./scripts/mysql_install_db --datadir=/data/mysql --user=mysql
Installing MySQL system tables..../bin/mysqld: error while loading shared libraries: libaio.so.1: cannot open shared object file: No such file or directory
#安装包
yum -y install libaio.x86_64
3.1.6 准备服务脚本,并启动服务
如果下载的包提供启动脚本, 一般是centos 6, 那么可以直接使用启动脚本.
mysql5.6.48的二进制包提供的是启动脚本
cp ./support-files/mysql.server /etc/rc.d/init.d/mysqld #mysql-server是官方提供的启动脚本模版, 可以自己复制到 /etc/rc.d/init.d/mysqld, 添加执行权限,启动mysql
chmod a+x /etc/rc.d/init.d/mysqld
chkconfig --add mysqld
service mysqld start
如果下载的包提供service文件,一般是centos 7 或者 8 版本
cp ./support-files/systemd/mysqld.service /usr/lib/systemd/system/
systemctl daemon-reload
systemctl enable --now mysqld
3.1.7 PATH路径
[22:53:03 root@centos7-3 ~]#echo 'PATH=/usr/local/mysql/bin:$PATH' > /etc/profile.d/mysql.sh
source /etc/profile.d/mysql.sh
3.1.8 安全初始化
[22:54:16 root@centos7-3 ~]#/usr/local/mysql/bin/mysql_secure_installation
3.2 实战案例: 一键安装mysql-5.6.48通用二进制包脚本
- 官网下载: mysql-5.6.48-linux-glibc2.12-x86_64.tar.gz
- 准备my.cnf配置文件 (如果需要修改socket文件位置, 那么在配置文件中的[mysqld]和[mysql]指定相同文件路径即可), 不过5.6版本建议把sock放在/tmp下, 以免出现问题
# my.cnf
[mysqld]
datadir=/data/mysql
innodb_file_per_table=on #在mysql5.5以上版本是默认值， 可以不加
skip_name_resolve=on #禁止主机名解析，建议使用
user=mysql
log-error=/data/mysql/mysql.log
pid-file=/data/mysql/mysql.pid
socket=/tmp/mysql.sock # socket文件要放在tmp目录下, 否则安全加固脚本无法运行, 如果无需加固, 则放在/data/mysql下也行
[client]
socket=/tmp/mysql.sock
- 将三个文件拷贝到/opt目录下
[15:05:59 root@centos7-3 /opt]#ls
install_mysql_5.6.sh my.cnf mysql-5.6.48-linux-glibc2.12-x86_64.tar.gz
- 运行下面脚本
[16:24:42 root@centos7-3 /opt]#vim install_mysql_5.6.sh
#!/bin/bash
DIR=$(pwd)
NAME='mysql-5.6.48-linux-glibc2.12-x86_64'
FULL_NAME="${DIR}/${NAME}.tar.gz"
DATA_DIR='/data/mysql'
#install dependency
echo "install dependencies"
sleep 2
yum -y remove mariadb-libs &> /dev/null
yum -y install perl-Getopt-Long libaio perl-Data-Dumper &> /dev/null
if [ $? -eq 0 ]; then
echo "yum install succeed"
echo "Proceeding"
sleep 2
else
echo "yum install failed,please check yum log"
exit 4
fi
#check existence of install file and /usr/local/mysql file
if [ -f ${FULL_NAME} ]; then
echo "install_file exists!"
sleep 2
echo "Proceeding"
else
echo "install_file doesn't exist!"
echo "Please provide install_file and run again!"
exit 4
fi
if [ -e /usr/local/mysql ]; then
echo "MySQL may have been installed!"
exit 4
else
echo "MySQL has not been installed yet! Proceeding"
sleep 2
tar xf ${FULL_NAME} -C /usr/local
ln -s /usr/local/${NAME} /usr/local/mysql
id mysql &> /dev/null && echo "mysql user exists, skip user creation" || useradd -r -s /sbin/nologin mysql -d /data/mysql
chown -R mysql.mysql /usr/local/mysql/
if [ ! -d /data/mysql ]; then
mkdir -pv /data/mysql && chown -R mysql.mysql /data/mysql/
cd /usr/local/mysql
./scripts/mysql_install_db --datadir=/data/mysql --user=mysql --basedir=/usr/local/mysql
cp /usr/local/${NAME}/support-files/mysql.server /etc/init.d/mysqld
chmod a+x /etc/init.d/mysqld
\cp ${DIR}/my.cnf /etc/my.cnf
echo 'PATH=/usr/local/mysql/bin:$PATH' > /etc/profile.d/mysql.sh
source /etc/profile.d/mysql.sh
chkconfig --add mysqld
service mysqld start
else
echo "MySQL data folder exists"
exit 4
fi
fi
/usr/local/mysql/bin/mysql_secure_installation <<EOF
y
hahahaha
hahahaha
y
y
y
y
EOF
if [ $? -eq 0 ]; then
echo "mysql secure run successfully"
else
echo "mysql secure failed"
fi
3.3 二进制安装 MySQL 5.7
MySQL 5.6 和 MySQL 5.7 二进制安装略有区别
-
在生成数据库文件,初始化一步, MySQL 5.6 使用脚本, /usr/local/mysql/scripts/mysql_install_db
而MySQL 5.7 直接使用mysqld命令, mysqld --initialize --user=mysql --datadir=/data/mysql
并且, MySQL5.7版本的mysqld --initialize命令在初始化时, 会生成一串随机字符串作为root的临时密码, 用户需要用这个密码登录到数据库, 然后重置root密码. 该随机密码会在 mysqld --initialize执行命名时显示, 也可以查看自定义的mysql日志文件, 里面会有记录.
或者, 也可以用mysqld --initialize-insecure来初始化数据库, 这样不会生成随机密码 -
初始化命令不同:
MySQL 5.6利用脚本, /usr/local/mysql/scripts/mysql_install_db
MySQL 5.7利用命令, mysqld --initialize 或者 mysqld --initialize--insecure
/usr/local/mysql/bin/mysql_secure_installation: 是用来安全加固mysql的, 设置root密码, 删除匿名用户, 删除test库, 禁止root远程登录. 各个版本都有通用.
awk '/temporary password/{print $NF}' /data/mysql/mysql.log #获取初始化时生成的临时密码
- 脚本:
[17:27:21 root@centos7-3 /opt]#vim install_mysql_5.7.sh
#!/bin/bash
yum -y install libaio numactl-libs
groupadd mysql
useradd -r -g mysql -s /bin/false mysql
tar xf mysql-5.7.31-linux-glibc2.12-x86_64.tar.gz -C /usr/local
cd /usr/local
ln -s mysql-5.7.31-linux-glibc2.12-x86_64 mysql
chown -R mysql.mysql /usr/local/mysql/ #此处/usr/local/mysql/bin/mysql_secure_installation目录和内容的owner都不是mysql,需要修改
echo 'PATH=/usr/local/mysql/bin:$PATH' > /etc/profile.d/mysql.sh
. /etc/profile.d/mysql.sh
mv /etc/my.cnf /etc/my.cnf.bak
cat >> /etc/my.cnf <<EOF
[mysqld]
datadir=/data/mysql
skip_name_resolve=1
socket=/data/mysql/mysql.sock
log-error=/data/mysql/mysql.log
pid-file=/data/mysql/mysql.pid
[client]
socket=/data/mysql/mysql.sock
EOF
mkdir -pv /data/mysql && chown -R mysql.mysql /data/mysql/ # 需要修改/data/mysql权限为mysql:mysql
mysqld --initialize --user=mysql --datadir=/data/mysql #mysqld --initialize会自动创建/data/mysql目录, 但是前提是其上级目录, /data事先存在, 因此最好事先手动创建/data/mysql目录
cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld
chkconfig --add mysqld
service mysqld start
awk '/temporary password/{print $NF}' /data/mysql/mysql.log > /root/pd.txt
退出当前登录, 使环境变量生效, 之后进行安全加固
# 先拿到初始化生成的随机密码来进行加固， 修改root密码
usr/local/mysql/bin/mysql_secure_installation
- 修改root密码
#安装过程产生的临时密码只能登录, 无法做操作. 必须先手动修改密码， 或者运行加固脚本修改密码
#手动修改密码方式1:
[17:28:50 root@centos7-3 ~]#mysqladmin -uroot -p'hEyOfsB(x3iy' password linux
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
Warning: Since password will be sent to server in plain text, use ssl connection to ensure password safety.
# 手动修改密码方式2:
# 也可以用临时密码登录mysql, 再用alter命令修改root密码
[17:29:27 root@centos7-3 ~]#mysql -uroot -plinux
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 4
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>
MySQL数据访问控制实现原理
MySQL的访问控制由两个功能模块共同组成, 一个是用户管理模块, 负责检查用户是否可以登录到mysql, 另一个是访问控制模块,用来管理用户在登录到数据库后可以进行的操作
- 客户端发起登录请求, 提供用户名,密码,主机信息
- 用户管理模块查询mysql.user表,验证用户信息, 如果验证通过, 则用户成功登录进数据库, 否则连接拒绝
- 登录成功后, 用户执行命令, 访问控制模块解析命令所需权限, 查找grant表相关授权信息, 如果匹配成功则将命令发往后端执行,然后将结果返还用户. 如果匹配时报,则请求被拒绝
MySQL的启动方式
mysqld & 开启 mysql -uroot -p -e "shutdown" | mysqladmin -uroot -p shutdown 关闭
可以在启动数据库时加入参数, 临时生效
mysqld_safe &:
1. 检查系统和选项。
2. 检查MyISAM表。
3. 保持MySQL服务器窗口。
4. 启动并监视mysqld，如果因错误终止则重启。
5. 将mysqld的错误消息发送到数据目录中的host_name.err 文件。
6. 将mysqld_safe的屏幕输出发送到数据目录中的host_name.safe文件。
7. 启动时, 临时定义参数, 无需修改配置文件, mysqld_safe --skip-networking --skip-grant-tables --default-files=/opt/my.cnf &
启动脚本或者Service文件: 也是调用的mysqld_safe去启动和关闭MySQL -- systemctl stop mysqld | service mysqld stop
5.6和5.7区别
1. 初始化:
5.6使用mysql_install_db脚本
5.7使用mysqld --initialize命令
2. socket文件
5.6 建议配置在/tmp/mysql.sock, 否则无法运行安全加固, 或者如果无需运行加固脚本, 那么就可以自己指定位置
5.7 没有影响, 一般放在/data/mysql/mysql.sock即可
3. 配置文件
5.6 官方带的模本包含sql mode设置, 需要删除
5.7 不包含
4. 默认的配置
5.6: 含有匿名用户, root远程登录
mysql> select user,host,password from mysql.user;
+------+-----------+----------+
| user | host | password |
+------+-----------+----------+
| root | localhost | |
| root | 5-6 | |
| root | 127.0.0.1 | |
| root | ::1 | |
| | localhost | |
| | 5-6 | |
+------+-----------+----------+
6 rows in set (0.00 sec)
5.6 包含test数据库, 没有sys数据库
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+
4 rows in set (0.00 sec)
5.7: 没有匿名用户, root无法远程登录
mysql> select user,host,authentication_string from mysql.user;
+---------------+-----------+-------------------------------------------+
| user | host | authentication_string |
+---------------+-----------+-------------------------------------------+
| root | localhost | *C260A4F79FA905AF65142FFE0B9A14FE0E1519CC |
| mysql.session | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| mysql.sys | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
+---------------+-----------+-------------------------------------------+
3 rows in set (0.00 sec)
5.7 默认删除了test数据库, 多了sys数据库
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)
