mysql 自定义维护脚本

#!/bin/sh

#set passwd
#mysql_config_editor set -G mysql_pass -S /data/mysql1/run/mysql.sock -uroot -p
#chmod a+x msql


echo -e "\n=============`date`===================\n"

case $1 in
ttsql)
        mysqladmin --login-path=mysql_pass processlist| grep -v Sleep
;;

status)
        mysqladmin --login-path=mysql_pass status
echo 
;;

tab_size)      
mysql --login-path=mysql_pass -e "SELECT CONCAT(table_schema,'.',table_name) as table_name,CONCAT(ROUND(SUM(data_length / 1024 / 1024), 2), 'MB') AS data_length_MB,
       CONCAT(ROUND(SUM(index_length / 1024 / 1024), 2), 'MB') AS index_length_MB
  FROM information_schema.TABLES
 WHERE 
  CONCAT(table_schema,'.',table_name)='$2'
"
echo 
;;
desc_table)      
mysql --login-path=mysql_pass -e "desc  $2"
echo 
;;
get_tab_ddl)      
mysql --login-path=mysql_pass -e "show create table $2\G"
echo 
;;
tab_idx)      
mysql --login-path=mysql_pass -e "show index from  $2"
echo 
;;
list)      
mysql --login-path=mysql_pass -e "show global variables  like '%$2%'"
echo 
;;
lsuser)      
mysql --login-path=mysql_pass -e "select user,host,account_locked from  mysql.user"
echo 
;;
lsdb)      
mysql --login-path=mysql_pass -e "show databases"
echo 
;;
lstab)      
mysql --login-path=mysql_pass -e "show tables from $2"
echo 
;;
ls_user_privs)      
mysql --login-path=mysql_pass -e " show grants for  $2"
echo 
;;
ls_all_privs)      
mysql --login-path=mysql_pass -e " show privileges "
echo 
;;
get_db_ddl)      
mysql --login-path=mysql_pass -e "show create database $2 "
echo 
;;
-e)      
mysql --login-path=mysql_pass -e "$2"
echo 
;;


*)
     echo '''  msql keyword [value1 [value2]] 
                ---------------------------------------------------------------------------
                msql ttsql                              -> list running sql 
                msql status                             -> list mysql status 
                msql tab_size [schema.table_name]       -> list table size 
                msql desc_table [schema.table_name]     -> list table describle 
                msql get_tab_ddl [schema.table_name]    -> list table ddl 
                msql tab_idx [schema.table_name]        -> list index describle 
                msql list [value]                       -> list variables  
                msql lsuser                             -> list user  
                msql lsdb                               -> list database  
                msql lstab [db_name]                    -> list database tables 
                msql ls_user_privs [username]           -> list user privs 
                msql ls_all_privs                       -> list all system privs 
                msql get_db_ddl [database_name]         -> list database ddl
                msql -e [value]                         -> exec mysql command  
                ---------------------------------------------------------------------------
      '''
     ;;
esac

[mysql1@ecsmysql1 ~]$ msql ttsql

=============2019年 01月 18日 星期五 09:25:20 CST===================

+--------+-----------+--------------------+--------+-------------+--------+---------------------------------------------------------------+------------------+
| Id     | User      | Host               | db     | Command     | Time   | State                                                         | Info             |
+--------+-----------+--------------------+--------+-------------+--------+---------------------------------------------------------------+------------------+
| 740220 | repl_bcdb | 10.226.22.32:12246 |        | Binlog Dump | 177382 | Master has sent all binlog to slave; waiting for more updates |                  |
| 744994 | root      | localhost          |        | Query       | 0      | starting                                                      | show processlist |
+--------+-----------+--------------------+--------+-------------+--------+---------------------------------------------------------------+------------------+

[mysql1@ecsmysql1 ~]$ msql lsdb

=============2019年 01月 18日 星期五 09:25:51 CST===================

+--------------------+
| Database           |
+--------------------+
| information_schema |
| msgdb1             |
| msgdb2             |
| msgdb3             |
| msgdb4             |
| msgdb5             |
| msgdb6             |
| msgdb7             |
| msgdb8             |
| mysql              |
| percona            |
| performance_schema |
| sys                |
| test               |
+--------------------+

[mysql1@ecsmysql1 ~]$ msql lstab msgdb3

=============2019年 01月 18日 星期五 09:26:14 CST===================

+---------------------------------+
| Tables_in_msgdb3                |
+---------------------------------+
| dual                            |
| ecps_activity_play_record       |
| ecps_coc_message_log            |
| ecps_date_order                 |
| ecps_date_order_detail          |
| ecps_date_order_detail_busi     |
| ecps_date_order_detail_contract |
| zhouc                           |
+---------------------------------+

也可以直接执行mysql语句

[mysql2@ecsmysql2 ~]$ msql -e "show slave status\G"|grep IO
               Slave_IO_State: Waiting for master to send event
             Slave_IO_Running: Yes
                Last_IO_Errno: 0
                Last_IO_Error: 
      Last_IO_Error_Timestamp: