MySQL
jxzkin
这个作者很懒,什么都没留下…
展开
-
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
Solution:mysql> use mysqlDatabase changedmysql> update user set password=old_password('mysql') where user='root' and host='localhost' ;Query OK, 1 row affected (0.07 sec)Rows matched: 1 C原创 2012-05-02 22:51:35 · 700 阅读 · 0 评论 -
MySQL导出用户权限脚本
#!/bin/bash#Function export user privilegesexpgrants(){ mysql -B -N $@ -e "SELECT CONCAT('SHOW GRANTS FOR ''',user, '''@''', host, ''';') AS query FROM mysql.user" | \ mysql $@ | \ sed 's/原创 2012-11-02 11:37:44 · 836 阅读 · 0 评论 -
mysql字符串截取
1. substring_indexsubstring_index(str,delim,count)mysql> select substring_index('a-b-c-d','-',-1);+-----------------------------------+| substring_index('a-b-c-d','-',-1) |+----------------原创 2012-10-25 12:35:37 · 824 阅读 · 0 评论 -
MySQL快速插入大批量数据存储过程
-- ------------------------------ Table structure for `test`-- ----------------------------DROP TABLE IF EXISTS `test`;CREATE TABLE `test` ( `id` int(11) NOT NULL AUTO_INCREMENT, `ordernum` va原创 2012-11-05 15:05:36 · 998 阅读 · 0 评论 -
Shell-xtrabackup script
#!/bin/bash################################################################################### purpose:xtrabackup script # version:v1.5# date :2012-08-22# author :kin zhang####################原创 2012-09-10 14:45:49 · 897 阅读 · 0 评论 -
MySQL常用命令及SQL
1.查询支持引擎mysql> show engines;+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+| Engine | Support | Com原创 2012-07-04 09:12:34 · 706 阅读 · 0 评论 -
MySQL error: 145 (Table 'xxx' is marked as crashed and should be repaired
Symptoms:MySQL MySQL error: 145 (Table 'xxx' is marked as crashed and should be repairedSolutions:/usr/local/mysql/bin/myisamchk -r xxx.MYI原创 2013-01-16 16:57:12 · 1193 阅读 · 0 评论 -
innobackupex-1.5.1: Error: mysql child process has died: sh: mysql: command not found
ln -s /usr/local/mysql/bin /usr/bin/mysql原创 2013-02-05 11:04:25 · 1887 阅读 · 0 评论 -
Shell-Check MySQL Backup Status
# ######################################################################### Purpose:Check MySQL Backup status# Authors:Kin Zhang# Version:v1.0# Date:2013-02-18# #################################原创 2013-02-18 10:15:42 · 871 阅读 · 0 评论 -
MySQL-启动双实例
-- install db/usr/local/mysql56/scripts/mysql_install_db --user=mysql --datadir=/db/data56 --basedir=/usr/local/mysql56-- start mysql/usr/local/mysql56/bin/mysqld_safe --defaults-file=/etc/my.cnf.原创 2013-02-18 16:25:23 · 763 阅读 · 0 评论 -
MySQL导出routines创建脚本
mysqldump -n -d -t --routines --triggers --all-databases > /tmp/proc.sql原创 2013-03-28 10:18:42 · 2092 阅读 · 0 评论 -
MySQL查询结果导出为文件
select * from db into outfile '/tmp/db.csv'fields terminated by ',';如需字段名可用以下方法:mysql -uroot -pmysql mysql -Be "select * from db" > /tmp/db.xls原创 2013-05-28 11:48:26 · 762 阅读 · 0 评论 -
MySQL-mysqldump backup script
#!/bin/bash################################################################################### purpose:mysqldump script # version:v1.0# date :2013-07-01# author :kin zhang#####################原创 2013-07-01 17:00:00 · 959 阅读 · 0 评论 -
MySQL使用TCMalloc库
TCMalloc的全称为Thread-Caching Malloc,是谷歌开发的开源工具“google-perftools”中的一个成员。与标准的glibc库的malloc相比,TCMalloc库在内存分配效率和速度上要高很多,这在很大程度上提高了服务器在高并发情况下的性能,从而降低系统负载。要安装TCMalloc库,需要安装libunwind(32位操作系统不需要安装)和google-per转载 2013-12-06 13:14:52 · 1462 阅读 · 0 评论 -
MySQL mysqldump小记
mysqldump -uroot -p -d db > db.sql #导出数据库表结构mysqldump -uroot -p -d db tab > db.sql #导出某张表的结构mysqldump -uroot -p db > db.sql #导出数据库所有表结构及数据mysqldump -uroot -p db tab >db.sql #导出数据库某张原创 2014-03-07 11:47:24 · 738 阅读 · 0 评论 -
MySQL-lock table thread
kill mysql lock table thread原创 2014-07-09 13:31:26 · 626 阅读 · 0 评论 -
MySQL Tuner- Author Major Hayden
#!/usr/bin/perl -w# mysqltuner.pl - Version 1.2.0# High Performance MySQL Tuning Script# Copyright (C) 2006-2011 - - major@mhtx.net## For the latest updates, please visit http://mysqltuner.com/#原创 2012-09-24 08:18:39 · 1422 阅读 · 0 评论 -
SQL递归查询
http://space.itpub.net/118838/viewspace-735995connect by是结构化查询中用到的,其基本语法是:select ... from tablename start by cond1connect by cond2where cond3;简单说来是将一个树状结构存储在一张表里,比如一个表中存在两个字段:id,parentid转载 2012-09-14 13:49:41 · 1071 阅读 · 0 评论 -
mysqldump注意事项
参考三思笔记,需要重视这个无效view问题可能导致mysqldump失败.http://space.itpub.net/7607759/viewspace-697899 自己也先演练一下.mysql> create table kin(id int,sel varchar(10));Query OK, 0 rows affected (0.01 sec)mysql> cre原创 2012-05-21 16:49:40 · 853 阅读 · 0 评论 -
Xtrabackup full backup
Step:create full backup:innobackupex --defaults-file=/etc/my.cnf --user=root --password=mysql /tmp/backupshutdown mysql:mysqladmin -uroot -p'mysql' -S /tmp/mysql.sock shutdownprepare:inn原创 2012-05-23 09:45:46 · 930 阅读 · 0 评论 -
MySQL存储过程执行拼接语句
delimiter //create procedure sp_concat()begindeclare v_date varchar(4000);declare v_sql varchar(4000);set v_date =(select date_format(now(),'%Y%m%d%H%s%i'));set @v_date =v_date;set v_sql原创 2012-06-18 22:58:33 · 1915 阅读 · 0 评论 -
Power Designer无法连接ODBC源
平台:OS:Win7 64bitSoftware Version:PowerDesigner 15MySQL ODBC Version:5.1 64bit 现象:PD无法连接MySQL ODBC源 解决方法:下载安装MySQL ODBC Version:5.1 32bit原创 2012-07-04 19:07:23 · 1604 阅读 · 0 评论 -
安装MySQLdb for Linux
1.下载安装对应rpmptools-0.6c11-1.src.rpm (md5)uptools-0.6c11-py2.4.egg (md5)http://pypi.python.org/pypi/setuptools/ 2.安装Mysql for Pythonhttp://sourceforge.net/projects/mysql-python/tar zvxf原创 2012-07-10 14:17:34 · 631 阅读 · 0 评论 -
py_innodb_page_info.py
#py_innodb_page_info.py#! /usr/bin/env python #encoding=utf-8import mylibfrom sys import argvfrom mylib import myargvif __name__ == '__main__': myargv = myargv(argv) if myargv.parse_原创 2012-07-10 16:04:59 · 1000 阅读 · 0 评论 -
MySQL group_concat函数进行行转列使用
创建测试数据:mysql> create table group_concat (a int);Query OK, 0 rows affected (0.04 sec)mysql> insert into group_concat values(2),(1),(4),(5),(8);Query OK, 5 rows affected (0.00 sec)Records: 5原创 2012-08-06 15:48:33 · 3657 阅读 · 0 评论 -
MySQL CASE WHEN END使用
mysql> select * from tmp;+------+------+------+| a | b | c |+------+------+------+| 1 | 10 | 1 || 10 | 10 | 2 || 10 | 100 | 2 |+------+------+------+3 row原创 2012-08-06 16:24:06 · 918 阅读 · 0 评论 -
MySQL Slave_IO_Running: NO异常处理
show slave status\G; Master_Log_File: mysql-bin.000029Read_Master_Log_Pos: 3154083Relay_Log_File: c7-relay-bin.000178Relay_Log_Pos: 633Relay_Master_Log_File: mysql-bin.转载 2012-07-24 16:43:28 · 1280 阅读 · 0 评论 -
mysql查询缓慢SQL State:Copying to tmp table
平台:OS:CentOS6.2DB Version:MySQL 5.5.24现象:页面查询缓慢处理过程:show processlist;非常多语句SQL State:Copying to tmp table查询tmp_table_size 为默认16Mmysql> show variables like '%tmp%';+-----原创 2012-07-31 09:33:12 · 2998 阅读 · 0 评论 -
Starting MySQL.The server quit without updating PID file
现象:Starting MySQL.The server quit without updating PID file (/usr/local/mysql/...).[FAILED]解决方法在my.cnf添加对应datadir=原创 2012-08-21 10:15:29 · 856 阅读 · 0 评论 -
MySQL设定MAX_BINLOG_SIZE
1.修改/etc/my.cnf添加max_binlog_size=512m2.在线修改SET @set_value = 1073741824;SET @@global.max_binlog_size = @set_value;SELECT @@global.max_binlog_size;原创 2012-08-22 09:26:00 · 4285 阅读 · 0 评论 -
MySQL监控脚本
#/bin/bash# * * * * * sh /tmp/check_kpi_local.sh > /dev/null 2>&1LOGFILE=/tmp/KPI_$(date +%F).logCURRENT=$(date "+%T %F")HOST=`/sbin/ifconfig | grep "inet addr" | awk -F: '{print $2}' | awk {'pr转载 2012-09-03 14:02:27 · 712 阅读 · 0 评论 -
MySQL binlog分析脚本
#!/bin/bashmysqlbinlog $1 | \ grep -i -e "^update" -e "^insert" -e "^delete" -e "^replace" -e "^alter" | \ cut -c1-100 | \ tr '[A-Z]' '[a-z]' | \ sed -e "s/\t/ /g;s/\`//g;s/(.*$//;s/ set .*$//;原创 2012-08-24 10:50:59 · 873 阅读 · 0 评论 -
mysqlsla安装使用
Installwget http://hackmysql.com/scripts/mysqlsla-2.03.tar.gztar zvxf mysqlsla-2.03.tar.gz cd mysqlsla-2.03 perl Makefile.PL make make install Usage:mysqlsla -lt slow slow_sta原创 2012-09-11 15:33:12 · 962 阅读 · 0 评论 -
MySQL开启慢查询
long_query_time = 1log-slow-queries = /usr/local/mysql/data/slow.loglog-queries-not-using-indexesmysqldumpslow –h主要介绍两个参数-s和-t-s 这个是排序参数,可选的有:al: 平均锁定时间ar: 平均返回记录数at: 平均查原创 2012-09-11 09:23:33 · 630 阅读 · 0 评论