2.mysql的基础管理

2022-03-18 分类：mysql 阅读(324) 评论(0)

孙富阳，江湖人称没人称。多年互联网运维工作经验，曾负责过孙布斯大规模集群架构自动化运维管理工作。擅长Web集群架构与自动化运维，曾负责国内某大型博客网站运维工作。

1.用户管理

1.作用

登录数据库管理数据库对象
数据库的用户：用户名@白名单

2.安全规范

白名单尽量小，最好细化到单一ip，%不要
用户名有特点
无用的用户要删除， ‘ ’@localhost
密码超过三种复杂度，12位以上
长期不用的用户要锁定

什么是白名单，--->在白名单的ip才能连Mysql
sfy@'localhost'-->本地用户
sfy@'10.0.0.2'-->单一ip
sfy@'10.0.0.%'-->范围ip
sfy@'10.0.0.5%'-->范围ip 10.0.0.50-10.0.0.59可以连接
sfy@'10.0.0.0/255.255.255.0'-->范围ip不支持写成10.0.0.0/24
sfy@'%'-->范围ip,所有网段均可访问，极其不安全

3.增删改查

1.查询用户和密码

mysql> select user,host,authentication_string,plugin from mysql.user;
整点花里胡哨的别名as
mysql> select user as "用户",host as "主机白名单",authentication_string as "密码",plugin as "插件" from mysql.user;
查询用户表里有那些字段
mysql> desc mysql.user;

2.创建用户

mysql> create user sfy@'10.0.0.%' identified by '123';

ps:密码插件彩蛋

1.8.0之后，必须先建用户后授权，grant不在支持建用户功能和密码修改功能。
XXXXX  grant all on *.* to wo@'10.0.0.%' identified by '123';
2.密码插件，8.0之前使用mysql_native_password; 8.0之后使用caching_sha2_passwd。
导致问题：
使用老的客户端程序，连接不上8.0版本
解决方法：
建用户时，指定mysql_native_password的插件进行密码加密，
修改用户时，可以替换密码加密
配置文件中指定默认的加密插件：mysql_native_password
mysql> create user oldboyguo@'10.0.0.%' identified with mysql_native_password by '123';

3.修改用户

alter user sfy@'10.0.0.%' identified with mysql_native_password by '123';

4.锁定用户与解锁用户

锁定
mysql> alter user sfy@'%' ACCOUNT LOCK;
解锁
mysql> alter user sfy@'%' ACCOUNT UNLOCK;

5.删除用户（极其危险）

mysql> drop user sfy@'%';

2.权限管理

1.作用

约束用户的权限。对数据库能够干什么事情

2.用户的分类

业务上的用户，业务上的增删改查
主从从用户
管理用户等

3.查看权限列表

mysql> show privileges;
#all 权限---除了grant option 这个给别人授权的权限。

4.权限范围

*.*    全局范围，包含了所有的库表   相当于Linux的chmod 777 -R /
Wordpress.*      单库范围   相当于Linux的chmod 777 -R /wordpress
Wordpress.t1     库中的一个表   chmod 777 -R /wordpress/t1

5.授权一个用户远程管理数据库

mysql> create user root@'10.0.0.%' identified by '123';
mysql> grant all on *.* to root@'10.0.0.%';
mysql> create user wp_user@'10.0.0.%' identified by '123';
mysql> grant select,update,delete,insert on wordpress.* to wp_user@'10.0.0.%';

6. 查看授予用户的权限

1.专用命令查询授权情况

mysql> show grants for wp_user@'10.0.0.%';

2.授权表查询

表	说明
user表	用户的基本信息， user host auth plugin 全局授权（.）的权限
db表	user host 单库范围授权(wordpress.*)的用户权限
tables_priv表	user host 单表范围授权(wordpress.t1)的用户权限

mysql> select * from  mysql.db \G

7.权限回收

mysql> show grants for wp_user@'10.0.0.%' ;
mysql> revoke DELETE ON wordpress.* from wp_user@'10.0.0.%';

8.权限的彩蛋（8.0新特性）

将权限打包成一个角色，授权角色就OK
select update delete insert---->app_rw
1.建立一个角色
create role app_rw@'10.0.0.%';
2.角色操作的的业务(wordpress)
授权权限给角色
grant select,update,delete,insert on wordpress.* to app_rw;
3.给用户授权角色，用户就有权限了
create user test@'10.0.0.%' identified with mysql_native_password by '123';
grant app_rw@'10.0.0.%' to test@'10.0.0.%';

9.管理员密码忘了咋办

1.先关闭数据库
/etc/init.d/mysql stop
2.无验证模式登录
mysqld_safe --skip-grant-tables --skip-networking &
--skip-grant-tables   不加载授权表###只要能ping通我的，都能连接我的数据库，及其不爱不起
--skip-networking   限制登录
3.登录数据库，手动刷新权限
mysql
>flush privileges;
>alter user root@'localhost' identified by '123'; 修改密码
4.重新重启数据库
/etc/init.d/mysql restart
5.用户root加密码即可登录

3.连接管理

1.mysql命令连接

1本地socket文件连接
条件：localhost用户需要提前创建
alter user root@'localhost' identified with mysql_native_password by '123';
登录方法完整的写法，需要加入sock
[root@test ~]# mysql -uroot -p123 -S /tmp/mysql.sock
2网络连接串（tcp/ip）连接（远程连接，跨主机连接）
条件：远程连接的用户必须在白名单
mysql -uroot -p123 -h10.0.0.51 -P3306
mysqldump备份命令也是连接工具

2.开发工具连接

sqlyon
navicat
workbench###官方提供的软件

3.程序代码连接

php代码、python代码等

4.配置文件管理

1. 方式

源码包，编译时配置一些参数（CMAKE）
二进制，配置文件
命令行指定参数

2.配置文件的应用

1配置文件的默认读取路径
[root@db01 ~]# mysqld --help --verbose |grep my.cnf
/etc/my.cnf /etc/mysql/my.cnf /usr/local/mysql/etc/my.cnf ~/.my.cnf 
注意：
多个文件会依次从左到右读取，如果有重复，最后读的生效
2指定配置文件路径
启动时，强制指定读取哪个配置文件（--defaults-file=/opt/a.txt）,通过 [mysqld]  [mysqld_safe]程序能够调用
3配置文件的基本格式
[]标签项
[服务端]： [mysqld] [mysqld_safe] [server]
影响：数据库启动，初始化
[客户端]： [mysql] [mysqldump]  [client]
影响：只影响到本机的客户端程序运行。
配置项

5.数据库启动与关闭

1.启动

/usr/local/mysql/bin/mysqld &
#还可以定制参数参数例如mysqld --port=3360 这样端口就是3360了。
缺陷，没有关闭的命令，并且不会记录日志，有问题会直接输出到屏幕
/usr/local/mysql/bin/mysql_safe
#也没有关闭的命令
/usr/local/msyql/support-files/mysql.server  start stop restart

2.关闭

1.针对没有关闭命令的两种启动方式，可以用以下命令停止#mysqladmin -uroot -p123 shutdown
2.还可以进入mysql使用shutdown命令关闭数据库
3./etc/init.d/mysqld stop

ps:彩蛋mysql8.0新功能支持mysql内部restart

6.数据库的多实例

1.同版本多实例

1准备多套目录

[root@db01 ~]# mkdir -p /data/330{7..9}/data
[root@db01 ~]# chown -R mysql.mysql /data

2编辑配置文件

[root@db01 ~]# cat /data/3307/my.cnf
[mysqld]
user=mysql
basedir=/usr/local/mysql
datadir=/data/3307/data
port=3307
socket=/tmp/mysql3307.sock
[client]
socket=/tmp/mysql3307.sock
[root@db01 ~]# cat /data/3308/my.cnf
[mysqld]
user=mysql
basedir=/usr/local/mysql
datadir=/data/3308/data
port=3308
socket=/tmp/mysql3308.sock
[client]
socket=/tmp/mysql3308.sock
[root@db01 ~]# cat /data/3309/my.cnf
[mysqld]
user=mysql
basedir=/usr/local/mysql
datadir=/data/3309/data
port=3309
socket=/tmp/mysql3309.sock
[client]
socket=/tmp/mysql3309.sock

3初始化数据库

mysqld --initialize-insecure --user=mysql --basedir=/usr/local/mysql --datadir=/data/3307/data
mysqld --initialize-insecure --user=mysql --basedir=/usr/local/mysql --datadir=/data/3308/data
mysqld --initialize-insecure --user=mysql --basedir=/usr/local/mysql --datadir=/data/3309/data

4启动数据库

mysqld_safe --defaults-file=/data/3307/my.cnf &
mysqld_safe --defaults-file=/data/3308/my.cnf &
mysqld_safe --defaults-file=/data/3309/my.cnf &

5systemd管理mysql服务（样例）

vim /etc/systemd/system/mysqld.service 
[Unit]
Description=MySQL Server
Documentation=man:mysqld(8)
Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html
After=network.target
After=syslog.target
[Install]
WantedBy=multi-user.target
[Service]
User=mysql
Group=mysql
ExecStart=/app/mysql/bin/mysqld --defaults-file=/etc/my.cnf
LimitNOFILE = 5000

2.多版本多实例

1准备软件包，并准备数据目录

[root@db01 /opt]# tar xf mysql-5.6.46-linux-glibc2.12-x86_64.tar.gz 
[root@db01 /opt]# tar xf mysql-5.7.30-linux-glibc2.12-x86_64.tar.gz
[root@db01 /opt]# ln -s /opt/mysql-5.6.46-linux-glibc2.12-x86_64 /usr/local/mysql5.6
[root@db01 /opt]# ln -s /opt/mysql-5.7.30-linux-glibc2.12-x86_64 /usr/local/mysql5.7
[root@db01 /opt]# mkdir -p /data/331{6..7}/data
[root@db01 /opt]# chown -R mysql.mysql /data/

2编辑配置文件

[root@db01 /opt]# cat /data/3316/my.cnf
[mysqld]
user=mysql
basedir=/usr/local/mysql5.6
datadir=/data/3316/data
port=3316
socket=/tmp/mysql3316.sock
[client]
socket=/tmp/mysql3316.sock
[root@db01 /opt]# cat /data/3317/my.cnf
[mysqld]
user=mysql
basedir=/usr/local/mysql5.7
datadir=/data/3317/data
port=3317
socket=/tmp/mysql3317.sock
[client]
socket=/tmp/mysql3317.sock

3初始化数据

[root@db01 /opt]# /usr/local/mysql5.7/bin/mysqld --initialize-insecure --user=mysql --basedir=/usr/local/mysql5.7 --datadir=/data/3317/data
5.6版本
[root@db01 /opt]#/usr/local/mysql5.6/scripts/mysql_install_db --user=mysql --basedir=/usr/local/mysql5.6 --datadir=/data/3316/data

4启动服务

[root@db01 /opt]# /usr/local/mysql5.6/bin/mysqld_safe --defaults-file=/data/3316/my.cnf &
[root@db01 /opt]# /usr/local/mysql5.7/bin/mysqld_safe --defaults-file=/data/3317/my.cnf &

5本地连接数据库

#指定socket
mysql -S /tmp/mysql3316.sock

7.数据库版本升级

1.升级方法

inplace(就地升级)
merging（迁移升级）
inplace
适用于主从环境，先升级从库，没有问题，在升级主库
merging
逻辑备份迁移升级

2.升级注意事项（inplace）

官网：https://dev.mysql.com/doc/refman/8.0/en/upgrade-before-you-begin.html
官网原文及翻译
Important
Downgrade from MySQL 8.0 to MySQL 5.7, or from a MySQL 8.0 release to a previous MySQL 8.0 release, is not supported. The only supported alternative is to restore a backup taken before upgrading. It is therefore imperative that you back up your data before starting the upgrade process.
重要的
不支持从MySQL 8.0降级到MySQL 5.7，或从MySQL 8.0版本降级到以前的MySQL 8.0版本。唯一受支持的替代方法是恢复升级前进行的备份。因此，您必须在开始升级过程之前备份数据。
Upgrade from MySQL 5.7 to 8.0 is supported. However, upgrade is only supported between General Availability (GA) releases. For MySQL 8.0, it is required that you upgrade from a MySQL 5.7 GA release (5.7.9 or higher). Upgrades from non-GA releases of MySQL 5.7 are not supported.
支持从MySQL 5.7升级到8.0。但是，仅在通用可用性（GA）版本之间支持升级。对于MySQL 8.0，需要从MySQL 5.7 GA版本（5.7.9或更高版本）升级。不支持从MySQL 5.7的非GA版本升级。
Upgrading to the latest release is recommended before upgrading to the next version. For example, upgrade to the latest MySQL 5.7 release before upgrading to MySQL 8.0.
建议在升级到下一版本之前升级到最新版本。例如，在升级到MySQL 8.0之前，先升级到最新的MySQL 5.7版本。
Upgrade that skips versions is not supported. For example, upgrading directly from MySQL 5.6 to 8.0 is not supported.
不支持跳过版本的升级。例如，不支持直接从MySQL 5.6升级到8.0。
总结
a.支持GA（GA是稳定版）版本之间升级
b.5.6-5.7，先将5.6升级至最新版，在升级到5.7
c.5.5--5.7,现将5.5升级至最新，在5.5---5.6最新，在5.6--5.7 最新
d.回退方案要提前考虑好，最好升级前要备份（特别是往8.0版本升级）
e.降低停机时间（停业务的时间）

3.INPLACE升级过程原理

备份原数据库数据
安装新版本软件
关闭原来数据库（挂维护页）
使用新版本软件“挂”旧版本数据启动（添加启动项）
--skip-grant-tables,--skip-networking：5.6和5.7存密码的字段是不一样的。所以表要更新，不能验证，不联网。
升级：只是升级系统表，升级时间和数据量无关
正常重启数据库
验证各项功能是否正常
业务恢复

4.升级实操(5.6-5.7)

1.安装新版本软件 5.7.30

2.停原库，做冷备

1优雅的关闭数据库
[root@db01 /opt]# mysql -S /tmp/mysql3316.sock -e "set global innodb_fast_shutdown=0 ;"
[root@db01 /opt]# mysql -S /tmp/mysql3316.sock -e " select @@innodb_fast_shutdown ;"
##注意命令要写绝对路径，要不然使用的命令版本会有问题
[root@db01 /opt]# /usr/local/mysql5.6/bin/mysqladmin -S /tmp/mysql3316.sock shutdown
2数据库冷备
[root@db01 /opt]# cp -rp /data/3316/data /tmp/bak

3.使用高版本软件（5.7.30）挂低版本（5.6.48）数据启动

[root@db01 /opt]# cat /data/3316/my.cnf 
[mysqld]
user=mysql
basedir=/usr/local/mysql5.7
datadir=/data/3316/data
port=3316
socket=/tmp/mysql3316.sock
[client]
socket=/tmp/mysql3316.sock
启动
[root@db01 /opt]# /usr/local/mysql5.7/bin/mysqld_safe --defaults-file=/data/3316/my.cnf --skip-grant-tables --skip-networking &

4.升级（8.0可忽略，自动的）

/usr/local/mysql5.7/bin/mysql_upgrade -S /tmp/mysql3316.sock --force

5.重启数据到正常状态

[root@db01 /opt]# /usr/local/mysql5.7/bin/mysqladmin -S /tmp/mysql3316.sock shutdown
[root@db01 /opt]# /usr/local/mysql5.7/bin/mysqld_safe --defaults-file=/data/3316/my.cnf &

5.升级实操(5.7-8.0)

1.升级前的预检查

1安装mysql-shell软件
官方链接https://downloads.mysql.com/archives/shell/
[root@db01 /opt]# tar xf mysql-shell-8.0.20-linux-glibc2.12-x86-64bit.tar.gz 
[root@db01 /opt]# ln -s /opt/mysql-shell-8.0.20-linux-glibc2.12-x86-64bit /usr/local/mysqlsh
export PATH=/usr/local/mysqlsh/bin:$PATH
2在5.7版本的数据空中创建授权用户
mysql> create user root@'10.0.0.%' identified with mysql_native_password by '123';
mysql> grant all on *.* to root@'10.0.0.%';
3开始检测
[root@db01 ~]# mysqlsh root:123@10.0.0.51:3317 -e "util.checkForServerUpgrade()"

2.升级

同5.6版本升级5.7版本操作一致

8.Mysql体系结构

1.Mysql C/S结构

clinet: mysql mysqldump sqlyog API
Server: mysqld守护进程

2.实例（instance）

实例：mysqld + 线程（master thread io sql purge..） + 预分配内存（数据。日志。线程）
公司： boss +员工（总经理+普通员工） +办公室

3.mysql服务的构成

1.Server

a. 连接层
提供连接协议，提供验证功能（授权表），提供连接
b. SQL层
语法检查、语义（DDL、DCL、DML）、库表权限验证、解析成计算机语言（优化器--explain计划--执行（结果在磁盘的XXX位置））

2 .engine 存储引擎层（磁盘）

未经允许不得转载：孙某某的运维之路 » 2.mysql的基础管理

评论已关闭

孙某某的运维之路

孙某某的运维之路