MySQL8.0日常管理

## 3.1 启动与关闭方式 ### 1.常规启动和关闭方式 ``` systemd: systemctl {stop|start|restart} mysqld sys-v: service mysqld {stop|start|restart}==/etc/init.d/mysqld start # 注意：{stop|start|restart}括号内3选1，分别为停止、启动和重启。 # 采用二进制安装，上述命令的启动脚本来自/usr/local/mysql/support-files/mysql.server # 注意：上述命令本质: # 停止原理:kill mysql_pid # 启动原理:mysqld_safe .... ``` ### 2.维护启动和关闭方式 a.维护性启动方式（往往需要额外加参数启动） ``` # --mysqlx=0为关闭33060端口，这是8.0增加的功能。 mysqld --port=3306 --mysqlx=0 & # root管理员密码丢失使用这个命令启动。 mysqld_safe --skip-grant-tables --skip-networking & # 使用mysqld和mysqld_safe启动优势：可以额外加参数启动，命令行高于配置文件my.cnf里的配置。 ``` b.维护性关闭方式 ``` kill pid #<==这里的pid为数据库服务对应的进程号。 killall mysqld #<==这里的mysqld是数据库服务对应的进程名字。 pkill mysqld #<==这里的mysqld是数据库服务对应的进程名字。 # 注意:千万不要用-9,强制杀死。 ``` ### 3. 其它关闭方式 MySQL提供了一个mysqladmin管理命令，可以用来优雅的关闭数据库，命令如下： ``` [root@oldboy ~]# mysqladmin shutdown #<==注意密码要正确。 #还可以登录到数据库里面执行关闭。 mysql> shutdown; mysql> restart; ``` --- ## 3.2 MySQL启动与关闭原理 ### 3.2.1 关闭原理 systemctl命令关闭原理分析 ``` [root@oldboy ~]# sed -n '281,301p' /etc/init.d/mysqld 'stop') # Stop daemon. We use a signal here to avoid having to know the # root password. if test -s "$mysqld_pid_file_path" then # signal mysqld_safe that it needs to stop touch "$mysqld_pid_file_path.shutdown" mysqld_pid=`cat "$mysqld_pid_file_path"` if (kill -0 $mysqld_pid 2>/dev/null) ###判断pid对应的进程是否运行. then echo $echo_n "Shutting down MySQL" kill $mysqld_pid #=======================kill pid # mysqld should remove the pid file when it exits, so wait for it. wait_for_pid removed "$mysqld_pid" "$mysqld_pid_file_path"; return_value=$? else log_failure_msg "MySQL server process #$mysqld_pid is not running!" rm "$mysqld_pid_file_path" fi ``` ### 3.2.2 启动原理采用systemctl或者service方式关闭数据库的本质都是调用mysqld_safe命令启动。对应脚本如下： ``` [root@oldboy ~]# sed -n '255,279p' /etc/init.d/mysqld 'start') #<==启动命令的传参。 cd $basedir echo $echo_n "Starting MySQL" if test -x $bindir/mysqld_safe then # Give extra arguments to mysqld with the my.cnf file. This script # may be overwritten at next upgrade. $bindir/mysqld_safe --datadir="$datadir" --pidfile="$mysqld_pid_file_path" $other_args >/dev/null & #<==这正是在初始化数据库时系统给出的快速启动MySQL方式。 ...省略若干行... ;; # 结论： # systemctcl关闭数据库原理 kill pid # systemctcl启动数据库原理 mysqld_safe [root@oldboy ~]# ps -ef|grep mysql|grep -v grep root 21671 1 0 23:06 ? 00:00:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/data/3306/data --pidfile=/data/3306/data/oldboy.pid mysql 21827 21671 27 23:06 ? 00:00:01 /usr/local/mysql/bin/mysqld -- basedir=/usr/local/mysql --datadir=/data/3306/data --plugindir=/usr/local/mysql/lib/plugin --user=mysql --log-error=oldboy.err --pidfile=/data/3306/data/oldboy.pid --socket=/tmp/mysql.sock --port=3306 ``` #/etc/init.d/mysqld是MySQL自带的使用Shell编写的启动脚本，执行脚本后最终会调用mysqld_safe命令脚本，mysqld_safe脚本执行后又会调用mysqld主程序启动MySQL服务，因此在前文查看MySQL进程时，会发现不仅有mysqld_safe进程，还有mysqld进程。 ``` #生产注意:一定不要随意重启数据库,会影响业务. # 1.20秒业务不可用。 # 2.业务访问可能变慢(缓存失效)。 ``` ### 3.2.3 退出MySQL 数据库维护完毕后，如果需要退出，则可以执行下面的命令： ``` 1、quit (\q) Quit mysql。 2、exit (\q) Exit mysql. Same as quit。 3、快捷键Ctrl+d。 ``` ### 3.2.4 登录MySQL ``` # mysql命令选项: -u用户 -p密码 -Ssocket #多实例 -P端口 -h主机 #远程登录 -e 非交互操作mysql # mysql登录示例： [root@oldboy ~]# mysql -uroot -p -S /tmp/mysql.sock -P3306 -hlocalhost -e "show databases;" Enter password: +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | +--------------------+ ``` 3.5 MySQL命令行帮助 ``` #?和help ====>Linux下man # 核心: help contents # 创建数据库: # 帮助方法1:命令行查帮助 # 步骤1.help contents mysql> help contents You asked for help about help category: "Contents" For more information, type 'help <item>', where <item> is one of the following categories: Data Definition # 步骤2.? Data Definition mysql> ? Data Definition ALTER DATABASE ALTER EVENT ALTER FUNCTION ALTER TABLESPACE ALTER VIEW CREATE DATABASE # 步骤3.mysql> ? CREATE DATABASE # 创建数据库语法就看到了 mysql> ? CREATE DATABASE CREATE DATABASE Syntax: CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name 说明: 1){DATABASE | SCHEMA} 必须2选1. 2)[IF NOT EXISTS]中括号可选. create_option: [DEFAULT] { CHARACTER SET [=] charset_name | COLLATE [=] collation_name | ENCRYPTION [=] {'Y' | 'N'} } # 步骤4:创建 CREATE DATABASE db_name # 帮助方法2:官方文档 URL: https://dev.mysql.com/doc/refman/8.0/en/create-database.html # 帮助方法3.baidu https://dev.mysql.com/doc/refman/8.0/en/drop-table.html # 帮助方法4:问会的人 ``` ---- ## 3.3 管理MySQL用户 ### 3.3.2 MySQL用户 1.用户组成用户@主机域，两部分共同构成了mysql用户，主机域是登录的客户端主机的IP范围。 2.用户特点 ``` oldboy@'localhost' #<==本机用户，只能在本机登录。适合单机. oldboy@'10.0.0.2' #<==单一IP，只能从10.0.0.2这台机器登录 oldboy@'10.0.0.%' #<==模糊匹配，一个范围内IP，例如10.0.0.1-10.0.0.254。集群模式. oldboy@'10.0.0.1%' #<==模糊匹配，一个范围内的IP，例如10.0.0.10-10.0.0.19/10.0.0.100- 10.0.0.199 oldboy@'10.0.0.0/255.255.0.0' #<==一个子网内的所有IP。 oldboy@'%' #<==模糊匹配，任意用户都可以登录IP，此方式极不可取。 #在前一章节，安装数据库后，输入mysql直接登录到数据库中，实际的用户为：root@localhost; ``` **MySQL用户安全规范**（生产技巧） ``` a. 主机域范围要尽量的小，最好细化到单一IP，甚至是用locahost，%模糊匹配应坚决禁止。 b. 用户名应有实际意义，一看就明白什么项目，比如:blog。 c. 无用的用户应删除或者锁定。 d. 密码应该至少为数字、大小写字母、特殊字符三种以上复杂度，非字典密码，且总数8位以上。 e. 应为一个项目设置一个对应用户，禁止使用root用户作为业务项目的用户。 ``` 3.查询用户管理库mysql，用户表user。 ``` # 1）查看当前登录用户 mysql> select user(); +----------------+ | user() | +----------------+ | root@localhost | +----------------+ # 2）查看用户表的表结构 mysql> desc mysql.user; ========================== mysql> use mysql mysql> desc user; # 3）查询用户 mysql> select user,host from mysql.user; +------------------+-----------+ | user | host | +------------------+-----------+ | mysql.infoschema | localhost | | mysql.session | localhost | | mysql.sys | localhost | | root | localhost | ====>当前登录 +------------------+-----------+ # 查询更多列 mysql> select user,host,authentication_string,plugin from mysql.user; ``` 4.增加用户分别创建oldboy、oldgirl用户，允许从10.0.0.0/24整个网段的主机访问数据库， ``` create user oldboy@'10.0.0.%' identified by '123'; create user oldgirl@'10.0.0.%' identified with mysql_native_password by '123'; #推荐使用。 ``` **重点知识:*** ``` 1.8.0之后必须先建用户后授权，grant命令不再支持建用户同时创建密码功能。 2.8.0之前密码插件使用的mysql_native_password，8.0之后使用是caching_sha2_password。 # 这可能导致使用老的客户端程序（例如sqlyog客户端）无法连接新的8.0版本数据库。解决方法: a. 增加用户时，指定mysql_native_password插件进行密码加密。 b. 修改用户时，可以修改指定的插件。 c. 配置文件my.cnf中可以指定默认加密插件为mysql_native_password。 ``` 5.修改用户 1）将oldboy用户的密码插件更改为mysql_native_password。 `mysql>alter user oldboy@'10.0.0.%' identified with mysql_native_password by '123';` 6.锁定用户 2）锁定和解锁用户（不确定是否有用的账户，可以先锁定） ``` mysql> alter user oldgirl@'10.0.0.%' ACCOUNT LOCK; mysql> alter user oldgirl@'10.0.0.%' ACCOUNT UNLOCK; ``` 7.删除用户 `mysql> drop user oldgirl@'10.0.0.0/255.255.255.0';` ### 3.3.3 MySQL客户端 1.客户端介绍 1) 开发人员常用：navicat/sqlyog/phpmyadmin 2) 运维人员常用：mysql 2.创建远程连接管理员 ``` mysql> create user oldboy@'10.0.0.%' identified with mysql_native_password by 'oldboy123'; mysql> grant all on *.* to oldboy@'10.0.0.%' with grant option; mysql> flush privileges; ``` --- ## 3.4 管理MySQL用户密码 ### 3.4.1 设置管理员root密码 ``` mysqladmin password 'oldboy123' # 登录测试 mysql -uroot -poldboy123 ``` ### 3.4.2 修改管理员root密码 1.Linux命令行修改法 ``` #<== oldboy123为原密码，新密码为oldboy。 mysqladmin -uroot -poldboy123 password 'oldboy' mysql -uroot -poldboy ``` 2.SQL语句修改法 ``` #常用于root密码遗忘后使用 mysql> alter user root@'localhost' identified by 'oldboy123'; mysql> flush privileges; #<==刷新权限使得修改密码生效。 #交互登录 mysql -uroot -p ``` ### 3.4.3 找回MySQL root用户密码 ``` 1.首先停止MySQL服务。 systemctl stop mysqld netstat -lntup|grep 330 2.启动mysql服务。 mysqld_safe --skip-grant-tables --skip-networking & ###--skip-grant-tables（忽略授权登录验证）和--skip-networking（忽略网络） 3.无需密码登录MySQL。 mysql 4.修改root密码为新密码。 mysql> flush privileges; #<==此命令必须先执行。 mysql> alter user root@'localhost' identified by 'oldboy123'; mysql> quit 5.重启mysql killall mysqld systemctl start mysqld netstat -lntup|grep 330 6.登录验证 mysql -uroot -p ``` **遇到的故障:** ``` mysql> select user,host from mysql.user; +------------------+-----------+ | user | host | +------------------+-----------+ | oldboy | 10.0.0.% | | oldgirl | 10.0.0.% | | mysql.infoschema | localhost | | mysql.session | localhost | | mysql.sys | localhost | | root | localhost | +------------------+-----------+ 6 rows in set (0.01 sec) mysql> quit Bye [root@db01 ~]# mysql -uoldboy -p123; ERROR 1045 (28000): Access denied for user 'oldboy'@'localhost' (using password: YES) # 以上是使用oldboy@localhost登录 mysql -uoldboy -p123 -hlocalhost #而实际用户是:oldboy@10.0.0.%，所以无法登录 #正确登录 mysql -uoldboy -p123 -h10.0.0.51 ``` ## 3.5 管理MySQL用户权限 ### 3.5.1 什么是MySQL权限 MySQL权限是针对用户设置的，用来控制及约束用户对数据库里不同对象(库、表)都能干啥，比如增加的权限，删除的权限，修改的权限、查看的权限等。 ### 3.5.2 MySQL权限 1.查看权限 `mysql> show privileges;` 2.ALL PRIVILEGES权限说明 ALL PRIVILEGES（也可省略为ALL），包含除了grant option以外的所有权限。另外，工作中授权时，授权用户应尽量授权最小的满足业务需求的权限，而不是直接授权“ALL”。 grant option ===>可以创建真正管理员用户的权限） Grant option To give to other users those privileges you possess 创建一个和root管理员一样的权限: mysql创建和root等同的权限: ``` create user oldboy@'localhost' identified by '123'; grant all on . to oldboy@'localhost' with grant option; flush privilges; ``` 3.几个案例案例1：创建test用户，具备所有库表权限，从localhost主机登录管理数据库，密码是test123。 ``` # 方法1：MySQL5.7以前，grant命令可以授权的同时，创建用户并设置好密码，命令如下： mysql> grant all on *.* to 'test'@'localhost' identified by 'test123'; mysql> flush privileges; # 方法2：但是5.7及以后必须单独创建用户，再进行授权，不能用grant命令一步到位了，实际命令为： mysql> create user 'test'@'localhost' identified with mysql_native_password by 'test123'; mysql> grant all privileges on *.* to 'test'@'localhost'; mysql> flush privileges; ``` 案例2：创建blog用户，具备oldboy库里表增和查的权限，登录网段为10.0.0.0/24，密码是123。 ``` create user blog@'10.0.0.0/24' identified by '123'; grant insert,select on oldboy.* to blog@'10.0.0.0/24'; ``` 案例3：创建用户，具备mysql库里user表的增和查权限，登录网段为10.0.0.0/24，密码是123。 ``` create user zhangsan@'10.0.0.0/24' identified by '123'; grant insert,select on mysql.user to zhangsan@'10.0.0.0/24'; ``` ### 3.5.3 查看用户权限 1.专门查看用户授权命令 `mysql> show grants for 'oldboy'@'localhost';` 2.授权表说明（了解） 1)说明(专业DBA需要会) ``` *.* #<==授权[所有库.所有表],查mysql.user oldboy.* #<==单库范围，oldboy库里的所有表，查mysql.db oldboy.t1 #<==单表范围，oldboy库里的t1表。查mysql.tables_priv ``` 2)实践: ``` #所有库所有表查mysql.user mysql> select * from mysql.user where user='oldboy' and host='10.0.0.%'\G #库级别:查mysql.db mysql> select * from mysql.db where user='blog'\G #表级别查mysql.tables_priv mysql> select * from mysql.tables_priv where user='zhangsan ``` ### 3.5.4 企业中grant授权权限说明 1)连接数据库的对象 Web服务(php,tomcat,python)/app服务 2)如何授权? select,insert,update 针对表内容授权. 合理授权: `grant select,insert,update,delete on oldboy.* to test@'172.16.1.%';` 除了select,insert,update,delete 权限外的权限,用户用不到的:DBA的权限,create,drop 都应该控制在DBA人员手里.给别人最小的权限,拥有最大的安全. 3)安装wordpess,要网上用,用户如何授权合理? 1.安装时候授权: `select,insert,update,delete,create,drop` 2.安装回收create,drop,剩余 `select,insert,update,delete` 注意：授予用户权限时有如下3条安全红线不要轻易跨过。 ``` a.权限一定不用all, 而是select、insert. update、 delete权限。 b.库表不用，而应用oldboy.*格式具体到库更为妥当。 c.主机域不用%，而应用内网的IP段，即'172.16.1.%'格式。 ``` 结论:只要是应用服务器用户授权,99.99%,select,insert,update,delete 对于表\库对象的修改一定是管理员操作,不能交给开发管 ### 3.5.4 撤销授权权限 1.语法: ``` REVOKE INSERT ON . FROM 'jeffrey'@'localhost'; REVOKE 'role1', 'role2' FROM 'user1'@'localhost', 'user2'@'localhost'; REVOKE SELECT ON world.* FROM 'role3'; ``` 2.举例: 安装wordpress如何授权合理? 1.安装时候授权: ``` select,insert,update,delete,create,drop create user blog@'localhost' identified by '123'; grant select,insert,update,delete,create,drop on wordpress.* to blog@'localhost'; show grants for blog@'localhost'; ``` 2.安装回收create,drop,剩余 ``` select,insert,update,delete REVOKE create,drop ON wordpress.* FROM blog@'localhost'; ``` 注意： ###回收和授权命令的库表,用户,要严格对应否则收不回来。 ###关键字不区分大小写,但是库\表名小写,而且字母开头.只能含有字母数字下划线. --- ## 3.6 MySQL连接管理 ### 3.6.1 客户端连接MySQL服务原理 MySQL是一个典型的C/S服务结构软件 C/S:client/server/胖客户端: C:client:mysql、mysqladmin、mysqldump、mysqlbinlog等 S:mysqld B/S #瘦客户端，趋势 B/S Browser/Server 浏览器/服务器 ### 3.6.2 MySQL连接方式 1.TCP/IP和Socket连接介绍及实践 **Socket连接:本机** `mysql -uroot -poldboy123 -S /tmp/mysql.sock` **TCP/IP:** 客户端软件:例如sqlyog(开发人员) mysql -uroot -poldboy123 -h10.0.0.51 ### 3.6.3 MySQL连接提示符说明 ``` mysql> prompt \u@oldboy \r:\m:\s-> root@oldboy 04:53:18-> ``` 配置文件:/etc/my.cnf 无需重启 ``` [client] prompt=\\u@oldboy \\r:\\m:\\s-> ``` --- ## 3.7 MySQL配置文件及参数 ### 3.7.1 配置文件默认首个配置位置:/etc/my.cnf ``` [root@oldboy ~]# mysqld --help --verbose|grep my.cnf /etc/my.cnf /etc/mysql/my.cnf /usr/local/mysql/etc/my.cnf ~/.my.cnf ``` ### 3.7.2 参数配置方式 1.源码包编译安装时 2.系统命令行指定部分MySQL参数 1)例:服务端：忽略授权表启动mysql的命令： `mysqld_safe --skip-grant-tables --skip-networking & ` 2)例:客户端：登录mysql `mysql -uroot -poldboy123 -h 10.0.0.128 -P3306` 3.MySQL命令行临时设置MySQL参数查看所有参数`show variables;` 修改参数:两个级别,全局:global,局部:session ### 3.7.3 参数查看 1.1 global级:全局 ``` mysql> select @@global.wait_timeout; or mysql> show global variables like 'wait_timeout'; or mysql> select * from performance_schema.global_variables where variable_name='wait_timeout'; ``` 1.2 session级:局部 ``` mysql> select @@wait_timeout; or mysql> select @@session.wait_timeout; or mysql> show variables like 'wait_timeout'; or mysql> show session variables like 'wait_timeout'; or mysql> select * from performance_schema.session_variables where variable_name ='wait_timeout'; ``` ### 3.7.4 参数修改 2.1 session级参数的修改 ``` mysql> set wait_timeout=10; or mysql> set session wait_timeout=10; ``` 2.2 global级参数的修改 `mysql> set global wait_timeout=10;` # 不能重启MySQL，还要需要参数，用这个命令。 2.3 配置文件修改:重启生效 ``` vi /etc/my.cnf [mysqld] wait_timeout = 10 ``` 工作中不允许重启MySQL，改参数用下面方法 1.先set global全局修改。 2.同时改配置文件/etc/my.cnf。 ### 3.7.5 my.cnf配置 1.默认首个配置位置:/etc/my.cnf ``` [root@oldboy ~]# mysqld --help --verbose|grep my.cnf [/etc/my.cnf] /etc/mysql/my.cnf /usr/local/mysql/etc/my.cnf ~/.my.cnf ``` 2.mysql配置文件基本结构执行cat /etc/my.cnf查看前文安装时的简易my.cnf配置文件。 ``` [root@oldboy ~]# cat /etc/my.cnf #by oldboy weixin:oldboy0102 [mysqld] #<==服务端标签,需要重启,影响服务运行。 user=mysql basedir=/usr/local/mysql datadir=/data/3306/data port=3306 socket=/tmp/mysql.sock [client] #<==mysql客户端标签 socket=/tmp/mysql.sock [mysqld_safe] #<==启动标签 [mysqldump] #<==备份标签 ``` 客户端标签只影响本机指定客户端程序的运行，配置后直接生效。