时间:2021-07-01 10:21:17 帮助过:50人阅读
1、用户登录
- TCP/<span style="color: #000000;">IP方式(远程、本地):
- mysql </span>-uroot -poldboy123 -h <span style="color: #800080;">10.0</span>.<span style="color: #800080;">0.51</span> -<span style="color: #000000;">P3306
- Socket方式(仅本地):
- mysql </span>-uroot -poldboy123 -S /tmp/mysql.sock<br><br><br>
- <code class=" language-kotlin">用户名@<span class="token string">‘白名单‘
- <span class="token label symbol">wordpress@<span class="token string">‘10.0.0.%‘
- <span class="token label symbol">wordpress@<span class="token string">‘%‘
- <span class="token label symbol">wordpress@<span class="token string">‘10.0.0.200‘
- <span class="token label symbol">wordpress@<span class="token string">‘localhost‘
- <span class="token label symbol">wordpress@<span class="token string">‘db02‘
- <span class="token label symbol">wordpress@<span class="token string">‘10.0.0.5%‘
- <span class="token label symbol">wordpress@<span class="token string">‘10.0.0.0/255.255.254.0‘</span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></code>
2、用于管理数据库及数据
- <span style="color: #000000;">增:
- mysql</span>> create user oldboy@<span style="color: #800000;">‘</span><span style="color: #800000;">10.0.0.%</span><span style="color: #800000;">‘</span> identified by <span style="color: #800000;">‘</span><span style="color: #800000;">123</span><span style="color: #800000;">‘</span><span style="color: #000000;">;
- 查:
- mysql</span>> desc mysql.user; ----><span style="color: #000000;"> authentication_string
- mysql</span>> <span style="color: #0000ff;">select</span> user ,host ,authentication_string <span style="color: #0000ff;">from</span><span style="color: #000000;"> mysql.user 查用户密码,一般都是加密
- 改:
- mysql</span>> alter user oldboy@<span style="color: #800000;">‘</span><span style="color: #800000;">10.0.0.%</span><span style="color: #800000;">‘</span> identified by <span style="color: #800000;">‘</span><span style="color: #800000;">456</span><span style="color: #800000;">‘</span><span style="color: #000000;">;
- 删:
- mysql</span>> drop user oldboy@<span style="color: #800000;">‘</span><span style="color: #800000;">10.0.0.%</span><span style="color: #800000;">‘</span>;
命令:
- grant 权限 on 权限范围 to 用户 identified by ‘密码‘
权限
- 对数据库的读、写等操作
- (insert update、select、delete、drop、create等)
- <code class=" language-dart">ALL<span class="token punctuation">:
- SELECT<span class="token punctuation">,INSERT<span class="token punctuation">, UPDATE<span class="token punctuation">, DELETE<span class="token punctuation">, CREATE<span class="token punctuation">, DROP<span class="token punctuation">, RELOAD<span class="token punctuation">, SHUTDOWN<span class="token punctuation">, PROCESS<span class="token punctuation">, FILE<span class="token punctuation">, REFERENCES<span class="token punctuation">, INDEX<span class="token punctuation">, ALTER<span class="token punctuation">, SHOW DATABASES<span class="token punctuation">, SUPER<span class="token punctuation">, CREATE TEMPORARY TABLES<span class="token punctuation">, LOCK TABLES<span class="token punctuation">, EXECUTE<span class="token punctuation">, REPLICATION SLAVE<span class="token punctuation">, REPLICATION CLIENT<span class="token punctuation">, CREATE VIEW<span class="token punctuation">, SHOW VIEW<span class="token punctuation">, CREATE ROUTINE<span class="token punctuation">, ALTER ROUTINE<span class="token punctuation">, CREATE USER<span class="token punctuation">, EVENT<span class="token punctuation">, TRIGGER<span class="token punctuation">, CREATE TABLESPACE
- ALL <span class="token punctuation">: 以上所有权限,一般是普通管理员拥有的
- <span class="token keyword">with grant option:超级管理员才具备的,给别的用户授权的功能</span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></code>
权限范围
- *.* 所有库所有表 ----><span style="color: #000000;">管理员用户
- wordpress.</span>* 指定当前库所有表授权 ----><span style="color: #000000;">开发和应用用户
- wordpress.t1 当前库指定表 ti 授权</span>
用户只能通过10.0.0.0/24网段访问,用户名为clsn 密码为123
这个用户只能对clsn数据库下的对象进行增insert create、改update 、查select;
创建命令:
- grant select,create,insert,update on clsn.* to ‘clsn‘@‘10.0.0.%‘ identified by ‘123‘;
查看用户权限
- <code class=" language-css">mysql> show grants for app@<span class="token string">‘10.0.0.%‘<span class="token punctuation">;</span></span></code>
- [root@db01 ~]# mysqld_safe --skip-grant-tables --skip-networking &<span style="color: #000000;">
- mysql
- mysql</span>><span style="color: #000000;"> flush privileges;
- mysql</span>> alter user root@<span style="color: #800000;">‘</span><span style="color: #800000;">localhost</span><span style="color: #800000;">‘</span> identified by <span style="color: #800000;">‘</span><span style="color: #800000;">123</span><span style="color: #800000;">‘</span>;
查看当前存在的用户:
- <span style="color: #0000ff;">select</span> user,host <span style="color: #0000ff;">from</span> mysql.user;
企业里创建用户一般是授权一个内网网段登录,最常见的网段写法有两种。
方法1:172.16.1.%(%为通配符,匹配所有内容)。
方法2:172.16.1.0/255.255.255.0,但是不能使用172.16.1.0/24,是个小遗憾。
标准的建用户方法:
- create user <span style="color: #800000;">‘</span><span style="color: #800000;">web</span><span style="color: #800000;">‘</span>@<span style="color: #800000;">‘</span><span style="color: #800000;">172.16.1.%</span><span style="color: #800000;">‘</span> identified by <span style="color: #800000;">‘</span><span style="color: #800000;">web123</span><span style="color: #800000;">‘</span>;
给用户授权
- # 创建用户
- create user ‘clsn‘@‘localhost‘ identified by ‘clsn123‘;
- # 查看用户
- select user,host from mysql.user;
- # 授权所有权限给clsn用户
- GRANT ALL ON *.* TO ‘clsn‘@‘localhost‘;
- # 查看clsn用户的权限
- SHOW GRANTS FOR ‘clsn‘@‘localhost‘\G
创建用户的同时授权
- grant all on *.* to clsn@‘172.16.1.%‘ identified by ‘clsn123‘;
- # 刷新权限
- flush privileges; #<==可以不用。
创建用户然后授权
- create user ‘clsn‘@‘localhost‘ identified by ‘clsn123‘;
- GRANT ALL ON *.* TO ‘clsn‘@‘localhost‘;
授权和root一样的权限
- grant all on *.* to system@‘localhost‘ identified by ‘clsn123‘ with grant option;
授权给用户select,create,insert,update 权限
- grant select,create,insert,update on clsn.* to ‘clsn‘@‘10.0.0.%‘ identified by ‘123‘;
回收权限
- REVOKE INSERT ON *.* FROM clsn@localhost;
可以授权的用户权限
- INSERT,SELECT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN,
- PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER,
- CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE,
- REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER
- ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE
【示例】博客授权收回示例
- grant select,insert,update,delete,create,drop on blog.* to ‘blog‘@‘172.16.1.%‘ identified by ‘blog123‘;
- revoke create,drop on blog.* from ‘blog‘@‘172.16.1.%‘;
授权博客类的最多权限:select,insert,update,delete
- mysql> SOURCE /data/mysql/world.sql
或者使用非交互式:(尽量避免使用mysql 导入数据,会产生大量的无用日志)
- mysql</data/mysql/world.sql
基本语法
- mysqladmin -u<name> -p<password> commands
MySQL用户管理及SQL语句详解
标签:lan replicat numbers 创建 view 客户端 number temporary 管理数据