MySQL命令行操作笔记

文章目录

一、用户管理

1.创建用户

2.删除用户

2.1、drop

2.2、delete

2.3、两者的区别

二、用户授权

1. grant 普通数据用户，查询、插入、更新、删除 数据库中所有表数据的权利

2. grant 数据库开发人员，创建表、索引、视图、存储过程、函数……等权限

3. grant 普通 DBA 管理某个 MySQL 数据库的权限

4. grant 高级 DBA 管理 MySQL 中所有数据库的权限。

5. grant 权限时指定权限作用范围，如整个MySQL服务器或单个数据库等

三、权限撤销

四、权限查看

五、用户权限注意事项

---

---

前言

本篇文章主要是对日常使用MySQL过程中的常用命令进行记录，以便以后查询需要。

---

一、安装与配置

1、查看系统中是否已经自带mysql数据库

-- 查看系统是否安装MySQL
rpm -e mysql
-- 查看安装的MySQL版本号
rpm -qi mysql-server
-- 查看本地MySQL安装包
rpm -qa|grep mysql

2、删除MySQL数据库

-- 普通删除模式
rpm -e mysql
-- 强力删除模式，如果使用上面命令删除时，提示有依赖的其它文件，则用该命令可以对其进行强力删除
rpm -e --nodeps mysql

3、使用yum命令按照MySQL

-- 查询yum上提供下载的MySQL版本信息
yum list | grep mysql
-- 安装MySQL
-- mysql 客户端
yum install mysql
-- 数据库服务器
yum install mysql-server
-- 开发用到的库以及包含文件
yum install mysql-devel

4、设置root用户密码

mysqladmin -u root password 'root'

5、修改MySQL配置文件

vim /etc/my.cnf

6、设置账号可远程登录数据库（解决1130错误）

# 登录MySQL数据库
mysql -u -root -p

-- 制定数据库
use mysql;
-- 查看账户情况
select 'host' from user where user='root';
-- 将对于账户的host字段更新为 %
update user set host = '%' where user ='root';
-- 刷新权限
flush privileges;
-- 检查是否已修改成功
select 'host'   from user where user='root';

运行完毕后再次连接测试，若还不行重启mysql服务，或是直接重启电脑

7、数据库操作

# 创建数据库，并指定字符集和排序规则
create database itms_wj default character set utf8mb4 collate utf8mb4_general_ci;

二、用户管理

1.创建用户

格式：create user 账户名[@主机地址] identified by 密码;

代码如下（示例）：

-- 创建用户需要操作 mysql 表
USE mysql;
-- 创建只允许在本地登录的用户，密码为123456
create user guest@'localhost' identified by '123456';
-- 创建可以在所有主机登录的用户，密码为123456, "@'%'" 可省略
create user guest@'%' identified by '123456';
create user guest identified by 123456;

2.删除用户

2.1、drop

代码如下（示例）：

-- 删除已存在的用户，默认删除的是 guest@'%' 这个用户
-- 若有其他用户（如 guest@'localhost'等）则不会一起被删除
drop user guest;
-- 删除已存在的指定主机地址的用户
drop user guest@'local';

2.2、delete

代码如下（示例）：

-- 使用delete删除指定主机用户
delete from user where user='guest' and host='localhost';
-- 使用delete时，删除数据后需要刷新权限
flush privileges;

2.3、两者的区别

drop不仅会将user表中的数据删除，还会删除其他权限表的内容；而delete只删除user表中的数据，所以使用delete删除用户时需要执行 flush privileges 来刷新权限。

三、用户授权

1. grant 普通数据用户，查询、插入、更新、删除 数据库中所有表数据的权利

代码如下（示例）：

-- 根据需要赋予用户对应权限
grant select on teachingDb.* to guest@'%';
grant insert on teachingDb.* to guest@'%';
grant update on teachingDb.* to guest@'%';
grant delete on teachingDb.* to guest@'%';
-- 或者
grant select, insert, update, delete on teachingDb.* to guest@'%';

2. grant 数据库开发人员，创建表、索引、视图、存储过程、函数……等权限

代码如下（示例）：

-- grant 创建、修改、删除 MySQL 数据表结构权限。
grant create on teachingDb.* to dev@'192.168.0.%';
grant alter on teachingDb.* to dev@'192.168.0.%';
grant drop on teachingDb.* to dev@'192.168.0.%';

-- grant 操作 MySQL 外键权限。
grant references on teachingDb.* to dev@'192.168.0.%';

-- grant 操作 MySQL 临时表权限。
grant create temporary tables on teachingDb.* to dev@'192.168.0.%';

-- grant 操作 MySQL 索引权限。
grant index on teachingDb.* to dev@'192.168.0.%';

-- grant 操作 MySQL 视图、查看视图源代码 权限。
grant create view on teachingDb.* to dev@'192.168.0.%';
grant show view on teachingDb.* to dev@'192.168.0.%';

-- grant 操作 MySQL 存储过程、函数新增、修改、执行等权限。
grant create routine on teachingDb.* to dev@'192.168.0.%'; -- now, can show procedure status
grant alter routine on teachingDb.* to dev@'192.168.0.%'; -- now, you can drop a procedure
grant execute on teachingDb.* to dev@'192.168.0.%';

3. grant 普通 DBA 管理某个 MySQL 数据库的权限

-- privileges 关键字可省略
grant all privileges on teachingDbto dba@'localhost'

-- 或者
grant all on teachingDbto.* to dba@'localhost';

4. grant 高级 DBA 管理 MySQL 中所有数据库的权限。

grant all on *.* to dba@'localhost'

5. grant 权限时指定权限作用范围，如整个MySQL服务器或单个数据库等

-- 1. grant 作用在整个 MySQL 服务器上：
grant select on *.* to dba@localhost; -- dba 可以查询 MySQL 中所有数据库中的表。
grant all on *.* to dba@localhost; -- dba 可以管理 MySQL 中的所有数据库

-- 2. grant 作用在单个数据库上：
grant select on teachignDb.* to dba@localhost; -- dba 可以查询 testdb 中的表。

-- 3. grant 作用在单个数据表上：
grant select, insert, update, delete on teachignDb.orders to dba@localhost;

-- 这里在给一个用户授权多张表时，可以多次执行以上语句。例如：
grant select(user_id,username) on smp.users to mo_user@'%' identified by '123345';
grant select on smp.mo_sms to mo_user@'%' identified by '123345';

-- 4. grant 作用在表中的列上：
grant select(id, se, rank) on teachignDb.apache_log to dba@localhost;

-- 5. grant 作用在存储过程、函数上：
grant execute on procedure teachignDb.pr_add to 'dba'@'localhost'
grant execute on function teachignDb.fn_add to 'dba'@'localhost'

四、权限撤销

-- revoke 跟 grant 类似，只需要把关键字 “to” 换成 “from” 即可：
-- grant all on *.* to dba@localhost;
revoke all on *.* from dba@localhost;

五、权限查看

-- 查看当前用户（自己）权限：
show grants;
-- 查看指定 MySQL 用户权限：
show grants for dba@localhost;

六、用户权限注意事项

1. grant, revoke 用户权限后，该用户只有重新连接 MySQL 数据库，权限才能生效。

2. 如果想让授权的用户，也可以将这些权限 grant 给其他用户，需要选项 “grant option“

grant select on testdb.* to dba@localhost with grant option;

---

七、问题汇集

1、分配权限报错——ERROR 1410 (42000): You are not allowed to create a user with GRANT

-- mysql8的分配权限不能带密码隐士创建账号了，要先创建账号再设置权限
-- 按上面方法创建账号，然后再执行权限分配命令
grant all privileges on test.* to 'test'@'%';
-- 若还是报错，则修改对于账户的host字段
update user set host='%' where user='test';
-- 刷新权限
flush privileges;

2、MySQL8大小写敏感

        MySQL8默认是大小写敏感的，若要改为小写不敏感，必须要在数据库初始化时先配置好，否则只能删除库文件后修改配置并再次重新初始化

# 以超级管理员角色登录
su root

# 停止 MySQL 服务：
service mysql stop

# 删除 MySQL 数据目录，MySQL库文件默认路径/var/lib/mysql：
rm -rf /var/lib/mysql

# 重新创建 MySQL 数据目录并赋予权限（是的，仅仅删除其内容是不够的）：
mkdir /var/lib/mysql
chown mysql:mysql /var/lib/mysql
chmod 700 /var/lib/mysql


# 打开 MySQL 数据库配置文件
vim /etc/my.cnf

# 在/etc/my.cnf文件的[mysqld]最后添加
lower_case_table_names = 1。

# 重新初始化 MySQL
mysqld --defaults-file=/etc/my.cnf --initialize --lower_case_table_names=1 --user=mysql --console

# 启动 MySQL 服务：
service mysql start

# 检索 MySQL 的 root 用户新生成的密码：
grep ‘temporary password’ /var/log/mysql/error.log
# 若上述路径不存在error.log，则在数据库文件路径下找，即：
grep ‘temporary password’ /var/lib/mysql/error.log

# 用找到的初始化密码登录 MySQL 并更改 MySQL root用户的密码，
mysql -u root -p
ALTER USER ‘root’@‘localhost’ IDENTIFIED WITH mysql_native_password BY ‘你的密码’;

# 退出 MySQL 之后，再次登陆 MySQL 来验证设置：
mysql -u root -p
# 执行：
SHOW VARIABLES LIKE ‘lower_case_%’;

3、MySQL 报1251错误

        mysql8 之前的版本中规则是mysql_native_password,而在mysql8之后,加密规则是caching_sha2_password解决办法：把mysql用户登录密码加密规则还原成mysql_native_password.

# 登录数据库
mysql -uroot -p

# 切换为mysql
use mysql;

# 将加密方式改为mysql_native_password
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '12345';

# 更新用户的密码
ALTER USER 'root'@'localhost' IDENTIFIED BY 'password' PASSWORD EXPIRE NEVER;

# 刷新使修改生效
FLUSH PRIVILEGES;