1:数据库的基本命令实战
1)启动和关闭数据库命令
/etc/init.d/mysqld start
/etc/init.d/mysqld stop
2)查看数据库端口
ss -lnt|grep 3306
3)查看数据库进程
ps -ef|grep mysql|grep -v grep
4)数据库启动原理
/etc/init.d/mysqld 是一个shell启动脚本,启动后最终会调用mysqld_safe脚本,最后调用mysqld服务启动mysql。
1.初始化数据库启动方法
mysqld_safe --user=mysql &
2.强制关闭数据库方法
killall mysqld
pkill mysqld
killall -9 mysqld
总结killall,kill,pkill命令?
5)优雅关闭数据库方法
第一种mysqladmin方法:
mysqladmin -uroot -phubo2017 shutdown
第二种自带脚本
/etc/init.d/mysqld stop
第三种kill信号的方法
kill -USE2 'cat path/pid'
6)多实例的启动命令关闭命令
自己写脚本启动
启动
/data/3306/mysql start
/data/3307/mysql start
关闭
/data/3306/mysql stop
/data/3307/mysql stop
2:登录数据库
1)单实例数据库登录的方法
1.mydql <====刚装完系统无密码情况登录方式。不要密码
2.mysql -uroot <====刚装完系统无密码情况登录方式。不要密码
3.mysql -uroot -p <====这是标准的dba命令行登录命令。
4.mysql -uroot -p 'hubo2013' <====非脚本一般不这样使用,密码明文会泄露密码。
2)多实例的登录方法
mysql -uroot -p -S /data/3306/mysql.sock
mysql -uroot -p -S /data/3307/mysql.sock
提示:多实例通过mysql 的-S命令指定不同的sock文件登录不同的服务中。
注意:多实例的远程连接无需指定sock路径
mysql -uroot -p -h 127.0.0.1 -p 3307
3:设置和修改数据库root用户密码
设置密码
mysqladmin -u root password 'hubo123' <======没有密码的用户设置密码命令
mysqladmin -u root -p'hubo456' password 'hubo123' -S /data/3306/mysql.sock <===适合多实例方式
以上的命令是在linux命令行,不是mysql命令行。
修改管理员root密码之一:命令行外修改法
mysqladmin -u root -p'hubo456' password 'hubo123'
mysqladmin -u root -p'hubo456' password 'hubo123' -S /data/3306/mysql.sock <===适合多实例方式
修改管理员root密码之二:sql语句修改法
mysql> update mysql.user set password=password(”hubo456“) where user='root' and host='localhost';
立即生效
flush privileges;
这种方式适合密码丢失用,这种方法慎用
1,必须指定where条件,
2,必须使用password()函数来加密更改密码。
第三种方法
mysql> set password=password('hubo2017')
flush privileges;
这种方式适合改密码
SQL语句最常见的分类
DDL-数据定义语言(CREATE,ALTER,DROP)《=====运维
DML-数据库操作语言(SELECT,INSERT,DELETRE,UPDATE,),《====开发
DCL-数据控制语言(GRANT,REVOKE,COMMIT,ROLLBACK)《====运维
数据库常见管理
1)创建数据库
a.建立一个hubo_default的数据库
命令语法:create database <数据库名>
mysql> create database hubo;
+--------------------+
| Database |
+--------------------+
| information_schema |
| hubo |
| mysql |
| performance_schema |
+--------------------+
4 rows in set (0.03 sec)
mysql> show databases;
mysql> show create database hubo\G
*************************** 1. row ***************************
Database: hubo
Create Database: CREATE DATABASE `hubo` /*!40100 DEFAULT CHARACTER SET utf8 */
1 row in set (0.00 sec)
小结:
create database hubo;《==========默认数据库配置,
create database hubo_gbk DEFAULT CHARACTER SET gbk COLLATE gbk_chinese_ci; 《====创建gbk字符集数据库
create database hubo_utf8 DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci; 《====创建utf8字符集数据库
提示:字符集不一致是字符集中文内容乱码的罪魁祸首
如果编译的时候指定了特定的字符集,则以后创建对应字符集的数据库不需要指定字符集了,如下:
cmake . -DCMAKE_INSTALL_PREFIX=/application/mysql-5.5.32 \
-DMYSQL_DATADIR=/application/mysql-5.5.32/data \
-DMYSQL_UNIX_ADDR=/application/mysql-5.5.32/tmp/mysql.sock \
-DDEFAULT_CHARSET=utf8 \
-DDEFAULT_COLLATION=utf8_general_ci \
企业里怎么创建数据库呢?
1,根据开发的程序确定字符集(建议UTF8)!
2,编译时候指定字符集,例如
-DDEFAULT_CHARSET=utf8 \
-DDEFAULT_COLLATION=utf8_general_ci \
然后将苦的时候迷人创建即可,create database hubo;。
3,编译的时候没有指定字符集或者指定了和程序不同的字符集,如何解决?
指定字符集创建数据即可。
create database hubo_gbk DEFAULT CHARACTER SET gbk COLLATE gbk_chinese_ci; 《====创建gbk字符集数据库
create database hubo_utf8 DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci; 《====创建utf8字符集数据库
显示数据库
命令:show databases;
mysql> select database();
+------------+
| database() |
+------------+
| NULL |
+------------+
1 row in set (0.00 sec)
查看当前数据库
mysql> use hubo
Database changed
mysql> select database();
+------------+
| database() |
+------------+
| hubo |
+------------+
1 row in set (0.00 sec)
mysql>
删除数据库
命令:drop database <数据库名>
连接数据库
命令:use <数据库名> 《====相当于linux下的cd切换目录的命令,use 是切换数据库
mysql> use root
ERROR 1049 (42000): Unknown database 'root'
mysql> select database();
+------------+
| database() |
+------------+
| hubo |
+------------+
1 row in set (0.00 sec)
删除数据库系统多余账号
语法:drop user"user"@"主机域"; 《====注意引号,可以是单或双引号,但是不能不加。
创建数据库用户及赋予用户权限
1通过mysql中输入“help grant”得到如下帮助信息。
mysql> help grant
省略一部分
CREATE USER 'jeffrey'@'localhost' IDENTIFIED BY 'mypass';
GRANT ALL ON db1.* TO 'jeffrey'@'localhost';
GRANT SELECT ON db2.invoice TO 'jeffrey'@'localhost';
GRANT USAGE ON *.* TO 'jeffrey'@'localhost' WITH MAX_QUERIES_PER_HOUR 90;
省略一部分
2运维人员比较常用的创建用户的方法是,使用grant命令在创建用户的同时进行权限授权。具体授权例子为:
GRANT ALL ON db1.* TO 'jeffrey'@'localhost'IDENTIFIED BY 'mypass';
3上述grant命令帮组里还提供了一个先用create命令创建用户,然后再grant授权的方法,即创建用户和授权
权限分开进行,例:
CREATE USER 'jeffrey'@'localhost' IDENTIFIED BY 'mypass';
GRANT ALL ON db1.* TO 'jeffrey'@'localhost';
以上两条命令相当于下面一条命令
GRANT ALL ON db1.* TO 'jeffrey'@'localhost'IDENTIFIED BY 'mypass';
例如:
mysql> select user,host from mysql.user;
+------+-----------+
| user | host |
+------+-----------+
| root | 127.0.0.1 |
| root | localhost |
+------+-----------+
2 rows in set (0.00 sec
建立库,权限,密码
mysql> GRANT ALL privileges ON test.* TO 'hubo'@'localhost'IDENTIFIED BY 'hubo2017';
Query OK, 0 rows affected (0.00 sec)
刷新权限
mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)
mysql> select user,host from mysql.user;
+------+-----------+
| user | host |
+------+-----------+
| root | 127.0.0.1 |
| hubo | localhost |
| root | localhost |
+------+-----------+
3 rows in set (0.00 sec)
mysql>
查看权限
mysql> show grants for hubo@localhost;
+------------------------------------------------------------------------------------------------------------
-+| Grants for hubo@localhost
|+------------------------------------------------------------------------------------------------------------
-+| GRANT USAGE ON *.* TO 'hubo'@'localhost' IDENTIFIED BY PASSWORD '*E62866614323971603987EC0281B68439D5F8785'
|| GRANT ALL PRIVILEGES ON `test`.* TO 'hubo'@'localhost'
|+------------------------------------------------------------------------------------------------------------
-+2 rows in set (0.00 sec)
mysql>
2:create和grant配合法
1,首先创建用户username及密码passwd,授权主机localhost。
CREATE USER 'username'@'localhost' IDENTIFIED BY 'passwd';
2,然后授权 localhost主机上通过用户username管理dbname数据库的所有权限,无需密码。
GRANT ALL ON dbname.* TO 'username'@'localhost';
3,操作案例2:
创建hubo2018用户,对test库具备所有权限,允许从 localhost主机登录管理数据库,密码是hubo2018123。
4,实战演示
查看当前数据库用户情况,然后执行对应命令创建用户如下:
创建一个用户,给密码
mysql> CREATE USER 'hubo2018'@'localhost' IDENTIFIED BY 'hubo2018123';
Query OK, 0 rows affected (0.00 sec)
还没权限 GRANT USAGE ON
mysql> show grants for hubo2018@localhost;
+------------------------------------------------------------------------------------------------------------
-----+| Grants for hubo2018@localhost
|+------------------------------------------------------------------------------------------------------------
-----+| GRANT USAGE ON *.* TO 'hubo2018'@'localhost' IDENTIFIED BY PASSWORD '*0236C076537E7C61843F94B8AED65B99B8C97
C72' |+------------------------------------------------------------------------------------------------------------
-----+1 row in set (0.00 sec)
mysql>
授权
mysql> GRANT ALL ON test.* TO 'hubo2018'@'localhost';
Query OK, 0 rows affected (0.00 sec)
mysql> show grants for hubo2018@localhost;
+------------------------------------------------------------------------------------------------------------
-----+| Grants for hubo2018@localhost
|+------------------------------------------------------------------------------------------------------------
-----+| GRANT USAGE ON *.* TO 'hubo2018'@'localhost' IDENTIFIED BY PASSWORD '*0236C076537E7C61843F94B8AED65B99B8C97
C72' || GRANT ALL PRIVILEGES ON `test`.* TO 'hubo2018'@'localhost'
|+------------------------------------------------------------------------------------------------------------
-----+2 rows in set (0.02 sec)
记得刷新权限哦
mysql> flush privileges;
Query OK, 0 rows affected (0.00 se
授权局域网内主机远程连接数据库:
根据grant命令语法,我们知道test@'localhost'位置为授权访问数据库的主机,localhost可以用域名,IP地址
或IP段来替代,因此,要授权局域网内主机可以通过如下方法实现:
1,百分号匹配法
mysql> grant all on *.* to test@'10.0.0.%' identifide 'test123'
Query OK, 0 rows affected (0.00 sec)
记得刷新权限哦
mysql> flush privileges;
Query OK, 0 rows affected (0.00 se
2,子网掩码配置法
mysql> grant all on *.* to test@'10.0.0.0/255.255.255.0' identifide 'test123'
Query OK, 0 rows affected (0.00 sec)
记得刷新权限哦
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
实战演示一:
mysql> CREATE USER 'test'@'10.0.0.%' IDENTIFIED BY 'test';
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
连接成功
[root@mysql-node1 ~]# mysql -utest -ptest -h 172.16.18.226
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 17
Server version: 5.5.32 Source distribution
Copyright (c) 2000, 2013, 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>
实战演示二
mysql> CREATE USER 'test1'@'172.16.18.0/255.255.255.0' IDENTIFIED BY 'test1';
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec
连接成功
[root@mysql-node1 ~]# mysql -utest1 -ptest1 -h 172.16.18.226
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 22
Server version: 5.5.32 Source distribution
Copyright (c) 2000, 2013, 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>
数据库用户可以授权的权限有哪些
通过实验看看ALL有哪些权限
mysql> show grants for hubo@localhost;
+------------------------------------------------------------------------------------------------------------
-+| Grants for hubo@localhost
|+------------------------------------------------------------------------------------------------------------
-+| GRANT USAGE ON *.* TO 'hubo'@'localhost' IDENTIFIED BY PASSWORD '*E62866614323971603987EC0281B68439D5F8785'
|| GRANT ALL PRIVILEGES ON `test`.* TO 'hubo'@'localhost'
|+------------------------------------------------------------------------------------------------------------
-+2 rows in set (0.00 sec)
mysql
这个没有收回成功
mysql> show grants for hubo@localhost;
+------------------------------------------------------------------------------------------------------------
-+| Grants for hubo@localhost
|+------------------------------------------------------------------------------------------------------------
-+| GRANT USAGE ON *.* TO 'hubo'@'localhost' IDENTIFIED BY PASSWORD '*E62866614323971603987EC0281B68439D5F8785'
|| GRANT ALL PRIVILEGES ON `test`.* TO 'hubo'@'localhost'
|+------------------------------------------------------------------------------------------------------------
-+2 rows in set (0.00 sec)
这个收回成功了
mysql> REVOKE INSERT ON test.* FROM 'hubo'@'localhost';
Query OK, 0 rows affected (0.00 sec)
mysql> show grants for hubo@localhost;
+------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------+| Grants for hubo@localhost
|+------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------+| GRANT USAGE ON *.* TO 'hubo'@'localhost' IDENTIFIED BY PASSWORD '*E62866614323971603987EC0281B68439D5F8785'
|| GRANT SELECT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES,
EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER ON `test`.* TO 'hubo'@'localhost' |+------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------+2 rows in set (0.00 sec)
mysql>
不进入数据库执行命令
[root@mysql-node1 ~]# mysql -uroot -phubo2017 -e "show grants for hubo@localhost;"
+------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------+| Grants for hubo@localhost
|+------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------+| GRANT USAGE ON *.* TO 'hubo'@'localhost' IDENTIFIED BY PASSWORD '*E62866614323971603987EC0281B68439D5F8785'
|| GRANT SELECT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES,
EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER ON `test`.* TO 'hubo'@'localhost' |+------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------+
1)启动和关闭数据库命令
/etc/init.d/mysqld start
/etc/init.d/mysqld stop
2)查看数据库端口
ss -lnt|grep 3306
3)查看数据库进程
ps -ef|grep mysql|grep -v grep
4)数据库启动原理
/etc/init.d/mysqld 是一个shell启动脚本,启动后最终会调用mysqld_safe脚本,最后调用mysqld服务启动mysql。
1.初始化数据库启动方法
mysqld_safe --user=mysql &
2.强制关闭数据库方法
killall mysqld
pkill mysqld
killall -9 mysqld
总结killall,kill,pkill命令?
5)优雅关闭数据库方法
第一种mysqladmin方法:
mysqladmin -uroot -phubo2017 shutdown
第二种自带脚本
/etc/init.d/mysqld stop
第三种kill信号的方法
kill -USE2 'cat path/pid'
6)多实例的启动命令关闭命令
自己写脚本启动
启动
/data/3306/mysql start
/data/3307/mysql start
关闭
/data/3306/mysql stop
/data/3307/mysql stop
2:登录数据库
1)单实例数据库登录的方法
1.mydql <====刚装完系统无密码情况登录方式。不要密码
2.mysql -uroot <====刚装完系统无密码情况登录方式。不要密码
3.mysql -uroot -p <====这是标准的dba命令行登录命令。
4.mysql -uroot -p 'hubo2013' <====非脚本一般不这样使用,密码明文会泄露密码。
2)多实例的登录方法
mysql -uroot -p -S /data/3306/mysql.sock
mysql -uroot -p -S /data/3307/mysql.sock
提示:多实例通过mysql 的-S命令指定不同的sock文件登录不同的服务中。
注意:多实例的远程连接无需指定sock路径
mysql -uroot -p -h 127.0.0.1 -p 3307
3:设置和修改数据库root用户密码
设置密码
mysqladmin -u root password 'hubo123' <======没有密码的用户设置密码命令
mysqladmin -u root -p'hubo456' password 'hubo123' -S /data/3306/mysql.sock <===适合多实例方式
以上的命令是在linux命令行,不是mysql命令行。
修改管理员root密码之一:命令行外修改法
mysqladmin -u root -p'hubo456' password 'hubo123'
mysqladmin -u root -p'hubo456' password 'hubo123' -S /data/3306/mysql.sock <===适合多实例方式
修改管理员root密码之二:sql语句修改法
mysql> update mysql.user set password=password(”hubo456“) where user='root' and host='localhost';
立即生效
flush privileges;
这种方式适合密码丢失用,这种方法慎用
1,必须指定where条件,
2,必须使用password()函数来加密更改密码。
第三种方法
mysql> set password=password('hubo2017')
flush privileges;
这种方式适合改密码
SQL语句最常见的分类
DDL-数据定义语言(CREATE,ALTER,DROP)《=====运维
DML-数据库操作语言(SELECT,INSERT,DELETRE,UPDATE,),《====开发
DCL-数据控制语言(GRANT,REVOKE,COMMIT,ROLLBACK)《====运维
数据库常见管理
1)创建数据库
a.建立一个hubo_default的数据库
命令语法:create database <数据库名>
mysql> create database hubo;
+--------------------+
| Database |
+--------------------+
| information_schema |
| hubo |
| mysql |
| performance_schema |
+--------------------+
4 rows in set (0.03 sec)
mysql> show databases;
mysql> show create database hubo\G
*************************** 1. row ***************************
Database: hubo
Create Database: CREATE DATABASE `hubo` /*!40100 DEFAULT CHARACTER SET utf8 */
1 row in set (0.00 sec)
小结:
create database hubo;《==========默认数据库配置,
create database hubo_gbk DEFAULT CHARACTER SET gbk COLLATE gbk_chinese_ci; 《====创建gbk字符集数据库
create database hubo_utf8 DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci; 《====创建utf8字符集数据库
提示:字符集不一致是字符集中文内容乱码的罪魁祸首
如果编译的时候指定了特定的字符集,则以后创建对应字符集的数据库不需要指定字符集了,如下:
cmake . -DCMAKE_INSTALL_PREFIX=/application/mysql-5.5.32 \
-DMYSQL_DATADIR=/application/mysql-5.5.32/data \
-DMYSQL_UNIX_ADDR=/application/mysql-5.5.32/tmp/mysql.sock \
-DDEFAULT_CHARSET=utf8 \
-DDEFAULT_COLLATION=utf8_general_ci \
企业里怎么创建数据库呢?
1,根据开发的程序确定字符集(建议UTF8)!
2,编译时候指定字符集,例如
-DDEFAULT_CHARSET=utf8 \
-DDEFAULT_COLLATION=utf8_general_ci \
然后将苦的时候迷人创建即可,create database hubo;。
3,编译的时候没有指定字符集或者指定了和程序不同的字符集,如何解决?
指定字符集创建数据即可。
create database hubo_gbk DEFAULT CHARACTER SET gbk COLLATE gbk_chinese_ci; 《====创建gbk字符集数据库
create database hubo_utf8 DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci; 《====创建utf8字符集数据库
显示数据库
命令:show databases;
mysql> select database();
+------------+
| database() |
+------------+
| NULL |
+------------+
1 row in set (0.00 sec)
查看当前数据库
mysql> use hubo
Database changed
mysql> select database();
+------------+
| database() |
+------------+
| hubo |
+------------+
1 row in set (0.00 sec)
mysql>
删除数据库
命令:drop database <数据库名>
连接数据库
命令:use <数据库名> 《====相当于linux下的cd切换目录的命令,use 是切换数据库
mysql> use root
ERROR 1049 (42000): Unknown database 'root'
mysql> select database();
+------------+
| database() |
+------------+
| hubo |
+------------+
1 row in set (0.00 sec)
删除数据库系统多余账号
语法:drop user"user"@"主机域"; 《====注意引号,可以是单或双引号,但是不能不加。
创建数据库用户及赋予用户权限
1通过mysql中输入“help grant”得到如下帮助信息。
mysql> help grant
省略一部分
CREATE USER 'jeffrey'@'localhost' IDENTIFIED BY 'mypass';
GRANT ALL ON db1.* TO 'jeffrey'@'localhost';
GRANT SELECT ON db2.invoice TO 'jeffrey'@'localhost';
GRANT USAGE ON *.* TO 'jeffrey'@'localhost' WITH MAX_QUERIES_PER_HOUR 90;
省略一部分
2运维人员比较常用的创建用户的方法是,使用grant命令在创建用户的同时进行权限授权。具体授权例子为:
GRANT ALL ON db1.* TO 'jeffrey'@'localhost'IDENTIFIED BY 'mypass';
3上述grant命令帮组里还提供了一个先用create命令创建用户,然后再grant授权的方法,即创建用户和授权
权限分开进行,例:
CREATE USER 'jeffrey'@'localhost' IDENTIFIED BY 'mypass';
GRANT ALL ON db1.* TO 'jeffrey'@'localhost';
以上两条命令相当于下面一条命令
GRANT ALL ON db1.* TO 'jeffrey'@'localhost'IDENTIFIED BY 'mypass';
例如:
mysql> select user,host from mysql.user;
+------+-----------+
| user | host |
+------+-----------+
| root | 127.0.0.1 |
| root | localhost |
+------+-----------+
2 rows in set (0.00 sec
建立库,权限,密码
mysql> GRANT ALL privileges ON test.* TO 'hubo'@'localhost'IDENTIFIED BY 'hubo2017';
Query OK, 0 rows affected (0.00 sec)
刷新权限
mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)
mysql> select user,host from mysql.user;
+------+-----------+
| user | host |
+------+-----------+
| root | 127.0.0.1 |
| hubo | localhost |
| root | localhost |
+------+-----------+
3 rows in set (0.00 sec)
mysql>
查看权限
mysql> show grants for hubo@localhost;
+------------------------------------------------------------------------------------------------------------
-+| Grants for hubo@localhost
|+------------------------------------------------------------------------------------------------------------
-+| GRANT USAGE ON *.* TO 'hubo'@'localhost' IDENTIFIED BY PASSWORD '*E62866614323971603987EC0281B68439D5F8785'
|| GRANT ALL PRIVILEGES ON `test`.* TO 'hubo'@'localhost'
|+------------------------------------------------------------------------------------------------------------
-+2 rows in set (0.00 sec)
mysql>
2:create和grant配合法
1,首先创建用户username及密码passwd,授权主机localhost。
CREATE USER 'username'@'localhost' IDENTIFIED BY 'passwd';
2,然后授权 localhost主机上通过用户username管理dbname数据库的所有权限,无需密码。
GRANT ALL ON dbname.* TO 'username'@'localhost';
3,操作案例2:
创建hubo2018用户,对test库具备所有权限,允许从 localhost主机登录管理数据库,密码是hubo2018123。
4,实战演示
查看当前数据库用户情况,然后执行对应命令创建用户如下:
创建一个用户,给密码
mysql> CREATE USER 'hubo2018'@'localhost' IDENTIFIED BY 'hubo2018123';
Query OK, 0 rows affected (0.00 sec)
还没权限 GRANT USAGE ON
mysql> show grants for hubo2018@localhost;
+------------------------------------------------------------------------------------------------------------
-----+| Grants for hubo2018@localhost
|+------------------------------------------------------------------------------------------------------------
-----+| GRANT USAGE ON *.* TO 'hubo2018'@'localhost' IDENTIFIED BY PASSWORD '*0236C076537E7C61843F94B8AED65B99B8C97
C72' |+------------------------------------------------------------------------------------------------------------
-----+1 row in set (0.00 sec)
mysql>
授权
mysql> GRANT ALL ON test.* TO 'hubo2018'@'localhost';
Query OK, 0 rows affected (0.00 sec)
mysql> show grants for hubo2018@localhost;
+------------------------------------------------------------------------------------------------------------
-----+| Grants for hubo2018@localhost
|+------------------------------------------------------------------------------------------------------------
-----+| GRANT USAGE ON *.* TO 'hubo2018'@'localhost' IDENTIFIED BY PASSWORD '*0236C076537E7C61843F94B8AED65B99B8C97
C72' || GRANT ALL PRIVILEGES ON `test`.* TO 'hubo2018'@'localhost'
|+------------------------------------------------------------------------------------------------------------
-----+2 rows in set (0.02 sec)
记得刷新权限哦
mysql> flush privileges;
Query OK, 0 rows affected (0.00 se
授权局域网内主机远程连接数据库:
根据grant命令语法,我们知道test@'localhost'位置为授权访问数据库的主机,localhost可以用域名,IP地址
或IP段来替代,因此,要授权局域网内主机可以通过如下方法实现:
1,百分号匹配法
mysql> grant all on *.* to test@'10.0.0.%' identifide 'test123'
Query OK, 0 rows affected (0.00 sec)
记得刷新权限哦
mysql> flush privileges;
Query OK, 0 rows affected (0.00 se
2,子网掩码配置法
mysql> grant all on *.* to test@'10.0.0.0/255.255.255.0' identifide 'test123'
Query OK, 0 rows affected (0.00 sec)
记得刷新权限哦
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
实战演示一:
mysql> CREATE USER 'test'@'10.0.0.%' IDENTIFIED BY 'test';
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
连接成功
[root@mysql-node1 ~]# mysql -utest -ptest -h 172.16.18.226
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 17
Server version: 5.5.32 Source distribution
Copyright (c) 2000, 2013, 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>
实战演示二
mysql> CREATE USER 'test1'@'172.16.18.0/255.255.255.0' IDENTIFIED BY 'test1';
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec
连接成功
[root@mysql-node1 ~]# mysql -utest1 -ptest1 -h 172.16.18.226
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 22
Server version: 5.5.32 Source distribution
Copyright (c) 2000, 2013, 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>
数据库用户可以授权的权限有哪些
通过实验看看ALL有哪些权限
mysql> show grants for hubo@localhost;
+------------------------------------------------------------------------------------------------------------
-+| Grants for hubo@localhost
|+------------------------------------------------------------------------------------------------------------
-+| GRANT USAGE ON *.* TO 'hubo'@'localhost' IDENTIFIED BY PASSWORD '*E62866614323971603987EC0281B68439D5F8785'
|| GRANT ALL PRIVILEGES ON `test`.* TO 'hubo'@'localhost'
|+------------------------------------------------------------------------------------------------------------
-+2 rows in set (0.00 sec)
mysql
这个没有收回成功
mysql> show grants for hubo@localhost;
+------------------------------------------------------------------------------------------------------------
-+| Grants for hubo@localhost
|+------------------------------------------------------------------------------------------------------------
-+| GRANT USAGE ON *.* TO 'hubo'@'localhost' IDENTIFIED BY PASSWORD '*E62866614323971603987EC0281B68439D5F8785'
|| GRANT ALL PRIVILEGES ON `test`.* TO 'hubo'@'localhost'
|+------------------------------------------------------------------------------------------------------------
-+2 rows in set (0.00 sec)
这个收回成功了
mysql> REVOKE INSERT ON test.* FROM 'hubo'@'localhost';
Query OK, 0 rows affected (0.00 sec)
mysql> show grants for hubo@localhost;
+------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------+| Grants for hubo@localhost
|+------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------+| GRANT USAGE ON *.* TO 'hubo'@'localhost' IDENTIFIED BY PASSWORD '*E62866614323971603987EC0281B68439D5F8785'
|| GRANT SELECT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES,
EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER ON `test`.* TO 'hubo'@'localhost' |+------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------+2 rows in set (0.00 sec)
mysql>
不进入数据库执行命令
[root@mysql-node1 ~]# mysql -uroot -phubo2017 -e "show grants for hubo@localhost;"
+------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------+| Grants for hubo@localhost
|+------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------+| GRANT USAGE ON *.* TO 'hubo'@'localhost' IDENTIFIED BY PASSWORD '*E62866614323971603987EC0281B68439D5F8785'
|| GRANT SELECT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES,
EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER ON `test`.* TO 'hubo'@'localhost' |+------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------+