Linux MariaDB基本用法 发表于 2022-01-11 浏览量 575 评论数 1 # 一、MariaDB数据库创建用户 ``` CREATE USER 'username'@'host' IDENTIFIED BY 'password'; username //需要创建的用户名 host //指定用户在哪个主机上可以登录，如果是本地用户可以用localhost，如果想让用户可以在任意地方登陆，可以使用% password //该用户的登录密码，可以为空。 ``` + 实例 ``` MariaDB [(none)]> create user ouge@'%' identified by '123.com'; Query OK, 0 rows affected (0.000 sec) MariaDB [(none)]> create user ouge1@'172.16.0.%' identified by '123.com'; Query OK, 0 rows affected (0.000 sec) ``` # 二、MariaDB数据库给用户授权 ``` GRANT Privileges ON DatabaseName.TableNmae to 'UserName'@'Host' privileges //用户操作权限，如select，insert，update，delete等，如果全部则使用ALL。 DatabaseName //要授权的数据库名 TableNmae //要授权的表名，如果受该用户对数据库和表相应的操作权限则可使用*.* ``` + 实例 ``` MariaDB [(none)]> grant select on ocsweb.* to ouge@'%'; //授权ouge拥有ocsweb数据库有查找权限，允许在任意登录 Query OK, 0 rows affected (0.000 sec) MariaDB [(none)]> flush privileges; //刷新权限 Query OK, 0 rows affected (0.000 sec) MariaDB [(none)]> grant all on ocsweb.* to ouge1@'172.16.0.%'; //授权ouge1拥有ocsweb数据库所有权限，允许在172.16.0.%登录，%表示任意。 Query OK, 0 rows affected (0.000 sec) MariaDB [(none)]> flush privileges; //刷新权限 Query OK, 0 rows affected (0.000 sec) ``` # 三、MariaDB数据库创建用户并授权 ``` MariaDB [(none)]> grant all on *.* to ouge3@'172.16.7.220' identified by '123.com'; //创建ouge3用户并给予权限，还指定用户远程登录地址。 Query OK, 0 rows affected (0.000 sec) MariaDB [(none)]> flush privileges; Query OK, 0 rows affected (0.000 sec) ``` # 四、MariaDB数据库查看用户 + 查看当前登录用户 ``` MariaDB [(none)]> select user(); //方法1 MariaDB [(none)]> select current_user; //方法2 MariaDB [(none)]> select current_user(); //方法3 ``` + 查看所有用户 ``` MariaDB [(none)]> select user,host,password from mysql.user; ``` + 查找显示所有用户（不重复） ``` MariaDB [mysql]> select distinct user from mysql.user; ``` # 五、MariaDB数据库删除用户 ``` MariaDB [(none)]> delete from mysql.user where user='ouge'; Query OK, 2 rows affected (0.000 sec) MariaDB [(none)]> drop user 'ouge1'@'172.16.0.%'; Query OK, 0 rows affected (0.000 sec) MariaDB [(none)]> delete from mysql.user where user='ouge' and host='172.16.0.%'; Query OK, 1 row affected (0.000 sec) ``` # 六、MariaDB数据库撤销用户权限 ``` revoke privileges on databasename.tablename from 'username'@'host'; ``` + 实例 ``` MariaDB [(none)]> grant select on ocsweb.* to ewain@localhost; //授权 Query OK, 0 rows affected (0.000 sec) MariaDB [(none)]> revoke select on ocsweb.* from ewain@localhost; //撤销授权 Query OK, 0 rows affected (0.000 sec) ``` + 查看授权信息 ``` MariaDB [(none)]> show grants for ewain@localhost; ``` # 七、远程登录数据库 ``` MariaDB [mysql]> select user,host from user where user='ewain'; //查看ewain用户的host MariaDB [mysql]> update user set host='%' where user='ewain'; //更改ewain的host为% Query OK, 1 row affected (0.000 sec) Rows matched: 1 Changed: 1 Warnings: 0 MariaDB [mysql]> flush privileges; //刷新权限 [root@ocsng-t ~]# mysql -h 172.16.6.12 -uewain -p //需要安装客户端，需要放行端口。 ``` # 八、修改密码 ``` #方法1:登录mysql mysql>set password for username@'host' = password('newpassword') //格式 mysql>set password for ewain@localhost = password('123.com') //例子 #方法2：用mysqladmin mysqladmin -u username -p oldpassword password newpassword //格式 mysqladmin -uroot -p123456 password 123.com //例子 #方法3：用update直接编辑user表，登录mysql mysql> use msyql; mysql> update user set password=password('123') where user='root' and host='localhost'; mysql> flush privileges; ``` # 九、分页查看 ``` mysql> pager less; 或者 mysql> pager more; ```
怎么收藏这篇文章？