- 浏览: 1318214 次
- 性别:
- 来自: 广州
文章分类
- 全部博客 (351)
- Java General (37)
- .net General (2)
- Linux Toy (55)
- Oracle (81)
- Mysql (11)
- Programer Career (12)
- Oh, my living ! (2)
- Shell Script (8)
- Web Service (0)
- Linux Server (22)
- Php/Python/Perl (3P) (2)
- Javascript General (5)
- Saleforce Apex Dev (2)
- Web General (5)
- Xen & VM tech. (17)
- PSP (13)
- OpenSolaris (34)
- php (1)
- RAI/flex/action script (16)
- asterisk/CTI (7)
- 交互设计 (6)
- English (3)
- Lucene (1)
最新评论
-
GuolinLee:
markmark
JVM调优总结 -Xms -Xmx -Xmn -Xss -
di1984HIT:
写的太好啊。
JVM调优总结 -Xms -Xmx -Xmn -Xss -
javajdbc:
javajdbc 写道
JVM调优总结 -Xms -Xmx -Xmn -Xss -
javajdbc:
...
JVM调优总结 -Xms -Xmx -Xmn -Xss -
alvin198761:
非常感谢,国外的被封杀了,你这里还有一份
How to Convert An Image-Based Guest To An LVM-Based Guest
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.
Installationsudo 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
Configurationsu - monitor # -c Create Necessary MySQL Permissions # -u User with SUPER permissions, defaults to root scripts/log-mysql-stats.sh -udba -c
Testingmkdir 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
发表评论
-
A sample to update mysqm column charecter set
2010-10-21 12:58 1276mysql> SHOW CHARACTER SET LI ... -
使用MySQL的23个注意事项
2009-09-26 16:55 11791.如果客户端和服务器端的连接需要跨越并通过不可信任的网络,那 ... -
linux shell 下 用 mysql的中文问题
2009-09-18 10:30 2745虽然很少有人用linux下的mysql 客户端来维护数据,毕竟 ... -
Mysql Cluster: The definitive HOWTO
2008-07-29 14:55 1750Mysql Cluster: The definitive H ... -
mysql 打竖显示结果
2008-05-28 10:31 1536加个\G在最后现就可以了. mysql> select ... -
dump data into txt file
2008-05-10 15:42 1454mysql> select * from Directo ... -
Using Master/Slave Replication with ReplicationCon
2008-04-28 15:51 124825.4.4.6. Using Master/Slav ... -
How To Control Mysql Replication
2008-04-28 15:46 1668How To Control Mysql Replicatio ... -
Implementing High Availability in MySQL
2008-04-16 08:04 2343MySQL provides a built-in data ... -
innodb data file per table
2008-04-16 08:04 2146One very interesting thing I no ...
相关推荐
MySQL DBA(数据库管理员)在日常工作中经常需要执行各种脚本来进行数据库的管理和维护工作。这些脚本涵盖了从数据备份、恢复、性能优化到错误排查等多个方面。以下是一些MySQL DBA常用的知识点,结合“mysql_mgr_...
该文档适合MYSQL数据库管理员使用,请大家多支持多指正
【标签】"工具" 表明这可能是一个实用工具集合,这些脚本可以帮助开发者或DBA更高效地处理日常MySQL工作。可能包括数据库迁移、数据清理、查询优化或者安全相关的功能。 【压缩包子文件的文件名称列表】"script" 这...
/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`用于设置读...
9. 监控工具:如Oracle Enterprise Manager、SQL Server Management Studio (SSMS) 和 MySQL Workbench等,这些工具帮助DBA进行日常管理和故障排查。 10. 数据仓库和BI:在大数据时代,DBA可能还会涉及数据仓库的...
- `shell script`:组合多个命令,实现复杂自动化流程。 - `Perl` 或 `Python` 脚本:更高级的自动化工具,可以处理更复杂的逻辑和数据操作。 5. **安全性脚本**: - 用户管理:添加、删除、修改用户权限。 - `...
总的来说,MySQL Workbench 6.3.7 CE是一个全面的数据库解决方案,无论是初学者还是经验丰富的DBA,都能从中受益。它简化了数据库的设计和管理过程,提高了工作效率,是MySQL数据库管理员不可或缺的工具。通过这个...
- **自动生成 SQL 脚本**:完成模型设计后,通过“File -> Export -> Forward Engineer MySQL Create Script”导出为 SQL 脚本。 5. **管理服务器实例**: - 新建服务器实例、管理导入/导出、安全管理和实例监控...
而在**MySQL**中,则使用`source`命令,例如`source script.sql;`。 #### 四、SQL表达式和系统时间获取 **Oracle**与**MySQL**在SQL语句的表达式方面基本相似,但在获取系统时间时存在差异。Oracle使用`sysdate`,...
总的来说,MySQL Workbench是一个强大的集成工具,涵盖了数据库开发的各个环节,从设计模型到执行SQL,再到管理服务器,都提供了直观的图形化界面,极大地提高了开发人员和DBA的工作效率。无论你是初学者还是经验...
MySQLMTOP 2.2 更新记录:1.google字体样式本地化,解决字体加载慢的问题2.python脚本执行方式修改由./python script更新为python script3.复制监控支持gtid模式的监控4.修复复制延时无法报警的问题5.修复进程监控里...
SQL脚本批量执行工具是一种高效、实用的数据库管理软件,专为DBA(数据库管理员)和开发人员设计,用于一次性处理多个SQL脚本文件。在数据库维护、数据迁移或更新过程中,这种工具能够显著提高工作效率,减少手动...
当在SSIS中使用这些接口时,你可以直接连接到任何支持OLEDB或ODBC的数据源,包括但不限于SQL Server、Oracle、MySQL等。 描述中提到的“与子查询一起使用做更新”意味着可能在脚本中嵌入了SQL查询,这些查询可能...
"MySQL_create_script.zip"文件很可能包含用于创建数据库和表的脚本,MySQL是一种广泛使用的开源关系型数据库管理系统,特别适合中小型企业或作为开发和测试环境。通过这些脚本,我们可以快速搭建财务会计应用所需的...
MySQL的创建脚本(MySQL_create_script.zip)可能是用于初始化数据库结构和填充测试数据的SQL语句集合。 6. **安全措施**:在财务系统中,数据安全至关重要。这涉及使用强密码策略,设置访问控制,加密敏感数据,...
这个软件套装主要面向IT专业人员,特别是那些使用SQL语言进行数据库操作、管理和开发的程序员和DBA。在这款工具集中,用户可以找到一系列实用的工具,旨在提高数据库管理效率,简化日常任务。标签"Delphi"可能意味着...
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-Class-and-PHPWrapper-Script.pdf`可能包含了项目的详细文档,解释了如何使用这两个Wrapper类,以及可能的示例代码和安装步骤。 - `MYSQLWrapper.zip`则可能是C#版本的Wrapper类库的源代码或...
`MySQL_create_script.zip`可能包含了创建数据库表结构和初始化数据的SQL脚本。这些脚本可以帮助快速部署和配置数据库环境。 总之,“财务会计应用数据库IV:通用文件和固定资产”涉及到数据库设计、SQL操作、文件...