Mysql tools中的orzdba举例分析

本篇内容主要讲解“Mysql tools中的orzdba举例分析”，感兴趣的朋友不妨来看看。本文介绍的方法操作简单快捷，实用性强。下面就让小编来带大家学习“Mysql tools中的orzdba举例分析”吧!

mysql工具集：orzdba
修改代码160行左右配置MySQL的相关验证信息,如username,password,host，port，sock等
安装tcprstat
安装orzdba_rt_depend_perl_module.tar.gz依赖包（version模块、Class-Data-Inheritable模块、Module-Build模块、File-Lockfile模块）

cd Perl_Module/
perl Makefile.PL
make
make install

cd Module-Build-0.31
perl  Makefile.PL
make
make install

cd File-Lockfile-v1.0.5
perl Build.PL
perl ./Build
perl ./Build test
perl ./Build install

cd version-0.99
perl  Makefile.PL
make
make install

mv orzdba.txt orzdba
./orzdba.sh --help

grep -n 'my $MYSQL' orzdba
root@pts/1 # 修改代码：
160 my $MYSQL = qq{mysql -s –skip-column-names -uroot -P$port };
161 $MYSQL .= qq{-S$socket } if defined $socket;
162 my $TCPRSTAT = “/usr/bin/tcprstat –no-header -t 1 -n 0 -p $port”;

修改为：

这个步骤可以不用，主要是因为有密码报警
160 my $MYSQL = qq{mysql -s –skip-column-names -uroot -P$port -h227.0.0.1 -p12345678 };
161 $MYSQL .= qq{-S$socket } if defined $socket;
162 my $TCPRSTAT = “/usr/bin/tcprstat –no-header -t 1 -n 0 -p $port”;

报错处理：
ERROR 1054 (42S22) at line 1: Unknown column 'Com_select' in 'where clause'

修改代码如下：

 -e 'show variables where Variable_name in ("sync_binlog","max_connections","max_user_connections","max_connect_errors","table_open_cache","table_definition_cache","thread_cache_size","binlog_format","open_files_limit","max_binlog_size","max_binlog_cache_size")'

改成：
 -e "show variables where Variable_name in ('sync_binlog','max_connections','max_user_connections','max_connect_errors','table_open_cache','table_definition_cache','thread_cache_size','binlog_format','open_files_limit','max_binlog_size','max_binlog_cache_size')"

错误代码
-e 'show variables where Variable_name in ("innodb_flush_log_at_trx_commit","innodb_flush_method","innodb_buffer_pool_size","innodb_max_dirty_pages_pct","innodb_log_buffer_size","innodb_log_fil        e_size","innodb_log_files_in_group","innodb_thread_concurrency","innodb_file_per_table","innodb_adaptive_hash_index","innodb_open_files","innodb_io_capacity","innodb_read_io_threads","innodb_write_io_threads","innodb_adaptive_fl        ushing","innodb_lock_wait_timeout","innodb_log_files_in_group")'

改成
 -e "show variables where Variable_name in ('innodb_flush_log_at_trx_commit','innodb_flush_method','innodb_buffer_pool_size','innodb_max_dirty_pages_pct','innodb_log_buffer_size','innodb_log_fil        e_size','innodb_log_files_in_group','innodb_thread_concurrency','innodb_file_per_table','innodb_adaptive_hash_index','innodb_open_files','innodb_io_capacity','innodb_read_io_threads','innodb_write_io_threads','innodb_adaptive_fl        ushing','innodb_lock_wait_timeout','innodb_log_files_in_group')"

错误代码
 -e 'show global status where Variable_name in ("Com_select","Com_insert","Com_update","Com_delete","Innodb_buffer_pool_read_requests","Innodb_buffer_pool_reads","Innodb_rows_inserted","Innodb_rows_updated","Innodb_rows_deleted","Innodb_rows_read","Threads_running","Threads_connected","Threads_cached","Threads_created","Bytes_received","Bytes_sent","Innodb_buffer_pool_pages_data","Innodb_buffer_pool_pages_free","Innodb_buffer_pool_pages_dirty","Innodb_buffer_pool_pages_flushed","Innodb_data_reads","Innodb_data_writes","Innodb_data_read","Innodb_data_written","Innodb_os_log_fsyncs","Innodb_os_log_written")'

 改成
 -e "show global status where Variable_name in ('Com_select','Com_insert','Com_update','Com_delete','Innodb_buffer_pool_read_requests','Innodb_buffer_pool_reads','Innodb_rows_inserted','Innodb_rows_updated','Innodb_rows_deleted','Innodb_rows_read','Threads_running','Threads_connected','Threads_cached','Threads_created','Bytes_received','Bytes_sent','Innodb_buffer_pool_pages_data','Innodb_buffer_pool_pages_free','Innodb_buffer_pool_pages_dirty','Innodb_buffer_pool_pages_flushed','Innodb_data_reads','Innodb_data_writes','Innodb_data_read','Innodb_data_written','Innodb_os_log_fsyncs','Innodb_os_log_written')"
 
去掉密码告警信息
Warning: Using a password on the command line interface can be insecure.

创建加密密码：
mysql_config_editor set --user=root --password
ls -alh .my*
mysql_config_editor print --all
或
cd root
[root@server11 ~]# more .my.cnf
[client]
user=root
password=123456

 查看Linux主机指标
 ./orzdba -sys -C 10 -i 1 -t -d sda3

-sys：打印系统信息，包括-t(打印当前时间)、-l(打印负载信息，分1分钟、5分钟、15分钟)、-c(打印cpu信息)、-s(打印交换分区信息)
-d：打印磁盘信息，需要指点磁盘设备名
-n：打印网络信息，接收和发送大小，需要指点网卡设备名

查看MySQL响应时间(rt)
通过调用tcprstat来监控MySQL的响应时间。
./orzdba -rt -C 10 -i 1 -t -d sda

监控InnoDB的性能指标
./orzdba -innodb -C 10 -i 1

监控MySQL Server性能
./orzdba -mysql -C 10 -i 1

懒人模式：
nohup ./orzdba -lazy -d sda -C 5 -i 2 -L /tmp/orzdba.log  > /dev/null 2>&1 &
-----------------
orzdba报表

1、运行orzdba 收集mysql相关性能数据，生成log.xxxx-xx-xx收集数据文件

这边我对orzdba稍微做了些修改，为了获得更加连续的数据

原：if ( $mycount%15 == 0 ) {
修改 193 行为 if ( $mycount%100000000 == 0 ) {

2、使用orzdba_report.py分析orzdba.log.xxxx-xx-xx文件生成报表

orzdba_report.py源码：orzdba_report

到此，相信大家对“Mysql tools中的orzdba举例分析”有了更深的了解，不妨来实际操作一番吧！这里是亿速云网站，更多相关内容可以进入相关频道进行查询，关注我们，继续学习！