Fork me on GitHub
Fork me on GitHub

MySQL介绍及安装部署

发表于   |   更新于   |   分类于   |   阅读次数

MySQL产品

当年Sun公司买下了MySQL，后来Sun公司被Oracle收购了。也就是MySQL也变成Oracle的了。Oracle向来视MySQL为眼中钉，所以Oracle在收购Sun的时候遭到了欧盟的极力反对，他们担心MySQL。Oracle有前科的，对于自己的产品，有竞争对手的，把对方公司收购，在把产品打入冷宫，不卖了。Oracle当前做出承诺，5年内花人力物力研发MySQL。Oracle也的确这么做了，但是MySQL有两个版本：社区版和商业版。在Oracle收购Sun之后，在不到两年的时间，商业版价格翻了4倍。好在Oracle在收购Sun的时候还收购了一个Openoffice的组件，是Linux上的一个办公套件。Oracle收购之后第一件事，就是openoffice不再开源，开始收钱了。很遗憾的是，Oracle毙掉之后，大家不认账，没人买他的。后来，openoffice那个团队又在早期的openoffice基础上做了另外一套office叫Libreoffice，比openoffice还要好，依然开源。没过多久，Oracle把openoffice又开源了，但是没人在用了。所以不知道MySQL会不会面临这个命运。MySQL的原作者当前在Oracle收购Sun时就极力反对，在Sun被收购后，他又组织了当年的开源团队，在早期MySQL的基础上重新创建了另外一套叫做MariaDB。MariaDB开源，并且其在设计上比早期的MySQL更先进。而且其API和MySQL完全兼容，所以Redhat7不再附带MySQL，而是MariaDB。现在MySQL家族的产品主要有：

  • MySQL Server（mysqld、mysql）
  • MySQL Cluster
  • MySQL Proxy
  • MySQL Adminitrator
  • MySQL Query Browser
  • MySQL Workbench
  • MySQL Migration Toolkit
  • MySQL Embedded Server
  • MySQL Drivers and Connectors

MySQL Server：分为企业级DB和社区版DB。
MySQL Cluster：市面上几乎没有使用这种企业级集群。
MySQL Proxy：由代理来判断谁能被读，谁能被写。
对于我们来说，会用到MySQL Server、MySQL Proxy和MySQL Drivers and Connectors。

MySQL逻辑架构


MySQL是个多用户服务器，支持多个连接同时连接进来。可以通过线程池来定义最多可以有多少个用户同时连进来。多余的连接请求放在连接队列中，线程池里出去一个，就从队列里进去线程池一个。据说oracle为mysql的商业版引入了很强大的线程池机制。连接/线程处理是MySQL的重要组成部分。
用户请求接进来以后，如果是个select语句，先查询缓存(mysql缓存是根据查询语句的hash码对比的，select的大小写都会导致hash码不一样)。如果命中，直接返回结果，如果未命中，接下来分析器做语句分析，分析之后缓存中还有可能命中。所以分析器分析后仍然要去查缓存。如果缓存还是未命中，接下来交给优化器，优化以后由执行引擎交给存储引擎做查询操作。
优化器优化之后，真正负责执行语句的是存储引擎。mysql是插件式存储引擎的，所以不同的表可以使用不同的存储引擎。

MySQL逻辑组件


Connection Pool：Authentication, Thread Reuse, Connection Limits, Check Memory, Caches。
认证，线程重用， 连接限制， 检查内存， 实现连接缓存。


存储引擎将数据转换成存储在文件系统中的文件，文件系统可以是NTFS、ufs、ext2/3、NFS、SAN、NAS。文件系统上的文件表现为文件和日志。文件有数据文件和索引文件。日志有重做、撤销、二进制、错误、查询和慢查询日志，一般不涉及撤销日志，所以这边日志有5种。

备份工具、安全工具、恢复工具复制工具、集群工具等。


用户请求接进来了，连接管理器负责接收用户请求，接进来后，线程管理器需要生成一个线程响应用户请求(MySQL是单进程多线程模型的)，用户输入了帐号和密码后，需要一个用户模块，用户肯定需要和用户模块打交道的，因为需要认证。只要连接进来，后续的sql语句也是和用户模块打交道，而不会和连接管理器打交道了。
用户认证后，会发出很多SQL命令，由命令派发器来派发给查询缓存，查询缓存如果命中了则直接返回结果，当然完成了就需要记录日志(记录日志是可选步骤)。如果查询缓存没命中，就要交给分析器分析了。用户的语句有DDL、DML，分析结束后，如果是select就交给优化器，如果是update、insert、delete、replace就交给表修改模块，如果是repair就交给表维护模块，如果是replication类操作就交给复制模块，还需要个状态报告模块报告服务器执行状态。
这些组件最终由访问控制模块来控制，你到底有没有权限来操作，这个模块是做访问检查的。前面那个用户模块是做认证检查的，帐号密码没问题不代表可以访问里面的数据。访问控制模块检查没问题后，接下来就真正执行操作了。表管理器接口的真正执行的操作依赖于存储引擎。所以接下来要交给存储引擎的抽象接口。存储引擎抽象接口将用户请求在转发给各存储引擎。

MySQL安装部署

1.MySQL的安装方式

  • 源码编译
  • rpm包：
    • OS Vendor提供的
    • MySQL官方提供的
  • 通用二进制格式

2.MySQL版本

  • GA（General Availability）：要选择GA版
  • RC（Release Candidate）：候选版，马上要发布为GA版本
  • beta：公测版
  • alpha：内测版

3.MySQL官方提供的rpm包

  • MySQL-devel：开发需要的头文件和额外的库文件。
  • MySQL-embedded：嵌入式环境下专用的。
  • MySQL-ndb-management：MySQL集群管理器。
  • MySQL-shared：被各种应用程序所依赖的贡献库。
  • MySQL-shared-compat：兼容库。有些老的应用程序可能依赖mysql老版本的库。
  • MySQL-test：测试套件。包含一些压测工具等等。
  • MySQL-VERSION.PLATFORM.src.rpm：源代码包。

对我们而言，需要MySQL-client、MySQL-server、MySQL-shared、MySQL-shared-compat和MySQL-test。

4.二进制方式部署MySQL 5.6
下载地址：http://mirrors.sohu.com/mysql

主机名 操作系统版本 IP地址 安装软件
console CentOS 7.0 114.55.29.246 mysql-5.6.27-linux-glibc2.5-x86_64.tar.gz

（1）安装依赖包

[root@console local]# yum install libaio* -y

（2）解压安装

[root@console local]# tar zxf mysql-5.6.27-linux-glibc2.5-x86_64.tar.gz 
[root@console local]# ln -sv mysql-5.6.27-linux-glibc2.5-x86_64 mysql
‘mysql’ -> ‘mysql-5.6.27-linux-glibc2.5-x86_64’

（3）新建用户
运行mysql最好不要用root去运行，而以普通用户身份。添加用户mysql。

[root@console local]# groupadd -r -g 300 mysql
[root@console local]# useradd -g mysql -r -s /sbin/nologin -u 300 mysql
[root@console local]# id mysql
uid=300(mysql) gid=300(mysql) groups=300(mysql)

（4）修改mysql文件权限为mysql.mysql

[root@console local]# cd mysql
[root@console mysql]# chown -R mysql.mysql ./*

（5）执行初始化操作，生成一个系统库叫mysql，它里面保存着有当前所有能够使用mysql服务器的用户帐号、所有数据库的名字、每个库中表的名字、表中字段的名字等等。
脚本路径：/usr/local/mysql/scripts
创建数据文件目录：

[root@console mysql]# mkdir -pv /data/{mydata,binlog}
[root@console mysql]# scripts/mysql_install_db --user=mysql --basedir=/usr/local/mysql --datadir=/data/mydata
[root@console mysql]# ls /data/mydata

（6）修改mysql目录下的文件属主为root，属组为mysql

[root@console mysql]# chown -R root .

（7）修改data目录属主、属组为mysql

[root@console mysql]# chown -R mysql.mysql /data

（8）拷贝修改mysql的配置文件

copy写好的my.cnf到/etc/目录下。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
# The following options will be passed to all MySQL clients
[client]
#password       = your_password
port            = 3306
socket          = /tmp/mysql.sock
# The MySQL server
[mysqld]
port            = 3306
socket          = /tmp/mysql.sock
skip-external-locking
key_buffer_size = 256M
max_allowed_packet = 1M
table_open_cache = 256
sort_buffer_size = 1M
read_buffer_size = 1M
read_rnd_buffer_size = 4M
myisam_sort_buffer_size = 64M
thread_cache_size = 8
query_cache_size= 16M
# Try number of CPU's*2 for thread_concurrency
thread_concurrency = 4
datadir=/data/mydata
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES 
character-set-server = utf8
max_connections = 1000
# Replication Master Server (default)
# binary logging is required for replication
log-bin=/data/binlog/master-bin
# binary logging format - mixed recommended
binlog_format=mixed
# required unique id between 1 and 2^32 - 1
# defaults to 1 if master-host is not set
# but will not function as a master if omitted
server-id       = 1
[mysqldump]
quick
max_allowed_packet = 16M
[mysql]
no-auto-rehash
[myisamchk]
key_buffer_size = 128M
sort_buffer_size = 128M
read_buffer = 2M
write_buffer = 2M
[mysqlhotcopy]
interactive-timeout

（9）拷贝mysql的启动脚本，并加入系统服务

[root@console mysql]# cp support-files/mysql.server /etc/init.d/mysqld 
[root@console mysql]# chkconfig --add mysqld

（10）启动mysql

[root@console mysql]# service mysqld start

（11）配置环境变量，配置完重新打开一个shell

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

（12）修改root密码，因为一装完root密码是空的

[root@console ~]# mysql -uroot mysql 


mysql> UPDATE user SET Password=PASSWORD('wisedu123') where USER='root';
mysql> FLUSH PRIVILEGES;


此时再以root登录就需要密码了。

（13）删除两个匿名帐号

mysql> use mysql
mysql> SELECT host,user,password FROM user;


mysql> DROP USER ''@localhost;   #注意’’@localhost是两个单引号
mysql> DROP USER ''@console;

MySQL初始化

MySQL是基于TCP的3306端口。虽然如此，但是还支持其他的通信方式，比如socket、memory、pipe。tcp是远程通信时用的，Linux或unix本机是基于socket，windows本机是基于memory或pipe。
MySQL服务器无论是通用二进制方式安装还是编译的方式安装，安装完成后要做MySQL的初始化。系统初始化时，默认库是mysql。这个库存储了当前mysql的各种元数据。这些初始化包含以下工作：

提供配置文件

配置文件：.cnf结尾

  • 集中式的配置:多个应用程序共用的配置文件
    • [mysqld]
    • [mysqld_safe]
    • [client]

使用配置文件的方式（安装mysql的方式不同，读取配置文件的顺序可能不同）

  • 1.它依次查找每个需要查找的文件，结果是所有文件并集；
  • 2.如果某参数在多个文件中出现多次，后读取的最终生效；

配置文件顺序：

# /usr/local/mysql/bin/mysqld --help --verbose | head -20
Default options are read from the following files in the given order:
/etc/mysql/my.cnf  /etc/my.cnf  ~/.my.cnf

删除所有匿名用户（5.7之前的版本）

mysql> DROP USER ''@'localhost';
mysql> DROP USER ''@'www.abc.com'; #www.abc.com为主机名

用户帐号由两部分组成：username@host，host指所能够远程访问时使用的客户端主机
host还可以使用通配符：

  • %: 任意长度的任意字符
  • _: 匹配任意单个字符

给所有的root用户设定密码（5.7之前的版本）

1.第一种方式

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

2.第二种方式

mysql> UPDATE user SET password = PASSWORD('your_password') WHERE user = 'root';   ——这是修改授权表
mysql> FLUSH PRIVILEGES;   ——通知mysqld重读授权表

3.第三种方式： 命令行mysqladmin工具

# mysqladmin -uUserName -hHost password 'new_password' -p    ——这里的host指mysqld服务器的ip，并不是帐号中的主机地址
# mysqladmin -uUserName -hHost -p flush-privileges

客户端工具

mysql, mysqladmin, mysqldump, mysqlcheck, mysqlimport
配置文件中[client]下面的配置是所有mysql客户端共享的配置
命令行客户端的通用的选项：

  • -u, –user=
  • -h, –host=
  • -p, –password=
  • –protocol={tcp|socket|memory|pipe}
  • –port=
  • –socket= 例如：/tmp/mysql.sock

非客户端类的管理工具：myisamchk, myisampack。 都是在mysql服务器上运行的，不能基于客户端服务器端通信方式进行使用的。myisamchk是检测工具，检测myisam表是不是有不一致的情况。myisampack是打包压缩工具。

mysql客户端

mysql这个命令行客户端工作模式:

  • 交互式模式 mysql>
  • 脚本模式(批处理模式) mysql < /path/to/mysql_script.sql

1.mysql交互式模式
（1）客户端命令：

mysql> help  ——获取客户端命令帮助
mysql> \?    ——获取客户端命令帮助
       \c
       \g
       \G
       \q
       \!
       \s
       \. /path/to/mysql_script.sql

当然，客户端命令里面偶尔可能还会用到其他的，比如”rehash (#) Rebuild completion hash.”，MySQL其实也支持命令补全的，比如说你有一个库叫mysql，或者叫其他的库，等会我们去use设定某个默认库时，或者只要但凡用到库名的时候，你可以不用写全了，它可以自动给你补全。但是这一功能在mysql启动时默认是关闭的，因为要想能够实现此种功能意义上的补全，需要让mysql服务器启动时读取每一个mysql对象并且给它们生成一个hash表才能补全。这通常会导致mysql启动时被卡住，尤其是非常大的mysql数据库时。启动后如果想用补全，就使用#，重新生成补全的hash表的hash码。

（2）服务器端命令：需要把命令写全后一并发送给服务器端执行的，因此需要命令结束符，默认为分号(;)

2.mysql的快捷键

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

mysqldmin工具

mysqladmin [options] command [arg] [command [arg]] ...

command:

  • create DB_NAME
  • drop DB_NAME
  • debug: 打开调试日志并记录于error log中；如果我们启动mysql总是出错的话，你可以使用mysqladmin debug，它可以限定通知mysql服务器运行过程中的所有信息都送往mysql的error log。
  • status：显示mysql运行的简要状态信息
    • –sleep #: 指定间隔时长刷新状态信息
    • –count #: 显示的批次，显示几次。
  • extended-status: 输出mysqld的各状态变量及其值，相当于执行“mysql> SHOW GLOBAL STATUS”
  • variables: 输出mysqld的各服务器变量
  • flush-hosts: 清空主机相关的缓存：DNS解析缓存，此前因为连接错误次数过多而被拒绝访问mysqld的主机列表。如果我们在某台客户端主机上连服务器，mysql默认好像是10次，在有限时间内错误过多可能就被放到黑名单中去了。此后再发连接请求，直接拒绝。一旦出现这种情况，就得去mysql服务器端flush-hosts。
  • flush-logs: 日志滚动，只二进制日志和中继日志。
  • refresh: 相当于同时使用flush-logs和flush-hosts
  • flush-privileges: 通知服务器重读授权表。
  • reload: 功能同flush-privileges。
  • flush-status: 重置服务器状态变量的值。重置extended-status显示的变量值，不是所有变量。
  • flush-tables: 关闭当前打开的表文件句柄。
  • flush-threads：清空线程缓存。
  • kill:　给线程id，杀死指定的线程，可以一次杀死多个线程，以逗号分隔，但不能有多余空格。mysql是单进程多线程的。
  • password: 修改当前用户的密码。
  • ping: 探测服务器是否在线。
  • processlist：显示mysql服务器的线程列表。
  • shutdown: 关闭mysqld进程。所以mysqladmin是个很危险的命令。
  • start-slave
  • stop-slave: 启动/关闭从服务器线程
  • version：显示mysqld的版本。

【示例】：

[root@osb30 ~]# mysqladmin -uroot -p create testdb
Enter password: 
[root@osb30 ~]# mysqladmin -uroot -p status
Enter password: 
Uptime: 15116890  Threads: 23  Questions: 144580431  Slow queries: 12904887  Opens: 380  Flush tables: 1  Open tables: 338  Queries per second avg: 9.564
[root@osb30 ~]# mysqladmin -uroot -p version
Enter password: 
mysqladmin  Ver 8.42 Distrib 5.6.27, for Linux on x86_64
Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Server version          5.6.27
Protocol version        10
...
[root@osb30 ~]# mysqladmin -uroot -p ping
Enter password: 
mysqld is alive

GUI客户端工具：

  • Navicat for mysql 商业版的
  • Toad for mysql
  • mysql front 商业版的
  • sqlyog 商业版的
  • phpMyAdmin 基于php的开源工具

mysql忘记root密码怎么办

在my.cnf文件中加入如下配置：

skip-grant-tables=1

然后用空密码方式使用root用户登录 MySQL：

mysql -u root

修改root用户的密码：

mysql> update mysql.user set password=PASSWORD('newpassword') where User='root';  
mysql> flush privileges;  
mysql> quit