mysql数据库三：

MySQL用户和权限管理   （show processlist :查看mysql的进程 kill+进程编号：杀掉进程）常用

用户管理 ；

查看用户账号及密码：

select user,password,host from mysql.user;

创建用户；

create user 'lv'@'192.168.60.2'   identified by 'centos'; （指定用户名并指定在哪台主机登陆，设置密码为centos）此用户只能在192.168.60.2 的主机登陆且用户只能为lv 。

连接：mysql  -ulv -p密码 -h192.168.60.4（数据库主机的IP地址）

用户重命名；

RENAME USER old_user_name@host TO new_user_name @host

rename user wang@192.168.60.5 to lv@192.168.60.8;

删除用户；

DROP USER 'USERNAME'@'HOST'

drop user 'lv'@'192.168.60.5';

示例：删除默认的空用户 (所谓的匿名账号)

DROP USER   '  '@'localhost'

新建的用户虽然可以登陆数据库，但无法查看库中的表因为没有权限。

FLUSH PRIVILEGES  （刷新数据）

修改密码；

mysql>SET PASSWORD FOR 'user'@'host' = PASSWORD(‘new_password');

set password for lv@'192.168.60.8'=password('msd');

mysqladmin -u root –poldpass password ‘newpass‘  （也可以更改旧口令）

或者使用update更改表的方式来修改密码；

忘记管理员密码的解决办法：

vim /etc/my.cnf  (编辑此文件在下面的标题下添加下面一行)

[mysqld]

skip-grant-tables  （意思是跳过授权）

systemctl restart mariadb  （重新启动服务）

就可以登陆了，但仅限这一次登陆后要修改密码。

进入到use mysql库里查看用户表：select user,password,host from user;

使用UPDATE命令修改管理员密码

 

MySQL权限管理

权限类别：

数据库级别

表级别

字段级别 管理类 程序类
授权；

1 . 直接授权：

grant all on hellodb.* to lv@'192.168.60.%';

all 代表所有权限；192.168.60.% ：这样写代表，192.168.60这个网段的所有主机，hellodb.* 为只对hellodb下的表有操作权限，但对其他库没有操作权限。

可以授权单独的某一项操作命令：

grant select，insert  on hellodb.* to lv@'192.168.60.2';  (lv用户对hellodb库下的所有表，只有查询和插入的权限)

grant select，insert on hellodb.students to lv@'192.168.60.2'

(对students单个表授权操作，对hellodb库下的其他表没有权限)

grant select（name,id)  on hellodb.students to lv@'192.168.60.2' 

(针对特定表的某个列进行查询)

2 . 连创建用户带授权：

grant select,insert on hellodb.* to mage@'192.168.33.3' identified by 'centos';

（权限设定）                                                                                            （设定密码）

3 . 回收授权：REVOKE

revoke delete on hellodb.* from lv@'192.168.60.%'; （回收某一项权限；delete:删除权限）

revoke all on hellodb.* from lv@'192.168.60.%';         （回收所有的权限；all；代表剩余所有的权限）

4 . 查看已授权的用户所拥有的权限：

show grants;（查看本机root用户的权限）

show grants for lv@'192.168.60.%' ； （查看某个用户所拥有的权限）

 

注意：

MariaDB服务进程启动时会读取mysql库中所有授权表至内存 (1) GRANT或REVOKE等执行权限操作会保存于系统表中，MariaDB的服务进程通常会自 动重读授权表，使之生效

(2) 对于不能够或不能及时重读授权表的命令，可手动让MariaDB的服务进程重读授权 表：

mysql> FLUSH PRIVILEGES;

 

 

索引；

索引是特殊数据结构：定义在查找时作为查找条件的字段

优点：提高查询速度，

缺点：占用额外空间，影响插入速度

索引类型： 

聚簇（集）索引、非聚簇索引：数据是否与索引存储在一起 

主键索引、辅助索引 

稠密索引、稀疏索引：是否索引了每一个数据项 

B+ TREE、HASH、R TREE 

简单索引、组合索引

左前缀索引：取前面的字符做索引

覆盖索引：从索引中即可取出要查询的数据，性能高

 

B+TREE索引 ：（常用的索引）

B+ Tree索引：顺序存储，每一个叶子节点到根结点的距离是相同的；左 前缀索引，适合查询范围类的数据

全值匹配：精确所有索引列，如：姓wang，名xiaochun，年龄30

匹配最左前缀：即只使用索引的第一列，如：姓wang

匹配列前缀：只匹配一列值开头部分，如：姓以w开头的

匹配范围值：如：姓ma和姓wang之间

精确匹配某一列并范围匹配另一列：如：姓wang,名以x开头的 只访问索引的查询

B-Tree索引的限制：

如果不从最左列开始，则无法使用索引：如：查找名为xiaochun，或姓为g结 尾 

不能跳过索引中的列：如：查找姓wang,年龄30的，只能使用索引第一列

如果查询中某个列是为范围查询，那么其右侧的列都无法再使用索引：如： 姓wang,名x%,年龄30，只能利用姓和名上面的索引 

 

Hash索引

Hash索引：基于哈希表实现，只有精确匹配索引中的所有列的查询才有效，索 引自身只存储索引列对应的哈希值和数据指针，索引结构紧凑，查询性能好 

只支持等值比较查询，包括=, IN(), <=>

 

管理索引 ；

1 . 创建索引

create index index_age on students(age); （对students表添加以age列的索引；index_age;是定义索引的新名称）

2 . 删除索引

drop index index_age on students; （对students表删除索引，索引的名字为index_age ）

3 . 查看索引

show indexes from students\G;（查看此表students中的索引）Column_name为索引的称号前；

4 .优化表空间

OPTIMIZE TABLE tb_name

 

并发控制 ；

锁的含义：当一个数据库服务器的某一个表同时被多个用户修改表数据时，存储引擎会自动对此表进行加锁；其他用户只能对此表进行读，而且读的是未被修改的数据。只有数据修改完成，同步之后，才可以看到修改后的数据。

1 .锁粒度：

表级锁 （myisun只支持表级的加锁）

行级锁  （innodb支持行级锁）

读锁：共享锁，只读不可写，多个读互不阻塞， 自己也不可以对其进行修改。

写锁：独占锁,排它锁，一个写锁会阻塞其它读和它锁 ，自己可以读，写

实现

存储引擎：自行实现其锁策略和锁粒度

服务器级：实现了锁，表级锁；用户可显式请求

显示使用锁 ：

 

添加锁；

lock tables students write;  （添加写锁）

lock tables students read;  (对某个表添加读锁)

解锁：

unlock tables;

 

 

 

事务和日志

事务Transactions：一组SQL语句，或一个独立工作单元的一组 SQL语言

事务日志：记录事务信息，实现undo,redo等故障恢复功能

ACID特性：

A：atomicity原子性；整个事务中的所有操作要么全部成功执行，要么全部失 败后回滚 

C：consistency一致性；数据库总是从一个一致性状态转换为另一个一致性状 态 

I：Isolation隔离性；一个事务所做出的操作在提交之前，是不能为其它事务 所见；隔离有多种隔离级别，实现并发 

D：durability持久性；一旦事务提交，其所做的修改会永久保存于数据库中 ：（如果没有提交的话则不会写入磁盘的。）

启动事务：START TRANSACTION （start transaction）

结束事务：

(1) COMMIT：提交

(2) ROLLBACK: 回滚 (事物开启后如果没提交之前是可以撤销之前所做的操作的)

注意：只有事务型存储引擎方能支持此类操作 （既innodb支持myision是不支持的）

隐性事物：（mysql默认使用）每一个select语句为一个事物，每一个事物提交执行一次

显性事物：有明显的开始结束标志，中间多个select语句写完一起执行。

show variables like 'autocommit';  （查看自动提交事物处于什么状态）

set global autocommit=0 (全局) 但在别的终端使用时，需要重新登陆。

set autocommit=0   （仅是再此次开启的mysql生效）在mysql里进行设置。

永久开启需要写进配置文件：vim /etc/my.cnf

[mysqld]
autocommit=0  (将自动提交事物关闭)
只要不提交
rollbock(撤销操作)；此撤销操作仅局限对于表的的增删改查操作；如果删除表和库以及新建表和库都无法撤销操作的
commit (提交操作)

 

单独开启事物：

start transaction；  (开启临时事物)

………

……….（中间为SQL语句）

……..

…….

commit； (提交操作)/rollback； (撤销操作)

 

事务隔离级别

READ UNCOMMITTED 可读取到未提交数据，产生脏读 

READ COMMITTED 可读提交数据，但未提交数据不可读，产生不可重 复读，即可读取到多个提交数据，导致每次读取数据不一致 

REPEATABLE READ 可重复读，多次读取数据都一致，产生幻读，即读 取过程中，即使有其它提交的事务修改数据，仍只能读取到未修改前 的旧数据。此为MySQL默认设置 ；直到自己的事物提交后再次查看数据，才能看到已经更新的数据了。

SERIALIZABILE 可串行化，未提交的读事务阻塞修改事务，或者未提交 的修改事务阻塞读事务和修改事物。导致并发性能差

 

系统默认隔离状态下

死锁状态；1用户更改a表；2用户更改b表，都没有提交时；1用户再去更改b表的同一行，2用户再去更改1表同一行；此状态进入死锁状态。

如果进入到死锁状态系统会自动杀掉等待时间短的事物

查看mysql数据库的执行的那些进程：show processlist :
如果事物一直未提交，影响其他用户访问时；
kill +进程编号 （杀掉进程）
杀掉没提交的事物，修改的数据不会生效。

 

 

日志

事务日志：transaction log 

错误日志：error log 

查询日志：query log 

慢查询日志：slow query log 

二进制日志：binary log 

中继日志：reley log

 

1 .  事物日志
事物日志写入类型是追加的，不是动态更新的，只有退出mysql数据库时才写入。只是记录本机的操作。
事务日志和数据库文件一般不存放到一张磁盘里的。
redo log （事物已经做完了，没写到磁盘里，断电了，重启后会自动再次执行已经做完的事物，并写入到磁盘里去）
undo log (没提交的事物，断电重启后会撤销未执行的事物)

 

show variables like '%innodb_log%';  （查看事物日志的相关变量的信息）

事物日志默认5M大小，2个文件；当第一个文件写满了，去第二个文件里写，第二个文件写满后，回去覆盖第一个的文件。（存放的路径/var/lib/mysql/ib_logfile0;ib_logfile1）
生产中将文件的容量调大，文件个数加多
需要修改配置文件：vim /etc/my.cnf

[mysqld]
innodb_log_files_in_group=3 (原有日志文件存在可能不行，需要将原有的文件移走或者删除，就可以再生成了)  增加文件个数

或者：innodb_log_files_in_group=3
innodb_log_group_home_dir=/data/msq/ （重新指定存放的目录）

重启服务：如果起不来可能是因为selinux或者防火墙：getenforce(查看selinux的当前状态)

setenforce=0 (临时关闭selinux)

 

2 . 错误日志：

log-error=/var/log/mariadb/mariadb.log  (默认路径)
在配置文件里 /etc/my.cnf可以修改路径：
修改路径的文件存放错误日志，要确保此文件对mysql有写的权限。

重启服务：如果起不来可能是因为selinux或者防火墙：getenforce(查看selinux的当前状态)
setenforce=0 (临时关闭selinux)

 

3  .通用日志：（默认不启用）
记录所有用户执行命令的记录（包括远程用户的主机登陆本机的数据库）

查看当前的状态：show variables like 'general_log%';
启用： set general_log=ON|OFF (非全局改)

set  global general_log=ON|OFF (全局更改)

记录日志的文件：/var/lib/mysql/centos7.log (当开启此服务时此文件才生成)

show variables like 'log_output'   (查看此时此日志是以什么方式来存放的)

set global log_output="table"  （将此日志文件以表的方式来存放，易于读看）

记录以表形式的文件路径： 在mysql库里有张general_log的一张表
(一般不启用，数据太大会占用磁盘空间)

 

 

4  .  慢查询记录日志

show variables like 'slow_query_log';（查看慢查询的当前状态）
set  global  slow_query_log=ON|OFF （开启或者关闭慢查询记录）

show variables like 'long_qu%'(查看当前慢查询的阈值)
long_query_time=N (记录慢查询的阈值，默认10s;超过10s的查询记录就会被记录到慢查询日志里)

slow_query_log_file=HOSTNAME-slow.log

当发现一个SQL语句执行超过10s会被记录到慢查询日志里去。

查询时没有使用索引的查询记录也会被记录到慢查询日志里让你可以再次优化它。

慢查询日志的文件路径：/var/lib/mysql/centos7-slow.log  （此文件也是在开启慢查询是生成的文件）

profiling=on|off (默认关闭的)

show variables like 'profiling';  （查看此服务的状态）

set profiling=on; （临时开启此服务）

show profiles (查看执行的进程信息)
show profile for qurey 10023 （查看单个进程哪个部分花费的时间）

 

5  .  中继日志：relay log 主从复制架构中，从服务器用于保存从主服务器的二进制日志中读取到的事件

 

 

6  . 二进制日志：注意：建议二进制日志和数据文件分开存放
记录对数据库操作的命令；（SQL语句）
记录已提交的事物，未完成的事物不会记录到二进制文件日志里
通过‘重放’来对来生成数据副本

a   .二进制日志记录三种格式 ;

基于“语句”记录：statement，记录语句，默认模式

基于“行”记录：row，记录数据，日志量较大  (推荐使用)

混合模式：mixed, 让系统自行判定该基于哪种方式进行

查看现在基于什么记录：show variables like '%binlog_format%';

更改记录的模式：

set binlog_format=row

set binlog_format=mixed

b  .二进制日志文件的构成
有两类文件；
日志文件：mysql-bin.000001
索引文件：mysql-bin.index

c  . 二进制文件的启用

set sql_log_bin=ON|OFF：是否记录二进制日志，默认ON启用(select @@sql_log_bin;查看当前状态)

（0代表禁用 ；1代表启用）

log_bin=/PATH/BIN_LOG_FILE：指定文件位置；默认OFF，表示不启用二进制 日志功能(select @@log_bin;查看当前状态  ；0代表禁用；1代表启用)

此项不支持命令行的修改，需要到配置文件里去修改。

vim /etc/my.cnf

[mysqld]

log_bin=/data/msq/mysql-bin   (此为指定路径，mysql-bin为指定的前缀)

重启服务后就会在/data/msq/mysql-bin.000001(生成此文件)并生成mysql-bin.index（索引文件）
上述两项都开启才可

d   二进制文件里的变量：

max_binlog_size=1073741824：单个二进制日志文件的最大体积，到达最大值 会自动滚动，自动生成第二个文件，默认为1G

(show variables like 'max_binlog_size';查看现在设置的大小)

（set global max_binlog_size=222222;设定文件的大小，但最大只能为1G不能设定超过1G的。（只在全局）

 

sync_binlog=1|0：设定是否启动二进制日志即时同步磁盘功能，默认0，由操 作系统负责同步日志到磁盘

（ show variables like 'sync_binlog';查看此时的状态值；set global sync_binlog=1;更改设置 ）

 

expire_logs_days=N：二进制日志可以自动删除的天数。 默认为0，即不自动 删除

（show variables like 'expire_logs%'，查看此时状态值；set global expire_logs_days=3；设定自动删除的天数）

 

e  二进制文件的查看和使用

show master logs;  （查看二进制文件的个数和大小）

show master status;（查看当前二进制文件处于什么状态，里面的数值就是它改变的位置值）

show binlog events in 'mysql-bin.000001'; （查看此二进制文件里记录的事件）

show binlog events in 'mariadb-bin.000001' from 6516 limit 2,3

（limit 2,3 从6516的位置开始读取跳过开始的2个位置，读取之后的3个位置的）

专业的二进制查看工具：

mysqlbinlog mysql-bin.000001  -v （此命令需要在linux命令行里操作，直接打开二进制文件）

at245…………at336(中间为一个事物；at245为一个事物的开始标记；at336为此事物的结束标记)

mysqlbinlog mysql-bin.000001 > sss.sql（将此二进制文件导入到一个文件里，可供使用）

mysql < sss.sql  (重新走一遍二进制的脚本之后在此脚本记录的更改的内容就会还原了)

mysqlbinlog mysql-bin.000001 -v –start-position=309 –stop-position=416;  （具体查看某一位置的内容）

–start-datetime="2018-01-30 20:30:10" –stop-datetime="2018-01-30 20:35:22"  （指定时间段）

 

f . 清除指定二进制日志：

先进入到数据库里：

show master logs;  （查看二进制文件的个数和大小）

purge binary logs to 'mysql-bin.000002'; （删除到002的二进制文件不包括002文件）

PURGE BINARY LOGS BEFORE '2017-01-23';   (删除此日期之前的二进制日志)

PURGE BINARY LOGS BEFORE '2017-03-22 09:25:30'; （删除此日期之前的二进制日志）

删除所有二进制日志，index文件重新记数 ：

reset master;（删除所有二进制日志文件，重新生成一号文件开始记录）

切换日志文件： FLUSH LOGS；（原来的二进制日志文件不在记录，重新生成一个二进制文件记录内容）

（此命令在数据库里执行）