MySQL创建用户与授权
创建数据库
sql
create database yearning default charset utf8mb4 collate utf8mb4_general_ci;mysql8修改密码
sql
ALTER USER 'zlhn'@'10.10.%' IDENTIFIED WITH caching_sha2_password BY '密码';
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'new_password';mysql5.x修改密码
sql
set password for 'root'@'localhost'=password('tCb2b4hNbPcnpPW6');一. 创建用户
命令:
sql
CREATE USER 'hn_crm'@'localhost' IDENTIFIED BY 'asdf@';
CREATE USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'new_password';
CREATE USER 'website'@'192.168.%' IDENTIFIED WITH caching_sha2_password BY 'new_password';说明：
- username：你将创建的用户名
- host：指定该用户在哪个主机上可以登陆，如果是本地用户可用localhost，如果想让该用户可以从任意远程主机登陆，可以使用通配符%
- password：该用户的登陆密码，密码可以为空，如果为空则该用户可以不需要密码登陆服务器
例子：
sql
CREATE USER 'dog'@'localhost' IDENTIFIED BY '123456';
CREATE USER 'pig'@'192.168.1.101_' IDENDIFIED BY '123456';
CREATE USER 'pig'@'%' IDENTIFIED BY '123456';
CREATE USER 'pig'@'%' IDENTIFIED BY '';
CREATE USER 'pig'@'%';二. 授权:
命令:
sql
GRANT privileges ON databasename.tablename TO username@'host';
grant select,insert,update,delete,alter,index,create,references on hn_crm.* to 'hn_crm'@'localhost';说明:
- privileges：用户的操作权限，如SELECT，INSERT，UPDATE, ALTER, DROP等，如果要授予所的权限则使用ALL
- databasename：数据库名
- tablename：表名，如果要授予该用户对所有数据库和表的相应操作权限则可用表示，如.*
例子:
sql
GRANT SELECT, INSERT ON test.user TO pig@'%';
GRANT ALL ON *.* TO 'pig'@'%';
GRANT ALL ON maindataplus.* TO 'pig'@'%';注意:
用以上命令授权的用户不能给其它用户授权，如果想让该用户可以授权，用以下命令:
sql
GRANT privileges ON databasename.tablename TO 'username'@'host' WITH GRANT OPTION;三.设置与更改用户密码
命令:
sql
SET PASSWORD FOR 'username'@'host' = PASSWORD('newpassword');如果是当前登陆用户用:
sql
SET PASSWORD = PASSWORD("newpassword");例子:
sql
SET PASSWORD FOR 'pig'@'%' = PASSWORD("123456");四. 撤销用户权限
查看用户授权
sql
show grants for yinxinerp@'192.168.%';命令:
sql
REVOKE privilege ON databasename.tablename FROM 'username'@'host';说明:
- privilege, databasename, tablename：同授权部分
例子:
sql
REVOKE SELECT ON *.* FROM 'pig'@'%';
revoke all privileges ON *.* from 'yinxinerp'@'192.168.%';注意:
假如你在给用户'pig'@'%'授权的时候是这样的（或类似的）：GRANT SELECT ON test.user TO 'pig'@'%'，则在使用REVOKE SELECT ON *.* FROM 'pig'@'%';命令并不能撤销该用户对test数据库中user表的SELECT 操作。相反，如果授权使用的是GRANT SELECT ON *.* TO 'pig'@'%';则REVOKE SELECT ON test.user FROM 'pig'@'%';命令也不能撤销该用户对test数据库中user表的Select权限。
具体信息可以用命令SHOW GRANTS FOR 'pig'@'%'; 查看。
五.删除用户
命令:
sql
DROP USER 'username'@'host';http://blog.csdn.net/piaocoder/article/details/53704126
初始化数据目录
sql
bin/mysqld --initialize --user=mysql --basesdir=/opt/mysql/mysql --datadir=/opt/mysql/mysql/data分组查询限制
sql
sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION'Docker安装MySQL5.7
shell
docker run --name mysql -p 50336:3306 \
-v /data/mysql57/conf:/etc/mysql/conf.d \
-v /data/mysql57/data:/var/lib/mysql \
-e TZ=Asia/Shanghai \
-e MYSQL_ROOT_PASSWORD=123456 \
--restart=always -d mysql:5.7挂载数据目录
shell
mount -t nfs 192.168.1.4:/data/crm /data/apps/crm/crm_data
192.168.1.4:/data/crm /data/apps/crm/crm_data nfs defaults,vers=4,x-systemd.requires=network-online.target 0 0