八、MySQL数据库高级操作

数据库的备份与还原

数据的备份：mysqldump命令

mysqldump -u username -p password dbname [tbname1 [tbname2...]] > filename.sql

还原数据库：mysql命令或者source命令

mysql -u username -p password [dbname]<filename.sql
//注意：应先新建空数据库，再还原数据到该数据库中。执行环境是cmd下

source filename.sql;
//注意：先新建空数据库，并且进入该数据库，执行该命令

用户管理

mysql数据库中的user表

1. 用户列
   包括Host，User，Password，分别代表主机名，用户名，密码，前两者是该表的联合主键，当用户连接服务器的时候，必须匹配这三者才能成功连接。

2. 权限列
   包括Select_priv，Insert_priv，Update_priv等以priv结尾的字段，决定了用户的权限，包括查询权限，修改权限，关闭服务等权限。默认为N，即不允许

3. 安全列
   1.ssl_type和ssl_cipher：用于加密2.x509_issuer和x509_subject标准：可用来识别用户3.plugin和authentication_string：用于存储与授权相关的插件

4. 资源控制列
   a. max_question：每小时允许用户执行查询操作的次数
   b. max_updates：每小时允许用户执行更新操作的次数
   c. max_connections：每小时允许用户建立连接的次数
   d. max_user_connections：允许单个用户同时建立连接的次数

创建用户

1. GRANT语句：创建用户并赋予权限

GRANT privileges ON database.table TO 
   'username'@'hostname' [IDENTIFIED BY [PASSWORD]'password'] 
   [,'username'@'hostname' [IDENTIFIED BY [PASSWORD]'password']]...

privileges 表示该用户具有的权限信息
database.table 表示新用户的权限范围表，可以在指定的数据库、表上使用自己的权限
username 是新用户的名称
hostname 是主机名
password 是新用户的密码

例一：创建一个新用户，用户名为use1，密码为123，并授予该用户对charter8.student表有查询权限
GRANT SELECT ON chapter8.student TO 'user1'@'localhost' IDENTIFIED BY '123';

2. CREATE USER语句：只创建用户，没有权限

CREATE USER 'username'@'hostname' [IDENTIFIED BY [PASSWORD]'password'] [,'username'@'hostname' [IDENTIFIED BY [PASSWORD]'password']]...

例一：创建一个新用户，用户名为use1，密码为123
create user 'user1'@'localhost' identified by '123';

3. INSERT INTO语句：在user表中插入一条数据

INSERT INTO mysql.user(Host,User,Password,ssl_cipter,x509_issuer,x509_subject)
    VALUE('hostname','username',PASSWORD('password'),'','','');
FLUSH PRIVILEGES;

例一：创建一个新用户，用户名为use1，密码为123
INSERT INTO mysql.user(Host,User,Password,ssl_cipter,x509_issuer,x509_subject) 
	VALUE('localhost','user1',PASSWORD('123'),'','','');
flush privileges;

4. 删除用户

DROP语句

DROP USER 'username'@'hostname'[,'username'@'hostname'];

DELETE语句

DELETE FROM mysql.user WHERE Host='localhost' AND User='user2';
flush privileges;
--直接对user表操作，需要重新加载用户权限

5. 修改用户密码

修改root用户密码：mysqladmin命令修改root用户密码

mysqladmin -u username [-h hostname] -p password new_password
注意：该命令必须在C:\Documents and Settings\[当前Windows用户目录] 下运行

UPDATE mysql.user set Password = PASSWORD('new_password') WHERE User='username' and Host='hostname';
flush privileges;
//用root用户登录，再使用set语句
set password=PASSWORD('new_password');

利用root用户修改普通用户的密码

GRAND USAFE ON *.* TO 'username'@'localhost' IDENTIFIED BY [PASSWORD]'new_password';

UPDATE mysql.user set Password = PASSWORD('new_password') WHERE User='username' and Host='hostname';
flush privileges;
SET PASSWORD FOR 'username'@'hostname' = PASSWORD('new_password');

普通用户修改自己的密码

--普通用户登录到数据库中，再执行以下语句
set password=PASSWORD('new_password');

权限管理

MySQL的权限

MySQL中的权限信息存储在mysql数据库的user，db，host，tables_priv，column_priv和procs_priv表中

常用权限：

1. CREATE和DROP权限：创建数据库，表，索引，或者删除它们
2. INSERT，DELETE，UPDATE，SELECT权限：对数据库中的表增删改查
3. INDEX权限：可以创建或者删除索引，适用于所有的表
4. ALTER权限：可以用于修改表的结构或者重命名表
5. GRANT权限：允许为其他用户授权，可用于数据库和表
6. FILE权限：被赋予该权限的用户能读写MySQL服务器上的任何文件

授予权限

GRANT privileges [(columns)][,privileges[(columns)]] ON database.table 'username'@'hostname' [IDENTIFIED BY [PASSWORD]'password'] [,'username'@'hostname' [IDENTIFIED BY [PASSWORD]'password']]...[WITH with_option [with_option]...]

with_option：GRAND OPTION：将自己权限授予其他用户
MAX_QUERIES_PER_HOUR count：设置每小时最多可执行多少次（count）查询
MAX_UPDATES_PER_HOUR count：设置每小时最多可以执行多少次更新
MAX_CONNECTIONS_PER_HOUR count：设置每小时最大的连接数量
MAX_USER_CONNECTIONS：设置每个用户最多可以同时建立连接的数量

例一：使用GRANT语句创建一个新的用户，用户名为user4、密码为123，user4用户对所有数据库有INSERT、SELECT权限。并使用WITH GRAND OPTION
grant insert,select on *.* to 'user4'@'localhost' identified by '123' with grant option;

查看权限

SHOW GRANTS FOR 'username'@'hostname';

例一：查看root用户的权限
show grants for 'root'@'localhost';

收回权限

REVOKE privileges [columns] [,privileges [columns]] ON database.table FROM 'username'@'hostname' [,'username'@'hostname']...

--例一：收回user4用户的INSERT权限
revoke insert on *.* from 'user4'@'localhost';

收回所有权限

REVOKE ALL PRIVILEGES,GRANT OPTION FROM 'username'@'hostname' [,'username'@'hostname']...

--例二：收回user4用户的所有权限
revoke all privileges,grant option from 'user4'@'localhost';