peng - APE

Elasticsearch, logstash, MySQL 11 月 24,2022

logstash 配置jdbc数据源 output 到 Elasticsearch

input {
  jdbc {
    # MySQ驱动配置
    jdbc_driver_library => "D:\apps\logstash-7.14.0\config\mysql-connector-java-5.1.42.jar"
    jdbc_driver_class => "com.mysql.jdbc.Driver"
    jdbc_connection_string => "jdbc:mysql://localhost:3306/db_name?characterEncoding=UTF-8&useSSL=false"
    jdbc_user => root
    jdbc_password => root
    #是否进行分页
    #jdbc_paging_enabled => "false"
    #jdbc_page_size => "5000"
    #增量查询参考字段（SQL中 :sql_last_value 变量会替换为该值）
    tracking_column => "vr_id"
    tracking_column_type => "numeric"
    #这个文件需要自己创建出来 作用就是记录tracking_column的值（重启不会重置） 下次执行同步数据库的操作的时候直接从这个标识查起，文件名随意起
    last_run_metadata_path => "D:\apps\logstash-7.14.0\config\last_run_metadata\textrepair.txt"
    use_column_value => true
    # statement_filepath => "sql文件路径，与下面的执行语句二选1"
    statement => "SELECT * FROM table_name WHERE vr_id > :sql_last_value"
    # 设置监听间隔  各字段含义（由左至右）秒、分、时、天、月、年，全部为*默认含义为每分钟都更新
    # "*/5 * * * * *"  5秒钟执行一次
    schedule => "*/5 * * * * *"
  }
  
  jdbc {
    jdbc_driver_library => "D:\apps\logstash-7.14.0\config\mysql-connector-java-5.1.42.jar"
    jdbc_driver_class => "com.mysql.jdbc.Driver"
    jdbc_connection_string => "jdbc:mysql://localhost:3306/db_name?characterEncoding=UTF-8&useSSL=false"
    jdbc_user => root
    jdbc_password => root
    #是否进行分页
    #jdbc_paging_enabled => "false"
    #jdbc_page_size => "5000"
    #增量查询参考字段（SQL中 :sql_last_value 变量会替换为该值）
    tracking_column => "vr_id"
    tracking_column_type => "numeric"
    #这个文件需要自己创建出来 作用就是记录tracking_column的值（重启不会重置） 下次执行同步数据库的操作的时候直接从这个标识查起，文件名随意起
    last_run_metadata_path => "D:\apps\logstash-7.14.0\config\last_run_metadata\videorepair.txt"
    use_column_value => true
    # statement_filepath => "sql文件路径，与下面的执行语句二选1"
    statement => "SELECT * FROM table_name WHERE vr_id > :sql_last_value"
    # 设置监听间隔  各字段含义（由左至右）秒、分、时、天、月、年，全部为*默认含义为每分钟都更新
    schedule => "*/5 * * * * *"
  }
  
  jdbc {
    #如果配置多个数据源需要用type来区分
    type => "schematic_diagram"
    jdbc_driver_library => "D:\apps\logstash-7.14.0\config\mysql-connector-java-5.1.42.jar"
    jdbc_driver_class => "com.mysql.jdbc.Driver"
    jdbc_connection_string => "jdbc:mysql://localhost:3306/db_name?characterEncoding=UTF-8&useSSL=false"
    jdbc_user => root
    jdbc_password => root
    #是否进行分页
    #jdbc_paging_enabled => "false"
    #jdbc_page_size => "5000"
    #增量查询参考字段（SQL中 :sql_last_value 变量会替换为该值）
    tracking_column => "product_id"
    tracking_column_type => "numeric"
    #这个文件需要自己创建出来 作用就是记录tracking_column的值（重启不会重置） 下次执行同步数据库的操作的时候直接从这个标识查起，文件名随意起
    last_run_metadata_path => "D:\apps\logstash-7.14.0\config\last_run_metadata\schematic_diagram.txt"
    use_column_value => true
    # statement_filepath => "sql文件路径，与下面的执行语句二选1"
    statement => "SELECT * FROM table_name WHERE product_id > :sql_last_value"
    # 设置监听间隔  各字段含义（由左至右）秒、分、时、天、月、年，全部为*默认含义为每分钟都更新
    schedule => "*/5 * * * * *"
  }
  
  jdbc {
    #如果配置多个数据源需要用type来区分
    type => "program"
    jdbc_driver_library => "D:\apps\logstash-7.14.0\config\mysql-connector-java-5.1.42.jar"
    jdbc_driver_class => "com.mysql.jdbc.Driver"
    jdbc_connection_string => "jdbc:mysql://localhost:3306/db_name?characterEncoding=UTF-8&useSSL=false"
    jdbc_user => root
    jdbc_password => root
    #是否进行分页
    #jdbc_paging_enabled => "false"
    #jdbc_page_size => "5000"
    #增量查询参考字段（SQL中 :sql_last_value 变量会替换为该值）
    tracking_column => "program_id"
    tracking_column_type => "numeric"
    #这个文件需要自己创建出来 作用就是记录tracking_column的值（重启不会重置） 下次执行同步数据库的操作的时候直接从这个标识查起，文件名随意起
    last_run_metadata_path => "D:\apps\logstash-7.14.0\config\last_run_metadata\program.txt"
    use_column_value => true
    # statement_filepath => "sql文件路径，与下面的执行语句二选1"
    statement => "SELECT * FROM table_name WHERE program_id> :sql_last_value"
    # 设置监听间隔  各字段含义（由左至右）秒、分、时、天、月、年，全部为*默认含义为每分钟都更新
    schedule => "*/5 * * * * *"
  }
}

output {
  if[type]=="text"{
    elasticsearch {
      document_id => "%{vr_id}"
      index => "knowledge_textrepaires"
      hosts => ["localhost:9200"]
      template_overwrite => true
    }
  }
  
  if[type]=="video"{
    elasticsearch {
      document_id => "%{vr_id}"
      index => "knowledge_videorepaires"
      hosts => ["localhost:9200"]
      template_overwrite => true
    }
  }
  
  if[type]=="schematic_diagram"{
    elasticsearch {
      document_id => "%{product_unique_code}"
      index => "knowledge_schematicdiagrames"
      hosts => ["localhost:9200"]
      template_overwrite => true
    }
  }
  
  if[type]=="program"{
    elasticsearch {
      document_id => "%{program_id}"
      index => "knowledge_programes"
      hosts => ["localhost:9200"]
      template_overwrite => true
    }
  }
  
  # 这里输出调试，正式运行时可以注释掉
  stdout{
    codec => rubydebug
  }
}

注意事项

如果SQL查询结果中存在type字段output中根据type控制输出位置使用的是查询返回的type，此时配置文件中的type无效，展现出来的效果会误认为通过if控制输出位置无效

运行环境

logstash 7.14.0
Elasticsearch 7.14.0
MySQL 5.7

作者 peng

MySQL 12 月 11,2020

MySQL SQL性能分析Explain执行计划

一. 执行计划返回信息详解

①. 执行计划所含字段

输出列	含义
id	查询标识
select_type	查询类型
table	查询涉及的表
partitions	匹配到的分区信息
type	连接类型
possible_keys	可能选择的索引
key	实际使用的索引
key_len	实际使用的索引的长度
ref	和索引进行比较的列
rows	需要被检索的大致行数
filtered	按表条件过滤的行百分比
Extra	额外信息

②. id

执行计划序列号，可出现3种组合方式

第一种：id全部相同，这时会从上至下依次执

第二种：部分id相同，这时会按照id大小从大到小执行；id相同的则会被认定为是一组执行计划，同一组遵循第一种id全相同的顺序执行

UNION RESULT：UNION的结果

第三种：id全部不相同，这时会按照id大小从大到小执行

③. select_type 引用（懒得写）：https://blog.csdn.net/qq_39885372/article/details/104173941 CC 4.0 BY-SA

SIMPLE：简单的select查询，查询中不包含子查询或者UNION
PRIMARY：查询中若包含任何复杂的子部分，最外层查询则被标记为
SUBQUERY：在SELECT或WHERE列表中包含了子查询
DERIVED：在FROM列表中包含的子查询被标记为DERIVED（衍生）MYSQL会递归执行这些子查询，把结果放在临时表里
UNION：若第二个SELECT出现在UNION之后，则被标记为UNION若UNION包含在FROM子句的子查询中，外层SELECT将被标记为：DERIVED
UNION RESULT：从UNION表获取结果的SELECT

④. table

查询涉及的表

⑤. partitions

由查询匹配记录的分区。非分区表为NULL

⑥. type 引用：https://blog.csdn.net/lilongsy/article/details/9518459 CC 4.0 BY-SA

链接类型	说明
`system`	表只有一行，`MyISAM`引擎。
`const`	常量连接，表最多只有一行匹配，通用用于主键或者唯一索引比较时
`eq_ref`	每次与之前的表合并行都只在该表读取一行，这是除了system，const之外最好的一种，特点是使用=，而且索引的所有部分都参与join且索引是主键或非空唯一键的索引
`ref`	如果每次只匹配少数行，那就是比较好的一种，使用=或<=>，可以是左覆盖索引或非主键或非唯一键
`fulltext`	全文搜索
`ref_or_null`	与`ref`类似，但包括`NULL`
`index_merge`	表示出现了索引合并优化(包括交集，并集以及交集之间的并集)，但不包括跨表和全文索引。这个比较复杂，目前的理解是合并单表的范围索引扫描（如果成本估算比普通的range要更优的话）
`unique_subquery`	在in子查询中，就是value in (select…)把形如`select unique_key_column`的子查询替换。PS：所以不一定in子句中使用子查询就是低效的！
`index_subquery`	同上，但把形如”select non_unique_key_column“的子查询替换
`range`	常数值的范围
`index`	索引树扫描。a.当查询是索引覆盖的，即所有数据均可从索引树获取的时候（Extra中有Using Index）；b.以索引顺序从索引中查找数据行的全表扫描（无 Using Index）；c.如果Extra中Using Index与Using Where同时出现的话，则是利用索引查找键值的意思；d.如单独出现，则是用读索引来代替读行，但不用于查找
`all`	全表扫描(full table scan)

由上到下性能最好到最差

⑦. possible_keys

列出查询涉及到的字段的索引，但不一定会被使用

⑧. key

实际使用的索引，如果为NULL，则没有使用索引

⑨. key_len

表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度，在不损失精确性的情况下，长度越短越好。key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的。

⑩. ref

和前方表连接的字段,const代表是常量值连接

⑪. rows

查询大致需要涉及读取的行数，越少越好

⑫. extra

包含不适合在其他列中显式但十分重要的额外信息（暂不展开）

作者 peng

MySQL 12 月 8,2020

CentOS 7安装MySQL8.0配置主从复制

一.下载安装MySQL

下载地址：https://dev.mysql.com/downloads/mysql/

按照图中选择下载适用与CentOS7 64位MySQL安装包

解压安装包压缩文件

tar -xf mysql-8.0.22-1.el8.x86_64.rpm-bundle.tar

解压后看到下面图中必要文件

按照顺序执行安装命令安装MySQL

1. rpm -ivh mysql-community-common-8.0.22-1.el8.x86_64.rpm
2. rpm -ivh mysql-community-client-plugins-8.0.22-1.el7.x86_64.rpm
3. rpm -ivh mysql-community-libs-8.0.22-1.el7.x86_64.rpm
4. rpm -ivh mysql-community-client-8.0.22-1.el7.x86_64.rpm
5. rpm -ivh mysql-community-server-8.0.22-1.el7.x86_64.rpm

启动MySQL

启动MySQL：systemctl start mysqld
停止MySQL：systemctl stop mysqld
重启MySQL：systemctl restart mysqld

查看MySQL默认密码

cat /var/log/mysqld.log | grep password

修改root密码为root

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'root';

如需远程访问执行下列命令添加root用户远程访问

create user 'root'@'%' identified with mysql_native_password by 'root';
grant all privileges on *.* to 'root'@'%' with grant option;
flush privileges;

如果不能远程连接需检查firewall防火墙是否开启，可直接关闭firewall

systemctl stop firewalld.service;
systemctl disable firewalld.service;

二. 配置主节点Master

编辑主服务器Master my.cnf配置文件

nano /etc/my.cnf

加入以下内容

[mysqld]
log-bin=mysql-bin   //启用二进制日志(必填)
server-id=1         //唯一ID，一般取IP最后

编辑从服务器Slave my.cnf配置文件

[mysqld]
log-bin=mysql-bin   //启用二进制日志(选填)
server-id=2         //唯一ID，一般取IP最后（不能重复）

在主节点创建一个用户sync，从服务器同步数据使用

mysql> CREATE USER 'sync'@'192.168.89.5' IDENTIFIED WITH mysql_native_password BY 'Qa1256>!';
mysql> GRANT REPLICATION SLAVE ON *.* TO 'sync'@'192.168.89.5';
mysql> flush privileges;

获取主节点当前mysql-bin.log文件名和位置（Position）

mysql> SHOW MASTER STATUS;

在从节点（Slave）设置主节点参数

mysql> CHANGE MASTER TO
MASTER_HOST='192.168.89.4',
MASTER_USER='sync',
MASTER_PASSWORD='Qa1256>!',
MASTER_LOG_FILE='mysql-bin.000003',
MASTER_LOG_POS=156;

开启同步

mysql> start slave;

查看主从同步状态

mysql> show slave status\G;

查看Master/Slave服务器数据库（创建一个新数据库测试同步）

mysql> show databases;

Master/Slave节点数据一致

创建测试数据库

mysql> create database sync_test charset='utf8';

Master节点

查看Slave节点数据库

Slave节点

三. 配置过程遇到问题

1. 由于是克隆虚拟机导致主从节点UUID相同，解决办法

错误信息
The slave I/O thread stops because master and slave have equal MySQL server UUIDs; these UUIDs must be different for replication to work.

删除一台数据库的auto.cnf后重启MySQL

rm -rf /var/lib/mysql/auto.cnf
systemctl restart mysqld

作者 peng

All posts by peng