MariaDB的账号修改 ,赋予权限,和链接
2019-07-16 本文已影响0人
IT行者q
1.基本命令
启动
systemctl start mariadb.service
停止
systemctl stop mariadb.service
查看状态
systemctl status mariadb.service
重启
systemctl restart mariadb
2.修改密码
systemctl stop mariadb # 停止MariaDB数据库
mysqld_safe --skip-grant-tables # 安全模式进行MariaDB,并且跳过授权表
此时要打开另一个ssh窗口
mysql -uroot -p # 连接数据库
Enter password: # 不要输入密码,直接回车
MariaDB [(none)]> use mysql;
update user set password=PASSWORD("admin@1234") where user='root'; # 先重置数据库root密码
flush privileges; # 必须要刷新权限才能生效
systemctl restart mariadb # 重启MariaDB数据库,使用密码进入MariaDB数据库
3.创建新用户
MariaDB [(none)]> create user newuser@localhost identified by '123456';
Query OK, 0 rows affected (0.22 sec) #创建新用户
任意用户localhost连接
GRANT ALL PRIVILEGES ON *.* TO 'username'@'localhost' WITH GRANT OPTION;
任意用户host连接
GRANT ALL PRIVILEGES ON *.* TO 'username'@'%' WITH GRANT OPTION;
带密码的localhost连接
8.0以前的版本
GRANT ALL PRIVILEGES ON *.* TO 'username'@'localhost' IDENTIFIED BY 'your_password' WITH GRANT OPTION;
8.0版本分两步
# 创建账号密码
CREATE USER `wangwei`@`127.0.0.1` IDENTIFIED BY 'passowrd';
# 授予权限
GRANT ALL ON *.* TO `wangwei`@`127.0.0.1` WITH GRANT OPTION;
# 删除权限
REVOKE all privileges ON databasename.tablename FROM 'username'@'host';
# 修改密码
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '你的密码'; </pre>
刷新
FLUSH PRIVILEGES;
MariaDB [(none)]> grant all privileges on *.* to my@localhost identified by '123456';
Query OK, 0 rows affected (0.00 sec) #新用户授权,要在none中执行此命令
MariaDB [(none)]> show grants #查看权限是否生成
MariaDB [(none)]> select user,host from mysql.user; #验证全向是否生成
4.mysqld_safe A mysqld process already exists的问题解决
执行 ps aux | grep mysqld
kill -9 进程号 杀掉进程后重新执行 安全模式
5.如果是阿里云,去创建一个0.0.0.0/0 端口为3306的安全组
删除mysql账号
delete from user where user='XXX' and host='localhost';其中XXX为用户名,localhost为主机名。
delete from user where user='newbeidou' and host='%';
FLUSH PRIVILEGES;
授予权限
grant create on testdb.* to developer@'192.168.0.%';
revoke all on *.* from dba@localhost;
grant select,delete,update,create on bigloans.* to 'yu2036'@'%';