MySQL管理工具:mysql_config_editor的使用

mysql_config_editor是MySQL自带的一款用于安全加密登录的工具,对于多实例的MySQL数据库来说,每次登陆需要指定host,port,password是非常烦人的,所以,可以使用 mysql_config_editor工具可以很容易管理多实例。对于有很多DB的DBA来说,当你在1台机器上设置了你所有DB的信息时候,就不需要再频繁登陆相应的服务器去处理了。

下面,我们对这个工具,做一些简单的介绍,大家可以自行练习

首先,对该工具进行一个简单的总体演练,大家看一眼,就应该知道怎么用了。

[root@slave1 home]# mysql_config_editor set –login-path=remote3306 –host=172.16.10.54 –user=root –password –port=3306

Enter password:

[root@slave1 home]# hexdump ~/.mylogin.cnf
0000000 0000 0000 0f1c 0605 0010 151d 1519 0e0f
0000010 060a 0903 0b19 0110 0010 0000 08c8 25a0
0000020 907b e28c 75d3 9d5d 6bbe 90b6 0010 0000
0000030 7698 6fad 169e bea9 7e58 4e0a 135e 7303
0000040 0010 0000 a0a3 8978 06af ef3c 5d7e fa82
0000050 32e3 dcf0 0020 0000 8455 18d8 3093 129b
0000060 0602 df2e 78e5 dc8a dd81 50ee 1b9d 0656
0000070 56dc 00a3 a7e4 2d4e 0010 0000 6ca8 f0ab
0000080 ed41 7eae d3c5 9a44 ec8d 6e6b 0010 0000
0000090 c2f6 d7ad d751 39e0 3077 9e20 afb5 5ca5
00000a0 0010 0000 7698 6fad 169e bea9 7e58 4e0a
00000b0 135e 7303 0020 0000 248d 3ca6 f169 6b50
00000c0 449c ee50 cbf9 55ac c820 ff96 26ad eb50
00000d0 6686 32f6 4e18 76eb 0020 0000 3538 f59b
00000e0 6723 a926 096a a351 9356 9017 f42f 62af
00000f0 43e3 30ee c5ee a15e 9a4a 346a 0010 0000
0000100 6ca8 f0ab ed41 7eae d3c5 9a44 ec8d 6e6b
0000110

[root@slave1 home]# mysql –login-path=remote3306
Welcome to the MySQL monitor. Commands end with ; or \g.
Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.

(root@172.16.10.54:(none) 01:23)>

 

相关选项说明:

mysql_config_editor:

* set 对login path进行登陆信息设置:

mysql_config_editor set –login-path=remote3306 –host=172.16.10.54 –user=root –password –port=3306

* print 显示指定的login path所有信息

[root@slave1 home]# mysql_config_editor print –login-path=remote3306

[remote3306]

user = root

password = *****

host = 172.16.10.54

port = 3306

* remove 从登陆文件中删除所有的login path

[root@slave1 home]# mysql_config_editor remove –login-path=remote3306

* reset 删除登陆日志的所有内容

[root@slave1 home]# mysql_config_editor reset

 

mysql_config_editor set

* -h,–host=name 添加host到登陆文件中

* -G,–login-path=name 在登录文件中为loginpath添加名字(默认为client)

* -p,–password 在登陆文件中添加密码(该密码会被mysql_config_editor自动加密)

* -u,–user 添加用户名到登陆文件中

* -S,–socket=name 添加sock文件路径到登陆文件中

* -P,–port=name 添加登陆端口到登陆文件中

 

mysql_config_editor print

* –all ,输出所有的login path的登陆信息

* –login-path, 指定login path,输出指定的login path登陆信息

 

mysql_config_editor remove

* -h,–host 删除login path中的host信息

* -G,–login-path 指定删除的loginpath(默认为client)

* -p,–password 删除login path中的password信息

* -u,–user 删除login path中的用户名信息

* -S,–socket 删除login path中的sock文件信息

* -P,–port 删除login path中的port信息

 

mysql_config_editor reset

MySQL密码强度审计插件:validate_password的使用说明

相信很多人在日常工作中,都会遇到设置用户、密码之类的问题,很多人使用keepass来生成和保存密码;但是,很多人为了易于记忆,会选择相对简答的密码,这样,在安全性方面,会存在非常严重的安全隐患。

在mysql 5.6对密码的强度进行了加强,推出了validate_password 插件。支持密码的强度要求。

此插件要求版本:5.6.6 以上版本
安装方式:

1.安装插件:(默认安装了插件后,强度插件就启用了,关闭,需要在配置文件假如相关关闭参数)

mysql>INSTALL PLUGIN validate_password SONAME ‘validate_password.so’;

2.配置文件添加部分参数:

[mysqld]

plugin-load=validate_password.so

validate_password_policy=2

validate-password=FORCE_PLUS_PERMANENT

3.以上处理后,就可以测试了:

mysql> SET PASSWORD = PASSWORD(‘abc’);

ERROR 1819 (HY000): Your password does not satisfy the current policy requirements

mysql> SET PASSWORD = ‘*0D3CED9BEC10A777AEC23CCC353A8C08A633045E’;

Query OK, 0 rows affected (0.01 sec)

4.相关说明:

(1).相关选项:

validate-password=ON/OFF/FORCE/FORCE_PLUS_PERMANENT: 决定是否使用该插件(及强制/永久强制使用)。

validate_password_dictionary_file:插件用于验证密码强度的字典文件路径。

validate_password_length:密码最小长度。

validate_password_mixed_case_count:密码至少要包含的小写字母个数和大写字母个数。

validate_password_number_count:密码至少要包含的数字个数。

validate_password_policy:密码强度检查等级,0/LOW、1/MEDIUM、2/STRONG。

validate_password_special_char_count:密码至少要包含的特殊字符数。

其中,关于validate_password_policy-密码强度检查等级:

0/LOW:只检查长度。

1/MEDIUM:检查长度、数字、大小写、特殊字符。

2/STRONG:检查长度、数字、大小写、特殊字符字典文件。

(2).插件的安装启用:

插件对应的库对象文件需在配置选项plugin_dir指定的目录中。

可使用–plugin-load=validate_password.so,在server启动时载入插件,或者将plugin-load=validate_password.so写入配置文件。

也可以通过如下语句在server运行时载入插件(会注册进mysql.plugins表)

mysql> INSTALL PLUGIN validate_password SONAME ‘validate_password.so’;

(3).为阻止该插件在运行时被删除可在配置文件中添加:

[mysqld]

plugin-load=validate_password.so

validate-password=FORCE_PLUS_PERMANENT

mysql 审计插件的安装和使用


很多人都一直在寻找mysql的审计插件,目前,mariadb 官方已经提供了审计功能,并且含有审计插件,可以在mysql使用
具体方式:
       就是先下载安装一个mriadb ,安装完成后,按如下方式操作:
1. 在mariadb 里执行:SHOW VARIABLES LIKE ‘plugin_dir’;
    查找插件目录,进入插件目录,将名称为:server_audit.so 复制到 mysql  的插件目录(mysql插件目录查询方式,同mariadb)
2.mysql 和 mariadb 中,审计插件的安装方式:
(1).mysql 的安装方式
     INSTALL PLUGIN server_audit SONAME ‘server_audit.so’;
 
(2).mariadb的安装方式:
     INSTALL PLUGIN server_audit SONAME ‘server_audit’;
(3).卸载插件:
     uninstall plugin server_sudit
 
3.审计插件相关状态参数的查看命令
状态参数查看:
mysql> SHOW global VARIABLES LIKE ‘%audit%’;
mysql> SHOW global status LIKE ‘%audit%’;
 
4.启用审计插件:
mysql> set global server_audit_logging=1;
5.设置记录的内容:
mysql> set global  server_audit_events =’connect,query,TABLE’;
注:
       永久生效的话,写入到my.cnf文件里:
[mysqld]
server_audit_events=connect,query,table
6.其它相关说明
mysql>  SET GLOBAL server_audit_excl_users=’confluence,hoss_user,jira,nagios,sonar,test,tm_jdbc,tmp_test,mpm,repl,cacti,dba_test’;
mysql>  set global server_audit_incl_users=’haowu_dev,haowu_test,root,dev_debug’;
 

参数说明:
server_audit_output_type:指定日志输出类型,可为SYSLOG或FILE
server_audit_logging:启动或关闭审计
server_audit_events:指定记录事件的类型,可以用逗号分隔的多个值(connect,query,table),如果开启了查询缓存(query cache),查询直接从查询缓存返回数据,将没有table记录
server_audit_file_path:如server_audit_output_type为FILE,使用该变量设置存储日志的文件,可以指定目录,默认存放在数据目录的server_audit.log文件中
server_audit_file_rotate_size:限制日志文件的大小
server_audit_file_rotations:指定日志文件的数量,如果为0日志将从不轮转
server_audit_file_rotate_now:强制日志文件轮转
server_audit_incl_users:指定哪些用户的活动将记录,connect将不受此变量影响,该变量比server_audit_excl_users优先级高
server_audit_syslog_facility:默认为LOG_USER,指定facility
server_audit_syslog_ident:设置ident,作为每个syslog记录的一部分
server_audit_syslog_info:指定的info字符串将添加到syslog记录
server_audit_syslog_priority:定义记录日志的syslogd priority
server_audit_excl_users:该列表的用户行为将不记录,connect将不受该设置影响
server_audit_mode:标识版本,用于开发测试
7.日志样式:
Image
 

mysql中order by和limit同时使用时存在的问题及解决方法

今天突然遇到一个问题,同时使用order by 和 limit 时存在问题(order by 列存在重复值);
就是当使用 limit 140,10  和 limit 130,10的数据是一样;
按正常理解,两者之间数据应该是不一样
执行sql如下:[同时,我给了测试sql,测试数据以及测试表结构]
SELECT
     p.id,
     p.org_name AS orgName,
     p.city_id AS cityId,
     p.is_cooperation AS isCooperation,
     p.create_time AS createTime,
     p.maintenancer AS creater,
     p.audit_status AS auditStatus
FROM partner_organization p
WHERE  p.partner_type = 1
AND p.city_id IN (1, 2)
ORDER BY p.create_time DESC
LIMIT 140,10
 
执行后结果集如下:
QQ截图20150228184142
执行如下sql:
SELECT
     p.id,
     p.org_name AS orgName,
     p.city_id AS cityId,
     p.is_cooperation AS isCooperation,
     p.create_time AS createTime,
     p.maintenancer AS creater,
     p.audit_status AS auditStatus
FROM partner_organization p
WHERE p.partner_type = 1
AND p.city_id IN (1, 2)
ORDER BY p.create_time DESC
LIMIT 130,10
 
则结果集如下:
QQ截图20150228184142
两者的结果集明显一样;
因此,我们推测:
     在同时使用order by和limit时,MySQL进行了某些优化,
     将语句执行逻辑从”where——order by——limit”变成了”order by——limit——where”
 
那么针对这条语句,我们如何进行优化呢,我们采取两种方式:
方式一:我们将排序列,新增一列,确保唯一性来实现limit
SELECT
p.id,
p.org_name AS orgName,
p.city_id AS cityId,
p.is_cooperation AS isCooperation,
p.create_time AS createTime,
p.maintenancer AS creater,
p.audit_status AS auditStatus
FROM partner_organization p
WHERE p.partner_type = 1
AND p.city_id IN (1, 2)
ORDER BY
p.create_time,id DESC
LIMIT 140,10
方式二:我们用where过滤后形成结果集,作为子查询来处理
SELECT
p.id,
p.org_name AS orgName,
p.city_id AS cityId,
p.is_cooperation AS isCooperation,
p.create_time AS createTime,
p.maintenancer AS creater,
p.audit_status AS auditStatus
FROM (
SELECT     *
FROM partner_organization p
WHERE p.partner_type = 1 AND p.city_id IN (1, 2)
) p

ORDER BY p.create_time DESC
LIMIT 130,10
测试数据下载地址如下:
链接: http://pan.baidu.com/s/1gdy7hQj
密码: icx8

mysql 5.6在gtid复制模式下复制错误,如何跳过??

这里就不粘贴复制的报错状态信息,简要说一下当前的环境:

系统为:red hat linux 6 (2.6.32)

mysql版本为:5.6.10

采用的一机多实例的部署方式

前言:

gtid 复制模式下,不要使用 replicate_wild_do_table 此参数,使用此类参数,会复制异常(即状态信息正常,但从机没有复制过来)

下面进入正题:

在我们遇到复制错误时,如果复制没有特别的错误时,我们一般采用 set global sql_slave_skip_counter=1 来跳过错误,但是,在mysql 5.6 的gtid模式下,此方法是没有作用的。我们采取如下方式:

1.查看从机的复制状态信息:(主要是以下几个值)

Retrieved_Gtid_Set: D68DBC47-3AAE-11E2-BC2F-842B2B699BDA:141-151

Executed_Gtid_Set: D68DBC47-3AAE-11E2-BC2F-842B2B699BDA:1-140

Retrieved_Gtid_Set项:记录了relay日志从Master获取了binlog日志的位置

Executed_Gtid_Set项:记录本机执行的binlog日志位置(如果是从机,包括Master的binlog日志位置和slave本身的binlog日志位置)

2.在从机上执行如下操作,以下所有操作,如果没有特别说明,均在从机上执行:

> reset master;

> reset slave all;

> set global grid_purged=’D68DBC47-3AAE-11E2-BC2F-842B2B699BDA:1-141′;

> change master to master_host=’****’,master_user=’****’,master_password=’******’,master_port=3306,master_auto_position=1;

> start slave;

> show slave status\G;

此时再查看复制状态信息,重复以上操作 set global grid_purged=’D68DBC47-3AAE-11E2-BC2F-842B2B699BDA:1-141′; 此值依次往后推,直到复制正常为止

其它相关信息,可以参考下面博客链接:

http://imysql.cn/2014/07/31/mysql-faq-exception-replication-with-gtid.shtml

 

[转] Heartbeat+DRBD+MySQL高可用方案

文章转载自:http://www.cnblogs.com/gomysql/p/3674030.html

1.方案简介

本方案采用Heartbeat双机热备软件来保证数据库的高稳定性和连续性,数据的一致性由DRBD这个工具来保证。默认情况下只有一台mysql 在工作,当主mysql服务器出现问题后,系统将自动切换到备机上继续提供服务,当主数据库修复完毕,又将服务切回继续由主mysql提供服务。

2.方案优缺点

优点:安全性高、稳定性高、可用性高,出现故障自动切换。

缺点:只有一台服务器提供服务,成本相对较高,不方便扩展,可能会发生脑裂。

3.软件介绍

Heartbeat介绍

官方站点:http://linux-ha.org/wiki/Main_Page

heartbeat可以资源(VIP地址及程序服务)从一台有故障的服务器快速的转移到另一台正常的服务器提供服务,heartbeat和keepalived相似,heartbeat可以实现failover功能,但不能实现对后端的健康检查

DRBD介绍

官方站点:http://www.drbd.org/

DRBD(DistributedReplicatedBlockDevice)是一个基于块设备级别在远程服务器直接同步和镜像数据的软件,用软件实现的、无共享的、服务器之间镜像块设备内容的存储复制解决方案。它可以实现在网络中两台服务器之间基于块设备级别的实时镜像或同步复制(两台服务器都写入成功)/异步复制(本地服务器写入成功),相当于网络的RAID1,由于是基于块设备(磁盘,LVM逻辑卷),在文件系统的底层,所以数据复制要比cp命令更快。DRBD已经被MySQL官方写入文档手册作为推荐的高可用的方案之一

4.方案拓扑

5.方案适用场景:

适用于数据库访问量不太大,短期内访问量增长不会太快,对数据库可用性要求非常高的场景。

6.测试环境介绍(如下所示,均已关闭防火墙及selinux,生产环境自行开放端口)

主机名             ip               系统                DRBD磁盘                heartbeat版本
db-server-01    192.168.0.10    centos6.2 64bit         /dev/sda5                  3.0.4
db-server-02    192.168.0.20    centos6.2 64bit         /dev/sda5                  3.0.4

7.软件安装以及环境配置

(1)安装drbd依赖组件(两台机器,安装以后重启系统,因为会升级内核版本,不重启会对不上内核版本,有知道不用重启的童鞋请给我留言^_^):

yum install -y kernel kernel-devel kernel-headers  flex

(2)下载软件安装(两台机器操作一样)

wget http://oss.linbit.com/drbd/8.4/drbd-8.4.2.tar.gz
tar xf drbd-8.4.2.tar.gz 
cd drbd-8.4.2
./configure --prefix=/usr/local/drbd --with-km
make KDIR=/usr/src/kernels/2.6.32-431.11.2.el6.x86_64/   #很多童鞋无法加载drbd模块,多半是正在运行的内核版本和新安装的不相符
make install
mkdir -p /usr/local/drbd/var/run/drbd
cp /usr/local/drbd/etc/rc.d/init.d/drbd /etc/rc.d/init.d
chmod 755 /etc/init.d/drbd
cd drbd
make clean
make KDIR=/usr/src/kernels/2.6.32-431.11.2.el6.x86_64/
cp drbd.ko /lib/modules/`uname -r`/kernel/lib/
modprobe drbd

检查是否加载了drbd模块

[root@192.168.0.10 ~]# lsmod | grep drbd
drbd                  314246  0 
libcrc32c               1246  1 drbd
[root@192.168.0.10 ~]#

(3)DRBD配置(配置之前需要先使用fdisk对 /dev/sda进行分区)

[root@192.168.0.10 ~]# df -HT
Filesystem    Type     Size   Used  Avail Use% Mounted on
/dev/sda2     ext4      19G   2.6G    16G  15% /
tmpfs        tmpfs     121M      0   121M   0% /dev/shm
/dev/sda1     ext4     204M    52M   141M  27% /boot
/dev/sda5     ext4      34G   185M    32G   1% /data
[root@192.168.0.10 ~]#

我这里两台机器之前都已经分区了,由于是自己笔记本上的虚拟机,所以懒得加磁盘了,我直接把 /data/卸载,然后格式化/dev/sda5,我两台机器都这样操作,如果你有空的磁盘,照样需要进行分区,比如可以将一个1T的盘分一个区就行了。

[root@192.168.0.10 ~]# umount /data/         
[root@192.168.0.10 ~]# mkfs.ext4 /dev/sda5
mke2fs 1.41.12 (17-May-2010)
Filesystem label=
OS type: Linux
Block size=4096 (log=2)
Fragment size=4096 (log=2)
Stride=0 blocks, Stripe width=0 blocks
2048000 inodes, 8185344 blocks
409267 blocks (5.00%) reserved for the super user
First data block=0
Maximum filesystem blocks=4294967296
250 block groups
32768 blocks per group, 32768 fragments per group
8192 inodes per group
Superblock backups stored on blocks: 
        32768, 98304, 163840, 229376, 294912, 819200, 884736, 1605632, 2654208, 
        4096000, 7962624

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

This filesystem will be automatically checked every 28 mounts or
180 days, whichever comes first.  Use tune2fs -c or -i to override.
[root@192.168.0.10 ~]#
[root@192.168.0.10 ~]# fdisk -l

Disk /dev/sda: 53.7 GB, 53687091200 bytes
255 heads, 63 sectors/track, 6527 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes
Disk identifier: 0x000eb0ff

   Device Boot      Start         End      Blocks   Id  System
/dev/sda1   *           1          26      204800   83  Linux
Partition 1 does not end on cylinder boundary.
/dev/sda2              26        2321    18432000   83  Linux
/dev/sda3            2321        2451     1048576   82  Linux swap / Solaris
/dev/sda4            2451        6528    32742400    5  Extended
/dev/sda5            2451        6528    32741376   83  Linux
[root@192.168.0.10 ~]#

我这里还要在/etc/fstab里面注释一项:

#UUID=33958004-e8a7-4135-844f-707a5537e86a /data                   ext4    defaults        1 2

否则重启机器的时候提示无法挂载,会无法启动的。

修改/etc/hosts文件,两台服务器操作一样。

192.168.0.10    db-server-01
192.168.0.20    db-server-02

drbd配置只需要修改/usr/local/drbd/etc/drbd.d/global_common.conf配置文件即可,修改后如下(两台服务器配置一样):

[root@192.168.0.10 ~]# cat /usr/local/drbd/etc/drbd.d/global_common.conf
global { usage-count yes; }
common { syncer { rate 30M; } }       #同步速率,视带宽而定 
resource r0 {                         #创建一个资源,名字叫"r0" 
        protocol C;                   #选择的是drbd的C 协议(数据同步协议,C为收到数据并写入后返回,确认成功) 
        startup {
        }
        disk {
                on-io-error detach;
        }
        net {
        }
        on db-server-01 {            #设定一个节点,分别以各自的主机名命名 
                device /dev/drbd0;   #设定资源设备/dev/drbd0 指向实际的物理分区 /dev/sda5
                disk /dev/sda5;
                address 192.168.0.10:7888;  #设定监听地址以及端口 
                meta-disk internal;
        }
        on db-server-02 {
                device /dev/drbd0;
                disk /dev/sda5;
                address 192.168.0.20:7888;
                meta-disk internal;     #internal表示是在同一个局域网内 
        }
}
[root@192.168.0.10 ~]#

(4)DRBD的管理与维护:

创建DRBD资源

配置好drbd以后,就需要使用命令创建配置的drbd资源,使用如下命令(两台服务器操作一样):

[root@192.168.0.10 ~]# dd if=/dev/zero of=/dev/sda5 bs=1M count=100  #不这样做的话,在创建资源的时候报错
100+0 records in
100+0 records out
104857600 bytes (105 MB) copied, 3.34339 s, 31.4 MB/s
[root@192.168.0.10 ~]#
[root@192.168.0.10 ~]# drbdadm create-md r0                             
Writing meta data...
initializing activity log
NOT initializing bitmap
New drbd meta data block successfully created.
success
[root@192.168.0.10 ~]#

(5)DRBD的启动与状态查看(分别在两台服务器启动)

[root@192.168.0.10 ~]# /etc/init.d/drbd start               
Starting DRBD resources: [
     create res: r0
   prepare disk: r0
    adjust disk: r0
     adjust net: r0
]
.....
[root@192.168.0.10 ~]#
[root@192.168.0.20 ~]# /etc/init.d/drbd start
Starting DRBD resources: [
     create res: r0
   prepare disk: r0
    adjust disk: r0
     adjust net: r0
]
.
[root@192.168.0.20 ~]#

查看drbd的状态:

[root@192.168.0.10 ~]# /etc/init.d/drbd status
drbd driver loaded OK; device status:
version: 8.4.2 (api:1/proto:86-101)
GIT-hash: 7ad5f850d711223713d6dcadc3dd48860321070c build by root@db-server-01, 2014-04-18 21:15:57
m:res  cs         ro                   ds                         p  mounted  fstype
0:r0   Connected  Secondary/Secondary  Inconsistent/Inconsistent  C
[root@192.168.0.10 ~]#

可以看见都还没有主节点。设置当前节点(192.168.0.10)为主节点,并进行格式化和挂载 。

drbdadm -- --overwrite-data-of-peer primary all
mkfs.ext4 /dev/drbd0
mkdir /data
mount /dev/drbd0 /data/

在另外一台服务器创建挂载目录,也创建/data

[root@192.168.0.20 ~]# mkdir /data

查看一下drbd的状态(可以看见还在同步):

[root@192.168.0.10 ~]# /etc/init.d/drbd status
drbd driver loaded OK; device status:
version: 8.4.2 (api:1/proto:86-101)
GIT-hash: 7ad5f850d711223713d6dcadc3dd48860321070c build by root@db-server-01, 2014-04-18 21:15:57
m:res  cs          ro                 ds                     p  mounted  fstype
...    sync'ed:    13.7%              (27596/31972)M
0:r0   SyncSource  Primary/Secondary  UpToDate/Inconsistent  C  /data    ext4
[root@192.168.0.10 ~]#

(6)mysql安装,我这里为了简单直接安装编译好的二进制软件包(两台服务器都需要安装,操作一样,只是第二台mysql不需要初始化数据)

注意:两台服务器上的mysql用户的uid和gid要一样。不然切换后会导致mysql数据目录的属主不正确而启动失败。

[root@192.168.0.10 ~]# wget http://cdn.mysql.com/Downloads/MySQL-5.5/mysql-5.5.37-linux2.6-x86_64.tar.gz
[root@192.168.0.10 ~]# tar xf mysql-5.5.37-linux2.6-x86_64.tar.gz -C /usr/local/
[root@192.168.0.10 ~]# cd /usr/local/
[root@192.168.0.10 local]# ln -s mysql-5.5.37-linux2.6-x86_64/ mysql
[root@192.168.0.10 local]# groupadd mysql
[root@192.168.0.10 local]# useradd -r -g mysql mysql
[root@192.168.0.10 local]# cd mysql
[root@192.168.0.10 mysql]# chown -R mysql .
[root@192.168.0.10 mysql]# chgrp -R mysql .
[root@192.168.0.10 mysql]# mkdir /data/mysql
[root@192.168.0.10 mysql]# chown -R mysql.mysql /data/mysql/
[root@192.168.0.10 mysql]# /usr/local/mysql/scripts/mysql_install_db --user=mysql --datadir=/data/mysql/ --basedir=/usr/local/mysql
[root@192.168.0.10 mysql]# chown -R root .
[root@192.168.0.10 mysql]# cp support-files/my-medium.cnf /etc/my.cnf
[root@192.168.0.10 mysql]# cp support-files/mysql.server /etc/init.d/mysqld
[root@192.168.0.10 mysql]# chmod 755 /etc/init.d/mysqld
[root@192.168.0.10 mysql]# egrep 'datadir|basedir' /etc/my.cnf       #两台服务器上的mysql配置文件都加入这里的配置 
datadir=/data/mysql
basedir=/usr/local/mysql                                
[root@192.168.0.10 mysql]#

(7)手动切换drbd的主从。看另外一台服务器是否有数据(自动切换需要使用heartbeat,后面介绍):

[root@192.168.0.10 ~]# ll /data/
total 20
drwx------ 2 root  root  16384 Apr 18 22:16 lost+found
drwxr-xr-x 5 mysql mysql  4096 Apr 18 23:01 mysql
[root@192.168.0.10 ~]#
[root@192.168.0.20 ~]# ll /data/
total 0
[root@192.168.0.20 ~]#
[root@192.168.0.10 ~]# /etc/init.d/drbd status
drbd driver loaded OK; device status:
version: 8.4.2 (api:1/proto:86-101)
GIT-hash: 7ad5f850d711223713d6dcadc3dd48860321070c build by root@db-server-01, 2014-04-18 21:15:57
m:res  cs         ro                 ds                 p  mounted  fstype
0:r0   Connected  Primary/Secondary  UpToDate/UpToDate  C  /data    ext4
[root@192.168.0.10 ~]#

可以看见当前服务器是主,也就是数据在这台服务器上,另外一台服务器是没有数据的。下面进行手动切换

主切换成从,需要先卸载文件系统,再执行降级为从的命令:

[root@192.168.0.10 ~]# umount /data/
[root@192.168.0.10 ~]# drbdadm secondary all

从切换成主,要先执行升级成主的命令然后挂在文件系统:

[root@192.168.0.20 ~]# drbdadm  primary all
[root@192.168.0.20 ~]# mount /dev/drbd0 /data/
[root@192.168.0.20 ~]# ll /data/
total 20
drwx------ 2 root  root  16384 Apr 18 22:16 lost+found
drwxr-xr-x 5 mysql mysql  4096 Apr 18 23:01 mysql
[root@192.168.0.20 ~]# /etc/init.d/drbd status
drbd driver loaded OK; device status:
version: 8.4.2 (api:1/proto:86-101)
GIT-hash: 7ad5f850d711223713d6dcadc3dd48860321070c build by root@db-server-02, 2014-04-18 21:22:55
m:res  cs         ro                 ds                 p  mounted  fstype
0:r0   Connected  Primary/Secondary  UpToDate/UpToDate  C  /data    ext4
[root@192.168.0.20 ~]#

可以看见已经成功切换成主,并且mysql初始化数据也存在了。

DRBD脑裂后的处理

当DRBD出现脑裂后,会导致drbd两边的磁盘数据不一致,在确定要作为从的节点上切换成secondary,并放弃该资源的数据:

drbdadm secondary r0
drbdadm -- --discard-my-data connect r0

在要作为primary的节点重新连接secondary(如果这个节点当前的连接状态为WFConnection的话,可以省略),使用如下命令连接:

drbdadm connect r0

(8)Heartbeat安装(两台服务器)

需要添加epel源,centos默认自己没有该软件包,当然你可以自己源码编译。

rpm -ivh http://dl.fedoraproject.org/pub/epel/6/x86_64/epel-release-6-8.noarch.rpm
yum install heartbeat -y

创建DRBD脚本文件drbddisk:(两台服务器)

注意:

此处是一个大坑,因为默认yum安装Heartbeat,不会在 /etc/ha.d/resource.d/创建drbddisk脚本,估计是版本太新了吧。记得前两年都不会这样的。囧。而且也无法在安装后从本地其他 路径找到该文件。此处也是因为启动Heartbeat后无法PING通虚IP,最后通过查看/var/log/ha-log日志,找到一行ERROR: Cannot locate resource script drbddisk,然后进而到/etc/ha.d/resource.d/路径下发现竟然没有drbddisk脚本,最后在google上找到该代码,创 建该脚本,终于测试通过:

[root@192.168.0.20 ~]# chmod 755 /etc/ha.d/resource.d/drbddisk 
[root@192.168.0.20 ~]# cat /etc/ha.d/resource.d/drbddisk 
#!/bin/bash
#
# This script is inteded to be used as resource script by heartbeat
#
# Copright 2003-2008 LINBIT Information Technologies
# Philipp Reisner, Lars Ellenberg
#
###

DEFAULTFILE="/etc/default/drbd"
DRBDADM="/sbin/drbdadm"

if [ -f $DEFAULTFILE ]; then
 . $DEFAULTFILE
fi

if [ "$#" -eq 2 ]; then
 RES="$1"
 CMD="$2"
else
 RES="all"
 CMD="$1"
fi

## EXIT CODES
# since this is a "legacy heartbeat R1 resource agent" script,
# exit codes actually do not matter that much as long as we conform to
#  http://wiki.linux-ha.org/HeartbeatResourceAgent
# but it does not hurt to conform to lsb init-script exit codes,
# where we can.
#  http://refspecs.linux-foundation.org/LSB_3.1.0/
#LSB-Core-generic/LSB-Core-generic/iniscrptact.html
####

drbd_set_role_from_proc_drbd()
{
local out
if ! test -e /proc/drbd; then
ROLE="Unconfigured"
return
fi

dev=$( $DRBDADM sh-dev $RES )
minor=${dev#/dev/drbd}
if [[ $minor = *[!0-9]* ]] ; then
# sh-minor is only supported since drbd 8.3.1
minor=$( $DRBDADM sh-minor $RES )
fi
if [[ -z $minor ]] || [[ $minor = *[!0-9]* ]] ; then
ROLE=Unknown
return
fi

if out=$(sed -ne "/^ *$minor: cs:/ { s/:/ /g; p; q; }" /proc/drbd); then
set -- $out
ROLE=${5%/**}
: ${ROLE:=Unconfigured} # if it does not show up
else
ROLE=Unknown
fi
}

case "$CMD" in
   start)
# try several times, in case heartbeat deadtime
# was smaller than drbd ping time
try=6
while true; do
$DRBDADM primary $RES && break
let "--try" || exit 1 # LSB generic error
sleep 1
done
;;
   stop)
# heartbeat (haresources mode) will retry failed stop
# for a number of times in addition to this internal retry.
try=3
while true; do
$DRBDADM secondary $RES && break
# We used to lie here, and pretend success for anything != 11,
# to avoid the reboot on failed stop recovery for "simple
# config errors" and such. But that is incorrect.
# Don't lie to your cluster manager.
# And don't do config errors...
let --try || exit 1 # LSB generic error
sleep 1
done
;;
   status)
if [ "$RES" = "all" ]; then
   echo "A resource name is required for status inquiries."
   exit 10
fi
ST=$( $DRBDADM role $RES )
ROLE=${ST%/**}
case $ROLE in
Primary|Secondary|Unconfigured)
# expected
;;
*)
# unexpected. whatever...
# If we are unsure about the state of a resource, we need to
# report it as possibly running, so heartbeat can, after failed
# stop, do a recovery by reboot.
# drbdsetup may fail for obscure reasons, e.g. if /var/lock/ is
# suddenly readonly.  So we retry by parsing /proc/drbd.
drbd_set_role_from_proc_drbd
esac
case $ROLE in
Primary)
echo "running (Primary)"
exit 0 # LSB status "service is OK"
;;
Secondary|Unconfigured)
echo "stopped ($ROLE)"
exit 3 # LSB status "service is not running"
;;
*)
# NOTE the "running" in below message.
# this is a "heartbeat" resource script,
# the exit code is _ignored_.
echo "cannot determine status, may be running ($ROLE)"
exit 4 #  LSB status "service status is unknown"
;;
esac
;;
   *)
echo "Usage: drbddisk [resource] {start|stop|status}"
exit 1
;;
esac

exit 0
[root@192.168.0.20 ~]#

(9)heartbeat配置

Hearbeat的配置主要包括三个配置文件,authkeys,ha.cf和haresources的配置,下面就分别来看看:

Authkerys的配置(两台服务器配置一样)

这个文件用来配置密码认证方式,支持3种认证方式,crc,md5和sha1,从左到右安全性越来越高,消耗的资源也越多。因此如果 heartbeat运行在安全的网路之上,比如私网,那么可以将验证方式设置成crc,master和backup的authkeys配置一样。我的 authkeys文件配置如下:

[root@192.168.0.10 ~]# cat /etc/ha.d/authkeys 
auth 1
1 crc
[root@192.168.0.10 ~]# chmod 600 /etc/ha.d/authkeys

注意:该文件权限必须是600

ha.cf的配置(两台机器稍微有点区别),Primary(192.168.0.10)如下:

[root@192.168.0.10 ~]# cat /etc/ha.d/ha.cf 
logfile /var/log/ha-log 
#定义Heartbeat的日志名字及位置 
logfacility local0 
keepalive 2 
#设定心跳(监测)时间为2秒 
deadtime 15 
#设定死亡时间为15秒 
ucast eth1 192.168.0.20
#采用单播的方式,IP地址指定为对方IP 
auto_failback off 
#当Primary机器发生故障切换到Secondary机器后Primary恢复后是否进行切回操作 (最好是我们有需求手动进行切换)
node db-server-01
node db-server-02
[root@192.168.0.10 ~]#

Secondary(192.168.0.20)如下:

[root@192.168.0.20 ~]# cat /etc/ha.d/ha.cf 
logfile /var/log/ha-log 
#定义Heartbeat的日志名字及位置 
logfacility local0 
keepalive 2 
#设定心跳(监测)时间为2秒 
deadtime 15 
#设定死亡时间为15秒 
ucast eth1 192.168.0.10
#采用单播的方式,IP地址指定为对方IP 
auto_failback off
#当Primary机器发生故障切换到Secondary机器后Primary恢复后是否进行切回操作(一般我们可以看需求,否则不用自动切换) 
node db-server-01
node db-server-02
[root@192.168.0.20 ~]#

haresources的配置(两台机器配置一样):

[root@192.168.0.10 ~]# cat /etc/ha.d/haresources 
db-server-01 IPaddr::192.168.0.88/24/eth1 drbddisk::r0 Filesystem::/dev/drbd0::/data::ext4  mysqld 
[root@192.168.0.10 ~]#

注:该文件内IPaddr,Filesystem等脚本存放路径在 /etc/ha.d/resource.d/下,也可在该目录下存放服务启动脚本(例如:mysqld),将相同脚本名称添到/etc/ha.d /haresources内容中,从而跟随heartbeat启动而启动该脚本。

IPaddr::192.168.0.88/24/eth1:用IPaddr脚本配置浮动VIP

drbddisk::r0:用drbddisk脚本实现DRBD主从节点资源组的挂载和卸载

Filesystem::/dev/drbd0::/data::ext4:用Filesystem脚本实现磁盘挂载和卸载

(10)heartbeat的管理

配置好heartbeat之后,需要将mysql从自启动服务器中去掉,因为主heartbeat启动的时候会挂载drdb文件系统以及启动mysql,切换的时候会将主上的mysql停止并卸载文件系统,从上会挂载文件系统,并启动mysql。因此需要做如下操作(两台服务器):

[root@192.168.0.10 ~]# chkconfig mysqld off
[root@192.168.0.10 ~]# chkconfig heartbeat off
[root@192.168.0.10 ~]# chkconfig drbd off
[root@192.168.0.10 ~]# cat /etc/rc.local 
#!/bin/sh
#
# This script will be executed *after* all the other init scripts.
# You can put your own initialization stuff in here if you don't
# want to do the full Sys V style init stuff.

touch /var/lock/subsys/local
modprobe drbd              #必须先加载模块,这也是因为将启动命令放在这里的原因
/etc/init.d/drbd start
/etc/init.d/heartbeat start

[root@192.168.0.10 ~]#

到这里heartbeat+drbd+mysql高可用环境就搭建结束了。接下来进行测试。

高可用测试

(1)在第一台服务器上面启动mysql服务。(192.168.0.10)

[root@192.168.0.10 ~]# /etc/init.d/mysqld start
Starting MySQL.The server quit without updating PID file (/[FAILED]ql/db-server-01.pid).
[root@192.168.0.10 ~]# ll /data/
total 0
[root@192.168.0.10 ~]#

怎么回事?/data/下面为空。这里是因为我们在前面已经把这个节点变为Secondary

[root@192.168.0.10 ~]# /etc/init.d/drbd status
drbd driver loaded OK; device status:
version: 8.4.2 (api:1/proto:86-101)
GIT-hash: 7ad5f850d711223713d6dcadc3dd48860321070c build by root@db-server-01, 2014-04-18 21:15:57
m:res  cs         ro                 ds                 p  mounted  fstype
0:r0   Connected  Secondary/Primary  UpToDate/UpToDate  C
[root@192.168.0.10 ~]#

我们现在需要手动切换回来。才能启动mysql

[root@192.168.0.20 ~]# umount /data/
[root@192.168.0.20 ~]# drbdadm secondary all
[root@192.168.0.20 ~]#
[root@192.168.0.10 ~]# drbdadm  primary all
[root@192.168.0.10 ~]# mount /dev/drbd0 /data/
[root@192.168.0.10 ~]# ll /data/
total 20
drwx------ 2 root  root  16384 Apr 18 22:16 lost+found
drwxr-xr-x 5 mysql mysql  4096 Apr 18 23:01 mysql
[root@192.168.0.10 ~]# /etc/init.d/drbd status
drbd driver loaded OK; device status:
version: 8.4.2 (api:1/proto:86-101)
GIT-hash: 7ad5f850d711223713d6dcadc3dd48860321070c build by root@db-server-01, 2014-04-18 21:15:57
m:res  cs         ro                 ds                 p  mounted  fstype
0:r0   Connected  Primary/Secondary  UpToDate/UpToDate  C  /data    ext4
[root@192.168.0.10 ~]#

可以看见已经切换回来了,我们现在可以启动mysql了。

[root@192.168.0.10 ~]# /etc/init.d/mysqld start             
Starting MySQL.......                                      [  OK  ]
[root@192.168.0.10 ~]# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.37-log MySQL Community Server (GPL)

Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

(2)在两台服务器上面启动heartbeat

[root@192.168.0.10 ~]# /etc/init.d/heartbeat start
Starting High-Availability services: INFO:  Resource is stopped
Done.

[root@192.168.0.10 ~]#
[root@192.168.0.20 ~]# /etc/init.d/heartbeat start
Starting High-Availability services: INFO:  Resource is stopped
Done.

[root@192.168.0.20 ~]#
[root@192.168.0.10 ~]# ip addr | grep eth1
3: eth1: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP qlen 1000
    inet 192.168.0.10/24 brd 192.168.0.255 scope global eth1
    inet 192.168.0.88/24 brd 192.168.0.255 scope global secondary eth1
[root@192.168.0.10 ~]#

可以看见虚拟ip192.168.0.88已经存在了。说明成功了。我们看看heartbeat的日志就能发现。

[root@192.168.0.10 ~]# tail -n 20 /var/log/ha-log  
harc(default)[5598]:    2014/04/19_00:25:21 info: Running /etc/ha.d//rc.d/status status
Apr 19 00:25:22 db-server-01 heartbeat: [5591]: info: Comm_now_up(): updating status to active
Apr 19 00:25:22 db-server-01 heartbeat: [5591]: info: Local status now set to: 'active'
Apr 19 00:25:22 db-server-01 heartbeat: [5591]: info: Status update for node db-server-02: status active
harc(default)[5618]:    2014/04/19_00:25:22 info: Running /etc/ha.d//rc.d/status status
Apr 19 00:25:33 db-server-01 heartbeat: [5591]: info: remote resource transition completed.
Apr 19 00:25:33 db-server-01 heartbeat: [5591]: info: remote resource transition completed.
Apr 19 00:25:33 db-server-01 heartbeat: [5591]: info: Initial resource acquisition complete (T_RESOURCES(us))
/usr/lib/ocf/resource.d//heartbeat/IPaddr(IPaddr_192.168.0.88)[5671]:   2014/04/19_00:25:33 INFO:  Resource is stopped
Apr 19 00:25:33 db-server-01 heartbeat: [5635]: info: Local Resource acquisition completed.
harc(default)[5752]:    2014/04/19_00:25:33 info: Running /etc/ha.d//rc.d/ip-request-resp ip-request-resp
ip-request-resp(default)[5752]: 2014/04/19_00:25:33 received ip-request-resp IPaddr::192.168.0.88/24/eth1 OK yes
ResourceManager(default)[5775]: 2014/04/19_00:25:33 info: Acquiring resource group: db-server-01 IPaddr::192.168.0.88/24/eth1 drbddisk::r0 Filesystem::/dev/drbd0::/data::ext4 mysqld
/usr/lib/ocf/resource.d//heartbeat/IPaddr(IPaddr_192.168.0.88)[5803]:   2014/04/19_00:25:33 INFO:  Resource is stopped
ResourceManager(default)[5775]: 2014/04/19_00:25:33 info: Running /etc/ha.d/resource.d/IPaddr 192.168.0.88/24/eth1 start
IPaddr(IPaddr_192.168.0.88)[5926]:      2014/04/19_00:25:34 INFO: Adding inet address 192.168.0.88/24 with broadcast address 192.168.0.255 to device eth1
IPaddr(IPaddr_192.168.0.88)[5926]:      2014/04/19_00:25:34 INFO: Bringing device eth1 up
IPaddr(IPaddr_192.168.0.88)[5926]:      2014/04/19_00:25:34 INFO: /usr/libexec/heartbeat/send_arp -i 200 -r 5 -p /var/run/resource-agents/send_arp-192.168.0.88 eth1 192.168.0.88 auto not_used not_used
/usr/lib/ocf/resource.d//heartbeat/IPaddr(IPaddr_192.168.0.88)[5900]:   2014/04/19_00:25:34 INFO:  Success
/usr/lib/ocf/resource.d//heartbeat/Filesystem(Filesystem_/dev/drbd0)[6030]:     2014/04/19_00:25:34 INFO:  Running OK
[root@192.168.0.10 ~]#

激动的时刻到了,我们测试一下自动切换。我们先看看两台服务器的状态:

[root@192.168.0.10 ~]# df -HT
Filesystem    Type     Size   Used  Avail Use% Mounted on
/dev/sda2     ext4      19G   3.5G    15G  20% /
tmpfs        tmpfs     121M      0   121M   0% /dev/shm
/dev/sda1     ext4     204M    52M   141M  27% /boot
/dev/drbd0    ext4      33G   216M    32G   1% /data
[root@192.168.0.10 ~]#
[root@192.168.0.20 ~]# df -HT
Filesystem    Type     Size   Used  Avail Use% Mounted on
/dev/sda2     ext4      19G   4.9G    13G  28% /
tmpfs        tmpfs     121M      0   121M   0% /dev/shm
/dev/sda1     ext4     204M    52M   141M  27% /boot
[root@192.168.0.20 ~]#

可以看见挂载在第一台服务器。

测试方法:

1.停掉master上的mysqld,看看是否切换(因为heartheat不检查服务的可用性,因此需要通过而外的脚本来实现)。
2.停掉master的heartheat看看是否能正常切换。
3.停掉master的网络或者直接将master系统shutdown,看看能否正常切换。
4.启动master的heartbeat看看是否能正常切换回来。
5.重新启动master看看能否切换过程是否OK。
注意:这里说的切换是不是已经将mysql停掉、是否卸载了文件系统等等。

我就停止master(192.168.0.10)上的heartbeat来测试是否会自动切换,这里除了第一条无法实现,其他的都可以切换:

[root@192.168.0.10 ~]# /etc/init.d/heartbeat stop
Stopping High-Availability services: Done.
[root@192.168.0.10 ~]# df -HT
Filesystem    Type     Size   Used  Avail Use% Mounted on
/dev/sda2     ext4      19G   3.5G    15G  20% /
tmpfs        tmpfs     121M      0   121M   0% /dev/shm
/dev/sda1     ext4     204M    52M   141M  27% /boot
[root@192.168.0.10 ~]# /etc/init.d/drbd status
drbd driver loaded OK; device status:
version: 8.4.2 (api:1/proto:86-101)
GIT-hash: 7ad5f850d711223713d6dcadc3dd48860321070c build by root@db-server-01, 2014-04-18 21:15:57
m:res  cs         ro                 ds                 p  mounted  fstype
0:r0   Connected  Secondary/Primary  UpToDate/UpToDate  C
[root@192.168.0.10 ~]#

可以看见已经切换了,我们看另外一台机器的情况:

[root@192.168.0.20 ~]# df -HT
Filesystem    Type     Size   Used  Avail Use% Mounted on
/dev/sda2     ext4      19G   4.9G    13G  28% /
tmpfs        tmpfs     121M      0   121M   0% /dev/shm
/dev/sda1     ext4     204M    52M   141M  27% /boot
/dev/drbd0    ext4      33G   216M    32G   1% /data
[root@192.168.0.20 ~]# netstat -nltp | grep 3306 | grep -v grep
tcp        0      0 0.0.0.0:3306                0.0.0.0:*                   LISTEN      5542/mysqld         
[root@192.168.0.20 ~]#

可以发现已经切换过来,mysql也自动启动了。之前是没有启动的。

[root@192.168.0.20 ~]# ip addr | grep eth1
3: eth1: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP qlen 1000
    inet 192.168.0.20/24 brd 192.168.0.255 scope global eth1
    inet 192.168.0.88/24 brd 192.168.0.255 scope global secondary eth1
[root@192.168.0.20 ~]# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.37-log MySQL Community Server (GPL)

Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

可以看见,一切正常呢。如果我们查看日志,就可以看见到底发生了什么。

[root@192.168.0.20 ~]# tail -n 10 /var/log/ha-log 
ResourceManager(default)[4768]: 2014/04/19_00:36:42 info: Running /etc/ha.d/resource.d/Filesystem /dev/drbd0 /data ext4 start
Filesystem(Filesystem_/dev/drbd0)[5131]:        2014/04/19_00:36:42 INFO: Running start for /dev/drbd0 on /data
/usr/lib/ocf/resource.d//heartbeat/Filesystem(Filesystem_/dev/drbd0)[5122]:     2014/04/19_00:36:42 INFO:  Success
ResourceManager(default)[4768]: 2014/04/19_00:36:43 info: Running /etc/init.d/mysqld  start
mach_down(default)[4741]:       2014/04/19_00:36:46 info: /usr/share/heartbeat/mach_down: nice_failback: foreign resources acquired
mach_down(default)[4741]:       2014/04/19_00:36:46 info: mach_down takeover complete for node db-server-01.
Apr 19 00:36:46 db-server-02 heartbeat: [4637]: info: mach_down takeover complete.
Apr 19 00:36:58 db-server-02 heartbeat: [4637]: WARN: node db-server-01: is dead
Apr 19 00:36:58 db-server-02 heartbeat: [4637]: info: Dead node db-server-01 gave up resources.
Apr 19 00:36:58 db-server-02 heartbeat: [4637]: info: Link db-server-01:eth1 dead.
[root@192.168.0.20 ~]#

对于mysqld服务挂掉的情况无法实现自动切换,所以需要一个脚本来帮助我们完成,我这里有个简单的脚本,能实现当mysqld服务不可用时进行自动切换,当进行切换时发送邮件等。该脚本放在主服务器执行,也就是运行mysqld服务的服务器上执行。

[root@192.168.0.20 ~]# cat mysqlmon.sh 
#!/bin/bash
trap 'echo  PROGRAM INTERRUPTED; exit 1'  INT
username=root
password=123456
n=0
log='/var/log/mysqlmon.log'
while true
do
    if /usr/local/mysql/bin/mysql  -u${username} -p${password} -e "use test"   >&/dev/null
    then
        echo `date +"%Y-%m-%d  %H:%M:%S"`  mysqld is alive!  >> ${log}
        n=0
    else
        echo  "`date +"%Y-%m-%d  %H:%M:%S"`  mysqld  cannot be  connected!"  >> ${log}
        n=$[n + 1]
        if [ $n -eq 3 ]
        then
            /etc/init.d/heartbeat stop
            echo  "`date +"%Y-%m-%d  %H:%M:%S"`  mysqld  switched to backup!" >> ${log}
            echo "`date +"%Y-%m-%d  %H:%M:%S"`  mysqld  switched to backup" | mutt -s "mysqld switched to backup" saltstack@163.com
            break
        fi
    fi
    sleep 10
done

[root@192.168.0.20 ~]#

挂在后台执行:

[root@192.168.0.10 ~]# nohup mysqlmon.sh &

停止mysqld服务,看是否进行切换以及发送邮件:

[root@192.168.0.10 ~]# /etc/init.d/mysqld stop
Shutting down MySQL.                                       [  OK  ]
[root@192.168.0.10 ~]#

[root@192.168.0.20 ~]# df -HT
Filesystem    Type     Size   Used  Avail Use% Mounted on
/dev/sda2     ext4      19G   4.9G    13G  28% /
tmpfs        tmpfs     121M      0   121M   0% /dev/shm
/dev/sda1     ext4     204M    52M   141M  27% /boot
/dev/drbd0    ext4      33G   216M    32G   1% /data
[root@192.168.0.20 ~]# netstat -nltp | grep 3306
tcp        0      0 0.0.0.0:3306                0.0.0.0:*                   LISTEN      13771/mysqld        
[root@192.168.0.20 ~]#

总结:

搭建还不算复杂,但是也踩了不少坑,比如yum安装的heartbeat没有drbddisk脚本。该方案的优点是安全性高、稳定性高、可用性高,出现故障自动切换,但是缺点也很明显,只有一台服务器提供服务,成本相对较高。不方便扩展。可能会发生脑裂。当mysql服务挂掉或者不可用的情况下不能进行自动切换,需要通过crm模式实现或者额外的脚本实现(比如shell脚本监测到master的mysql不可用就将主上的heartbeat停掉,这样就会切换到backup中去)。监控也特别重要,可以使用nagios或者zabbix监控。

 

参考资料:

http://wiki.weithenn.org/cgi-bin/wiki.pl?HA-DRBD_Heartbeat_%E5%BB%BA%E7%BD%AE_MySQL_%E9%AB%98%E5%8F%AF%E7%94%A8%E6%80%A7

利用mysqldump实现mysql数据库全量备份和增量备份脚本

mysqldump实现全量备份,增量备份脚本,

增量备份,其实就是复制日志

全备脚本:
#! /bin/bash
# mysql 全量备份脚本,建议在slave上运行,并开启log_slave_updates=1
mkdir /backup
cd /backup
datadir=`date +”%y-%m-%d”`
mkdir -p $datadir/data
path=/usr/local/mysql/data
for i in `mysql -uroot -p123456 -e “show databases” |grep -v “Database”`
do
mysqldump -uroot -p123456 –defaultes-character-set=utf8 -q –lock-all-tables –flush-logs -E -R –triggers -B $i |gzip > /backup/$datadir/data/${i}_${datadir}.sql.gz
#mysqldump -uroot -ptiancity –opt –single-transaction –flush-logs -E -R -C –triggers $i |gzip > “/backup/$datadir/data/${i}_${datadir}.sql.gz”
done
binlog_rm=`tail -n 1 /app/mysql/data/mysql-bin.index |sed ‘s/\/app\/mysql\/data\///’`
mysql -uroot -p123456 -e “purge binary logs to ‘$binlog_rm'”

增备脚本:
#! /bin/bash
# mysql 增量备份脚本,其实就是复制二进制日志,
cd /backup
datadir=`date +”%y-%m-%d”`
mkdir -p $datadir/data
path=/usr/local/mysql/data
mysqladmin -uroot -p12334 flush-logs
# mysql -uroot -p12334 -e “flush logs”
binlog_cp=`head -n -1 $path/mysql-bin.index |sed ‘s/\/app\/mysql\/data\///’`
for i in $binlog_cp
do
cp $path/$i /backup/$datadir/data/
done
binlog_rm=`tail -n 1 /app/mysql/data/mysql-bin.index |sed ‘s/\/app\/mysql\/data\///’`
mysql -uroot -p12334 -e “purge binary logs to ‘$binlog_rm'”

mysql数据库参数文件性能调试工具:tuning-primer.sh

个人感觉,这脚本非常不错,可以很直观的发现,参数配置上的问题:
下载地址为:http://www.day32.com/MySQL/tuning-primer.sh 
 
下面安装,演示一下:
1.安装的话,如果能连外网,直接使用如下命令(不能连接外网,就先下来再使用)
 [root@10-9-7-79 test]#  wget http://www.day32.com/MySQL/tuning-primer.sh
2.给予脚本执行权限:
[root@10-9-7-79 test]# chmod 755 tuning-primer.sh
3.执行tuning-primer.sh 脚本:
[root@10-9-7-79 test]# ./tuning-primer.sh
4.下面就是测试结果:[测试结果内容,主要看每个测试结果的最后几行,特别注意内存这块,这块很容易就配置超出内存]
Using login values from ~/.my.cnf
– INITIAL LOGIN ATTEMPT FAILED –
Testing for stored webmin passwords:
None Found
Could not auto detect login info!
Found potential sockets: /tmp/mysql.sock
Using: /tmp/mysql.sock
# 以下这几项需要输入的,相信各位都能看得懂,就不解释
Would you like to provide a different socket?: [y/N] n
Do you have your login handy ? [y/N] : y
User: root
Password: 122345

Would you like me to create a ~/.my.cnf file for you? [y/N] : n

— MYSQL PERFORMANCE TUNING PRIMER —
– By: Matthew Montgomery –

MySQL Version 5.5.37-log x86_64

Uptime = 5 days 4 hrs 11 min 46 sec
Avg. qps = 0
Total Questions = 13261
Threads Connected = 1

Server has been running for over 48hrs.
It should be safe to follow these recommendations

To find out more information on how each of these
runtime variables effects performance visit:
http://dev.mysql.com/doc/refman/5.5/en/server-system-variables.html
Visit http://www.mysql.com/products/enterprise/advisors.html
for info about MySQL’s Enterprise Monitoring and Advisory Service

SLOW QUERIES
The slow query log is enabled.
Current long_query_time = 1.000000 sec.
You have 1533 out of 13282 that take longer than 1.000000 sec. to complete
Your long_query_time seems to be fine

BINARY UPDATE LOG
The binary update log is enabled
Binlog sync is not enabled, you could loose binlog records during a server crash

WORKER THREADS
Current thread_cache_size = 512
Current threads_cached = 5
Current threads_per_sec = 0
Historic threads_per_sec = 0
Your thread_cache_size is fine

MAX CONNECTIONS
Current max_connections = 1000
Current threads_connected = 1
Historic max_used_connections = 6
The number of used connections is 0% of the configured maximum.
You are using less than 10% of your configured max_connections.
Lowering max_connections could help to avoid an over-allocation of memory
See “MEMORY USAGE” section to make sure you are not over-allocating

INNODB STATUS
Current InnoDB index space = 160 M
Current InnoDB data space = 2.10 G
Current InnoDB buffer pool free = 46 %
Current innodb_buffer_pool_size = 4.00 G
Depending on how much space your innodb indexes take up it may be safe
to increase this value to up to 2 / 3 of total system memory

MEMORY USAGE
Max Memory Ever Allocated : 4.10 G
Configured Max Per-thread Buffers : 2.31 G
Configured Max Global Buffers : 4.09 G
Configured Max Memory Limit : 6.41 G
Physical Memory : 7.62 G
Max memory limit seem to be within acceptable norms

KEY BUFFER
Current MyISAM index space = 109 K
Current key_buffer_size = 64 M
Key cache miss rate is 1 : 5
Key buffer free ratio = 81 %
Your key_buffer_size seems to be fine

QUERY CACHE
Query cache is enabled
Current query_cache_size = 2 M
Current query_cache_used = 16 K
Current query_cache_limit = 1 M
Current Query cache Memory fill ratio = .82 %
Current query_cache_min_res_unit = 4 K
Your query_cache_size seems to be too high.
Perhaps you can use these resources elsewhere
MySQL won’t cache query results that are larger than query_cache_limit in size

SORT OPERATIONS
Current sort_buffer_size = 256 K
Current read_rnd_buffer_size = 256 K
Sort buffer seems to be fine

JOINS
Current join_buffer_size = 132.00 K
You have had 0 queries where a join could not use an index properly
Your joins seem to be using indexes properly

OPEN FILES LIMIT
Current open_files_limit = 65535 files
The open_files_limit should typically be set to at least 2x-3x
that of table_cache if you have heavy MyISAM usage.
Your open_files_limit value seems to be fine

TABLE CACHE
Current table_open_cache = 4096 tables
Current table_definition_cache = 4096 tables
You have a total of 183 tables
You have 184 open tables.
The table_cache value seems to be fine

TEMP TABLES
Current max_heap_table_size = 16 M
Current tmp_table_size = 16 M
Of 3294 temp tables, 38% were created on disk
Perhaps you should increase your tmp_table_size and/or max_heap_table_size
to reduce the number of disk-based temporary tables
Note! BLOB and TEXT columns are not allow in memory tables.
If you are using these columns raising these values might not impact your
ratio of on disk temp tables.

TABLE SCANS
Current read_buffer_size = 512 K
Current table scan ratio = 9703 : 1
You have a high ratio of sequential access requests to SELECTs
You may benefit from raising read_buffer_size and/or improving your use of indexes.

TABLE LOCKING
Current Lock Wait ratio = 0 : 13543
Your table locking seems to be fine

mysql优化可以考虑的21个方面

在日常朋友间交流中,总会谈论数据库的优化问题,下面为转载的一片文章,优化方面说的还是蛮详细的。

细节东西(table cache, 表设计,索引设计,程序端缓存之类的)先不赘述。

1. 要确保有足够的内存

数据库能够高效的运行,最关建的因素需要内存足够大,能缓存住数据,更新也可以在内存先完成。但不同的业务对内存需要强度不一样,推荐内存要占到数据的15-25%的比例,特别的热的数据,内存基本要达到数据库的80%大小。

2. 需要更多更快的CPU

MySQL 5.6可以利用到64个核,而MySQL每个query只能运行在一个CPU上,所以要求更多的CPU,更快的CPU会更有利于并发。

3. 要选择合适的操作系统

在官方建议估计最推荐的是Solaris, 从实际生产中看CentOS, REHL都是不错的选择,推荐使用CentOS, REHL 版本为6以后的,当然Oracle Linux也是一个不错的选择。虽然从MySQL 5.5后对Windows做了优化,但也不推荐在高并发环境中使用windows.

4. 合理的优化系统的参数

更改文件句柄 ulimit –n 默认1024 太小

进程数限制 ulimit –u 不同版本不一样

禁掉NUMA numctl –interleave=all

修改方法:vim /etc/profile ==> 添加 ulimit -HSn 65535 ,然后 source /etc/profile

5. 选择合适的内存分配算法

默认的内存分配就是c的malloc 现在也出现许多优化的内存分配算法:

jemalloc and tcmalloc

从MySQL 5.5后支持声明内存储方法。

[mysqld_safe]

malloc-lib = tcmalloc

或是直接指到so文件

[mysqld_safe]

malloc-lib=/usr/local/lib/libtcmalloc_minimal.so

6. 使用更快的存储设备ssd或是固态卡

存储介质十分影响MySQL的随机读取,写入更新速度。新一代存储设备固态ssd及固态卡的出现也让MySQL 大放异彩。

7. 选择良好的文件系统推荐XFS, Ext4

如果还在使用ext2,ext3的同学请尽快升级别。推荐XFS,这个也是今后一段时间Linux会支持一个文件系统。

文件系统强烈推荐: XFS

查看方式为: df -Th

8. 优化挂载文件系统的参数

挂载XFS参数:(rw, noatime,nodiratime,nobarrier)

挂载ext4参数:ext4 (rw,noatime,nodiratime,nobarrier,data=ordered)

如果使用SSD或是固态盘需要考虑:

innodb_page_size = 4K

Innodb_flush_neighbors = 0

9. 选择适合的IO调度

正常请下请使用deadline 默认是noop

echo dealine > /sys/block/{DEV-NAME}/queue/scheduler

10. 选择合适的Raid卡

Cache策略请使用带电的Raid,启用WriteBack,对于加速redo log ,binary log, data file都有好处。

11. 禁用Query Cache

Query Cache在Innodb中有点鸡肋,Innodb的数据本身可以在Innodb buffer pool中缓存,Query Cache属于结果集缓存,如果开启Query Cache更新写入都要去检查query cache反而增加了写入的开销。
在MySQL 5.6中Query cache是被禁掉了。

12. 使用Thread Pool

现在一个数据对应5个以上App场景比较,但MySQL有个特性随着连接增多的情况下性能反而下降,所以对于连接超过200的以后场景请考虑使用thread pool. 这是一个伟大的发明。

这个mysql 5.5中提供,但仅是商业版提供,所以建议使用mariadb

13. 合理调整内存

(1). 减少连接的内存分配,连接可以用thread_cache_size缓存,观察不如thread pool给力。数据库在连上分配的内存如下:

max_used_connections * (read_buffer_size +read_rnd_buffer_size +join_buffer_size +sort_buffer_size +binlog_cache_size +thread_stack +2 * net_buffer_length …)

(2). 使较大的buffer pool要把60-80%的内存分给innodb_buffer_pool_size. 这个不要超过数据大小了,另外也不要分配超过80%,不然会利用到swap.

14. 合理选择LOG刷新机制Redo Logs:

– innodb_flush_log_at_trx_commit = 1 // 最安全

– innodb_flush_log_at_trx_commit = 2 //  较好性能

– innodb_flush_log_at_trx_commit = 0 //  最好的情能
binlog :

sync_binlog= 1 需要group commit支持,如果没这个功能可以考虑binlog_sync=0来获得较佳性能。
关于组提交实现的前提条件是:应该是:【 sync_binlog=0,innodb_support_xa=0】,不知道理解对否?大家可以发表想法.

数据文件:innodb_flush_method = O_DIRECT

15. 请使用Innodb表,以利用更多资源,在线alter操作有所提高

目前也支持非中文的full text(mysql 5.6支持), 同时支持Memcache API访问。目前也是MySQL最优秀的一个引擎。如果你还在MyISAM请考虑快速转换。

16. 设置较大的Redo log

以前Percona 5.5和官方MySQL 5.5比拼性能时,胜出的一个Tips就是分配了超过4G的Redo log ,而官方MySQL5.5 redo log不能超过4G. 从 MySQL 5.6后可以超过4G了,通常建Redo log加起来要超过500M。 可以通过观查redo log产生量,分配Redo log大于一小时的量即可。

17. 优化磁盘的IO

innodb_io_capactiy 在sas 15000转的下配置800就可以了,在ssd下面配置2000以上。
在MySQL 5.6:

innodb_lru_scan_depth = innodb_io_capacity / innodb_buffer_pool_instances

innodb_io_capacity_max = min(2000, 2 * innodb_io_capacity)

18. 使用独立表空间

目前来看新的特性都是独立表空间支持:

truncate table 表空间回收,表空间传输,较好的去优化碎片等管理性能的增加,整体上来看使用独立表空间是没用的。

19. 配置合理的并发

innodb_thread_concurrency =并发数

这个参数在Innodb中变化也是最频繁的一个参数。不同的版本,有可能不同的小版本也有变动。一般推荐:

在使用thread pool 的情况下:

innodb_thread_concurrency = 0 就可以了。

如果在没有thread pool的情况下:

5.5 推荐:innodb_thread_concurrency =16 – 32

5.6 推荐:innodb_thread_concurrency = 36

20. 优化事务隔离级别

默认是 Repeatable read

推荐使用Read committed: 这个观点,我也有点不太同意此篇文章所讲的,mysql的默认隔离级别可能会忧于Read committed

binlog格式使用mixed或是Row

较低的隔离级别 = 较好的性能

21. 注重监控。

任何环境离不开监控,如果少了监控,有可能就会陷入盲人摸象。 推荐zabbix+mpm构建监控。

文章转载地址:http://www.mysqlsupport.cn/21ways-optimize-mysql/