MySQL数据库企业级应用实践

MySQL数据库企业级应用实践

标签（空格分隔）： linux学习知识整理

---

一，概述

1.1 MySQL介绍

• MySQL属于传统关系型数据库产品，它开放式的架构使得用户选择性很强，同时社区开发与维护人数众多。其功能稳定，性能卓越，且在遵守GPL协议的前提下，可以免费使用与修改，也为MySQL的推广与使用带来了更多的利好。在MySQL成长与发展过程中，支持的功能逐渐增多，性能也不断提高，对平台的支持也越来越多。
• MySQL是一种关系型数据库管理系统，关系型数据库的特点是将数据保存在不同的表中，再将这些表放入不同的数据库中，而不是将所有数据统一放在一个大仓库里，这样的设计增加了MySQL的读取速度，而且灵活性和可管理性也得到了很大提高。访问及管理MySQL数据库的最常用标准化语言为SQL结构化查询语言。

1.2 MariaDB 数据库的诞生背景介绍

自甲骨文公司收购MySQL后，其在商业数据库与开源数据库领域市场的占有份额都跃居第一，这样的格局引起了业内很多的人士的担忧，因为商业数据库的老大有可能将MySQL闭源。为了避免Oracle将MySQL闭源，而无开源的类MySQL数据库可用，MySQL社区采用分支的方式来避开这个风险。MariaDB数据库就这样诞生了，MariaDB是一个向后兼容，可能在以后替代MySQL的数据库产品，其官方地址为：https://mariadb.org/ 不过，这里还是建议大家选择更稳定，使用更广泛的MySQL数据库，可以先测试MariaDB数据库，等使用的人员更多一些，社区更活跃后再考虑使用为好。

二，MySQL多实例介绍

2.1 什么是MySQL多实例

MySQL多实例就是在一台服务器上同时开启多个不同的服务器端口（如：3306，3307），同时运行多个MySQL服务进程，这些服务进程通过不同的socket监听不同的服务器端口来提供服务。
这些MySQL多实例共用一套MySQL安装程序，使用不同的my.cnf（也可以相同）配置文件，启动程序（也可以相同）和数据文件。在提供服务时，多实例MySQL在逻辑上看起来是各自独立的，它们根据配置文件的对应设定值，获得服务器相应数量的硬件资源。
打个比方吧，MySQL多实例就相当于房子的多个卧室，每个实例可以看作一间卧室，整个服务器就是一套房子，服务器的硬件资源（CPU，Mem，Disk），软件资源（Centos操作系统）可以看作房子的卫生间，厨房，客厅，是房子的公用资源。
其实很多网络服务都是可以配置多实例的，例如Nginx，Apache，Haproxy，Redis，Memcache等。这在门户网站使用得很广泛。

2.2 MySQL多实例的作用与问题

MySQL多实例的作用如下：

• 有效利用服务器资源

当单个服务器资源有剩余时，可以充分利用剩余的资源提供更多的服务，且可以实现资源的逻辑隔离。

• 节约服务器资源

当公司资金紧张，但是数据库又需要各自尽量独立地提供服务，而且，需要主从复制等技术时，多实例就再好不过了。

MySQL多实例有它的好处，但也有其弊端，比如，会存在资源互相抢占的问题。
当某个数据库实例并发很高或有SQL慢查询时，整个实例会消耗大量的系统CPU，磁盘I/O等资源，导致服务器上的其他数据库实例提供服务的质量一起下降。这就相当于大家住在一个房子的不同卧室一样，早晨起来上班，都要刷牙，洗脸等，这样卫生间就会长期占用，其他人要等待一样。不同实例获取的资源是相对独立的，无法像虚拟化一样完全隔离。

三， MySQL多实例的生产应用场景

3.1 资金紧张型公司的选择

若公司资金紧张，公司业务访问量不太大，但又希望不同业务的数据库服务各自尽量独立地提供服务而互相不受影响，同时，还需要主从复制等技术提供备份或读写分离服务，那么多实例就再好不过了。例如：可以通过3台服务器部署9～15个实例，交叉做主从复制，数据备份及读写分离，这样就可达到9～15台服务器每个只装一个数据库才有的效果。这里要强调的是，所谓的尽量独立是相对的。

3.2 并发访问不是特别大的业务

当公司业务访问量不太大的时候，服务器的资源基本上都浪费了，这时就很适合多实例的应用，如果对SQL语句的优化做得比较好，MySQL多实例会是一个很值得使用的技术，即使并发很大，合理分配好系统资源，搭配好服务，也不会有太大问题。

3.3 门户网站应用MySQL多实例场景

门户网站通常都会使用多实例，因为配置硬件好的服务器，可节省IDC机柜空间，同时，跑多实例也会减少硬件资源跑不满的浪费。比如，百度公司的很多数据库都是多实例，不过，一般是从库多实例，例如某部门中使用的IBM服务器为48核CPU，内存96GB，一台服务器跑3～4个实例；此外，新浪网使用的也是多实例，内存48GB左右。

说明：
据调查，新浪网的数据库单机1～4个数据库实例的居多，其中又数1～2个的最多，因为大业务占用的机器比较多。服务器是DELL R510的居多，CPU是E5210，48GB内存，磁盘12×300G SAS，做RAID10，此为门户网站的服务器配置参考，希望能给同学们的面试带来一些启迪。
另外，新浪网站安装数据库时，一般采用编译安装的方式，并且会在优化之后做成rpm包，以便统一使用。

四， MySQL多实例常见的配置方案

4.1 单一配置文件，单一启动程序的多实例部署方案

下面是MySQL官方文档提到的单一配置文件，单一启动程序多实例部署方案，但不推荐此方案，这里仅作为知识点提及，后文不再涉及此方案的说明。my.cnf配置文件示例（MySQL手册里提到的方法）如下：

[mysqld_multi]
mysqld      =   /usr/bin/mysqld_safe
mysqladmin  =   /usr/bin/mysqladmin
user        =   mysql
[mysqld1]
socket      =   /var/lib/mysql/mysql.sock
port        =   3306
pid-file    =   /var/lib/mysql/mysql.pid
datadir     =   /var/lib/mysql/
user        =   mysql
[mysqld2]
socket      =   /mnt/data/db1/mysql.sock
port        =   3302
pid-file    =   /mnt/data/db1/mysql.pid
datadir     =   /mnt/data/db1/
user        =   mysql
skip-name-resolv
server-id=10
default-storage-engine=innodb
innodb_buffer_pool_size=512M
innodb_additional_mem_pool=10M
default_character_set=utf8
character_set_server=utf8
#read-only
relay-log-space-limit=3G
expire_logs_day=20

启动程序的命令如下：

mysqld_multi --config-file=/data/mysql/my_multi.cnf start 1,2

该方案的缺点是耦合度太高，一个配置文件，不好管理。工作开发和运维的统一原则为降低耦合度。

4.2 多配置文件，多启动程序的部署方案

多配置文件，多启动程序部署方案，是本文主要讲解的方案，也是非常常用并极力推荐的多实例方案。下面来看配置示例。

[root@mysql01 /]# tree /data
/data
├── 3306
│   ├── data        #3306实例的数据目录
│   ├── my.cnf      #3306实例的配置文件
│   └── mysql       #3306实例的启动文件
└── 3307
    ├── data        #3307实例的数据目录
    ├── my.cnf      #3307实例的配置文件
    └── mysql       #3307实例的启动文件

4 directories, 4 files
提示：
这里的配置文件my.cnf,启动程序mysql都是独立的文件，数据文件data目录也是独立的。

五，安装并配置多实例MySQL数据库

5.1 安装MySQL多实例

5.1.1，安装MySQL需要的依赖包和编译软件

#####（1）安装MySQL需要的依赖包

安装MySQL之前，最好先安装MySQL需要的依赖包，不然后面会出现很多报错信息，到那时还得再回来安装MySQL的依赖包。安装命令如下：

[root@mysql01 ~]# yum -y install ncurses-devel libaio-devel
[root@mysql01 ~]# rpm -qa ncurses-devel libaio-devel
ncurses-devel-5.7-4.20090207.el6.x86_64
libaio-devel-0.3.107-10.el6.x86_64

#####（2）安装编译MySQL需要的软件

首先通过网络获得cmake软件，然后进行如下操作：

[root@mysql01 ~]# ls -lh cmake-2.8.6.tar.gz 
-rw-r--r-- 1 root root 5.4M 7月  19 20:43 cmake-2.8.6.tar.gz          #此软件需提前准备
[root@mysql01 ~]# tar xf cmake-2.8.6.tar.gz -C /usr/src/
[root@mysql01 ~]# cd /usr/src/cmake-2.8.6/
[root@mysql01 cmake-2.8.6]# ./configure
[root@mysql01 cmake-2.8.6]# gmake && gmake install
[root@mysql01 cmake-2.8.6]# which cmake

5.1.2 开始安装MySQL

为了让同学们学习更多的MySQL技术，接下来会以相对复杂的源代码安装来讲解MySQL多实例的安装。大型公司一般都会将MySQL软件定制成rpm包，然后放到yum仓库里，使用yum安装，中小企业里的二进制和编译安装的区别不大。

#####（1）建立MySQL用户账号

首先以root身份登录到Linux系统中，然后执行如下命令创建mysql用户账号

[root@mysql01 ~]# useradd -s /sbin/nologin -M mysql
[root@mysql01 ~]# id mysql
uid=500(mysql) gid=500(mysql) 组=500(mysql)

#####（2）获取MySQL软件包

MySQL软件包的下载地址为：https://dev.mysql.com/downloads/mysql/ 提示：
本例以MySQL编译的方式来讲解，之前已经演示过二进制方式安装了。在生产场景中，二进制和源码包两种安装方法都是可以用的，其应用场景一般没什么差别。不同之处在于，二进制的安装包较大，名字和源码包也有些区别，二进制安装过程比源码更快。

#####（3）采用编译方式安装MySQL

配置及编译安装的步骤如下：

[root@mysql01 ~]# tar xf mysql-5.5.22.tar.gz -C /usr/src/
[root@mysql01 ~]# cd /usr/src/mysql-5.5.22/
[root@mysql01 mysql-5.5.22]# cmake . -DCMAKE_INSTALL_PREFIX=/usr/local/mysql-5.5.22 \
> -DMYSQL_DATADIR=/usr/local/mysql-5.5.22/data \        #数据存放目录
> -DMYSQL_UNIX_ADDR=/usr/local/mysql-5.5.22/tmp/mysql.sock \    #MySQL进程间通信的套接字位置
> -DDEFAULT_CHARSET=utf8 \                  #默认字符集为utf8
> -DDEFAULT_COLLATION=utf8_general_ci \     #默认字符集排序规则
> -DEXTRA_CHARSETS=gbk,gb2312,utf8,ascii \  #额外的字符集支持
> -DENABLED_LOCAL_INFILE=ON \               #是否启用加载本地数据
> -DWITH_INNOBASE_STORAGE_ENGINE=1 \        #静态编译innodb存储引擎到数据库
> -DWITH_FEDERATED_STORAGE_ENGINE=1 \       #静态编译FEDERATED存储引擎到数据库
> -DWITH_BLACKHOLE_STORAGE_ENGINE=1 \       #静态编译blackhole存储引擎到数据库
> -DWITHOUT_EXAMPLE_STORAGE_ENGINE=1 \      #不编译EXAMPLE存储引擎到数据库
> -DWITHOUT_PARTITION_STORAGE_ENGINE=1 \    #不支持数据库分区
> -DWITH_FAST_MUTEXES=1 \
> -DWITH_ZLIB=bundled \                     #zlib压缩模式
> -DENABLED_LOCAL_INFILE=1 \                #是否启用本地的LOCAL_INFILE
> -DWITH_READLINE=1 \                       #使用捆绑的readline
> -DWITH_EMBEDDED_SERVER=1 \                #是否要建立嵌入式服务器
> -DWITH_DEBUG=0                            #禁用DEBUG（开启影响性能）
# 提示：编译时可配置的选项很多，具体可参考官方文档

[root@mysql01 mysql-5.5.22]# make && make install
下面设置不带版本号的软链接/usr/local/mysql,操作步骤如下：
[root@mysql01 mysql-5.5.22]# ln -s /usr/local/mysql-5.5.22 /usr/local/mysql
[root@mysql01 mysql-5.5.22]# ls /usr/local/mysql
bin      data  include         lib  mysql-test  scripts  sql-bench
COPYING  docs  INSTALL-BINARY  man  README      share    support-files
如果上述操作未出现错误，查看/usr/local/mysql目录下有内容，则MySQL
-
5.5.22源代码包采用cmake方式的安装就算成功了。

5.2 创建MySQL多实例的数据文件目录

在企业中，通常以/data目录作为MySQL多实例总的根目录，然后规划不同的数字（即MySQL实例端口号）作为/data下面的二级目录，不同的二级目录对应的数字就作为MySQL实例的端口号，以区别不同的实例，数字对应的二级目录下包含MySQL的数据文件，配置文件及启动文件等。

[root@mysql01 ~]# mkdir -p /data/{3306,3307}/data
[root@mysql01 ~]# tree /data/
/data/
├── 3306            #3306实例目录
│   └── data        #3306实例的数据文件目录
├── 3307            #3307实例目录
    └── data        #3307实例的数据文件目录


4 directories, 0 files
提示：
（1）mkdir -p /data/{3306,3307}/data相当于mkdir -p /data/3306/data;mkdir -p /data/3307/data两条命令
（2）如果是创建多个目录，可以增加如3308,3309这样的目录名，在生产环境中，一般为3~4个实例为佳。

5.3 创建MySQL多实例的配置文件

MySQL数据库默认为用户提供了多个配置文件模板，用户可以根据服务器硬件配置的大小来选择。

[root@mysql01 mysql]# ls -l support-files/my*.cnf
-rw-r--r-- 1 root root  4751 7月  19 21:33 support-files/my-huge.cnf
-rw-r--r-- 1 root root 19805 7月  19 21:33 support-files/my-innodb-heavy-4G.cnf
-rw-r--r-- 1 root root  4725 7月  19 21:33 support-files/my-large.cnf
-rw-r--r-- 1 root root  4736 7月  19 21:33 support-files/my-medium.cnf
-rw-r--r-- 1 root root  2900 7月  19 21:33 support-files/my-small.cnf

注意：
这些配置文件里的注释非常详细，不过是英文的。。。

上面是单实例的默认配置文件模板，如果配置多实例，和单实例会有不同。为了让MySQL多实例之间彼此独立，要为每一个实例建立一个my.cnf配置文件和一个启动文件MySQL，让他们分别对应自己的数据文件目录data。

首先，通过vim命令添加配置文件内容，命令如下：
vim /data/3306/my.cnf
vim /data/3307/my.cnf
不同的实例需要添加的my.cnf内容会有区别,其中的配置由官方的配置模板修改而来。当然，在实际工作中，我们是拿早已配置好的模板来进行修改的，可以通过rz等方式上传配置文件模板my.cnf文件到相关目录下。

##实例3306配置文件my.cnf
[root@mysql01 ~]# cat /data/3306/my.cnf
[client]
port		= 3306
socket		= /data/3306/mysql.sock
[mysqld]
server-id = 1
user		= mysql
port		= 3306
socket		= /data/3306/mysql.sock
basedir		= /usr/local/mysql
datadir		= /data/3306/data
open_files_limit	= 1024
back_log = 600
max_connections = 800
max_connect_errors = 3000
table_open_cache = 614
external-locking = FALSE
max_allowed_packet = 8M
#binlog_cache_size = 1M
#max_heap_table_size = 64M
#read_buffer_size = 2M
#read_rnd_b