MySQL 入门知识（上） | 周末送资料
由社区专家royalwzy整理
1、MySQL安装必需的体系结构组件：MySQL服务器，客户机程序以及MySQL非客户机程序；中央程序充当服务器，客户机程序连接到该服务器以发出数据请求。
2、MySQL客户机/服务器通信并不仅限于所有计算机都运行同一操作系统的环境。
客户机程序可以连接到在相同主机或不同主机上运行的服务器
客户机/服务器通信可以发生在计算机运行不同操作系统的环境中
客户机程序：
1、mysql/mysqldump客户机程序是使用最多的一个客户段工具了；
2、MySQL Workbench是一款GUI工具,可以用来：
为数据库建模
执行数据库查询
执行管理任务
MySQL服务器：
1、服务器和主机之间的概念差别：
服务器：一个软件程序(mysqld),具有版本号和一系列功能；
主机：服务器程序在其上运行的物理计算机，其中包含：硬件配置、操作系统、网络地址
2、多个mysqld实例可同时在一台主机上运行;
服务器进程：
1、应用程序主要是mysqld进程来访问数据库服务器,由mysqld来操作不同的存储引擎(磁盘:InnoDB,MyISAM;内存:Memory;网络:NDB);
2、mysqld(服务器程序)进程可以划分为以下三个层：
- 连接层：处理连接,此层存在于所有服务器软件(Web/邮件/LDAP服务器)上;
- SQL层:处理所连接的应用程序发送的SQL查询;
- 存储层:处理数据存储,数据可以按不同格式和结构存储在不同物理介质上
连接层：
1、连接层可通过多种通信协议接受来自应用程序的连接:
- TCP/IP;
- UNIX套接字;
- 共享内存;
- 命名管道;
2、其中TCP/IP适用于整个网络,也是最常用的连接方式;客户机和服务器在同一台计算机上运行时,上面列出的其他协议仅支持本地连接;
3、此层针对每个连接维护一个线程,此线程处理查询执行;在某个连接可以开始发送SQL查询之前,将会通过验证用户名+口令+客户机主机来对该连接进行验证;
通信协议：
1、TCP/IP(传输控制协议/Internet协议,Transmission Control Protocol/Internet Protocol)：该通信协议套件用于连接Internet上的主机;在Linux操作系统中,TCP/IP是内置的,供 Internet使用,从而使其成为通过网络传输数据的标准;这也是适用于Windows的最佳连接类型;
2、UNIX套接字：一种进程间通信形式,用于在同一台计算机上的进程之间形成双向 通信链路的一端;套接字需要本地系统上的物理文件,这是适用于Linux的最佳连接类型;
3、共享内存：一种在程序之间传递数据的有效方式;一个程序创建其他进程(如果允许)可以访问的内存部分;此Windows显式“被动”模式仅适用于单台(Windows)计算机;默认情况下,共享内存处于禁用状态,要启用共享内存连接,必须使用–shared-memory选项启动服务器;
4、命名管道：命名管道的使用偏向于客户机/服务器通信,其工作方式与套接字非常相似;命名管道支持读/写操作,以及服务器应用程序的显式“被动”模式;此协议仅适用于单台(Windows)计算机;默认情况下,命名管道处于禁用状态,要启用命名管道连接,必须使用–enable-named-pipe选项启动服务器;
SQL层：
1、解析器:解析器验证语法是否正确;
2、授权:验证是否允许所连接的用户运行特定查询;
3、优化器:创建每个查询的执行计划,这是有关如何以最优化的方式执行查询的分步指令集,确定要使用哪些索引以及采用何种顺序处理表是此步骤的最重要部分;
4、查询执行:完成每个查询的执行计划;
5、查询高速缓存:(可选)可配置的查询高速缓存,可用于存储(并立即返回)执行的查询和结果;
6、查询日志记录:可以启用以跟踪执行的查询;
SQL语句处理：
第一次的选择主要是查看是否有配置开启[高速缓存查询]的特性;
存储层：
1、通过MySQL,可以使用称为“存储引擎”的不同类型的存储,数据可以存储在磁盘,内存和网络中;
2、数据库中的每个表可以使用任何可用的存储引擎,“磁盘”存储便宜且持久,而“内存”存储则要快得多;
3、InnoDB是默认存储引擎,它可提供事务,全文索引和外键约束,因此适用于各种混合查询;它具有多种用途,支持读密集型工作负荷,读/写工作负荷和事务工作负荷;
4、其他存储引擎包括:
- MyISAM:适用于频繁读取但很少更新的数据;
- MEMORY:在内存中存储所有数据;
- NDB:供MySQL Cluster用来为高可用性数据提供冗余的可伸缩拓扑
注:存储引擎可扩展,超越存储层,而不只包含存储,它们还包括其他结构和实现机制;
存储引擎概览：
1、客户机通过以SQL语句形式向服务器发送请求从表中检索数据或更改表中的数据;
2、服务器通过使用双层处理模型执行每条语句;
3、客户机通常不需要关心哪些引擎参与SQL语句处理,这种独立于引擎的SQL语句的一些例外情况包括:
- CREATE TABLE具有ENGINE选项,可基于每个表指定要使用的引擎;
- ALTER TABLE具有ENGINE选项,允许将表转换为使用不同的存储引擎;
- 某些索引类型仅适用于特定存储引擎;例如,仅InnoDB和MyISAM引擎支持全文索引;
- COMMIT和ROLLBACK操作仅影响事务存储引擎(例如InnoDB和NDB)管理的表;
依赖于存储引擎的功能：
1、存储介质:表存储引擎可以在磁盘上,在内存中或通过网络存储数据;
2、事务功能:某些存储引擎支持全面的ACID事务功能,而其他存储引擎可能不具有事务支持;
3、锁定:存储引擎可能使用不同的锁定粒度(例如表级别锁定或行级别锁定)和机制来提供与并发事务的一致性;
4、备份和恢复:可能会受到存储引擎存储和操作数据的方式的影响;
5、优化:不同的索引实现可能会影响优化,存储引擎以不同的方式使用内部高速缓存,缓冲区和内存以优化性能;
6、特殊功能:某些引擎类型具有提供全文搜索和引用完整性的功能以及处理空间数据的能力;
TIPS:优化器可能需要根据存储引擎进行不同的选择,但这均是通过每种存储引擎支持的标准化接口(API)进行处理的;
MySQL如何使用磁盘空间：
1、默认情况下,程序文件随数据目录一起存储在服务器安装目录下;执行各种客户机程序,管理程序和实用程序时将创建程序可执行文件和日志文件;
2、首要使用磁盘空间的是数据目录:
服务器日志文件和状态文件包含有关服务器处理的语句的信息,日志可用于进行故障排除/监视/复制和恢复;
InnoDB日志文件(适用于所有数据库)驻留在数据目录级别;
InnoDB系统表空间包含数据字典,撤消日志和缓冲区;
每个数据库在数据目录下均具有单一目录(无论在数据库中创建何种类型的表),数据库目录存储以下内容:
- 数据文件:特定于存储引擎的数据文件,这些文件也可能包含元数据或索引信息,具体取决于所使用的存储引擎;
- 格式文件(.frm):包含每个表和/或视图结构的说明,位于相应的数据库目录中;
- 触发器:与某个表关联并在该表发生特定事件时激活的命名数据库对象;
数据目录的位置取决于配置,操作系统,安装包和分发;典型位置是/var/lib/mysql;
MySQL在磁盘上存储系统数据库(mysql),mysql包含诸如用户/特权/插件/帮助列表/事件/时区实现和存储例程之类的信息;
MySQL如何使用内存：
1、内存分配可以划分为以下两种类别:
- 全局(每实例内存):服务器启动时分配一次并在服务器关闭时释放,此内存在所有会话间共享;当所有物理内存用尽时,操作系统开始交换,这会对MySQL服务器性能具有不利影响,可能会导致服务器崩溃;
- 会话(每会话内存):基于每个会话(有时称为“线程”)动态进行分配;此内存可在会话结束时或不再需要会话时释放,此内存多用于处理查询结果,所使用的缓冲区大小基于每个连接;例如,read_buffer为10MB且具有100个连接意味着可能总共有100*10MB同时用于所有读取缓冲区;
内存结构：
服务器在运行时会为许多种类的数据分配内存:
1、查询高速缓存还用于加速处理重复发出的查询;
2、线程高速缓存:在MySQL(和其他程序)中使用线程将应用程序执行划分为两个或更多个同时运行的任务,将会为连接到MySQL服务器的每个客户机创建单独的线程以处理该连接;
3、缓冲区和高速缓存:缓冲区和高速缓存提供数据管理子系统并支持快速访问项目,例如授权表缓冲区,存储引擎缓冲区(如InnoDB的日志缓冲区)和保存开放表说明符的表开放缓冲区;如果使用MEMORY存储引擎,MySQL将使用主内存作为主体数据存储,其他存储引擎也可能使用主内存进行数据存储,但MEMORY是唯一的,未设计为在磁盘上存储数据;
4、连接/会话:
1）.内部临时表:在某些查询执行情况下,MySQL会创建一个临时表来解析查询;可以在内存中或在磁盘上创建临时表,具体取决于其大小或内容或者查询语法;
2）.特定于客户机的缓冲区:专门设计为支持所连接的各个客户机;缓冲区示例包括:
用于交换信息的通信缓冲区;
排序操作:表读取缓冲区(包括支持联接的缓冲区);
MySQL插件接口：
1、当前,插件API支持:
- 可用于替换或扩充内置全文解析器的全文解析器插件;例如,某个插件可以使用不同于内置解析器所使用的规则将文本解析为字,要解析具有不同于内置解析器所预期的特征的文本,这很有用;
- 向服务器提供低级别存储,检索和数据索引的存储引擎;
- 信息模式插件;信息模式插件作为MySQL INFORMATION_SCHEMA数据库中的表出现,稍后将更详细地讨论INFORMATION_SCHEMA数据库;
- 守护进程插件启动在服务器内运行的后台进程(例如,定期执行心跳处理);
2、插件接口需要mysql数据库中的PLUGINS表,此表是在MySQL安装过程中创建的
二、系统管理
MySQL服务器分发
1.MySQL可用于多个操作系统,包括Linux/Windows/Mac OS X和Oracle Solaris等;本课程仅讲述Linux;
2.MySQL可以作为二进制文件分发和源代码分发的形式提供:
- 二进制文件分发:是预编译的,可以运行的程序,可用于Enterprise和Community MySQL Server版本,这些二进制文件是正式的经过Oracle测试的版本;
- 源代码分发:不保证与商业代码更新一致,它们也不包括Oracle支持;
TIPS:有关可用OS类型的完整列表http://dev.mysql.com/downloads/mysql/;
MySQL二进制文件分发
1.用于Linux的二进制文件:
- RPM文件可用于基于RPM的Linux分发,例如Oracle Linux;通过使用rpm程序或者通过使用yum等软件包管理器来安装这些文件,每个RPM的安装布局由RPM文件自身内包含的规范文件提供;(使用rpm -qpl
2.用于Windows的二进制文件:
- 完整分发:包含MySQL安装的所有文件以及配置向导。
- 非安装分发:.zip归档文件,不使用安装或配置向导,您只需解压缩该归档文件并将其移至所需的安装位置;
TIPS:二进制文件分发还可以压缩文件形式用于多个其他操作系统(包括Oracle Solaris);
MySQL源代码分发
1.如果需要预编译分发中可能没有的功能(例如完整的调试支持),可以根据源代码编译MySQL;
2.要使服务器在运行时使用较少内存,可能需要禁用不需要的功能;例如,可能需要禁用可选存储引擎,或者仅编译实际需要的那些字符集;
3.二进制文件分发仅可用于已发行的版本,不可用于最新的开发源代码;
4.源代码分发可以安装在任何所需位置;默认Linux安装位置为/usr/local/mysql;
用于Linux的MySQL RPM安装文件
1.Oracle提供两种类型的MySQL RPM:
- 与分发无关:MySQL提供给社区的RPM,它们应该可以在支持RPM软件包并使用glibc 2.3(GNU C库是标准C库的GNU实现)的所有Linux版本上运行;(查看方式:ldd –version)
- 特定于分发:面向目标Linux平台,Oracle为许多平台提供了RPM文件;
2.MySQL的RPM安装通常分为不同的软件包,对于标准安装,必须至少安装服务器程序 和客户机程序,标准安装不需要其他软件包;
- MySQL-client-advanced-.rpm:客户端命令行工具;
- MySQL-devel-advanced-.rpm:包含了编译软件所需要的头文件,需要使用客户端共享库;
- MySQL-embedded-advanced-.rpm:嵌入式数据库,为移动智能设备使用;
- MySQL-server-advanced-.rpm:服务端(包含mysqld的二进制文件);
- MySQL-shared-advanced-.rpm:包含客户端的共享库(libmysqlclient.so*);
- MySQL-shared-compat-advanced-.rpm:包含了兼容旧版本的客户端共享库;
- MySQL-test-advanced-.rpm:包含了测试套件;
Linux MySQL RPM安装过程
1.解压缩包:unizp MySQL-5.6.25-oel6-x86_64.zip;
2.创建用户:useradd mysql;
3.安装RPM包:rpm -ivh MySQL-*-advanced-5.6.25-1.el6.x86_64.rpm;安装在运行时自动执行以下任务:
- 将RPM文件提取到其默认位置;
- 在/etc/init.d目录中注册名为mysql的启动脚本;
- 执行mysql_install_db,即创建系统数据库和默认my.cnf文件的脚本,为root帐户设置随机口令并将该口令保存在安装用户主目录中名为.mysql_secret的文件中;
- 为mysql设置登录帐户以及用户名和组名称(用于管理和运行服务器);
4.发生与自带版本冲突的话:
- 删除原来的包:yum remove mysql-libs-5.1.73-3.el6_5.x86_64;缺点是会删除相关的依赖文件,可能其它程序会用;
- 添加–replacefiles选项:rpm -ivh –replacefiles MySQL--advanced;
5.产生的目录:
/usr/bin:客户端程序和脚本;
/usr/sbin:mysqld服务程序;
/var/lib/mysql:数据库和日志文件,之后讲到数据库结构会讲每个文件的作用;
/usr/share/info:info格式的MySQL手册;
/usr/share/man:标准的Unix man格式手册;
/usr/include/mysql:MySQL所需的头文件;
/usr/lib64/mysql:库文件;
/usr/share/mysql:其它杂项,包括支持文件,错误信息,字符集文件,示例配置文件和数据库安装的SQL文件等;
/usr/share/sql-bench:测试基线;
/etc/my.cnf, /usr/my.cnf:缺省配置文件;
/etc/init.d/:包含了mysql启动脚本;
/var/log:mysqld.log文件;
6.启动数据库:service mysql start;
- 查看后台进程:ps -ef | grep mysql;
- 本来是启动的mysqld服务,后台多了一个mysqld_safe服务,之后讲数据库启动的几种方式会提到;
7.客户端登陆:mysql;
之前版本都可以使用空密码登录,现在却报错;
查看安装过程,会提示密码随机了,之后需要修改密码;
A RANDOM PASSWORD HAS BEEN SET FOR THE MySQL root USER !
You will find that password in ‘/root/.mysql_secret’.
You must change that password on your first connect,
no other statement but ‘SET PASSWORD’ will be accepted.
See the manual for the semantics of the ‘password expired’ flag.
Also, the account for the anonymous user has been removed.
查看到密码后再登录:mysql -uroot -p;
如何使用SET PASSWORD: help SET PASSWORD,发现报错,必须先修改:SET PASSWORD = PASSWORD(“mysql”);
8.卸载数据库:
查看安装了哪些软件:yum list | grep MySQL;
删除软件:yum remove MySQL-*;
在Linux上启动MySQL服务器;
1.可以使用多种方法在Linux上启动服务器:
- mysqld:手动调用服务器来调试MySQL服务器;默认情况下,错误消息传至终端,而不是错误日志;
- mysqld_safe:设置错误日志,然后启动mysqld并对其进行监视,如果mysqld异常终止(kill -9 pid),mysqld_safe会将其重新启动;如果服务器未正常启动,请查看错误日志;
- mysql.server:用作mysqld_safe的包装,针对使用System V运行级别目录的Linux和Oracle Solaris等系统;
- mysqld_multi:该Perl脚本用于简化单台主机上的多个服务器管理,它可以启动或停止服务器,它还可以报告服务器是否正在运行;
- 其它:mysqladmin -uroot -p start;
2.安装正确的脚本以使服务器在启动时自动运行:
- 在BSD样式的Linux系统上,最常见的是通过某一个系统启动脚本(例如/etc目录中的rc.local脚本)调用mysqld_safe;
- 在/etc/init.d下具有运行级别目录的Linux和UNIX System V变体使用mysql.server脚本,预先构建的 Linux二进制软件包针对相应的运行级别在名称mysql安装mysql.server;使用chkconfig注册服务;
在Linux上停止MySQL服务器
1.要手动停止服务器,请使用以下方法之一:
- mysqladmin:具有关闭命令,它作为客户机连接到服务器并且可以关闭本地或远程服务器;mysqladmin -uroot -p shutdown;
- mysql.server:在使用stop参数调用时停止和/或关闭本地服务器;
- mysqld_multi:停止和/或关闭其管理的任何服务器,它通过调用mysqladmin来执行此操作;
2.mysqld_safe没有服务器关闭功能;
提高安装安全性
1.从RPM软件包安装MySQL时,将为root帐户设置随机口令并将该口令保存到安装用户主目录中的.mysql_secret文件;对于所有其他安装,初始口令为空白;
2.在不带参数的情况下调用mysql_secure_installation,这将提示您确定要执行的操作;
例子:可以运行一下# /usr/bin/mysql_secure_installation,虽然此处显示mysql_secure_installation 脚本以Linux root用户身份运行,但您可以普通用户身份运行该脚本;
Windows MySQL服务器安装目录
1.默认情况下,MySQL5.6安装在目录路径C:\Program Files\MySQL\MySQL 5.6 Server中;
2.\bin包含MySQL服务器和客户机程序:
1.mysqld.exe:标准服务器;
2.其他客户机程序,例如mysqladmin.exe;
3.\data是服务器存储数据库和日志文件的位置,此目录是预配置的,可以直接使用;例如,此目录包括mysql子目录(包含授权表)和用于test数据库的test子目录(可用于测试目的);
4.my.ini:配置选项文件指定安装目录的位置以及其他可选设置;
在Windows上运行MySQL
1.通过使用net start MySQL和net stop MySQL命令从命令行手动启动和停止服务;
2.使用mysqld –install命令从命令行调用服务器;在停止服务后将其删除,使用mysqld –remove;
数据目录
1.每个表都具有*.frm文件(包括视图);
2.MySQL服务器开始执行时会将其当前工作目录转到其data目录,必须确保MySQL服务器具有正确的访问权限,以便在data目录中创建文件,该服务器必须可以访问它要在其中创建数据文件或日志文件的所有目录;
3.MySQL服务器将每个数据库映射到MySQL data目录下的一个目录,并且默认情况下,它将数据库中的表映射到数据库目录中的文件名;这具有以下含义:
- 数据库和表名称仅在具有区分大小写的文件名的操作系统(例如大多数Linux系统)上的MySQL服务器中才区分大小写;
- 可以通过将数据目录,数据库和/或单个表(具体取决于存储引擎选项)移至不同的物理位置来分割磁盘使用,这可以提高性能;
MySQL服务器发行版
1.升级之前还应该查看readme文档:
- 在关于升级的部分中,一定要阅读与正在执行的升级类型有关的注释,按照建议的过程执行操作;
- 在关于新版本的更改注释部分中,查看在当前版本与要安装的版本之间发生的所有更改,请注意不与当前版本向后兼容的所有更改;
2.RPM和源代码升级通常不需要进行重新配置,因为它们往往使用相同安装目录位置,而不考虑MySQL版本;
3.需要进行一些重新配置的情况:
- 如果使用通用Linux二进制文件进行升级,可以选择创建特定于新版本的目录来包含升级的发行版;
- 此外,Windows安装程序在Program Files下创建特定于版本的文件夹;
4.设置指向旧安装目录的软链接,从而可以轻松删除并重新创建该链接来指向新安装目录,对该符号链接的后续引用将访问新安装;比如:把data目录做成一个软链接;
5.如果您的安装最初是通过安装多个RPM软件包而生成的,则最好升级所有软件包,而不仅是其中的一些;例如,如果先前安装了服务器和客户机RPM,则不要仅升级服务器RPM;
补充:
1.升级MySQL总体上可以简单使用以下步骤进行操作:
- 备份你的数据库;
- 关闭Server;
- 在已存版本上安装新版本MySQL;
- 启动Server;
2.MySQL当前提供的升级不提供跳级升级,因此,如果你当前的MySQL版本为5.1,那么升级到5.7的流程为:
5.1->5.5->5.6->5.7;
检查升级的表
1.在每次进行MySQL升级时,都需要运行mysql_upgrade程序,主要是执行了以下操作:
- 检查数据库中的所有表与MySQL服务器当前版本是否兼容性;
- 修复表中发现的所有问题以及可能的不兼容性;
- 升级系统表来添加新版本中可用的所有新特权或功能;
- 使用当前MySQL版本号标记所有已检查和已修复的表;
2.mysql_upgrade会将MySQL版本号保存在数据目录下的一个mysql_upgrade_info的文件中,这个文件被用于快速查看是否所有表针对升级版本已经做了检查,是否可以跳过表检查;当然,运行mysql_upgrade时,你也可以使用–force项来跳过查看此文件;
3.在MySQL 5.7.5之前,为了检查和修正表并进行系统表升级,mysql_upgrade会调用以下2条命令:
mysqlcheck –check-upgrade –all-databases –auto-repair
mysql_fix_privilege_tables
从MySQL 5.7.5以后,mysql_upgrade将直接和MySQL Server交互,发送所需的SQL语句来执行升级;
补充:升级过程;
1.停止服务:service mysql stop;
2.备份文件:
- 创建临时目录:mkdir /tmp/mbackup;
- 备份配置文件:cp /etc/my.cnf /tmp/mbackup/;
- 备份数据文件:cp -rf /var/lib/mysql/ /tmp/mbackup/mysql;也可以用mysqldump/xtrabackup;
3.删除软件:yum -y remove MySQL-*-advanced-5.6.25-1.el6.x86_64;(不推荐)
4.安装软件:rpm -ivh –replacefiles MySQL-server-advanced-5.6.26-1.el6.x86_64.rpm MySQL-client-advanced-5.6.26-1.el6.x86_64.rpm;
5.拷贝回配置文件:cp /tmp/mbackup/my.cnf /etc/my.cnf;
6.尝试重启:service mysql restart;
7.执行更新:mysql_upgrade -uroot -p;
8.查看生成的更新文件并且尝试登录查看版本信息:less $MYSQL_DATADIR/mysql_upgrade_info;
使用多个服务器
1.要在运行生产服务器的同一台计算机上测试MySQL的新发行版时,需要运行多个服务器;假定每个组具有其自己的指定root用户,该用户无法查看属于其他组的数据库,如果所有 客户机将共享同一服务器则可能会这样;
2.不允许任何服务器共享必须由单个服务器独占使用的资源;
3.mysqld_multi脚本设计用来管理多个mysqld进程,这些进程监听不同UNIX套接字文件和TCP/IP端口上的连接,该脚本搜索my.cnf中名为[mysqldN]的组,然后将该N的设置应用于编号的实例;
例如,要启动两个mysqld实例,它们分别应用来自my.cnf部分[mysqld3]和[mysqld5]的设置,请运行以下命令:
shell> mysqld_multi start 3, 5
多个服务器选项
使用mysqld或mysqld_multi以及每个服务器函数的相应选项来调用每个MySQL服务器:
1.数据目录:使用–datadir选项的唯一值启动每个服务器;
2.网络:通过使用–port,–socket和–shared-memory-basename选项的唯一值启动每个服务器,将每个服务器设置为使用其自己的网络接口;
3.组名称:使用mysqld_multi时,每个服务器组在Linux或UNIX上必须具有唯一的mysqldN名称;
4.日志文件:每个服务器必须具有其自己的日志和PID文件;
5.InnoDB表空间和日志文件:不能由多个服务器共享;
6.Windows服务名称:每个mysqld Windows服务必须使用唯一的服务名称,通过使用–install设置服务名称;服务器启动时,它们从标准选项文件中的各个相应服务组中读取选项;
练习任务
1.创建和导入world_innodb数据库;
$ mysql -uroot -p;
mysql> CREATE DATABASE world_innodb;
mysql> USE world_innodb
mysql> SET autocommit=0;
mysql> SOURCE /labs/world_innodb.sql;
mysql> SET autocommit=1; // 为了提高运行速度,批量提交;
2.查看本地MySQL服务器数据目录;
mysql> SHOW VARIABLES LIKE ‘datadir’\G
3.检查MySQL服务器的状态;
service mysql status;4.关闭/启动MySQL服务器;
service mysql stop/start;
补充:源码安装MySQL
1.准备安装工具:yum -y install gcc* make* perl*;
- cmake:从Mysql5.5以后使用cmake编译,可以从www.cmake.org下载最新版本;
- GUN make:操作系统自带;
- gcc:操作系统自带;
- perl:操作系统自带;
- libncurses5-dev(ncurses-devel):运行cmake必须的包,如果没有安装会报错.
2.安装cmake:
- 解压压缩包:>tar -zxvf cmake-VERSION.tar.gz;
- 进入到cmake的解压缩目录,执行./configure命令生成makefile;
- 执行>make;make install;命令生成安装软件并安装cmake;
- 软件安装到了/usr/local/share/cmake-VERSION/目录下,执行文件在/usr/local/bin目录下;
3.安装ncurses-devel插件
- 在Debian和Ubuntu上的包名是libncurses5-dev;
- 在RHEL和其它版本上是ncurses-devel,执行:yum install -y ncurses-devel*;
- 如果不安装会出现以下错误;
4.创建mysql用户:>useradd mysql;
5.解压缩mysql5.5的源码包:>tar -zxvf mysql-VERSION.tar.gz;
6.进入目录mysql-VERSION目录;
7.执行cmake命令生成makefile(MyISAM,MERGE,MEMBER和CSV四种引擎默认静态编译);
cmake -DCMAKE_INSTALL_PREFIX=/usr/local/mysql \
-DDEFAULT_CHARSET=utf8 \
-DDEFAULT_COLLATION=utf8_general_ci \
-DWITH_EXTRA_CHARSETS:STRING=utf8,gbk \
-DWITH_MYISAM_STORAGE_ENGINE=1 \
-DWITH_INNOBASE_STORAGE_ENGINE=1 \
-DWITH_FEDERATED_STORAGE_ENGINE=1 \
-DWITH_BLACKHOLE_STORAGE_ENGINE=1 \
-DWITH_ARCHIVE_STORAGE_ENGINE=1 \
-DWITH_READLINE=1 \
-DENABLED_LOCAL_INFILE=1 \
-DMYSQL_TCP_PORT=3306
8.编译文件:>make;make install;在mysql的安装目录下生成可执行文件,并自动创建了data文件(可以手动再创建一个logs目录,用来存放生成的日志文件,与数据目录不在同一块磁盘上,减小I/O并发),修改目录权限为mysql:>chown mysql:mysql data;
9.编写/etc/my.cnf;
10.在配置文件下添加目录配置,指定数据文件的位置;
bsedir = /usr/local/mysql/
datadir = /usr/local/mysql/data
11.数据库的初始化,主要是数据库的创建,帮助文件的填充,用户文件的填充,执行:>./scripts/mysql_install_db –defaults-file=./my.cnf –user=mysql(在my.cnf配置文件中添加user参数,并且拷贝到/etc目录下就不用再加参数,执行>./scripts/mysql_install_db即可)
12.启动服务器:>./bin/mysqld_safe –-user=mysql &;
13.修改MYSQL服务器root用户的密码:>./bin/mysqladmin –u root password ‘pwd’;
14.登录:
- 如果没有设置root的密码,默认是空密码,使用>./bin/mysql就可以登录;
- 如果设置了root密码,则登录时要数据密码验证>./bin/mysql –uroot -p;
- 进入数据库后修改用户密码:update user set password=PASSWORD(‘123456′) where user=’root’;
15.把mysql添加到环境变量:
- 打开~root/.bash_profile文件;
- 修改环境变量:>PATH=/usr/local/mysql/bin:$PATH,尽量把mysql的bin目录放在PATH的前面,使用mysql的工具的时候提高优先级,否则可能会使用系统预装的mysql的工具,造成版本不一致的错误;
- 使环境变量立即生效:>. ./.bash_profile;
16.把配置文件放到默认读取的路径,并在配置文件中指定启动用户为mysql,添加开机启动:
- 拷贝文件到开机启动目录:cp /usr/local/mysql/support-files/mysql.server /etc/rc.d/init.d/mysqld;
- 添加开机启动项：chkconfig –add mysqld;chkconfig mysqld on;
- 启动/关闭mysql服务:service mysqld start/stop;
配置文件my.cnf(RPM安装)
配置选项
1.预编译的选项:
（1）.在生成RPM包时指定的选项;
（2）.在源码安装时指定的选项;
2.命令行选项:
（1）.可以在启动服务器(mysqld)时在命令行上指定启动选项;
（2）.默认情况下,服务器在运行时使用其配置变量的预编译值;但是,如果默认值不适合环境,则可添加运行时选项,让服务器使用其他值来执行以下操作:
①.指定重要的目录和文件的位置;
②.控制服务器写入的日志文件;
③.覆盖服务器与性能相关的变量的内置值(即,控制最大同时连接数以及缓冲区和高速缓存的大小);
④.在服务器启动时启用或禁用预编译的存储引擎通过使用命令行选项或选项文件,或者使用两者的组合,可以指定服务器启动时的运行时选项(以更改其配置和行为);
（3）.命令行选项优先于选项文件中的任何设置
3.配置文件选项:在配置文件my.cnf中指定的启动选项;最常用的方式;
4.查看相关帮助mysqld –verbose –help;
使用选项文件的原因
1.将选项放在文件中后,不需要每次启动服务器时都在命令行上指定选项;对于复杂的选项(如用于配置InnoDB表空间的选项),这样做更加方便,并且更不容易出错;
2.如果所有服务器选项都在一个选项文件中,则可概览服务器的配置情况;
3.MySQL程序可以访问多个选项文件中的选项,要创建或修改某个选项文件,必须拥有该文件的写入权限;客户机程序仅需要读取访问权限;
选项文件组
1.选项文件中的选项按组进行组织,每个组前面有一个为组命名的[group-name]行,通常,组名称是选项组适用的程序的类别或名称;
选项组示例包括:
2.[client]:用于指定适用于所有客户机程序的选项;[client]组的一个常见用途是指定连接参数,因为在一般情况下,不管使用什么客户机程序,都要建立到同一个服务器的连接;
3.[mysql]和[mysqldump]:分别用于指定适用于mysql和mysqldump客户机的选项;此外,也可以单独指定其他客户机选项;
4.[server]:用于指定同时适用于mysqld和mysqld_safe服务器程序的选项;
5.[mysqld],[mysqld-5.6],[mysqld56]和[mysqld_safe]:用于指定适用于不同服务器版本或启动方法的选项;
编写选项文件
1.要创建或修改某个选项文件,最终用户必须拥有该文件的写入权限;服务器本身仅需要读取访问权限;服务器读取选项文件,但不创建或修改选项文件;
2.如何在选项文件中写入一个选项:
（1）.使用长选项格式(像命令行上使用的那样),但省略前导短划线;
（2）.如果某个选项取值,则允许在等号两则加空格( = );此规则不适用于在命令行上指定的选项,eg:指定默认选项文件;
3.在幻灯片上的示例中,请注意以下方面:
（1）.[client]:此组中的选项适用于所有标准客户机;
①.host:指定服务器主机名;
②.compress:指示客户机/服务器协议对通过网络发送的通信使用压缩;
（2）.[mysql]:此组中的选项仅适用于mysql客户机;
show-warnings:指示MySQL在每条语句后显示任何当前警告;
（3）.mysql客户机同时使用[client]和[mysql]组中的选项,因此将使用显示的全部三个选项;
选项文件位置
1.Linux:
（1）./etc/my.cnf;
（2）./etc/mysql/my.cnf;
（3）./usr/local/mysql/etc/my.cnf;
（4）.~/.my.cnf;
（5）.如果设置了MYSQL_HOME环境变量,则将搜索$MYSQL_HOME/my.cnf文件;
2.Windows:
（1）.C:\目录下的my.ini和my.cnf;
（2）.C:\Windows(或C:\WinNT)目录;
（3）.C:\Program Files\MySQL\MySQL Server 目录;
3.MySQL命令行程序会在MySQL安装目录中搜索选项文件;
选项文件中的启动选项
要在选项文件中指定服务器选项,需要在[mysqld]或[server]组下指示特定选项;
1.日志记录:可以通过启用所需日志的类型为服务器启用日志记录;比如:
（1）.general_log # 常规查询日志;
（2）.log-bin # 二进制日志;
（3）.slow_query_log # 慢速查询日志;
2.默认存储引擎:可以使用–default-storage-engine选项指定不同于InnoDB的默认存储引擎;
3.系统变量:可以通过设置服务器系统变量值来定制服务器;
（1）.max_connections=200 # 增加允许的最大连接数;
（2）.innodb_buffer_pool_instances=4 # 增加InnoDB缓冲池数的默认值;
4.共享内存:在Windows上默认不启用;可以使用shared-memory选项来启用命名管道支持;
5.命名管道:要启用命名管道支持,使用enable-named-pipe选项;
样例选项文件
1.Linux:
（1）.对于RPM安装,样例选项文件在/usr/share/mysql中;
（2）.对于TAR文件安装,样例文件在MySQL安装目录下的share目录中;
2.Windows:选项文件位于MySQL安装目录(my.ini)中;
3.如果多次指定一个选项(不管是在同一个选项文件中,还是在多个选项文件中),则最后出现的选项值优先;
（1）.–defaults-file=
显示选项文件中的选项
1.# my_print_defaults –defaults-file=/usr/local/mysql/my.cnf client mysql mysqld;
2.# mysql –print-defaults:貌似打印不出来;
遮蔽验证选项
1.建议不要使用mysql -uroot -poracle形式在命令行上指定口令;缺点:可以通过history命令查看到密码;
2.为方便起见,可以将口令放在[client]选项组中,但口令以纯文本方式存储,对选项文件有读取访问权限的任何人都能轻易地看到;
3.利用mysql_config_editor实用程序,可以将验证凭证存储在加密的登录文件.mylogin.cnf中;在Linux和UNIX上,该文件位置是当前用户的主目录;MySQL客户机程序以后可以读取该文件以获取用于连接到MySQL服务器的验证凭证;
TIPS:加密方法是可逆的,因此不应假设凭证对任何有文件读取特权的人都是安全的;相反,该功能使得避免使用纯文本凭证变得更容易;
4.文件说明:
.mylogin.cnf登录文件的未加密格式由选项组组成,类似于其他选项文件;
.mylogin.cnf中的每个选项组称为“登录路径”,仅允许一组有限的选项:主机,用户和口令;可将登录路径视为一组值,可以指示服务器主机以及用于服务器验证的凭证;eg:
[admin]
user = root
password = oracle
host = 127.0.0.1
登录路径
1.创建登录路径:mysql_config_editor set –login-path=admin –host=localhost –user=root –password;如果调用mysql_config_editor时不使用–login-path选项,则将使用[client]登录路径;默认情况下,所有标准客户机都使用此登录路径;
（1）.查看生成的文件:ll ~/.mylogin.cnf;
（2）.登录:mysql –login-path=admin;
2.以纯文本格式查看单个登录路径:mysql_config_editor print –login-path=admin;
3.以纯文本格式查看所有登录路径:mysql_config_editor print –all;
4.删除登录路径:mysql_config_editor remove –login-path=admin;
服务器系统变量
1.查看所有参数的默认值和读取选项文件之后的值:mysqld –verbose –help;
2.查看所有参数的默认值和忽略任何选项文件中的设置:mysqld –no-defaults –verbose –help;
3.查看变量值,没有其他启动选项:SHOW GLOBAL VARIABLES;
动态系统变量
1.MySQL维护了两种包含系统变量的作用域:
（1）.GLOBAL变量影响服务器的整体操作;
（2）.SESSION变量影响其对单个客户机连接的操作;
（3）.变量存在于任一作用域中,也可同时存在于两个作用域中;
2.变量及其作用域的示例包括:
（1）.仅全局:key_buffer_size,query_cache_size;
（2）.全局和会话:sort_buffer_size,max_join_size;
（3）.仅会话:timestamp,error_count;
3.在更改变量值时,适用以下几点:
（1）.设置会话变量不需要任何特殊特权,但客户机只能更改自己的会话变量,不能更改其他任何客户机的会话变量;
（2）.LOCAL和@@local是SESSION和@@session的同义词;
（3）.如果不指定GLOBAL或SESSION,则当会话变量存在时,SET将更改会话变量;会话变量不存在时,将产生错误;
TIPS:修改的全局参数不会在选项文件中反应出来,需要手动修改,以便下次启动后生效;与Oracle对比;
显示动态系统变量
设定特定的变量时要注意赋值的类型;
— 结构化系统变量;
1.MySQL支持一种结构化变量类型,该变量类型可以指定控制键高速缓存操作的参数;键高速缓存结构化变量具有以下组件:
（1）.key_buffer_size;
（2）.key_cache_block_size;
（3）.key_cache_division_limit;
（4）.key_cache_age_threshold;
2.要引用结构化变量实例的组件,可使用复合名称:instance_name.component_name;
示例:hot_cache.key_buffer_size/hot_cache.key_cache_block_size;cold_cache.key_cache_block_size;
3.相关文档:http://dev.mysql.com/doc/refman/5.6/en/structured-system-variables.html。
服务器状态变量
1.LOCAL是SESSION的同义词;
2.如果没有修饰符,则默认值为SESSION;
3.SHOW STATUS示例:mysql> SHOW GLOBAL STATUS;
SQL模式
SQL模式由控制查询处理某些方面的可选值组成,设置了相应的SQL模式后,客户机就可以对以下项目进行某种程度的控制:
1.输入数据:SQL模式可用于指示服务器对接受输入数据的宽容度;
2.标准SQL符合性:SQL模式可用于启用或禁用与标准SQL符合性相关的行为;
3.兼容性:SQL模式可用于改进与其他数据库系统的兼容性;
设置SQL模式
1.可以使用–sql-mode选项设置服务器启动时的默认SQL模式;
2.单个客户机可按自己的要求在选项文件内配置SQL模式;
3.如果没有修饰符,则SET将更改会话SQL模式;调用SET语句时可以带一个空字符串来清除当前SQL模式,也可以带一个或多个模式名称(用逗号分隔);
4.如果值为空或者包含多个模式名称,则必须将值放在引号中;如果值包含一个模式名称,则引号可有可无;SQL 模式值不区分大小写;
5.查看当前的sql_mode模式:
（1）.使用SELECT语句检查当前的sql_mode设置:SELECT @@sql_mode;
（2）.查看系统变量:SHOW VARIABLES LIKE ‘sql_mode’;
例子:
1.使用单个模式值设置SQL模式:SET sql_mode = ANSI_QUOTES; SET sql_mode = ‘TRADITIONAL’;
2.使用多个模式名称设置SQL模式:SET sql_mode = ‘IGNORE_SPACE,ANSI_QUOTES,NO_ENGINE_SUBSTITUTION’;
常用SQL模式
1.STRICT_TRANS_TABLES,STRICT_ALL_TABLES:没有这些模式,MySQL将接受缺少,超出范围或格式不正确的值;启用 STRICT_TRANS_TABLES时将为事务表设置“严格模式”;也可在默认的my.cnf文件中启用;启用STRICT_ALL_TABLES 时将为所有表设置严格模式;
2.TRADITIONAL:启用此SQL模式可对输入数据值施加类似于其他数据库服务器的限制;在此模式下,使用GRANT语句可创建要求指定口令的用户;
3.IGNORE_SPACE:默认情况下,必须调用函数名称与后接括号间没有空格的函数;启用此模式后,允许存在此类空格,并使函数名称成为保留字;
4.ERROR_FOR_DIVISION_BY_ZERO:默认情况下,除数为零时将产生结果NULL,在启用此模式的情况下插入数据时,除数为零将导致出现警告,在严格模式下将出现错误;(这个模式之后会被废弃掉)
5.ANSI:使用此组合模式将使MySQL服务器变得更加“类似于ANSI”;即,此模式支持的行为更像标准SQL,如ANSI_QUOTES和PIPES_AS_CONCAT;
6.NO_ENGINE_SUBSTITUTION:如果在创建或更改表时指定了不可用的存储引擎,除非启用了此模式,否则MySQL 将替换默认存储引擎;这是默认的SQL模式;
日志文件
1.错误日志(error log):
（1）.记录MySQL启动,关闭和运行时产生的重大的错误的信息;
（2）.如果mysqld警告一个表需要自动的检查或者修复,也会记录一个错误日志;
（3）.可以使用–log-error=file_name选项来指定错误日志文件,如果没有指定,则系统默认在data目录下生产一个hostname.err的文件;也可以在配置文件中配置log_error变量;
（4）.主要是由mysqld_safe脚本可创建错误日志,并在启动服务器时将其输出重定向到该错误日志;
（5）.查询log_error的位置:>show variables like ‘log_error’;查看主机名:>system hostname;
2.常规查询日志(general query log):
（1）.常规查询日志记录服务器运行期间收到的所有语句.当客户端连接或者断开的时候服务器会记录信息到日志中,并记录所有从客户端接收到的sql语句,它对于在客户端中排错和查看是哪个客户端发送的命令很有帮助;
（2）.mysqld是按照接收到命令的方式记录语句的,这可能跟它们执行的顺序不同(这与二进制日志是有区别的,二进制日志是执行后记录);
（3）.使用–general_log=[0|OFF|1|ON]来控制是否打开常规查询日志和–general_log_file=file_name来指定生成的常规查询日志文件(在mysql5.1.6中可以使用–log选项启动和使用–log-output选项指定日志输出的位置),也可以输出到”Server Log Tables”表中;如果没有指定file_name,默认是在data目录下生产一个hostname.log文件;
（4）.服务器重新启动和log flush不会产生一个新的文件;
（5）.默认此功能关闭,通过show variables like ‘general_log%’来查看;
（6）.设置打开一般查询日志:set global general_log=1;set global general_log_file=file_name(两个变量会同时打开关闭,打开之后立即生效);
（7）.可以在启动的时候指定–general-file选项或者在配置文件中指定general_log=1,general_file_log=/path;
（8）.一般不打开此日志功能,数据量太大,如果打开可以放到单独的磁盘中.
（9）.log_output变量值:
①.FILE:保存到文件中;
②.TABLE:保存到表中;SET GLOBAL log_output = ‘TABLE’;
③.清空日志表:TRUNCATE mysql.general_log;
（10）.备份切换一般日志文件:
①.先备份:>mv hostname.log hostname.log.bak
②.切换日志组:mysqladmin flush-logs;
3.慢查询日志(slow query log):
（1）.调优时使用,记录超出指定时间的sql语句;
（2）.慢查询日志主要记录执行时间超过long_query_time变量指定时间的sql语句,这个时间不包括获得锁的时间,只包含执行时间,系统默认时间是1s(long_query_time=1.0000);
（3）.使用–slow-query-log=0|1选项和–slow_query_log_file=file_name选项指定(在mysql5.1.6版本可以指定mysqld的–long-slow-queries=file_name选项启动);
（4）.命令行参数:–log-slow-queries=file_name;指定慢查询日志文件
（5）.系统变量:
①.slow_query_log:开启慢查询功能,set global slow_query_log = [0|OFF|1|ON];
②.slow_query_log_file:指定慢查询日志文件,系统默认是在data目录下的hostname-slow.log文件;
③.long_query_time:指定查询的最大时间,set global long_query_time=n;
④.log_queries_not_using_indexes:不用索引的慢查询,默认是功能关闭的;
查看某个表是否有索引:> show index from t;
打开此功能:>set global log_queries_not_using_indexes =1;
查看变量:>show variables like ‘log_queries_not_using_indexes’;
（6）.log_output变量值:
①.FILE:保存到文件中;
②.TABLE:保存到表中;SET GLOBAL log_output = ‘TABLE’;
③.清空日志表:TRUNCATE mysql.slow_log;
（7）.分析慢日志文件的工具:mysqldumpslow,查看具体的帮助是用mysqldumpslow –help;
4.二进制日志(binary log):
（1）.记录所有对数据库更新和潜在的更新语句(一个delete语句,但是没有影响的行),语句以事件(event)的方式存储,同时也包含了更新语句执行的时间信息;
（2）.它不记录那些不修改任何数据的语句,如果想要记录所有的语句,可以使用query log;
（3）.它主要的目的是在做还原操作时尽可能全的更新数据库,因为它包含在一次备份后的所有更新操作.它同样被用在master replication server中作为一个记录发送给slave servers;
（4）.打开二进制日志会损失1%的性能,但是它带来的好处远远超过这些;
（5）.查看二进制日志是否打开,需要查看log_bin参数是否是ON:>show variables like ‘log_bin’;
（6）.命令行参数
①.–log-bin=filename:记录二进制日志文件的位置,尽量指定路径名,如果不指定的话则保存在数据目录;
②.–log-bin-index=file:记录二进制日志文件索引的位置,保存了日志文件名;
③.–max_binlog_size:单个文件最大多少;
④.–binlog-do-db=db_name:哪个数据库使用,只有这个数据库使用;
⑤.–binlog-ignore-db=db_name:哪个数据库不使用,只有这个数据库不使用;
（7）.系统变量
①.log_bin:日志的位置;
②.binlog_cache_size:二进制日志缓存大小,是每一个连接进来的线程分配的大小,不是整个服务器的大小;
③.max_binlog_cache_size:最大缓存大小;
④.max_binlog_size:单个文件最大大小,超过此大小则再分配一个文件,但是一个事务必须在一个文件中,所以可能会稍大点;
⑤.binlog_cache_use:当前连接使用的binlog缓存的事务的数量,使用show status like ‘binlog_cache_use’查看(show status命令显示了所有连接到mysql服务器的状态值);
⑥.binlog_cache_disk_use:如果binlog_cache_use不够用,则在磁盘上缓存,应该尽量避免;
⑦.binlog_do_db:设置master-slave时使用;
⑧.binlog-ignore-db:设置哪个数据库不记录日志;
⑨.sync_binlog:缓存与硬盘的同步频率(commit多少下同步一次,0表示服务器自动控制);
⑩.binlog_format:二进制日志的格式;
（8）.查看当前二进制文件的名称和大小,show binary/master logs;
（9）.如果不指定二进制日志文件的位置,默认存放在data文件夹下,日志文件是:mysql-bin.xxxxxx,索引文件是mysql-bin.index;
（10）.如果要切换日志的话,执行flush logs命令;
（11）.初始化二进制日志系统,从新生成:reset master命令;
（12）.删除某个日志文件:purge binary logs [before ‘datetime’ / to ‘log_name’] 删除指定日期之前的和删除指定文件之前的日志文件;
（13）.设置日志文件的失效期:参数为–expire_logs_days,set global expire_log_days=n,N天前的日志自动删除;
（14）.二进制日志的格式
①.查看格式:show [global] variables like ‘binlog_format’;
②.设置日志格式:set [global] binlog_format = statement|row|mixed;
③.查看binlog中的事件:show binlog events in ‘mysql-bin.000002’ from 0;
④.使用mysqlbinlog程序打开;
5.审计日志(audit log):
（1）.用于记录企业版基于策略的审计信息;审计日志是作为企业版插件提供的;
（2）.由–audit-log选项和audit_log_file选项来控制;
（3）.审计过程会不断写入审计日志,直到将该插件删除,或者通过audit_log_policy=NONE 选项设置关闭审计;
（4）.在服务器启动时使用audit_log=FORCE_PLUS_PERMANENT作为选项,可以防止删除该插件;
补充:
6.InnoDB重做日志(innodb redo log);
（1）.与innodb数据引擎相关;
（2）.用来实现灾难恢复(crash recovery),突然断电会导致innodb表空间中的数据没有写到磁盘上,通过执行redo log能够重新执行这些操作来恢复数据;
（3）.提升innodb的i/o性能,innodb引擎把数据和索引都载入到内存中的缓冲池中,如果每次休息数据和索引都需要更新到磁盘,必定会增加i/o请求,而且因为每次更新的位置都是随机的,磁头需要频繁的定位导致效率很低,所以innodb每处理完一个事务后只添加一条日志log,另外有一个线程负责智能的读取日志文件并批量更新到磁盘上,实现最高效的磁盘写入;
（4）.系统变量:
①.innodb_log_buffer_size:日志缓冲区的大小;
②.innodb_log_file_size:日志文件的大小;
③.innodb_log_files_in_group:一组日志中有几个文件:
- 文件名为ib_logfileX(X从0开始一次增加);
- 先关闭数据库服务:>mysqladmin shutdown(mysql.server stop);
- 把data目录下的ib_logfile文件移动走:>mv ib_logfile /tmp;
- 在配置文件中添加innodb_log_files_in_group=n的参数;
- 启动数据库服务:>mysqld –defaults-file=./my.cnf –user=mysql(mysql.server start);
- 可以查看error log文件观察启动过程;
④.innodb_log_group_home_dir:日志存放的性对路径(相对于$MYSQL_HOME/mysql/data目录,即datadir目录);
- 关闭服务器;
- 在配置文件中添加此参数,并指定路径;
- 启动服务器;
（5）.innodb_flush_log_at_trx_commit:根据不同的数据安全级别去设定.
1.0:日志缓冲每秒一次的被写入到日志文件,并且对日志文件做到磁盘操作的刷新,但是在一个事务提交不做任何修改;
2.1:每个事务提交时,日志缓冲被写到日志文件,对日志文件做到磁盘操作的刷新;
3.2:每个事务提交后,日志缓冲被写到日志文件,但不对日志文件做到磁盘操作刷新,对日志文件每秒刷新一次;
4.查看此变量:>show variables like ‘innodb_flush_log_at_trx_commit’;
（6）.innodb_os_log_written:写入到文件日志的数据量,使用show status查询;
（7）.innodb_os_log_fsyncs:写入到磁盘的次数,使用show status查询;
日志文件用法列表
二进制日志记录
1.二进制日志包含描述数据库更改(如创建数据库或更改表数据)的“事件”;二进制日志 还包含可能做出更改的语句的事件(例如,没有匹配行的DELETE);该日志还包含有关 每条更新语句所用时间的信息;
2.二进制日志有两个重要用途:复制和数据恢复;
3.MySQL使用日志传送复制解决方案;使用日志传送系统时,可以将主系统上发生的所有数据更改存储在二进制日志中,然后通过从系统检索这些数据更改,并根据接收到的这些日志文件执行更改;
4.可以实时下载日志文件并执行内容;即,只要生成日志文件事件,就将其发送到连接的从系统供执行;由于网络传播存在延迟,从系统可能需要几秒到几分钟(最坏的情况)时间来接收更新;在理想的情况下,延迟会在一秒以内;
5.发生以下事件之一时,二进制日志会轮转:
（1）.重新启动MySQL服务器;
（2）.达到允许的最大大小(max_binlog_size);
（3）.发出了FLUSH LOGS SQL命令;
6.二进制日志独立于存储引擎,不管使用的存储引擎是哪个(即InnoDB或MyISAM),MySQL复制都会工作;
二进制日志记录格式
1.基于语句的二进制日志记录:
（1）.包含实际SQL语句
（2）.包括DDL(CREATE,DROP等)和DML(UPDATE,DELETE等)语句;
（3）.相对较小的文件保存磁盘空间和网络带宽;
（4）.并非所有复制的语句都会在远程计算机上正确重放;
（5）.要求主系统和从系统上复制的表和列完全相同(或者符合多个限制条件);
2.基于行的二进制日志记录:
（1）.指示对单个表行的影响情况;
（2）.正确重放所有语句,即使对于在使用基于语句的日志记录时未正确复制的功能导致的更改也是如此;
3.按如下方式设置格式:SET [GLOBAL|SESSION] BINLOG_FORMAT=[row|statement|mixed|default];
注:使用mixed选项可让MySQL选取最适合单个事件的格式,MySQL通常会使用基于语句的二进制日志,但在需要时可恢复到基于行的复制;
列出二进制日志文件
1.SHOW BINARY LOGS语句可以列出当前日志文件和文件大小;
2.SHOW MASTER STATUS语句可以显示下一个事件的主状态;需要SUPER或REPLICATION CLIENT特权;
查看二进制日志内容
1.方式1:show binlog events in ‘mysql-bin.000002’ from 0;
2.方法2:mysqlbinlog mysql-bin.xxxxxx;
删除二进制日志
1.默认情况下,不会删除旧的日志文件;
2.根据存在时间删除日志:
（1）.要在二进制日志轮转过程中自动删除存在时间多于指定天数的任何二进制日志,可使用expire_logs_days设置;
（2）.也可以在选项文件中配置expire_logs_days:
[mysqld]
expire_logs_days=7
（3）.PURGE BINARY LOGS BEFORE now() – INTERVAL 3 day;
4.根据文件名删除日志:PURGE BINARY LOGS TO ‘mysql-bin.000010’;
配置企业审计
1.要安装audit_log插件:
（1）.方法1:使用INSTALL PLUGIN语法:INSTALL PLUGIN audit_log SONAME ‘audit_log.so’;
（2）.方法2:在服务器启动时设置plugin-load选项:
[mysqld]
plugin-load=audit_log.so
2.默认情况下,装入该插件就会启用日志记录;将选项audit-log设置为OFF可禁用日志记录;
3.要防止在运行时删除该插件,可设置以下选项:audit-log=FORCE_PLUS_PERMANENT;
4.日志文件命名为audit.log,默认情况下位于服务器数据目录中;要更改该文件的名称或位置,可在服务器启动时设置 audit_log_file系统变量;
5.要平衡遵从性和性能,可使用audit_log_strategy选项在SYNCHRONOUS,ASYNCHRONOUS,SEMISYNCHRONOUS和 PERFORMANCE之间进行选择;
6.如果将audit_log_rotate_on_size设置为某个大于0的数字,则当日志文件大小超出了该数量的4KB数据块大小时,将轮转日志文件;
审计日志文件
1.每个审计记录的TIMESTAMP采用UTC格式;
2.NAME属性代表事件类型;例如,“Connect”表示登录事件,“Quit”表示客户机断开连接,“Shutdown”表示服务器关闭;
3.“Audit”和“NoAudit”表示审计开始和停止的点;
4.STATUS属性提供命令状态;这与MySQL命令SHOW ERRORS显示的Code值相同;
5.有些属性仅在特定的事件类型中出现;例如,“Connect”事件包括诸如HOST,DB,IP和USER之类的属性;“Query”事件包括SQLTEXT属性;
补充:审计过滤工具;
mysqlauditgrep –users=root –query-type=SELECT –status=0 /var/lib/mysql/audit.log
欢迎关注社区 "数据库"技术主题 ，将会不断更新优质资料、文章。地址：
http://www.talkwithtrend.com/Topic/597
下载 twt 社区客户端 APP
与更多同行在一起
高手随时解答你的疑难问题
轻松订阅各领域技术主题
浏览下载最新文章资料
长按识别二维码即可下载
或到应用商店搜索“twt”
*本公众号所发布内容仅代表作者观点，不代表社区立场