heartbeat+drbd+mysql高可用配置

Posted on 2011 年 08 月 19 日 by 惜分飞

一、heartbeat
1、安装heartbeat
见Heartbeat安装及简单配置

2、配置参数
2.1）/etc/ha.d/ha.cf
logfile /var/log/ha-log
keepalive 2
deadtime 30
warntime 10
initdead 120
udpport 694
ucast eth1 10.10.10.2
auto_failback off
watchdog /dev/watchdog
node node1
node node2
ping 192.168.1.1
respawn hacluster /usr/lib/heartbeat/ipfail

2.2)/etc/ha.d/haresources
node1 IPaddr::192.168.1.100/24/eth0/ drbddisk Filesystem::/dev/drbd0::/opt/mysql::ext3 mysql

2.3)/etc/ha.d/authkeys
auth 1
1 crc

二、DRBD
1、DRBD安装
见DRBD安装配置说明

2、/etc/drbd.conf

global {
usage-count yes;
}
common {
  syncer { rate 10M; }
}
resource r0 {
protocol C;
disk {
    on-io-error   detach;
  }
net {
after-sb-0pri disconnect;
after-sb-1pri disconnect;
after-sb-2pri disconnect;
rr-conflict   disconnect;
}
on node1 {
    device     /dev/drbd0;
    disk       /dev/sdb1;
    address    10.10.10.1:7788;
    meta-disk  internal;
  }
on node2 {
    device    /dev/drbd0;
    disk      /dev/sdb1;
    address   10.10.10.2:7788;
    meta-disk internal;
  }
}

三、mysql
1、mysql安装
见mysql 5.5二进制文件安装

四、服务配置
1、配置mysql服务器（不要设置开机启动，由heartbeat控制）
修改mysql.server文件
basedir=/opt/mysql/product/5.5
datadir=/opt/mysql/mysqldata
mv /opt/mysql/product/5.5/support-files/mysql.server /etc/init.d/mysql

2、配置heartbeat
chkconfig –add heartbeat
chkconfig heartbeat on

mysql 5.5二进制文件安装

Posted on 2011 年 08 月 19 日 by 惜分飞

1、创建相关目录用户
su – root
groupadd mysql
useradd -g mysql -p xifenfei -s /bin/bash -m mysql -d /opt/mysql
MYSQL_BASE=/opt/mysql
mkdir -p $MYSQL_BASE/product/5.5
mkdir -p $MYSQL_BASE/mysqldata
mkdir -p $MYSQL_BASE/mysqllog
mkdir -p /var/run/mysqld/
chmod -R 777 $MYSQL_BASE
chown -R mysql:mysql $MYSQL_BASE
chown -R mysql:mysql /var/run/mysqld

3、环境变量配置
export MYSQL_BASE=/opt/mysql
export basedir=$MYSQL_BASE/product/5.5
export datadir=$MYSQL_BASE/mysqldata
export LD_LIBRARY_PATH=$basedir/lib:/lib:/usr/lib:/usr/local/lib
export TMPDIR=/tmp
export PATH=${PATH}:/usr/bin:/bin:/usr/bin/X11:/usr/local/bin
export PATH=$basedir:$basedir/bin:${PATH}:$LD_LIBRARY_PATH

4、调整mysql 用户系统限制（可选）
编辑文件：/etc/security/limits.conf 加入以下语句：
mysql soft nproc 2047
mysql hard nproc 16384
mysql soft nofile 1024
mysql hard nofile 65536
确认如下语句是否存在于/etc/pam.d/login，如果不存在请增加：
session required pam_limits.so
如果用户SHELL 用的是Bourne, Bash, 或者Korn shell 修改/etc/profile，并增加如
下内容。(在SuSe 操作系统上，则需要修改/etc/profile.local)：
if [ $USER = “mysql” ]; then
if [ $SHELL = “/bin/ksh” ]; then
ulimit -p 16384
ulimit -n 65536
else
ulimit -u 16384 -n 65536
fi
fi

5、安装数据库
5.1）解压数据库至/opt/mysql/product/5.5中
5.2）安装数据
[mysql@localhost ~]$ cd product/5.5/scripts/
[mysql@localhost scripts]$ ./mysql_install_db –basedir=$basedir –datadir=$datadir –user=mysql

5.3)设置my.cnf参数
vi /etc/my.cnf

[mysqladmin]
socket =/var/run/mysqld/mysqld.sock
[client]
port = 3306
socket =/var/run/mysqld/mysqld.sock
[mysqld]
port = 3306
socket = /var/run/mysqld/mysqld.sock
user = mysql
pid-file = /var/run/mysqld/mysqld.pid
basedir = /opt/mysql/product/5.5
datadir = /opt/mysql/mysqldata
skip-external-locking = 1
interactive_timeout = 1200
wait_timeout = 1200
character-set-server = utf8
back_log = 500
default_time_zone = '+08:00'
max_connections = 1000
max_connect_errors = 1000
connect_timeout = 300
table_open_cache = 100
max_allowed_packet = 16M
binlog_cache_size = 8M
max_heap_table_size = 64M
sort_buffer_size = 8M
join_buffer_size = 8M
thread_cache_size = 32
thread_concurrency = 16
query_cache_size = 0M
default-storage-engine = INNODB
thread_stack = 192K
transaction_isolation = READ-COMMITTED
tmp_table_size = 64M
log-bin =/opt/mysql/mysqllog/mysqlbin
log-error=/opt/mysql/mysqllog/mysqld.err
expire_logs_days = 7
binlog_format = ROW
max_binlog_size = 300M
slow_query_log = 1
slow_query_log_file =/opt/mysql/mysqllog/mysqld-slow
long_query_time = 10
tmpdir = /tmp
server-id = 1
key_buffer_size = 8M
read_buffer_size = 1M
read_rnd_buffer_size = 1M
bulk_insert_buffer_size = 1M
myisam_sort_buffer_size = 128K
innodb_additional_mem_pool_size = 16M
innodb_buffer_pool_size = 2G
innodb_data_file_path = ibdata1:1024M:autoextend
innodb_autoextend_increment = 64
innodb_file_per_table = 1
innodb_data_home_dir = /opt/mysql/mysqldata
innodb_file_io_threads = 4
innodb_thread_concurrency = 16
innodb_flush_log_at_trx_commit = 2
innodb_log_buffer_size = 8M
innodb_log_file_size = 512M
innodb_log_files_in_group = 3
innodb_max_dirty_pages_pct = 40
innodb_lock_wait_timeout = 120
innodb_locks_unsafe_for_binlog = 1
innodb_autoinc_lock_mode = 2
skip-name-resolve
lower_case_table_names=1
[mysqldump]
quick
max_allowed_packet = 16M
[mysql]
auto-rehash
[myisamchk]
key_buffer_size = 512M
sort_buffer_size = 512M
read_buffer = 8M
write_buffer = 8M
[mysqlhotcopy]
interactive-timeout
[mysqld_safe]
open-files-limit = 8192

5.4）异常处理
5.4.1）直接通过mysqld_safe不能正常启动数据库
ln -s /opt/mysql/product/5.5 /usr/local/mysql
或者
ln -s /opt/mysql/product/5.5/bin/mysqld /usr/local/mysql/bin/mysqld

5.4.2）mysql_secure_installation不能正常执行
1)
执行：/opt/mysql/product/5.5/bin/mysql_secure_installation出现以下错误，解决方法（ln -s /var/run/mysqld/mysqld.sock /tmp/mysql.sock）
Enter current password for root (enter for none):
ERROR 2002 (HY000): Can’t connect to local MySQL server through socket ‘/tmp/mysql.sock’ (2)

2)
修改mysql_secure_installation 脚本的do_query 函数（推荐处理方法）
do_query() {
echo “$1″ >$command
#sed ‘s,^,> ,’ < $command # Debugging # mysql --defaults-file=$config <$command ----此行修改如下： mysql --defaults-extra-file=$config <$command return $? } 6、添加开机启动服务
修改mysql.server文件
basedir=/opt/mysql/product/5.5
datadir=/opt/mysql/mysqldata
[root@localhost ~]# cp /opt/mysql/product/5.5/support-files/mysql.server /etc/init.d/mysqld
[root@localhost ~]# chkconfig –add mysqld
[root@localhost ~]# chkconfig mysqld on
[root@localhost ~]# chkconfig –list mysqld
mysqld 0:off 1:off 2:on 3:on 4:on 5:on 6:off

oracle 插入含&字符串

Posted on 2011 年 08 月 19 日 by 惜分飞

1、创建表
SQL> create table t(id number,name varchar2(20));

表已创建。

2、常规方式插入
SQL> insert into t values(1,’a&b’);
输入 b 的值: a&b
原值 1: insert into t values(1,’a&b’)
新值 1: insert into t values(1,’aa&b’)

已创建 1 行。

3、chr(38)插入
SQL> insert into t values(2,’c’||chr(38)||’d’);

已创建 1 行。

4、set define
SQL> set define `
SQL> insert into t values(3,’e&f’);

已创建 1 行。

5、查看结果
SQL> select * from t;

ID NAME
———- ——————–
1 aa&b
2 c&d
3 e&f

6、说明
第一种方法不能得到正常的，我想要的结果，而且第一种方法在应用程序中也比较难实现
第二种比较适合应用程序实现
第三种比较适合sqlplus进行批量处理数据

DRBD安装配置说明

Posted on 2011 年 08 月 18 日 by 惜分飞

一、安装前说明
DRBD(Distributed Replicated Block Device)，分布式复制块设备，是一种通过TCP/IP网络实现块设备数据实时镜像的方案。利用这种方案，单一主节点模式（single primary mode）双机系统能够实时地将业务数据保存在主备节点的磁盘中，正常情况下两个节点的数据是一模一样的。
根据官方的说明文档，如果系统内核（linux）版本低于2.6.33，在安装软件之前需要加载DRBD模块，如果高于（或等于）2.6.33，则只安装客户端软件。
如果没有安装DRBD模块，会在启动drbd时出现如下错误：
[root@node1 tmp]# /etc/init.d/drbd start
Starting DRBD resources: Can not load the drbd module.
其实就是安装一个kmod-drbd包即可

二、安装前环境设定
主机名 IP地址 DRBD使用磁盘
node1 10.10.10.1 /dev/sdb1
node2 10.10.10.2 /dev/sdb1
版本信息
[root@node1 /]# uname -a
Linux node1 2.6.9-89.0.0.0.1.ELsmp #1 SMP Tue May 19 04:23:49 EDT 2009 i686 i686 i386 GNU/Linux
[root@node2 test]# uname -a
Linux node2 2.6.9-89.0.0.0.1.ELsmp #1 SMP Tue May 19 04:23:49 EDT 2009 i686 i686 i386 GNU/Linux
挂载点：/drbd

三、安装步骤（两个节点相同）
1、安装kmod-drbd包
[root@node1 ~]# rpm -ivh /tmp/kmod-drbd83-smp-8.3.8-1.el4_8.i686.rpm
警告：/tmp/kmod-drbd83-smp-8.3.8-1.el4_8.i686.rpm: V3 DSA 簽章：NOKEY, key ID 443e1821
準備中… ########################################### [100%]
1:kmod-drbd83-smp ########################################### [100%]

2、安装drbd包
[root@node1 ~]# rpm -ivh /tmp/drbd83-8.3.8-1.el4_8.i386.rpm
警告：/tmp/drbd83-8.3.8-1.el4_8.i386.rpm: V3 DSA 簽章：NOKEY, key ID 443e1821
準備中… ########################################### [100%]
1:drbd83 ########################################### [100%]

四、fdisk磁盘（两个节点均要）
[root@node1 ~]# fdisk /dev/sdb
Device contains neither a valid DOS partition table, nor Sun, SGI or OSF disklabel
Building a new DOS disklabel. Changes will remain in memory only,
until you decide to write them. After that, of course, the previous
content won’t be recoverable.

The number of cylinders for this disk is set to 2610.
There is nothing wrong with that, but this is larger than 1024,
and could in certain setups cause problems with:
1) software that runs at boot time (e.g., old versions of LILO)
2) booting and partitioning software from other OSs
(e.g., DOS FDISK, OS/2 FDISK)
Warning: invalid flag 0x0000 of partition table 4 will be corrected by w(rite)

Command (m for help): n
Command action
e extended
p primary partition (1-4)
p
Partition number (1-4): 1
First cylinder (1-2610, default 1):
Using default value 1
Last cylinder or +size or +sizeM or +sizeK (1-2610, default 2610): 200

Command (m for help): w
The partition table has been altered!

Calling ioctl() to re-read partition table.
Syncing disks.

五、/etc/drbd.conf配置（两个节点一样）

global {
usage-count yes;
}
common {
  syncer { rate 10M; }
}
resource r0 {
protocol C;
disk {
    on-io-error   detach;
  }
net {
after-sb-0pri disconnect;
after-sb-1pri disconnect;
after-sb-2pri disconnect;
rr-conflict   disconnect;
}
on node1 {
    device     /dev/drbd0;
    disk       /dev/sdb1;
    address    10.10.10.1:7788;
    meta-disk  internal;
  }
on node2 {
    device    /dev/drbd0;
    disk      /dev/sdb1;
    address   10.10.10.2:7788;
    meta-disk internal;
  }
}

六、创建DRBD资源
[root@node1 tmp]# drbdadm create-md r0 //r0为配置文件中定义的资源名
[root@node2 tmp]# drbdadm create-md r0

七、DRBD的启动、关闭以及测试
1、启动DRBD
[root@node1 ~]# /etc/init.d/drbd start
[root@node2 ~]# /etc/init.d/drbd start

2、查看状态（两种方法均可）
[root@node1 tmp]# cat /proc/drbd
version: 8.3.8 (api:88/proto:86-94)
GIT-hash: d78846e52224fd00562f7c225bcc25b2d422321d build by mockbuild@builder10.centos.org, 2010-06-04 08:13:40
0: cs:Connected ro:Secondary/Secondary ds:Inconsistent/Inconsistent C r—-
ns:0 nr:0 dw:0 dr:0 al:0 bm:0 lo:0 pe:0 ua:0 ap:0 ep:1 wo:b oos:1606380

[root@node2 ~]# /etc/init.d/drbd status
drbd driver loaded OK; device status:
version: 8.3.8 (api:88/proto:86-94)
GIT-hash: d78846e52224fd00562f7c225bcc25b2d422321d build by mockbuild@builder10.centos.org, 2010-06-04 08:13:40
m:res cs ro ds p mounted fstype
0:r0 Connected Secondary/Secondary Inconsistent/Inconsistent C

3、设置主机
[root@node1 tmp]# drbdsetup /dev/drbd0 primary -o

4、再查看状态
[root@node1 tmp]# cat /proc/drbd
version: 8.3.8 (api:88/proto:86-94)
GIT-hash: d78846e52224fd00562f7c225bcc25b2d422321d build by mockbuild@builder10.centos.org, 2010-06-04 08:13:40
0: cs:SyncSource ro:Primary/Secondary ds:UpToDate/Inconsistent C r—-
ns:15520 nr:0 dw:0 dr:15520 al:0 bm:0 lo:0 pe:0 ua:0 ap:0 ep:1 wo:b oos:1590860
[>………………..] sync’ed: 1.3% (1590860/1606380)K delay_probe: 0
finish: 0:03:23 speed: 7,760 (7,760) K/sec

[root@node2 ~]# /etc/init.d/drbd status //表示数据初始化已经完成
drbd driver loaded OK; device status:
version: 8.3.8 (api:88/proto:86-94)
GIT-hash: d78846e52224fd00562f7c225bcc25b2d422321d build by mockbuild@builder10.centos.org, 2010-06-04 08:13:40
m:res cs ro ds p mounted fstype
0:r0 Connected Secondary/Primary UpToDate/UpToDate C

5、主节点磁盘操作
[root@node1 tmp]# mkfs.ext3 /dev/drbd0
mke2fs 1.35 (28-Feb-2004)
Filesystem label=
OS type: Linux
Block size=4096 (log=2)
Fragment size=4096 (log=2)
200928 inodes, 401595 blocks
20079 blocks (5.00%) reserved for the super user
First data block=0
Maximum filesystem blocks=415236096
13 block groups
32768 blocks per group, 32768 fragments per group
15456 inodes per group
Superblock backups stored on blocks:
32768, 98304, 163840, 229376, 294912

Writing inode tables: done
Creating journal (8192 blocks): done
Writing superblocks and filesystem accounting information: done

This filesystem will be automatically checked every 29 mounts or
180 days, whichever comes first. Use tune2fs -c or -i to override.
[root@node1 tmp]# mkdir /drbd
[root@node1 tmp]# mount /dev/drbd0 /drbd
[root@node1 tmp]# df -h
Filesystem 容量已用可用已用% 挂载点
/dev/sda2 18G 9.1G 7.7G 55% /
/dev/sda1 99M 13M 82M 14% /boot
none 395M 0 395M 0% /dev/shm
/dev/drbd0 1.6G 35M 1.4G 3% /drbd
[root@node1 tmp]# cd /drbd/
[root@node1 drbd]# ll
总用量 16
drwx—— 2 root root 16384 7月 14 15:01 lost+found
[root@node1 drbd]# mkdir test
[root@node1 drbd]# mkdir test
[root@node1 drbd]# cd test
[root@node1 test]# vi xifenfei.cf
drbd 测试 //vi 写入xifenfei.cf中数据

6、启用备机磁盘
[root@node1 /]# umount /drbd/
[root@node1 /]# drbdsetup 0 secondary
[root@node2 ~]# drbdadm primary r0
[root@node2 ~]# /etc/init.d/drbd status
drbd driver loaded OK; device status:
version: 8.3.8 (api:88/proto:86-94)
GIT-hash: d78846e52224fd00562f7c225bcc25b2d422321d build by mockbuild@builder10.centos.org, 2010-06-04 08:13:40
m:res cs ro ds p mounted fstype
0:r0 Connected Primary/Secondary UpToDate/UpToDate C
[root@node2 ~]# mkdir /drbd
[root@node2 ~]# mount /dev/drbd0 /drbd
[root@node2 ~]# df -h
Filesystem 容量已用可用已用% 挂载点
/dev/sda2 18G 9.1G 7.7G 55% /
/dev/sda1 99M 13M 82M 14% /boot
none 395M 0 395M 0% /dev/shm
/dev/drbd0 1.6G 35M 1.4G 3% /drbd
[root@node2 ~]# cd /drbd
[root@node2 drbd]# ll
总用量 20
drwx—— 2 root root 16384 7月 14 15:01 lost+found
drwxr-xr-x 2 root root 4096 7月 14 15:05 test
[root@node2 drbd]# cd test/
[root@node2 test]# ll
总用量 4
-rw-r–r– 1 root root 10 7月 14 15:05 chengfei.cf
[root@node2 test]# more xifenfei.cf
drbd 测试 //主节点中 vi 写入xifenfei.cf中数据