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舉例分析”有了更深的了解，不妨來實際操作一番吧！這里是億速云網站，更多相關內容可以進入相關頻道進行查詢，關注我們，繼續學習！