mysql操作

# mysql操作
## 导出
### 数据库
```
mysqldump -u 用户名 -p密码 --default-character-set=utf8 数据库名 > 导出的文件名
```

### 导出数据库中的一张表
```
mysqldump -u 用户名 -p密码 数据库名 表名> 导出的文件名
```

### 以CSV格式导出
```
SELECT * FROM mytable
INTO OUTFILE '/tmp/mytable.csv'
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n';
```

### 仅导出数据库中的表结构
```
mysqldump -u wcnc -p -d –add-drop-table smgp_apps_wcnc >d:wcnc_db.sql
```
`-d` 没有数据  
`–add-drop-table` 在每个create语句之前增加一个drop table

## 导入
### 使用source
```
进入mysql数据库控制台，
如mysql -u root -p
mysql>use 数据库
然后使用source命令，后面参数为脚本文件(如这里用到的.sql)
mysql>source wcnc_db.sql
```

### 使用mysql
```
mysql -u username -p密码 -D dbname < filename.sql
```

## 表
### 查看表占物理空间大小
```
select concat(round(sum(data_length/1024/1024),2),'MB') as data from information_schema.tables where table_schema='DB_Name' and table_name='Table_Name';
```
## 用户权限管理
### 创建用户
```
CREATE USER user [IDENTIFIED BY [PASSWORD] 'password'] [, user [IDENTIFIED BY [PASSWORD] 'password']] ...

mysql>create user test1@'localhost' identified by '12345';    --test1只能本地访问
mysql>create user test2@'%' identified by '12345';              --test2可以远程访问
mysql>create user test3@’172.21.78.89’ identified by ‘12345’;  --test3仅从172.21.78.89上访问
mysql>create user test4@172.21.78.% identified by ‘12345’;   --test4可以从172.21.78.*网段上访问

-- 第二种 创建用户并同时授权
grant select,insert,update,delete on mydb.* to test1@localhost identified by "123456";

-- 第三种
insert into mysql.user(Host,User,Password) values("localhost","test3",password("12345"));
flush privileges;
```
如果创建用户的时候没有指定host, 并等同于允许所有主机连接

### 查看用户
```
select host, user from user;
```

### 修改用户密码
```
-- 第一种：mysqladmin -u用户名 -p旧密码 password 新密码
mysqladmin -uroot -pxxx -password 12345

-- 第二种：update
mysql>update mysql.user set password=password('123456') where User='test1' and Host='localhost';
mysql>flush privileges;

-- 第三种：
mysql> set password for test1@localhost = password('public12');
```

### 删除用户
```
-- 删除账户及权限
mysql>DROP USER test3@'172.21.78.89’;
```

### 授权
GRANT权限列表 ON 库名.表名TO 用户名@来源地址[IDENTIFIEDBY ‘密码‘]

使用GRANT语句的注意事项：

* 权限列表：用于列出授权使用的各种数据库操作，以逗号分隔。All表示所有权限；
* 库名.表名：用于指定授权操作的库和表的名称，其中可以使用“*”。如：使用“mydb.*”表示授权操作的对象为mydb库中的所有表。
* 用户名@来源地址：用于指定用户名称和允许访问的客户机地址，即谁能连接、能从哪里连接。来源的地址可以是域名、IP地址，还可以使用“%”通配符，表示某个区域或网段内的所有地址。如：“%.example.com”、“172.168.10.%”等
* IDENTIFIED BY：设置用户连接数据库时所使用的密码字符串。在新建用户时，若省略则用户的密码将为空。
* 使用GRANT语句授权的用户记录，会保存到mysql库的user、db、host、tables_priv等。

```
例：添加一个名为test1的数据库用户，并允许其从本机访问，对mydb库中的所有表具有查询权限，验证密码为“12345”。
mysql> GRANT select ON mydb.* TO 'test1'@'localhost' IDENTIFIED BY '12345';

例：增加一个test2用户，密码为12345，可以在任何主机上登录，并对所有数据库有查询，增加，修改和删除的功能。
mysql>grant select, insert, update, delete on *.* to test2@'%' identified by '12345'；

例：增加一个test3用户，密码为12345，只能在172.21.78.89上登录，并对mydb数据库有查询，增加，修改和删除的功能。
mysql>grant select, insert, update, delete on mydb.* to test3@172.21.78.89 identified by '12345';

例：授权用户test4拥有数据库mydb的所有权限
mysql>grant all privileges on mydb.* to test4@localhost identified by '12345';

例：创建一个只允许从本地登录的超级用户test5，并允许将权限赋予别的用户，密码为12345
mysql>GRANT ALL PRIVILEGES ON *.* TO test5@localhost IDENTIFIED BY '12345' WITH GRANT OPTION;

例：创建一个一般应用程序用户，并只需要SELECT, INSERT, UPDATE, DELETE, CREATE TEMPORARY TABLES等权限，如有存储过程还需要加上EXECUTE权限。指定登录网段172.21.78网段。
mysql>GRANT  USAGE,SELECT, INSERT, UPDATE, DELETE, SHOW VIEW ,CREATE TEMPORARY TABLES,EXECUTE ON mydb.* TO test6@'172.21.78.%' IDENTIFIED BY  '12345';

例：创建一个普通用户(仅有查询权限)
mysql>GRANT USAGE,SELECT ON mydb.* TO test7@'172.21.78.%' IDENTIFIED BY  '12345';

```

### 查看权限
```
mysql> show grants for username;
mysql> show grants for test1;
mysql> show grants for test5@172.21.78.89;
```

### 撤销权限
REVOKE 权限列表 ON 数据库名.表名 FROM 用户名@来源地址
```
revoke all on *.* from test5@172.21.78.89;
```

### 刷新权限
```
mysql>FLUSH PRIVILEGES;
```
改完权限后别忘记刷新

## binlog
### 设置过期时间
```
set global binlog_expire_logs_seconds=604800;
```

### 清除binlog
```
#将bin.000055之前的binlog清掉:
mysql>purge binary logs to 'bin.000055';

#将指定时间之前的binlog清掉:
mysql>purge binary logs before '2017-05-01 13:09:51';
```

阿川CH