第二十九节 MySQL数据库企业级应用实践
一，概述
1.1 MySQL介绍
MySQL属于传统关系型数据库产品，它开放式的架构使得用户选择性很强，同时社区开发与维护人数众多。其功能稳定，性能卓越，且在遵守GPL协议的前提下，可以免费使用与修改，也为MySQL的推广与使用带来了更多的利好。在MySQL成长与发展过程中，支持的功能逐渐增多，性能也不断提高，对平台的支持也越来越多。
MySQL是一种关系型数据库管理系统，关系型数据库的特点是将数据保存在不同的表中，再将这些表放入不同的数据库中，而不是将所有数据统一放在一个大仓库里，这样的设计增加了MySQL的读取速度，而且灵活性和可管理性也得到了很大提高。访问及管理MySQL数据库的最常用标准化语言为SQL结构化查询语言。
1.2 MariaDB 数据库的诞生背景介绍
自甲骨文公司收购MySQL后，其在商业数据库与开源数据库领域市场的占有份额都跃居第一，这样的格局引起了业内很多的人士的担忧，因为商业数据库的老大有可能将MySQL闭源。为了避免Oracle将MySQL闭源，而无开源的类MySQL数据库可用，MySQL社区采用分支的方式来避开这个风险。MariaDB数据库就这样诞生了，MariaDB是一个向后兼容，可能在以后替代MySQL的数据库产品，其官方地址为： 。不过，这里还是建议大家选择更稳定，使用更广泛的MySQL数据库，可以先测试MariaDB数据库，等使用的人员更多一些，社区更活跃后再考虑使用为好。
二，MySQL多实例介绍
在之前LNMP的讲解中，已经针对MySQL数据库进行了介绍，并说明了为什么要选择MySQL数据库，以及MySQL数据库在Linux系统下的多种安装方式，同时讲解了MySQL的二进制方式单实例安装，基础优化等内容，本节将为同学们讲解更为实用的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多实例的作用如下：
（1）有效利用服务器资源
当单个服务器资源有剩余时，可以充分利用剩余的资源提供更多的服务，且可以实现资源的逻辑隔离。
（2）节约服务器资源
当公司资金紧张，但是数据库又需要各自尽量独立地提供服务，而且，需要主从复制等技术时，多实例就再好不过了。
MySQL多实例有//代码效果参考：http://hnjlyzjd.com/hw/wz_24029.html
它的好处，但也有其弊端，比如，会存在资源互相抢占的问题。当某个数据库实例并发很高或有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 //代码效果参考：http://hnjlyzjd.com/xl/wz_24027.html
= /usr/bin/mysqld_safemysqladmin = /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@localhost /】# 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 安装MySQL多实例
1，安装MySQL需要的依赖包和编译软件
（1）安装MySQL需要的依赖包
安装MySQL之前，最好先安装MySQL需要的依赖包，不然后面会出现很多报错信息，到那时还得再回来安装MySQL的依赖包。安装命令如下：
【root@localhost ~】# yum -y install ncurses-devel libaio-devel
【root@localhost ~】# 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@localhost ~】# 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@localhost ~】# tar xf cmake-2.8.6.tar.gz -C /usr/src/
【root@localhost ~】# cd /usr/src/cmake-2.8.6/
【root@localhost cmake-2.8.6】# ./configure
【root@localhost cmake-2.8.6】# gmake && gmake install
【root@localhost cmake-2.8.6】# which cmake
2，开始安装MySQL
为了让同学们学习更多的MySQL技术，接下来会以相对复杂的源代码安装来讲解MySQL多实例的安装。大型公司一般都会将MySQL软件定制成rpm包，然后放到yum仓库里，使用yum安装，中小企业里的二进制和编译安装的区别不大。
（1）建立MySQL用户账号
首先以root身份登录到Linux系统中，然后执行如下命令创建mysql用户账号：
【root@localhost ~】# useradd -s /sbin/nologin -M mysql
【root@localhost ~】# id mysql
uid=500(mysql) gid=500(mysql) 组=500(mysql)
（2）获取MySQL软件包
MySQL软件包的下载地址为：
提示：
本例以MySQL编译的方式来讲解，之前已经演示过二进制方式安装了。在生产场景中，二进制和源码包两种安装方法都是可以用的，其应用场景一般没什么差别。不同之处在于，二进制的安装包较大，名字和源码包也有些区别，二进制安装过程比源码更快。
MySQL源码包和二进制安装包的名称见下图
（3）采用编译方式安装MySQL
配置及编译安装的步骤如下：
【root@localhost ~】# tar xf mysql-5.5.22.tar.gz -C /usr/src/
【root@localhost ~】# cd /usr/src/mysql-5.5.22/
【root@localhost mysql-5.5.22】# cmake . -DCMAKE_INSTALL_PREFIX=/usr/local/mysql-5.5.22 \
> -DWITH_ZLIB=bundled \ #zlib压缩模式
# 提示：编译时可配置的选项很多，具体可参考官方文档
【root@localhost mysql-5.5.22】# make && make install
下面设置不带版本号的软链接/usr/local/mysql,操作步骤如下：
【root@localhost mysql-5.5.22】# ln -s /usr/local/mysql-5.5.22 /usr/local/mysql
【root@localhost 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目录下有内容，则MySQL5.5.22源代码包采用cmake方式的安装就算成功了。
5.2 创建MySQL多实例的数据文件目录
在企业中，通常以/data目录作为MySQL多实例总的根目录，然后规划不同的数字（即MySQL实例端口号）作为/data下面的二级目录，不同的二级目录对应的数字就作为MySQL实例的端口号，以区别不同的实例，数字对应的二级目录下包含MySQL的数据文件，配置文件及启动文件等。
下面以配置3306,3307两个实例为例进行讲解。创建MySQL多实例的目录如下：
【root@localhost ~】# mkdir -p /data/{3306,3307}/data
【root@localhost ~】# 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@localhost 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文件到相关目录下。
MySQL3306,3307实例配置文件如下
##实例3306配置文件my.cnf
【root@localhost ~】# cat /data/3306/my.cnf
【client】
port = 3306
socket = /data/3306/mysql.sock
【mysqld】
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_buffer_size = 16M
sort_buffer_size = 1M
join_buffer_size = 1M
thread_cache_size = 100
thread_concurrency = 2
query_cache_size = 2M
query_cache_limit = 1M
query_cache_min_res_unit = 2k
#ft_min_word_len = 4
#default-storage-engine = MYISAM
thread_stack = 192K
transaction_isolation = READ-COMMITTED
tmp_table_size = 2M
max_heap_table_size = 2M
#log-bin=mysql-bin
#binlog_format=mixed
#slow_query_log
long_query_time = 1
pid-file = /data/3306/mysql.pid
relay-log = /data/3306/relay-bin
relay-log-info-file = /data/3306/relay-log.info
binlog_cache_size = 1M
max_binlog_cache_size = 1M
max_binlog_size = 2M
key_buffer_size = 16M
read_buffer_size = 1M
read_rnd_buffer_size = 1M
bulk_insert_buffer_size = 1M
lower_case_table_names = 1
skip-name-resolve
slave-skip-errors = 1032,1062
replicate-ignore-db = mysql
server-id = 1
#key_buffer_size = 32M
#bulk_insert_buffer_size = 64M
#myisam_sort_buffer_size = 128M
#myisam_max_sort_file_size = 10G
#myisam_repair_threads = 1
#myisam_recover
innodb_additional_mem_pool_size = 4M
innodb_buffer_pool_size = 32M
innodb_data_file_path = ibdata1:128M:autoextend
innodb_file_io_threads = 4
#innodb_write_io_threads = 8
#innodb_read_io_threads = 8
innodb_thread_concurrency = 8
innodb_flush_log_at_trx_commit = 2
innodb_log_buffer_size = 2M
innodb_log_file_size = 4M
innodb_log_files_in_group = 3
innodb_max_dirty_pages_pct = 90
innodb_lock_wait_timeout = 120
innodb_file_per_table = 0
【mysqldump】
quick
max_allowed_packet = 2M
【mysql】
no-auto-rehash
#【myisamchk】
#key_buffer_size = 512M
#sort_buffer_size = 512M
#read_buffer = 8M
#write_buffer = 8M
#【mysqlhotcopy】
#interactive-timeout
【mysqld_safe】
log-error = /data/3306/mysql_yunjisuan3306.err
pid-file = /data/3306/mysqld.pid
提示：实例3307的配置文件只需要将3306配置文件里的所有3306数字替换成3307(server-id换个数字)即可。
最终完成后的多实例根/data目录结果如下：
【root@localhost ~】# tree /data
/data
├── 3306
│ ├── data
│ └── my.cnf #这个就是3306实例的配置文件
└── 3307
├── data
└── my.cnf #这个就是3307实例的配置文件
4 directories, 2 files
5.4 创建MySQL多实例的启动文件
MySQL多实例启动文件的创建和配置文件的创建几乎一样，也可以通过vim命令来添加，如下：
vim /data/3306/mysql
vim /data/3307/mysql
需要添加的MySQL启动文件内容如下。（当然，在实际工作中我们是拿早已配置好的模板来进行修改的，可以通过rz等方式上传配置文件模板MySQL文件到相关目录下）
【root@localhost ~】# cat /data/3306/mysql
#!/bin/bash
###############################################
#this scripts is created by Mr.chen at 2016-06-25
port=3306
mysql_user="root"
mysql_pwd="" #这里需要修改为用户的实际密码
CmdPath="/usr/local/mysql/bin"
mysql_sock="/data/${port}/mysql.sock"
#startup function
function_start_mysql(){
if 【 ! -e "$mysql_sock" 】;then
printf "Starting MySQL....\n"
/bin/sh ${CmdPath}/mysqld_safe --defaults-file=/data/${port}/my.cnf 2>&1 >/dev/null &
else
printf "MySQL is running...\n"
exit
fi
}
#stop function
function_stop_mysql(){
if 【 ! -e "$mysql_sock" 】;then
printf "MySQL is stopped...\n"
exit
else
printf "Stoping MySQL...\n"
${CmdPath}/mysqladmin -u ${mysql_user} -p${mysql_pwd} -S /data/${port}/mysql.sock shutdown
fi
}
#restart function
function_restart_mysql(){
printf "Restarting MySQL...\n"
function_stop_mysql
sleep 2
function_start_mysql
}
case $1 in
start)
function_start_mysql
;;
stop)
function_stop_mysql
;;
restart)
function_restart_mysql
;;
)
printf "Usage: /data/${port}/mysql{start|stop|restart}\n"
esac
3307实例的启动文件只需修改3306启动文件的端口即可
最终完成后的多实例根/data目录结果如下：
【root@localhost ~】# tree /data
/data
├── 3306
│ ├── data
│ ├── my.cnf #3306实例的配置文件
│ └── mysql #3306实例的启动文件
└── 3307
├── data
├── my.cnf #3307实例的配置文件
└── mysql #3307实例的启动文件
4 directories, 4 files
需要特别说明一下，在多实例启动文件中，启动MySQL不同实例服务，所执行的命令实质是有区别的，例如，启动3306实例的命令如下：
mysqld_safe --defaults-file=/data/3306/my.cnf 2>&1 >/dev/null &
启动3307实例的命令如下：
mysqld_safe --defaults-file=/data/3307/my.cnf 2>&1 >/dev/null &
下面看看在多实例启动文件中，停止MySQL不同实例服务的实质命令。
停止3306实例的命令如下：
mysqladmin -uroot -pyunjisuan123 -S /data/3306/mysql.sock shutdown
停止3307实例的命令如下：
mysqladmin -u root -pyunjisuan123 -S /data/3307/mysql.sock shutdown
5.5 配置MySQL多实例的文件权限
1）通过下面的命令，授权mysql用户和组管理整个多实例的根目录/data
【root@localhost ~】# chown -R mysql.mysql /data
【root@localhost ~】# find /data -name "mysql" | xargs ls -l
-rw-r--r--. 1 mysql mysql 1039 Jul 20 19:33 /data/3306/mysql
-rw-r--r--. 1 mysql mysql 1039 Jul 20 19:34 /data/3307/mysql
2）通过下面的命令，授权MySQL多实例所有启动文件的mysql可执行，设置700权限最佳，注意不要用755权限，因为启动文件里有数据库管理员密码，会被读取到。
【root@localhost ~】# find /data -name "mysql" | xargs chmod 700
【root@localhost ~】# find /data -
