study-23: Mysql基本命令实践

1，Mysql数据库用户安全策略介绍

安装mysql数据库后，默认的管理员root密码为空，这很不安全。

1）增加system并提升为超级管理员，即和root等价的用户，只是名字不同

mysql > grant all privileges on *.* to system@'localhost' identified by 'oldboy123' with grant option;

mysql > flush privileges;

2）除system用户外，删除所有mysql中的用户，包括root超级用户

mysql > delete from mysql.user where user != 'system';

2，创建指定字符集的数据库

create database example_utf8 character set utf8 collate utf8_general_ci;

create database example_gbk character set gbk collate gbk_chinese_ci; 

3，显示数据库

show databases;

show databases like '%xxx%'

查询当前选择的数据库:

select database()

查询登录用户：

select user()

4，删除数据库

drop database xxxx;

5，查询所有的表

show tables;

show tables from xxx库；

6，创建用户并授权

GRANT ALL ON xx库.* TO ‘xxx’@'localhost' IDENTIFIED BY  'xxxxx'

查看授权：
show grants for 'xxx'@'xxx'

7，局域网内主机远程链接数据库

grant all on *.* to  test@'10.0.0.%' identified by '123456';

grant all on *.* to  test@'10.0.0.0/255.255.255.0' identified by '123456';

8，收回 INSERT 权限

REVOKE INSERT ON xxx数据库.* FROM 'jeffrey'@'localhost';

权限列表：

SELECT , INSERT , UPDATE , DELETE , CREATE , DROP , REFERENCES , INDEX , ALTER , CREATE TEMPORARY TABLES,

LOCK TABLES , EXECUTE , CREATE VIEW , SHOW VIEW , CREATE ROUTING , ALTER ROUTING , EVENT , TRIGGER 

#在授权时，可以授权用户最小的满足业务需求的权限，而不是一味的授权 “ALL PRIVILEGES”

9，企业生产环境如何授权用户

1)，博客、CMS等产品的数据库安装前

对web链接用户授权尽量采用最小化原则，安装一般是 select , insert ,update , delete , create, drop 权限

2)，生成数据库表后，要收回create ，drop授权

revoke insert on blog.* from xxx@xxxx;

revoke drop on blog.* from xxx@xxxx;

3)，生产环境针对主库（写为主读为辅）用户的授权：

普通环境：

本机：lnmp，lamp环境数据库授权

grant all privileges on blog.* to blog@'localhost' identified by '123456';

应用服务器和数据库不在一个主机上的授权：

grant all privileges on blog.* to blog@'10.0.0.%' identified by '123456';

严格的授权：重视安全，忽视了方便：

grant select, insert, update, delete on blog.* to blog@'10.0.0.%' identified by '123456';

4),生产环境针对从库（只读）用户授权：

grant select on blog.* to blog@'10.0.0.%' identified by '123456';

10，建表

11，数据库全量备份

mysqldump -uroot -p123456 -B -S /data/3306/mysql.sock mysql > /opt/1.sql

12，数据库全量恢复

mysql -uroot -p123456 -S /data/3306/mysql.sock mysql < /opt/1.sql

13，如何避免乱码（执行 set names 是临时生效）:

1）mysql命令行下：set names utf8;

2）备份.sql文件中，指明：set names utf8;

3）导入时指定：mysql -uroot -p123456 --default-character-set=utf8  xx数据库名  <   xxxx.sql

4）my.cnf的配置文件：

[ client ] 

default-character-set=utif8;

[ mysqld ]

default-character-set=utif8;

# 与客户端字符集有变化关系的几个参数（默认和/etc/sysconfig/i18n系统字符集一致），执行set names 实际就是修改下面三个参数的默认值：

character_set_client

character_set_connection

character_set_results

（ mysql -uroot -p123456 -S /data/3306/mysql.sock -e "show variables like 'character_set%'"; ）

# 防止乱码：保证 客户端字符集 与 建库建表 的字符集一致

14，更改生产线上Mysql数据字符集的方案（例如从 latin1 修改为 utf8）：

1）库及表的语句导出，sed 批量修改表结构字符集为utf8   （只导出结构：mysqldump -uroot -p123456 -S /data/3306/mysql.sock -d 数据库名> /opt/111.sql）

2）导出所有数据 ( 只导出数据：mysqldump -uroot -p123456 -S /data/3306/mysql.sock -t mysql > /opt/111.sql )

3）修改mysql配置文件my.cnf中的[client] [mysqld] 字符集为utf8

4）删除原有的库表及数据

5）导入新的建库及建表的语句

6）导入mysql的所有数据