当前位置: 首页 > news >正文

03_mysql运维核心基础

03_mysql运维核心基础

1.启动,关闭mysql

脚本启动,是后台启动
systemctl start/stop/restart mysqldservice mysqld start /etc/init.d/mysqld start 其实脚本也依然是mysql提供的二进制命令启动的[root@db-51 /www.yuchaoit.cn]#ls /opt/mysql/bin/mysqld* -l
-rwxr-xr-x 1 mysql mysql 260613605 Sep 27  2019 /opt/mysql/bin/mysqld
-rwxr-xr-x 1 mysql mysql 213374233 Sep 27  2019 /opt/mysql/bin/mysqld-debug
-rwxr-xr-x 1 mysql mysql     27139 Sep 27  2019 /opt/mysql/bin/mysqld_multi
-rwxr-xr-x 1 mysql mysql     28494 Sep 27  2019 /opt/mysql/bin/mysqld_safe
-rwxr-xr-x 1 mysql mysql  15712383 Sep 27  2019 /opt/mysql/bin/mysqldump
-rwxr-xr-x 1 mysql mysql      7865 Sep 27  2019 /opt/mysql/bin/mysqldumpslow

mysqld_safe 和mysqld的区别

mysql_safe作用

1. mysql官方启动脚本,是以执行mysqld_safe为入口,其实mysqld_safe也是个shell脚本,调用了myqsld命令启动服务2.mysqld_safe脚本设置运行环境,如以守护进程运行
3.mysqld_safe检测mysqld运行状态
4.mysqld_safe检测mysqld进程运行信息,写入 mysql实例目录下的hostname.err文件
5.以及mysqld_safe会读取my.cnf配置文件的[mysqld],[mysqld_safe]等配置

2.关闭mysql

脚本关闭

systemctl stop mysqld
service mysqld stop
/etc/init.d/mysqld stop

命令关闭

mysql -uroot -pwww.yuchaoit.cn -e 'shutdown;'[root@db-51 /www.yuchaoit.cn]#netstat -tunlp|grep mysql
tcp6       0      0 :::3307                 :::*                    LISTEN      5004/mysqld         
tcp6       0      0 :::3308                 :::*                    LISTEN      5189/mysqld      [root@db-51 /www.yuchaoit.cn]#systemctl start mysqld
[root@db-51 /www.yuchaoit.cn]#!net
netstat -tunlp|grep mysql
tcp6       0      0 :::3306                 :::*                    LISTEN      6598/mysqld         
tcp6       0      0 :::3307                 :::*                    LISTEN      5004/mysqld         
tcp6       0      0 :::3308                 :::*                    LISTEN      5189/mysqld

特殊情况下,不建议用这个操作

kill pid
pkill mysqld
killall mysqldkill -9 pid # 极端情况下,才能用这个

4.配置文件模版

[root@db-51 ~]#cat /etc/my.cnf 
[mysqld] # 服务端标签
port=3306    # 端口
server_id # 主机编号,用于主从复制
user=mysql    # 内置运行用户
basedir=/opt/mysql    # 软件目录
datadir=/www.yuchaoit.cn/mysql_3306    # 数据目录
socket=/tmp/mysql.sock    # 套接字文件路径[mysql]
socket=/tmp/mysql.sock    # mysql客户端连接数据库,默认读取的socket文件路径配置语法
[server]                服务端读取的配置
[mysqld]                mysqld进程读取的配置
[mysqld_safe]        mysqld_safe脚本会加载的配置客户端配置参数
[mysql]        客户端命令读取的设置
[client]    所有本地客户端读取的设置
[mysqldump]    备份命令读取的设置

5.远程连接管理

创建一个用户
mysql -uroot -pwww.yuchaoit.cn -e "grant all privileges on *.* to yuchao01@'localhost' identified by 'yuchao666';"

查看mysql的用户表

mysql> select User,Host,authentication_string from mysql.user;
+---------------+-----------+-------------------------------------------+
| User          | Host      | authentication_string                     |
+---------------+-----------+-------------------------------------------+
| root          | localhost | *E4270FA99E3E2D95856323D2C35CB2E4728028A1 |
| mysql.session | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| mysql.sys     | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| yuchao01      | localhost | *518455521988924B96DD6FFF6F10BC59693382B0 |
+---------------+-----------+-------------------------------------------+
4 rows in set (0.00 sec)

本地账号登录

[root@db-51 ~]#mysql -uyuchao01 -pyuchao666[root@db-51 ~]#mysql -uyuchao01 -pyuchao666 -h127.0.0.1[root@db-51 ~]#mysql -uyuchao01 -pyuchao666 -hlocalhost[root@db-51 ~]#mysql -uyuchao01 -pyuchao666 -h127.0.0.1 -P3306

使用mysql套接字登录

[root@db-51 ~]#mysql -uyuchao01 -pyuchao666 -S /tmp/mysql.sock -e "show databases;"
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+

远程连接

授权,允许访问的网段

1. 之允许yuchao02用户在10.0.0.x网段登录,有最大的权限
2. 授权语句只有root用户才能操作mysql -uroot -pwww.yuchaoit.cn -S /tmp/mysql.sock -e "grant all on *.* to yuchao02@'10.0.0.%' identified by 'yuchao666';"

远程登录看看

[root@web-7 ~]#mysql -uyuchao02 -pyuchao666 -h10.0.0.51 -P3306
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MySQL connection id is 15
Server version: 5.7.28 MySQL Community Server (GPL)Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.MySQL [(none)]> select user();
+-------------------+
| user()            |
+-------------------+
| yuchao02@10.0.0.7 |
+-------------------+
1 row in set (0.00 sec)MySQL [(none)]>

image-20251013141204518

6.用户说明

Linux用户
- 管理文件
- 登录文件mysql用户
- 登录mysql
- 管理mysql的库,表

6.3 用户管理

查看mysql用户列表

[root@db-51 ~]#mysql -uroot -pwww.yuchaoit.cn -e 'select User,Host,authentication_string from mysql.user;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+---------------+-----------+-------------------------------------------+
| User          | Host      | authentication_string                     |
+---------------+-----------+-------------------------------------------+
| root          | localhost | *E4270FA99E3E2D95856323D2C35CB2E4728028A1 |
| mysql.session | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| mysql.sys     | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| yuchao01      | localhost | *518455521988924B96DD6FFF6F10BC59693382B0 |
| yuchao02      | 10.0.0.%  | *518455521988924B96DD6FFF6F10BC59693382B0 |
| bob01         | 10.0.0.7  | *518455521988924B96DD6FFF6F10BC59693382B0 |
+---------------+-----------+-------------------------------------------+
[root@db-51 ~]#

创建用户

create user chaoge01@'localhost'; # 创建用户无密码select user,host,authentication_string from mysql.user; # 查询创建且设置密码create user chaoge02@'localhost' identified by '123';

修改用户密码,root去修改

alter user chaoge01@'localhost' identified by '123';
alter user chaoge01@'localhost' identified by 'yuchaoge666';

改自己密码

[root@db-51 ~]#
[root@db-51 ~]#mysql -uchaoge02 -p123
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 27
Server version: 5.7.28 MySQL Community Server (GPL)Copyright (c) 2000, 2019, 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> set password=password('chaoge666');
Query OK, 0 rows affected, 1 warning (0.00 sec)mysql>

删除用户

mysql> drop user chaoge02@'localhost';
Query OK, 0 rows affected (0.00 sec)

7.授权管理

权限的作用

限制mysql的用户,可以执行哪些SQL语句。

image-20251013141337389

8.修改root密码

Mysqladmin改密码

[root@db-51 ~]#mysqladmin -uroot -pwww.yuchaoit.cn password yuchao666

set语句修改

mysql> set password for root@localhost=password('chaoge666');
Query OK, 0 rows affected, 1 warning (0.00 sec)mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)

update语句修改

mysql> update mysql.user set authentication_string=password("www.yuchaoit.cn") where user='root' and host='localhost';
Query OK, 1 row affected, 1 warning (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 1mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)刷新后,权限表会更新

用户修改自己密码

mysql> set password=password('yuchao666');
Query OK, 0 rows affected, 1 warning (0.00 sec)mysql>

9.忘记root密码咋办

1.停止服务
[root@db-51 ~]#systemctl stop mysql2.跳过授权表,免密运行mysqld服务端
[root@db-51 ~]#mysqld_safe --skip-grant-tables --user=mysql
2022-07-20T09:45:54.661054Z mysqld_safe Logging to '/www.yuchaoit.cn/mysql_3306/db-51.err'.
2022-07-20T09:45:54.684950Z mysqld_safe Starting mysqld daemon with databases from /www.yuchaoit.cn/mysql_33063.改密码
只能用update语句
mysql> update mysql.user set authentication_string=password('www.yuchaoit.cn') where user='root' and host='localhost';
Query OK, 1 row affected, 1 warning (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 14.停止mysql
mysql> shutdown;
Query OK, 0 rows affected (0.00 sec)5.重启mysql
systemctl start mysql再次登录
[root@db-51 ~]#mysql -uroot -pwww.yuchaoit.cn6.提醒
授权表参数,会导致任意客户端,都可以免密直接登录,务必要记住要删掉
http://www.hskmm.com/?act=detail&tid=30095

相关文章:

  • 2025.10.13+7
  • Adobe Animate 2025 中文破解版下载|HTML5 动画开发 + 2D 交互设计工具(附安装教程)
  • 2025年10月双氧水厂家最新权威推荐榜:高效消毒与环保品质之选
  • GSPO
  • 2025 年制袋机源头厂家最新推荐排行榜权威发布:30 项专利加持企业领衔,细分领域新锐品牌深度盘点
  • 日记6
  • 2025年10月舒适轮胎厂家最新推荐排行榜,静音轮胎,耐磨轮胎,节能轮胎,高性能轮胎公司推荐!
  • 日记5
  • 日记7
  • 软件为什么卸载不了?流氓软件克星?专业流氓卸载工具?强力卸载软件Geek免安装版
  • 流量突然提升100倍QPS,怎么办?
  • 2025年10月冷却塔厂家最新推荐排行榜,闭式冷却塔,开式冷却塔,工业冷却塔,高效节能冷却塔公司推荐!
  • 2025年10月通风气楼厂家最新推荐排行榜,屋顶通风气楼,工业厂房通风气楼,节能环保通风气楼公司推荐!
  • 10/13
  • CF1882E1 Two Permutations (Easy Version)
  • 2025年10月实验室净化订做厂家最新推荐排行榜,专业定制与高效服务口碑之选
  • 20234320 2025-2026-1 《网络与系统攻防技术》实验一实验报告
  • 2025年10月清洗机厂家最新推荐排行榜,高压清洗机,超声波清洗机,工业清洗机,商用清洗机公司推荐!
  • 2025年10月上海殡葬服务一条龙最新权威推荐榜:专业贴心的全程陪伴与优质服务厂家选择指南
  • [GenAI] 大模型微调
  • 2025年10月气柱袋厂家最新推荐排行榜,缓冲包装气柱袋,防震气柱袋,充气气柱袋公司推荐!
  • [GenAI] LoRA微调
  • 2025年10月保洁公司最新权威推荐榜:专业清洁与高效服务的品质之选
  • 2025年10月粉末涂料厂家最新推荐排行榜,环氧粉末涂料,聚酯粉末涂料,丙烯酸粉末涂料,耐候性粉末涂料公司推荐
  • 基于单片机的汽车防碰撞刹车系统(论文+源码) - 实践
  • git submodule
  • 2025年10月确有专长培训机构最新推荐榜单:专业课程与高通过率口碑之选
  • 有源探头DC与RMS参数详解:选型与应用指南
  • Objective-C Runtime 中的关联对象(Associated Object)方法
  • 2025年10月无锡公考培训机构最新权威推荐榜单:专业师资与高通过率口碑之选