MySQL用户权限及二进制日志

谁践踏了优雅 2022-08-03 13:40 207阅读 0赞

用户和权限管理  
![这里写图片描述][20150808081514251]  
![这里写图片描述][20150808081523372]  
SUPER（服务器级别管理权限），可以用来终止其他会话或者更改服务器操作的方式  
例如:use CHANGE MASTER TO设定从服务器的主服务器 KILL or mysqladmin kill to kill threads belonging to other accounts  
PURGE BINARY LOGS,删除二进制日志configuration changes using SET GLOBAL to modify global system variables  
stored routines存储程序相关（存储过程，存储函数）  
例如：The CREATE ROUTINE privilege is needed to create stored routines (procedures and functions)  
file access on server host访问服务器文件  
The FILE privilege gives you permission to read and write files on the server host using the LOAD DATA INFILE and SELECT … INTO OUTFILE statements and the LOAD\_FILE() function. A user who has the FILE privilege can read any file in any database directory, and create new files in any directory where the MySQL server has write access. because the server can access any of those files.  
![这里写图片描述][20150808081600842]  
root创建新用户Jerry，默认只有USAGE权限  
![这里写图片描述][20150808081617689]  
以Jerry身份建立一个新会话进行测试是否有足够权限执行请求的操作  
![这里写图片描述][20150808081702076]  
root授权  
![这里写图片描述][20150808081711308]  
![这里写图片描述][20150808081748750]  
![这里写图片描述][20150808082001978]  
Jerry测试  
![这里写图片描述][20150808082049841]  
root取消授权  
![这里写图片描述][20150808082057835]  
Jerry没有权限  
![这里写图片描述][20150808082105153]  
如果忘记mysql管理员密码，可以通过以下方法重置密码  
先停止服务在启动脚本中添加两项  
![这里写图片描述][20150808082224838]  
![这里写图片描述][20150808082233191]  
之后启动服务，更新mysql中user表相关密码字段  
![这里写图片描述][20150808082242616]  
![这里写图片描述][20150808082250673]  
通过更新授权表方式直接修改其密码，而后移除此两个选项重启服务器  
![这里写图片描述][20150808082332703]  
MySQ日志之binary\_log  
记录任何可能引起数据库变化的操作，是实现MySQL从服务器复制及即时点还原的重要依据  
开启二进制日志，编辑/etc/my.cnf  
添加log-bin=mysql-bin到\[mysqld\]下  
![这里写图片描述][20150808082409163]  
my.conf 文件中的\[mysqld\]标签下的log\_bin指定日志文件，如果不提供文件名，mysql将自己产生缺省文件名。mysql会在文件名后面自动添加数字引，每次启动服务时，都会重新生成一个新的二进制文件。此外，使用log-bin-index可以指定索引文件；  
日志变量：  
log-bin=\{YES|NO\}是否启用二进制日志，如果为mysqld设定了–log-bin选项，则其值为ON，否则则为OFF。其仅用于显示是否启用了二进制日志，并不反应log-bin的设定值。作用范围为全局级别，属非动态变量。  
sql\_log\_bin=\{ON|OFF\} 用于控制二进制日志信息是否记录进日志文件。默认为ON，表示启用记录功能。用户可以在会话级别修改此变量的值，但其必须具有SUPER权限。作用范围为全局和会话级别，属动态变量。  
注：在实现数据恢复时应当暂时关闭sql\_log\_bin（=OFF）  
![这里写图片描述][20150808082453800]  
![这里写图片描述][20150808082502046]  
查看当前正在使用的二进制log file  
![这里写图片描述][20150808082651331]  
查看二进制日志事件  
![这里写图片描述][20150808082658088]  
开启了二进制日志，之后做的所有和修改数据表有关的操作都会被记录  
但是保存日志的格式是二进制不能被人类理解，要借助mysql自带的命令行工具mysqlbinlog才能读懂  
mysqlbinlog  
–start-datetime  
–stop-datetime  
–start-position  
–stop-position  
![这里写图片描述][20150808082740067]  
可以将输出的信息重定向到指定目录下的.sql文件，而后将此文件内容导入数据库以实现数据恢复  
![这里写图片描述][20150808082750242]  
实现二进制日志滚动  
![这里写图片描述][20150808082837639]  
二进制日志默认存放在数据存储目录下，每一次与数据相关的写操作都会引起二进制日志的写操作，两者都会产生IO,存放在同一块磁盘上会产生竞争，导致性能低下，避免由于数据文件所在的设备损坏导致的二进制log file destoryed,建议二进制日志与数据不要存放在同一个存储设备上(基于性能与数据安全性)  
![这里写图片描述][20150808082846520]  
删除指定二进制日志文件mysql-bin.000003之前的日志  
![这里写图片描述][20150808082934753]  
中继日志：  
从主服务器的二进制日志文件中复制而来的事件，并保存为的日志文件；  
事务日志：  
事务性存储引擎用于保证原子性、一致性、隔离性和持久性；  
innodb\_flush\_log\_at\_trx\_commit: 默认为1  
0: 每秒同步，并执行磁盘flush操作；  
1：每事务同步，并执行磁盘flush操作；  
2: 每事务同步，但不执行磁盘flush操作；  
![这里写图片描述][20150808082957139]  
Controls the balance between strict ACID compliance for commit operations, and higher performance that is possible when commit-related I/O operations are rearranged and done in batches.  
The default value of 1 is required for full ACID compliance. With this value, the contents of the InnoDBlog buffer are written out to the log file at each transaction commit and the log file is flushed to disk.  
With a value of 0, the contents of the InnoDB log buffer are written to the log file approximately once per second and the log file is flushed to disk.  
With a value of 2, the contents of the InnoDB log buffer are written to the log file after each transaction commit and the log file is flushed to disk approximately once per second.  
其他相关参数：  
**expire\_logs\_days=\{0..99\}**  
设定二进制日志的过期天数，超出此天数的二进制日志文件将被自动删除。默认为0，表示不启用过期自动删除功能。如果启用此功能，自动删除工作通常发生在MySQL启动时或FLUSH日志时。作用范围为全局，可用于配置文件，属动态变量。  
**general\_log=\{ON|OFF\}**  
设定是否启用查询日志，默认值为取决于在启动mysqld时是否使用了  
**–general\_log**  
如若启用此项，其输出位置则由–log\_output选项进行定义，如果log\_output的值设定为NONE，即使用启用查询日志，其也不会记录任何日志信息。作用范围为全局，可用于配置文件，属动态变量。  
**general\_log\_file=FILE\_NAME**  
查询日志的日志文件名称，默认为“hostname.log”。作用范围为全局，可用于配置文件，属动态变量。  
**binlog-format=\{ROW|STATEMENT|MIXED\}**  
指定二进制日志的类型，默认为STATEMENT。如果设定了二进制日志的格式，却没有启用二进制日志，则MySQL启动时会产生警告日志信息并记录于错误日志中。作用范围为全局或会话，可用于配置文件，且属于动态变量。  
**log=\{YES|NO\}**  
是否启用记录所有语句的日志信息于一般查询日志(general query log)中，默认通常为OFF。MySQL 5.6已经弃用此选项。  
**log-bin=\{YES|NO\}**  
是否启用二进制日志，如果为mysqld设定了–log-bin选项，则其值为ON，否则则为OFF。其仅用于显示是否启用了二进制日志，并不反应log-bin的设定值。作用范围为全局级别，属非动态变量。  
**log\_bin\_trust\_function\_creators=\{TRUE|FALSE\}**  
此参数仅在启用二进制日志时有效，用于控制创建存储函数时如果会导致不安全的事件记录二进制日志条件下是否禁止创建存储函数。默认值为0，表示除非用户除了CREATE ROUTING或ALTER ROUTINE权限外还有SUPER权限，否则将禁止创建或修改存储函数，同时，还要求在创建函数时必需为之使用DETERMINISTIC属性，再不然就是附带READS SQL DATA或NO SQL属性。设置其值为1时则不启用这些限制。作用范围为全局级别，可用于配置文件，属动态变量。  
**log\_error=/PATH/TO/ERROR\_LOG\_FILENAME**  
定义错误日志文件。作用范围为全局或会话级别，可用于配置文件，属非动态变量。  
**log\_output=\{TABLE|FILE|NONE\}**  
定义一般查询日志和慢查询日志的保存方式，可以是TABLE、FILE、NONE，也可以是TABLE及FILE的组合(用逗号隔开)，默认为TABLE。如果组合中出现了NONE，那么其它设定都将失效，同时，无论是否启用日志功能，也不会记录任何相关的日志信息。作用范围为全局级别，可用于配置文件，属动态变量。  
**log\_query\_not\_using\_indexes=\{ON|OFF\}**  
设定是否将没有使用索引的查询操作记录到慢查询日志。作用范围为全局级别，可用于配置文件，属动态变量。  
**log\_slave\_updates**  
用于设定复制场景中的从服务器是否将从主服务器收到的更新操作记录进本机的二进制日志中。本参数设定的生效需要在从服务器上启用二进制日志功能。  
**log\_slow\_queries=\{YES|NO\}**  
是否记录慢查询日志。慢查询是指查询的执行时间超出long\_query\_time参数所设定时长的事件。MySQL 5.6将此参数修改为了slow\_query\_log。作用范围为全局级别，可用于配置文件，属动态变量。  
**log\_warnings=1**  
设定是否将警告信息记录进错误日志。默认设定为1，表示启用；可以将其设置为0以禁用；而其值为大于1的数值时表示将新发起连接时产生的“失败的连接”和“拒绝访问”类的错误信息也记录进错误日志。  
**long\_query\_time=10**  
设定区别慢查询与一般查询的语句执行时间长度。这里的语句执行时长为实际的执行时间，而非在CPU上的执行时长，因此，负载较重的服务器上更容易产生慢查询。其最小值为0，默认值为10，单位是秒钟。它也支持毫秒级的解析度。作用范围为全局或会话级别，可用于配置文件，属动态变量。  
**max\_binlog\_cache\_size\{4096 .. 18446744073709547520\}**  
二进定日志缓存空间大小，5.5.9及以后的版本仅应用于事务缓存，其上限由max\_binlog\_stmt\_cache\_size决定。作用范围为全局级别，可用于配置文件，属动态变量。  
**max\_binlog\_size=\{4096 .. 1073741824\}**  
设定二进制日志文件上限，单位为字节，最小值为4K，最大值为1G，默认为1G。某事务所产生的日志信息只能写入一个二进制日志文件，因此，实际上的二进制日志文件可能大于这个指定的上限。作用范围为全局级别，可用于配置文件，属动态变量。  
**max\_relay\_log\_size=\{4096..1073741824\}**  
设定从服务器上中继日志的体积上限，到达此限度时其会自动进行中继日志滚动。此参数值为0时，mysqld将使用max\_binlog\_size参数同时为二进制日志和中继日志设定日志文件体积上限。作用范围为全局级别，可用于配置文件，属动态变量。  
**innodb\_log\_buffer\_size=\{262144 .. 4294967295\}**  
设定InnoDB用于辅助完成日志文件写操作的日志缓冲区大小，单位是字节，默认为8MB。较大的事务可以借助于更大的日志缓冲区来避免在事务完成之前将日志缓冲区的数据写入日志文件，以减少I/O操作进而提升系统性能。因此，在有着较大事务的应用场景中，建议为此变量设定一个更大的值。作用范围为全局级别，可用于选项文件，属非动态变量。  
**innodb\_log\_file\_size=\{108576 .. 4294967295\}**  
设定日志组中每个日志文件的大小，单位是字节，默认值是5MB。较为明智的取值范围是从1MB到缓存池体积的1/n，其中n表示日志组中日志文件的个数。日志文件越大，在缓存池中需要执行的检查点刷写操作就越少，这意味着所需的I/O操作也就越少，然而这也会导致较慢的故障恢复速度。作用范围为全局级别，可用于选项文件，属非动态变量。  
**innodb\_log\_files\_in\_group=\{2 .. 100\}**  
设定日志组中日志文件的个数。InnoDB以循环的方式使用这些日志文件。默认值为2。作用范围为全局级别，可用于选项文件，属非动态变量。  
**innodb\_log\_group\_home\_dir=/PATH/TO/DIR**  
设定InnoDB重做日志文件的存储目录。在缺省使用InnoDB日志相关的所有变量时，其默认会在数据目录中创建两个大小为5MB的名为ib\_logfile0和ib\_logfile1的日志文件。作用范围为全局级别，可用于选项文件，属非动态变量。  
**relay\_log=file\_name**  
设定中继日志的文件名称，默认为host\_name-relay-bin。也可以使用绝对路径，以指定非数据目录来存储中继日志。作用范围为全局级别，可用于选项文件，属非动态变量。  
**relay\_log\_index=file\_name**  
设定中继日志的索引文件名，默认为为数据目录中的host\_name-relay-bin.index。作用范围为全局级别，可用于选项文件，属非动态变量。  
**relay-log-info-file=file\_name**  
设定中继服务用于记录中继信息的文件，默认为数据目录中的relay-log.info。作用范围为全局级别，可用于选项文件，属非动态变量。  
**relay\_log\_purge=\{ON|OFF\}**  
设定对不再需要的中继日志是否自动进行清理。默认值为ON。作用范围为全局级别，可用于选项文件，属动态变量。  
**relay\_log\_space\_limit=0**  
设定用于存储所有中继日志文件的可用空间大小。默认为0，表示不限定。最大值取决于系统平台位数。作用范围为全局级别，可用于选项文件，属非动态变量。  
**slow\_query\_log=\{ON|OFF\}**  
设定是否启用慢查询日志。0或OFF表示禁用，1或ON表示启用。日志信息的输出位置取决于log\_output变量的定义，如果其值为NONE，则即便slow\_query\_log为ON，也不会记录任何慢查询信息。作用范围为全局级别，可用于选项文件，属动态变量。  
**slow\_query\_log\_file=/PATH/TO/SOMEFILE**  
设定慢查询日志文件的名称。默认为hostname-slow.log，但可以通过–slow\_query\_log\_file选项修改。作用范围为全局级别，可用于选项文件，属动态变量。  
**sql\_log\_bin=\{ON|OFF\}**  
用于控制二进制日志信息是否记录进日志文件。默认为ON，表示启用记录功能。用户可以在会话级别修改此变量的值，但其必须具有SUPER权限。作用范围为全局和会话级别，属动态变量。  
**sql\_log\_off=\{ON|OFF\}**  
用于控制是否禁止将一般查询日志类信息记录进查询日志文件。默认为OFF，表示不禁止记录功能。用户可以在会话级别修改此变量的值，但其必须具有SUPER权限。作用范围为全局和会话级别，属动态变量。

[20150808081514251]: /images/20220731/bf1ff077fa8a4c7e941eda62b308234d.png
[20150808081523372]: /images/20220731/442337137c3b4c8b802efaf130cf0912.png
[20150808081600842]: /images/20220731/1809df6de4184a30bd92d750f6ea7652.png
[20150808081617689]: /images/20220731/d9d23737e4ac436284383013979d34b7.png
[20150808081702076]: /images/20220731/2346e2b8584045af83dd65b1bbd532b4.png
[20150808081711308]: /images/20220731/c0cfa1b06a3f44a8ac4d0cacf705026d.png
[20150808081748750]: /images/20220731/ccf63014953a49c5ac1011fdfedf0676.png
[20150808082001978]: /images/20220731/45cd9c1263154d10bbcb3590135bd8e2.png
[20150808082049841]: /images/20220731/39ca37cfc88047ae8fbc82e117e8a22c.png
[20150808082057835]: /images/20220731/3eca3b8a5bbf4000861c8c20a2b7ee15.png
[20150808082105153]: /images/20220731/899235484f4049298c0edf8ad00bb6c9.png
[20150808082224838]: /images/20220731/3d2bc5da5d33447b9988db587f5dbb07.png
[20150808082233191]: /images/20220731/6257811b807740bc83020aad2a03040e.png
[20150808082242616]: /images/20220731/712ca76ba3e54ac7be5f0b3ed4260dec.png
[20150808082250673]: /images/20220731/bca5842eb47e4b068244e9c95e21b661.png
[20150808082332703]: /images/20220731/d1047a0d6a1c4cafbeb29946c669f1fb.png
[20150808082409163]: /images/20220731/a82cd10e008145d1a6858048168b7a72.png
[20150808082453800]: /images/20220731/b50c80b3035440e0b5ac3d4b84685da8.png
[20150808082502046]: /images/20220731/f2703fab36824553b7728fe15df98105.png
[20150808082651331]: /images/20220731/db4625a5e70d4d1b93318fcc03da6b26.png
[20150808082658088]: /images/20220731/12ffd7c7d2d9483888c36eb24e88ded5.png
[20150808082740067]: /images/20220731/a1b5ed85792c4c2b9a1aa849a5f62608.png
[20150808082750242]: /images/20220731/78010a3782604e3388d888a2f79c1411.png
[20150808082837639]: /images/20220731/1e9819d727374f249bc545eca3eca6d0.png
[20150808082846520]: /images/20220731/535c0885a84a4e7893197a340b4278b8.png
[20150808082934753]: /images/20220731/2777b90651b840828aa991cdde898f89.png
[20150808082957139]: /images/20220731/cdbcc4cf019b4b9ea840ffdcc4520f45.png