MySQL的MyISAM和InnoDB对比及优化

本帖最后由 andy 于 2009-11-17 09:58 编辑

MyISAM和InnoDB是在使用MySQL最常用的两个表类型,各有优缺点,视具体应用而定。基本的差别为:MyISAM类型不支持事务处理等高级处理,而InnoDB类型支持。MyISAM类型的表强调的是性能,其执行速度比InnoDB类型更快,但是不提供事务支持,而InnoDB提供事务支持已经外部键等高级数据库功能。

MyISAM是ISAM表的新版本,有如下扩展:
1、二进制层次的可移植性。
2、NULL列索引。
3、对变长行比ISAM表有更少的碎片。
4、支持大文件。
5、更好的索引压缩。
6、更好的键码统计分布。
7、更好和更快的auto_increment处理。

InnoDB 是 MySQL 上第一个提供外键约束的引擎,除了提供事务处理外,InnoDB 还支持行锁,提供和 Oracle 一样的一致性的不加锁读取,能增加并发读的用户数量并提高性能,不会增加锁的数量。
InnoDB 的设计目标是处理大容量数据时最大化性能,它的 CPU 利用率是其他所有基于磁盘的关系数据库引擎中最有效率的。
InnoDB 是一套放在 MySQL 后台的完整数据库系统,InnoDB 有它自己的缓冲池,能缓冲数据和索引,InnoDB 还把数据和索引存放在表空间里面,可能包含好几个文件,这和 MyISAM 表完全不同,在 MyISAM 中,表被存放在单独的文件中,InnoDB 表的大小只受限于操作系统文件的大小,一般为 2GB。

以下是一些细节和具体实现的差别:

1、InnoDB不支持FULLTEXT类型的索引。
2、InnoDB 中不保存表的具体行数,也就是说,执行select count(*) from table时,InnoDB要扫描一遍整个表来计算有多少行,但是MyISAM只要简单的读出保存好的行数即可。注意的是,当count(*)语句包含 where条件时,两种表的操作是一样的。
3、对于AUTO_INCREMENT类型的字段,InnoDB中必须包含只有该字段的索引,但是在MyISAM表中,可以和其他字段一起建立联合索引。
4、DELETE FROM table时,InnoDB不会重新建立表,而是一行一行的删除。
5、LOAD TABLE FROM MASTER操作对InnoDB是不起作用的,解决方法是首先把InnoDB表改成MyISAM表,导入数据后再改成InnoDB表,但是对于使用的额外的InnoDB特性(例如外键)的表不适用。
6、InnoDB表的行锁也不是绝对的,如果在执行一个SQL语句时MySQL不能确定要扫描的范围,InnoDB表同样会锁全表,例如update table set num=1 where name like “%aaa%”

MyISAM和InnoDB优化:
key_buffer_size - 这对MyISAM表来说非常重要。如果只是使用MyISAM表,可以把它设置为可用内存的 30-40%。合理的值取决于索引大小、数据量以及负载 -- 记住,MyISAM表会使用操作系统的缓存来缓存数据,因此需要留出部分内存给它们,很多情况下数据比索引大多了。尽管如此,需要总是检查是否所有的 key_buffer 都被利用了 -- .MYI 文件只有 1GB,而 key_buffer 却设置为 4GB 的情况是非常少的。这么做太浪费了。如果你很少使用MyISAM表,那么也保留低于 16-32MB 的 key_buffer_size 以适应给予磁盘的临时表索引所需。
innodb_buffer_pool_size - 这对Innodb表来说非常重要。Innodb相比MyISAM表对缓冲更为敏感。MyISAM可以在默认的 key_buffer_size 设置下运行的可以,然而Innodb在默认的 innodb_buffer_pool_size 设置下却跟蜗牛似的。由于Innodb把数据和索引都缓存起来,无需留给操作系统太多的内存,因此如果只需要用Innodb的话则可以设置它高达 70-80% 的可用内存。一些应用于 key_buffer 的规则有 -- 如果你的数据量不大,并且不会暴增,那么无需把
innodb_additional_pool_size - 这个选项对性能影响并不太多,至少在有差不多足够内存可分配的操作系统上是这样。不过如果你仍然想设置为 20MB(或者更大),因此就需要看一下Innodb其他需要分配的内存有多少。
innodb_log_file_size 在高写入负载尤其是大数据集的情况下很重要。这个值越大则性能相对越高,但是要注意到可能会增加恢复时间。我经常设置为 64-512MB,跟据服务器大小而异。
innodb_log_buffer_size 默认的设置在中等强度写入负载以及较短事务的情况下,服务器性能还可以。如果存在更新操作峰值或者负载较大,就应该考虑加大它的值了。如果它的值设置太高了,可能会浪费内存 -- 它每秒都会刷新一次,因此无需设置超过1秒所需的内存空间。通常 8-16MB 就足够了。越小的系统它的值越小。
innodb_flush_logs_at_trx_commit 是否为Innodb比MyISAM慢1000倍而头大?看来也许你忘了修改这个参数了。默认值是 1,这意味着每次提交的更新事务(或者每个事务之外的语句)都会刷新到磁盘中,而这相当耗费资源,尤其是没有电池备用缓存时。很多应用程序,尤其是从 MyISAM转变过来的那些,把它的值设置为 2 就可以了,也就是不把日志刷新到磁盘上,而只刷新到操作系统的缓存上。日志仍然会每秒刷新到磁盘中去,因此通常不会丢失每秒1-2次更新的消耗。如果设置为 0 就快很多了,不过也相对不安全了 -- MySQL服务器崩溃时就会丢失一些事务。设置为 2 指挥丢失刷新到操作系统缓存的那部分事务。
table_cache -- 打开一个表的开销可能很大。例如MyISAM把MYI文件头标志该表正在使用中。你肯定不希望这种操作太频繁,所以通常要加大缓存数量,使得足以最大限度地缓存打开的表。它需要用到操作系统的资源以及内存,对当前的硬件配置来说当然不是什么问题了。如果你有200多个表的话,那么设置为 1024 也许比较合适(每个线程都需要打开表),如果连接数比较大那么就加大它的值。我曾经见过设置为 100,000 的情况。
thread_cache -- 线程的创建和销毁的开销可能很大,因为每个线程的连接/断开都需要。我通常至少设置为 16。如果应用程序中有大量的跳跃并发连接并且 Threads_Created 的值也比较大,那么我就会加大它的值。它的目的是在通常的操作中无需创建新线程。
query_cache -- 如果你的应用程序有大量读,而且没有应用程序级别的缓存,那么这很有用。不要把它设置太大了,因为想要维护它也需要不少开销,这会导致MySQL变慢。通常设置为 32-512Mb。设置完之后最好是跟踪一段时间,查看是否运行良好。在一定的负载压力下,如果缓存命中率太低了,就启用它。
sort_buffer_size --如果你只有一些简单的查询,那么就无需增加它的值了,尽管你有 64GB 的内存。搞不好也许会降低性能。

mysql显示SQL语句执行时间

查看 MySQL 語法 詳細執行時間 與 CPU/記憶體使用量: MySQL Query Profiler

MySQL 的 SQL 語法調整主要都是使用 EXPLAIN , 但是這個並沒辦法知道詳細的 Ram(Memory)/CPU 等使用量.

於 MySQL 5.0.37 以上開始支援 MySQL Query Profiler, 可以查詢到此 SQL 會執行多少時間, 並看出 CPU/Memory 使用量, 執行過程中 System lock, Table lock 花多少時間等等.

MySQL Query Profile 詳細介紹可見: Using the New MySQL Query Profiler (2007.04.05 發表)

效能分析主要分下述三種(轉載自上篇):

Bottleneck analysis - focuses on answering the questions: What is my database server waiting on; what is a user connection waiting on; what is a piece of SQL code waiting on?
Workload analysis - examines the server and who is logged on to determine the resource usage and activity of each.
Ratio-based analysis - utilizes a number of rule-of-thumb ratios to gauge performance of a database, user connection, or piece of code.
MySQL Query Profile 使用方法
啟動
mysql> set profiling=1; # 此命令於 MySQL 會於 information_schema 的 database 建立一個 PROFILING 的 table 來紀錄.
SQL profiles show
mysql> show profiles; # 從啟動之後所有語法及使用時間, 含錯誤語法都會紀錄.
ex: (root@localhost) [test]> show profiles; # 注意 Query_ID, 下面執行時間統計等, 都是依 Query_ID 在紀錄
+----------+------------+---------------------------+ | Query_ID | Duration | Query | +----------+------------+---------------------------+ | 1 | 0.00090400 | show profile for query 1 | | 2 | 0.00008700 | select * from users | | 3 | 0.00183800 | show tables | | 4 | 0.00027600 | mysql> show profiles | +----------+------------+---------------------------+
查詢所有花費時間加總
mysql> select sum(duration) from information_schema.profiling where query_id=1; # Query ID = 1
+---------------+ | sum(duration) | +---------------+ | 0.000447 | +---------------+
查詢各執行階段花費多少時間
mysql> show profile for query 1; # Query ID = 1
+--------------------+------------+ | Status | Duration | +--------------------+------------+ | (initialization) | 0.00006300 | | Opening tables | 0.00001400 | | System lock | 0.00000600 | | Table lock | 0.00001000 | | init | 0.00002200 | | optimizing | 0.00001100 | | statistics | 0.00009300 | | preparing | 0.00001700 | | executing | 0.00000700 | | Sending data | 0.00016800 | | end | 0.00000700 | | query end | 0.00000500 | | freeing items | 0.00001200 | | closing tables | 0.00000800 | | logging slow query | 0.00000400 | +--------------------+------------+
查詢各執行階段花費的各種資源列表
mysql> show profile cpu for query 1; # Query ID = 1
+--------------------------------+----------+----------+------------+ | Status | Duration | CPU_user | CPU_system | +--------------------------------+----------+----------+------------+ | (initialization) | 0.000007 | 0 | 0 | | checking query cache for query | 0.000071 | 0 | 0 | | Opening tables | 0.000024 | 0 | 0 | | System lock | 0.000014 | 0 | 0 | | Table lock | 0.000055 | 0.001 | 0 | | init | 0.000036 | 0 | 0 | | optimizing | 0.000013 | 0 | 0 | | statistics | 0.000021 | 0 | 0 | | preparing | 0.00002 | 0 | 0 | | executing | 0.00001 | 0 | 0 | | Sending data | 0.015072 | 0.011998 | 0 | | end | 0.000021 | 0 | 0 | | query end | 0.000011 | 0 | 0 | | storing result in query cache | 0.00001 | 0 | 0 | | freeing items | 0.000018 | 0 | 0 | | closing tables | 0.000019 | 0 | 0 | | logging slow query | 0.000009 | 0 | 0 | +--------------------------------+----------+----------+------------+
mysql> show profile IPC for query 1;
+--------------------------------+----------+---------------+-------------------+ | Status | Duration | Messages_sent | Messages_received | +--------------------------------+----------+---------------+-------------------+ | (initialization) | 0.000007 | 0 | 0 | | checking query cache for query | 0.000071 | 0 | 0 | | Opening tables | 0.000024 | 0 | 0 | | System lock | 0.000014 | 0 | 0 | | Table lock | 0.000055 | 0 | 0 | | init | 0.000036 | 0 | 0 | | optimizing | 0.000013 | 0 | 0 | | statistics | 0.000021 | 0 | 0 | | preparing | 0.00002 | 0 | 0 | | executing | 0.00001 | 0 | 0 | | Sending data | 0.015072 | 0 | 0 | | end | 0.000021 | 0 | 0 | | query end | 0.000011 | 0 | 0 | | storing result in query cache | 0.00001 | 0 | 0 | | freeing items | 0.000018 | 0 | 0 | | closing tables | 0.000019 | 0 | 0 | | logging slow query | 0.000009 | 0 | 0 | +--------------------------------+----------+---------------+-------------------+
其它屬性列表
ALL - displays all information
BLOCK IO - displays counts for block input and output operations
CONTEXT SWITCHES - displays counts for voluntary and involuntary context switches
IPC - displays counts for messages sent and received
MEMORY - is not currently implemented
PAGE FAULTS - displays counts for major and minor page faults
SOURCE - displays the names of functions from the source code, together with the name and line number of the file in which the function occurs
SWAPS - displays swap counts
設定 Profiling 存的 Size
mysql> show variables where variable_name='profiling_history_size'; # 預設是 15筆
關閉
mysql> set profiling=0;
文章出处:DIY部落(http://www.diybl.com/course/7_da ... 0090810/168559.html)

升级mysql,支持分区功能

MYSQL分区功能,该功能只在5.1中增加,所有要从以前的5.0升级至5.1.

刚开始编译,
./configure   --prefix=/opt/mysql  --localstatedir=/mysql/data

结果安装完以后登入MSYQL显示不支持分区

mysql> SHOW VARIABLES LIKE ‘%partition%’;
+——————-+——-+
| Variable_name     | Value |
+——————-+——-+
| have_partitioning | NO    |
+——————-+——-+


后来再仔细看了下帮助,发现MYSQL 5.1.23默认好像不安装分区,这个功能只是当作一个插件

再重新编译

./configure  --prefix=/opt/mysql  --localstatedir=/mysql/data   --with-plugins=partition

make && make install

再查看发现以支持分区功能

mysql> SHOW VARIABLES LIKE ‘%partition%’;
+——————-+——-+
| Variable_name     | Value |
+——————-+——-+
| have_partitioning | YES   |
+——————-+——-+

2台mysql实现HA的架构,并同步启用replication模式

一、背景
当前大多网站采用Linux +MYSQL+APACHE+PHP这种经典配置,如何防止单点失败造成的整个网站的不可用是网站管理者必须要考虑的问题,其中数据库的高可用性(Database server’s high availability)是重中之重。
对于数据库的高可用性,各商业软件的厂商都有各自的解决方案,比如Oracle OPS server和IBM DB2 (share-nothing architecture)。最近MYSQL AB也发布了MYSQL cluster 软件,使用与IBM DB2类似的技术。
MySQL cluster可能会是日后最理想的方案,但是从资源、可集成度方面考虑,给出一个简单实用的方案借鉴。本文介绍的是性价比比较高的一种: 使用Heartbeat 2.0配置Linux高可用性集群,同时使用Heartbeat也可以实现简单的web集群。

二、实现原理
通过Linux HA 软件 heartbeat 实现IP的自动漂移,即当一台服务器宕机后,浮动IP(整个cluster的对外IP )自动漂移到另外一台服务器。

通过Mysql自身的replication 实现不同机器上多个数据库的同步整体性能此方案将会降低MYSQL 1%左右的性能,可用性及数据安全性将大有提高,同时服务器的切换对终端使用者是透明的,终端应用不需要进行更改。
所需硬件:
安装有双网卡的配置大致相同的服务器或工作机两台 一条交叉网线(用于双机对连的心跳线)

所需软件:
Linux HA 软件 heartbeat (只支持两个节点),安装盘里集成的有这个软件的rpm包
软件主页:http://www.linux-ha.org/
Mysql软件
软件主页:http://www.mysql.com/

安装步骤:
1、安装MYSQL
#cd /opt
#useradd mysql -d /usr/local/mysql -s /sbin/nologin
#tar -zxvf mysql-5.1.29-rc.tar.gz
#cd mysql-5.1.29-rc
# ./configure --prefix=/usr/local/mysql --with-mysqld-user=mysql --without-debug --with-big-tables --with-charset=gbk --with-collation=gbk_chinese_ci --with-extra-charsets=all --with-pthread --enable-thread-safe-client --enable-assembler --without-isam --without-innodb --without-ndb-debug --with-mysqli
#make && make install
#/usr/local/mysql/bin/mysql_install_db        //初始化数据库
#chown -R mysql.mysql /usr/local/mysql/     //设置目录宿主
#cp /usr/local/mysql/share/mysql/my-medium.cnf /etc/my.cnf   //复制配置文件
#cp support-files/mysql.server /etc/rc.d/init.d/mysqld    //设置启动文件  
#chmod 700 /etc/rc.d/init.d/mysqld                        
#/usr/local/mysql/bin/mysqld_safe --user=mysql &         
#/etc/rc.d/init.d/mysqld start
在启动mysql时报错


查看日志发现以下错误:
  


将/etc/my.cnf文件中的skip-federated注释掉即可
#chkconfig --add mysqld
#chkconfig --level 2345 mysqld on
#ln -s /usr/local/mysql/bin/mysql /sbin/mysql
#ln -s /usr/local/mysql/bin/mysqladmin /sbin/mysqladmin
#mysqladmin –u root password 1q2w3e        //设置root 密码
#配置库文件搜索路径
#echo "/usr/local/mysql/lib/mysql" >> /etc/ld.so.conf
#ldconfig
#添加/usr/local/mysql/bin到环境变量PATH中
#export PATH=$PATH:/usr/local/mysql/bin
mysqlB也按以上的方式进行安装
2、mysqlA设置
设置同步数据库:
   在这里我就用test数据库来做测试,在test数据库里新建一个data表,并添加一些数据,具体操作如下:
#mysql –u root –p1q2w3e


mysql> create table data(name VARCHAR(20), address VARCHAR(50), phone VARCHAR(20));
mysql>insert into data(name,address,phone) values('jhone','beijing','138000000');
mysql>select * from data;
设置数据库同步帐户:
mysql>GRANT REPLICATION SLAVE,REPLICATION CLIENT,RELOAD,SUPER ON *.* TO 'backup'@'10.0.0.2' IDENTIFIED BY 'qawsed';
(授与从10.0.0.2主机上登录用户backup数据复制权限,4.02版本以前用:GRANT FILE ON *.* TO [email protected] IDENTIFIED BY 'qawsed';)
mysql>flush privileges;
  

修改配置文件:
停止mysql服务
[root@mysqlA opt]#service mysqld stop
建立用于更新日志的目录,并给于mysql的权限
[root@mysqlA opt]# mkdir /var/log/mysql
[root@mysqlA opt]# chown mysql.mysql /var/log/mysql/
更改Mysql配置文件/etc/my.cnf
[root@mysqlA opt]# vi /etc/my.cnf
server-id=1                 //服务器ID号
log-bin=mysql-bin
log-bin=/var/log/mysql/updatelog   //启用更新日志
binlog-do-db=test       //表示需要备份的数据库是test这个数据库
replicate-same-server-id
master-host=10.0.0.2      //指定主服务器IP
master-user=backup       //指定在主服务器上可以同步的帐号
master-password=qawsed //指定帐号对应的密码
master-connect-retry=60  //断点重试间隔为60秒
replicate-do-db=test      //表示同步test数据库
binlog-ignore-db=mysql   //不同步mysql数据库
把test进行备份
[root@mysqlA opt]# service mysqld start    //先启动mysql
[root@mysqlA opt]# /usr/local/mysql/bin/mysqldump -h localhost -u root -p1q2w3e test >test.sql
[root@mysqlA opt]#scp test.sql [email protected]:/opt     //(将test.sql复制到mysqlB的/opt目录下)
至此mysqlA服务器上有关mysql的设置已完成,下一步开始配置mysqlB
设置mysqlB
设置数据库同步帐户:
mysql>GRANT REPLICATION SLAVE,REPLICATION CLIENT,RELOAD,SUPER ON *.* TO 'backup'@'10.0.0.1' IDENTIFIED BY 'qawsed';
(授与从10.0.0.1主机上登录用户backup数据复制权限,4.02版本以前用:GRANT FILE ON *.* TO [email protected] IDENTIFIED BY 'qawsed';)
mysql>flush privileges;
修改配置文件:
停止mysql服务
[root@mysqlA opt]#service mysqld stop
建立用于更新日志的目录,并给于mysql的权限
[root@mysqlA opt]# mkdir /var/log/mysql
[root@mysqlA opt]# chown mysql.mysql /var/log/mysql/
更改Mysql配置文件/etc/my.cnf
[root@mysqlA opt]# vi /etc/my.cnf
server-id=2                 //服务器ID号
log-bin=mysql-bin
log-bin=/var/log/mysql/updatelog   //启用更新日志
binlog-do-db=test       //表示需要备份的数据库是test这个数据库
replicate-same-server-id
master-host=10.0.0.1      //指定主服务器IP
master-user=backup       //指定在主服务器上可以同步的帐号
master-password=qawsed //指定帐号对应的密码
master-connect-retry=60  //断点重试间隔为60秒
replicate-do-db=test      //表示同步test数据库
binlog-ignore-db=mysql   //不同步mysql数据库

还原从mysqlA备份过的test.sql
#service mysqld start
# mysql -u root -p1q2w3e test <test.sql
重启两边的mysql服务
查询配置
Show Slave status:此处Slave_IO_Running ,Slave_SQL_Running 都应该是yes,表示从库的I/O,Slave_SQL线程都正确开启.
在Mysql中可通过以下命令来查看主从状态
show master status 查看master状态
show slave status 查看slave状态
show processlist G 查看当前进程
stop slave 暂时停止slave进程
start slave 开始slave进程
在primary服务器上 MySQL命令符下输入:
mysql>show master status;

mysql>show slave status\G;


如果出现以错误:



解决办法:在/etc/my.cnf 的[mysqld]下面加上replicate-same-server-id参数


到此mysql的双机互备已基本完成,在两台服务器的mysql数据库中任意添加数据,都可以同步到对端服务器上
安装配置heartbeat:
Heartbeat下载地址如下:
http://download.opensuse.org/repositories/server:/ha-clustering:/lha-2.1/CentOS_5/i386/
下载对应的版本,在此我的系统是centos 5.2
rpm -ivh libnet-1.1.2.1-2.1.i386.rpm
rpm -ivh heartbeat-pils-2.1.4-2.1.i386.rpm
rpm -ivh --nodeps heartbeat-stonith-2.1.4-2.1.i386.rpm
rpm -ivh --nodeps heartbeat-2.1.4-2.1.i386.rpm
rpm -ivh --nodeps heartbeat-devel-2.1.4-2.1.i386.rpm
rpm -ivh --nodeps heartbeat-ldirectord-2.1.4-2.1.i386.rpm
rpm -ivh ipvsadm-1.24-8.1.i386.rpm
配置 HA的各配置文件:
复制配置文件
[root@mysqlA opt]# cp /usr/share/doc/packages/heartbeat/authkeys /etc/ha.d/
[root@mysqlA opt]# cp /usr/share/doc/packages/heartbeat/haresources /etc/ha.d/
[root@mysqlA opt]# cp /usr/share/doc/packages/heartbeat/ha.cf /etc/ha.d/
配置心跳的加密方式:authkeys
[root@mysqlA opt]#vi /etc/ha.d/authkeys
#如果使用双机对联线(双绞线),可以配置如下:
auth 1
1 crc
#存盘退出,然后
[root@mysqlA opt]#chmod 600 authkeys

配置心跳的监控:haresources
[root@mysqlA opt]#vi /etc/ha.d/haresources
#各主机这部分应完全相同。
mysqlA IPaddr::192.168.8.155 ipvsadm mysqld
[root@mysqlA opt]# mkdir -p /var/log/ha_log
[root@mysqlA opt]#chmod 777 /var/log/ha_log/

配置心跳的配置文件:ha.cf
[root@mysqlA opt]#vi /etc/ha.d/ha.cf
logfile /var/log/ha_log/ha-log.log   ## ha的日志文件记录位置。如没有该目录,则需要手动添加
bcast eth1     ##使用eht1做心跳监测
keepalive 2    ##设定心跳(监测)时间时间为2秒
warntime 10
deadtime 30
initdead 120
hopfudge 1
udpport 694    ##使用udp端口694 进行心跳监测
auto_failback on
node mysqlA  ##节点1,必须要与 uname -n 指令得到的结果一致。
node mysqlB  ##节点2
ping 192.168.8.100   ##通过ping 网关来监测心跳是否正常。
respawn hacluster /usr/lib/heartbeat/ipfail
apiauth ipfail gid=root uid=root
debugfile /var/log/ha_log/ha-debug.log

设置ipvsadm的巡回监测
[root@mysqlA opt]#ipvsadm -A -t 192.168.8.155:3306 -s rr
[root@mysqlA opt]#ipvsadm -a -t 192.168.8.155:3306 -r 192.168.8.151:3306 -m
[root@mysqlA opt]#ipvsadm -a -t 192.168.8.155:3306 -r 192.168.8.152:3306 –m

执行后进行监测:
[root@mysqlA opt]#ipvsadm –list






HA服务的启动、关闭以及测试
启动HA: service heartbeat start
关闭HA; service heartbeat stop
进行监控: service heartbeat status


防火墙设置
heartbeat 默认使用udp 694端口进行心跳监测。 如果系统有使用iptables 做防火墙,应记住把这个端口打开。
iptables -I INPUT -p udp --dport 694 -j ACCEPT

到此mysql双机互备已基本完成,不管哪一台服务器菪机都不会影响mysql的正常运行

监视查询缓存

在启用查询缓存之后,重要的是要理解它是否得到了有效的使用。MySQL 有几个可以查看的变量,可以用来了解缓存中的情况。清单 2 给出了缓存的状态。

清单 2. 显示查询缓存的统计信息
+-------------------------+-----------+
| Variable_name           | Value     |
+-------------------------+-----------+
| Qcache_free_blocks      | 37753     |
| Qcache_free_memory      | 169738824 |
| Qcache_hits             | 3249809   |
| Qcache_inserts          | 3730811   |
| Qcache_lowmem_prunes    | 0         |
| Qcache_not_cached       | 101648    |
| Qcache_queries_in_cache | 62077     |
| Qcache_total_blocks     | 162003    |
+-------------------------+-----------+

这些项的解释如表 1 所示。

表 1. MySQL 查询缓存变量
变量名说明
Qcache_free_blocks 缓存中相邻内存块的个数。数目大说明可能有碎片。FLUSH QUERY CACHE 会对缓存中的碎片进行整理,从而得到一个空闲块。
Qcache_free_memory 缓存中的空闲内存。
Qcache_hits 每次查询在缓存中命中时就增大。
Qcache_inserts 每次插入一个查询时就增大。命中次数除以插入次数就是不中比率;用 1 减去这个值就是命中率。在上面这个例子中,大约有 87% 的查询都在缓存中命中。
Qcache_lowmem_prunes 缓存出现内存不足并且必须要进行清理以便为更多查询提供空间的次数。这个数字最好长时间来看;如果这个数字在不断增长,就表示可能碎片非常严重,或者内存很少。(上面的 free_blocks 和 free_memory 可以告诉您属于哪种情况)。
Qcache_not_cached 不适合进行缓存的查询的数量,通常是由于这些查询不是 SELECT 语句。
Qcache_queries_in_cache 当前缓存的查询(和响应)的数量。
Qcache_total_blocks 缓存中块的数量。

同一台MySQL服务器启动多个端口

wget  二进制的源码包

解压
cat INSTLL-BINARY
根据说明操作,具体安装步骤请看 http://mylinux.5d6d.com/thread-7-1-1.html
其中1-4步骤是相同的,第5步,也是最重要的一步,要初始化数据库
因为是多个端口,所以要根据配置文件来初始化多个数据库
比如说有2个端口
则要运行两次
./scripts/mysql_install_db --datadir=/home/mysql1 --userdir=mysql
./scripts/mysql_install_db --datadir=/home/mysql2 --userdir=mysql

配置文件要编辑成这样
[mysqld0]
port            = 3300
socket          = /tmp/mysql0.sock
pid-file        = /home/mysql0/localhost.localdomain0.pid
datadir         = /home/mysql0
#log            = /data/mysql0/mysql0.log
user            = mysql
skip-locking
skip-name-resolve
skip-bdb
skip-innodb
key_buffer = 128M
max_allowed_packet = 1M
table_cache = 864
sort_buffer_size = 1M
read_buffer_size = 512K
read_rnd_buffer_size = 1M
myisam_sort_buffer_size = 32M
thread_cache_size = 16
query_cache_size = 32M
thread_concurrency = 8
#skip-networking
wait_timeout=8
max_connections=512
max_connect_errors = 10000000
max_user_connections=20
#slow_queries=/data/mysql0slowquer.sql
#log_slow_queries=/data/mysql0slowquer.sql
long_query_time=3
log-bin=mysql0-bin
###########################
[mysqld1]
port            = 3301
socket          = /tmp/mysql1.sock
pid-file        = /home/mysql1/localhost.localdomain1.pid
datadir         = /home/mysql1
#log            = /data/mysql1/mysql1.log
user            = mysql
skip-locking
skip-name-resolve
skip-innodb
skip-bdb
key_buffer = 128M
max_allowed_packet = 1M
table_cache = 864
sort_buffer_size = 1M
read_buffer_size = 512K
read_rnd_buffer_size = 1M
myisam_sort_buffer_size = 32M
thread_cache_size = 16
query_cache_size = 32M
thread_concurrency = 8
#skip-networking
wait_timeout=8
max_connections=512
max_connect_errors = 10000000
max_user_connections=20
#log_slow_queries=/data/mysql1slowquer.sql
long_query_time=3
log-bin=mysql1-bin
########################################

把配置文件放在 /etc/my.cnf

最后就该启动了
/usr/local/mysql/bin/mysqld_multi start 0-1  这里的0或1是根据配置文件中"[mysqld0]"来定的。

mysql 大量的 unauthenticated user(mysql 加速)

近日在数据库方面,发现异常的联机状况该状况在 mysql 下指令
Show Processlist;
可以看到问题该问题如下; : 3436942 : unauthenticated user : 192.168.0.52:49607 : : Connect : : login : : 3436943 : unauthenticated user : 192.168.0.52:49608 : : Connect : : login :   
会有非常多的 unauthenticated user 尝试做登入使用 mysql 的情况当这情况在无限制的发生时,就会照成系统的停顿在经过多方测试,以及询问官方原厂的响应下发现这属于官方一个系统上的特殊设定,亦可称呼他为 mysql 的 bug 不管连结的的方式是经过 hosts 或是 IP 的模式,他都会对 DNS 做反查mysqld 会尝试去反查 IP -> dns ,由于反查解析 过慢,无法应付快速多量的查询 反查是上层 ISP 所掌控,并不是我们可以解决及要求的在知道问题的症结点后,要解决这个问题就有相对应的方法其方法分为两个步骤,  
第一是开启 Mysqld 的时候加入特定的参数启动  
第二就是修改 mysql 的联机设定等,如下所示    

    启动参数; with --skip-name-resolve =>这样就可以关闭反查动作   
    启动参数; with --skip-name-resolve =>这样就可以关闭反查动作


      更改 my.cnf;加强以下设定     [mysqld]   datadir=/services/mysql/   socket=/tmp/mysql.sock   port=3306  set-variable = key_buffer_size=64M   set-variable = max_connections=1024  set-variable = interactive_timeout=30 ------ skip-name-resolve #这样就可以关闭反查动作   [mysqld.server]   user=mysql   basedir=/usr/local   

MySQL备份与恢复

目前 MySQL 支持的免费备份工具有:mysqldump、mysqlhotcopy,还可以用 SQL 语法进行备份:BACKUP TABLE 或者 SELECT INTO OUTFILE,又或者备份二进制日志(binlog),还可以是直接拷贝数据文件和相关的配置文件。MyISAM 表是保存成文件的形式,因此相对比较容易备份,上面提到的几种方法都可以使用。Innodb 所有的表都保存在同一个数据文件 ibdata1 中(也可能是多个文件,或者是独立的表空间文件),相对来说比较不好备份,免费的方案可以是拷贝数据文件、备份 binlog,或者用 mysqldump。

1、mysqldump
1.1 备份
mysqldump 是采用SQL级别的备份机制,它将数据表导成 SQL 脚本文件,在不同的 MySQL 版本之间升级时相对比较合适,这也是最常用的备份方法。
现在来讲一下 mysqldump 的一些主要参数:

--compatible=name
它告诉 mysqldump,导出的数据将和哪种数据库或哪个旧版本的 MySQL 服务器相兼容。值可以为 ansi、mysql323、mysql40、postgresql、oracle、mssql、db2、maxdb、no_key_options、no_tables_options、no_field_options 等,要使用几个值,用逗号将它们隔开。当然了,它并不保证能完全兼容,而是尽量兼容。

--complete-insert,-c
导出的数据采用包含字段名的完整 INSERT 方式,也就是把所有的值都写在一行。这么做能提高插入效率,但是可能会受到 max_allowed_packet 参数的影响而导致插入失败。因此,需要谨慎使用该参数,至少我不推荐。

--default-character-set=charset
指定导出数据时采用何种字符集,如果数据表不是采用默认的 latin1 字符集的话,那么导出时必须指定该选项,否则再次导入数据后将产生乱码问题。

--disable-keys
告诉 mysqldump 在 INSERT 语句的开头和结尾增加 /*!40000 ALTER TABLE table DISABLE KEYS */; 和 /*!40000 ALTER TABLE table ENABLE KEYS */; 语句,这能大大提高插入语句的速度,因为它是在插入完所有数据后才重建索引的。该选项只适合 MyISAM 表。

--extended-insert = true|false
默认情况下,mysqldump 开启 --complete-insert 模式,因此不想用它的的话,就使用本选项,设定它的值为 false 即可。

--hex-blob
使用十六进制格式导出二进制字符串字段。如果有二进制数据就必须使用本选项。影响到的字段类型有 BINARY、VARBINARY、BLOB。

--lock-all-tables,-x
在开始导出之前,提交请求锁定所有数据库中的所有表,以保证数据的一致性。这是一个全局读锁,并且自动关闭 --single-transaction 和 --lock-tables 选项。

--lock-tables
它和 --lock-all-tables 类似,不过是锁定当前导出的数据表,而不是一下子锁定全部库下的表。本选项只适用于 MyISAM 表,如果是 Innodb 表可以用 --single-transaction 选项。

--no-create-info,-t
只导出数据,而不添加 CREATE TABLE 语句。

--no-data,-d
不导出任何数据,只导出数据库表结构。

--opt
这只是一个快捷选项,等同于同时添加 --add-drop-tables --add-locking --create-option --disable-keys --extended-insert --lock-tables --quick --set-charset 选项。本选项能让 mysqldump 很快的导出数据,并且导出的数据能很快导回。该选项默认开启,但可以用 --skip-opt 禁用。注意,如果运行 mysqldump 没有指定 --quick 或 --opt 选项,则会将整个结果集放在内存中。如果导出大数据库的话可能会出现问题。

--quick,-q
该选项在导出大表时很有用,它强制 mysqldump 从服务器查询取得记录直接输出而不是取得所有记录后将它们缓存到内存中。

--routines,-R
导出存储过程以及自定义函数。

--single-transaction
该选项在导出数据之前提交一个 BEGIN SQL语句,BEGIN 不会阻塞任何应用程序且能保证导出时数据库的一致性状态。它只适用于事务表,例如 InnoDB 和 BDB。
本选项和 --lock-tables 选项是互斥的,因为 LOCK TABLES 会使任何挂起的事务隐含提交。
要想导出大表的话,应结合使用 --quick 选项。

--triggers
同时导出触发器。该选项默认启用,用 --skip-triggers 禁用它。

其他参数详情请参考手册,我通常使用以下 SQL 来备份 MyISAM 表:

/usr/local/mysql/bin/mysqldump -uyejr -pyejr \
--default-character-set=utf8 --opt --extended-insert=false \
--triggers -R --hex-blob -x db_name > db_name.sql
使用以下 SQL 来备份 Innodb 表:

/usr/local/mysql/bin/mysqldump -uyejr -pyejr \
--default-character-set=utf8 --opt --extended-insert=false \
--triggers -R --hex-blob --single-transaction db_name > db_name.sql
另外,如果想要实现在线备份,还可以使用 --master-data 参数来实现,如下:

/usr/local/mysql/bin/mysqldump -uyejr -pyejr \
--default-character-set=utf8 --opt --master-data=1 \
--single-transaction --flush-logs db_name > db_name.sql
它只是在一开始的瞬间请求锁表,然后就刷新binlog了,而后在导出的文件中加入CHANGE MASTER 语句来指定当前备份的binlog位置,如果要把这个文件恢复到slave里去,就可以采用这种方法来做。

1.2 还原
用 mysqldump 备份出来的文件是一个可以直接倒入的 SQL 脚本,有两种方法可以将数据导入。

直接用 mysql 客户端
例如:

/usr/local/mysql/bin/mysql -uyejr -pyejr db_name < db_name.sql

用 SOURCE 语法
其实这不是标准的 SQL 语法,而是 mysql 客户端提供的功能,例如:

SOURCE /tmp/db_name.sql;
这里需要指定文件的绝对路径,并且必须是 mysqld 运行用户(例如 nobody)有权限读取的文件。

2、 mysqlhotcopy
2.1 备份
mysqlhotcopy 是一个 PERL 程序,最初由Tim Bunce编写。它使用 LOCK TABLES、FLUSH TABLES 和 cp 或 scp 来快速备份数据库。它是备份数据库或单个表的最快的途径,但它只能运行在数据库文件(包括数据表定义文件、数据文件、索引文件)所在的机器上。mysqlhotcopy 只能用于备份 MyISAM,并且只能运行在 类Unix 和 NetWare 系统上。

mysqlhotcopy 支持一次性拷贝多个数据库,同时还支持正则表达。以下是几个例子:

root#/usr/local/mysql/bin/mysqlhotcopy -h=localhost -u=yejr -p=yejr \
db_name /tmp (把数据库目录 db_name 拷贝到 /tmp 下)
root#/usr/local/mysql/bin/mysqlhotcopy -h=localhost -u=yejr -p=yejr \
db_name_1 ... db_name_n /tmp
root#/usr/local/mysql/bin/mysqlhotcopy -h=localhost -u=yejr -p=yejr \
db_name./regex/ /tmp
更详细的使用方法请查看手册,或者调用下面的命令来查看 mysqlhotcopy 的帮助:

perldoc /usr/local/mysql/bin/mysqlhotcopy
注意,想要使用 mysqlhotcopy,必须要有 SELECT、RELOAD(要执行 FLUSH TABLES) 权限,并且还必须要能够有读取 datadir/db_name 目录的权限。

2.2 还原
mysqlhotcopy 备份出来的是整个数据库目录,使用时可以直接拷贝到 mysqld 指定的 datadir (在这里是 /usr/local/mysql/data/)目录下即可,同时要注意权限的问题,如下例:

root#cp -rf db_name /usr/local/mysql/data/
root#chown -R nobody:nobody /usr/local/mysql/data/ (将 db_name 目录的属主改成 mysqld 运行用户)

3、 SQL 语法备份
3.1 备份
BACKUP TABLE 语法其实和 mysqlhotcopy 的工作原理差不多,都是锁表,然后拷贝数据文件。它能实现在线备份,但是效果不理想,因此不推荐使用。它只拷贝表结构文件和数据文件,不同时拷贝索引文件,因此恢复时比较慢。
例子:

BACK TABLE tbl_name TO '/tmp/db_name/';
注意,必须要有 FILE 权限才能执行本SQL,并且目录 /tmp/db_name/ 必须能被 mysqld 用户可写,导出的文件不能覆盖已经存在的文件,以避免安全问题。

SELECT INTO OUTFILE 则是把数据导出来成为普通的文本文件,可以自定义字段间隔的方式,方便处理这些数据。
例子:

SELECT * INTO OUTFILE '/tmp/db_name/tbl_name.txt' FROM tbl_name;
注意,必须要有 FILE 权限才能执行本SQL,并且文件 /tmp/db_name/tbl_name.txt 必须能被 mysqld 用户可写,导出的文件不能覆盖已经存在的文件,以避免安全问题。

3.2 恢复
用 BACKUP TABLE 方法备份出来的文件,可以运行 RESTORE TABLE 语句来恢复数据表。
例子:

RESTORE TABLE FROM '/tmp/db_name/';
权限要求类似上面所述。

用 SELECT INTO OUTFILE 方法备份出来的文件,可以运行 LOAD DATA INFILE 语句来恢复数据表。
例子:

LOAD DATA INFILE '/tmp/db_name/tbl_name.txt' INTO TABLE tbl_name;
权限要求类似上面所述。倒入数据之前,数据表要已经存在才行。如果担心数据会发生重复,可以增加 REPLACE 关键字来替换已有记录或者用 IGNORE 关键字来忽略他们。

4、 启用二进制日志(binlog)
采用 binlog 的方法相对来说更灵活,省心省力,而且还可以支持增量备份。

启用 binlog 时必须要重启 mysqld。首先,关闭 mysqld,打开 my.cnf,加入以下几行:

server-id        = 1
log-bin                = binlog
log-bin-index        = binlog.index
然后启动 mysqld 就可以了。运行过程中会产生 binlog.000001 以及 binlog.index,前面的文件是 mysqld 记录所有对数据的更新操作,后面的文件则是所有 binlog 的索引,都不能轻易删除。关于 binlog 的信息请查看手册。

需要备份时,可以先执行一下 SQL 语句,让 mysqld 终止对当前 binlog 的写入,就可以把文件直接备份,这样的话就能达到增量备份的目的了:

FLUSH LOGS;如果是备份复制系统中的从服务器,还应该备份 master.info 和 relay-log.info 文件。

备份出来的 binlog 文件可以用 MySQL 提供的工具 mysqlbinlog 来查看,如:

/usr/local/mysql/bin/mysqlbinlog /tmp/binlog.000001
该工具允许你显示指定的数据库下的所有 SQL 语句,并且还可以限定时间范围,相当的方便,详细的请查看手册。

恢复时,可以采用类似以下语句来做到:

/usr/local/mysql/bin/mysqlbinlog /tmp/binlog.000001 | mysql -uyejr -pyejr db_name
把 mysqlbinlog 输出的 SQL 语句直接作为输入来执行它。

如果你有空闲的机器,不妨采用这种方式来备份。由于作为 slave 的机器性能要求相对不是那么高,因此成本低,用低成本就能实现增量备份而且还能分担一部分数据查询压力,何乐而不为呢?

5、 直接备份数据文件
相较前几种方法,备份数据文件最为直接、快速、方便,缺点是基本上不能实现增量备份。为了保证数据的一致性,需要在靠背文件前,执行以下 SQL 语句:

FLUSH TABLES WITH READ LOCK;也就是把内存中的数据都刷新到磁盘中,同时锁定数据表,以保证拷贝过程中不会有新的数据写入。这种方法备份出来的数据恢复也很简单,直接拷贝回原来的数据库目录下即可。

注意,对于 Innodb 类型表来说,还需要备份其日志文件,即 ib_logfile* 文件。因为当 Innodb 表损坏时,就可以依靠这些日志文件来恢复。

6、 备份策略
对于中等级别业务量的系统来说,备份策略可以这么定:第一次全量备份,每天一次增量备份,每周再做一次全量备份,如此一直重复。而对于重要的且繁忙的系统来说,则可能需要每天一次全量备份,每小时一次增量备份,甚至更频繁。为了不影响线上业务,实现在线备份,并且能增量备份,最好的办法就是采用主从复制机制(replication),在 slave 机器上做备份。

7、 数据维护和灾难恢复
作为一名DBA(我目前还不是,呵呵),最重要的工作内容之一是保证数据表能安全、稳定、高速使用。因此,需要定期维护你的数据表。以下 SQL 语句就很有用:

CHECK TABLE 或 REPAIR TABLE,检查或维护 MyISAM 表
OPTIMIZE TABLE,优化 MyISAM 表
ANALYZE TABLE,分析 MyISAM 表
当然了,上面这些命令起始都可以通过工具 myisamchk 来完成,在这里不作详述。

Innodb 表则可以通过执行以下语句来整理碎片,提高索引速度:

ALTER TABLE tbl_name ENGINE = Innodb;
这其实是一个 NULL 操作,表面上看什么也不做,实际上重新整理碎片了。

通常使用的 MyISAM 表可以用上面提到的恢复方法来完成。如果是索引坏了,可以用 myisamchk 工具来重建索引。而对于 Innodb 表来说,就没这么直接了,因为它把所有的表都保存在一个表空间了。不过 Innodb 有一个检查机制叫 模糊检查点,只要保存了日志文件,就能根据日志文件来修复错误。可以在 my.cnf 文件中,增加以下参数,让 mysqld 在启动时自动检查日志文件:

innodb_force_recovery        = 4
关于该参数的信息请查看手册。

8、 总结
做好数据备份,定只好合适的备份策略,这是一个DBA所做事情的一小部分,万事开头难,就从现在开始吧!

myisamchk 修复表

今天用mysqlcheck 修复一个大表,竟然用了两个多小时,影响了正常业务,心里那个惭愧呀。后来听同事说修复大表要用myisamchk,以前不知道,学习一下。

阶段1:检查你的表

运行myisamchk *.MYI或(myisamchk -e *.MYI,如果你有更多的时间)。使用-s(沉默)选项禁止不必要的信息。

你必须只修复那些myisamchk报告有一个错误的表。对这样的表,继续到阶段2。

如果在检查时,你得到奇怪的错误(例如out of memory错误),或如果myisamchk崩溃,到阶段3。

舞台 2 :简单安全的修复

首先,试试myisamchk -r -q tbl_name(-r -q意味着“快速恢复模式”)。这将试图不接触数据文件来修复索引文件。如果数据文件包含它应有的一切和在数据文件指向正确地点的删除连接,这应该管用并且表可被修复。开始修理下一张表。否则,使用下列过程:

在继续前做数据文件的一个备份。
使用myisamchk -r tbl_name(-r意味着“恢复模式”)。这将从数据文件中删除不正确的记录和已被删除的记录并重建索引文件。
如果前面的步骤失败,使用myisamchk --safe-recover tbl_name。安全恢复模式使用一个老的恢复方法,处理常规恢复模式不行的少数情况(但是更慢)。
如果在修复时,你得到奇怪的错误(例如out of memory错误),或如果myisamchk崩溃,到阶段3。

舞台 3 :困难的修理

如果在索引文件的第一个16K块被破坏,或包含不正确的信息,或如果索引文件丢失,你只应该到这个阶段 。在这种情况下,创建一个新的索引文件是必要的。按如下这样做:

把数据文件移更安全的地方。
使用表描述文件创建新的(空)数据和索引文件:
shell> mysql db_name
mysql> DELETE FROM tbl_name;
mysql> quit

将老的数据文件拷贝到新创建的数据文件之中。(不要只是将老文件移回新文件之中;你要保留一个副本以防某些东西出错。)
回到阶段2。现在myisamchk -r -q应该工作了。(这不应该是一个无限循环)。

mysql-5.1.34 源码包下载

http://downloads.mysql.com/archives/mysql-5.1/mysql-5.1.34.tar.gz

MYSQL慢查询日志分析

mysql有一个功能就是可以log下来运行的比较慢的sql语句,默认是没有这个log的,为了开启这个功能,要修改my.cnf或者在mysql启动的时候加入一些参数。
如果在my.cnf里面修改,需增加如下几行
long_query_time = 10
log-slow-queries =

long_query_time 是指执行超过多久的sql会被log下来,这里是10秒。
log-slow-queries 设置把日志写在那里,为空的时候,系统会给慢查询日志赋予主机名,并被附加slow.log

    如果设置了参数log-long-format,那么所有没有使用索引的查询也将被记录。在文件my.cnf或my.ini中加入下面这一行可以记录这些查询

这是一个有用的日志。它对于性能的影响不大(假设所有查询都很快),并且强调了那些最需要注意的查询(丢失了索引或索引没有得到最佳应用)

# Time: 070927 8:08:52

# User@Host: root[root] @ [192.168.0.20]

# Query_time: 372 Lock_time: 136 Rows_sent: 152 Rows_examined: 263630
select id, name from manager where id in (66,10135);
这是慢查询日志中的一条,用了372秒,锁了136秒,返回152行,一共查了263630行

    如果日志内容很多,用眼睛一条一条去看会累死,mysql自带了分析的工具,使用方法如下:
命令行下,进入mysql/bin目录,输入mysqldumpslow –help或--help可以看到这个工具的参数,主要有
Usage: mysqldumpslow [ OPTS... ] [ LOGS... ]

Parse and summarize the MySQL slow query log. Options are

--verbose    verbose

--debug      debug

--help       write this text to standard output

-v           verbose

-d           debug

-s ORDER     what to sort by (t, at, l, al, r, ar etc), 'at' is default

-r           reverse the sort order (largest last instead of first)

-t NUM       just show the top n queries

-a           don't abstract all numbers to N and strings to 'S'

-n NUM       abstract numbers with at least n digits within names

-g PATTERN   grep: only consider stmts that include this string

-h HOSTNAME hostname of db server for *-slow.log filename (can be wildcard),

               default is '*', i.e. match all

-i NAME      name of server instance (if using mysql.server startup scrīpt)

-l           don't subtract lock time from total time

-s,是order的顺序,说明写的不够详细,俺用下来,包括看了代码,主要有
c,t,l,r和ac,at,al,ar,分别是按照query次数,时间,lock的时间和返回的记录数来排序,前面加了a的时倒叙
-t,是top n的意思,即为返回前面多少条的数据
-g,后边可以写一个正则匹配模式,大小写不敏感的

mysqldumpslow -s c -t 20 host-slow.log
mysqldumpslow -s r -t 20 host-slow.log

上述命令可以看出访问次数最多的20个sql语句和返回记录集最多的20个sql。
mysqldumpslow -t 10 -s t -g “left join” host-slow.log
这个是按照时间返回前10条里面含有左连接的sql语句。


一般cpu达到100%都是垃圾SQL和索引不当造成的。
只要硬盘有空余内存不是很小,就可以确定是SQL问题。

mount后做软链问题

好的,机器1:/data/archivedir       /data/stardeals/log/admin
机器2:/data1/archivedir     
目地:想把机器1的/data/stardeals/log/admin里的log,同步到机器2的/data1/archivedir上

mount没有问题,
但是在机器1上执行ln -s /data/stardeals/log/admin /data/archivedir/adminlog
在机器1上正常


但是登录到机器2上时,adminlog这个link是不可以用的
求助,哈哈

MYSQL服务器系统变量

查询MYSQL服务器系统变量:

C:\Program Files\MySQL\MySQL Server 5.0\bin> mysqld --verbose –help

通过mysqladmin命令来查询MYSQL服务器系统变量:

C:\Program Files\MySQL\MySQL Server 5.0\bin>mysqladmin -uroot -p variables > d:\init.txt
Enter password: ******

Init.txt部分内容:

+---------------------------------+----------------------------------------------------------------+
| Variable_name                   | Value                                                          |
+---------------------------------+----------------------------------------------------------------+
| auto_increment_increment        | 1                                                              |
| auto_increment_offset           | 1                                                              |
| automatic_sp_privileges         | ON                                                             |
| back_log                        | 50  
                                                                                          
…………
| version_compile_machine         | ia32                                                           |
| version_compile_os              | Win32                                                          |
| wait_timeout                    | 28800                                                          |
+---------------------------------+----------------------------------------------------------------+

获得MYSQL实际使用的服务器系统变量:

mysql> show variables;

利用like参数来显示具体的服务器系统变量:

mysql> show variables like 'init_connect%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| init_connect  |       |
+---------------+-------+
1 row in set (0.00 sec)

调整MYSQL服务器的系统变量

mysqld服务器维护两种变量。全局变量影响服务器的全局操作。会话变量影响具体客户端连接相关操作。服务器启动时,将所有全局变量初始化为默认值。可以在选项文件或命令行中指定的选项来更改这些默认值。服务器启动后,通过连接服务器并执行SET GLOBAL var_name语句可以更改动态全局变量。要想更改全局变量,必须具有SUPER权限。

方法一:

mysql> SHOW VARIABLES LIKE 'query_cache_size';
+------------------+----------+
| Variable_name    | Value    |
+------------------+----------+
| query_cache_size | 23068672 |
+------------------+----------+
1 row in set (0.01 sec)

mysql> SET GLOBAL query_cache_size = 31457280;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW VARIABLES LIKE 'query_cache_size';
+------------------+----------+
| Variable_name    | Value    |
+------------------+----------+
| query_cache_size | 31457280 |
+------------------+----------+
1 row in set (0.00 sec)

方法二:

mysql> show variables like 'query_cache_size%';
+------------------+----------+
| Variable_name    | Value    |
+------------------+----------+
| query_cache_size | 31457280 |
+------------------+----------+
1 row in set (0.00 sec)

mysql> SET @@global.query_cache_size = 20971520;
Query OK, 0 rows affected (0.09 sec)

mysql> show variables like 'query_cache_size%';
+------------------+----------+
| Variable_name    | Value    |
+------------------+----------+
| query_cache_size | 20971520 |
+------------------+----------+
1 row in set (0.00 sec)

mysql> select @@query_cache_size;
+--------------------+
| @@query_cache_size |
+--------------------+
|           20971520 |
+--------------------+
1 row in set (0.06 sec)

mysqld服务器还为每个客户端连接维护会话变量。连接时使用相应全局变量的当前值对客户端会话变量进行初始化。客户可以通过SET [SESSION] var_name语句来更改动态会话变量。设置会话变量不需要特殊权限,但客户可以只更改自己的会话变量,而不更改其它客户的会话变量。

mysql> SET sort_buffer_size = 10 * 1024 * 1024;
Query OK, 0 rows affected (0.08 sec)

mysql> show variables like 'sort_buffer%';
+------------------+----------+
| Variable_name    | Value    |
+------------------+----------+
| sort_buffer_size | 10485760 |
+------------------+----------+
1 row in set (0.00 sec)

注意:

当使用启动选项设置变量时,变量值可以使用后缀K、M或G分别表示千字节、兆字节或gigabytes。例如,下面的命令启动服务器时的键值缓冲区大小为16 megabytes:

C:\ProgramFiles\MySQL\MySQL Server 5.0\bin>mysqld--key_buffer_size=16M
后缀的大小写美关系;16M和16m是同样的。

运行时,使用SET语句来设置系统变量。此时,不能使用后缀,但值可以采取下列表达式:

mysql> SET sort_buffer_size = 10 * 1024 * 1024;

2  MYSQL服务器状态变量

mysqladmin查看服务器状态变量(动态变化):

C:\Program Files\MySQL\MySQL Server 5.0\bin>mysqladmin -uroot -p extended-status

Enter password: ******
+-----------------------------------+----------+
| Variable_name                     | Value    |
+-----------------------------------+----------+
| Aborted_clients                   | 0        |
| Aborted_connects                  | 3        |
| Binlog_cache_disk_use             | 0        |
| Binlog_cache_use                  | 0        |
| Bytes_received                    | 2664     |
| Bytes_sent                        | 96723    |
| Com_admin_commands                | 0        |
| Com_alter_db                      | 0        |
| Com_alter_table                   | 0        |
| Com_analyze                       | 0        |

该命令和下面命令等效:

获得MYSQL服务器的统计和状态指标:

mysql> show status;
+-----------------------------------+----------+
| Variable_name                     | Value    |
+-----------------------------------+----------+
| Aborted_clients                   | 0        |
| Aborted_connects                  | 3        |
| Binlog_cache_disk_use             | 0        |
| Binlog_cache_use                  | 0        |
| Bytes_received                    | 765      |
| Bytes_sent                        | 80349    |
| Com_admin_commands                | 0        |
| Com_alter_db                      | 0        |
| Com_alter_table                   | 0        |
| Com_analyze                       | 0        |

刷新MYSQL服务器状态变量

mysql> show status like 'Bytes_sent%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Bytes_sent    | 53052 |
+---------------+-------+
1 row in set (0.00 sec)

mysql> flush status;
Query OK, 0 rows affected (0.00 sec)

mysql> show status like 'Bytes_sent%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Bytes_sent    | 11    |
+---------------+-------+
1 row in set (0.00 sec)

mysql 5.0 与 5.1 记录慢查询日志的区别

在mysql5.1之前的版本中,只需要
log_slow_queries=filename  // 注意:这里必须是绝对路径
slow-query-log=1

就可以开启Slow Query log(慢查询日志)了。
但这样在mysql 5.1中并不管用。

日志中会有如下的错误:
[Warning] '--log_slow_queries' is deprecated and will be removed in a future release. Please use ''--slow_query_log'/'--slow_query_log_file'' instead.

修改如下:
log_output=FILE # also can be FILE,TABLE or TABLE or NONE
slow-query-log=1
slow_query_log_file=filename

清除mysql庞大的binlog

环境:mysql master/slave架构

问题:主库硬盘不足

原因:mysql的var下大量bin二进制log,200多个g啊

目标:安全删除binlog

方法:网上搜搜关键词“mysql bin文件 删除”,还真有~~

相关语句:

PURGE {MASTER | BINARY} LOGS TO 'log_name'

PURGE {MASTER | BINARY} LOGS BEFORE 'date'
用于删除列于在指定的日志或日期之前的日志索引中的所有二进制日志。这些日志也会从记录在日志索引文件中的清单中被删除,这样被给定的日志成为第一个。

例如:

PURGE MASTER LOGS TO 'mysql-bin.010';
PURGE MASTER LOGS BEFORE '2003-04-02 22:46:26';
BEFORE变量的date自变量可以为'YYYY-MM-DD hh:mm:ss'格式。MASTER和BINARY是同义词。

如果您有一个活性的从属服务器,该服务器当前正在读取您正在试图删除的日志之一,则本语句不会起作用,而是会失败,并伴随一个错误。不过,如果从属服务器是休止的,并且您碰巧清理了其想要读取的日志之一,则从属服务器启动后不能复制。当从属服务器正在复制时,本语句可以安全运行。您不需要停止它们。

要清理日志,需按照以下步骤:

1.    在每个从属服务器上,使用SHOW SLAVE STATUS来检查它正在读取哪个日志。

2.    使用SHOW MASTER LOGS获得主服务器上的一系列日志。

3.    在所有的从属服务器中判定最早的日志。这个是目标日志。如果所有的从属服务器是更新的,这是清单上的最后一个日志。

4.    制作您将要删除的所有日志的备份。(这个步骤是自选的,但是建议采用。)

5.    清理所有的日志,但是不包括目标日志

实践:

从库下---

mysql> show slave status\G;

……

Master_Log_File: tc-ns-comment-db00-bin.000162

Relay_Master_Log_File: tc-ns-comment-db00-bin.000162

……

主库下---
mysql> show master log;

…………一堆堆

mysql> PURGE MASTER LOGS TO 'tc-ns-comment-db00-bin.000070';

……等待n长时间

mysql>quit

$du -sh ./

减少了好多g啊,世界清净了~~

总结:发现这个解决方法,其实就是mysql参考手册的原文cp,但是一个but,问题是不知道原来这个语句是用在这里的啊……汗一下自己