一、基本介绍:

       MySQL是一款开源的关系数据库管理系统,最早有瑞典的MySQL AB公司开发,2008年被SUN公司收购,2009年,SUN公司被Oracle公司收购。MySQL性能高、成本低、安全稳定,被广泛应用在中小型网站中,随着MySQL的性能不断提高,像维基百科、Google、Facebook等大公司也正在使用MySQL(维基百科现在以迁移到MariaDB)。

       MySQL使用C和C++编写,并为多种语言提供了API,支持多线程,充分利用CPU资源,支持多用户,提供TCP/IP/、ODBC和JDBC等等多种数据库连接途径,且提供了用于管理、检查、优化数据库操作的管理工具,可以处理拥有上千万条记录的大型数据库。

二、MySQL体系结构:

wKioL1ND_YWCeulJAAJ8fPu-W-g198.jpg

1、Connectors指的是不同语言中与SQL的交互

2、Management Serveices & Utilities: 系统管理和控制工具

3、Connection Pool: 连接池

   管理缓冲用户连接,线程处理等需要缓存的需求。

4、SQL Interface: SQL接口

   接受用户的SQL命令,并且返回用户需要查询的结果。比如select from就是调用SQL Interface。

5、Parser: 解析器。

   SQL命令传递到解析器的时候会被解析器验证和解析。解析器是由Lex和YACC实现的,是一个很长的脚本。

主要功能:

   a . 将SQL语句分解成数据结构,并将这个结构传递到后续步骤,以后SQL语句的传递和处理就是基于这个结构的 。

   b.  如果在分解构成中遇到错误,那么就说明这个sql语句是不合理的。

6、Optimizer: 查询优化器

   SQL语句在查询之前会使用查询优化器对查询进行优化。他使用的是“选取-投影-联接”策略进行查询。

   用一个例子就可以理解: select uid,name from user where gender = f;

       这个select 查询先根据where 语句进行选取,而不是先将表全部查询出来以后再进行gender过滤。

       这个select查询先根据uid和name进行属性投影,而不是将属性全部取出以后再进行过滤。

       将这两个查询条件联接起来生成最终查询结果。

7、Cache和Buffer: 查询缓存

   如果查询缓存有命中的查询结果,查询语句就可以直接去查询缓存中取数据。

   这个缓存机制是由一系列小缓存组成的。比如表缓存,记录缓存,key缓存,权限缓存等。

8、Engine :存储引擎

   存储引擎是MySql中具体的与文件打交道的子系统。也是Mysql最具有特色的一个地方。

   Mysql的存储引擎是插件式的。它根据MySql AB公司提供的文件访问层的一个抽象接口来定制一种文件访问机制(这种访问机制就叫存储引擎)。

   现在有很多种存储引擎,各个存储引擎的优势各不一样,最常用的MyISAM,InnoDB,BDB。

   默认下MySql是使用MyISAM引擎,它查询速度快,有较好的索引优化和数据压缩技术。但是它不支持事务。

   InnoDB支持事务,并且提供行级的锁定,应用也相当广泛。

   Mysql也支持自己定制存储引擎,甚至一个库中不同的表使用不同的存储引擎,这些都是允许的。

   后面我会详细介绍这些引擎各自的特性。

从上图我们换个角度(微观)来解析一下它:

wKioL1NEDvHxo2cbAAH4amhifFk933.jpg

1、连接管理器:

   当客户发起请求后由连接管理器负责接收这个请求;

2、线程管理器:

   MySQL是以单进程多线程的方式运作的,于是它将会为每一个用户生成一个线程

3、用户模块

   它的作用是进行身份进行认证,管理用户,一旦访问完成后,退出了,线程就会被线程管理器回收到连接池中;其实用户最终要用户模块打交道,只有第一次刚刚发起连接时才需要认证;后续的SQL语句都与用户模块打交道,而不再是连接管理器了;

4、命令派发器:

   一旦用户认证通过连接进来后它会将那些SQL语句派发到:

       "查询缓存",如果查询缓存一旦命中,那么结果也就直接返回客户端;

       "日志记录",一旦我们开启了查询日志,待我们查询完成后需要将执行操作记录日志;

5、分析器:

   如果上面的由命令派发到查询缓存没有命中,那么命令派发器会将SQL语句转交给分析器,由分析器进行SQL语句分析;这些语句有可能包含:DML、DDL,这些不同的语句就需要有不同的机制来处理;

   优化器:分析器分析出这是一条查询语句那么由优化器来负责处理;SELECT

   表修改模块:如果是更新或修改表中数据的操作,则由表修改模块负责;UPDATE/INSET/DELETE/REPLACE.....

    表维护模块:如果表需要修复则由表维护模块负责;

    复制模块:如果分析出是表或数据复制操作,则有复制模块操作;Replication(要启用该模块功能才起作用)

    状态报告模块:优化器之所以能够完成优化,它是根据Mysql服务器不断收集状态信息得来的,所以由此模块负责状态报告;

6.访问控制模块:

    上面的用户认证授权通过后并不意味着就有权限访问任意数据,这里通过访问控制模块再次对权限进行检查;

7.表管理器

     如果访问控制检查通过,真正执行操作则有表管理器来负责,无论是增删查改等这样的语句;

8.存储引擎

     表管理器执行操作则会通过它的内部接口,这个操作要依赖于存储引擎; 存储引擎这个抽象接口将用户请求转交给各个对应使用的存储引擎,完成操作最终将其结果返回客户端。


OK,了解了MySQL的基本体系架构后我们尝试安装一下吧,我们知道安装mysql的方式有三种:二进制源码包安装、RPM包安装以及编译安装前面的那些试验当中也演示到了二进制源码和rpm包安装,这里我将采用源码包的方式安装:

说明:

    MySQL5.5版本开始,通过./configure进行编译配置方式已经被取消,取而代之的是cmake工具。

因此,我们首先要在系统中源码编译安装cmake工具,我这里使用的cmake版本为cmake-2.8.10.2,mysql为5.5.36

  注意:开发环境

1.获取cmake,安装之

[root@mysql ~]# wget http://down1.chinaunix.net/distfiles/cmake-2.8.10.2.tar.gz
[root@mysql ~]# tar -xf cmake-2.8.10.2.tar.gz -C /usr/src/
[root@mysql ~]# cd /usr/src/cmake-2.8.10.2/
[root@mysql cmake-2.8.10.2]# ./configure

2. 安装mysql前的系统设置

建立mysql安装目录及数据存放目录

安装路径:

[root@mysql ~]# mkdir /usr/local/mysql

数据库路径:

[root@mysql ~]# mkdir /data/mysql
#建议此目录挂载至LVM上面,这样对于后期的扩容有很大的帮助!

创建用户和用户组

[root@mysql ~]#groupadd mysql
[root@mysql ~]#useradd -g mysql mysql

赋予数据存放目录权限

[root@mysql ~]# chown mysql:mysql -R /data/mysql


3.编译安装 MySQL 5.5

通过http://www.mysql.com/downloads/mysql官方网址或国内的sohu镜像下载软件包.

[root@mysql ~]# wget http://mirrors.sohu.com/mysql/MySQL-5.5/mysql-5.5.36.tar.gz
[root@mysql ~]# cd /usr/src/
[root@mysql src]# ln -sv mysql-5.5.36 mysql
`mysql' -> `mysql-5.5.36'
[root@mysql ~]# cd mysql
[root@mysql mysql]#  cmake . -DCMAKE_INSTALL_PREFIX=/usr/local/mysql \
-DMYSQL_DATADIR=/mydata/data \
-DSYSCONFDIR=/etc \
-DWITH_INNOBASE_STORAGE_ENGINE=1 \
-DWITH_ARCHIVE_STORAGE_ENGINE=1  \
-DWITH_BLACKHOLE_STORAGE_ENGINE=1 \
-DWITH_READLINE=1  \
-DWITH_SSL=system \
-DWITH_ZLIB=system \
-DWITH_LIBWRAP=0 \
-DMYSQL_UNIX_ADDR=/tmp/mysql.sock -DDEFAULT_CHARSET=utf8  \
-DDEFAULT_COLLATION=utf8_general_ci
[root@mysql ~]# make && make install

makemake install的时候可以看到进度百分比,感觉这一点要比configure方式要好。

参数说明:

指定安装文件的安装路径时常用的选项:

-DCMAKE_INSTALL_PREFIX=/usr/local/mysql
-DMYSQL_DATADIR=/data/mysql
-DSYSCONFDIR=/etc

默认编译的存储引擎包括:csv、myisam、myisammrg和heap。若要安装其它存储引擎,可以使用类似如下编译选项:

-DWITH_INNOBASE_STORAGE_ENGINE=1
-DWITH_ARCHIVE_STORAGE_ENGINE=1
-DWITH_BLACKHOLE_STORAGE_ENGINE=1
-DWITH_FEDERATED_STORAGE_ENGINE=1

若要明确指定不编译某存储引擎,可以使用类似如下的选项:

-DWITHOUT_<ENGINE>_STORAGE_ENGINE=1

比如:

-DWITHOUT_EXAMPLE_STORAGE_ENGINE=1
-DWITHOUT_FEDERATED_STORAGE_ENGINE=1
-DWITHOUT_PARTITION_STORAGE_ENGINE=1

如若要编译进其它功能,如SSL等,则可使用类似如下选项来实现编译时使用某库或不使用某库:

-DWITH_READLINE=1
-DWITH_SSL=system
-DWITH_ZLIB=system
-DWITH_LIBWRAP=0

其它常用的选项:

-DMYSQL_TCP_PORT=3306
-DMYSQL_UNIX_ADDR=/tmp/mysql.sock
-DENABLED_LOCAL_INFILE=1
-DEXTRA_CHARSETS=all
-DDEFAULT_CHARSET=utf8
-DDEFAULT_COLLATION=utf8_general_ci
-DWITH_DEBUG=0
-DENABLE_PROFILING=1

如果想清理此前的编译所生成的文件,则需要使用如下命令:

make clean
rm CMakeCache.txt

makemake install的时候可以看到进度百分比,感觉这一点要比configure方式要好。


首先说一下mysql主配置文件的特点(按以下序号次序来寻找)

1  /etc/my.cnf  集中式配置文件,为多个命令提供配置
2  /etc/mysql/my.cnf
3  $MYSQL_HOME/my.cnf
4  /path/to/file when defaults-extra-file=/path/to/file is specified
5   ~/.my.cnf

   如果发现多个配置文件, MySQL会将所有的配置文件组合起来,将他们的合合集作为配置文件来使用 。如果配置出现冲突,则以读取最后一个配置文件中的配置为准。          

   如果启动服务的时候想不使用默认的数据存放目录,需要在初始化的时候使用datadir指令来定义。

让mysql服务启动的时候读取额外的配置文件:

mysqld_safe --defaults-extra-file=/etc/mysql/my_instance.cnf。


将源码安装的apache的二进制文件的路径加入环境变量中,并通过source对文件重读:

[root@mysql mysql]# vim /etc/profile.d/mysqld.sh
export PATH=$PATH:/usr/local/mysql/bin:PATH
[root@mysql mysql]# source /etc/profile.d/mysqld.sh

将源码安装的mysql的头文件导入到系统找得到的位置:

[root@mysql mysql]# mkdir  /usr/local/include/mysql
[root@mysql mysql]# ln -sv /usr/local/mysql/include /usr/local/include/mysql
/usr/local/include/mysql/include' -> `/usr/local/mysql/include'
[root@mysql mysql]#

将源码安装的apache的库文件导出给系统找得到的位置

[root@mysql mysql]# vim /etc/ld.so.conf.d/mysqld.conf
/usr/local/mysql/lib  #加入此行

将源码安装的apacheman手册的导入系统找得到的位置

[root@mysql mysql]# vim /etc/man.config
MANPATH /usr/local/mysql/man

创建my.cnf配置文件

[root@mysql mysql]# cp support-files/my-large.cnf /etc/my.cnf
cp: overwrite `/etc/my.cnf'? y
[root@mysql mydata]# vim /etc/my.cnf
datadir = /data/mydata  #加入此行

执行初始化脚本对数据库进行初始化:

[root@mysql mysql]# scripts/mysql_install_db --user=mysql --basedir=/usr/local/mysql/ --datadir=/data/mydata/

创建管理MySQL数据库的shell脚本

[root@mysql mysql]# cp support-files/mysql.server /etc/rc.d/init.d/mysqld
[root@mysql mysql]# chmod +x /etc/rc.d/init.d/mysqld

添加为开机自启动项

[root@mysql mysql]# chkconfig --add mysqld
[root@mysql mysql]# chkconfig mysqld on

启动mysql

[root@mysql mysql]# service mysqld start
Starting MySQL..              [  OK  ]
[root@mysql mysql]# netstat -an | grep :3306
tcp        0   0    0.0.0.0:3306    0.0.0.0:*        LISTEN
[root@mysql mysql]#

ok!至此我们源码包编译安装操作已经完成,接下来就是对他进行简单配置即可:


在mysql安装完之后,要做的第一件事就是为管理员账户设定密码,并删除两个匿名用户;一般mysql安装完成之后会生成三个管理员用户账号,和两个匿名用户账号。

四个个管理员用户账号:

root@127.0.0.1
root@localhost
root@hostname
root@::1

两个匿名用户账号:

''@localhost
''@hostname

删除所有匿名用户

mysql> DROP USER ''@'localhost';
mysql> DROP USER ''@'www.magedu.com';

用户帐号由两部分组成:username@hostname
 host还可以使用通配符:
  %: 任意长度的任意字符
  _: 匹配任意单个字符
给所有的root用户设定密码:
   第一种方式:    

mysql> SET PASSWORD FOR username@hostname = PASSWORD('your_passwrod');

   第二种方式:

mysql> UPDATE user SET password = PASSWORD('your_password') WHERE user = 'root';
mysql> FLUSH PRIVILEGES;

   第三种方式:

# mysqladmin -uUserName -hHost password 'new_password' -p
# mysqladmin -uUserName -hHost -p flush-privileges

连入MySQL服务器
    mysql client <--mysql protocol--> mysqld
mysqld接收连接请求:
  本地通信:客户端与服务器端位于同一主机,而且还要基于127.0.0.1(localhost)地址或lo接口进行通信;
  远程通信:客户端与服务器位于不同的主机,或在同一主机便使用非回环地址通信
客户端工具:mysql, mysqladmin, mysqldump, mysqlcheck

帮助信息:
-?
-I
--help
**********************************************************
(单字符后面不带空格)
--user,-u 指定用户访问mysqld
--host,-h 指定服务器的名字
--password,-p
--protocol 指定连接协议(tcp/socket/pipe/memory)
使用--protocol socket时,本地客户端是通过/tmp/mysql.sock(sock文件位置可能不同)连接本地mysqld服务
--port 远程服务器的连接端口
--socket
-D db_name 直接使用某个数据库作为默认数据库
--datebase=...
--compress 数据在服务器端和客户端之间压缩传输
--default-character-set=charset_name 指定字符集
-V  显示版本号
-v命令执行时显示详细信息
--ssl-ca=/path/to/ssl_ca_file证书存放位置 (为了验证对方的证书)
--ssl-capath=/path/to/ca_dir证书目录
--ssl-cert=/path/to/cert_file//自己的证书
--ssl-cipher=cipher_list   加密方式
--ssl-key=/path/to/key_file自己的私钥
--ssl-verify-server-cert    验证服务器证书


mysql -e 不登陆mysql直接执行命令

[root@mysql ~]# mysql -e 'SHOW DATABASES;'
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+
[root@mysql ~]#


mysqladmin常用选项:

create 创建数据库
drop   删除数据库
debug  用于输出调试信息
status 显示全局变量和他的值
--sleepn  每个n秒显示一次
--count n  一共显示n次
例如:#mysqladmin --sleep 3 --count 3 status
extended-status 显示所有系统变量和值/运行状态属性信息
flush-hosts  清空hosts内部信息
flush-logs   做二进制日志滚动
flush-privileges 刷新
flush-status  重新开始计数
flush-tables  关闭表
flush-threads 重置线程缓存池
kill结束某个客户端线程
processlist 列举所有的进程和线程
password 为某用户设置密码
ping查看是否处于运行状态
reload   flush-privilege
refresh    flush-hosts&flush-logs
shutdown关闭指定mysql服务器
start-slave 启动从服务器
stop-slave
variables 显示全局变量

mysql的快捷键:

Ctrl + w: 删除光标之前的单词
Ctrl + u: 删除光标之前至命令行首的所有内容
Ctrl + y: 粘贴使用Ctrl+w或Ctrl+u删除的内容
Ctrl + a: 移动光标至行首
Ctrl + e: 移动光标至行尾

数据类型的功用:

1、存储的值类型;
2、占据的礁存储空间;
3、定长,变长;
4、如何被索引及排序;
5、是否能够被索引;

SQL语言组成部分:

DDL:数据库定义语言
DML:
完整性定义语言:DDL的一部分功能
    主键、外键、惟一键、条件、非空、事务
视图定义:虚表,存储下来的SELECT语句
事务控制
嵌入式SQL和动态SQL
DCL:授权


数据字典:系统编目(system catalog, )

保存数据库服务器上的元数据(非数据本身没有关系,但是可以通过其管理数据)

初始化mysql后生成的"mysql"

元数据:
关系的名字
每个关系的各字段的名字
各字段的数据类型和长度
约束
每个关系上的视图的名字及视图的定义
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 
授权用户的名字
用户授权和账户信息
统计类的数据
    每个关系字段的个数
    每个关系中行数
    每个关系的存储方法
保存原数据的数据库:
    information_schema
    performance_schema (类似于Linux文件系统中的proc)


三、mysql数据类型:

1.常用数据类型:

数值型:

       整型(5种)

    TINYINT

    SMALLINT

    MEDIUMINT

    INT   (大于前面的)

    BIGINT

    十进制定点数

    DESCIMAL

    单精度浮点数:

    FLOAT

    双精度浮点数

    DOUBLE

    位:按位存储

    BIT

wKiom1NEyMfzD0ZlAAF700sDddE417.jpg


字符型

===========================

作为非二进制对象存储<不区分大小写>

CHAR(定长), VARCHAR(变长)

TINYTEXT, TEXT, MEDIUTEXT, LONGTEXT

wKiom1NEyR_gWvVXAAEDpaHVTDM022.jpg

===========================

++++++++++++++++++++++++++++

作为二进制对象存储<区分大小写.没有字符集>

BINARY(0-255定长),

VARBINARY(65535变长)

TINYBLOB(Up to 255 bytes, 1bytes overhead)

BLOB(Up to 64 kb, 2bytes overhead)

MEDIUMBLOB(Up to 16M, 3bytes overhead)

LONGBLOB(Up to 4Gb, 4bytes overhead)

wKioL1NEySzwIz2TAAH0kcrqUtM307.jpg

++++++++++++++++++++++++++++

日期时间型

wKiom1NEyarDBaPTAAD-JYYdqa4140.jpg


2.常用修饰符

1)、字符串常用修饰符:只修饰字符型,不修饰二进制型

CHAR,VARCHAR和TEXT字符型常用的属性修饰符:
1.NOT NULL:非空约束
2.NULL:允许
3.DEFAULT'string'默认值,仅用于char,varchar不适用于TEXT类型
4.CHARACTER SET '字符集'
    mysql>SHOW VARIABLES LIKE '%char%' 当前系统上的字符集
    mysql>SHOW CHARACTER SET 查看所有支持的字符集
    默认情况下如果没有指定字符集它会去继承表的,如果表也没有指,就会继承库的,如果库也没有指,那么会继承服务器的。
5.COLLATION '规则'
    查看本机上面排序规则 mysql>SHOW COLLATION;
BINARY,VARBINARY和BLOB只用三种修饰符(NULL, NOT NULL, DEFAULT:不适用于BLOB)
内置类型
ENUM   存储的值字串
SET    集合,存储的是组合索引
   修饰符:NOT NULL
          NULL
          DEFAULT 'string'

2)、整型常见的修饰符

整型的常用属性修饰符:
     AUTO_INCREMENT:自动增长(前提:非空、且唯一;支持索引, 非负值)
批量插入的副作用:一次插入多行数据时,仅记录第一个值
通过mysql>SELECT LAST_INSERT_ID();查看增长值,显示结果并非精确
    mysql>TRUNCATE tb_name
     UNSIGNED:无符号
          mysql>LAST_INSERT_ID();显示结果并非精确
          mysql>TURNCATE tb_name;
浮点型常用属性修饰符
     NULL
     NOT NULL
     DEFAULT
     UNSINGNED

任何字符型必须加引号,任何数值型不能加!

3)、日期时间型的修饰符

NOT NULL
NULL
DEFAULT


四、SQL 模式:

TRADITIONAL           传统模式
STRICT_TRANS_TABLES   仅对支持事务的表,严格模式
STRICT_ALL_TABLES     对所有表都是用严格模式

系统默认使用的是空模式


设定服务器变量的值:(仅于支持动态的变量)

   支持修改的服务器变量

       动态变量

            可以在Mysql运行是修改

       静态变量

            在配置文件中修改其值,并重启后方能修改


服务器变量从其生效范围来讲,有两类:

  全局变量:服务器级别,修改之后仅对新建立的会话有效

  会话变量:会话级别,仅对当前会话有效;会话建立时,从全局继承各变量


查看服务器变量三种方式:

mysql>SHOW [{GLOBAL|SESSION}] VARIABLES [LIKE ''];
mysql>SETLECT @@{GLOBAL|SESSION}.sql_mode;
mysql>SETLECT * FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES WHERE VARIABLE_NAME='SOME_VARIABLE_NAME';

修改变量:前提:默认仅管理员有权限修改全局变量

mysql> SET {GLOBAL|SESSION} VARIABLE_NAME='VALUE';

注意:无论是全局还是会话级别的动态变量修改,在重启mysqld后都会失效;想永久有效,可定义在配置文件中的[mysqld]段落中!


五、Mysql中字符大小写:

1.SQL关键字及函数名不区分字符大小写;要么大写、要么小写

2.数据库、表以及视图名称的大小写区分与否取决于底层OS及File System;

3.存储过程、存储函数及时间调度器的名称不区分大小写,但触发器区分;

4.表的别名区分大小写

5.对于字段中的数据,如果字段类型为Binary类型,则区分大小写,非Binary不区分大小写;