2018-08-29 09:51:11    2019-11-14 14:31:25   

metricbeat filebeat elasticsearch logstash kibana
### 〇、介绍 #### **`ELK是Elasticsearch、Logstash、Kibana的简称,亦可称为Elastic Stack,这三者是核心套件。但并非全部,其中还包括Filebeat/Heartbeat/Metricbeat/Auditbeat/Packetbeat/Winlogbeat数据收集代理。`** **Elasticsearch** `实时全文搜索和分析引擎,提供搜集、分析、存储数据三大功能;是一套开放REST和JAVA API等结构提供高效搜索功能,可扩展的分布式系统。它构建于Apache Lucene搜索引擎库之上` **Logstash** `一个用来搜集、分析、过滤日志的工具。它支持几乎任何类型的日志,包括系统日志、错误日志和自定义应用程序日志。它可以从许多来源接收日志,这些来源包括 syslog、消息传递(例如 RabbitMQ)和JMX,它能够以多种方式输出数据,包括电子邮件、websockets和Elasticsearch` **Kibana** `一个基于Web的图形界面,用于搜索、分析和可视化存储在 Elasticsearch指标中的日志数据。它利用Elasticsearch的REST接口来检索数据,不仅允许用户创建他们自己的数据的定制仪表板视图,还允许他们以特殊的方式查询和过滤数据` ![](https://image.ynotes.cn/elk_structure.png) &emsp; &emsp; ### 一、环境准备 系统: `CentOS7` 软件: - `elasticsearch`:`7.3.1` - `logstash`:`7.3.1` - `kibana`:`7.3.1` - `filebeat`:`7.3.1` - `metricbeat`:`7.3.1` - `redis(消息队列)`:`5.0.5` 服务器: `node1(172.16.0.101)`:`elasticsearch` `filebeat` `metricbeat` `node2(172.16.0.102)`:`elasticsearch` `filebeat` `metricbeat` `kibana` `logstash` `redis` `node3(172.16.0.103)`:`elasticsearch` `filebeat` `metricbeat` &emsp; &emsp; ### 二、ElasticSearch **`说明:搭建一个包含三个节点的ElasticSearch集群,三个节点为对等节点(主节点/数据节点),集群初始化的主节点为node1。`** #### 导入签名key `所有节点` ```bash rpm --import https://artifacts.elastic.co/GPG-KEY-elasticsearch ``` &emsp; #### 配置Elastic Stack仓库 `所有节点` ```bash cat << EOF >/etc/yum.repos.d/elasticsearch.repo [elasticsearch-7.x] name=Elasticsearch repository for 7.x packages baseurl=https://artifacts.elastic.co/packages/7.x/yum gpgcheck=1 gpgkey=https://artifacts.elastic.co/GPG-KEY-elasticsearch enabled=1 autorefresh=1 type=rpm-md EOF ``` &emsp; #### JAVA `注意:ElasticSearch主目录捆绑安装JDK(/usr/share/elasticsearch/jdk),无需安装` #### 安装elasticsearch `所有节点` ```bash yum install elasticsearch -y ``` &emsp; #### 配置elasticsearch `配置文件路径位于$ES_PATH_CONF指定的值,默认为/etc/elasticsearch` ```bash grep ES_PATH_CONF /etc/sysconfig/elasticsearch #查看配置文件的路径 ``` ``` ES_PATH_CONF=/etc/elasticsearch ``` &emsp; #### 配置elasticsearch.yml `node1` ```bash cat <<EOF >/etc/elasticsearch/elasticsearch.yml cluster.name: es-ynotes.cn-cluster node.name: node1 path.data: /var/lib/elasticsearch path.logs: /var/log/elasticsearch http.cors.enabled: true http.cors.allow-origin: "*" node.master: true node.data: true transport.tcp.port: 9300 transport.tcp.compress: true network.host: 172.16.0.101 http.port: 9200 discovery.seed_hosts: ["node1", "node2", "node3"] #集群节点 cluster.initial_master_nodes: ["node1"] #初始化主节点 EOF ``` `node2/node3` `注意:修改node.name和network.host即可` &emsp; #### 启动elasticsearch ```bash systemctl start elasticsearch ``` &emsp; #### 查看集群状态 ```bash curl http://172.16.0.101:9200/_cluster/health?pretty ``` ``` { "cluster_name" : "es-ynotes.cn-cluster", "status" : "red", "timed_out" : false, "number_of_nodes" : 3, "number_of_data_nodes" : 3, "active_primary_shards" : 12, "active_shards" : 12, "relocating_shards" : 0, "initializing_shards" : 8, "unassigned_shards" : 24, "delayed_unassigned_shards" : 0, "number_of_pending_tasks" : 7, "number_of_in_flight_fetch" : 0, "task_max_waiting_in_queue_millis" : 1838, "active_shards_percent_as_number" : 27.27272727272727 } ``` `集群名为es-ynotes.cn-cluster,数据节点数为3个` &emsp; &emsp; ### 二、Kibana #### 安装Kibana `node2节点` ```bash yum install kibana -y ``` &emsp; #### 配置kibana ```bash vim /etc/kibana/kibana.yml #修改下面对应的值即可 ``` ```yaml server.host: "0.0.0.0" #监听的地址 elasticsearch.hosts: ["http://node1:9200","http://node2:9200","http://node3:9200"] #配置es集群节点 i18n.locale: "zh-CN" #设置界面为中文 ``` &emsp; &emsp; ### 三、Redis(docker运行) #### 安装并启动docker `node2` ```bash yum install docker -y systemctl start docker ``` #### docker运行Redis `node2` ```bash docker run -d --name logredis -p 6379:6379 redis --requirepass "ynotes.cn" ``` &emsp; &emsp; ### 四、Logstash #### 安装Logstash `node2` ```bash yum install logstash -y ``` &emsp; #### logstash配置 **方式一:** `数据流`:`日志` -> `logstash` -> `elasticsearch` `说明:该方式不需要数据收集代理(Filebeat/Metircbeat),logstash直接收集数据写入elasticsearch` ```bash cat <<EOF >/etc/logstash/logstash.conf input{ file { path => ["/var/log/messages"] type => "system" start_position => beginning } } filter { #无过滤规则 } output{ elasticsearch { hosts => ["node1:9200", "node2:9200", "node3:9200"] index => "%{[@metadata][beat]}-%{[@metadata][version]}-%{+YYYY.MM.dd}" #user => "elastic" #password => "changeme" } } EOF ``` **方式二:** `数据流`:`日志` -> `Filebeat` -> `logstash` -> `elasticsearch` `说明:该方式使用数据收集代理(如Filebeat/Metircbeat)收集数据写入logstash,再由logstash写入elasticsearch` ```bash cat <<EOF >/etc/logstash/logstash.conf input { beats { port => 5044 } } filter { #无过滤规则 } output { elasticsearch { hosts => ["node1:9200", "node2:9200", "node3:9200"] index => "%{[@metadata][beat]}-%{[@metadata][version]}-%{+YYYY.MM.dd}" #user => "elastic" #password => "changeme" } } EOF ``` **方式三:** `数据流`:`日志` -> `Filebeat` -> `redis` -> `logstash` -> `elasticsearch` `说明:该方式需要数据收集代理(如Filebeat)收集数据写入到redis,再由logstash从redis获取数据写入elasticsearch` ```bash cat <<EOF >/etc/logstash/logstash.conf input { redis { data_type => "list" key => "filebeat_list" host => "node2" port => 6379 password => "ynotes.cn" db => 0 threads => 4 } } filter { #无过滤规则 } output { elasticsearch { hosts => ["node1:9200", "node2:9200", "node3:9200"] index => "%{[@metadata][beat]}-%{[@metadata][version]}-%{+YYYY.MM.dd}" } } EOF ``` &emsp; #### 启动Logstash ```bash systemctl start logstash ``` &emsp; #### 过滤模块Grok `grok是一种采用组合多个预定义的正则表达式,用来匹配分割文本并映射到关键字的工具。通常用来对日志数据进行预处理。logstash的filter模块中grok插件是其实现之一。` `配置与调试` 例如日志如下: ```bash localhost GET /index.html 1024 0.016 ``` 可依次判断类型为:IPORHOST、WORD、URIPATHPARAM、INT、NUMBER, 形成的grok语句如下为 ```bash %{IPORHOST:client} %{WORD:method} %{URIPATHPARAM:request} %{INT:size} %{NUMBER:duration} ``` `预定义匹配样例` grok默认内置120个预定义匹配字段,已附在文末,logstash内置的可参考网址: https://github.com/logstash-plugins/logstash-patterns-core/tree/master/patterns `调试工具` - kibana内置Grok调试器 - 调试网址:https://grokdebug.herokuapp.com `官方文档`:https://www.elastic.co/guide/en/logstash/current/plugins-filters-grok.html &emsp; &emsp; ### 四、Filebeat #### 安装filebeat `所有节点` ```bash yum install filebeat -y ``` &emsp; #### 收集日志(`以系统日志为例`) **启用模块** `所有节点` ```bash filebeat modules enable system #收集其他日志启用相应模块即可 ``` **配置filebeat** **方式一:** `数据流`:`日志` -> `Filebeat` -> `elasticsearch` ```bash vim /etc/filebeat/filebeat.yml ``` ```bash filebeat.config.modules: path: ${path.config}/modules.d/*.yml reload.enabled: true setup.template.settings: index.number_of_shards: 1 setup.kibana: host: "node2:5601" output.elasticsearch: #配置输出流到elasticsearch hosts: ["node1:9200", "node2:9200", "node3:9200"] processors: - add_host_metadata: ~ - add_cloud_metadata: ~ ``` **方式二:** `数据流`:`日志` -> `Filebeat` -> `logstash` -> `elasticsearch` ```bash vim /etc/filebeat/filebeat.yml ``` ```bash filebeat.config.modules: path: ${path.config}/modules.d/*.yml reload.enabled: true setup.template.settings: index.number_of_shards: 1 setup.kibana: host: "node2:5601" output.logstash: #配置输出流到logstash hosts: ["node2:5044"] processors: - add_host_metadata: ~ - add_cloud_metadata: ~ ``` **方式三:** `数据流`:`日志` -> `Filebeat` -> `redis` -> `logstash` -> `elasticsearch` ```bash vim /etc/filebeat/filebeat.yml ``` ```bash filebeat.config.modules: enabled: true path: ${path.config}/modules.d/*.yml reload.enabled: true reload.period: 10s setup.kibana: host: "node2:5601" output.redis: #配置输出流到redis hosts: ["node2:6379"] password: "ynotes.cn" key: "filebeat_list" db: 0 timeout: 5 processors: - add_host_metadata: ~ - add_cloud_metadata: ~ #优化参数 max_procs: 1 queue.mem: events: 1024 flush.min_events: 512 flush.timeout: 5s max_message_bytes: 100000 ``` &emsp; #### 手动加载索引模板(`注意:filebeat输出没有直连elasticsearch必须执行,方式二和方式三必须执行`) ```bash filebeat export template >filebeat.template.json curl -XPUT -H 'Content-Type: application/json' http://node1:9200/_template/filebeat-7.3.1 -d@filebeat.template.json ``` &emsp; #### 启动Filebeat ```bash filebeat setup #命令加载Kibana安装数据图表和仪表板。如果已设置,请省略此命令,filebeat不能直连kibana的情况,请使用一个可以直连kibana的filebeat去执行该操作 systemctl start filebeat ``` &emsp; ### 五、Metricbeat #### 安装metricbeat `所有节点` ```bash yum install metricbeat -y ``` &emsp; #### 添加指标(`以系统指标为例`) **启用模块** `所有节点` ```bash metricbeat modules enable system #添加其他指标启用相应模块即可 ``` **配置metricbeat** **方式一:** `数据流`:`日志` -> `metricbeat` -> `elasticsearch` ```bash vim /etc/metricbeat/metricbeat.yml ``` ```bash metricbeat.config.modules: path: ${path.config}/modules.d/*.yml reload.enabled: false setup.template.settings: index.number_of_shards: 1 index.codec: best_compression setup.kibana: host: "node2:5601" output.elasticsearch: hosts: ["node1:9200", "node2:9200", "node3:9200"] processors: - add_host_metadata: ~ - add_cloud_metadata: ~ ``` **方式二:** `数据流`:`日志` -> `metricbeat` -> `logstash` -> `elasticsearch` ```bash vim /etc/metricbeat/metricbeat.yml ``` ```bash metricbeat.config.modules: path: ${path.config}/modules.d/*.yml reload.enabled: true setup.template.settings: index.number_of_shards: 1 setup.kibana: host: "node2:5601" output.logstash: #配置输出流到logstash hosts: ["node2:5044"] processors: - add_host_metadata: ~ - add_cloud_metadata: ~ ``` **方式三:** `数据流`:`日志` -> `metricbeat` -> `redis` -> `logstash` -> `elasticsearch` ```bash vim /etc/metricbeat/metricbeat.yml ``` ```bash metricbeat.config.modules: path: ${path.config}/modules.d/*.yml reload.enabled: true setup.template.settings: index.number_of_shards: 1 index.codec: best_compression output.redis: #配置输出流到redis enabled: true hosts: ["node2:6379"] password: "ynotes.cn" key: "metricbeat_list" db: 0 timeout: 5 processors: - add_host_metadata: ~ - add_cloud_metadata: ~ ``` &emsp; #### 手动加载索引模板(`注意:metricbeat输出没有直连elasticsearch必须执行,方式二和方式三必须执行`) ```bash metricbeat export template >metricbeat.template.json curl -XPUT -H 'Content-Type: application/json' http://node1:9200/_template/metricbeat-7.3.1 -d@metricbeat.template.json ``` &emsp; #### 启动metricbeat ```bash metricbeat setup #命令加载Kibana安装数据图表和仪表板。如果已设置,请省略此命令,metricbeat不能直连kibana的情况,请使用一个可以直连kibana的metricbeat去执行该操作 systemctl start metricbeat ``` &emsp; ### 六、生产环境实战 `说明:Elasticsearch放在公司内网环境,生产服务器在阿里云,为了让日志写入到Elasticsearch,采用的是前面filebeat介绍的方式三:` `日志` -> `Filebeat` -> `redis` -> `logstash` -> `elasticsearch` `其中filebeat和redis在生产服务器,logstash和elasticsearch在内网,生产服务器暴露redis(需认证)的端口,logstash去拉取数据然后写入elasticsearch` `以配置nginx/tomcat日志为例` #### filebeat配置 ```bash cat /etc/filebeat/filebeat.yml ``` ```yml filebeat.config.inputs: enabled: true path: ${path.config}/configs/*.yml #指定input输入配置文件路径 output.redis: hosts: ["172.18.176.146:6379"] #阿里云内网的一台redis password: "password" #redis的认证密码 key: "default_list" db: 0 timeout: 5 processors: - add_host_metadata: ~ - add_cloud_metadata: ~ max_procs: 1 queue.mem: events: 1024 flush.min_events: 512 flush.timeout: 5s max_message_bytes: 100000 ``` 配置nginx日志 ```bash vim /etc/filebeat/configs/nginx.yml ``` ```ini - type: log enabled: true paths: - /var/log/nginx/domain.com.access.log name: "nginx-prod-project_name-api-access_log" tags: ['nginx','prod','project_name','api','access_log'] tail_files: true exclude_lines: ['^-'] #排除-开头的日志(这里是因为阿里云配置SLB,所以很多内网访问的日志开头为-) - type: log enabled: true paths: - /var/log/nginx/error.log name: "nginx-prod-error_log" tags: ['nginx','prod','error_log'] tail_files: true ``` 配置tomcat日志 ```bash vim /etc/filebeat/configs/tomcat.yml ``` ```ini - type: log enabled: true paths: - /data/app/tomcat/logs/catalina.out.* name: "tomcat-prod-project_name-api-catalina_log" tags: ['tomcat','prod','project_name','api','catalina_log'] multiline: pattern: '^([0-9]{4}-[0-9]{2}-[0-9]{2}|[0-9]{2}-[a-zA-Z]{3}-[0-9]{4})' #多行匹配特定格式的日期为新行 negate: true match: after tail_files: true - type: log enabled: true paths: - /data/app/tomcat/logs/localhost.20* name: "tomcat-prod-project_name-api-localhost_log" tags: ['tomcat','prod','project_name','api','localhost_log'] multiline: pattern: '^[[:space:]]' #多行模式匹配空格开头非新行 negate: false match: after tail_files: true ``` &emsp; #### logstash配置 ```bash vim /data/elk/logstash/config/logstash.yml ``` ```yml input { redis { id => 'outer' type => 'outer' data_type => "list" key => "default_list" host => "xx.xx.xx.xx" #阿里云暴露的reids服务器 port => 6379 password => "password" db => 0 threads => 4 } } filter { if "nginx" in [tags] and "access_log" in [tags] { grok { patterns_dir => "/data/elk/logstash/patterns" #GROK模式匹配目录 match => ["message","%{NGINXACCESS}"] #NGINXACCESS为自定义的GROK匹配格式 } date { match => [ "timestamp" , "dd/MMM/YYYY:HH:\mm:ss Z" ] #\mm应该为mm,因为网页会转义成图片 } geoip { source => "client_ip" } } if "nginx" in [tags] and "error_log" in [tags] { grok { patterns_dir => "/data/elk/logstash/patterns" match => ["message", "%{NGINXERRORTIME:logdate}"] #NGINXERRORTIME为自定义的GROK匹配格式 } date { match => [ "logdate" , "yyyy/MM/dd HH:\mm:ss" ] #\mm应该为mm,因为网页会转义成图片 target => "@timestamp" } } if "tomcat" in [tags] and "catalina_log" in [tags] { grok { patterns_dir => "/data/elk/logstash/patterns" match => ["message", "%{TIMESTAMP_ISO8601:logdate} %{WORD:LOGLEVEL}","message","%{TOMCATLOCALHOSTTIME:logdate}"] #TOMCATLOCALHOSTTIME为自定义的GROK匹配格式 } date { match => [ "logdate" , "yyyy-MM-dd HH:\mm:ss.SSS","dd-MMM-yyyy HH:\mm:ss.SSS"] #\mm应该为mm,因为网页会转义成图片 target => "@timestamp" } } if "tomcat" in [tags] and "localhost_log" in [tags] { grok { patterns_dir => "/data/elk/logstash/patterns" match => ["message", "%{TOMCATLOCALHOSTTIME:logdate}"] #TOMCATLOCALHOSTTIME为自定义的匹配格式 } date { match => [ "logdate" , "dd-MMM-yyyy HH:\mm:ss.SSS" ] #\mm应该为mm,因为网页会转义成图片 target => "@timestamp" } } } #输出配置 output { if [type] == "outer" { if "prod" in [tags] and "nginx" in [tags] and "project_name" in [tags] and "api" in [tags] and "access_log" in [tags] { elasticsearch { hosts => ["192.168.50.251:9200"] #内网Elasticsearch服务器 index => "prod_nginx_project_name_api_access_log" #索引名 } } if "prod" in [tags] and "nginx" in [tags] and "error_log" in [tags] { elasticsearch { hosts => ["192.168.50.251:9200"] index => "prod_nginx_error_log" } } if "prod" in [tags] and "tomcat" in [tags] and "project_name" in [tags] and "api" in [tags] and "catalina_log" in [tags] { elasticsearch { hosts => ["192.168.50.251:9200"] index => "prod_tomcat_project_name_api_catalina_log" } } if "prod" in [tags] and "tomcat" in [tags] and "project_name" in [tags] and "api" in [tags] and "localhost_log" in [tags] { elasticsearch { hosts => ["192.168.50.251:9200"] index => "prod_tomcat_project_name_api_localhost_log" } } } } ``` 自定义的GROK匹配格式(`注意:对nginx/tomcat的日志格式做相应修改`) ```bash cat /data/elk/logstash/patterns/nginx ``` ```ini NGINXACCESS %{IPORHOST:client_ip|-} %{IPORHOST:remote_addr} - (%{USERNAME:remote_user}|-) \[%{HTTPDATE:timestamp}\] \"%{HOSTNAME:http_host}\" \"%{WORD:verb} %{URIPATHPARAM:request} HTTP/%{NUMBER:httpversion}\" %{NUMBER:http_status} (?:%{NUMBER:body_bytes_sent}|-) (?:\"(?:%{URI:referrer}|-)\"|%{QS:referrer}) \"%{WORD:http_x_forwarded_proto}\" %{QS:agent} (?:%{HOSTPORT:upstream_addr}|-) (%{NUMBER:upstream_response_time}|-) (%{NUMBER:request_time}|-) NGINXERRORTIME %{YEAR}/%{MONTHNUM}/%{MONTHDAY} %{HOUR}:%{MINUTE}:%{SECOND} ``` ```bash cat /data/elk/logstash/patterns/tomcat ``` ```ini TOMCATLOCALHOSTTIME %{MONTHDAY}-%{MONTH}-%{YEAR} %{HOUR}:%{MINUTE}:%{SECOND} ``` &emsp; #### kibana配置 `1.创建对应的索引模式` `2.创建对应的可视化图和仪表板即可`
阅读 13 评论 0 收藏 0
阅读 13
评论 0
收藏 0

   2018-08-13 23:05:48    2018-08-13 23:05:48   

tomcat docker 容器 docker-compose 容器编排
#### **项目目录结构** ```bash competitionShare |-- docker-compose.yml #docker-compose编排文件 |-- fastdfs #fastdfs文件服务器目录 | |-- build #编译目录 | | `-- Dockerfile #编译文件 | |-- data #数据存放目录 | | |-- storage #文件数据存储目录 | | | |-- data | | | `-- logs | | `-- tracker #tracker日志和元数据目录 | | |-- data | | `-- logs | `-- nginx | `-- logs |-- mysql | |-- conf | | `-- mysqld.cnf #mysql配置文件 | |-- data #mysql数据存放目录 | |-- db_init_sql | | `-- competitionShare.sql #项目的表结构和初始化数据sql | `-- log |-- nginx | |-- conf | | |-- mysite.template #nginx模板文件 | | `-- nginx.conf #nginx配置文件 | |-- html | | `-- competitionShare_web #项目静态站点目录 | | |-- index.html | | `-- static | |-- log | `-- ssl #ssl证书目录 | |-- demo.xxxxx.org.cn | | |-- fullchain.pem | | `-- privkey.pem | `-- fastdfs.xxxxx.org.cn | |-- fullchain.pem | `-- privkey.pem `-- tomcat #tomcat目录 |-- conf | `-- server.xml #tomcat的server.xml文件 |-- log `-- webapps |-- competitionShare #项目API接口 `-- competitionShareBackstage #项目后台 ``` #### **创建fastdfs容器使用的目录** ```bash $ mkdir fastdfs/{build,data,nginx} -p ``` build:存放fastdfs构建目录 data:存放fastdfs数据的目录 nginx:存放nginx日志 #### **创建fastdfs/build/Dockerfile** ```bash FROM alpine:3.6 MAINTAINER ynotes <admin@ynotes.cn> #编译参数 ARG HOME=/root ARG FASTDFS_VERSION=5.11 ARG LIBFASTCOMMON_VERSION=1.0.38 ARG FASTDFS_NGINX_MODULE_VERSION=1.20 ARG NGINX_VERSION=1.12.1 ARG FDFS_NGX_PORT #添加FDFS_NGX_PORT参数 ARG TRACKER_PORT #环境变量 ENV FDFS_NGX_PORT "$FDFS_NGX_PORT" #读取docker-compose的变量FDFS_NGX_PORT ENV TRACKER_PORT "$TRACKER_PORT" #读取docker-compose的变量TRACKER_PORT #下载包 RUN cd ${HOME} \ && sed -i 's#http://[^/]*/\(.*\)$#http://mirrors.aliyun.com/\1#g' /etc/apk/repositories \ && apk update \ && apk add --no-cache --virtual .build-deps bash gcc libc-dev make openssl-dev pcre-dev zlib-dev linux-headers curl gnupg libxslt-dev gd-dev geoip-dev \ && curl -fLS https://github.com/happyfish100/fastdfs/archive/V${FASTDFS_VERSION}.tar.gz -o V${FASTDFS_VERSION}.tar.gz \ && curl -fLS https://github.com/happyfish100/libfastcommon/archive/V${LIBFASTCOMMON_VERSION}.tar.gz -o V${LIBFASTCOMMON_VERSION}.tar.gz \ && curl -fLS https://github.com/happyfish100/fastdfs-nginx-module/archive/V${FASTDFS_NGINX_MODULE_VERSION}.tar.gz -o V${FASTDFS_NGINX_MODULE_VERSION}.tar.gz \ && curl -fSL http://nginx.org/download/nginx-${NGINX_VERSION}.tar.gz -o nginx-${NGINX_VERSION}.tar.gz \ && tar xf V${FASTDFS_VERSION}.tar.gz \ && tar xf V${LIBFASTCOMMON_VERSION}.tar.gz \ && tar xf V${FASTDFS_NGINX_MODULE_VERSION}.tar.gz \ && tar zxf nginx-${NGINX_VERSION}.tar.gz #安装包 RUN cd ${HOME}/libfastcommon-${LIBFASTCOMMON_VERSION}/ \ && ./make.sh \ && ./make.sh install \ && cd ${HOME}/fastdfs-${FASTDFS_VERSION}/ \ && ./make.sh \ && ./make.sh install \ && sed "s@/home/yuqing/fastdfs@/data/fastdfs/tracker@g" /etc/fdfs/tracker.conf.sample > /etc/fdfs/tracker.conf \ && sed "s@/home/yuqing/fastdfs@/data/fastdfs/storage@g" /etc/fdfs/storage.conf.sample > /etc/fdfs/storage.conf \ && sed "s@/home/yuqing/fastdfs@/data/fastdfs/storage@g" /etc/fdfs/client.conf.sample > /etc/fdfs/client.conf \ && sed -i 's#CORE_INCS=.*#CORE_INCS="$CORE_INCS /usr/include/fastdfs /usr/include/fastcommon/"#g' ${HOME}/fastdfs-nginx-module-${FASTDFS_NGINX_MODULE_VERSION}/src/config \ && sed -i 's#ngx_module_incs=.*#ngx_module_incs="/usr/include/fastdfs /usr/include/fastcommon/"#g' ${HOME}/fastdfs-nginx-module-${FASTDFS_NGINX_MODULE_VERSION}/src/config \ && chmod u+x ${HOME}/fastdfs-nginx-module-${FASTDFS_NGINX_MODULE_VERSION}/src/config \ && cd ${HOME}/nginx-${NGINX_VERSION} \ && ./configure --add-module=${HOME}/fastdfs-nginx-module-${FASTDFS_NGINX_MODULE_VERSION}/src \ && make && make install #配置包 RUN cp ${HOME}/fastdfs-nginx-module-${FASTDFS_NGINX_MODULE_VERSION}/src/mod_fastdfs.conf /etc/fdfs/ \ && sed -i "s#^store_path0.*#store_path0 = /data/fastdfs/storage#g" /etc/fdfs/mod_fastdfs.conf \ && sed -i "s#^url_have_group_name.*#url_have_group_name = true#g" /etc/fdfs/mod_fastdfs.conf \ && cd ${HOME}/fastdfs-${FASTDFS_VERSION}/conf/ \ && cp http.conf mime.types /etc/fdfs/ \ && echo -e "worker_processes 2;\nevents { \nworker_connections 10240; \n}\nhttp { \ninclude mime.types;\ndefault_type application/octet-stream;\nsendfile on;\nkeepalive_timeout 65;\nserver {\nlisten $FDFS_NGX_PORT;\nserver_name localhost;\nlocation ~/group([0-9])/M00 {\nngx_fastdfs_module;\n}\n}\n}">/usr/local/nginx/conf/nginx.conf #清理包 RUN rm -rf ${HOME}/* \ && apk del .build-deps gcc libc-dev make openssl-dev linux-headers curl gnupg libxslt-dev gd-dev geoip-dev \ && apk add bash pcre-dev zlib-dev #安装脚本 RUN echo -e "mkdir -p /data/fastdfs/storage/data\nmkdir -p /data/fastdfs/tracker\nln -s /data/fastdfs/storage/data /data/fastdfs/storage/data/M00\nsed -i "s/^tracker_server=.*$/tracker_server=\$HOST_IP:$TRACKER_PORT/g" /etc/fdfs/storage.conf\nsed -i "s/^tracker_server=.*$/tracker_server=\$HOST_IP:$TRACKER_PORT/g" /etc/fdfs/mod_fastdfs.conf\n/etc/init.d/fdfs_trackerd start \n/etc/init.d/fdfs_storaged start\n/usr/local/nginx/sbin/nginx\ntail -f /usr/local/nginx/logs/access.log" >/start.sh \ && chmod +x /start.sh ENTRYPOINT ["/bin/bash","/start.sh"] ``` #### **创建mysql容器使用的目录** ```bash $ mkdir mysql/{conf,data,db_init_sql,log} -p $ chmod 777 mysql/log ``` conf:存放mysql配置文件 data:存放mysql数据的目录 log:存放mysql日志,修改权限为777   #### **编辑mysql配置文件mysql/conf/mysqld.cnf** ```bash [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock symbolic-links=0 log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid default-time-zone = '+08:00' character-set-server=utf8 character-set-server = utf8mb4 collation-server = utf8mb4_unicode_ci character-set-client-handshake = FALSE innodb_buffer_pool_size = 128M sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES [client] default-character-set = utf8mb4 [mysql] default-character-set = utf8mb4 ``` #### **创建nginx容器使用的目录** ```bash $ mkdir nginx/{conf,html,log,ssl} $ mkdir nginx/ssl/{demo.xxxxx.org.cn,fastdfs.xxxxx.org.cn} $ chmod 777 nginx/log ``` conf:存放nginx的配置文件 html: 静态站点存放目录 log:存放日志目录 ssl: ssl证书存放目录 #### **编辑nginx/conf/nginx.conf** ```nginx user nginx; worker_processes 2; error_log /var/log/nginx/error.log warn; pid /var/run/nginx.pid; events { use epoll; worker_connections 10240; } http { include /etc/nginx/mime.types; default_type application/octet-stream; log_format main '$remote_addr - $remote_user [$time_local] "$request" ' '$status $body_bytes_sent "$http_referer" ' '"$http_user_agent" "$http_x_forwarded_for"'; access_log /var/log/nginx/access.log main; sendfile on; #tcp_nopush on; keepalive_timeout 65; #gzip on; include /etc/nginx/conf.d/demo.xxxxx.org.cn.conf; } ``` #### **编辑nginx/conf/mysite.template** ```nginx upstream my_tomcat{ server $TOMCAT:8080; } upstream my_fdfs{ server $FASTDFS:8888; } server { listen $NGINX_PORT; server_name $NGINX_HOST; charset utf-8; rewrite ^(.*)$ https://${server_name}$1 permanent; } server { listen $NGINX_SSL_PORT ssl http2; server_name $NGINX_FASTDFS_HOST; add_header X-Frame-Options SAMEORIGIN; access_log /var/log/nginx/fastdfs.xxxxx.org.cn.access.log main; location ~ .*.(svn|Git|cvs) { deny all; } ssl_certificate "/etc/nginx/ssl/fastdfs.xxxxx.org.cn/fullchain.pem"; ssl_certificate_key "/etc/nginx/ssl/fastdfs.xxxxx.org.cn/privkey.pem"; ssl_session_cache shared:SSL:1m; ssl_session_timeout 10m; ssl_protocols TLSv1 TLSv1.1 TLSv1.2; ssl_ciphers ECDHE-RSA-AES128-GCM-SHA256:HIGH:!aNULL:!MD5:!RC4:!DHE; ssl_prefer_server_ciphers on; location ~ /group1/M00 { add_header Strict-Transport-Security max-age=86400; proxy_next_upstream http_502 http_504 error timeout invalid_header; proxy_pass http://my_fdfs; } } server { listen $NGINX_SSL_PORT ssl http2 default_server; server_name $NGINX_HOST; add_header X-Frame-Options SAMEORIGIN; access_log /var/log/nginx/demo.xxxxx.org.cn.access.log main; location ~ .*.(svn|Git|cvs) { deny all; } location / { add_header Strict-Transport-Security max-age=86400; root /var/www/html/competitionShare_web; index index.html index.htm; try_files $uri $uri/ /index.html =404; } ssl_certificate "/etc/nginx/ssl/demo.xxxxx.org.cn/fullchain.pem"; ssl_certificate_key "/etc/nginx/ssl/demo.xxxxx.org.cn/privkey.pem"; ssl_session_cache shared:SSL:1m; ssl_session_timeout 10m; ssl_protocols TLSv1 TLSv1.1 TLSv1.2; ssl_ciphers ECDHE-RSA-AES128-GCM-SHA256:HIGH:!aNULL:!MD5:!RC4:!DHE; ssl_prefer_server_ciphers on; # max upload size client_max_body_size 75M; # adjust to taste # Django media # Finally, send all non-media requests to the Django server. error_page 404 /404.html; location = /40x.html { } error_page 500 502 503 504 /50x.html; location = /50x.html { } location /competitionShare { add_header Strict-Transport-Security max-age=86400; proxy_set_header Host $host; proxy_set_header Cookie $http_cookie; proxy_set_header X-Real-IP $remote_addr; proxy_set_header X-Forwarded-For $proxy_add_x_forwarded_for; proxy_set_header X-Forwarded-Proto https; proxy_redirect off; proxy_pass http://my_tomcat; } location ^~ /competitionShareBackstage { add_header Strict-Transport-Security max-age=86400; proxy_set_header Host $host:$server_port; proxy_set_header Cookie $http_cookie; proxy_set_header X-Real-IP $remote_addr; proxy_set_header X-Forwarded-For $proxy_add_x_forwarded_for; proxy_set_header X-Forwarded-Proto https; proxy_redirect off; proxy_pass http://my_tomcat; } } ``` #### **拷贝SSL证书到对应的nginx/ssl/{demo.xxxxx.org.cn,fastdfs.xxxxx.org.cn}目录** ```bash $ scp fullchain.pem root@docker-host:/root/docker_compose_demo/competitionShare/nginx/ssl/demo.xxxxx.org.cn $ scp privkey.pem root@docker-host:/root/docker_compose_demo/competitionShare/nginx/ssl/demo.xxxxx.org.cn $ scp fullchain.pem root@docker-host:/root/docker_compose_demo/competitionShare/nginx/ssl/fastdfs.xxxxx.org.cn $ scp privkey.pem root@docker-host:/root/docker_compose_demo/competitionShare/nginx/ssl/fastdfs.xxxxx.org.cn ``` #### **创建tomcat容器使用的目录** ```bash $ mkdir tomcat/{conf,log,webapps} ``` conf:tomcat配置存放目录 log:存放日志目录 webapps: 项目存放目录 #### **编辑tomcat/conf/server.xml** ```xml <?xml version='1.0' encoding='utf-8'?> <Server port="8005" shutdown="SHUTDOWN"> <Listener className="org.apache.catalina.startup.VersionLoggerListener" /> <Listener className="org.apache.catalina.core.AprLifecycleListener" SSLEngine="on" /> <Listener className="org.apache.catalina.core.JreMemoryLeakPreventionListener" /> <Listener className="org.apache.catalina.mbeans.GlobalResourcesLifecycleListener" /> <Listener className="org.apache.catalina.core.ThreadLocalLeakPreventionListener" /> <GlobalNamingResources> <Resource name="UserDatabase" auth="Container" type="org.apache.catalina.UserDatabase" description="User database that can be updated and saved" factory="org.apache.catalina.users.MemoryUserDatabaseFactory" pathname="conf/tomcat-users.xml" /> </GlobalNamingResources> <Service name="Catalina"> <Connector port="8080" protocol="HTTP/1.1" connectionTimeout="20000" redirectPort="8443" /> <Connector port="8009" protocol="AJP/1.3" redirectPort="8443" /> <Engine name="Catalina" defaultHost="localhost"> <Realm className="org.apache.catalina.realm.LockOutRealm"> <Realm className="org.apache.catalina.realm.UserDatabaseRealm" resourceName="UserDatabase"/> </Realm> <Valve className="org.apache.catalina.valves.RemoteIpValve" remoteIpHeader="X-Forwarded-For" protocolHeader="X-Forwarded-Proto" protocolHeaderHttpsValue="https"/> <Host name="localhost" appBase="webapps" unpackWARs="true" autoDeploy="true"> <Valve className="org.apache.catalina.valves.AccessLogValve" directory="logs" prefix="localhost_access_log" suffix=".txt" pattern="%h %l %u %t &quot;%r&quot; %s %b" /> </Host> </Engine> </Service> </Server> ``` #### **拷贝项目到tomcat/webapps目录** ```bash $ scp competitionShare root@docker-host:/root/docker_compose_demo/competitionShare/tomcat/webapps $ scp competitionShareBackstage root@docker-host:/root/docker_compose_demo/competitionShare/tomcat/webapps ``` #### **替换tomcat项目中mysql和fastdfs配置** 数据库配置 ```bash env=${PROJECT_ENV} demo.jdbc_url=${DEMO_JDBC_URL} demo.jdbc_username=${DEMO_JDBC_USER} demo.jdbc_password=${DEMO_JDBC_PASS} ``` fastdfs配置 ```bash tracker_server = fastdfs:22122 ``` #### **编辑docker-compose.yml** ```xml version: '3' services: db: image: mysql:5.7 restart: always container_name: cs_web-db environment: MYSQL_ROOT_PASSWORD: abc123456 MYSQL_DATABASE: competitionShare MYSQL_USER: demo MYSQL_PASSWORD: abc123456 volumes: - ./mysql/conf/mysqld.cnf:/etc/mysql/mysql.conf.d/mysqld.cnf - ./mysql/db_init_sql:/docker-entrypoint-initdb.d - ./mysql/data:/var/lib/mysql - ./mysql/log:/var/log fastdfs: build: context: ./fastdfs/build/ dockerfile: Dockerfile args: TRACKER_PORT: 22122 FDFS_NGX_PORT: 8888 image: fastdfs-nginx:5.11 restart: always container_name: cs_web-fastdfs environment: TRACKER_PORT: 22122 FDFS_NGX_PORT: 8888 HOST_IP: fastdfs volumes: - ./fastdfs/data:/data/fastdfs - ./fastdfs/nginx/logs:/usr/local/nginx/logs/ nginx: image: nginx:stable restart: always container_name: cs_web-nginx environment: NGINX_HOST: demo.xxxxx.org.cn NGINX_FASTDFS_HOST: fastdfs.xxxxx.org.cn NGINX_PORT: 80 NGINX_SSL_PORT: 443 TOMCAT: cs_web-tomcat FASTDFS: cs_web-fastdfs ports: - 80:80 - 443:443 volumes: - ./nginx/conf/nginx.conf:/etc/nginx/nginx.conf - ./nginx/conf/mysite.template:/etc/nginx/conf.d/mysite.template - ./nginx/ssl/demo.xxxxx.org.cn/fullchain.pem:/etc/nginx/ssl/demo.xxxxx.org.cn/fullchain.pem - ./nginx/ssl/demo.xxxxx.org.cn/privkey.pem:/etc/nginx/ssl/demo.xxxxx.org.cn/privkey.pem - ./nginx/ssl/fastdfs.xxxxx.org.cn/fullchain.pem:/etc/nginx/ssl/fastdfs.xxxxx.org.cn/fullchain.pem - ./nginx/ssl/fastdfs.xxxxx.org.cn/privkey.pem:/etc/nginx/ssl/fastdfs.xxxxx.org.cn/privkey.pem - ./nginx/log/:/var/log/nginx/ - ./nginx/html/competitionShare_web/:/var/www/html/competitionShare_web/ command: /bin/bash -c "envsubst '$$NGINX_HOST $$NGINX_PORT $$NGINX_SSL_PORT $$TOMCAT $$FASTDFS $$NGINX_FASTDFS_HOST' < /etc/nginx/conf.d/mysite.template > /etc/nginx/conf.d/demo.xxxxx.org.cn.conf && nginx -g 'daemon off;'" tomcat: image: tomcat:8.0.53-jre8 restart: always depends_on: - db - fastdfs container_name: cs_web-tomcat environment: PROJECT_ENV: demo JAVA_OPTS: "-Dsupplements.host=supplements" CATALINA_OPTS: "-server -Xms256M -Xmx1024M -XX:MaxNewSize=256m" DEMO_JDBC_URL: jdbc:mysql://db:3306/competitionShare??characterEncoding=UTF-8 DEMO_JDBC_USER: demo DEMO_JDBC_PASS: abc123456 FDFS_URL: https://fastdfs.demo.org.cn/ volumes: - ./tomcat/webapps:/usr/local/tomcat/webapps - ./tomcat/conf/server.xml:/usr/local/tomcat/conf/server.xml - ./tomcat/log:/log ``` #### **启动** ```bash $ docker-compose up ``` ![](https://image.ynotes.cn/18-8-14/7776948.jpg) #### **浏览器访问** ![](https://image.ynotes.cn/18-8-14/75371827.jpg)
阅读 759 评论 1 收藏 0
阅读 759
评论 1
收藏 0

   2018-08-12 15:22:41    2019-11-14 14:32:31   

mysql ProxySQL
### 一、环境准备 系统: `CentOS7` 数据库: `ProxySQL:1.4.14` 服务器: `master`: `172.16.0.100/db1` `slave`: `172.16.0.101/db2` `slave/ProxySQL`: `172.16.0.102/db3` &emsp; &emsp; ### 二、准备工作 `a.数据库搭建MySQL主从` `b.从库开启`**`read_only=on`** &emsp; &emsp; ### 三、ProxySQL的安装 #### 安装依赖的软件包 `db3` ```bash yum -y install perl-DBD-MySQL perl-DBI perl-Time-HiRes perl-IO-Socket-SSL ``` &emsp; #### ProxySQL软件包的两个下载地址 GitHub官网:https://github.com/sysown/proxysql/releases percona官网:https://www.percona.com/downloads/proxysql/ &emsp; #### 安装ProxySQL `db3` ```bash yum install -y https://www.percona.com/downloads/proxysql/proxysql-1.4.14/binary/redhat/7/x86_64/proxysql-1.4.14-1.1.el7.x86_64.rpm ``` &emsp; #### 启动ProxySQL `db3` ```bash systemctl start proxysql ``` &emsp; #### 查看启动信息 `db3` ```bash netstat -tunlp |grep proxysql ``` ``` tcp 0 0 0.0.0.0:6032 0.0.0.0:* LISTEN 13331/proxysql tcp 0 0 0.0.0.0:6033 0.0.0.0:* LISTEN 13331/proxysql ``` `6032为管理端口,6033为对外服务的端口号` &emsp; #### 登录ProxySQL `db3` ```bash mysql -uadmin -padmin -h 127.0.0.1 -P 6032 ``` &emsp; #### 查看proxysql信息 `db3` ```sql mysql> show databases; +-----+---------------+-------------------------------------+ | seq | name | file | +-----+---------------+-------------------------------------+ | 0 | main | | | 2 | disk | /var/lib/proxysql/proxysql.db | | 3 | stats | | | 4 | monitor | | | 5 | stats_history | /var/lib/proxysql/proxysql_stats.db | +-----+---------------+-------------------------------------+ #可见有四个库:main、disk、stats和monitor。分别说明一下这四个库的作用。 #main:内存配置数据库,即MEMORY,表里存放后端db实例、用户验证、路由规则等信息。 mysql> show tables; +--------------------------------------------+ | tables | +--------------------------------------------+ | global_variables | | mysql_collations | | mysql_group_replication_hostgroups | | mysql_query_rules | | mysql_query_rules_fast_routing | | mysql_replication_hostgroups | | mysql_servers | | mysql_users | | proxysql_servers | | runtime_checksums_values | | runtime_global_variables | | runtime_mysql_group_replication_hostgroups | | runtime_mysql_query_rules | | runtime_mysql_query_rules_fast_routing | | runtime_mysql_replication_hostgroups | | runtime_mysql_servers | | runtime_mysql_users | | runtime_proxysql_servers | | runtime_scheduler | | scheduler | +--------------------------------------------+ #库下的主要表: #mysql_servers—后端可以连接MySQL服务器的列表。 #mysql_users—配置后端数据库的账号和监控的账号。 #mysql_query_rules—指定Query路由到后端不同服务器的规则列表。 #注:表名以runtime_开头的表示ProxySQL当前运行的配置内容,不能通过DML语句修改。只能修改对应的不以 runtime开头的表,然后“LOAD”使#其生效,“SAVE”使其存到硬盘以供下次重启加载。 #disk库—持久化磁盘的配置。 #stats库—统计信息的汇总。 #monitor库—一些监控的收集信息,包括数据库的健康状态等。 ``` &emsp; &emsp; ### 四、配置ProxySQL监控 #### Master上创建ProxySQL的监控账户和对外访问账户并赋予权限 `db1` ```sql mysql> create user 'monitor'@'172.16.0.%' identified by 'monitor'; mysql> grant all privileges on *.* to 'monitor'@'172.16.0.%' with grant option; mysql> create user 'proxysql'@'172.16.0.%' identified by 'proxysql'; mysql> grant all privileges on *.* to 'proxysql'@'172.16.0.%' with grant option; mysql> flush privileges; ``` &emsp; #### ProxySQL添加主从服务器列表 `db3` ```sql mysql> insert into mysql_servers(hostgroup_id,hostname,port) values(10,'172.16.0.100',3306); mysql> insert into mysql_servers(hostgroup_id,hostname,port) values(10,'172.16.0.101',3306); mysql> insert into mysql_servers(hostgroup_id,hostname,port) values(10,'172.16.0.102',3306); ``` &emsp; #### ProxySQL查看服务器列表 `db3` ```sql mysql> select * from mysql_servers; +--------------+--------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+ | hostgroup_id | hostname | port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment | +--------------+--------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+ | 10 | 172.16.0.100 | 3306 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | | | 20 | 172.16.0.101 | 3306 | ONLINE | 10 | 0 | 1000 | 0 | 0 | 0 | | | 20 | 172.16.0.102 | 3306 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | | +--------------+--------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+ ``` &emsp; #### ProxySQL配置监控账号 `db3` ```sql mysql> set mysql-monitor_username='monitor'; mysql> set mysql-monitor_password='monitor'; mysql> load mysql variables to runtime; mysql> save mysql variables to disk; ``` &emsp; #### ProxySQL验证监控信息 `db3` ```sql mysql> select * from monitor.mysql_server_connect_log order by time_start_us desc limit 6; +--------------+------+------------------+-------------------------+---------------+ | hostname | port | time_start_us | connect_success_time_us | connect_error | +--------------+------+------------------+-------------------------+---------------+ | 172.16.0.100 | 3306 | 1565593172754235 | 2118 | NULL | | 172.16.0.101 | 3306 | 1565593172137991 | 2729 | NULL | | 172.16.0.102 | 3306 | 1565593171521858 | 773 | NULL | | 172.16.0.100 | 3306 | 1565593113076006 | 2163 | NULL | | 172.16.0.101 | 3306 | 1565593112298748 | 2377 | NULL | | 172.16.0.102 | 3306 | 1565593111521583 | 628 | NULL | +--------------+------+------------------+-------------------------+---------------+ ``` &emsp; &emsp; ### 五、配置ProxySQL主从分组信息 #### ProxySQL插入读写分离组 `db3` ```sql mysql> insert into mysql_replication_hostgroups(writer_hostgroup,reader_hostgroup,comment) values (10,20,'proxy'); mysql> load mysql servers to runtime; mysql> save mysql servers to disk; # writer_hostgroup是写入组的编号,reader_hostgroup是读取组的编号。实验中使用10作为写入组,20作为读取组编号。 mysql> select * from mysql_servers; +--------------+--------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+ | hostgroup_id | hostname | port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment | +--------------+--------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+ | 10 | 172.16.0.100 | 3306 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | | | 20 | 172.16.0.101 | 3306 | ONLINE | 10 | 0 | 1000 | 0 | 0 | 0 | | | 20 | 172.16.0.102 | 3306 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | | +--------------+--------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+ # ProxySQL会根据server的read_only的取值将服务器进行分组。read_only=0的server,master被分到编号为10的写组,read_only=1的server,slave则被分到编号为20的读组。 ``` &emsp; #### ProxySQL配置对外访问账号(`默认指定主库,并对该用户开启事务持久化保护`) `db3` ```sql insert into mysql_users(username,password,default_hostgroup) values ('proxysql','proxysql',10); update mysql_users set transaction_persistent=1 where username='proxysql'; #设置为1,避免发生脏读、幻读等现象 load mysql users to runtime; save mysql users to disk; ``` &emsp; #### ProxySQL验证登录的服务器 `db3` ```bash mysql -uproxysql -pproxysql -h 172.16.0.102 -P 6033 ``` ```sql mysql> select @@hostname; +------------+ | @@hostname | +------------+ | db1 | +------------+ # 验证登入的服务器默认为主库(db1) ``` &emsp; &emsp; ### 六、ProxySQL配置读写分离策略 #### ProxySQL配置读写分离 `db3` ```sql mysql> insert into mysql_query_rules(active,match_pattern,destination_hostgroup, apply) VALUES(1,'^SELECT.*FOR UPDATE$',10,1); mysql> insert into mysql_query_rules(active,match_pattern,destination_hostgroup, apply) VALUES(1,'^SELECT',20,1); mysql> load mysql query rules to runtime; mysql> save mysql query rules to disk; ``` &emsp; #### ProxySQL配置服务器的权重 `db3` ```sql mysql> update mysql_servers set weight=10 where hostname='172.16.0.101'; mysql> load mysql servers to runtime; mysql> save mysql servers to disk; # 172.16.0.101的权重被修改为10,172.16。0.102的默认权重为1,则请求比例为10:1 ``` &emsp; &emsp; ### 七、测试读写分离 #### 执行select查询 `db3` ```sql mysql -uproxysql -pproxysql -h 172.16.0.102 -P 6033 ``` ```sql mysql> select node_name from percona.example where node_id=1; ``` &emsp; #### 执行select for update查询 `db3` ```sql mysql> select node_name from percona.example where node_id=1 for update; ``` &emsp; #### 执行update语句 `db3` ```sql mysql> update percona.example set node_name='test' where node_id=1; ``` &emsp; #### proxySQL查看统计信息 `db3` ```bash mysql -uadmin -padmin -h 127.0.0.1 -P 6032 ``` ```sql mysql> select hostgroup,digest_text from stats_mysql_query_digest; +-----------+--------------------------------------------------------------------------------------------------+ | hostgroup | digest_text | +-----------+--------------------------------------------------------------------------------------------------+ | 10 | update percona.example set node_name=? where node_id=? | | 10 | select node_name,@@hostname from percona.example where node_id=? for update | | 20 | select node_name,@@hostname from percona.example where node_id=? | +-----------+--------------------------------------------------------------------------------------------------+ ``` `从上面的输出信息可以发现读写分离成功了` 参考: https://blog.51cto.com/sumongodb/2130453
阅读 7 评论 0 收藏 0
阅读 7
评论 0
收藏 0

   2018-08-10 16:11:21    2018-08-10 16:11:21   

docker 容器 fastdfs Dockerfile fdfs
#### **环境:** 系统: **Centos7** Docker版本: **18.03.1-ce, build 9ee9f40** 容器网络: **桥接docker0** 容器网段: **10.10.0.0/24** #### **Dockerfile文件** ```bash FROM alpine:3.6 MAINTAINER ynotes.cn <admin@ynotes.cn> #环境变量 ENV NGINX_PORT 80 ENV FASTDFS_PORT 22122 #编译参数 ARG HOME=/root ARG FASTDFS_VERSION=5.11 ARG LIBFASTCOMMON_VERSION=1.0.38 ARG FASTDFS_NGINX_MODULE_VERSION=1.20 ARG NGINX_VERSION=1.12.1 #下载包 RUN cd ${HOME} \ && sed -i 's#http://[^/]*/\(.*\)$#http://mirrors.aliyun.com/\1#g' /etc/apk/repositories \ && apk update \ && apk add --no-cache --virtual .build-deps bash gcc libc-dev make openssl-dev pcre-dev zlib-dev linux-headers curl gnupg libxslt-dev gd-dev geoip-dev \ && curl -fLS https://github.com/happyfish100/fastdfs/archive/V${FASTDFS_VERSION}.tar.gz -o V${FASTDFS_VERSION}.tar.gz \ && curl -fLS https://github.com/happyfish100/libfastcommon/archive/V${LIBFASTCOMMON_VERSION}.tar.gz -o V${LIBFASTCOMMON_VERSION}.tar.gz \ && curl -fLS https://github.com/happyfish100/fastdfs-nginx-module/archive/V${FASTDFS_NGINX_MODULE_VERSION}.tar.gz -o V${FASTDFS_NGINX_MODULE_VERSION}.tar.gz \ && curl -fSL http://nginx.org/download/nginx-${NGINX_VERSION}.tar.gz -o nginx-${NGINX_VERSION}.tar.gz \ && tar xf V${FASTDFS_VERSION}.tar.gz \ && tar xf V${LIBFASTCOMMON_VERSION}.tar.gz \ && tar xf V${FASTDFS_NGINX_MODULE_VERSION}.tar.gz \ && tar zxf nginx-${NGINX_VERSION}.tar.gz #安装包 RUN cd ${HOME}/libfastcommon-${LIBFASTCOMMON_VERSION}/ \ && ./make.sh \ && ./make.sh install \ && cd ${HOME}/fastdfs-${FASTDFS_VERSION}/ \ && ./make.sh \ && ./make.sh install \ && sed "s@/home/yuqing/fastdfs@/data/fastdfs/tracker@g" /etc/fdfs/tracker.conf.sample > /etc/fdfs/tracker.conf \ && sed "s@/home/yuqing/fastdfs@/data/fastdfs/storage@g" /etc/fdfs/storage.conf.sample > /etc/fdfs/storage.conf \ && sed "s@/home/yuqing/fastdfs@/data/fastdfs/storage@g" /etc/fdfs/client.conf.sample > /etc/fdfs/client.conf \ && sed -i 's#CORE_INCS=.*#CORE_INCS="$CORE_INCS /usr/include/fastdfs /usr/include/fastcommon/"#g' ${HOME}/fastdfs-nginx-module-${FASTDFS_NGINX_MODULE_VERSION}/src/config \ && sed -i 's#ngx_module_incs=.*#ngx_module_incs="/usr/include/fastdfs /usr/include/fastcommon/"#g' ${HOME}/fastdfs-nginx-module-${FASTDFS_NGINX_MODULE_VERSION}/src/config \ && chmod u+x ${HOME}/fastdfs-nginx-module-${FASTDFS_NGINX_MODULE_VERSION}/src/config \ && cd ${HOME}/nginx-${NGINX_VERSION} \ && ./configure --add-module=${HOME}/fastdfs-nginx-module-${FASTDFS_NGINX_MODULE_VERSION}/src \ && make && make install #配置包 RUN cp ${HOME}/fastdfs-nginx-module-${FASTDFS_NGINX_MODULE_VERSION}/src/mod_fastdfs.conf /etc/fdfs/ \ && sed -i "s#^store_path0.*#store_path0 = /data/fastdfs/storage#g" /etc/fdfs/mod_fastdfs.conf \ && sed -i "s#^url_have_group_name.*#url_have_group_name = true#g" /etc/fdfs/mod_fastdfs.conf \ && cd ${HOME}/fastdfs-${FASTDFS_VERSION}/conf/ \ && cp http.conf mime.types /etc/fdfs/ \ && echo -e "worker_processes 2;\nevents { \nworker_connections 10240; \n}\nhttp { \ninclude mime.types;\ndefault_type application/octet-stream;\nsendfile on;\nkeepalive_timeout 65;\nserver {\nlisten NGINX_PORT;\nserver_name localhost;\nlocation ~/group([0-9])/M00 {\nngx_fastdfs_module;\n}\n}\n}">/usr/local/nginx/conf/nginx.conf #清理包 RUN rm -rf ${HOME}/* \ && apk del .build-deps gcc libc-dev make openssl-dev linux-headers curl gnupg libxslt-dev gd-dev geoip-dev \ && apk add bash pcre-dev zlib-dev #安装脚本 RUN sed -i "s/NGINX_PORT/$NGINX_PORT/g" /usr/local/nginx/conf/nginx.conf \ && echo -e "mkdir -p /data/fastdfs/storage/data\nmkdir -p /data/fastdfs/tracker\nln -s /data/fastdfs/storage/data /data/fastdfs/storage/data/M00\nHOST_IP=\$(ip addr |grep 'scope global eth0'|awk '{ print \$2}'|awk -F/ '{ print \$1 }')\nsed -i "s/^tracker_server=.*$/tracker_server=\$HOST_IP:$FASTDFS_PORT/g" /etc/fdfs/storage.conf\nsed -i "s/^tracker_server=.*$/tracker_server=\$HOST_IP:$FASTDFS_PORT/g" /etc/fdfs/mod_fastdfs.conf\n/etc/init.d/fdfs_trackerd start \n/etc/init.d/fdfs_storaged start\n/usr/local/nginx/sbin/nginx\ntail -f /usr/local/nginx/logs/access.log" >/start.sh \ && chmod +x /start.sh EXPOSE 80 22122 23000 ENTRYPOINT ["/bin/bash","/start.sh"] ``` #### **编译镜像文件** ```bash $ docker build -t fastdfs-nginx:v5.11 . ``` #### **启动容器** ```bash $ docker run -p 80:80 -p 22122:22122 -p 23000:23000 -v /root/docker_demo/fastdfs/data:/data/fastdfs fastdfs-nginx:v5.11 ```      #### **测试机1(Centos7)** 添加路由 ```bash $ route add -net 10.10.0.0 netmask 255.255.255.0 gw 192.168.50.252 ``` 上传图片 ```bash $ fdfs_test /etc/fdfs/client.conf upload zzzz.jpg ``` ``` group_name=group1, remote_filename=M00/00/00/CgoAAVtxAhqAWpxyAAE7WHOlIPs425.jpg source ip address: 10.10.0.1 file timestamp=2018-08-13 11:59:22 file size=80728 file crc32=1940201723 example file url: http://10.10.0.1/group1/M00/00/00/CgoAAVtxAhqAWpxyAAE7WHOlIPs425.jpg storage_upload_slave_by_filename group_name=group1, remote_filename=M00/00/00/CgoAAVtxAhqAWpxyAAE7WHOlIPs425_big.jpg source ip address: 10.10.0.1 file timestamp=2018-08-13 11:59:22 file size=80728 file crc32=1940201723 example file url: http://10.10.0.1/group1/M00/00/00/CgoAAVtxAhqAWpxyAAE7WHOlIPs425_big.jpg ```    #### **测试机2(window7)** 添加路由 ```cmd > route add 10.10.0.0 mask 255.255.255.0 192.168.50.252 ``` 访问图片 ![](https://image.ynotes.cn/18-8-13/79351609.jpg)
阅读 613 评论 0 收藏 0
阅读 613
评论 0
收藏 0

   2018-08-09 15:07:32    2019-07-23 09:50:22   

nginx https X-Forwarded-Proto scheme
#### **nginx+tomcat** nginx配置: ```bash proxy_set_header Host $host; proxy_set_header Cookie $http_cookie; proxy_set_header X-Real-IP $remote_addr; proxy_set_header X-Forwarded-For $proxy_add_x_forwarded_for; proxy_set_header X-Forwarded-Proto $scheme; proxy_pass http://127.0.0.1:8181; ``` tomcat配置: ```xml <Valve className="org.apache.catalina.valves.RemoteIpValve" remoteIpHeader="X-Forwarded-For" protocolHeader="X-Forwarded-Proto" protocolHeaderHttpsValue="https"/> ```    #### **阿里云SLB+nginx+tomcat** 阿里云SLB配置: ![](https://image.ynotes.cn/18-8-9/93864542.jpg) nginx配置: ```bash proxy_set_header Host $host; proxy_set_header Cookie $http_cookie; proxy_set_header X-Real-IP $remote_addr; proxy_set_header X-Forwarded-For $proxy_add_x_forwarded_for; proxy_set_header X-Forwarded-Proto $http_x_forwarded_proto; #http_x_forwarded_proto参数为SLB传过来的参数 proxy_pass http://127.0.0.1:8181; ``` tomcat配置: ```xml <Valve className="org.apache.catalina.valves.RemoteIpValve" remoteIpHeader="X-Forwarded-For" protocolHeader="X-Forwarded-Proto" protocolHeaderHttpsValue="https"/> ``` #### 上面的配置,一般的访问没有问题,当页面发生302重定向会请求http的问题,出现 requested an insecure XMLHttpRequest nginx配置(nginx+tomcat) ```bash proxy_redirect http:// $scheme://; #302重定向请求的http协议转发到$scheme ``` nginx配置(阿里云SLB+nginx+tomcat) ```bash proxy_redirect http:// $http_x_forwarded_proto://; #302重定向请求的http协议转发到$http_x_forwarded_proto ```
阅读 570 评论 0 收藏 0
阅读 570
评论 0
收藏 0

   2018-08-09 15:01:14    2019-11-14 14:32:44   

mysql PXC
### 一、环境准备 系统: `CentOS7` 数据库: `Percona-XtraDB-Cluster-57` 服务器: `master1`: `172.16.0.100/db1` `master2`: `172.16.0.101/db2` `master2`: `172.16.0.102/db3` &emsp; &emsp; ### 二、准备工作 `a.删除mysql-community` ```bash yum remove mysql-community-client mysql-community-server -y #仅安装了mysql-community需要执行 ``` `b.关闭防火墙` `c.关闭Selinux` &emsp; &emsp; ### 三、安装PXC集群 `所有节点` 安装Percona库 ```bash yum install https://repo.percona.com/yum/percona-release-latest.noarch.rpm -y ``` 安装Percona-XtraDB-Cluster-57 ```bash yum install Percona-XtraDB-Cluster-57 -y ``` 启动MySQL ```bash systemctl start mysql.service ``` 获取初始密码 ```bash grep 'temporary password' /var/log/mysqld.log ``` ``` 2019-08-09T03:22:26.358453Z 1 [Note] A temporary password is generated for root@localhost: so*WrNqjm3(e ``` 修改root密码 ```sql mysql -u root -p mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'rootPass'; mysql> exit ``` 停止MySQL ```bash systemctl stop mysql.service ``` &emsp; &emsp; ### 四、引导第一个节点 修改配置 `Master1` ```bash vim /etc/percona-xtradb-cluster.conf.d/wsrep.cnf ``` ```ini [mysqld] wsrep_provider=/usr/lib64/galera3/libgalera_smm.so wsrep_cluster_address=gcomm:// #配置wsrep_cluster_address=gcomm:// 为了初始化集群,添加其他节点之后再修改回正常的配置,再重启节点即可 binlog_format=ROW default_storage_engine=InnoDB wsrep_slave_threads= 8 wsrep_log_conflicts innodb_autoinc_lock_mode=2 wsrep_cluster_name=pxc-cluster-test #集群名字 wsrep_node_name=db1 #节点名 wsrep_node_address=172.16.0.100 #当前节点IP pxc_strict_mode=ENFORCING wsrep_sst_method=xtrabackup-v2 #SST传输方法 wsrep_sst_auth=sstuser:passw0rd #SST账号 ``` bootstrap启动节点 ```bash systemctl start mysql@bootstrap.service ``` 查看集群信息 ```sql mysql> show status like 'wsrep%'; +----------------------------+--------------------------------------+ | Variable_name | Value | +----------------------------------+--------------------------------+ | wsrep_local_state_uuid | ee7be278-ba54-11e9-9621-8ee7979a72d4 | | ... | ... | | wsrep_local_state | 4 | | wsrep_local_state_comment | Synced | | ... | ... | | wsrep_incoming_addresses | 172.16.0.100:3306 | | wsrep_cluster_size | 1 | | wsrep_cluster_status | Primary | | wsrep_connected | ON | | ... | ... | | wsrep_ready | ON | +----------------------------+--------------------------------------+ ``` `上面信息显示集群几点为1,节点状态为Synced` 创建SST用户 ```sql mysql CREATE USER 'sstuser'@'localhost' IDENTIFIED BY 'passw0rd'; mysql> GRANT RELOAD, LOCK TABLES, PROCESS, REPLICATION CLIENT ON *.* TO 'sstuser'@'localhost'; mysql> FLUSH PRIVILEGES; ``` &emsp; &emsp; ### 五、添加节点到集群 #### 添加Master2到集群 修改配置 `Master2` ```bash vim /etc/percona-xtradb-cluster.conf.d/wsrep.cnf ``` ```bash [mysqld] wsrep_provider=/usr/lib64/galera3/libgalera_smm.so wsrep_cluster_address=gcomm://172.16.0.100,172.16.0.101,172.16.0.102 binlog_format=ROW default_storage_engine=InnoDB wsrep_slave_threads= 8 wsrep_log_conflicts innodb_autoinc_lock_mode=2 wsrep_cluster_name=pxc-cluster-test wsrep_node_name=db2 wsrep_node_address=172.16.0.101 pxc_strict_mode=ENFORCING wsrep_sst_method=xtrabackup-v2 wsrep_sst_auth=sstuser:passw0rd ``` 启动节点 ```bash systemctl start mysql ``` 查看集群状态 ```sql mysql> show status like 'wsrep%'; +----------------------------+--------------------------------------+ | Variable_name | Value | +----------------------------------+--------------------------------+ | wsrep_local_state_uuid | ee7be278-ba54-11e9-9621-8ee7979a72d4 | | ... | ... | | wsrep_local_state | 4 | | wsrep_local_state_comment | Synced | | ... | ... | | wsrep_incoming_addresses | 172.16.0.101:3306,172.16.0.100:3306 | | wsrep_cluster_size | 2 | | wsrep_cluster_status | Primary | | wsrep_connected | ON | | ... | ... | | wsrep_ready | ON | +----------------------------+--------------------------------------+ ``` `上面信息显示集群现在为2个节点,172.16.0.101:3306,172.16.0.100:3306` #### 添加Master3到集群 修改配置 `Master3` ```bash vim /etc/percona-xtradb-cluster.conf.d/wsrep.cnf ``` ```bash [mysqld] wsrep_provider=/usr/lib64/galera3/libgalera_smm.so wsrep_cluster_address=gcomm://172.16.0.100,172.16.0.101,172.16.0.102 binlog_format=ROW default_storage_engine=InnoDB wsrep_slave_threads= 8 wsrep_log_conflicts innodb_autoinc_lock_mode=2 wsrep_cluster_name=pxc-cluster-test wsrep_node_name=db3 wsrep_node_address=172.16.0.102 pxc_strict_mode=ENFORCING wsrep_sst_method=xtrabackup-v2 wsrep_sst_auth=sstuser:passw0rd ``` 启动节点 ```bash systemctl start mysql ``` 查看集群状态 ```sql mysql> show status like 'wsrep%'; +----------------------------+-------------------------------------------------------+ | Variable_name | Value | +----------------------------+-------------------------------------------------------+ | wsrep_local_state_uuid | ee7be278-ba54-11e9-9621-8ee7979a72d4 | | ... | ... | | wsrep_local_state | 4 | | wsrep_local_state_comment | Synced | | ... | ... | | wsrep_incoming_addresses | 172.16.0.102:3306,172.16.0.101:3306,172.16.0.100:3306 | | wsrep_cluster_size | 3 | | wsrep_cluster_status | Primary | | wsrep_connected | ON | | ... | ... | | wsrep_ready | ON | +----------------------------+-------------------------------------------------------+ ``` `上面信息显示集群现在为3个节点,172.16.0.102:3306,172.16.0.101:3306,172.16.0.100:3306` 重新添加Master1到集群 停止MySQL `Master1` ```bash systemctl stop mysql@bootstrap.service ``` 修改配置 `Master1` ```sql vim /etc/percona-xtradb-cluster.conf.d/wsrep.cnf ``` ```ini ... #wsrep_cluster_address=gcomm:// 修改前 wsrep_cluster_address=gcomm://172.16.0.100,172.16.0.101,172.16.0.102 #修改后 ... ``` 启动数据库 `Master1` ```bash systemctl start mysql ``` &emsp; &emsp; ### 六、验证集群 在Master2节点插入测试数据 `Master2` ```sql mysql> CREATE DATABASE percona; mysql> USE percona; mysql> CREATE TABLE example (node_id INT PRIMARY KEY, node_name VARCHAR(30)); mysql> INSERT INTO example VALUES (1, 'percona1'); ``` 所有节点查询数据 `所有节点` ```sql mysql> SELECT * FROM percona.example; +---------+-----------+ | node_id | node_name | +---------+-----------+ | 1 | percona1 | +---------+-----------+ ``` `测试发现所有数据都已经同步,说明集群搭建成功!` &emsp; &emsp; ### 七、从节点在线转换成PXC节点 #### **`说明:测试使用db1做主节点,db3做从节点`** #### 重置db3 `a.清理旧的数据并初始化数据库` ```bash systemctl stop mysql mv /var/lib/mysql/ /var/lib/mysql_bak/ mkdir /var/lib/mysql mv /etc/percona-xtradb-cluster.conf.d/wsrep.cnf /tmp #移除PXC相关配置 chown -R mysql.mysql /var/lib/mysql systemctl start mysql #启动并初始化数据库 ``` `b.获取初始密码` ```bash grep 'temporary password' /var/log/mysqld.log ``` ``` 2019-08-09T03:22:26.358453Z 1 [Note] A temporary password is generated for root@localhost: so*WrNqjm3(e ``` `c.修改root密码` ```sql mysql -u root -p mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'rootPass'; mysql> exit ``` &emsp; #### 创建复制账号 `db1` ```sql mysql> create user 'bak'@'172.16.0.%' identified by '123456'; mysql> grant replication slave on *.* to 'bak'@'172.16.0.%'; mysql> flush privileges; ``` #### 备份数据库并发送到db3 `db1` ```bash mysqldump -hlocalhost -uroot -pxxxxxx --single-transaction -A --master-data=2 > all.sql scp all.sql db3:/root ``` #### 从节点执行数据库恢复 `db3` ```bash mysql -hlocalhost -uroot -prootPass </root/all.sql ``` #### 配置从节点同步主节点 `db3` ```sql mysql> CHANGE MASTER TO MASTER_HOST='172.16.0.100',MASTER_USER='bak',MASTER_PASSWORD='test123',MASTER_LOG_FILE='db1-bin.000004', MASTER_LOG_POS=88754; mysql> start slave; ``` #### 停止从节点获取同步位置 `db3` ```sql mysql> stop slave; #停止slave mysql> show slave status\G #查看同步位置 *************************** 1. row *************************** Slave_IO_State: Master_Host: 172.16.0.100 Master_User: bak Master_Port: 3306 Connect_Retry: 60 Master_Log_File: db1-bin.000004 Read_Master_Log_Pos: 190214 Relay_Log_File: db3-relay-bin.000003 Relay_Log_Pos: 101778 Relay_Master_Log_File: db1-bin.000004 Slave_IO_Running: No Slave_SQL_Running: No 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: 190214 ``` `上面的输出显示同步文件:db1-bin.000004,位置为:190214` #### 从节点重置slave配置(`下次启动直接使用PXC去同步`) `db3` ```sql mysql> reset slave all; ``` #### 从节点停止MySQL `db3` ```bash systemctl stop mysql ``` #### 主节点查看同步的Position对应Xid `db1` ```bash mysqlbinlog db1-bin.000004 |grep Xid|grep 190214 ``` ``` #190809 8:57:53 server id 1 end_log_pos 190214 CRC32 0x4cfb3e3b Xid = 675 ``` `确认Postion 190214对应的Xid为675` #### 主节点拷贝grastate.dat文件到从节点 `db1` ```bash scp grastate.dat db3:/var/lib/mysql ``` #### 从节点修改grastate.dat `db3` ```bash cat grastate.dat ``` ```ini # GALERA saved state version: 2.1 uuid: ee7be278-ba54-11e9-9621-8ee7979a72d4 seqno: 675 #-1修改为上面的Xid safe_to_bootstrap: 0 ``` ```bash chown mysql.mysql /var/lib/mysql/grastate.dat ``` #### 加入PXC相关配置 `db3` ```bash vim /etc/percona-xtradb-cluster.conf.d/wsrep.cnf ``` ```ini [mysqld] wsrep_provider=/usr/lib64/galera3/libgalera_smm.so wsrep_cluster_address=gcomm://172.16.0.100,172.16.0.101,172.16.0.102 binlog_format=ROW default_storage_engine=InnoDB wsrep_slave_threads= 8 wsrep_log_conflicts innodb_autoinc_lock_mode=2 wsrep_cluster_name=pxc-cluster-test wsrep_node_name=db3 wsrep_node_address=172.16.0.102 pxc_strict_mode=ENFORCING wsrep_sst_method=xtrabackup-v2 wsrep_sst_auth=sstuser:passw0rd ``` #### 启动数据库 `db3` ```bash systemctl start mysql ``` #### 查看集群信息 `db3` ```sql mysql> show status like 'wsrep%'; +----------------------------------+-------------------------------------------------------+ | Variable_name | Value | +----------------------------------+-------------------------------------------------------+ | wsrep_local_state_uuid | ee7be278-ba54-11e9-9621-8ee7979a72d4 | | wsrep_protocol_version | 9 | | wsrep_last_applied | 1431 | | wsrep_last_committed | 1431 | | wsrep_replicated | 0 | | wsrep_replicated_bytes | 0 | | wsrep_repl_keys | 0 | | wsrep_repl_keys_bytes | 0 | | wsrep_repl_data_bytes | 0 | | wsrep_repl_other_bytes | 0 | | wsrep_received | 247 | | wsrep_received_bytes | 77425 | | wsrep_local_commits | 0 | | wsrep_local_cert_failures | 0 | | wsrep_local_replays | 0 | | wsrep_local_send_queue | 0 | | wsrep_local_send_queue_max | 1 | | wsrep_local_send_queue_min | 0 | | wsrep_local_send_queue_avg | 0.000000 | | wsrep_local_recv_queue | 0 | | wsrep_local_recv_queue_max | 2 | | wsrep_local_recv_queue_min | 0 | | wsrep_local_recv_queue_avg | 0.004049 | | wsrep_local_cached_downto | 1191 | | wsrep_flow_control_paused_ns | 0 | | wsrep_flow_control_paused | 0.000000 | | wsrep_flow_control_sent | 0 | | wsrep_flow_control_recv | 0 | | wsrep_flow_control_interval | [ 173, 173 ] | | wsrep_flow_control_interval_low | 173 | | wsrep_flow_control_interval_high | 173 | | wsrep_flow_control_status | OFF | | wsrep_cert_deps_distance | 75.099585 | | wsrep_apply_oooe | 0.654762 | | wsrep_apply_oool | 0.009259 | | wsrep_apply_window | 5.033069 | | wsrep_commit_oooe | 0.000000 | | wsrep_commit_oool | 0.000000 | | wsrep_commit_window | 2.513228 | | wsrep_local_state | 4 | | wsrep_local_state_comment | Synced | | wsrep_cert_index_size | 116 | | wsrep_cert_bucket_count | 210 | | wsrep_gcache_pool_size | 88288 | | wsrep_causal_reads | 0 | | wsrep_cert_interval | 0.000000 | | wsrep_open_transactions | 0 | | wsrep_open_connections | 0 | | wsrep_ist_receive_status | | | wsrep_ist_receive_seqno_start | 0 | | wsrep_ist_receive_seqno_current | 0 | | wsrep_ist_receive_seqno_end | 0 | | wsrep_incoming_addresses | 172.16.0.101:3306,172.16.0.100:3306,172.16.0.102:3306 | +----------------------------------+-------------------------------------------------------+ ``` 参考:https://www.percona.com/doc/percona-xtradb-cluster/5.7/index.html#introduction 参考书籍: MySQL王者晋级之路/张甦
阅读 8 评论 0 收藏 0
阅读 8
评论 0
收藏 0

   2018-08-08 14:46:59    2019-11-14 14:33:01   

mysql GTID
### 环境准备 系统: `CentOS7` 数据库: `MySQL5.7` Master节点: `172.16.0.100(node1)` Slave节点: `172.16.0.101(node2)` #### GTID复制切换成传统复制 查看复制信息 `Slave` ```sql mysql> show slave status\G ``` ``` Slave_IO_State: Waiting for master to send event Master_Host: 172.16.0.100 Master_User: replica Master_Port: 3306 Connect_Retry: 60 Master_Log_File: on.000001 Read_Master_Log_Pos: 7227 Relay_Log_File: db3-relay-bin.000002 Relay_Log_Pos: 2374 Relay_Master_Log_File: on.000001 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: 7227 ... ``` 停止Slave,配置MASTER_AUTO_POSITION=0 `Slave` ```sql mysql> stop slave; mysql> CHANGE MASTER TO MASTER_HOST='172.16.0.100', MASTER_PORT=3306,MASTER_USER='replica', MASTER_PASSWORD='xxxxx',MASTER_AUTO_POSITION=0,MASTER_LOG_FILE='on.000001',MASTER_LOG_POS=7227; mysql> start slave; ``` 主从同时配置GTID模式为on_permissive `Master/Slave` ```sql mysql> set global gtid_mode=on_permissive ``` 主从同时配置GTID模式为off_permissive `Master/Slave` ```sql mysql> set global gtid_mode=off_permissive ``` 主从同时配置关闭GTID功能 `Master/Slave` ```sql mysql> set global enforce_gtid_consistency=off; mysql> set global gtid_mode=off; ``` 把gtid_mode=off和enforce_gtid_consistency=off写入my.cnf `Master/Slave` ```bash cat /etc/my.cnf ``` ```ini [mysqld] gtid_mode=on enforce_gtid_consistency=on ``` 验证传统复制是否成功 _1.插入数据到Master_ _2.Slave查看Executed_Gtid_Set值是否增加,如果值没变化,数据同步成功,说明传统复制配置成功_ &emsp; #### 传统复制切换GTID复制 主从同时修改参数enforce_gtid_consistency=warn `Master/Slave` ```sql mysql> set global enforce_gtid_consistency=warn; ``` 主从同时修改参数enforce_gtid_consistency=on `Master/Slave` ```sql mysql> set global enforce_gtid_consistency=on; ``` 主从同时配置GTID模式为off_permissive `Master/Slave` ```sql mysql> set global gtid_mode=off_permissive ``` 主从同时配置GTID模式为on_permissive `Master/Slave` ```sql mysql> set global gtid_mode=on_permissive ``` 确认从库的Ongoing_anonymous_transaction_count参数是否为0(为0,意味着没有等待的事务,可以直接进行下一步操作) `Slave` ```sql mysql> show global status like 'Ongoing_anonymous_transaction_count'; ``` ``` +-------------------------------------+-------+ | Variable_name | Value | +-------------------------------------+-------+ | Ongoing_anonymous_transaction_count | 0 | +-------------------------------------+-------+ ``` 主从同时配置gtid_mode=on `Master/Slave` ```sql mysql> set global gtid_mode=on; ``` 把传统模式改为GTID复制 `Slave` ```sql mysql> stop slave; mysql> change master to master_auto_position=1; mysql> start slave; ``` 验证GTID复制是否成功 _1.插入数据到Master_ _2.Slave查看Executed_Gtid_Set值是否增加,如果是则说明切换GTID复制成功_
阅读 6 评论 0 收藏 0
阅读 6
评论 0
收藏 0

   2018-08-05 11:05:07    2019-11-14 14:32:56   

mysql MHA
### 环境准备 系统: `CentOS7` 软件: `MySQL5.7` `MHA 0.56` VIP: `172.16.0.222` 服务器: `node1(Master节点)`: `172.16.0.100` `node2(SLave节点/Master备)`: `172.16.0.101` `node3(SLave节点/MHA manager)`: `172.16.0.102` &emsp; ### 初始化工作 **`a.三台机器SSH免密登陆`** **`b.三台机器的主机名和设置hosts文件解析`** **`c.两台SLAVE节点配置成MASTER节点的MySQL主从`** **`d.MySQL的MHA配置要求`** ```bash vim /etc/my.cnf ``` ```ini [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock symbolic-links=0 log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid bind-address = 0.0.0.0 #配置 server-id = 1 #节点的值唯一 log_bin = mysql-bin #MySQL Master或者备用Master binlog_ignore_db = mysql binlog_ignore_db = infomation_schema binlog_ignore_db = performance_schema #relay_log_purge = 0 #从库需要配置,执行完relay不删除 ``` `提示:MySQL Master或者备用Master需要打开binlog,MHA要求各个数据库节点的复制过滤规则(binlog-do-db, replicate-ignore-db)都一样` **`e. 开启半同步复制`** `所有节点都执行下面命令` ```sql mysql> install plugin rpl_semi_sync_master soname 'semisync_master.so'; #安装Master半同步复制插件 mysql> install plugin rpl_semi_sync_slave soname 'semisync_slave.so'; #安装Slave半同步复制插件 mysql> set global rpl_semi_sync_slave_enabled=on; #开启Slave半同步复制 mysql> set global rpl_semi_sync_master_enabled=on; #开启Master半同步复制 mysql> stop slave io_thread; mysql> start slave io_thread; mysql> show variables like '%semi%'; #查看半同步复制参数 mysql> show plugins; #查看加载的插件 ``` **`f. MySQL配置GTID复制(可选)`** 参考:https://ynotes.cn/blog/article_detail/203 传统复制和GTID复制转换 &emsp; ### 安装MHA 下载MHA Manager & MHA Node ```bash curl http://www.mysql.gr.jp/frame/modules/bwiki/index.php\?plugin\=attach\&pcmd\=open\&file\=mha4mysql-manager-0.56-0.el6.noarch.rpm\&refer\=matsunobu -o mha4mysql-manager-0.56-0.el6.noarch.rpm curl http://www.mysql.gr.jp/frame/modules/bwiki/index.php\?plugin\=attach\&pcmd\=open\&file\=mha4mysql-node-0.56-0.el6.noarch.rpm\&refer\=matsunobu -o mha4mysql-node-0.56-0.el6.noarch.rpm ``` 安装MHA Node `所有节点` ```bash yum localinstall -y mha4mysql-node-0.56-0.el6.noarch.rpm ``` 安装MHA Manager `node3(MHA manager节点)` ```bash yum localinstall -y mha4mysql-manager-0.56-0.el6.noarch.rpm ``` &emsp; ### 配置MHA管理节点 `node3(MHA manager节点)` 创建mha相关目录 ```bash mkdir -p /usr/local/mha mkdir -p /etc/mha ``` 创建mha.conf ```bash vim /etc/mha/mha.conf ``` ```ini [server default] manager_log=/var/log/mha/manager.log master_ip_failover_script=/etc/mha/scripts/master_ip_failover master_ip_online_change_script=/etc/mha/scripts/master_ip_failover # mysql user and password user=mha_admin password=123456 #ssh user ssh_user=root # working directory on the manager manager_workdir=/usr/local/mha # working directory on MySQL servers remote_workdir=/usr/local/mha repl_user=replica repl_password=123456 [server1] hostname=172.16.0.100 [server2] hostname=172.16.0.101 [server3] hostname=172.16.0.102 ``` 创建VIP切换脚本 ```bash vim /etc/mha/scripts/master_ip_failover ``` ```perl #!/usr/bin/env perl use strict; use warnings FATAL => 'all'; use Getopt::Long; my ( $command, $ssh_user, $orig_master_host, $orig_master_ip, $orig_master_port, $new_master_host, $new_master_ip, $new_master_port ); my $vip = '172.16.0.222/24'; # Virtual IP #设置VIP地址 my $key = "1"; my $ssh_start_vip = "/sbin/ifconfig eth1:$key $vip"; #针对网卡配置,如果使用的是eth0,则eth0:$key my $ssh_stop_vip = "/sbin/ifconfig eth1:$key down"; #针对网卡配置,如果使用的是eth0,则eth0:$key $ssh_user = "root"; GetOptions( 'command=s' => \$command, 'ssh_user=s' => \$ssh_user, 'orig_master_host=s' => \$orig_master_host, 'orig_master_ip=s' => \$orig_master_ip, 'orig_master_port=i' => \$orig_master_port, 'new_master_host=s' => \$new_master_host, 'new_master_ip=s' => \$new_master_ip, 'new_master_port=i' => \$new_master_port, ); exit &main(); sub main { print "\n\nIN SCRIPT TEST====$ssh_stop_vip==$ssh_start_vip===\n\n"; if ( $command eq "stop" || $command eq "stopssh" ) { # $orig_master_host, $orig_master_ip, $orig_master_port are passed. # If you manage master ip address at global catalog database, # invalidate orig_master_ip here. my $exit_code = 1; eval { print "Disabling the VIP on old master: $orig_master_host \n"; &stop_vip(); $exit_code = 0; }; if ($@) { warn "Got Error: $@\n"; exit $exit_code; } exit $exit_code; } elsif ( $command eq "start" ) { # all arguments are passed. # If you manage master ip address at global catalog database, # activate new_master_ip here. # You can also grant write access (create user, set read_only=0, etc) here. my $exit_code = 10; eval { print "Enabling the VIP - $vip on the new master - $new_master_host \n"; &start_vip(); $exit_code = 0; }; if ($@) { warn $@; exit $exit_code; } exit $exit_code; } elsif ( $command eq "status" ) { print "Checking the Status of the script.. OK \n"; `ssh $ssh_user\@cluster1 \" $ssh_start_vip \"`; exit 0; } else { &usage(); exit 1; } } # A simple system call that enable the VIP on the new master sub start_vip() { `ssh $ssh_user\@$new_master_host \" $ssh_start_vip \"`; } # A simple system call that disable the VIP on the old_master sub stop_vip() { `ssh $ssh_user\@$orig_master_host \" $ssh_stop_vip \"`; } sub usage { print "Usage: master_ip_failover --command=start|stop|stopssh|status --orig_master_host=host --orig_master_ip=ip --orig_master_port=port --new_master_host=host --new_master_ip=ip --new_master_port=port\n"; } ``` 创建复制用户(replica) `node1-2节点(Master节点/Master备)` ```sql mysql> GRANT REPLICATION SLAVE ON *.* TO 'replica'@'172.16.0.%' IDENTIFIED BY '123456'; mysql> FLUSH PRIVILEGES; ``` 创建管理用户(mha) `所有节点` ```sql mysql> GRANT ALL ON *.* TO 'mha'@'172.16.0.%' IDENTIFIED BY '123456'; mysql> FLUSH PRIVILEGES; ``` &emsp; ### Master配置VIP(第一次手动添加) `node1节点` ```bash /sbin/ifconfig eth1:1 172.16.0.222 ``` &emsp; ### 测试节点间的SSH登录 `node3(MHA manager节点)` ```bash masterha_check_ssh --conf=/etc/mha/mha.conf ``` ``` Mon Aug 5 03:17:00 2019 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping. Mon Aug 5 03:17:00 2019 - [info] Reading application default configuration from /etc/mha/mha.conf.. Mon Aug 5 03:17:00 2019 - [info] Reading server configuration from /etc/mha/mha.conf.. Mon Aug 5 03:17:00 2019 - [info] Starting SSH connection tests.. Mon Aug 5 03:17:01 2019 - [debug] Mon Aug 5 03:17:00 2019 - [debug] Connecting via SSH from root@172.16.0.100(172.16.0.100:22) to root@172.16.0.101(172.16.0.101:22).. Mon Aug 5 03:17:00 2019 - [debug] ok. Mon Aug 5 03:17:00 2019 - [debug] Connecting via SSH from root@172.16.0.100(172.16.0.100:22) to root@172.16.0.102(172.16.0.102:22).. Mon Aug 5 03:17:01 2019 - [debug] ok. Mon Aug 5 03:17:01 2019 - [debug] Mon Aug 5 03:17:00 2019 - [debug] Connecting via SSH from root@172.16.0.101(172.16.0.101:22) to root@172.16.0.100(172.16.0.100:22).. Mon Aug 5 03:17:01 2019 - [debug] ok. Mon Aug 5 03:17:01 2019 - [debug] Connecting via SSH from root@172.16.0.101(172.16.0.101:22) to root@172.16.0.102(172.16.0.102:22).. Mon Aug 5 03:17:01 2019 - [debug] ok. Mon Aug 5 03:17:02 2019 - [debug] Mon Aug 5 03:17:01 2019 - [debug] Connecting via SSH from root@172.16.0.102(172.16.0.102:22) to root@172.16.0.100(172.16.0.100:22).. Mon Aug 5 03:17:01 2019 - [debug] ok. Mon Aug 5 03:17:01 2019 - [debug] Connecting via SSH from root@172.16.0.102(172.16.0.102:22) to root@172.16.0.101(172.16.0.101:22).. Mon Aug 5 03:17:02 2019 - [debug] ok. Mon Aug 5 03:17:02 2019 - [info] All SSH connection tests passed successfully. ``` `上面显示ssh免密登陆配置成功` ### 检查mha配置 `node3(MHA manager节点)` ```bash masterha_check_repl --conf=/etc/mha/mha.conf ``` ``` Mon Aug 5 03:39:17 2019 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping. Mon Aug 5 03:39:17 2019 - [info] Reading application default configuration from /etc/mha/mha.conf.. Mon Aug 5 03:39:17 2019 - [info] Reading server configuration from /etc/mha/mha.conf.. Mon Aug 5 03:39:17 2019 - [info] MHA::MasterMonitor version 0.56. Mon Aug 5 03:39:18 2019 - [info] GTID failover mode = 0 Mon Aug 5 03:39:18 2019 - [info] Dead Servers: Mon Aug 5 03:39:18 2019 - [info] Alive Servers: Mon Aug 5 03:39:18 2019 - [info] 172.16.0.100(172.16.0.100:3306) Mon Aug 5 03:39:18 2019 - [info] 172.16.0.101(172.16.0.101:3306) Mon Aug 5 03:39:18 2019 - [info] 172.16.0.102(172.16.0.102:3306) Mon Aug 5 03:39:18 2019 - [info] Alive Slaves: Mon Aug 5 03:39:18 2019 - [info] 172.16.0.101(172.16.0.101:3306) Version=5.7.27-log (oldest major version between slaves) log-bin:enabled Mon Aug 5 03:39:18 2019 - [info] Replicating from 172.16.0.100(172.16.0.100:3306) Mon Aug 5 03:39:18 2019 - [info] 172.16.0.102(172.16.0.102:3306) Version=5.7.27-log (oldest major version between slaves) log-bin:enabled Mon Aug 5 03:39:18 2019 - [info] Replicating from 172.16.0.100(172.16.0.100:3306) Mon Aug 5 03:39:18 2019 - [info] Current Alive Master: 172.16.0.100(172.16.0.100:3306) Mon Aug 5 03:39:18 2019 - [info] Checking slave configurations.. Mon Aug 5 03:39:18 2019 - [info] read_only=1 is not set on slave 172.16.0.101(172.16.0.101:3306). Mon Aug 5 03:39:18 2019 - [warning] relay_log_purge=0 is not set on slave 172.16.0.101(172.16.0.101:3306). Mon Aug 5 03:39:18 2019 - [info] read_only=1 is not set on slave 172.16.0.102(172.16.0.102:3306). Mon Aug 5 03:39:18 2019 - [warning] relay_log_purge=0 is not set on slave 172.16.0.102(172.16.0.102:3306). Mon Aug 5 03:39:18 2019 - [info] Checking replication filtering settings.. Mon Aug 5 03:39:18 2019 - [info] binlog_do_db= , binlog_ignore_db= infomation_schema,mysql,performance_schema Mon Aug 5 03:39:18 2019 - [info] Replication filtering check ok. Mon Aug 5 03:39:18 2019 - [info] GTID (with auto-pos) is not supported Mon Aug 5 03:39:18 2019 - [info] Starting SSH connection tests.. Mon Aug 5 03:39:20 2019 - [info] All SSH connection tests passed successfully. Mon Aug 5 03:39:20 2019 - [info] Checking MHA Node version.. Mon Aug 5 03:39:21 2019 - [info] Version check ok. Mon Aug 5 03:39:21 2019 - [info] Checking SSH publickey authentication settings on the current master.. Mon Aug 5 03:39:21 2019 - [info] HealthCheck: SSH to 172.16.0.100 is reachable. Mon Aug 5 03:39:21 2019 - [info] Master MHA Node version is 0.56. Mon Aug 5 03:39:21 2019 - [info] Checking recovery script configurations on 172.16.0.100(172.16.0.100:3306).. Mon Aug 5 03:39:21 2019 - [info] Executing command: save_binary_logs --command=test --start_pos=4 --binlog_dir=/var/lib/mysql,/var/log/mysql --output_file=/usr/local/mha/save_binary_logs_test --manager_version=0.56 --start_file=mysql-bin.000008 Mon Aug 5 03:39:21 2019 - [info] Connecting to root@172.16.0.100(172.16.0.100:22).. Creating /usr/local/mha if not exists.. ok. Checking output directory is accessible or not.. ok. Binlog found at /var/lib/mysql, up to mysql-bin.000008 Mon Aug 5 03:39:22 2019 - [info] Binlog setting check done. Mon Aug 5 03:39:22 2019 - [info] Checking SSH publickey authentication and checking recovery script configurations on all alive slave servers.. Mon Aug 5 03:39:22 2019 - [info] Executing command : apply_diff_relay_logs --command=test --slave_user='mha_admin' --slave_host=172.16.0.101 --slave_ip=172.16.0.101 --slave_port=3306 --workdir=/usr/local/mha --target_version=5.7.27-log --manager_version=0.56 --relay_log_info=/var/lib/mysql/relay-log.info --relay_dir=/var/lib/mysql/ --slave_pass=xxx Mon Aug 5 03:39:22 2019 - [info] Connecting to root@172.16.0.101(172.16.0.101:22).. Checking slave recovery environment settings.. Opening /var/lib/mysql/relay-log.info ... ok. Relay log found at /var/lib/mysql, up to db2-relay-bin.000002 Temporary relay log file is /var/lib/mysql/db2-relay-bin.000002 Testing mysql connection and privileges..mysql: [Warning] Using a password on the command line interface can be insecure. done. Testing mysqlbinlog output.. done. Cleaning up test file(s).. done. Mon Aug 5 03:39:22 2019 - [info] Executing command : apply_diff_relay_logs --command=test --slave_user='mha_admin' --slave_host=172.16.0.102 --slave_ip=172.16.0.102 --slave_port=3306 --workdir=/usr/local/mha --target_version=5.7.27-log --manager_version=0.56 --relay_log_info=/var/lib/mysql/relay-log.info --relay_dir=/var/lib/mysql/ --slave_pass=xxx Mon Aug 5 03:39:22 2019 - [info] Connecting to root@172.16.0.102(172.16.0.102:22).. Checking slave recovery environment settings.. Opening /var/lib/mysql/relay-log.info ... ok. Relay log found at /var/lib/mysql, up to db3-relay-bin.000002 Temporary relay log file is /var/lib/mysql/db3-relay-bin.000002 Testing mysql connection and privileges..mysql: [Warning] Using a password on the command line interface can be insecure. done. Testing mysqlbinlog output.. done. Cleaning up test file(s).. done. Mon Aug 5 03:39:22 2019 - [info] Slaves settings check done. Mon Aug 5 03:39:22 2019 - [info] 172.16.0.100(172.16.0.100:3306) (current master) +--172.16.0.101(172.16.0.101:3306) +--172.16.0.102(172.16.0.102:3306) Mon Aug 5 03:39:22 2019 - [info] Checking replication health on 172.16.0.101.. Mon Aug 5 03:39:22 2019 - [info] ok. Mon Aug 5 03:39:22 2019 - [info] Checking replication health on 172.16.0.102.. Mon Aug 5 03:39:22 2019 - [info] ok. Mon Aug 5 03:39:22 2019 - [info] Checking master_ip_failover_script status: Mon Aug 5 03:39:22 2019 - [info] /etc/mha/scripts/master_ip_failover --command=status --ssh_user=root --orig_master_host=172.16.0.100 --orig_master_ip=172.16.0.100 --orig_master_port=3306 IN SCRIPT TEST====/sbin/ifconfig eth1:1 down==/sbin/ifconfig eth1:1 172.16.0.222/24=== Checking the Status of the script.. OK ssh: Could not resolve hostname cluster1: Name or service not known Mon Aug 5 03:39:22 2019 - [info] OK. Mon Aug 5 03:39:22 2019 - [warning] shutdown_script is not defined. Mon Aug 5 03:39:22 2019 - [info] Got exit code 0 (Not master dead). MySQL Replication Health is OK. ``` `MySQL Replication Health is OK.显示配置正确!` ### 启动MHA Manager ```bash nohup masterha_manager --conf=/etc/mha/mha.conf & ``` ### 验证是否启动成功 ```bash masterha_check_status --conf=/etc/mha/mha.conf ``` ``` mha (pid:27057) is running(0:PING_OK), master:172.16.0.100 ``` ### 停止MHA Manager(`不执行`) ```bash masterha_stop --conf=/etc/mha/mha.conf ``` &emsp; ### 测试 #### 测试故障转移 **停止node1节点的MySQL** ```bash systemctl stop mysqld ``` **node2查看VIP** ```bash ip a ``` ``` ... 3: eth1: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1450 qdisc pfifo_fast state UP group default qlen 1000 link/ether 5a:00:02:32:0f:46 brd ff:ff:ff:ff:ff:ff inet 172.16.0.101/16 brd 172.16.255.255 scope global eth1 valid_lft forever preferred_lft forever inet 172.16.0.222/24 brd 172.16.0.255 scope global eth1:1 valid_lft forever preferred_lft forever inet6 fe80::5800:2ff:fe32:f46/64 scope link valid_lft forever preferred_lft forever ``` `通过上面的信息可以看到VIP已经转移到node2` **查看是否为slave** ```sql mysql> show slave status\G #没有输出,说明node2已经为非slave节点 Empty set (0.00 sec) ``` **查看node3的slave信息** ```sql mysql> show slave status\G ``` ``` *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 172.16.0.101 Master_User: replica Master_Port: 3306 Connect_Retry: 60 ... ``` `通过上面的信息可以确定故障转移成功,master已经变成node2节点,node3节点已经同步到新的slave` &emsp; #### 旧master恢复的操作 `node1数据库恢复之后,首先设置为node2的slave,之后启动MHA Manager` 启动MySQL `node1` ```bash systemctl start mysqld ``` 配置node1为node2的slave _MHA节点查看同步信息_ `node3` ```bash grep "CHANGE MASTER TO" /var/log/mha/manager.log |tail -1 ``` ```bash Mon Aug 5 03:32:28 2019 - [info] All other slaves should start replication from here. Statement should be: CHANGE MASTER TO MASTER_HOST='172.16.0.101', MASTER_PORT=3306, MASTER_LOG_FILE='mysql-bin.000008', MASTER_LOG_POS=154, MASTER_USER='replica', MASTER_PASSWORD='xxx'; ``` _node1配置同步node2_ `node1` ```sql mysql> CHANGE MASTER TO MASTER_HOST='172.16.0.101', MASTER_PORT=3306, MASTER_LOG_FILE='mysql-bin.000008', MASTER_LOG_POS=154, MASTER_USER='replica', MASTER_PASSWORD='123456'; mysql> start slave; #启动slave mysql> show slave status\G ``` ``` *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 172.16.0.101 Master_User: replica Master_Port: 3306 Connect_Retry: 60 ... ``` 把 Master切回到原理的node1 `node3` ```bash masterha_master_switch --conf=/etc/mha/mha.conf --master_state=alive --new_master_host=172.16.0.100 --orig_master_is_new_slave ``` 检查mha配置 `node3` ```bash masterha_check_repl --conf=/etc/mha/mha.conf ``` ``` ... MySQL Replication Health is OK. ``` 启动MHA Manager `node3` ```bash nohup masterha_manager --conf=/etc/mha/mha.conf & ``` 参考: http://www.fblinux.com/?p=1018 https://joelhy.github.io/2015/02/06/mysql-mha/
阅读 10 评论 0 收藏 0
阅读 10
评论 0
收藏 0

第 3 页 / 共 7 页
 
第 3 页 / 共 7 页