编译安装Mysql5.5

mysql5.5采用cmake代替了configure

groupadd mysql -g 27
useradd mysql -u 27 -g 27 -c "MySQL Server" -d /home/mysql -m
wget -c http://dev.mysql.com/get/Downloads/MySQL-5.5/mysql-5.5.19.tar.gz/from/http://mysql.he.net/
tar zxf mysql-5.5.19.tar.gz
cd mysql-5.5.19
CFLAGS="-O3" CXX=gcc
CXXFLAGS="-O3 -felide-constructors -fno-exceptions -fno-rtti"

阅读剩余部分...

nginx和MySQL安装加载gperftools模块

gperftools是google小组
if [ `uname -i` == "x86_64" ];
then
cd /tmp
wget -c http://download.savannah.gnu.org/releases/libunwind/libunwind-1.1.tar.gz 
tar zxf libunwind-1.1.tar.gz
cd libunwind-1.1
./configure
make && make install
fi
cd /tmp
wget -c https://gperftools.googlecode.com/files/gperftools-2.0.tar.gz
tar zxf gperftools-2.0.tar.gz
cd gperftools-2.0
./configure  
make  && make install 

echo "/usr/local/lib" > /etc/ld.so.conf.d/usr_local_lib.conf
ldconfig
将编译好的libtcmalloc.so模块加入mysqld_safe启动的时候进行动态加载
 sed -i 's/executing mysqld_safe/executing mysqld_safe\nexport LD_PRELOAD=\/usr\/local\/lib\/libtcmalloc.so/g'    /usr/bin/mysqld_safe
/etc/init.d/mysql restart
nginx不支持动态加载所以要重新编译
./configure 【其他参数】 --with-google_perftools_module
make && make install 
echo "google_perftools_profiles /tmp/tcmalloc;">>/etc/nginx/nginx.conf
/etc/init.d/nginx restart
mkdir /tmp/tcmalloc/ 
chmod  0777 /tmp/tcmalloc/ 

xtrabackup备份和恢复MySQL

xtrabackup有两个主要的工具:innobackupex和xtrabackup,xtrabackup只能备份InnoDB和XtraDB数据表,innobackupex封装了xtrabackup,可以备份MyISAM数据表。

官方地址:http://www.percona.com/doc/percona-xtrabackup/installation.html
官方提供了yum apt 源码安装方案也可以直接rpm导入

阅读剩余部分...

MySQL Proxy 0.8.3 发布

MySQL Proxy 0.8.3发布。2012-08-20 这是MySQL官方的读写分离以及负载均衡工具,上一个版本还是2011-08-17的0.8.2.已经过了一年。

MySQL-Proxy是处在你的MySQL数据库客户和服务端之间的程序,它还支持嵌入性脚本语言Lua。

阅读剩余部分...

Linux下用shell对Mysql进行热备份

针对整个mysql数据库进行全局热备份,恢复的时候可以覆盖恢复,恢复的时候必须是同版本,如果是迁移或者升级建议使用导出为sql文本.
在使用脚本的时候请定义root的密码
可以把脚本放入crond定时执行,对于已经存在的目录会自动重命名.

#!/bin/bash
PATH=/usr/local/sbin:/usr/bin:/bin
BACKDIR=/data/mysql_backup
ROOTUSER=youuser
ROOTPASS=youpassword
if [ -d $BACKDIR ]; then mv $BACKDIR $BACKDIR$(date +"-%Y-%m-%d-%H-%M-%S"); fi
mkdir -p $BACKDIR
for DATANAME in `ls -p /var/lib/mysql | grep / | tr -d /`  ; do mysqlhotcopy $DATANAME -u $ROOTUSER -p $ROOTPASS $BACKDIR; done      

在使用前请确认是否安装了perl-DBD
Centos/Redhat

yum install perl-DBD-mysql

debian/ubuntu

apt-get install libdbd-mysql-perl

重置Mysql的root密码

service mysql restart  --skip-grant-tables
mysql -u root -ppwd <<EOF
use mysql
update user set password=password("新密码") where user="root";
flush privileges;
EOF
service mysql restart

在从的数据库上使用Load data from master的权限赋予

mysql> GRANT SUPER,REPLICATION CLIENT,REPLICATION SLAVE,RELOAD ON *.* TO 'repl'@' 172.16.140.108' IDENTIFIED BY 'bbsrep';

检测mysql主从是否同步的脚本内容

本帖最后由 andy 于 2009-11-18 09:45 编辑

#!/usr/local/php/bin/php
<?php
$master = mysql_connect('192.168.111.36', 'discuz', 'mysqlpaswd');
$slave['35'] = mysql_connect('192.168.111.35', 'discuz', 'mysqlpaswd');
$slave['28'] = mysql_connect('192.168.111.28', 'discuz', 'mysqlpaswd');


while(1) {
        $members_master = mysql_result(mysql_query("SELECT COUNT(*) FROM discuz.cdb_members", $master), 0);
        $threads_master = mysql_result(mysql_query("SELECT COUNT(*) FROM discuz.cdb_threads", $master), 0);
        $posts_master = mysql_result(mysql_query("SELECT COUNT(*) FROM discuz.cdb_posts", $master), 0);
        echo "\r\n";
        echo "\033[05;32mShou\033[0m \033[01;031mshou\033[0m";
        echo "\r\n".date("Y-m-d H:i:s");
        echo "\r\n"."host      members      threads      posts"."\r\n";
        echo "master    ".$members_master."      ".$threads_master."      ".$posts_master."\r\n";

        foreach($slave as $k=>$v){
                $slave_members_master = mysql_result(mysql_query("SELECT COUNT(*) FROM discuz.cdb_members", $v), 0);
                $slave_threads_master = mysql_result(mysql_query("SELECT COUNT(*) FROM discuz.cdb_threads", $v), 0);
                $slave_posts_master = mysql_result(mysql_query("SELECT COUNT(*) FROM discuz.cdb_posts", $v), 0);
                echo $k."        ".
                        $slave_members_master.($slave_members_master==$members_master?" ":"*")."     ".
                        $slave_threads_master.($slave_threads_master==$threads_master?" ":"*")."     ".
                        $slave_posts_master.($slave_posts_master==$posts_master?" ":"*")."\r\n";
        }
        sleep(2);
}


?>

mysql 在指定IP上启动端口

只需在my.cnf中的[mysqld]字段中加入一行
bind-addr       = 1.1.1.1

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 backup@10.0.0.2 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 root@10.0.0.2:/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 backup@10.0.0.1 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]"来定的。