C:\Users\hct>mysql -u root -p123456
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 40633
Server version: 5.7.16 MySQL Community Server (GPL)
Copyright (c)2000, 2016, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h'for help. Type '\c' to clear the current input statement.
mysql>
C:\Users\hct>mysql -h192.168.1.90 -u root -p
Enter password: ******
4、用户权限
4.1 远程授权
· 远程授权有多种方式,如下,选择一种即可
//1、赋予任何主机以root用户无密码访问数据的权限
mysql>GRANT ALL PRIVILEGES ON *.* TO 'root'@'%'WITH GRANT OPTION
//2、root使用123456从任何主机连接到mysql服务器
mysql>GRANT ALL PRIVILEGES ON *.* TO 'root'@'%'IDENTIFIED BY '123456' WITH GRANT OPTION;
//3、只允许用户root从ip为192.168.1.3的主机连接到mysql服务器,并使用mypassword作为密码
mysql>GRANT ALL PRIVILEGES ON *.* TO 'root'@'192.168.1.3'IDENTIFIED BY '123456' WITH GRANT OPTION;
//4、更新版本的mysql授权
mysql>alter user 'root'@'localhost' identified by '123456';(by 接着的是密码)
//5、只授权部分权限:
mysql> grant select,insert,update,delete,create,drop on mysql.* to 'root'@'192.168.1.90' identified by '123456';
//刷新生效
mysql>FLUSH PRIVILEGES
//常用方法
grant all on *.* to root@'%' identified by '123456';
flush privileges;sudoservice mysql restart
· mysql创建用户并授权格式:grant 权限 on 数据库名.表名 to 用户@登录主机ip identified by "用户密码"; 具体应用如下
mysql> grant all on *.* to root@'%' identified by '123456';
Query OK, 0 rows affected, 1 warning (0.32 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.18 sec)#重启mysql服务
mysql>sudoservice mysql restart;
4.2 收回权限
· 收回select、insert、update、delete部分或全部权限,也可指定 格式: revoke 权限列表 on 数据库名.表名 from '用户名'@'主机名';
mysql> revoke select on * . * from root;
Query OK, 0 rows affected (0.20 sec)
mysql> revoke select on loo.user from 'root'@'localhost';
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema || loo || mysql || performance_schema || rayview || sys ||test|
+--------------------+
9 rows inset(0.24 sec)
6.3 查看数据库中的表
· use loo是使用名为loo的数据库
mysql> use loo;
Database changed
mysql> show tables;
+---------------+
| Tables_in_loo |
+---------------+
| user |
+---------------+
1 row inset(0.01 sec)
6.4 查看用户权限
· show grants for 用户名@ip,之前赋值所有ip都可,可不选择ip查看
mysql> show grants for root;
+-------------------------------------------+
| Grants for root@% |
+-------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'%'|
+-------------------------------------------+
1 row inset(0.00 sec)
6.5 查看mysql数据库的引擎
· 查看数据库支持的引擎
mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES || MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO || MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO || BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears)| NO | NO | NO || MyISAM | YES | MyISAM storage engine | NO | NO | NO || CSV | YES | CSV storage engine | NO | NO | NO || ARCHIVE | YES | Archive storage engine | NO | NO | NO || PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO || FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows inset(0.00 sec)