`

MySQL dba Script

阅读更多

MySQL DBA scripts

The following various scripts as used for various different tasks when reviewing MySQL installations.

  • Calculate Your MySQL Database Size
  • Calculate Your Individual MySQL Schema Size
  • Log various MySQL statistics
  • Analyze MySQL statistics
  • Automated MySQL Installation

    Calculate Your Total MySQL Database Size

    I use the following SQL script as an audit of for each current MySQL Database Instance. You can use this for example on a daily basis to get an indication of the overall growth of your database.
    This SQL uses the MySQL Information_schema that is available for MySQL Version 5.0 and higher.
    For those using MySQL 4.x, using the CHECK TABLE STATUS for each schema and manually computing the same produces a similar result.

    Usage

    $ wget http://ronaldbradford.com/mysql-dba/allschemas.sql
    $ mysql -u[user] -p  -v -v -v < allschemas.sql
    

    Output

    The following as sample outputs from clients.

    +--------------------+-----------------+-----------------+-----------------+--------+
    | table_schema       | total_mb        | data_mb         | index_mb        | tables |
    +--------------------+-----------------+-----------------+-----------------+--------+
    | xxxxxx_xxx         | 993088.84375000 | 776273.18750000 | 216815.65625000 |     54 |
    | information_schema |      0.00390625 |      0.00000000 |      0.00390625 |     17 |
    +--------------------+-----------------+-----------------+-----------------+--------+
    2 rows in set (59.81 sec)
    
    +----------------------------+----------------+----------------+---------------+--------+
    | table_schema               | total_mb       | data_mb        | index_mb      | tables |
    +----------------------------+----------------+----------------+---------------+--------+
    | xxxxxxx_xxx_xxxx_xxx       | 45314.47730350 | 38458.88941288 | 6855.58789063 |   2359 |
    | xxxxxxx_xxx_xxxx_xxx       | 28758.38682079 | 24461.27060986 | 4297.11621094 |    275 |
    | xxxxxxx_xxx_xxxx_xxx       | 28732.41495800 | 24464.20304394 | 4268.21191406 |    368 |
    | xxxxxxx_xxx_xxxx_xxx       | 24586.48277569 | 20941.44176006 | 3645.04101563 |    302 |
    | xxxxxxx_xxx_xxxx_xxx       |  3128.63515377 |  2664.54726315 |  464.08789063 |     48 |
    | xxxxxxx_xxx_xxxx_xxx       |  2865.36613274 |  2440.44328117 |  424.92285156 |    265 |
    | xxxxxxx_xxx_xxxx_xxx       |  1635.16585350 |  1388.68831444 |  246.47753906 |   2034 |
    | xxxxxxx_xxx_xxxx_xxx       |  1442.15766239 |  1231.41840458 |  210.73925781 |     17 |
    ...
    +----------------------------+----------------+----------------+---------------+--------+
    74 rows in set (20.56 sec)
    

    Calculate Your Individual Schema Size

    Usage

    $ wget http://ronaldbradford.com/mysql-dba/perschema.sql
    $ mysql -u[user] -p -v -v -v [table-schema] < perschema.sql
    

    Output

    +------------------+--------+------------+------------+----------------+--------------+--------------+--------------+
    | table_name       | engine | row_format | table_rows | avg_row_length | total_mb     | data_mb      | index_mb     |
    +------------------+--------+------------+------------+----------------+--------------+--------------+--------------+
    | xxxxxxx          | InnoDB | Compact    |    1778523 |            314 | 658.39062500 | 533.84375000 | 124.54687500 |
    | xxxxxxxxx        | InnoDB | Compact    |     553266 |            846 | 472.25000000 | 446.75000000 |  25.50000000 |
    | xxxxxxx          | InnoDB | Compact    |     435892 |            884 | 392.25000000 | 367.81250000 |  24.43750000 |
    | xxxxxxxxxxxxxx   | InnoDB | Compact    |    1106547 |             65 | 133.26562500 |  68.59375000 |  64.67187500 |
    | xxxxxxxxxxxxxxxx | InnoDB | Compact    |      58281 |            531 |  30.34375000 |  29.51562500 |   0.82812500 |
    | xxxxxxxxxx       | InnoDB | Compact    |      68721 |            298 |  28.12500000 |  19.54687500 |   8.57812500 |
    ...
    

    NOTE: The table_rows and corresponding avg_row_length is only approximate for Innodb tables. These values under certain circumstances can be wildly inaccurate. The data and index size are considered very accurate.

    Logging MySQL Stats

    Usage

    $ wget http://ronaldbradford.com/mysql-dba/log-mysql-stats.sh
    $ chmod +x log-mysql-stats.sh
    $ export LOG_DIR=`pwd`
    $ export MYSQL_AUTHENTICATION="-umonitor -psakila"
    $ ./log-mysql-stats.sh variables
    $ ./log-mysql-stats.sh status
    $ ./log-mysql-stats.sh processlist
    $ ./log-mysql-stats.sh innodbstatus
    $ ./log-mysql-stats.sh masterstatus
    $ ./log-mysql-stats.sh slavestatus
    

    Output

    $ more mysql.variables.090629.003542.txt 
    | date_time | 090629.003542 |
    | timezone | -0400 |
    +---------------------------------+-----------------------------------------------------------+
    | Variable_name                   | Value                                                     |
    +---------------------------------+-----------------------------------------------------------+
    | auto_increment_increment        | 1                                                         |
    | auto_increment_offset           | 1                                                         |
    | autocommit                      | ON                                                        |
    | automatic_sp_privileges         | ON                                                        |
    | back_log                        | 50                                                        |
    | basedir                         | /opt/mysql-5.1.25-rc-linux-x86_64-glibc23/                |
    | big_tables                      | OFF                                                       |
    | binlog_cache_size               | 32768                                                     |
    | binlog_format                   | MIXED                                                     |
    | bulk_insert_buffer_size         | 8388608                                                   |
    ...
    
    $ more mysql.status.090629.003747.txt 
    | date_time | 090629.003747 |
    | timezone | -0400 |
    +-----------------------------------+-------------+
    | Variable_name                     | Value       |
    +-----------------------------------+-------------+
    | Aborted_clients                   | 4           |
    | Aborted_connects                  | 6037        |
    | Binlog_cache_disk_use             | 0           |
    | Binlog_cache_use                  | 0           |
    | Bytes_received                    | 2303295323  |
    | Bytes_sent                        | 50239245119 |
    | Com_admin_commands                | 0           |
    | Com_assign_to_keycache            | 0           |
    | Com_alter_db                      | 0           |
    | Com_alter_db_upgrade              | 0           |
    | Com_alter_event                   | 0           |
    | Com_alter_function                | 0           |
    | Com_alter_procedure               | 0           |
    | Com_alter_server                  | 0           |
    | Com_alter_table                   | 59          |
    | Com_alter_tablespace              | 0           |
    ...
    

    To implement logging in the real world.

    Installation
    sudo su -
    useradd -m -s /bin/bash monitor
    mkdir /home/monitor/scripts
    cd /home/monitor/scripts
    wget http://ronaldbradford.com/mysql-dba/log-mysql-stats.sh
    chmod +x log-mysql-stats.sh
    mkdir -p /somedir/mysqlstatlogs
    chown monitor:monitor /somedir/mysqlstatlogs
    chown -R monitor:monitor /home/monitor
    
    Configuration
    su - monitor
    # -c Create Necessary MySQL Permissions
    # -u User with SUPER permissions, defaults to root
    scripts/log-mysql-stats.sh -udba -c
    
    Testing
    mkdir tmp
    LOG_DIR=tmp scripts/log-mysql-stats.sh -m all
    ls -l tmp
    rm -rf tmp
    
    Operation
    # m h  dom mon dow   command
    * * * * * LOG_DIR="/somedir/mysqlstatlogs" /home/monitor/scripts/log-mysql-stats.sh -m -l status innodbstatus masterstatus slavestatus
    0 * * * * LOG_DIR="/somedir/mysqlstatlogs" /home/monitor/scripts/log-mysql-stats.sh -m -l processlist
    0 0 * * * LOG_DIR="/somedir/mysqlstatlogs" /home/monitor/scripts/log-mysql-stats.sh -m -l variables
    0 0 * * * LOG_DIR="/somedir/mysqlstatlogs" /home/monitor/scripts/log-mysql-stats.sh -z
    

    Analyze MySQL statistics

    $ wget -O mysql-stats.sh http://ronaldbradford.com/mysql-dba/mysql-stats.sh
    $ chmod +x mysql-stats.sh
    $ export LOG_DIR=`pwd`
    $ ./mysql-stats.sh all
    

    Install MySQL

    Usage

    $ wget -O meta.sh http://ronaldbradford.com/mysql-dba/meta.sh
    $ wget -O install-mysql.sh http://ronaldbradford.com/mysql-dba/install-mysql.sh
    $ chmod +x ./install-mysql.sh
    $ sudo ./install-mysql.sh -d [/environment] [-i [ip-address]]
    

    where:

    • -d [/environment] is the base directory of the environment in question, for simplicity, use a base directory (e.g. from /) with the environment name. NOTE: Script is untested for directories that are not root level '/' directories.
    • -i [ip] is a virtual IP that is assigned to the database environment

    Pre-requisites

    • This script must be run as the 'root' user, as it creates files and sets appropriate permissions.
    • The base environment directory (e.g. /[environment]) must exist and be empty
    • The Installed MySQL binary version must be in the /software directory
    • The meta.sh file is required in the same directory as the install_mysql.sh file

    Specific example

    $ sudo su -
    $ mkdir /software /db1
    $ cd /software
    $ wget http://dev.mysql.com/get/Downloads/MySQL-5.1/mysql-5.1.37-linux-x86_64-glibc23.tar.gz/from/http://mirror.trouble-free.net/mysql_mirror/
    $ mkdir /db1
    $ ./install-mysql.sh -d /db1
    

    Example Output

    # ./install-mysql.sh -d /db1
    16:50:55 INFO  Starting installation of MySQL
    16:50:55 WARN  IP not specified with -i, not binding to any IP
    16:50:55 WARN  No MySQL Version specified, using '5.1'
    16:50:55 INFO  Installing MySQL . . .
    16:50:55 INFO  Creating default MySQL user
    16:50:55 INFO  Group 'mysql' already exists
    16:50:55 INFO  User 'mysql' already exists
    16:50:55 INFO  Installing MySQL from binary tar file '/software/mysql-5.1.37-linux-x86_64-glibc23.tar.gz
    16:51:03 INFO  Setting MYSQL_HOME to '/db1/mysql'
    16:51:03 INFO  Installing mysql starter database
    16:51:13 INFO  Setting mysql directory ownership & permissions
    16:51:13 INFO  Configuring MySQL
    16:51:13 INFO  Setting MYSQL_HOME to '/db1/mysql'
    16:51:13 INFO  Generating my.cnf
    16:51:13 INFO  Configuring services for 'mysql_db1'
    16:51:15 INFO  Defining User Permissions
    16:51:15 INFO  Completed installation of MySQL
    
    
    A successful installation will result in:
    • MySQL installed and running in /db1
    $ export MYSQL_HOME=/db1/mysql
    $ $MYSQL_HOME/bin/mysql -udba -psakila -h${IP} -e "SELECT VERSION();SHOW SCHEMAS;"
    +------------+
    | VERSION()  |
    +------------+
    | 5.1.37-log | 
    +------------+
    +--------------------+
    | Database           |
    +--------------------+
    | information_schema | 
    | mysql              | 
    | test_db1           | 
    +--------------------+
    

    Common Errors

    Not run as root
        ./install_mysql.sh
        14:11:05 INFO  Starting installation of MySQL
        14:11:05 ERROR This script must be run as root
        14:11:05 INFO  Exiting with status code of '1'
    
    Not specifying an environment directory with -d.
        ./install_mysql.sh
        14:14:16 INFO  Starting installation of MySQL
        14:14:16 ERROR You must specify an install directory with -d
        14:14:16 INFO  Exiting with status code of '1'
    
    Not specifying a binding IP Address with -i
        ./install_mysql.sh -d /xxx
        14:30:45 INFO  Starting installation of MySQL
        14:30:45 ERROR You must specify a binding IP with -i
        14:30:45 INFO  Exiting with status code of '1'
    
    Specifying an environment directory that already contains an instance
        ./install_mysql.sh -d /qa -i 192.168.1.22
        14:33:33 INFO  Starting installation of MySQL
        14:33:33 WARN  No MySQL Version specified, using '5.1'
        14:33:33 ERROR A MySQL Instance at '/qa/mysql' has been detected, ensure no MySQL instance exists at '/qa'
        14:33:33 INFO  Exiting with status code of '1'
    
    Specifying an environment directory that does not exist.
        /install_mysql.sh -d /xxx -i 192.168.1.22
        14:34:29 INFO  Starting installation of MySQL
        14:34:29 WARN  No MySQL Version specified, using '5.1'
        14:34:29 ERROR Base deployment directory '/xxx' does not exist
        14:34:29 INFO  Exiting with status code of '1'
    
    Specify an invaid version of MySQL to install
        ./install_mysql.sh -d /xxx -i 192.168.1.22 -v 5.2
        14:37:34 INFO  Starting installation of MySQL
        14:37:34 ERROR Unable to find a '5.2' version of MySQL in '/software/'
        14:37:34 INFO  Exiting with status code of '1'
    

    Unsupported Scripts

    $ wget http://ronaldbradford.com/mysql-dba/mysql-sessions.sh
    
    
    
分享到:
评论

相关推荐

    mysql dba 常用脚本

    MySQL DBA(数据库管理员)在日常工作中经常需要执行各种脚本来进行数据库的管理和维护工作。这些脚本涵盖了从数据备份、恢复、性能优化到错误排查等多个方面。以下是一些MySQL DBA常用的知识点,结合“mysql_mgr_...

    mysqlDBA PPT

    该文档适合MYSQL数据库管理员使用,请大家多支持多指正

    Open18_mysql_script

    【标签】"工具" 表明这可能是一个实用工具集合,这些脚本可以帮助开发者或DBA更高效地处理日常MySQL工作。可能包括数据库迁移、数据清理、查询优化或者安全相关的功能。 【压缩包子文件的文件名称列表】"script" 这...

    MySQL DBA面试题

    /usr/local/bin/mysql-proxy -P 192.168.4.100:3306 -b 192.168.4.10:3306 -r 192.168.4.20 -s /path/to/script.sh & ``` 其中`-P`用于设置监控的IP和端口,`-b`用于设置写请求服务器的IP和端口,`-r`用于设置读...

    dba_script.rar

    9. 监控工具:如Oracle Enterprise Manager、SQL Server Management Studio (SSMS) 和 MySQL Workbench等,这些工具帮助DBA进行日常管理和故障排查。 10. 数据仓库和BI:在大数据时代,DBA可能还会涉及数据仓库的...

    DBA日常维护常用脚本

    - `shell script`:组合多个命令,实现复杂自动化流程。 - `Perl` 或 `Python` 脚本:更高级的自动化工具,可以处理更复杂的逻辑和数据操作。 5. **安全性脚本**: - 用户管理:添加、删除、修改用户权限。 - `...

    MySQL Workbench 6.3.7 CE.rar

    总的来说,MySQL Workbench 6.3.7 CE是一个全面的数据库解决方案,无论是初学者还是经验丰富的DBA,都能从中受益。它简化了数据库的设计和管理过程,提高了工作效率,是MySQL数据库管理员不可或缺的工具。通过这个...

    mysql-workbench使用说明

    - **自动生成 SQL 脚本**:完成模型设计后,通过“File -&gt; Export -&gt; Forward Engineer MySQL Create Script”导出为 SQL 脚本。 5. **管理服务器实例**: - 新建服务器实例、管理导入/导出、安全管理和实例监控...

    Oracle.VS.Mysql

    而在**MySQL**中,则使用`source`命令,例如`source script.sql;`。 #### 四、SQL表达式和系统时间获取 **Oracle**与**MySQL**在SQL语句的表达式方面基本相似,但在获取系统时间时存在差异。Oracle使用`sysdate`,...

    workbench,mysql,表格都没有.pdf

    总的来说,MySQL Workbench是一个强大的集成工具,涵盖了数据库开发的各个环节,从设计模型到执行SQL,再到管理服务器,都提供了直观的图形化界面,极大地提高了开发人员和DBA的工作效率。无论你是初学者还是经验...

    MySQLMTOP数据库监控工具 2.2.zip

    MySQLMTOP 2.2 更新记录:1.google字体样式本地化,解决字体加载慢的问题2.python脚本执行方式修改由./python script更新为python script3.复制监控支持gtid模式的监控4.修复复制延时无法报警的问题5.修复进程监控里...

    sql脚本批量执行工具

    SQL脚本批量执行工具是一种高效、实用的数据库管理软件,专为DBA(数据库管理员)和开发人员设计,用于一次性处理多个SQL脚本文件。在数据库维护、数据迁移或更新过程中,这种工具能够显著提高工作效率,减少手动...

    SSIS基本OLEDB / ODBC目标脚本

    当在SSIS中使用这些接口时,你可以直接连接到任何支持OLEDB或ODBC的数据源,包括但不限于SQL Server、Oracle、MySQL等。 描述中提到的“与子查询一起使用做更新”意味着可能在脚本中嵌入了SQL查询,这些查询可能...

    财务会计应用数据库II:基础结构

    "MySQL_create_script.zip"文件很可能包含用于创建数据库和表的脚本,MySQL是一种广泛使用的开源关系型数据库管理系统,特别适合中小型企业或作为开发和测试环境。通过这些脚本,我们可以快速搭建财务会计应用所需的...

    财务会计应用程序数据库III:完善的基础结构

    MySQL的创建脚本(MySQL_create_script.zip)可能是用于初始化数据库结构和填充测试数据的SQL语句集合。 6. **安全措施**:在财务系统中,数据安全至关重要。这涉及使用强密码策略,设置访问控制,加密敏感数据,...

    SQL_Toolbelt_2018_2.0.1.2321_Downloadly.ir.rar

    这个软件套装主要面向IT专业人员,特别是那些使用SQL语言进行数据库操作、管理和开发的程序员和DBA。在这款工具集中,用户可以找到一系列实用的工具,旨在提高数据库管理效率,简化日常任务。标签"Delphi"可能意味着...

    php.ini-development

    Using this ini directive may cause problems unless you know what script ; is doing. ; Note: You cannot use both "mb_output_handler" with "ob_iconv_handler" ; and you cannot use both "ob_gzhandler" ...

    C#MYSQLWrapper类和PHPWrapper脚本

    - `C-MYSQLWrapper-Class-and-PHPWrapper-Script.pdf`可能包含了项目的详细文档,解释了如何使用这两个Wrapper类,以及可能的示例代码和安装步骤。 - `MYSQLWrapper.zip`则可能是C#版本的Wrapper类库的源代码或...

    财务会计应用数据库IV:通用文件和固定资产

    `MySQL_create_script.zip`可能包含了创建数据库表结构和初始化数据的SQL脚本。这些脚本可以帮助快速部署和配置数据库环境。 总之,“财务会计应用数据库IV:通用文件和固定资产”涉及到数据库设计、SQL操作、文件...

Global site tag (gtag.js) - Google Analytics