疫情闭关修炼|MySQL 入门知识总结
文章来源：talkwithtrend
本资料包括 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
四、客户机和工具
命令行客户机程序
1.这几个客户机都必须运行mysqld服务器程序,客户机才能访问数据库;
2.mysql是通用的命令行客户机,用于向服务器发送SQL语句,其中包括用于管理的SQL语句;
3.mysqladmin是可帮助管理服务器的管理命令行客户机;
4.mysqlimport为LOAD DATA INFILE语句提供了命令行界面;使用该客户机可以将数据文件装入表中,而无需手动发出LOAD DATA INFILE语句;之后讲导入导出数据时会具体再讲;
5.mysqldump是用于转储数据库和表中内容的命令行客户机;使用它可备份数据库或将其复制到其他计算机;
调用命令行客户机
两种常见的选项语法格式:
1.长选项(–):双横线选项后面加等号和参数;
2.短选项(-):单横线选项后加空格和参数; mysql -V;
连接参数选项
1.-h:后跟给定主机的主机名或IP地址,用于连接到服务器(默认为localhost);
2.-C:压缩客户机和服务器之间发送的所有信息(如果两者都支持压缩);
3.–protocol:后跟用于连接到服务器的连接协议:{TCP|SOCKET|PIPE|MEMORY};
4.-P:后跟端口号,用于代替默认值(3306);
5.-S:用于设置UNIX套接字文件或在Windows上使用的命名管道的名称;
6.–shared-memory-base-name:(仅Windows)通过共享内存连接到本地服务器时所使用的共享内存的名称;此选项仅在服务器支持共享内存连接时适用;
调用mysql客户机
1.在命令行中提供凭证:
-u选项后面可带或不带空格;-p选项后面不带空格,如果对该选项使用空值,则系统会提示您输入口令;2.在登录路径中提供凭证:使用此登录路径(通过mysql_config_editor创建)的凭证;3.执行语句:mysql –login-path=admin -e “SELECT VERSION()”;4.使用特定选项文件执行:5.使用包含SQL语句的文本文件执行:- 可以使用[ > | < ]重定向流,用来运行脚本或者批处理文件;- 文件必须为纯文本格式,其中每个语句都有语句终结符;- 文件必须位于运行 mysql 客户机的主机上;- <和-e是互斥的,不能同时使用mysql 客户机:安全更新
1.可能会不小心发出一些会修改表中多个行的语句或者会返回特别大的结果集的语句,使用–safe-updates选项可帮助防止这些问题;(可以启动时设置,也可以在选项文件中设置)2.设置安全更新模式可施加以下SQL语句限制:1）.UPDATE和DELETE仅在包含WHERE子句(该子句通过键值明确标识了要更新或删除的记录)或LIMIT子句时才允许使用;2）.将单表SELECT语句中的输出限制为不超过1K行,但语句包含LIMIT子句时除外;3）.仅当MySQL为处理查询所检查的行不超过100W时,才允许使用多表SELECT语句;例子:# mysqlmysql> use db1;mysql> SELECT * FROM t1;mysql> UPDATE t1 SET id = id + 1;mysql> COMMIT;# mysql -uroot -p –safe-updatemysql> use db1;mysql> UPDATE t1 SET id = id + 1;ERROR 1175 (HY000): You are using safe update mode and you tried to update a table without a WHERE that uses a KEY columnmysql 客户机:输出格式
默认情况下,无论是以交互模式还是以批处理模式使用mysql,都会生成输出:1.交互式:当以交互模式调用mysql时,会以表格格式显示查询输出,其中使用长条和短划线显示在方框列中列出的值;–table(或 -t):生成表格输出格式,即使在以批处理模式运行时也是如此,这是交互模式的默认格式;2.批处理:在通过使用文件作为命令行中的输入源来调用mysql时,mysql会以批处理模式运行,并且在显示的查询输出中使用制表符来分隔数据值;–batch(或 -B):生成批处理模式(用制表符分隔的)输出(即使在以交互模式运行时也是如此),且不使用历史文件,这是批处理模式的默认格式;在批处理模式下,使用–raw或-r选项可禁止字符转换(例如,将换行符和回车符转换为\n或\r等转义序列),在原始模式下,将按字面值输出字符;3.使用以下选项可选择不同于以上任一默认格式的输出格式:–html(或 -H):生成 HTML 格式的输出;–xml(或 -X):生成 XML 格式的输出;mysql 客户机:MySQL客户机命令
一.列出所有的 MySQL 客户机级别命令:mysql> HELP;1.?与help一样,获得帮助信息;2.clear:清除当前输入的语句,在错误的语句后面输入\c即可;3.connect:重新连接服务器,可以指定主机名和服务器,输入connect dbname host或或者\r dbname host即可;4.delimiter:语句分隔符,设置执行语句的符号,执行delimiter $$即可;5.edit:调用vi修改sql语句,在要修改的语句后面输入\e即可;6.go:发送命令到mysql服务器;7.pager:查询内容太多,分页显示,设置:>pager less,禁止:>nopager;8.tee:把查询结果输入到一个文件中:>tee /tmp/rst.txt,禁止notee,主要用于数据库的备份脚本输出;9.prompt:改变提示符,如:prompt royalwzy>;则每次输入命令是提示符变为royalwzy>字符串(可以在配置文件中定义,prompt=xxx);10.quit/exit:都是退出客户端,输入quit,exit和\q都行;11.source:执行一个sql脚本文件,文件名为参数,source /tmp/sql.txt;12.system:执行操作系统的命令,用法:system ls -l /tmp 或者\! ls -l /tmp;13.status:查看服务器信息的状态,输入status或者\s即可;14.use:改变使用的数据库,后面跟数据库的名称,eg:use mysql;15.charset:修改字符集,可以通过status查看当前使用的字符集,eg:charset latin1;16.warnings:开启警告信息,当输入的sql语句出错时,可以通过show warning或者show errors来打印警告或者错误信息,设置:warning或者\W,关闭:nowarning或者\w;17.rehash:设置客户端自动补全功能;1）.在服务器的配置文件中[mysql]节点下,默认使用no-auto-rehash选项;2）.注释no-auto-rehash选项,添加auto-rehash选项,reboot;二.显示会话状态信息:mysql> \s;Using outfile:就是使用tee命令指定的值,把当前所有的操作和输出都重定向到一个文本;三.日志会话查询及其输出:tee my_tee_file.txt;mysql 客户机:SQL语句
一.数据定义语言(Data Definition Language, DDL)1.CREATE DATABASE/TABLE:用于创建具有给定名称的数据库或表;2.ALTER DATABASE/TABLE:可更改数据库或表的整体特性;3.DROP DATABASE/TABLE:用于删除数据库中的所有表并删除该数据库,或者用于删除特定的表;二.数据操纵语言(Data Manipulation Language, DML)1.SELECT:用于从一个或多个表中检索所选的行;2.INSERT:用于在现有表中插入新行;3.DELETE:用于删除现有表中的行;4.UPDATE:用于使用新值更新指定表中现有行的列;5.JOIN:组合使用多个表以用于SELECT,多表DELETE和UPDATE语句;mysql 客户机:有关SQL语句的帮助
获得服务器端帮助:>help contents;1.可以通过help cmd获得更详细的信息;2.获得管理操作的命令:>help administration;3.获得数据类型:>help data types;4.获得show命令的帮助:help show; ? show;5.show variables:打印系统变量,类似oracle中的show parameter;查询包含某一关键字的变量:show variables like ‘%buffer%’;设置变量的值用set命令:set global|session key=value;6.show variables和show status的区别show variables:当服务器运行之后如果没有人工干预所有的参数不会发生改变;show status:显示服务器运行过程中的动态信息,值会动态改变;7.不必逐步浏览目录列表中所列出的项来获取有关特定主题的帮助,只需给出主题作为关键字即可获得一些提示:HELP STATUS;mysql 客户机:SQL语句终结符
1.[;]和[\g]:常见的终结符,二者等效,可互换使用;2.\G:用于终止查询并以垂直方式显示查询结果,其中显示的每个输出行的每个列值均位于单独的行中;此终结符在查询生成的输出行非常宽的情况下十分有用(因为竖直格式可使结果更易阅读);3.\c:如果决定放弃正在编写的语句,则可取消该语句并返回到新的mysql>提示符下;mysql 客户机:特殊语句终结符
mysql 客户机:重新定义提示符1.可以更改默认提示符,将当前信息放入提示符中,例如用户(\u),主机(\h)和数据库(\d);2.PROMPT关键字之后第一个空格后面的所有内容都将成为提示符字符串的一部分,包括其他空格;该字符串可包含特殊序列;1.3.要将提示符恢复为默认值,请指定不包含参数的PROMPT或\R;eg:prompt (\u@\h)[\d]\>mysql 客户机:使用脚本文件
1.脚本文件应该为纯文本文件,其中所包含语句的格式要与以交互模式输入的语句的格式相同;具体来说,每个语句都必须以 终结符结束;2.SOURCE命令后的文件名无需用引号括起;mysqladmin客户机
查看帮助:1.create databasename:创建数据库;2.debug:把bug信息写入日志文件;3.drop databasename:删除数据库;4.extended-status:列出服务器的附加信息;5.flush-hosts:刷新主机缓存;6.flush-logs:刷新所有的日志;7.flush-status:清空状态变量;8.flush-tables:刷新表;9.flush-threads:刷新线程缓存;10.flush-privileges:重新加载授权表,相当于reload;11.kill id,id,…:杀掉mysql的进程;12.password [new-password]:以当前格式修改密码;13.old-password [new-password]:以旧的格式修改密码;14.ping:检查mysqld是否活动;15.processlist:列出所有活动的进程;16.reload:重新加载授权表;17.refresh:刷新所有的表,并重新打开日志文件;18.shutdown:关闭Server;19.status:查看服务器的状态信息;20.start-slave:启动slave;21.stop-slave:停止slave;22.variables:打印变量状态;23.version:查看服务器版本信息;MySQL工具
1.MySQL Workbench:一个可视化的下一代数据库设计应用程序,可用于高效设计,管理和记录数据库结构;它有开源和商业两种版本;2.MySQL Proxy:一个位于客户机与MySQL服务器之间的简单程序,可监视,分析或传输客户机与服务器之间的通信;MySQL Proxy 的灵活性使其具有多种用途,包括负载平衡/故障转移/查询分析/查询过滤和修改以及其他操作;该工具当前尚不可用于生产;3.MySQL Enterprise Monitor:MySQL的可视化企业监视系统,可用于检测MySQL服务器,通知潜在问题并就如何修复这些问题提供建议;4.MySQL Enterprise Backup:使用该工具可以执行联机非阻塞“热”备份,从完整备份恢复数据,还支持创建压缩的备份文件;5.MySQL Cluster Manager:通过自动执行常见的管理任务来简化MySQL Cluster Carrier Grade Edition数据库的创建和管理;TIPS:345工具仅在MySQL商业版本中提供;MySQL Enterprise Monitor
1.Enterprise Monitor可帮助管理多节点(可水平伸缩)环境中的多台MySQL服务器,调整当前MySQL服务器,发现MySQL数据库应用程序中存在的问题,并在这些问题变成严重问题或代价高昂的故障之前将其修复;2.此Web GUI应用程序可主动监视企业数据库环境,并就MySQL如何增强MySQL所推动系统的安全性,优化其性能和减少其停机 时间提供专家建议;3.Enterprise Monitor可监视各种类型的配置,从单个MySQL服务器直到支持繁忙Web站点的大型MySQL服务器群;— MySQL Enterprise Monitor:系统信息显示板;— MySQL Enterprise Monitor:访问;— MySQL Workbench;MySQL Workbench针对三种主要功能为DBA和开发者提供了基于GUI的跨平台集成工具环境:1.SQL开发有助于进行以下任务:编辑和执行SQL查询和脚本;创建或变更数据库对象;编辑表数据;2.数据库设计和建模有助于进行以下任务:执行增强型实体关系 (enhanced entity relationship, EER)建模;编辑和执行SQL查询和脚本;设计,生成和管理数据库;3.服务器管理(取代了MySQL Administrator)有助于进行以下任务:启动和停止服务器;编辑数据库服务器配置;管理用户;导入和导出数据;— MySQL Workbench:GUI窗口;— MySQL Workbench:访问;标准版(SE)提供了:1.OSS版本的商业扩展;2.高级功能;3.需要付费;MySQL Proxy
1.MySQL Proxy使用MySQL网络协议连接到网络,并提供一台或多台服务器与一台或多台MySQL客户机之间的通信;在最基本的MySQL Proxy配置中,可以执行以下操作:仅将查询从客户机传递到MySQL服务器,然后返回;对使用该协议的任何与MySQL兼容的客户机,可不经修改地使用MySQL Proxy;这包括mysql命令行客户机,任何使用MySQL客户机库的客户机以及任何支持MySQL网络协议的连接器;监视和变更客户机和服务器之间的通信;使用查询拦截添加分析,插入其他查询并删除其他结果;交换信息的拦截可脚本化;2.使用该代理可以对查询执行额外的监视,过滤或处理,而无需对客户机做出任何修改,客户机甚至不会知道正在与其通信的根本不是真正的MySQL服务器;3.MySQL Proxy仍在开发中,尚未作为GA软件发行;可以了解OneProxy;— MySQL连接器;— 第三方API;1.大多数第三方API均基于C客户机库,并针对其他某种语言提供绑定;2.虽然MySQL开发团队的成员经常与这些产品的开发者密切合作,但是这些API尚未得到Oracle的正式支持;五、数据类型
概览1.在MySQL中,可用的数据类型分为四个主要类别:（1）数值;（2）字符;（3）二进制;（4）时间;2.在每个类别中,存在多种特定的数据类型,这些数据类型使用的内存大小和磁盘空间各不相同,因此会对性能产生不同的影响;3.对于单个记录,为列选择最佳数据类型所产生的性能影响相对较小,但随着数据库的增大,这些较小的影响可能会汇聚成大的影响;应在设计过程中事先考虑这些影响,以免造成性能问题;4.MySQL还支持“空间扩展”存储引擎功能;通过空间扩展,能够以字符和二进制格式生成,存储和分析地理特征;5.数据类型的ABC要素:（1）Appropriate(适当):需要以最适合数据所代表的项的类型来表示数据;（2）Brief(简洁):选择所用存储空间最少的数据类型。这可节省资源并提高性能;（3）Complete(完整):选择的数据类型应分配有可存储特定项的最大可能值的充足空间;数值数据类型1.数值数据类型类:（1）整数:整数没有小数部分;即,没有小数位的单个整数值;（2）浮点数(FLOAT/DOUBLE):表示包含整数部分,小数部分或同时包括二者的近似值数值;此类数据类型使用服务器主机的CPU所用的本机二进制浮点格式(IEEE 754)来表示值;该数据类型用于存储和计算会很高效,但值会存在舍入误差;如果列可为空,则默认值为NULL;如果列不可为空,则默认值为0(数值零);（3）定点数(DECIMAL):包含整数部分,小数部分或同时包括二者;DECIMAL列中的所有值均包含相同的小数位数,并且完全按给定方式进行存储;DECIMAL值存储起来不如浮点数值高效,但DECIMAL值没有舍入误差,因此更加精确;通常用于存储货币值(其中每个值的精度比其存储大小更加重要);
（4）BIT:BIT列规范规定了一个宽度,指明每个值的位数(1至64位);2.“精度”和“范围”是适用于浮点值和定点值(这两种类型可以同时包含整数部分和小数部分)的术语:（1）精度:有效位数;（2）范围:小数点右侧的位数;TIPS:可以使用=号跟浮点值进行比较;但由于可能会出现舍入误差,因此结果可能并不总是与预期一样;字符串数据类型1.这些类型在以下几个方面有所不同:（1）数据是以固定长度格式还是可变长度格式存储;（2）可存储的最大长度;（3）该类型是否支持非结构化字符串值;2.为存储字符串数据,MySQL提供了以下数值数据类型存储类:（1）文本:用于表示真实的字符串数据类型;可以使用此类型存储非结构化且格式自由的字符串(如果其长度符合已定义的空间量);（2）整数(枚举/集合):用于表示结构化字符串类型;称为“结构化”的原因是,存储在这些类型的列中的值必须通过您所提供的值列表构建,从而定义数据类型;字符集和排序支持1.字符串具有以下特征:（1）序列由特定字符集中的字符组成;（2）多字节字符集中的每个字符所需的字节数可能是固定的,也可能是可变的;（3）比较基于对字符串所关联的字符集的排序;（4）多字节字符比较以字符而不是字节为单位执行;（5）排序将验证字符的大写版本和小写版本是否等效;（6）排序将决定同一个字符的不同重音标记是否等效;（7）排序可以为二进制,其中基于数值字符值进行比较;2.MySQL具有一个包含大量可供选择的字符集和排序的列表;选择正确与否会对性能有很大的影响;要查看可用的字符集:SHOW CHARACTER SET;3.使用排序选择可以为相同的字符集选择不同的排序顺序;例如,显示所有latin1字符集:SHOW COLLATION LIKE ‘latin1%’;二进制字符串数据类型1.与字符串不同,组成此类二进制字符串值的字节不代表字符;因此,二进制字符串没有附加的字符语义,而且缺少字符串类所表示的字符集和整理信息;2.在MySQL中,BLOB与TEXT类型非常相似,没有附加的字符集和排序;时间数据类型1.YYYY,MM,DD,hh,mm,ss和uuuuuu分别表示“年”,“月”,“日”,“小时”,“分钟”,“秒”和可选的“秒的小数位”;2.通过为该类型提供一个参数,可声明秒的小数位(可选);例如,TIME(3)是TIME类型,其中秒的小数位部分最多可达三位;3.TIMESTAMP值采用UTC进行存储(将根据需要转换为当地时间或从当地时间进行转换),其范围为从1970-01-01 00:00:00.000000至2038-01-19 03:14:07.999999;4.可以定义DATETIME和TIMESTAMP以自动记录当前日期和时间,而不管对行执行INSERT或UPDATE操作的时间为何时;5.TIMESTAMP与DATETIME的对比:（1）TIMESTAMP列值的范围比DATETIME列值的范围小,因此存储每个值所需的字节数更少;（2）通过为不允许出现NULL的TIMESTAMP指定NULL值,可以将其设置为当前日期和时间;例子:CREATE TABLE t_time
(
id INT NOT NULL,
t1 TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
t2 TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
t3 TIMESTAMP NOT NULL ON UPDATE CURRENT_TIMESTAMP
);
mysql> INSERT INTO t_time(id) VALUES(1);
mysql> COMMIT;
mysql> SELECT * FROM t_time;
+—-+———————+———————+———————+
| id | t1 | t2 | t3 |
+—-+———————+———————+———————+
| 1 | 2015-08-19 14:00:42 | 2015-08-19 14:00:42 | 0000-00-00 00:00:00 |
+—-+———————+———————+———————+
mysql> UPDATE t_time SET t1 = ‘2000-01-01 00:00:00’ WHERE id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> COMMIT;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT * FROM t_time;
+—-+———————+———————+———————+
| id | t1 | t2 | t3 |
+—-+———————+———————+———————+
| 1 | 2000-01-01 00:00:00 | 2015-08-19 14:00:42 | 2015-08-19 14:01:43 |
+—-+———————+———————+———————+
1 row in set (0.00 sec)空间数据类型MySQL实现了由开放地理空间协会(Open Geospatial Consortium, OGC)提出的“包含几何类型的SQL”环境的子集:1.用于单个几何体值:（1）GEOMETRY:层次结构的根类,任何类型的值;（2）POINT:坐标空间中的单个位置;（3）CURVE:一维几何体,由点组成的序列;（4）LINESTRING:点之间具有线性内插的曲线;（5）SURFACE:二维几何体;（6）POLYGON:表示多边几何体的平面;2.用于保存几何体值的集合:（1）MULTIPOINT:Point元素;（2）MULTICURVE:Curve元素;（3）MULTILINESTRING:LineString元素;（4）MULTISURFACE:Surface元素;（5）MULTIPOLYGON:Polygon元素;（6）GEOMETRYCOLLECTION:任意类型的几何体;将数据类型设置为NULL1.在SQL中,对表达式求值的结果可以为null;null值是一个特殊的值,表示值无法进行计算或未知;2.何时使用NULL:在数据库设计的开始阶段,当列出要包含在内的数据时,发现某些数据可能不适用于所有列;3.何时不应使用NULL:在某些情况下,不应允许列中使用null值;（1）最常见的情况即该列为主键时;（2）另一个例子是:当列必须包含值时,数据库设计才有意义;（3）NULL值不保存到索引中;
mysql> SHOW TABLES;
mysql> SHOW TABLES FROM mysql;
mysql> SHOW TABLES FROM INFORMATION_SCHEMA;
mysql> SHOW COLUMNS FROM CountryLanguage;
mysql> SHOW FULL COLUMNS FROM CountryLanguage\GDESCRIBE语句;1.DESC table_name等效于SHOW COLUMNS FROM table_name;但是,SHOW COLUMNS支持可选的LIKE和WHERE子句,而DESCRIBE不支持;2.当指定表名称作为参数时,EXPLAIN等效于DESCRIBE:mysql> EXPLAIN table_name;mysqlshow客户机;1.mysqlshow客户机为各种格式的SHOW语句提供了一个命令行界面,这些语句用于列出数据库的名称,数据库中的表或有关表列或索引的信息;2.mysqlshow客户机的选项部分可包含任一标准连接参数选项,例如–host或–user;如果默认连接参数不适合,则必须提供选项;mysqlshow也接受特定于其自身运行的选项;3.使用–help选项调用mysqlshow可查看其选项的完整列表;4.mysqlshow所执行的操作取决于已提供的非选项参数的数量;mysqlshow示例;1.在没有参数的情况下,mysqlshow将显示类似于SHOW DATABASES的结果;2.在使用单个参数的情况下,mysqlshow将该参数解释为数据库名称,并针对该数据库显示类似于SHOW TABLES的结果;3.在有两个参数的情况下,mysqlshow将参数解释为数据库和表名称,并针对该表显示类似于SHOW FULL COLUMNS的结果;4.在有三个参数的情况下,其输出与两个参数的情况相同,不同之处在于:mysqlshow将第三个参数当做列名称,且仅针对该列显示SHOW FULL COLUMNS输出;5.如果命令行中最后的参数包含特殊字符,mysqlshow会将该参数解释为模式,且仅显示与该模式匹配的名称;特殊字符包括:%或*(匹配任一字符序列),以及_或?(匹配任一单个字符);本示例中的命令仅显示那些名称始于w的数据库。
推荐阅读