|
环境说明：本环境由两台mysql 数据库和heartbeat 组成，一台的ip 为192.168.10.197，一台为192.168.10.198，对外提供服务的vip 为192.168.10.200 备注：heartbeat 本身是不能做到服务不可用自动切换的，所以用结合 额外的脚本才可以做到，本文中提到的moniter 脚本即为实现某个 mysql 服务不可用的时候自动切换的还可以自动报警 安装和配置过程分为如下几步： 第一部分：mysql 的安装配置 1 安装 1.1. 添加mysql 运行所需的用户和组 groupadd mysql useradd -g mysql mysql 1.2. 解压安装 tar mysql-5.1.26-rc-linux-x86_64-glibc23.tar.gz mv mysql-5.1.26-rc-linux-x86_64-glibc23 /usr/local/mysql/ chown -R mysql:mysql /usr/local/mysql 1.3. 复制主配置文件和启动脚本 cd /usr/local/mysql cp support-files/my-medium.cnf /etc/my.cf cp support-files/mysql.server /etc/init.d/mysqld chmod +x /etc/init.d/mysqld 1.4. 初始化数据库 cd /usr/local/mysql scripts/mysql_install_db --user=mysql 1.5. 更改数据目录的所有者和组 作者：曲宝全，转载标明出处！ 2 / 14 chown -R mysql:mysql ./data 1.6. 注册mysql 为系统服务 chkconfig --add mysqld chkconfig --levels 2345 mysqld on 1.7. 输出环境变量 #Vi /etc/profile(内容如下) PATH=$PATH:/usr/local/mysql/bin # source /etc/profile 1.8. 设置数据库密码 service mysqld start mysqladmin password 123456（密码自己定这里只是个例子） 1.9. 开启root 远程访问权限 mysql –p123456 mysql> grant all on *.* to root@'%' identified by '123456'; Query OK, 0 rows affected (0.00 sec) mysql> flush privileges; mysql> quit 2 参数设置 2.1. 创建相关目录并设置权限 mkdir /usr/local/mysql/binlog/ chown –R mysql:mysql /usr/local/mysql/binlog/ touch /var/log/mysql.log chown –R mysql:mysql /var/log/mysql.log 2.2. 添加同步复制的账号（主库和辅库的设置相同） [root@master ~]# mysql -p Enter password:（输入root 密码） mysql> grant all on *.* to qiangao identified by '123456';（其中的账号和密码要 和配置文件中指定的相同） mysql> flush privileges; 2.3. 修改主配置文件 作者：曲宝全，转载标明出处！ 3 / 14 注意：其中ip 要互相指向对方的ip 按实际情况来设定，用户名和密码要和上文的用户名密 码保持一致 主库设置 vi /etc/my.cnf（用如下内容覆盖原有内容） ################################################ ########## [client] port = 3306 socket = /tmp/mysql.sock [mysqld] ################### auto_increment ########################### auto_increment_offset = 1 auto_increment_increment = 2 ############## other options ############## default-character-set = utf8 default-storage-engine = InnoDB default-table-type = INNODB max_connections = 800 port = 3306 socket = /tmp/mysql.sock skip-locking ########## MyISAM options ################# myisam_max_sort_file_size = 10G myisam_max_extra_sort_file_size = 10G myisam_sort_buffer_size = 10M myisam_repair_threads = 1 ################ select cache options ################## read_buffer_size = 2M read_rnd_buffer_size = 16M bulk_insert_buffer_size = 5M 作者：曲宝全，转载标明出处！ 4 / 14 max_allowed_packet = 1M table_cache = 2048 query_cache_size = 32M query_cache_limit = 2M sort_buffer_size = 8M join_buffer_size = 8M thread_concurrency = 8 ################ index cache options ################## key_buffer_size = 32M key_buffer_size = 32M ################# master ######################### server-id = 1 log-bin=/usr/local/mysql/binlog/master-bin binlog_format=mixed relay-log=/usr/local/mysql/binlog/mysqld-relay-bin ##################### slave ########################## relay-log=/usr/local/mysql/binlog/slave-relay-bin master-host=192.168.10.198 master-user=qiangao master-password=123456 master-connect-retry=10 ############## log ################## log-error=/var/log/mysql.log ######### INNODB ######### innodb_file_per_table #+++++++ log ++++++++# innodb_log_buffer_size = 10M innodb_mirrored_log_groups = 1 innodb_log_files_in_group = 3 作者：曲宝全，转载标明出处！ 5 / 14 innodb_log_file_size = 50M innodb_flush_log_at_trx_commit = 0 #innodb_log_archive = 0 #+++++++ System buffer +++++++# innodb_buffer_pool_size = 1024M innodb_additional_mem_pool_size = 40M #+++++++ Other +++++++# innodb_file_io_threads = 4 innodb_lock_wait_timeout = 5 innodb_force_recovery = 0 innodb_fast_shutdown = 1 innodb_thread_concurrency = 8 innodb_lock_wait_timeout = 50 transaction-isolation = READ-COMMITTED [mysqldump] quick max_allowed_packet = 16M [mysql] no-auto-rehash # Remove the next comment character if you are not familiar with SQL #safe-updates [isamchk] key_buffer = 128M sort_buffer_size = 128M read_buffer = 2M write_buffer = 2M [myisamchk] key_buffer = 128M sort_buffer_size = 128M read_buffer = 2M 作者：曲宝全，转载标明出处！ 6 / 14 write_buffer = 2M [mysqlhotcopy] interactive-timeout 辅库设置 vi /etc/my.cnf（用如下内容覆盖原有内容，） ################################################ ########## [client] port = 3306 socket = /tmp/mysql.sock [mysqld] ################### auto_increment ########################### auto_increment_offset = 1 auto_increment_increment = 2 ############## other options ############## default-character-set = utf8 default-storage-engine = InnoDB default-table-type = INNODB max_connections = 800 port = 3306 socket = /tmp/mysql.sock skip-locking ########## MyISAM options ################# myisam_max_sort_file_size = 10G myisam_max_extra_sort_file_size = 10G myisam_sort_buffer_size = 10M myisam_repair_threads = 1 ################ select cache options ################## read_buffer_size = 2M read_rnd_buffer_size = 16M 作者：曲宝全，转载标明出处！ 7 / 14 bulk_insert_buffer_size = 5M max_allowed_packet = 1M table_cache = 2048 query_cache_size = 32M query_cache_limit = 2M sort_buffer_size = 8M join_buffer_size = 8M thread_concurrency = 8 ################ index cache options ################## key_buffer_size = 32M key_buffer_size = 32M ################# master ######################### server-id = 2 log-bin=/usr/local/mysql/binlog/master-bin binlog_format=mixed relay-log=/usr/local/mysql/binlog/mysqld-relay-bin ##################### slave ########################## relay-log=/usr/local/mysql/binlog/slave-relay-bin master-host=192.168.10.197 master-user=qiangao master-password=123456 master-connect-retry=10 ############## log ################## log-error=/var/log/mysql/error.log ######### INNODB ######### innodb_file_per_table #+++++++ log ++++++++# innodb_log_buffer_size = 10M innodb_mirrored_log_groups = 1 作者：曲宝全，转载标明出处！ 8 / 14 innodb_log_files_in_group = 3 innodb_log_file_size = 50M innodb_flush_log_at_trx_commit = 0 #innodb_log_archive = 0 #+++++++ System buffer +++++++# innodb_buffer_pool_size = 1024M innodb_additional_mem_pool_size = 40M #+++++++ Other +++++++# innodb_file_io_threads = 4 innodb_lock_wait_timeout = 5 innodb_force_recovery = 0 innodb_fast_shutdown = 1 innodb_thread_concurrency = 8 innodb_lock_wait_timeout = 50 transaction-isolation = READ-COMMITTED [mysqldump] quick max_allowed_packet = 16M [mysql] no-auto-rehash # Remove the next comment character if you are not familiar with SQL #safe-updates [isamchk] key_buffer = 128M sort_buffer_size = 128M read_buffer = 2M write_buffer = 2M [myisamchk] key_buffer = 128M sort_buffer_size = 128M 作者：曲宝全，转载标明出处！ 9 / 14 read_buffer = 2M write_buffer = 2M [mysqlhotcopy] interactive-timeout 2.4. 重新启动服务 service mysqld restart 3 防火墙是设置 注意主库和辅库的防火墙设置是一样的 iptables -I INPUT -p tcp --dport 3306 -j ACCEPT service iptables save 第二部分：heartbeat 的安装和配置 两台主机硬件环境(不必完全一致)： eth0: 对外IP eth1: 对内IP(HA 专用) 两台主机的eht1 使用双机对联线直接连接。 【二】安装前网络环境设定： ================================================ ======== node1: 主机名：master.qiangao.com eth0: 192.168.10.197 //对外IP 地址 eth1: 172.16.1.3 //HA 心跳使用地址 --------------------------- node2: 主机名：slave.qiangao.com eth0: 192.168.10.198 //对外IP 地址 eth1: 172.16.1.4 //HA 心跳使用地址 特别注意要检查以下几个文件： /etc/hosts /etc/host.conf /etc/resolv.conf /etc/sysconfig/network /etc/sysconfig/network-scripts/ifcfg-eth0 /etc/sysconfig/network-scripts/ifcfg-eth1 /etc/nsswitch.conf #vi /etc/hosts node1 的hosts 内容如下： 127.0.0.1 master.qiangao.com master localhost.localdomain localhost 192.168.10.197 master.qiangao.com 作者：曲宝全，转载标明出处！ 10 / 14 192.168.10.198 slave.qiangao.com ::1 localhost6.localdomain6 localhost6 --------------------------- node2 的hosts 内容如下: 127.0.0.1 slave.qiangao.com slave localhost.localdomain localhost 192.168.10.197 master.qiangao.com 192.168.10.198 slave.qiangao.com ::1 localhost6.localdomain6 localhost6 #cat /etc/host.conf order hosts,bind #cat /etc/resolv.conf nameserver 202.96.134.133 //DNS 地址 #cat /etc/sysconfig/network NETWORKING=yes HOSTNAME= master.qiangao.com //主机名 GATEWAY="192.168.10.1" //网关 GATEWAY="eth0" //网关使用网卡 ONBOOT=YES //启动时加载 FORWARD_IPV4="yes" //只允许IPV4 --------------------------- #cat /etc/sysconfig/network-scripts/ifcfg-eth0 DEVICE=eth0 ONBOOT=yes BOOTPROTO=static IPADDR=192.168.10.197 NETMASK=255.255.255.0 GATEWAY=192.168.10.1 TYPE=Ethernet IPV6INIT=no --------------------------- #cat /etc/sysconfig/network-scripts/ifcfg-eth1 DEVICE=eth1 ONBOOT=yes BOOTPROTO=none IPADDR=172.16.1.3 NETMASK=255.255.0.0 TYPE=Ethernet [node1] 与 [node2] 在上面的配置中，除了 /etc/hosts /etc/sysconfig/network /etc/sysconfig/network-scripts/ifcfg-eth0 /etc/sysconfig/network-scripts/ifcfg-eth1 要各自修改外，其他一致。 配置完成后，试试在各自主机上ping 对方的主机名，应该可以ping 通: 作者：曲宝全，转载标明出处！ 11 / 14 [root@master ~]# ping slave.qiangao.com PING slave.qiangao.com (192.168.10.198) 56(84) bytes of data. 64 bytes from slave.qiangao.com (192.168.10.198): icmp_seq=1 ttl=64 time=0.136 ms 【三】安装HA 与HA 依赖包 ================================================ =========== yum install heartbeat yum install ipvsadm yum install libnet 【四】 配置 HA 的各配置文件 首先复制配置文件到/etc 目录 cd /usr/share/doc/heartbeat-2.1.3 cp ha.cf haresources /etc/ha.d/ cp authkeys /etc/ha.d/ ================================================ =========== 配置心跳的加密方式:authkeys 如果使用双机对联线(双绞线)，可以配置如下： #vi /etc/hc.d/authkeys auth 1 1 crc 存盘退出，然后 #chmod 600 authkeys ================================================ =========== 配置心跳的监控：haresources 创建检测脚本（本处如果没有个监测脚本hearbeat 无法启动） vi /etc/init.d/test(内容如下) #!/bin/bash echo "" $>/dev/null chmod 777 /etc/init.d/test ================================================ =========== #vi /etc/ha.d/haresources(配置资源文件) 各主机这部分应完全相同。 master.qiangao.com 192.168.10.200 test 指定 master.qiangao.com 调用预先写好的一个测试脚本，系统附加一个虚拟IP 192.168.10.200 给eth0:0 这里如果master.qiangao.com 宕机后slave.qiangao.com 可以新分配IP 192.168.10.200 ================================================ =========== 配置心跳的配置文件：ha.cf 作者：曲宝全，转载标明出处！ 12 / 14 #vi /etc/ha.d/ha.cf logfile /var/log/ha_log/ha-log.log ## ha 的日志文件记录位置。如没有 该目录，则需要手动添加 bcast eth1 ##使用eht1 做心跳监测 keepalive 2 ##设定心跳(监测)时间时间为2 秒 warntime 4####警告时间 deadtime 6#########确定服务以死的时间 initdead 30 hopfudge 1 udpport 694 ##使用udp 端口694 进行心跳监测 auto_failback on node master.qiangao.com ##节点1，必须要与 uname -n 指令得到的结果一致。 node slave.qiangao.com ##节点2 ping 172.16.1.04 ##通过ping 对方来监测心跳是否正常。 respawn hacluster /usr/lib64/heartbeat/ipfail(因操作系统而异) apiauth ipfail gid=haclient uid=hacluster debugfile /var/logs/ha-debug.log --------------------------- 【五】 HA 服务的启动、关闭 [root@master ha.d]# chkconfig --add heartbeat [root@master ha.d]# chkconfig --levels 2345 heartbeat on 启动HA: service heartbeat start 关闭HA; service heartbeat stop 【六】 防火墙设置 ================================================ ==== heartbeat 默认使用udp 694 端口进行心跳监测。 如果系统有使用iptables 做 防火墙，应记住把这个端口打开。 #vi /etc/sysconfig/iptables 加入以下内容（互相指向对方ip） -A RH-Firewall-1-INPUT -p udp -m udp --dport 694 -d 172.16.1.4 -j ACCEPT 意思是udp 694 端口对 对方的心跳网卡地址 172.16.1.4 开放。 #service iptables restart 重新加载iptables。 第三部分：监控脚本 cat /usr/local/mysql/bin/moniter.sh #!/bin/bash mysql_path=/usr/local/mysql/bin/ user="root" password="123456" email="qubq@qian-gao.com" logfile=/var/log/moniter.log 作者：曲宝全，转载标明出处！ 13 / 14 date=`(date +%y-%m-%d--%H:%M:%S)` sleeptime=30 ip=$(/sbin/ifconfig | grep "inet addr" | grep -v "127.0.0.1" | awk '{print $2;}' | awk -F':' '{print $2;}' | head -1) Slave_IO_Running=$(mysql -u$user -p$password -e 'show slave status\G' | grep "Slave_IO_Running" | awk '{print $2}') Slave_SQL_Running=$(mysql -u$user -p$password -e 'show slave status\G' | grep "Slave_SQL_Running" | awk '{print $2}') echo "plese fix the server of $ip error now!" >$mysql_path/letter letter=$mysql_path/letter mysql -p$password -e "use test;" if [[ $? != 0 ]] then mail -s "{$ip}_database connect lost the srcprits fix it now " $email<$letter killall -9 heartbeat killall -9 mysqld /etc/init.d/mysqld start sleep $sleeptime mysql -p$password -e "use test;" if [ $? == 0 ] then echo "==============>$date<=====================">>$logfile mail -s "{$ip}_database up now " $email sleep $sleeptime service heartbeat start sleep $sleeptime netstat -an |grep udp |grep 694 if [ $? == 0 ] then echo "complete!">>$logfile else mail -s "{$ip} heartbeat can't to up please fix it !" $email<$letter fi else mail -s "{$ip}_database cant't to up plese fix it" $email fi else if [ "$Slave_IO_Running" = "Yes" -a "$Slave_SQL_Running" = "Yes" ] then echo "Slave is running!" >/dev/null 作者：曲宝全，转载标明出处！ 14 / 14 else echo "==============>$date<=====================">>$logfile echo "Slave is not running!" >> $logfile /bin/mail -s "{$ip}_replicate error please fix it " $email<$letter fi fi 然后设置自动化任务每2分钟检测一次就可以了，现在就可以实现双机热备了！ (责任编辑：IT) |