标签：mysql

使用Docker打造MySQL私有云

前言

  • 前几月经常看到有 MySQL 到底能不能放到 Docker 里跑的各种讨论。这样做是错的！这样做是对的！说错的理由也说了一大堆，说对的思想也很明确。大家都有道理。但是我本人觉得这样的讨论落地意义不大。因为对与错还是要实践来得出的。

  • 所以同程旅游也很早开始了 MySQL 的 Docker 化实践，到目前已经有超一千多个 MySQL 实例在 Docker 平台安全稳定地跑着，DB 运维能力发生了质的提高（DBA 再也不用担心删库跑路了）。

  • 当然这样是不是可以证明之前的讨论结论——是对的。我想也不一定，因为我们还只是一只在学飞行的小鸟，还要更多的学习，所以我们特将我们在 MySQL 的 Docker 化上的实践分享给大家。

背景介绍

  • 同程旅游早期的数据库都以 MSSQL 为主，这个产品有个特点就是 UI 操作很棒。但是批量和自动化管理很难做，人力的工作很多。后来逐渐替换为 MySQL 后也是按照传统的运维方式管理。导致大部分的工作需要人肉运维。

  • 当然像我们早期使用过的 MSSQL 也是有优点的：就是单机性能比较好，在当年那个资源不够的年代里我们常可以在高可用的实例上运行多个库。这种情况下物理机数量与实例数量还是比较可控的，相对数量比较少，人肉运维完全可以应对。

  • 但是 MSSQL 的缺陷也很多，比如做水平拆分比较困难，导致数据库成为系统中最大的一个瓶颈。但在我们使用 MySQL+ 中间件（我们做这个中间件也是下了不少心思的，以后可以分享一下）做水平拆分后就开始解决了这个瓶颈。

  • 水平拆分的引入也带来了一个小缺点，就是会造成数据库实例数量大幅上升。举个例子我们做 1024 分片的话一般是做 32 个 node，一主一从是必须的（大部分情况是一主两从），那么至少 64 个实例，再加上应急扩展和备份用的节点那就更多了（中间件的开发者更希望是 1024 片就是 1024 个实例）。

  • 一次上线做一个 32node 分片扩展从库，两个 DBA 足足花了 4 个小时。另外，如果做单机单实例那肯定更不行了，别的不说，成本也会是个大问题，且物理机的资源也未能最大化利用。况且因为 MySQL 单体的性能没优势所以分片居多所以大部分情况下并不是每个库都能跑满整个物理机的。即使有部分能跑满整机资源的库，它的多节点备份，环境一致性和运维动作统一等问题也会让 DBA 一头糟，忙碌又容易出错的工作其实是无意义的。

  • 有了单机多实例运行 MySQL 实例的需求。单机多实例要思考的主要问题就是如果进行资源隔离和限制，实现方案有很多，怎么选？KVM，Docker，Cgroups 是目前的可以实现隔离主流方案。

  • KVM 对一个 DB 的隔离来说太重了，性能影响太大，在生产环境用不合适。这是因为 MySQL 运行的就是个进程而且对 IO 要求比较高，所以 KVM 不满足要求 (虽然优化以后 IO 能有点提升)。

  • cgroups 比较轻，虽然隔离性不是很高，但对于我们的 MySQL 多实例隔离来说是完全够用了（Docker 的资源限制用的就是 cgroups）。但是我们还想针对每个 MySQL 实例运行额外的管理进程 (比如监控等等)。用 cgroups 实现起来会比较复杂，并且我们还想让实例管理和物理机区分开，那 cgroups 也放弃。

  • 至于 Docker，那就很不错了，那些裸用 cgroups 的麻烦它都给搞定了。并且有 API 可以提供支持，开发成本低。而且我们可以基于 Docker 镜像来做部署自动化，那么环境的一致性也可轻松解决。所以最终我们选择了 Docker 作为云平台的资源隔离方案 (当然过程中也做了很多性能、稳定性等的适配工作，这里就不赘述了)。

下面两个图可以形象展示这款产品带来的革命性意义：

未分类

当然要能称之为云，那么平台最基本的要求就是具备资源计算、资源调度功能，且资源分配无需人工参与。对用户来讲，拿到的应该是直接可用的资源，并且天生自带高可用、自动备份、监控告警、慢日志分析等功能，无需用户关心资源背后的事情。其次才是各种日常的 DBA 运维操作需求服务化输出。下面我们就来讲讲我们这个平台是如何一步步实现的。

平台实现过程

站在巨人的肩膀上

我一直认为评价一款数据库的优劣，不能只评价数据库本身。我们要综合它的周边生态是否健全，比如：高可用方案、备份方案、日常维护难度、人才储备等等。当然对于一个云平台也一样，所以我们进行了短平快的试错工作，将平台分为多期版本开发。第一个版本的开发周期比较短，主要用来试验，所以我们要尽可能运用已有的开源产品来实现我们的需求，或者对已有开源产品进行二次开发以后实现定制化的需求。以下是我们当时用到的部分开源产品和技术。

未分类

下面选几个产品简单说一下我们通过它实现什么功能：

  • Percona：我们的备份、慢日志分析、过载保护等功能都是基于 pt-tools 工具包来实现的。

  • Prometheus：性能优越且功能强大的 TSDB，用于实现整个平台实例的监控告警。缺点是没有集群功能，单机性能是个瓶颈 (虽然单机的处理能力已经很强了)，所以我们在业务层面进行了 DB 拆分，实现了分布式存储及扩展。

  • Consul：分布式的服务发现和配置共享软件，配合 prometheus 实现监控节点注册。

  • Python：管理 Docker 容器中 MySQL 实例的 agent 以及部分操作脚本。

  • Docker：承载 MySQL 实例并实现资源隔离和资源限制。

总体架构

未分类

容器调度系统如何选择

容器调度的开源产品主要有 Kubernetes 和 mesos，但是我们并没有选用这两个。主要原因是我们内部已经开发了一套基于 Docker 的资源管理、调度的系统，至今稳定运行 2 年多了。这套架构稍作修改是符合需求的。

另外第三方的资源调度系统兼容我们目前的高可用架构，其他自动化管理有些难度，同时资源分配策略也需要定制化。所以最终还是选择采用了自研的资源调度管理。适合自己现状的需求才是最好的。当然后面有机会做到计算调度和存储调度分离的情况下我们可能会转向 Kubernetes 的方案。

工作原理

我们就拿创建集群来举例吧。当平台发起一个创建集群的任务后，首先会根据集群规模 (一主一从还是一主多从，或者是分片集群) 确定要创建的实例数量，然后根据这个需求按照我们的资源筛选规则 (比如主从不能在同一台机器、内存配置不允许超卖等等)，从现有的资源池中匹配出可用资源，然后依次创建主从关系、创建高可用管理、检查集群复制状态、推送集群信息到中间件 (选用了中间件的情况下) 控制中心、最后将以上相关信息都同步到 CMDB。

以上的每一个工作都是通过服务端发送消息到 agent，然后由 agent 执行对应的脚本，脚本会返回指定格式的执行结果，这些脚本是由 DBA 开发的。这种方式的优势在于，DBA 比任何人都了解数据库，所以通过这种方式可以有效提升项目开发效率，也能让 DBA 参与到项目当中去。开发只需要写前台逻辑，DBA 负责后端具体执行的指令。如果未来功能有变更或迭代的话，只需要迭代脚本即可，维护量极小。

资源的调度分配原则

经过对同程多年的 DB 运维数据分析得到如下经验：

  • CPU 最大超卖 3 倍，内存不超卖；

  • 同一机房优先选择资源最空闲的机器；

  • 主从角色不允许在同一台机器上；

  • 若有 VIP 需求的主从端口需要一致，无 VIP 需求直接对接中间件的无端口一致的限制；

  • 分片的集群将节点分布在多台物理机上；

产品分类

未分类

核心功能

未分类

以上是已经上线的部分核心功能，还有很多功能就不再一一展示。

备份恢复系统

未分类

备份工具我们是用 percona-xtrabackup。通过流备份的方式将数据备份到远端的备份服务器。备份服务器有多台，分别按照所属机房划分。

我们提供了手工备份和定时备份来满足不同场景的需求。多实例备份一定要关注磁盘 IO 和网络，所以我们的备份策略会限制单个物理机上并行备份的数量，另外单个机房备份任务队列的并行度也有控制，确保并行备份任务始终保持到我们指定的数量。

假如整个机房并行的是 50 个任务，那么这 50 个当中如果有 5 个提前备份完成，那么会新加入 5 个等待备份的任务进入这个备份队列。我们后来改造了备份的存储方式，直接将备份流入分式存储。

监控告警系统

未分类

在上线这套云平台前，我们还是用传统的 zabbix 来实现监控告警的。zabbix 的功能的确非常强大，但是后端的数据库是个瓶颈，当然可以通过数据库拆分的方式解决。

数据库要监控的指标比较多，如果采集的项目比较多，zabbix 就需要加 proxy，架构越来越复杂，再加上和我们平台对接的成本比较高，对一些复杂的统计类查询 (95 值、预测值等) 性能比较差。

所以我们选了一款 TSDB——prometheus，这是一款性能极强、极其适合监控系统使用的时序性数据库。prometheus 优点就是单机性能超强。但凡事又有两面性，它的缺点就是不支持集群架构 (不过我们解决了扩展的问题，下面会讲到)。

prometheus 的使用应该是从一年前就开始的，那时候我们只是把它作为辅助的监控系统来使用的，随着逐渐熟悉，越来越觉得这个是容器监控的绝佳解决方案。所以在上云平台的时候就选择了它作为整个平台的监控系统。

监控数据采集

prometheus 是支持 pushgateway 和 pull 的方式。我们选用了 pull 的方式。因为结构简单，开发成本低的同时还能和我们的系统完美对接。consul 集群负责注册实例信息和服务信息，比如 MySQL 实例主从对应的服务、Linux 主从对应的服务、容器注册对应的服务。然后 prometheus 通过 consul 上注册的信息来获取监控目标，然后去 pull 监控数据。监控客户端是以 agent 的形式存在，prometheus 通过 HTTP 协议获取 agent 端采集到的数据。

监控指标画图

不得不说 grafana 是监控画图界的扛把子，功能齐全的度量仪表盘和图形编辑器，经过简单配置就能完成各种监控图形的展示。然后我们打通了云平台和 grafana 的关联，用户在云平台需要查看实例或集群信息，只要点击按钮即可。

未分类

告警管理

告警管理分为：告警发送、告警接收人管理、告警静默等功能。prometheus 有一个告警发送模块 alertmanager，我们通过 webhook 的方式让 alertmanager 把告警信息发送到云平台的告警 API，然后在云平台来根据后面的逻辑进行告警内容发送。

alertmanager 推过来的只是实例纬度的告警，所以我们结合告警平台的实例相关信息，会拼出一个多维信息的告警内容。让 DBA 一看就知道是谁的哪个集群在什么时间触发了什么等级的什么告警。告警恢复后也会再发一次恢复的通知。

未分类

alertmanager 也是功能强大的工具，支持告警抑制、告警路由策略、发送周期、静默告警等等。有需要可以自行配置。但是这种和平台分离的管理方式不是我们想要的，所以就想把 alertmanager 对告警信息处理的这部分功能集成到云平台内。

但是官方文档并没有提及到 alertmanager 的 API，通过对源码的分析，我们找到了告警管理相关的 API。然后 alertmanager 的原生 UI 上操作的功能完美移植到了我们的云平台，同时新增了实例相关集群名称、负责人等更多纬度的信息。

下面是一些操作样例：

当前告警：

未分类

添加告警静默：

未分类

已创建的静默规则：

未分类

慢日志分析系统

未分类

慢日志的收集是通过 pt-query-digest 每小时进行本地分析，分析完成以后将结果写入慢日志存储的数据库来完成的。当然如果用户需要立刻查看当前慢日志的情况，也可以在界面点击慢日志分析。分析完成后可以在 UI 界面点击慢日志查看，就能看到该实例的慢日志分析结果。它同时集成了 explain、查看 table status 等功能。

集群管理

集群管理作为该平台的核心功能之一，占据了整个平台 70% 的工作。这些功能就是 DBA 运维中经常需要用到的。我们的设计思路是以集群为单位，所以同时只能操作一个集群上的实例。这样就不会在一个页面上显示过多无用的信息，看着乱还有可能导致误操作。看了下图中的这些功能就能更明白为什么要这么设计了。

未分类

图中只是一部分，还有部分未展示出的功能 (集成中间件、Dashboard、黑屏诊断窗口等)，在后版中功能更多。

高可用

高可用方案我们使用了目前最流行的 MySQL 高可用方案 MHA。MHA 的优缺点就不在这里讲了，有 DBA 同学的应该都已经很熟悉了。这里我说一下我们基于同程业务做的调整。

GTID

因为我们主要使用的 MariaDB，但是 MHA 最新版本也是不能支持 MariaDB 的 GTID 切换。所以我们在原有的基础上做了改进，支持了 MariaDB 的 GTID。使用 GTID 以后灵活切换是一个方面，另外一个方面是 sync_master_info 和 sync_relay_log_info 就不需要设置成 1 了 (MariaDB 不支持写 table，只能写 file)，极大减少了从库复制带来的 IOPS。

切换时调整相关参数

我们在切换时调整 sync_binlog 和 innodb_flush_log_at_trx_commit 参数，这两个参数是决定数据落盘方式的，默认大家都是设置双 1。这样相对数据最安全，但是 IO 也最高。

云服务的多实例部署会导致一台物理机上既有 master 又有 slave。我们肯定不希望 slave 产生太高的 IO 影响到同机器的其他 slave(虽然可以 IO 隔离，但是优先降低不必要 IO 才靠谱)。所以理论上来说 Master 上面设置双 1，slave 则可以不这样设置。但是切换后原来的 salve 可能会变成了 master。所以我们默认 slave 非双 1，在 MHA 切换的时候会自动将新 master 的这两个参数设置为 1。

哨兵

我们在多个点部署了哨兵服务。这个哨兵是一个简单的 API 服务，带上响应的参数可以请求到指定的实例。当 MHA manager 检测到有 Master 无法连接时，会触发 secondary check 机制，带着 master 相关信息请求哨兵节点的 API，根据哨兵节点返回情况，若超过半数无法连接则切换。否则放弃切换。

高可用切换对接 DB 中间件

未分类

DB 中间件和 DB 通过物理 IP 连接，当发生高可用切换时将最新的 Master IP、Master port 信息推送到 DB 中间件控制中心，DB 中间件拿到配置后立刻下发并生效。

实例、库迁移

未分类

迁移功能初衷是为了将平台外的实例或者库迁移到平台里面来，后来随着逐渐使用发现这个功能可挖掘的空间很大，比如可以做平台内库表拆分等需求。实现原理也很简单，用 mydumper 将指定数据备份下来以后，再用 myloader 恢复到指定数据库。

这是一个全量的过程，增量复制用的是用我们自己开发的一个支持并行复制的工具，这个工具还支持等幂处理，使用更灵活。没有用原生复制的原因是，假如要将源实例多个库中的一个库迁移到目标实例，那么原生复制就需要对 binlog 做复制过滤，这里面涉及到配置修改，实例重启，所以果断不考虑。

实现过程并没有高大上，但是完全满足需求。当然 mydumper 和 myloader 也有一些问题，我们也做了小改动以后才实现的。后面我们计划用流的方式去做数据导出导入 (类似于阿里开源的 datax)。

迁移完成，增量无延迟的情况下，大家会关心迁移前后数据一致性的问题，我们提供了自研的数据校验工具。实测 300G 的数据校验时间约为 2 至 3 分钟，快慢取决于开多少线程。

屏蔽底层物理资源

对用户来讲，平台提供的是一个或一组数据库服务，不需要关系后端的实例是在哪台机器上。资源计算和调度全部由系统的算法进行管理。

提升资源利用率 (CPU、内存)

通过单机多实例，CPU 资源可超卖，有效提高 CPU 资源的利用。内存资源未超卖，但是可以控制到每个实例的内存使用，确保每个实例都能有足够的内存。若有剩余内存，则继续分配容器即可，不 OOM 的情况下压榨内存资源。

提升运维效率

效率的提升得益于标准化以后带来的自动化。批量运维的成本很低。以前部署一套分片集群需要花费将近 6 个小时 (不包含对接中间件的 1 到 2 个小时)，而现在只需要 5 分钟即可部署完成。并且部署完成以后会将提供一套中间件 +DB 分片集群的服务。

精细化管理

平台上线后有效提高了资源利用率，同时我们按照 1 库 1 实例的方式，可以有效避免不同库的压力不均导致相互影响的问题。并且性能监控也能精准到库级别。

Docker安装部署MySQL5.7

1、进入Linux后，使用Docker命令下载MySQL，命令如：

docker pull mysql:5.7

运行该命令后，则会显示以下日志：

[root@localhost ~]# docker pull mysql:5.7
5.7: Pulling from library/mysql
ad74af05f5a2: Pull complete 
0639788facc8: Pull complete 
de70fa77eb2b: Pull complete 
724179e94999: Pull complete 
50c77fb16ba6: Pull complete 
d51f459239fb: Pull complete 
937bbdd4305a: Pull complete 
35369f9634e1: Pull complete 
f6016aab25f1: Pull complete 
5f1901e920da: Pull complete 
fdf808213c5b: Pull complete 
Digest: sha256:96edf37370df96d2a4ee1715cc5c7820a0ec6286551a927981ed50f0273d9b43
Status: Downloaded newer image for mysql:5.7

2、先查看本机都有哪些镜像，命令如下：

docker images

[root@localhost ~]# docker images
REPOSITORY          TAG                 IMAGE ID            CREATED             SIZE
mysql               5.7                 c73c7527c03a        8 days ago          412MB
hello-world         latest              1815c82652c0        7 weeks ago         1.84kB
java                latest              d23bdf5b1b1b        6 months ago        643MB
learn/tutorial      latest              a7876479f1aa        4 years ago         128MB

3.然后启动我们的mysql的docker容器，命令如下：

docker run --name mysql5.7 -p 3306:3306 -e MYSQL_ROOT_PASSWORD=123456 -d mysql:5.7

注意，这里的容器名字叫：mysql5.7，mysql的root用户密码是：123456，映射宿主机子的端口3306到容器的端口3306，仓库名mysql和标签(tag）唯一确定了要指定的镜像，其实如果这里只有一个mysql也有必要要tag，执行该命令返回的结果是：

9238d9feb10a0c553d950451add144727b659a0972ccf04d7c59c3bfa198ed20

4.查看已经运行的的所有容器，命令如：docker ps

[root@localhost ~]# docker ps 
CONTAINER ID        IMAGE               COMMAND                  CREATED             STATUS              PORTS                    NAMES
9238d9feb10a        mysql:5.7           "docker-entrypoint..."   12 seconds ago      Up 9 seconds        0.0.0.0:3306->3306/tcp   mysql5.7
[root@localhost ~]#

5.使用mysql的工具，比如navicat连接成功如下：

未分类

Ubuntu 16.04 apt安装Nginx PHP5.6 MySQL5.6

在Ubuntu 16.04中使用源安装Nginx+PHP5.6+MySQL5.6的方法。

安装Nginx

1、首先添加nginx_signing.key(必须，否则出错)

$ wget http://nginx.org/keys/nginx_signing.key

$ sudo apt-key add nginx_signing.key

2、添加]Nginx](http://nginx.org/)官方提供的源

$ echo "deb http://nginx.org/packages/ubuntu/ trusty nginx" >> /etc/apt/sources.list

$ echo "deb-src http://nginx.org/packages/ubuntu/ trusty nginx" >> /etc/apt/sources.list

3、更新源并安装Nginx

$ sudo apt-get update

$ sudo apt-get install nginx

4、安装Nginx完成后可查看版本号，输入

$ /usr/sbin/nginx -v

安装PHP5.6

1、添加PPA

$ sudo apt-get install python-software-properties software-properties-common

$ sudo add-apt-repository ppa:ondrej/php

$ sudo apt-get update

2、安装PHP5.6以及所需的一些扩展

$ sudo apt-get install php5.6-fpm php5.6-mysql php5.6-common php5.6-curl php5.6-cli php5.6-mcrypt php5.6-mbstring php5.6-dom

3、配置PHP5.6

打开php.ini配置文件:

$ sudo vim /etc/php/5.6/fpm/php.ini

找到cgi.fix_pathinfo选项，去掉注释;，然后将值设置为0:

cgi.fix_pathinfo = 0;
display_errors  =  On

location ~ .php$ {
          # include snippets/fastcgi-php.conf;
        #
          # # With php7.0-cgi alone:
        # fastcgi_pass 127.0.0.1:9000;
        # # With php7.0-fpm:
                fastcgi_param SCRIPT_FILENAME     documentroot fastcgi_script_name;
                  fastcgi_pass unix:/run/php/php5.6-fpm.sock;
                  fastcgi_index index.php;
                  include fastcgi_params;
}

安装MySQL

$ sudo apt-get install mysql-server-5.6 mysql-client-5.6

途中会提示设置MySQL的密码，安装好后：

$ mysql -uroot -p

然后输入刚刚设置的密码，能成功进入即成功安装。

Ubuntu下迁移通过apt安装的MySQL数据库文件目录

用Ubuntu的apt包管理工具安装的MySQL数据库，默认将数据库文件保存在/var/lib/mysql目录下，时间久了数据库越来越大，所以准备挂载个新的硬盘专门存放mysql数据库。

1、确定mysql数据库文件存放目录

一般默认是在/var/lib/mysql目录下。先登录自己的mysql数据库，比如我用root账户登录，然后使用下面查询语句查询：

show variables like ‘%dir%’;

得到数据库文件配置信息：

未分类

可以看到其中datadir的值为/var/lib/mysql/即为当前数据库文件存放目录。

另外一个basedir参数表示mysql数据库的安装位置，迁移数据库文件位置不需要改动这个参数。

2、迁移数据库文件到新的目录下

先使用下面命令将mysql数据库服务停止：

[plain] view plain copy

sudo /etc/init.d/mysql stop

我新的数据盘挂载在/mnt/data目录下，因此要将数据库迁移到/mnt/data。

2.1 可以使用mv命令将原数据库目录文件移动到新的目录，好处是不会简单，不会修改原数据库文件的权限，以及用户和用户组归属：

[plain] view plain copy

sudo mv /var/lib/mysql /mnt/data/

2.2 也可以使用cp复制命令将原数据库目录文件复制到新的目录，好处是。。万一迁移失败，恢复工作相对简单一点，等确认迁移成功再来删掉原数据库目录文件也不迟。为了不影响复制过来数据库目录文件权限和用户用户组归属问题，使用cp命令时要加上-a参数：

[plain] view plain copy

sudo cp -a /var/lib/mysql /mnt/data/

注：由于/var/lib/mysql目录归属于mysql数据库创建的mysql用户和mysql用户组，所以迁移文件的时候需要使用root权限，命令要使用sudo

迁移成功后，可以看到/mnt/data/目录下已经将mysql数据库文件迁移过来了，并且目录文件的用户用户组归属还是mysql，没有变化：

未分类

3、修改配置文件

一共有三个配置文件需要修改：

3.1 my.cnf文件

mysql数据库会按顺序优先级从/etc/my.cnf、/etc/mysql/my.cnf、/usr/etc/my.cnf、~/.my.cnf四个位置找my.cnf配置文件，一旦找到就不再继续往下找。Ubuntu默认将my.cnf配置文件放在/etc/mysql/my.cnf位置，所以在/etc/my.cnf位置没有找到这个配置文件。

选择自己使用的文本编辑器编辑my.cnf配置文件，我用vim，所以sudo vim /etc/mysql/my.cnf。一样需要sudo，使用root权限编辑。将其中[mysqld]标签下的datadir属性值改为新数据库目录路径/mnt/data/mysql，如图：

未分类

修改后保存并退出。

3.2 usr.bin.mysqld文件

由于Ubuntu使用了apparmor安全模块，就是类似于沙盒运行的一种机制，它可以限制软件在运行时的一些行为，比如对哪些目录和文件可以读写加锁等等。

由于修改了数据库文件路径，所以要修改mysql数据库的apparmor配置文件，在其中将新数据库文件目录和文件的读写及加锁权限添加上去，同时可以删除或者注释掉原先/var/lib/mysql数据库文件目录的权限。mysql数据库的apparmor配置文件路径在/etc/apparmor.d/usr.sbin.mysqld。使用下面命令编辑这个配置文件：

sudo vim /etc/apparmor.d/usr.sbin.mysqld

找到其中的

/var/lib/mysql/ r,
/var/lib/mysql/** rwk,

两行权限声明，可以在前面加上#好注释掉。然后对照格式，加入新路径的权限声明：

/mnt/data/mysql/ r,
/mnt/data/mysql/** rwk,

结果如图：

未分类

保存并关闭。

3.3 abstractions/mysql文件

由于usr.bin.mysqld文件中引用了abstractions/mysql文件，也就是会将abstractions/mysql文件中的权限声明导入进来。因此，也修改下这个文件：

sudo vim /etc/apparmor.d/abstractions/mysql

同样也是将新数据库文件路径中的socket文件权限添加进去，同时可以删除或者注释掉全路径中申请的权限，效果如图：

未分类

保存后退出。

4、重启数据库

配置文件修改成功后就可以重启数据库，重启数据库之前需要先重新载入apparmor配置文件，使用下面命令重新载入：

[plain] view plain copy

sudo /etc/init.d/apparmor restart

重载成功就可以使用下面命令启动数据库：

[plain] view plain copy

sudo /etc/init.d/mysql start

5、权限问题

经过上诉步骤之后，你有可能数据库无法启动。忽略继续登录数据库出现下面关于sock的错误：

ERROR 2002 (HY000): Can’t connect to local MySQL server through socket ‘/var/run/mysqld/mysqld.sock’ (2)

查看数据库的启动错误日志，sudo vim /var/log/mysql/error.log，还能看到Table ‘plugin’ is read only这样的错误：

未分类

出现这种情况的原因还是在于新数据库文件目录的权限。

mysql数据库启动的时候需要以mysql用户的身份执行，所以mysql用户需要具备能读写数据库文件目录的权限。虽然上面迁移数据库文件的时候无论是使用mv还是cp -a命令都没有更改mysql目录的用户和用户组，上面也看到了/mnt/data/mysql所属的用户和用户组都还是mysql。因此可以肯定mysql用户具备读写/mnt/data/mysql的权限，但是这并没有保证上级目录/mnt/data和上上级目录/mnt也具备让mysql用户读取的权限。如果mysql用户不具备上级目录/mnt/data和上上级目录/mnt的读取权限，mysql用户一样读写不了自己的/mnt/data/mysql目录，因此就会出现上面的问题。

可以过头来看看原本数据库文件目录/var/lib/mysql的结构：

未分类

可以看出原本数据库文件目录/var/lib/mysql的上级目录/var/lib属于虽然属于root用户，但是它为同组用户和其它组用户都开放了’r’和‘x’权限，所以即使上级目录不属于mysql用户，mysql用户同样也能正常进入并访问到自己的数据库文件。

解决这个问题的方法说起来就是这么简单，只要保证mysql用户具备最终数据库文件目录的所有上级目录的‘r’和‘x’权限就可以了。

例如，使用下面命令修改本文中/mnt/data的权限：

sudo chmod 755 /mnt/data

再上级目录/mnt是系统目录，归属于root用户，root用户默认的目录的权限都是755，所以不用修改。

权限修改后再次启动数据库sudo /etc/init.d/mysql start，应该就能成功启动了。

进入数据库，查看当前路径配置信息：

未分类

数据库已经正常启动， 并且数据库文件路径也已经替换到/mnt/data/mysql目标路径，数据库文件迁移成功。

Redis缓存数据提高访问性能并同步到mysql永久保存

Redis 是一个高性能的key-value数据库。 redis的出现，很大程度上补偿了memcached这类key-value存储的不足，在部分场合可以对关系数据库起到很好的补充作用。它提供了Python，Ruby，Erlang，PHP客户端，使用很方便。由于游戏服务器数据更新频繁，如果每次读写数据会造成读写数据库的压力大，效率低。

redis的使用场景：

首先，程序先去redis中判断数据是否存在：如果存在，则直接返回缓存好的数据。如果不存在的话，就会去数据库中，读取数据，并把数据缓存到Redis中。

适用场合：如果数据量比较大，但不是经常更新的情况(比如用户排行)。
redis的流程图如下：

未分类

首先先去redis中判断数据是否存在：

  • 如果存在，则直接更新对应的数据，并把更新后的数据返回给页面。
  • 如果不存在的话，就会去先更新数据库中内容，然后把数据保存一份到Redis中。

后台会有Linux的contrab定时任务机制把Redis中的save_update_keys存储的key，分别读取出来，找到对应的数据，更新到DB中。

优点：这个流程的主要目的是把Redis当作数据库使用，更新获取数据比DB快。非常适合大数据量的频繁变动(比如用户游戏数据)。

缺点：对Redis的依赖很大，要做好宕机时的数据保存。(不过可以使用redis的快照AOF，快速恢复的话，应该不会有多大影响，因为就算Redis不工作了，也不会影响后续数据的处理。)

难点：在前期规划key的格式，存储类型很重要，因为这会影响能否把数据同步到DB。

其中第二种方式是游戏服务器中经常使用的方式，例如将用户的游戏数据以hashset的形式存储到redis中，实时更新效率比访问数据库的效率高。根据定时任务将更新的数据存入数据库中，做到永久保存。

使用percona-xtrabackup(innobackupex)工具快速配置mysql5.6.34主从同步复制

percona-xtrabackup工具实现mysql5.6.34的主从同步复制

未分类

下载并安装percona-xtrabackup工具

# wget https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.4.7/binary/redhat/6/x86_64/percona-xtrabackup-24-2.4.7-1.el6.x86_64.rpm

# yum localinstall -y percona-xtrabackup-24-2.4.7-1.el6.x86_64.rpm

1、备份，将MySQL数据库整个备份到/opt/目录下

# innobackupex --defaults-file="/etc/my.cnf" --user=root -proot --socket=/tmp/mysql.sock /opt

2、预处理，进行事物检查(也可以拷贝到从库后再进行检查)

# innobackupex --defaults-file="/etc/my.cnf" --user=root -proot --socket=/tmp/mysql.sock --apply-lg --use-memory=1G /opt/2017-05-18_00-13-42/

3、scp到从库

[root@centossz008 ~]# scp -r /opt/2017-05-18_01-34-42/ 192.168.3.13:/opt

4、关闭从库，清理从库数据，恢复数据到从库

/etc/init.d/mysqld stop

删除从库的数据和日志信息

[root@node5 ~]# rm -rf /data/mydata/*
[root@node5 ~]# rm -rf /data/binlogs/*
[root@node5 ~]# rm -rf /data/relaylogs/*

在从库上执行(将数据恢复到数据库中)

[root@node5 ~]# innobackupex --defaults-file="/etc/my.cnf" --user=root --socket=/tmp/mysql.sock --move-back /opt/2017-05-18_01-34-42/

5、修改权限，启动从库

[root@node5 mydata]# chown -R mysql.mysql /data
[root@node5 mydata]# /etc/init.d/mysqld start

查看主库中master位置

[root@node5 mydata]# cat /opt/2017-05-18_01-34-42/xtrabackup_binlog_info 
master-bin.000002    191    4c6237f8-a7da-11e6-9966-000c29f333f8:1-2

6、主库中创建建salve同步用户

mysql> grant replication slave,reload,super on *.* to [email protected] identified by 'replpass';
mysql> FLUSH PRIVILEGES;

7、从库执行同步

mysql> change master to master_host='192.168.3.12',master_user='repluser',master_password='replpass',master_log_file='master-bin.000002',master_log_pos=191;
mysql> start slave;

mysql> show slave statusG
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.3.12
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-bin.000002
Read_Master_Log_Pos: 586
Relay_Log_File: relay-bin.000002
Relay_Log_Pos: 710
Relay_Master_Log_File: master-bin.000002
Slave_IO_Running: Yes # 表示配置成功
Slave_SQL_Running: Yes
Replicate_Do_DB: 
Replicate_Ignore_DB: 
Replicate_Do_Table: 
Replicate_Ignore_Table: 
Replicate_Wild_Do_Table: 
Replicate_Wild_Ignore_Table: 
Last_Errno: 0
Last_Error: 
Skip_Counter: 0
Exec_Master_Log_Pos: 586
Relay_Log_Space: 908
Until_Condition: None
Until_Log_File: 
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File: 
Master_SSL_CA_Path: 
Master_SSL_Cert: 
Master_SSL_Cipher: 
Master_SSL_Key: 
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error: 
Last_SQL_Errno: 0
Last_SQL_Error: 
Replicate_Ignore_Server_Ids: 
Master_Server_Id: 100
Master_UUID: 4c6237f8-a7da-11e6-9966-000c29f333f8
Master_Info_File: /data/mydata/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
Master_Retry_Count: 86400
Master_Bind: 
Last_IO_Error_Timestamp: 
Last_SQL_Error_Timestamp: 
Master_SSL_Crl: 
Master_SSL_Crlpath: 
Retrieved_Gtid_Set: 4c6237f8-a7da-11e6-9966-000c29f333f8:3-4
Executed_Gtid_Set: 4c6237f8-a7da-11e6-9966-000c29f333f8:3-4
Auto_Position: 0
1 row in set (0.00 sec)

延迟复制：

启用方法：

mysql> stop slave;
mysql> change master to master_delay=600;
mysql> start slave;

应用场景：

1、误删除恢复
2、延迟测试(当有延迟时业务是否会受影响)
3、历史查询

mysql master 配置

[root@centossz008 ~]# cat /etc/my.cnf 
[client]
port = 3306
socket = /tmp/mysql.sock
default-character-set = utf8mb4

[mysqld]
port = 3306
innodb_file_per_table = 1
binlog-format=ROW
log-slave-updates=true
gtid-mode=on 
enforce-gtid-consistency=true
master-info-repository=file
relay-log-info-repository=file
sync-master-info=1
slave-parallel-workers=4
binlog-checksum=CRC32
master-verify-checksum=1
slave-sql-verify-checksum=1
binlog-rows-query-log_events=1
server-id=100
report-port=3306
log-bin=/data/binlogs/master-bin
max_binlog_size = 200M
datadir=/data/mydata
socket=/tmp/mysql.sock
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES


init-connect = 'SET NAMES utf8mb4'
character-set-server = utf8mb4

skip-name-resolve
skip-external-locking

back_log = 300
max_connections = 1024
max_connect_errors = 6000
open_files_limit = 65535
table_open_cache = 128
max_allowed_packet = 4M
binlog_cache_size = 1M
max_heap_table_size = 8M
tmp_table_size = 16M

read_buffer_size = 2M
read_rnd_buffer_size = 8M
sort_buffer_size = 8M
join_buffer_size = 8M
key_buffer_size = 4M
thread_cache_size = 8
query_cache_type = 1
query_cache_size = 16M
query_cache_limit = 2M
ft_min_word_len = 4
expire_logs_days = 10
performance_schema = 0
explicit_defaults_for_timestamp

default_storage_engine = InnoDB
innodb_open_files = 500
innodb_buffer_pool_size = 64M
innodb_write_io_threads = 4
innodb_read_io_threads = 4
innodb_thread_concurrency = 4
innodb_purge_threads = 1
innodb_flush_log_at_trx_commit = 2
innodb_log_buffer_size = 2M
innodb_log_file_size = 32M
innodb_log_files_in_group = 3
innodb_max_dirty_pages_pct = 90
innodb_lock_wait_timeout = 120

bulk_insert_buffer_size = 8M
myisam_sort_buffer_size = 8M
myisam_max_sort_file_size = 512M
myisam_repair_threads = 1

interactive_timeout = 28800
wait_timeout = 28800

[mysqldump]
quick
max_allowed_packet = 16M

[myisamchk]
key_buffer_size = 8M
sort_buffer_size = 8M
read_buffer = 4M
write_buffer = 4M

mysql slave 配置

[root@node5 src]# cat /etc/my.cnf 
[client]
port = 3306
socket = /tmp/mysql.sock
default-character-set = utf8mb4

[mysqld]

port = 3306
innodb_file_per_table = 1
binlog-format=ROW
log-slave-updates=true
gtid-mode=on 
enforce-gtid-consistency=true
master-info-repository=file
relay-log-info-repository=file
sync-master-info=1
slave-parallel-workers=4
binlog-checksum=CRC32
master-verify-checksum=1
slave-sql-verify-checksum=1
binlog-rows-query-log_events=1
server-id=200
report-port=3306
log-bin=/data/binlogs/master-bin
relay-log=/data/relaylogs/relay-bin
max_binlog_size = 200M
datadir=/data/mydata
socket=/tmp/mysql.sock
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

init-connect = 'SET NAMES utf8mb4'
character-set-server = utf8mb4

skip-name-resolve
skip-external-locking

back_log = 300
max_connections = 1024
max_connect_errors = 6000
open_files_limit = 65535
table_open_cache = 128
max_allowed_packet = 4M
binlog_cache_size = 1M
max_heap_table_size = 8M
tmp_table_size = 16M

read_buffer_size = 2M
read_rnd_buffer_size = 8M
sort_buffer_size = 8M
join_buffer_size = 8M
key_buffer_size = 4M
thread_cache_size = 8
query_cache_type = 1
query_cache_size = 16M
query_cache_limit = 2M
ft_min_word_len = 4
expire_logs_days = 10
performance_schema = 0
explicit_defaults_for_timestamp

default_storage_engine = InnoDB
innodb_open_files = 500
innodb_buffer_pool_size = 64M
innodb_write_io_threads = 4
innodb_read_io_threads = 4
innodb_thread_concurrency = 4
innodb_purge_threads = 1
innodb_flush_log_at_trx_commit = 2
innodb_log_buffer_size = 2M
innodb_log_file_size = 32M
innodb_log_files_in_group = 3
innodb_max_dirty_pages_pct = 90
innodb_lock_wait_timeout = 120

bulk_insert_buffer_size = 8M
myisam_sort_buffer_size = 8M
myisam_max_sort_file_size = 512M
myisam_repair_threads = 1

interactive_timeout = 28800
wait_timeout = 28800

[mysqldump]
quick
max_allowed_packet = 16M

[myisamchk]
key_buffer_size = 8M
sort_buffer_size = 8M
read_buffer = 4M
write_buffer = 4M

cenos6上启动mysql服务报错：

070517 23:08:52 mysqld_safe Starting mysqld daemon with databases from /data/mydata
2107-05-17 23:08:56 0 [ERROR] This MySQL server doesn't support dates later then 2038
2107-05-17 23:08:56 0 [ERROR] Aborting

将时间修改为1年前，即可启动，启动完成后改回时间即可

mysql主(master)从(slave)数据库复制的配置

主从数据库作用：

1、 对数据库进行读写分离，提高系统性能。主服务器专注于写操作，从数据库专注于读操作。
2、 从(slave)数据库在不破坏主数据库数据的情况下，执行数据备份。
3、 用主数据库产生实时数据，从(slave)数据库进行数据的分析。
4、 远程数据分布：创建远程站点的本地数据副本，不需要永久的访问主服务器。

步骤：

1、修改主服务器数据库的配置文件(my.ini或my.cnf)，配置如下内容。

[mysqld]   
log-bin=mysql-bin   
server-id=156       //这里一般去服务器ip位数

2、 为执行复制的从数据创建用户、密码，并分配权限。

mysql> CREATE USER 'cenliming'@'%' IDENTIFIED BY 'password';   //'%'代表允许 该用户远程访问数据库   
mysql> GRANT  REPLICATION  SLAVE ON  *.*   TO 'user'@'%';

注意：如果在本地测试，要配置本地数据库允许远程连接。

3、 获取二进制日志文件信息，若显示为空，请重启数据库即可查看到。

mysql > SHOW MASTER STATUS;     
+------------------+----------+--------------+------------------+   
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |   
+------------------+----------+--------------+------------------+   
| mysql-bin.000003 | 73       | test         | manual,mysql     |   
+------------------+----------+--------------+------------------+

(File:日志文件名 Position: 文件坐标)

4、 在主数据库中释放表锁，在从数据库中进行相关配置。 a. 主数据库释放锁：

mysql> UNLOCK TABLES;

修改从数据库配置文件，配置server-id：

[mysqld]   
server-id=232

在从数据库中进行连接主数据库的配置：

mysql> CHANGE MASTER TO  
    ->     MASTER_HOST='master_host_name',   
    ->     MASTER_USER='replication_user_name',   
    ->     MASTER_PASSWORD='replication_password',   
    ->     MASTER_LOG_FILE='recorded_log_file_name',   
    ->     MASTER_LOG_POS=recorded_log_position;

5、 启动从服务器并查看状态：

mysql> start  slave;     //停止用 stop   
mysql>show slave stausG;

未分类

Slave_IO_Running状态不为YES，说明从数据库未能成功连接到主数据库。 配置成功后，你对主数据库的更新操作都会更新到从数据库（异步处理）。

mysql主从配置步骤

基础环境

  • 系统：linux
  • mysql版本：5.5
  • 主服务器IP：192.168.1.101
  • 从服务器IP：192.168.1.102

1、 主服务器（master）要打开二进制日志
2、 从服务器（slave）要打开relay日志
3、 在主服务器（master）上建立replcation账号授权给从服务器（slave）
4、 修改从服务器（slave）的master服务器
5、 启动slave

操作步骤：

1、 主库的操作

vim /etc/my.cnf

然后在[mysqld]下添加如下配置

#给服务器起一个唯一的id
server-id=1
#开启二进制日志
log-bin=mysql-bin
#指定日志格式
binlog-format=mixed

保存后重启mysql

2、 从库操作

vim /etc/my.cnf

然后在[mysqld]下添加如下配置

#给服务器起一个唯一的id
server-id=2
#从服务器中继日志
relay-log=mysql-relay

保存后重启mysql

3、 在主服务器上创建相应的复制账号

grant replication client,replication slave on *.* to replName@’192.168.%.%’ identified by ‘123456’;

其中是replName是帐号名，123456是密码，这两项都可以自己修改

4、 在从服务器通过语句指定要复制的主服务器(注意,可以一主多从,不可一从多主).

change master to 
master_host=’192.168.1.101’, 
master_user=’replName’, 
master_password=’123456’, 
master_log_file=’mysql-bin.000001’, 
master_log_pos=0;

说明：

  • master_host是主服务器IP
  • master_user是刚才设置的主服务器复制帐号
  • master_password是刚才设置的主服务器复制帐号密码
  • master_log_file是主服务器二进制日志文件
  • master_log_pos是复制二进制文件的开始点
  • master_log_pos和master_log_file可以在主数据库中通过执行show master status;获取到

5、 启动slave

slave start

注意防火墙设置

主主配置

主从配置搞完以后，主主配置就很简单了，

基本思路：

1、 2台服务器都设置上2进制日志和relay日志
2、 都设置上replcation复制账号
3、 都设置对方为自己的master

开启mysql general log记录sql日志

对于在线的mysql修改如下:

mysql> show variables like 'general%';
Current database: loldb***
+------------------+------------------------+
| Variable_name | Value |
+------------------+------------------------+
| general_log | OFF |
| general_log_file | /data/mysql/var/s4.log |
+------------------+------------------------+
2 rows in set (0.03 sec)
mysql> SET GLOBAL general_log = 1;

然后去查看/data/mysql/var/s4.log 文件的内容，就可以看到sql语句

或在配置文件/etc/my.cnf里面[mysqld]下添加：

log = /data/mysql/var/s4.log

重启mysqld服务

例如类似的文件内容如下；

未分类

MySQL变量的用法

在编写存储过程中，有时需要使用变量，保存数据处理过程中的值
MySQL中，变量可以在子程序中，声明并使用，作用范围在BEGIN……END程序中

定义变量

在存储过程中，使用DECLARE语句，定义变量
语法格式

DECLARE var_name[,varname]……date_type[DEFAULT value]

参数说明

  • Var_name，为局部变量的名称
  • DEFAULT value，子句给变量提供一个默认值，该值除了可以声明为一个常数之外，还可以被指定为一个表达式
  • 如果，没有DEFAULT子句，变量的初始值为NULL

定义一个名称为myvariable的变量，类型为INT类型，默认值为100

DECLARE myvariable INT DEFAULT 100;

使用SET为变量赋值

定义变量之后，为变量赋值，改变变量的默认值
mysql中，使用SET语句为变量赋值

SET var_name=expr [,var_name=expr]……；

MySQL中
允许不同的变量类型，局域声明变量及全局变量，可以混合起来
允许把局部变量，和一些只对系统变量有意义的选项合并起来

SET a=x,b=y,……

声明三个变量，分别为var1、var2、var3，数据类型为INT，使用SET为变量赋值

DECLARE var1,var2,var3 int;
SET var1=10,var2=20;
SET var3=var1+var2;

使用SELECT……INTO为变量赋值

此外，除了使用SET为变量赋值
MySQL中，可以使用SELECT……INTO为一个或多个变量赋值
该语句可以把选定的列，直接存储到对应位置的变量
语法格式

SELECT col_name[……] INTO var_name [……] table_expr;

参数说明

  • Col_name，表示字段名称
  • Var_name，表示定义的变量名称
  • Table_expr，表示查询条件表达式，包括表名称和WHERE子句

声明变量s_grade和s_gender，通过SELECT……INTO语句，查询指定记录，并为变量赋值

未分类

上述语句，将student表中，name为rose的同学的成绩和性别，分别存入到了变量s_grade和s_gender中。