ubuntu24041__apt__mysql__241109_2">ubuntu24.04.1 用 apt 安装 mysql , 笔记241109

apt安装mysql sudo apt install mysql-server -y

sudo apt install mysql-server -y

修改 /etc/mysql/mysql.conf.d下的 mysqld.cnf 配置文件

sudo vi /etc/mysql/mysql.conf.d/mysqld.cnf

Ubuntu虽然安装mysql方便, 但默认不能远程访问, 原因在配置文件这条 bind-address = 127.0.0.1

#bind-address           = 127.0.0.1

本地root('root'@'localhost') 无初始密码, 可以直接登录

mysql -uroot

创建远程root: ('root'@'%') , 可简写成 root , 无密码,也可指定密码, 授予所有权限, 并将本地本地root:('root'@'localhost')赋予创建远程root: ('root'@'%') , 并默认使用该角色, 然后刷新权限

CREATE USER IF NOT EXISTS 'root'@'%' IDENTIFIED BY ''; GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION; GRANT root@'localhost' TO 'root'@'%'; SET DEFAULT ROLE root@'localhost' TO 'root'@'%'; FLUSH PRIVILEGES;

'root'@'%', 可简写成 root , 另外, GRANT ALL ON 等效 GRANT ALL PRIVILEGES ON

CREATE USER IF NOT EXISTS root IDENTIFIED BY ''; GRANT ALL ON *.* TO root WITH GRANT OPTION; GRANT root@'localhost' TO root; SET DEFAULT ROLE root@'localhost' TO root; FLUSH PRIVILEGES;

重启mysql服务

sudo systemctl restart mysql

MySQL8 创建用户,设置修改密码,授权

GRANT ALL PRIVILEGES ON *.* TO '用户名'@'%' IDENTIFIED BY '密码' WITH GRANT OPTION

CREATE USER u@'%' IDENTIFIED BY '密';  -- 创建用户并指定密码
GRANT ALL PRIVILEGES ON *.* TO u@'%' WITH GRANT OPTION;  --授权

CREATE USER u@'%' ;  -- 创建用户
ALTER  USER u@'%' IDENTIFIED BY '密';  -- 指定密码
GRANT ALL PRIVILEGES ON *.* TO u@'%' WITH GRANT OPTION;  -- 授权

CREATE USER u@'%' IDENTIFIED BY '密';  -- 创建用户并指定密码
GRANT ALL ON *.* TO u@'%' WITH GRANT OPTION;  --授权

FLUSH PRIVILEGES;

可以写在一行,以分号分隔

比如创建一个名为remote的用户

CREATE USER IF NOT EXISTS 'remote'@'%' IDENTIFIED BY 'remote'; GRANT ALL ON *.* TO 'remote'@'%' WITH GRANT OPTION; FLUSH PRIVILEGES;

remote='remote'@'%

create user remote identified by 'remote'; grant all on *.* to remote with grant option; flush privileges;

创建远程root的语句模板

创建root@'%' , 并将 root@'localhost' 的权限授予 root@'%'

CREATE USER IF NOT EXISTS 'root'@'%' IDENTIFIED BY '密码'; GRANT ALL ON *.* TO 'root'@'%' WITH GRANT OPTION; FLUSH PRIVILEGES;

创建 root@'%', 并让root@'%'扮演root@'localhost' 的角色 , 并设置为默认角色

CREATE USER IF NOT EXISTS 'root'@'%' IDENTIFIED BY '密码'; GRANT root@'localhost' TO 'root'@'%'; SET DEFAULT ROLE root@'localhost' TO 'root'@'%'; FLUSH PRIVILEGES;

权限和角色两者都加持

CREATE USER IF NOT EXISTS 'root'@'%' IDENTIFIED BY '密码'; GRANT ALL ON *.* TO 'root'@'%' WITH GRANT OPTION; GRANT root@'localhost' TO 'root'@'%'; SET DEFAULT ROLE root@'localhost' TO 'root'@'%'; FLUSH PRIVILEGES;

无密码

CREATE USER IF NOT EXISTS 'root'@'%' IDENTIFIED BY ''; GRANT ALL ON *.* TO 'root'@'%' WITH GRANT OPTION; GRANT root@'localhost' TO 'root'@'%'; SET DEFAULT ROLE root@'localhost' TO 'root'@'%'; FLUSH PRIVILEGES;

GRANT ALL PRIVILEGES ON 等效 GRANT ALL ON

CREATE USER IF NOT EXISTS 'root'@'%' IDENTIFIED BY ''; GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION; GRANT root@'localhost' TO 'root'@'%'; SET DEFAULT ROLE root@'localhost' TO 'root'@'%'; FLUSH PRIVILEGES;

'root'@'%'可以写成root@'%'可以写成root

CREATE USER IF NOT EXISTS root IDENTIFIED BY ''; GRANT ALL ON *.* TO root WITH GRANT OPTION; GRANT root@'localhost' TO root; SET DEFAULT ROLE root@'localhost' TO root; FLUSH PRIVILEGES;

删除用户 DROP USER 'u'@'h'

DROP USER root;只会删除'root'@'%' , 不会删除 'root'@'localhost'

DROP USER root;

DROP USER root@'%';

DROP USER 'root'@'%';

DROP USER IF EXISTS root;只会删除'root'@'%' , 不会删除 'root'@'localhost'

DROP USER IF EXISTS root;

DROP USER IF EXISTS root@'%';

DROP USER IF EXISTS 'root'@'%';