mysql相关

Posted on by liujie

使用脚本编写mysql被zabbix-server监控项信息

编写脚本mysql_monitor.sh
要求1、输入qps可以输出mysql 增删改查次数 / mysql服务启动时间的值
要求2、输入replication 可以输出主从复制状态，0正常，1异常，如果没有主从配置，返回2
要求3、输出process，输出当前正在运行的sql语句数量
要求4、输入time，找到当前执行时间大于10秒的所有sql

vim mysql_monitor.sh
#!/bin/bash
#输入qps可以输出mysql 增删改查次数 / mysql服务启动时间的值
mysql_user=root
mysql_password=Qianfeng@123
mysql_qps(){
 select=$(mysql -u${mysql_user} -p${mysql_password} -N -e "show global status like 'com_select'" 2>/dev/null| awk '{print $2}')
 uptime=$(mysql -u${mysql_user} -p${mysql_password} -N -e "show global status like 'uptime'" 2>/dev/null| awk '{print $2}')
 echo mysql每秒查询速率qps=$(echo "scale=2;$select / $uptime" | bc)
}
mysql_qps
#输入replication 可以输出主从复制状态，0正常，1异常，如果没有主从配置，返回2
mysql_slave(){
 IO=$(mysql -u${mysql_user} -p${mysql_password} -e "show slave status\G" 2>/dev/null| awk '/Slave_IO_Running:/{print $2}')
 SQL=$(mysql -u${mysql_user} -p${mysql_password} -e "show slave status\G" 2>/dev/null| awk '/Slave_SQL_Running:/{print $2}')
 thread="${IO} ${SQL}"
 for i in $thread
 do
  if [ "$i" = "Yes" ];then
   echo 0
   elif [ "$i" = "No" ];then
   echo 1
  else
   echo 2
  fi
 done
}
mysql_slave
#输出process，输出当前正在运行的sql语句数量
mysql_sql_number(){
 sql=$(mysql -u${mysql_user} -p${mysql_password} -N -e "show processlist" 2>/dev/null | awk '{print $8}'|wc -l)
 echo 正在运行的sql语句数量$sql
}
mysql_sql_number
#输入time，找到当前执行时间大于10秒的所有sql
mysql_time(){
 sql=$(mysql -u${mysql_user} -p${mysql_password} -N -e "show processlist" 2>/dev/null | awk '{print $6}')
 for i in $sql
 do
  if [ $i -gt 60 ];then
   echo $i
  fi
 done
}
mysql_time

mysql都有哪些数据备份方式，优缺点是什么

当谈到MySQL数据备份时，更详细的讨论通常涉及备份工具、备份策略和恢复过程。以下是一些备份方案的详细信息：

1. 物理备份（Physical Backup）：

   - 工具：使用工具如Percona XtraBackup 或 MySQL Enterprise Backup 来创建物理备份。
   - 备份过程： 备份包括MySQL数据目录（通常是/var/lib/mysql）的副本。可以在数据库运行时进行备份，也可以通过停止MySQL服务来确保一致性。
   - 恢复过程： 将备份的数据文件复制回原始位置，然后启动MySQL服务。这种方式相对快速，适用于大型数据库。

2. 逻辑备份（Logical Backup）：

   - 工具： mysqldump 是最常见的逻辑备份工具，它以SQL语句的形式导出数据库结构和数据。
   - 备份过程： mysqldump 将数据库导出到SQL文件中，可以选择备份整个数据库或特定表。
   - 恢复过程：使用 mysql 命令或类似工具将 SQL 文件导入到新的MySQL实例中。逻辑备份适用于小到中等大小的数据库。
3. 全量备份、增量备份和差异备份：

   - 全量备份： 对所有数据进行备份。
   - 增量备份： 使用工具如 mysqldump，但只备份自上次备份以来发生变化的数据。
   - 差异备份： 每次备份都包含自上次完整备份以来的所有变化。
   - 优缺点： 增量备份需要多个备份文件进行恢复，但占用的空间相对较小。差异备份相对较大，但恢复时只需要最近的完整备份和最近的差异备份。

mysql部分优化参数

innodb_buffer_pool_size：
作用： 指定InnoDB存储引擎的缓冲池大小，用于缓存表数据和索引。
使用场景： 应设置为足够大，以容纳常用的数据，减少磁盘I/O。

key_buffer_size：
作用： 用于缓存MyISAM存储引擎的索引数据。
使用场景： 对于使用MyISAM引擎的表，可以增大该参数以提高索引访问性能。

query_cache_size：
作用： 缓存查询结果，避免重复执行相同的查询。
使用场景： 在某些情况下，启用查询缓存可以提高性能，但在高并发写入的场景下可能会成为性能瓶颈。

innodb_log_file_size 和 innodb_log_buffer_size：
作用： 控制InnoDB事务日志的大小和缓冲区大小。
使用场景： 增大事务日志文件大小可以减少频繁的日志切换，提高性能。适当调整缓冲区大小可以优化写入性能。

innodb_flush_log_at_trx_commit：
作用： 控制事务日志的刷新策略。
使用场景： 将其设置为0可以提高性能，但会增加数据损失的风险。设置为1是默认值，保证每个事务的日志在提交时都被刷新，以保证数据持久性。

innodb_file_per_table：
作用： 控制InnoDB是否将每个表的数据存储在单独的文件中。
使用场景： 在创建大量小表的数据库时，将其设置为ON可以更好地管理磁盘空间。

max_connections：
作用： 指定MySQL服务器允许的最大连接数。
使用场景： 根据应用程序的并发连接需求，设置一个合适的值，避免过多的连接导致资源竞争。

innodb_flush_method：
作用： 指定InnoDB数据和日志文件的刷新方式。
使用场景： 根据操作系统和存储设备的不同，选择合适的刷新方式，以提高性能。

sort_buffer_size 和 read_rnd_buffer_size：
作用： 控制排序和随机读取操作的缓冲区大小。
使用场景： 适当调整这些参数可以改善相关查询的性能。

innodb_stats_on_metadata：
作用： 控制是否在执行SHOW TABLE STATUS等元数据查询时更新InnoDB统计信息。
使用场景： 在大型数据库中，禁用该选项可以减轻元数据查询的负担。

Related Posts