首页 > 代码库 > mysql mha高可用架构的安装

mysql mha高可用架构的安装

2024-11-05 11:51:02   203人阅读

MMM无法全然地保证数据的一致性，所以MMM适用于对数据的一致性要求不是非常高。可是又想最大程度的保证业务可用性的场景对于那些对数据一致性要求非常高的业务，非常不建议採用MMM的这样的高可用性架构。那么可以考虑使用MHA。在mysql故障切换的过程中。MHA可以在0-30s内自己主动完毕数据库的故障切换操作，而且MHA可以最大程度上保证数据的一致性，以达到真正意义上的高可用。


MHA软件由两部分组成，Manager工具包和Node工具包。详细的说明例如以下。

Manager工具包主要包含下面几个工具：
masterha_check_ssh              检查MHA的SSH配置状况
masterha_check_repl             检查MySQL复制状况
masterha_manger                 启动MHA
masterha_check_status           检測当前MHA执行状态
masterha_master_monitor         检測master是否宕机
masterha_master_switch          控制故障转移（自己主动或者手动）
masterha_conf_host              加入或删除配置的server信息

Node工具包（这些工具通常由MHA Manager的脚本触发。无需人为操作）主要包含下面几个工具：
save_binary_logs                保存和复制master的二进制日志
apply_diff_relay_logs           识别差异的中继日志事件并将其差异的事件应用于其它的slave
filter_mysqlbinlog              去除不必要的ROLLBACK事件（MHA已不再使用这个工具）
purge_relay_logs                清除中继日志（不会堵塞SQL线程）


注意：
（1）为了尽可能的降低主库硬件损坏宕机造成的数据丢失。因此在配置MHA的同一时候建议配置成MySQL 5.5的半同步复制

1.1、搭建好开发环境
<img src=http://www.mamicode.com/"http://img.blog.csdn.net/20160324175821383?
watermark/2/text/aHR0cDovL2Jsb2cuY3Nkbi5uZXQv/font/5a6L5L2T/fontsize/400/fill/I0JBQkFCMA==/dissolve/70/gravity/Center" alt="" />
1.2、yaolansvr安装ftp服务，并上传mha安装软件
（1）关闭selinux，否则vsftpd报226错误
# mkdir -p /yangsq/ftp
# useradd -d /yangsq/ftp -s /sbin/nologin uftp
# passwd uftp
# chown -R uftp:uftp /yangsq/ftp

# yum list all|grep vsftpd
# yum -y install vsftpd.x86_64

# cp /etc/vsftpd/vsftpd.conf /etc/vsftpd/vsftpd.conf.bak
anonymous_enable=YES改为anonymous_enable=NO
local_enable=YES
write_enable=YES
chroot_local_user=YES

# chkconfig vsftpd on
# service vsftpd start

# yum install ftp.x86_64 -y

# sestatus
SELinux status:                 enabled
SELinuxfs mount:                /selinux
Current mode:                   enforcing
Mode from config file:          enforcing
Policy version:                 24
Policy from config file:        targeted
# setenforce 0

# ftp 192.168.0.3 21

1.3、全部数据库节点安装Perl模块，同一时候做
yum -y install perl-DBD-MySQL
yum -y install perl-CPAN.x86_64

cd /yangsq/ftp
tar xvf mha4mysql-node-0.54.tar.gz

cd mha4mysql-node-0.54
perl Makefile.PL

make && make install
Installing /usr/local/share/perl5/MHA/BinlogPosFinderXid.pm
Installing /usr/local/share/perl5/MHA/SlaveUtil.pm
Installing /usr/local/share/perl5/MHA/NodeUtil.pm
Installing /usr/local/share/perl5/MHA/BinlogPosFinderElp.pm
Installing /usr/local/share/perl5/MHA/BinlogPosFindManager.pm
Installing /usr/local/share/perl5/MHA/BinlogPosFinder.pm
Installing /usr/local/share/perl5/MHA/BinlogManager.pm
Installing /usr/local/share/perl5/MHA/NodeConst.pm
Installing /usr/local/share/perl5/MHA/BinlogHeaderParser.pm
Installing /usr/local/share/man/man1/filter_mysqlbinlog.1
Installing /usr/local/share/man/man1/purge_relay_logs.1
Installing /usr/local/share/man/man1/apply_diff_relay_logs.1
Installing /usr/local/share/man/man1/save_binary_logs.1
Installing /usr/local/bin/filter_mysqlbinlog
Installing /usr/local/bin/apply_diff_relay_logs
Installing /usr/local/bin/save_binary_logs
Installing /usr/local/bin/purge_relay_logs

1.4、在yaolansvr_slave安装mha manager
（1）No package perl-Log-Dispatch available.
# mv CentOS-Base.repo CentOS-Base.repo.bak
sftp> put C:\Users\Yaolan\Downloads\CentOS6-Base-163.repo
# yum clean all
# yum makecache
或者
# rpm -ivh http://dl.fedoraproject.org/pub/epel/6/x86_64/epel-release-6-8.noarch.rpm

yum -y install perl-Config-Tiny
yum install perl-Log-Dispatch -y
yum install perl-Parallel-ForkManager -y
yum install perl-Time-HiRes -y

# tar xvf mha4mysql-manager-0.54.tar.gz
# cd mha4mysql-manager-0.54
# perl Makefile.PL 

# make && make install
Installing /usr/local/share/perl5/MHA/ManagerAdminWrapper.pm
Installing /usr/local/share/perl5/MHA/ManagerUtil.pm
Installing /usr/local/share/perl5/MHA/MasterFailover.pm
Installing /usr/local/share/perl5/MHA/MasterMonitor.pm
Installing /usr/local/share/perl5/MHA/ManagerAdmin.pm
Installing /usr/local/share/perl5/MHA/Config.pm
Installing /usr/local/share/perl5/MHA/DBHelper.pm
Installing /usr/local/share/perl5/MHA/HealthCheck.pm
Installing /usr/local/share/perl5/MHA/FileStatus.pm
Installing /usr/local/share/perl5/MHA/MasterRotate.pm
Installing /usr/local/share/perl5/MHA/Server.pm
Installing /usr/local/share/perl5/MHA/ServerManager.pm
Installing /usr/local/share/perl5/MHA/SSHCheck.pm
Installing /usr/local/share/perl5/MHA/ManagerConst.pm
Installing /usr/local/share/man/man1/masterha_check_ssh.1
Installing /usr/local/share/man/man1/masterha_secondary_check.1
Installing /usr/local/share/man/man1/masterha_conf_host.1
Installing /usr/local/share/man/man1/masterha_check_status.1
Installing /usr/local/share/man/man1/masterha_stop.1
Installing /usr/local/share/man/man1/masterha_manager.1
Installing /usr/local/share/man/man1/masterha_master_monitor.1
Installing /usr/local/share/man/man1/masterha_check_repl.1
Installing /usr/local/share/man/man1/masterha_master_switch.1
Installing /usr/local/bin/masterha_manager
Installing /usr/local/bin/masterha_check_ssh
Installing /usr/local/bin/masterha_check_status
Installing /usr/local/bin/masterha_master_monitor
Installing /usr/local/bin/masterha_secondary_check
Installing /usr/local/bin/masterha_conf_host
Installing /usr/local/bin/masterha_check_repl
Installing /usr/local/bin/masterha_stop
Installing /usr/local/bin/masterha_master_switch

1.5、全部mysql服务器配置ssh登录无密码验证，sshd_config已改不生效，万能的重新启动
注意：
（1）ssh-copy-id: command not found，解决：yum install openssh-clients -y
（2）对自己本身也要运行ssh-copy-id
（3）关闭slave后重新启动mysql实例，slave相关的进程正常启动
# ssh-keygen -t rsa
Generating public/private rsa key pair.
Enter file in which to save the key (/root/.ssh/id_rsa)

# yum install -y openssh-clients
# ssh-copy-id -i /root/.ssh/id_rsa.pub root@192.168.0.3
# ssh-copy-id -i /root/.ssh/id_rsa.pub root@192.168.0.4
# ssh-copy-id -i /root/.ssh/id_rsa.pub root@192.168.0.5

1.6、搭建主从复制环境
yaolansvr服务器master节点-----------
注意：
（1）datadir和server-id的设置，candidate master和slave仅仅改动server-id
（2）
# vi /usr/mysql/etc/my.cnf
[mysqld]
port = 3306
datadir=/data/mysql/data

#慢查询设置
slow-query-log-file=/var/log/MysqlQuery.log
long_query_time =5
slow_query_log=1

#server-id
server-id=16803

#binlog 设置
log-bin = /data/mysql/data/mysql-bin.log
binlog_cache_size = 8M
binlog_format=mixed

#全局
join_buffer_size = 2M
sort_buffer_size = 2M
read_rnd_buffer_size = 2M
read_buffer_size = 2M
max_heap_table_size = 64M
thread_cache_size=12
thread_concurrency = 12
query_cache_type = 1
query_cache_size = 32M
ft_min_word_len = 4
thread_stack = 192K
tmp_table_size = 64M

#myisam内存设置
key_buffer_size=1024M

#同意最大的复制传输
max_allowed_packet=64M
#跳过dns解析
skip-name-resolve
#连接数设置
max_connections = 1000
max_connect_errors = 200

#innodb设置
innodb_buffer_pool_size = 1G
innodb_additional_mem_pool_size = 16M
innodb_log_buffer_size = 8M
innodb_log_file_size = 512M
innodb_log_files_in_group = 3
innodb_file_per_table=1
innodb_stats_persistent_sample_pages=1000
innodb_write_io_threads = 8
innodb_read_io_threads = 8
innodb_thread_concurrency = 16
innodb_flush_log_at_trx_commit = 2
innodb_lock_wait_timeout = 30
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

# service mysqld start 

1.6.1、在yaolansvr做备份，并创建复制用户
# mysqldump  -A --flush-privileges --lock-all-tables --events --routines --triggers --master-data=http://www.mamicode.com/2>/yangsq/ftp/`date +%Y-%m-%d`_all.sql>

mysql mha高可用架构的安装


联系
我们