加入收藏 | 设为首页 | 会员中心 | 我要投稿 源码网 (https://www.900php.com/)- 科技、建站、经验、云计算、5G、大数据,站长网!
当前位置: 首页 > 站长学院 > MySql教程 > 正文

Mysql tools中的orzdba举例解析

发布时间:2021-12-17 10:11:09 所属栏目:MySql教程 来源:互联网
导读:本篇内容主要讲解Mysql tools中的orzdba举例分析,感兴趣的朋友不妨来看看。本文介绍的方法操作简单快捷,实用性强。下面就让小编来带大家学习Mysql tools中的orzdba举例分析吧! mysql工具集:orzdba 修改代码160行左右配置MySQL的相关验证信息,如username,
本篇内容主要讲解“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 ) {
  
./orzdba -mysql -innodb_rows -C 500000 -i 5
 
  -S /u01/tmp/mysql.sock
 
  -L /tmp/orzdba.log -logfile_by_day &
 
2、使用orzdba_report.py分析orzdba.log.xxxx-xx-xx文件生成报表
 
orzdba_report.py源码:orzdba_report
  
python orzdba_report.py orzdba.log.2016-02-21
 
到此,相信大家对“Mysql tools中的orzdba举例分析”有了更深的了解,不妨来实际操作一番吧!这里是亿速云网站,更多相关内容可以进入相关频道进行查询,关注我们,继续学习!

(编辑:源码网)

【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容!

    热点阅读