Mysql MHA 集群配置说明

#MHA 集群配置说明（初始化配置，非初始化配置需要全库备份导入）

#0、三台主机之间免密
#1、设置一主两从（使用HMA的my.cnf）
    #主节点（master）
    mysql> grant replication slave,replication client on *.* to slave@'%' identified by '123456';  #（所有节点）
    mysql> flush privileges;
    mysql> show master status;
    #从节点(备master和)
    mysql> change master to master_host='172.10.10.137',master_port=3306,master_user='slave',master_password='123456',master_log_file='mysql-bin.000004',master_log_pos=231;
#2、在mysql master数据库中创建mha管理用户
mysql> grant all privileges on *.* to 'mha'@'%' identified by '123456';   #（主、备主节点）
mysql> flush privileges;

#2.1在两个salve节点上执行，只读限制（防止意外被写数据，很重要）
    mysql> set global read_only=1;     #针对于root用户read_only不受限制
#3、manager节点依赖：
    yum install perl-Config perl-Log-Dispatch perl-Parallel-ForkManager perl-Time-HiRes cpan
    yum install perl-DBD-MySQL perl-Module-Install cpan
    
    node节点依赖:
    yum install perl-DBD-MySQL perl-Module-Install cpan
#5、MHA组件通过rpm安装包安装
    yum localinstall mha4mysql-manager-0.56-0.el6.noarch.rpm -y    #仅在主节点安装
    yum localinstall mha4mysql-node-0.56-0.el6.noarch.rpm -y       #所有节点都需安装
    
    
#6、配置MHA
    配置mha-manager管理节点
    在主节点配置mha配置文件并创建一些基准目录。注意：配置文件#后面包括#号全部删除
    [root@A74 ~]# mkdir -p /etc/masterha/app1 
    [root@A74 ~]# mkdir -p /var/lib/mysql/
    [root@A74 ~]# touch /etc/masterha/app1/manager.log
    [root@A74 ~]# vim /etc/masterha/app1.cnf
    
    #app1.cnf
      

  [server default]
        manager_workdir=/etc/masterha/app1
        manager_log=/etc/masterha/app1/manager.log
        user=mha
        password=123456
        #前面创建all权限的管理账号
        
        ssh_user=root
        #ssh做的免密码登录，需要填写账号。用的那个账号设置的就需要写哪一个账号。
        
        repl_user=slave
        repl_password=123456
        #主从复制账号
        
        [server1]
        hostname=172.10.10.137
        port=3306
        master_binlog_dir=/var/lib/mysql
        candidate_master=1
        check_repl_delay=0
        
        [server2]
        hostname=172.10.10.139
        port=3306
        master_binlog_dir=/var/lib/mysql
        candidate_master=1
        check_repl_delay=0
        
        [server3]
        hostname=172.10.10.136
        port=3306
        master_binlog_dir=/var/lib/mysql
        #忽略失败
        ignore_fail=1 
        #192.168.19.77不能成为master
        no_master=1 

#7、测试ssh
    （下面最后一行为成功）这里如果出现问题，一定需要反复检查4台主机之间是否都具备免密码登录的条件。
    [root@A74 ~]# masterha_check_ssh --conf=/etc/masterha/app1.cnf    
#8、测试repl
    [root@A74 ~]# masterha_check_repl --conf=/etc/masterha/app1.cnf
#9、启动MHA并查看进程
    重新开一个窗口，用于监控日志，因为这个是打开前台的进程
    [root@A74 ~]# nohup masterha_manager --conf=/etc/masterha/app1.cnf > /tmp/mha_manager.log 2>&1 &
    [root@A74 ~]# tail -f /etc/masterha/app1/manager.log
    #状态检查
    masterha_check_status --conf=/etc/masterha/app1.cnf 

    
#10、定时清理relay log
    [root@vm03 ~]# cat /etc/cron.d/purge_relay_logs
0 7 * * * /usr/bin/purge_relay_logs --user=root --password=123456 --disable_relay_log_purge  --port=3306 --workdir=/data/mysql/data/ >>/etc/mha/app1/purge_relay_logs.log 2>&1    
    
##
#宕机后再次启动MHA时需要先到/etc/masterha/app1删除app1.failover.complete文件，否正无法启动

##
mysql> change master to master_host='172.10.10.137',master_port=3306,master_user='slave',master_password='123456',master_log_file='mysql-bin.000005',master_log_pos=231; 
ERROR 1776 (HY000): Parameters MASTER_LOG_FILE, MASTER_LOG_POS, RELAY_LOG_FILE and RELAY_LOG_POS cannot be set when MASTER_AUTO_POSITION is active.
mysql> change master to master_auto_position=0;
#解决办法：
change master to master_auto_position=0;

###限制MySQL的只读模式，对于root用户是不起作用的

 

#my.cnf

#其中master和备master,slave三者配置文件之间的区别在于server-id不相同，其他都一样

# The following options will be passed to all MySQL clients
[client]
#password       = your_password
port            = 3306
socket          = /tmp/mysql.sock

# The MySQL server basic setting
[mysqld]
port            = 3306
socket          = /tmp/mysql.sock
basedir         = /zywa/mysql
datadir         = /zywa/mysql/data
pid-file        = mysqld.pid
log-error       = mysql-err.log
server-id       = 1

gtid_mode = on
enforce_gtid_consistency = 1
log_slave_updates = 1
relay_log_purge=0
plugin_load = "rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"
loose_rpl_semi_sync_master_enabled = 1
loose_rpl_semi_sync_slave_enabled = 1
loose_rpl_semi_sync_master_timeout = 5000

skip-name-resolve
skip-external-locking
explicit_defaults_for_timestamp = true

# Character encoding
character-set-server = utf8
collation-server = utf8_general_ci

# Connections settings
max_connections = 500
max_connect_errors = 1000
max_allowed_packet = 32M
back_log = 200

# MyISAM settings
key_buffer_size = 256M
sort_buffer_size = 2M
read_buffer_size = 2M
read_rnd_buffer_size = 8M
myisam_sort_buffer_size = 64M
join_buffer_size = 2M
query_cache_size = 32M
thread_cache_size = 16
table_open_cache = 2048

# InnoDB settings
innodb_buffer_pool_size = 512M
innodb_data_file_path = ibdata1:256M:autoextend
innodb_read_io_threads = 4
innodb_write_io_threads = 4
innodb_flush_log_at_trx_commit = 2
innodb_log_buffer_size = 16M
innodb_log_file_size = 128M
innodb_log_files_in_group = 3
innodb_max_dirty_pages_pct = 90
innodb_lock_wait_timeout = 120
innodb_file_per_table = 1

# Replication Master Server (default)
# binary logging is required for replication
log-bin = mysql-bin
binlog_format = row
expire_logs_days = 7

#sync_binlog = 1
#log-slave-updates=true

#replicate-ignore-db = mysql
#replicate-ignore-db = information_schema
#replicate-ignore-db = performance_schema
#replicate-wild-ignore-table = mysql.%
#replicate-wild-ignore-table = information_schema.%
#replicate-wild-ignore-table = performance_schema.%

# Slow log settings
slow_query_log = ON
slow_query_log_file = mysql-slow.log
long_query_time = 2

[mysqldump]
quick
max_allowed_packet = 64M

[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates

[myisamchk]
key_buffer_size = 256M
sort_buffer_size = 256M
read_buffer = 2M
write_buffer = 2M