Mysql数据库--自学笔记--3
1. 优化用户认证和密码
select user,host,password from mysql.user;
use mysql;
delete from user where user='';
delete from mysql.user where user='root' and host='::1';
use mysql;update user set password='xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx' where host='127.0.0.1';
2. 用户授权
语法：grant [权限] on [*.* dbname.* t] to 'user'@'ip or hostname' identified by 'password' [with grant options]
flush privileges;
3. 字符集问题
手工指定客户端的字符集
mysql -u root -p'112233' --default-character-set=charset
相当于在mysql客户端连接成功后执行：set names charset;
mysql> show global variables like '%char%';
+--------------------------+----------------------------------+
| Variable_name | Value |
+--------------------------+----------------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | utf8 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | utf8 |
| character_set_system | utf8 |
| character_sets_dir | /usr/local/mysql/share/charsets/ |
+--------------------------+----------------------------------+
修改my.cnf 的默认字符集
[mysql] 和 [client] [mysqld]
default-character_set =utf8 character_set_server = utf8
查看所有可用字符集的命令：
mysql> show character set;
+----------+-----------------------------+---------------------+--------+
| Charset | Description | Default collation | Maxlen |
+----------+-----------------------------+---------------------+--------+
|utf8 | UTF-8 Unicode | utf8_general_ci | 3 |
+----------+-----------------------------+---------------------+--------+
显示所有字符集和该字符集默认的校对规则
mysql> desc information_schema.character_sets;
+----------------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------------+-------------+------+-----+---------+-------+
| CHARACTER_SET_NAME | varchar(32) | NO | | | |
| DEFAULT_COLLATE_NAME | varchar(32) | NO | | | |
| DESCRIPTION | varchar(60) | NO | | | |
| MAXLEN | bigint(3) | NO | | 0 | |
+----------------------+-------------+------+-----+---------+-------+
查看相关字符集的校对规则
mysql> show collation like 'gbk%';
+----------------+---------+----+---------+----------+---------+
| Collation | Charset | Id | Default | Compiled | Sortlen |
+----------------+---------+----+---------+----------+---------+
| gbk_chinese_ci | gbk | 28 | Yes | Yes | 1 |
| gbk_bin | gbk | 87 | | Yes | 1 |
+----------------+---------+----+---------+----------+---------+
字符集修改流程步骤了：
字符集修改不能直接通过“alter database character set ***” 或者 "alter table tablename character set ***"
命令进行，这两个命令都没有更新已有的记录的字符集，而是对新创建的表或者记录生效。已有记录的字符集调整，
需要先将数据导出，经过适合的调整重新导入后才可以生效。
下面模式将latinl字符集的数据库修改成GBK字符集的过程；
a.导出表结构
mysqldump -uroot -p'112233' --default-character_set=gbk -d databasename >createtab.sql
b.手工修改createtab.sql中表结构定义中的字符集为新的字符集
c.确保记录不再更新，导出所有记录
mysqldump -uroot -p'112233' --quick --no-create-info --extended-insert --default-character_set=latin1 databasename >data.sql
d.打开data.sql ，将SET NAMES latin1 修改为 SET NAMES gbk
e.使用新的字符集创建新的数据库
create database databasename default charset gbk;
f.创建表，执行createtab.sql
mysqldump -uroot -p'112233' databasename < createtab.sql
g.导入数据，执行data.sql
mysqldump -uroot -p'112233' databasename < data.sql
4. InnoDB简介和参数优化
索引：
select * from test_table where id = v1 and name = v2;
select * from test_table where name = v2 order by id ;
表结构：
show dabatabases;
show dbname;
show create table user;
create table t1 (id int, name char(100), age samllint, primary key(id)) engine=innodb;
show create table t1;
alter table t1 add index test_index(name);
show table status like '%user%'\G
5. 升级MySQL版本
方法一：
6. DDL 语句--->对表的基本操作
创建表
create table emp (ename varchar(10),hiredate date,sal decimal(10,2),deptno int(2));
修改表emp的ename字段定义,将varchar(10) 改为varchar(20)
desc emp
alter table emp modify ename varchar(20);
增加字段age 类型为 int(3)
alter table emp add column age int(3);
删除字段age
alter table emp drop column age;
修改age改名为age1 。同时修改字段类型为int(4)
alter table emp change age age1 int(4);
mysql> desc emp;
+----------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+---------------+------+-----+---------+-------+
| ename | varchar(10) | YES | | NULL | |
| hiredate | date | YES | | NULL | |
| sal | decimal(10,2) | YES | | NULL | |
| deptno | int(2) | YES | | NULL | |
| age1 | int(4) | YES | | NULL | |
+----------+---------------+------+-----+---------+-------+
新增加的字段birthday 加在 ename 之后
alter table emp add birthday after ename;
修改字段age，将它放在最前面。
alter table emp modify age int(3) first;
修改表emp改名为emp1
alter table emp rename emp1;
7. DML 语句--->对表的基本操作
插入记录
insert into emp (ename,hiredate,sal,deptno) values('zzx1','2000-01-01','2000',1);
insert into emp values('zzx2','2001-11-21','2440',2);
insert into emp values('lisa','2011-10-11','3000',3);
mysql> select * from emp;
+-------+------------+---------+--------+
| ename | hiredate | sal | deptno |
+-------+------------+---------+--------+
| zzx1 | 2000-01-01 | 2000.00 | 1 |
| zzx2 | 2001-11-21 | 2440.00 | 2 |
+-------+------------+---------+--------+
将表 emp 中 ename 为‘lisa’ 的薪水（sal）从3000改为4000；
update emp set sal=4000 where ename='lisa';
同时更新表emp 中的字段sal 和表 dept 中的字段deptname
update emp a,dept b, set a.sal=a.sal*b.deptno,b.deptname=a.ename where a.deptno=b.deptno
删除‘lisa’ 的全部记录
delete from emp where='lisa';
同时删除表emp和dept中deptno为3的记录
delete a,b from emp a,dept b,where a.deptno=b.deptno and a.deptno=3;
多条语句一次性插入
insert into dept values(5,'dept5'),(6,'dept6')(3,'dept3');
条件查询
select * from emp where deptno=1;
select * from emp where deptno=1 and sal<3000;
排序和限制
select * from emp order by sal;
select * from emp order by sal limit 3;
select * from emp order by sal limit 1,3;
统计emp表中公司的总人数
select count(*) from emp;
统计各个部门的人数
select deptno,count(*) from emp group by deptno;
统计各个部门的人数,同时统计总人数
select deptno,count(*) from emp group by deptno with rollup;
统计总额，最高，最低薪水
select sum(sal),max(sal),min(sal) from emp;
连表emp,dept查询 ename,deptname
select ename,deptname from emp,dept where emp.deptno=dept.deptno;
select ename,deptname form emp left join dept on emp.deptno=dept.deptno;
子查询
select * from emp where deptno in(select deptno from dept);
如果子查询记录数唯一，可以用= 代替in
select * from emp where deptno in(select deptno from dept limit 1);
子查询转化为表连接
select emp.* from emp,dept where emp.deptno=dept.deptno;
记录联合
select deptno from emp
union all
select deptno from dept;
去掉重复的数据
select deptno from emp
union
select deptno from dept;
8. DCL 语句--->对表的基本操作
语法：grant [权限] on [*.* dbname.* t] to 'user'@'ip or hostname' identified by 'password' [with grant options]
flush privileges;
回收权限：revoke insert,update on *.* from 'user'@'ip or hostname';
9. 索引的设计和使用
为city 表创建10个字节的前缀索引
create index cityname on city (city(10));
如果以city为条件进行查询，可以发现索引cityname 被使用：
explain select * from city where city = 'fuzhou' \G
索引的删除：语法：drop index index_name on table_name;
drop index cityname on city;
查看表的索引
show index from table;
10. MySQL中的常用工具
查看一下当前连接用户
select current_user();
mysql -uroot -p'112233' -f -v --show-warnings <test.sql
-f 强制执行SQL
-v 显示更多信息
--show-warnings 显示警告信息
11. 日志管理工具 mysqlbinlog
语法：mysqlbinlog [options] log-files1 log-files2
option有很多的参数，常用如下
-d 指定数据库名称，只列出指定的数据库相关的操作
mysqlbinlog localhost-bin.000033 -d test
-o 忽略掉日志中的前n行命令
mysqlbinlog localhost-bin.000033 -o 3
-r 将输出的文本格式日志输出到指定文件
mysqlbinlog localhost-bin.000033 -o 3 -r resultfile
-s 显示简单格式，忽略掉一些信息
mysqlbinlog localhost-bin.000033 -0 -3 -s
--set-charset=char-name 在输出文本格式时，在文件第一行加上set naems char-name ,这个选项在某些情况下装数据时非常有用
--start-datetime=name --stop-datetime=name 指定日期间内的所有日志
mysqlbinlog localhost-bin.000033 --start-datetime= '2018/01/28 06:30:25' --stop-datetime= '2018/01/31 22:20:21'
--start-position= --stop-position= 指定位置间内的所有日志
mysqlbinlog localhost-bin.000033 --start-position=21 --stop-position=130 >hls.sql
mysql < hls.sql
12. MyISAM引擎的表维护工具 mysqlcheck
语法： mysqlcheck [OPTIONS] database [tables]
mysqlcheck [OPTIONS] --databases DB1 [DB2 DB3...]
mysqlcheck [OPTIONS] --all-databases
option中有如下参数
-c 检查表
-r 修复表
-a 分析表
-o 优化表
13. mysqlimport (数据导入工具)
14. 错误代码查看工具 perror
[root@localhost ~]# perror 30 60
OS error code 30: Read-only file system
OS error code 60: Device not a stream
15. mysql语句中----删除表数据drop、truncate和delete的用法
程度从强到弱
1、drop table tb
drop将表格直接删除，没有办法找回
2、truncate (table) tb
删除表中的所有数据，不能与where一起使用
3、delete from tb (where)
删除表中的数据(可制定某一行)
区别：truncate和delete的区别
1、事务：truncate是不可以rollback的，但是delete是可以rollback的；
原因：truncate删除整表数据(ddl语句,隐式提交)，delete是一行一行的删除，可以rollback
2、效果：truncate删除后将重新水平线和索引(id从零开始) ,delete不会删除索引
3、 truncate 不能触发任何Delete触发器。
4、delete 删除可以返回行数
16. MySQL日志
--log-error=filename 保存错误日志的路径
二进制日志格式：STATEMENT ROW MIXED --> 一般都是用MIXED 模式
日志的删除：
1. reset master;
2. purge master logs to 'localhost-bin.000006'
3. purge master logs before '2018-01-30 12:08:30'
4. 设置参数： --expire_logs_days=# 设置日志过期的天数。
执行mysqladmin flush-log ，删除#天前的日志
查看慢查询日志：
more localhost-slow.log
完成恢复数据：先把备份的数据导入数据库，再把bin-log 日志导进去 就可以完成恢复了
mysqlbinlog mysql-bin.000009 |mysql -uroot -p'112233'
基于时间点恢复：
1. 上午十点前发生了误操作，用备份和binlog将数据恢复到故障前
mysqlbinlog --stop-datetime= '2018/01/31 09:59:59' /usr/local/mysql/data/mysql-bin.000009 |mysql -uroot -p'112233'
2. 跳过故障时的时间点，继续执行后面的binlog ，完成恢复
mysqlbinlog --start-datetime= '2018/01/31 10:02:00' /usr/local/mysql/data/mysql-bin.000009 |mysql -uroot -p'112233'
基于位置恢复：
1. 先把时间段内的SQL语句导出来，查看，找到错误的位置号，分别是 36852 36855
mysqlbinlog --start-datetime= '2018/01/31 09:55:00' --stop-datetime= '2018/01/31 10:02:00' /usr/local/mysql/data/mysql-bin.000009 >/tmp/mysql_restore.sql
2. 恢复以前备份的文件
mysqlbinlog --stop-position="36852" /usr/local/mysql/data/mysql-bin.000009 |mysql -uroot -p'112233'
mysqlbinlog --start-position="36855" /usr/local/mysql/data/mysql-bin.000009 |mysql -uroot -p'112233'
17. 热备 热备工具 Xtrabackup
18. MySQL 权限和安全
查看用户的权限：
select * from user where user='root' and host='localhost' \G;
案例；创建新用户z2 ，可以从任何ip进行连接，权限为对test1数据库里的所有对表进行select，update，insert，delete操作，初始密码为“112233”
grant select,update,insert,delete on test1.* to 'z2'@'%' indentified by '112233';
select * from user where user='z2' and host='%'\G
select * from db where user='z2' and host='%'\G
查看账号的权限
show grants for z2@localhost;
show grants for z2;
删除用户
drop user z2@localhost;
以root用户启动的MySql，任何只要有FILE权限的用户都可以在任意目录写出文件，这样对操作系统的安全将造成严重隐患
select * from user outfile '/user.txt';
system more /user.txt
防止DNS欺骗
如果指定了域名，那就会带来如下安全隐患：如果对应的IP地址被修改，则数据库就会被恶意的ip地址进行访问，导致安全隐患
案例：
grant select on test.* to z2@test_hostname;
vim /etc/hosts
192.168.52.21 test_hostname
mysql -h 192.168.52.21 -p'112233' -uz2 -p
vim /etc/hosts
28.1.22.33 test_hostname
只授予账号必须的权限。不要用all privileges 权限
除了root外，任何用户不应该有mysql库 user表的存取权限
出现如下结果：
update user set password=password('abc') where user='root' and host='localhost';
上面的命令直接把root用户密码改了。导致一些安全隐患出现。
不要把FILE PROCESS SUPER 权限授予管理员外的账号
FILe ：
use mysql
create table t1 (name varchar(500));
load data infile '/etc/passwd' into table t1;
select * from t1;
PROCESS :
这个权限执行 show processlist 查看当前所有用户执行的查询的明文的文本，包括设定或者改变密码。但是如果一个普通用户看到了管理员执行的命令那就麻烦了。
SUPER ：
这个权限能执行kill命令，终止其他用户进程。
show processlist;
kill root; #把root进程杀死了。那就问题大了、
LOAD DATA LOCAL 带来的安全问题
LOAD DATA 默认读的是服务器上的文件，但是加上LOCAL参数后，可以将本地具有访问权限的文件加载到数据库中。
1. 可以任意加载本地文件到数据库。
2.
解决方法：
可以用 --local-infile=0 选项启动mysql 从服务端禁用所有的 LOAD DATA LOCAL 命令
DORP TABLE 命令并不收回以前的相关访问权限
DORP 表的时候，其他的用户对此表的权限并没有回收，这样导致重新创建同名的表时，以前的其他用户对此表的权限会自动赋予，
进而产生权限外流。因此，在删除表的同时，要同时收回其他用户的在此表的相应权限。
使用ssl
给所有的用户加上访问ip限制
REVOKE 命令的漏洞。
这是mysql权限机制的问题造成的隐患。在一个数据库上多次赋予权限，权限会自动的合并，但是在多个数据库上多次赋予权限，每个数据库
都会认为是单独的一组权限，必须在此数据库上用revoke 命令单独进行权限收回，而 revoke all privileges on *.* 并不会替用户自动完成这个功能
安全设置选项
19. MySql 常见问题和应用技巧
1. 忘记密码
详情看 MySQL学习笔记--第一次总结
2. MyISAM 存储引擎的表损坏
myisamchk -r -0 table_name
3. 数据目录磁盘空间不足
a. MyISAM
b. InnoDB
4. DNS方向解析的问题
5. mysl.sock 丢失后如何连接数据库
用tcp 连接：
mysql --protocol=tcp -uroot -p'112233' -P 3307 -h 127.0.0.1
6. 同一台服务器运行多个MySql数据库
7. 客户端怎么内网访问内网数据库
#####################################架构篇############################################################
20. MySQL 复制
posted on 2018-11-28 10:28 huanglinsheng 阅读(133) 评论(0) 编辑 收藏 举报
