Topwo博客-mysql数据库的高级操作

您现在的位置是：Topwo博客>>Mysql文章

mysql数据库的高级操作

发布时间：2021-09-27作者：Topwo来源：原创点击：324

### 数据的备份
MySQL提供了一个mysqldump命令，它可以实现数据的备份
1、备份单个数据库
```sql
mysqldump -uusername -ppassword dbname [tbname1 [tbname2…]]>filename.sql
```
2、备份多个数据库
```sql
mysqldump –uusername –ppassword -–database dbname1 [dbname2 dbname3…]>filename.sql
```
3、备份所有数据库
```sql
mysqldump –uusername –ppassword -–all-databases>filename.sql
```
### 数据的还原
数据库中的数据遭到破坏时，可以通过备份好的数据文件对数据进行还原，这里所说的还原是指还原数据库中的数据，而库是不能被还原的。
```sql
mysql –uusername –ppassword [dbname] <filename.sql
```
1、首先需要使用DROP语句将数据库chapter08删除
```sql
DROP DATABASE chapter08;
```
2、由于库是不能被还原的，因此先要创建一个数据库chapter08
```sql
CREATE DATABASE chapter08;
```
3、使用mysql语句还原C:/backup目录下的chapter08_20140305.sql文件
```sql
mysql -uroot -pitcast chapter08 <C:/backup/chapter08_20140305.sql
```
4、为了验证数据已经还原成功，可以使用SELECT语句查询chapter08中的数据`SELECT * FROM student;`
### 用户管理
#### user表
user表是最重要的一个权限表，它记录了允许连接到服务器的账号信息以及一些全局级的权限信息。
user表中有42个字段，大致可分为4类用户列、权限列、安全列、资源控制列
### 创建普通用户
#### 1、使用GRANT语句创建用户，是创建用户最常用的方法
```sql
GRANT privileges ON database.table TO 'username'@'hostname' [IDENTIFIED BY 'PASSWORD'] [,'username'@'hostname [IDENTIFIED BY 'PASSWORD']] …
```
#### 2、使用CREATE USER语句创建用户
```sql
CREATE USER 'username'@'hostname' [IDENTIFIED BY 'PASSWORD'] [,'username'@'hostname'[IDENTIFIED BY 'PASSWORD']]…
```
#### 3、使用INSERT语句创建用户
```sql
INSERT INTO mysql.user(Host,User,Password,ssl_cipher、x509_issuer、x509_subject) VALUES('hostname','username',PASSWORD('password'),'','','');
```
### 删除普通用户
#### 1、使用DROP USER语句删除用户
```sql
DROP USER 'username'@'hostname'[,'username'@'hostname'];
```
#### 2、使用DELETE语句删除用户
```sql
DELETE FROM mysql.user WHERE Host='hostname' AND User='username';
```
### 修改用户密码
### root用户修改root用户密码
#### 1、使用mysqladmin命令修改
```sql
mysqladmin –u username [–h hostname] –p password new_password
```
#### 2、使用UPDATE语句修改
```sql
UPDATE mysql.user set Password=PASSWORD('new_password') WHERE User='username' and Host='hostname';
```
#### 3、使用SET语句修改
```sql
SET PASSWORD=PASSWORD('new_password');
```
#### root用户修改普通用户密码
#### 1、使用GRANT USAGE语句修改
```sql
GRANT USAGE ON *.* TO 'username'@'localhost' IDENTIFIED BY [PASSWORD]'new_password';
```
#### 2、使用UPDATE语句修改
```sql
UPDATE mysql.user set Password=PASSWORD('new_password') WHERE User='username' and Host='hostname';
```
#### 3、使用SET语句修改
```sql
SET PASSWORD=PASSWORD('new_password');
```
#### 普通用户修改密码
```sql
SET PASSWORD=PASSWORD('new_password');
```
### 如何解决root用户密码丢失问题
停止MySQL服务
```shell
net stop mysql
```
使用--skip-grant-tables启动MySQL服务
```shell
mysql --skip-qrant-tables
```
登录MySQL服务器
重新开启一个运行对话框，在运行对话框中登录MySQL服务器，具体命令如下：
```shell
mysql -u root
```
使用UPDATE语句设置root用户密码
```sql
UPDATE mysql.user SET Password=PASSWORD('itcast') WHERE User='root' AND Host='localhost';
```
加载权限表
```sql
FLUSH PRIVILEGES;
```
上述步骤执行完，可以使用EXIT或\q命令退出服务器，然后使用新密码重新登录。至此，便完成了root用户的密码设置。

### 权限管理
MySQL的权限
MySQL中的权限信息被存储在mysql数据库的user、db、host、tables_priv、column_priv和procs_priv表中，当MySQL启动时会自动加载这些权限信息，并将这些权限信息读取到内存中。
授予权限
我们之所以可以对数据进行增删改查的操作，是因为数据库中的用户拥有不同的权限，合理的授权可以保证数据库的安全。
```sql
GRANT privileges [(columns)][,privileges[(columns)]] ON database.table TO 'username'@'hostname' [IDENTIFIED BY [PASSWORD]'password'] [,'username'@'hostname' [IDENTIFIED BY [PASSWORD]'password']] … [WITH with_option [with_option]…]
```
privileges：表示权限类型。
columns：参数表示权限作用于某一列，该参数可以省略不写，此时权限作用于整个表，username：表示用户名。
hostname表示主机名。
IDENTIFIED BY：参数为用户设置密码。
PASSWORD参数为关键字.
password为用户的新密码。WITH关键字后面可以带有多个参数with_option，这个参数有五个取值。
GRANT OPTION：将自己的权限授予其他用户MAX_QUERIES_PER_HOUR count：设置每小时最多可以执行多少次（count）查询。
MAX_UPDATES_PER_HOUR count：设置每小时最多可以执行多少次更新。
MAX_CONNECTIONS_PER_HOUR count：设置每小时最大的连接数量。
lMAX_USER_CONNECTIONS：设置每个用户最多可以同时建立连接的数量。
### 查看权限
```sql
SHOW GRANTS FOR 'username'@'hostname';
```
### 收回权限
为了保证数据库的安全性，需要将用户不必要的权限收回
#### 1、收回用户指定权限
```sql
REVOKE privileges [columns][,privileges[(columns)]] ON database.table FROM 'username'@'hostname'[,'username'@'hostname'] …
```
#### 2、收回用户全部权限
```sql
REVOKE ALL PRIVILEGES,GRANT OPTION FROM  'username'@'hostname' [,'username'@'hostname'] …
```

上一篇：python获取执行脚本的所在目录总结

下一篇：Can't create handler inside thread that has not called Looper.prepare()解决办法