myql 5.5.23安装：

1. 安装一些需要的库

yum install gcc-c++ gperf ncurses-devel readline-devel libaio-devel bison ncurses-devel openssl-devel cmake
yum groupinstall  Development tools

2. mysql 5.5以后用cmake编译了，安装cmake(centos 6内，在上面安装了cmake,这一步就可以不做了):

wget http://www.cmake.org/files/v2.8/cmake-2.8.8.tar.gz
cd cmake-2.8.8

./configure 

make && make install

阅读全文

今天看mysql slow日志的时候发现有些查询的时间特别长，如下面：

# User@Host: flowershop2[flowershop2] @  [10.176.202.139]
# Query_time: 18446744073709.550781  Lock_time: 0.000027 Rows_sent: 0  Rows_examined: 1
SET timestamp=1332464662;
UPDATE f_users SET uCash = ‘9861’, uSellFlowerTime = ‘1332464662’ WHERE uId = ‘1829711071’;
# Time: 120322 21:06:22
# User@Host: flowershop2[flowershop2] @  [10.176.227.212]
# Query_time: 18446744073709.550781  Lock_time: 0.000022 Rows_sent: 0  Rows_examined: 1
SET timestamp=1332464782;
UPDATE f_user_actions SET uaSize = ’11’ WHERE uId = ‘1551376611’ AND uaId = ‘1332129864763’;
# Time: 120322 21:07:22
# User@Host: flowershop2[flowershop2] @  [10.176.202.139]
# Query_time: 18446744073709.550781  Lock_time: 0.000020 Rows_sent: 2  Rows_examined: 2
SET timestamp=1332464842;

阅读全文

第一：首先要把mysqld停止

service mysqld stop

第二：启动mysql，但是要跳过权限表

/usr/local/mysql/bin/mysqld_safe –skip-grant-tables &

第三：进去mysql，并修改密码

mysql -u root
mysql>use mysql;
mysql>update set user password=password("new_pass") where user="root";
mysql>flush privileges;
mysql>q

阅读全文

yum install gcc gcc-c++

groupadd mysql
useradd --shell /sbin/nologin -g mysql mysql
tar zxvf mysql-5.1.58.tar.gz
cd mysql-5.1.58

阅读全文

INNODB_LOG_FILE 过于小，会直接触发CHECKPOINT，导致频繁IO请求； 多大是合适的？

========
: (none) 16:13:13> pager grep sequence               
PAGER set to 'grep sequence'
: (none) 16:13:14>
: (none) 16:13:15>  SHOW engine innodb STATUSG SELECT sleep(60); SHOW engine innodb STATUSG               
Log sequence number 1450 485101299
1 row in set (0.09 sec)


1 row in set (1 min 0.01 sec)

Log sequence number 1450 505024667
1 row in set (0.00 sec)

: (none) 16:14:37> nopager
PAGER set to stdout
: (none) 16:14:43> select (505024667-485101299)/1024/1024;
+---------------------------------+
| (505024667-485101299)/1024/1024 |
+---------------------------------+
|                     19.00040436 |
+---------------------------------+
1 row in set (0.00 sec)
========

阅读全文

监控mysql server的性能，在cacti上内估计很多人都是用mysql-cacti-templates模板吧，但是它默认的配置只支持标准mysql 3306端口的监控。

其实它的脚本是已经支持多端口的，只是模板内的配置默认不支持而已。脚本的参数如下：

Usage: php ss_get_mysql_stats.php --host --items [OPTION]

   --host      Hostname to connect to; use host:port syntax to specify a port
               Use :/path/to/socket if you want to connect via a UNIX socket
   --items     Comma-separated list of the items whose data you want
   --user      MySQL username; defaults to root if not given
   --pass      MySQL password; defaults to 5MebCCNrzdPYpx3L if not given
   --heartbeat MySQL heartbeat table; defaults to '' (see mk-heartbeat)
   --nocache   Do not cache results in a file
   --port      MySQL port; defaults to 3306 if not given
   --mysql_ssl Add the MYSQL_CLIENT_SSL flag to mysql_connect() call

阅读全文

如果innodb_log_file_size以前是256M,现在要调整到512M，那么更改配置后，你将无法启动mysql，这个参数调整特别是有数据时需要慎之又慎！！！发这篇个文章主要是为了提醒我自己，以前吃过这方面的亏，还好当时是测试服务器。

那万一碰到后怎么办呢？

先改回去试试，能成功启动的话再导出数据做备份。再：

阅读全文