1.拓扑图：
2.环境说明：
mha-master:192.168.80.204 MHA管理节点
db1: 192.168.80.201 master
db2: 192.168.80.202 备选master
db3: 192.168.80.203 backup
vip:192.168.80.200
3.配置ssh-key密钥需要每个机器都互信
a.所有主机先添加hosts文件
192.168.80.201 db1 db1.iyunw.cn 192.168.80.202 db2 db2.iyunw.cn 192.168.80.203 db3 db3.iyunw.cn 192.168.80.204 mha-master mha-master.iyunw.cn
b.每个主机分别设置ssh-keygen 并且和其他主机能够面密钥连接，这里以db1做演示
ssh-keygen -t rsa ssh-copy-id -i ~/.ssh/id_rsa.pub root@db1 ssh-copy-id -i ~/.ssh/id_rsa.pub root@db2 ssh-copy-id -i ~/.ssh/id_rsa.pub root@db3 ssh-copy-id -i ~/.ssh/id_rsa.pub root@mha-master
4.db1,db2,db3都装上mysql并做主从
a.安装mysql，安装包都在/usr/local/src下面
cd /usr/local/src && tar -xf mysql-5.6.35-linux-glibc2.5-x86_64.tar.gz mv /usr/local/src/mysql-5.6.35-linux-glibc2.5-x86_64 /usr/local/mysql useradd -s /sbin/nologin mysql chown -R mysql.mysql /usr/local/mysql 拷贝my.cnf到/etc/my.cnf,my.cnf配置文件见下面 cd /usr/local/mysql && ./scripts/mysql_install_db --defaults-file=/etc/my.cnf返回两个OK代表正常
echo 'export PATH=$PATH:/usr/local/mysql/bin'>>/etc/profile&&. /etc/profile cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld chmod +x /etc/init.d/mysqld
b.配置主从
db1:
(product)root@db1 [mysql]>update mysql.user set password=password('root') where user='root';
删除无用账户
(product)root@db1 [mysql]> delete from user where user=' ';
(product)root@db1 [mysql]> delete from user where host='::1';
创建主从复制账号
GRANT REPLICATION SLAVE ON *.* TO 'replication'@'192.168.80.%' IDENTIFIED BY '123456';flush privileges;
做全量备份，从库恢复（如果有数据），全新装不需要，如果是有数据建议先锁表。
查看并记录master pos 位置：show master status\G
db2:
改/etc/my.cnf server_id = 2,启动数据库
开始同步：
root@localhost [mysql]> CHANGE MASTER TO MASTER_HOST='db1', MASTER_PORT=3306, MASTER_LOG_FILE='mysql-bin.000005', MASTER_LOG_POS= 2563, MASTER_USER='replication', MASTER_PASSWORD='123456'; root@db2 [mysql]> start slave; root@db2 [mysql]>show slave status \G看到两个OK代表正常
关事件功能，如果从库不关事件的话，主库一但有事件执行存储过程那将会造成同步失败，注意！
(product)root@db2 [(none)]> set global event_scheduler=0; (product)root@db2 [(none)]> show variables like 'event_scheduler'; +-----------------+-------+ | Variable_name | Value | +-----------------+-------+ | event_scheduler | OFF | +-----------------+-------+
db3:
除了改/etc/my.cnf server_id = 3，其他和db2一样
测试主从，主库创建数据
5.配置mha
管理节点安装mha4mysql-manager-0.56-0.el6.noarch.rpm mha4mysql-node-0.56-0.el6.noarch.rpm
其他DB节点安装：ha4mysql-node-0.56-0.el6.noarch.rpm
db1-3:
db1作为master需要绑定vip:ip addr add 192.168.80.200/32 dev eth0
创建一个有权限的root或者其他用户，用于mha-master主机管理 GRANT all ON *.* TO 'root'@'%' IDENTIFIED BY 'root';flush privileges; 为了避免报错找不到mysql命令，需要添加软连接 ln -s /usr/local/mysql/bin/mysqlbinlog /usr/bin/mysqlbinlog ln -s /usr/local/mysql/bin/mysql /usr/bin yum -y install gcc gcc-c++ kernel-devel yum -y install perl-Config-Tiny.noarch perl-Log-Dispatch.noarch perl-Parallel-ForkManager.noarch perl-Time-HiRes perl-DBD-MySQL rpm -ivh mha4mysql-node-0.56-0.el6.noarch.rpm
mha-master:
yum -y install gcc gcc-c++ kernel-devel yum -y install perl-Config-Tiny.noarch perl-Log-Dispatch.noarch perl-Parallel-ForkManager.noarch perl-Time-HiRes perl-DBD-MySQL rpm -ivh mha4mysql-node-0.56-0.el6.noarch.rpm rpm –ivh mha4mysql-manager-0.56-0.el6.noarch.rpm 创建配置文件目录，默认没有 mkdir /etc/masterha 上传masterha三个文件到管理节点上的/etc/masterha app1.conf masterha_default.conf master_ip_failover
测试：
测试ssh
masterha_check_ssh --global_conf=/etc/masterha/masterha_default.conf --conf=/etc/masterha/app1.conf
查看主从结构是不是OK
masterha_check_repl --global_conf=/etc/masterha/masterha_default.conf --conf=/etc/masterha/app1.conf
管理进程启动脚本
#!/bin/bash nohup masterha_manager --global_conf=/etc/masterha/masterha_default.conf --conf=/etc/masterha/app1.conf >> /var/log/masterha/app1/manager.log 2>&1 &启动脚本
[root@mha-master sh]# sh mha_start.sh
查看MHA监控进程
ps -ef|grep master root 5894 1 0 Jan20 ? 00:00:12 /usr/libexec/postfix/master root 79627 1 4 09:05 pts/1 00:00:00 perl /usr/bin/masterha_manager --global_conf=/etc/masterha/masterha_default.conf --conf=/etc/masterha/app1.conf
可以 看到进程已经正常启动
接下来是查看集群状态
masterha_check_status --global_conf=/etc/masterha/masterha_default.conf --conf=/etc/masterha/app1.conf app1 (pid:79627) is running(0:PING_OK), master:db1
配置文件：
/etc/my.cnf
[client] port = 3306 socket = /tmp/mysql.sock # The MySQL server [mysqld] # Basic port = 3306 user = mysql basedir = /usr/local/mysql datadir = /usr/local/mysql/data tmpdir = /usr/local/mysql/ socket = /tmp/mysql.sock log-bin = /usr/local/mysql/data/mysql-bin log-error = error.log slow-query-log-file = slow.log skip-external-locking skip-name-resolve log-slave-updates ############################### # FOR Percona 5.6 #extra_port = 3345 gtid-mode = 0 #thread_handling=pool-of-threads #thread_pool_oversubscribe=8 explicit_defaults_for_timestamp ############################### server-id =1753306 character-set-server = utf8 slow-query-log binlog_format = row max_binlog_size = 128M binlog_cache_size = 1M expire-logs-days = 5 back_log = 500 long_query_time=1 max_connections=1100 max_user_connections=1000 max_connect_errors=1000 wait_timeout=100 interactive_timeout=100 connect_timeout = 20 slave-net-timeout=30 max-relay-log-size = 256M relay-log = relay-bin transaction_isolation = READ-COMMITTED performance_schema=0 #myisam_recover key_buffer_size = 64M max_allowed_packet = 16M #table_cache = 3096 table_open_cache = 6144 table_definition_cache = 4096 sort_buffer_size = 128K read_buffer_size = 1M read_rnd_buffer_size = 1M join_buffer_size = 128K myisam_sort_buffer_size = 32M tmp_table_size = 32M max_heap_table_size = 64M query_cache_type=0 query_cache_size = 0 bulk_insert_buffer_size = 32M thread_cache_size = 64 #thread_concurrency = 32 thread_stack = 192K skip-slave-start # InnoDB innodb_data_home_dir = /usr/local/mysql/data innodb_log_group_home_dir = /usr/local/mysql/data innodb_data_file_path = ibdata1:10M:autoextend innodb_buffer_pool_size = 500M innodb_buffer_pool_instances = 8 #innodb_additional_mem_pool_size = 16M innodb_log_file_size = 200M innodb_log_buffer_size = 16M innodb_log_files_in_group = 3 innodb_flush_log_at_trx_commit = 0 innodb_lock_wait_timeout = 10 innodb_sync_spin_loops = 40 innodb_max_dirty_pages_pct = 90 innodb_support_xa = 0 innodb_thread_concurrency = 0 innodb_thread_sleep_delay = 500 innodb_file_io_threads = 4 innodb_concurrency_tickets = 1000 log_bin_trust_function_creators = 1 innodb_flush_method = O_DIRECT innodb_file_per_table innodb_read_io_threads = 16 innodb_write_io_threads = 16 innodb_io_capacity = 2000 innodb_file_format = Barracuda innodb_purge_threads=1 innodb_purge_batch_size = 32 innodb_old_blocks_pct=75 innodb_change_buffering=all innodb_stats_on_metadata=OFF [mysqldump] quick max_allowed_packet = 128M #myisam_max_sort_file_size = 10G [mysql] no-auto-rehash max_allowed_packet = 128M prompt = '(product)\u@\h [\d]> ' default_character_set = utf8 [myisamchk] key_buffer_size = 64M sort_buffer_size = 512k read_buffer = 2M write_buffer = 2M [mysqlhotcopy] interactive-timeout
app1.conf
[server default] #mha manager工作目录 manager_workdir = /var/log/masterha/app1 manager_log = /var/log/masterha/app1/app1.log remote_workdir = /var/log/masterha/app1 [server1] hostname=db1 master_binlog_dir = /usr/local/mysql/data candidate_master = 1 check_repl_delay = 0 [server2] hostname=db2 master_binlog_dir = /usr/local/mysql/data candidate_master=1 check_repl_delay=0 [server3] hostname=db3 master_binlog_dir =/usr/local/mysql/data candidate_master=1 check_repl_delay=0
masterha_default.conf
[server default] #MySQL的用户和密码 user=root password=root #系统ssh用户 ssh_user=root #复制用户 repl_user= replication repl_password= 123456 #监控 ping_interval=1 #shutdown_script="" #切换调用的脚本 master_ip_failover_script= /etc/masterha/master_ip_failover master_ip_online_change_script= /etc/masterha/master_ip_online_change
master_ip_failover
#!/usr/bin/env perl
use strict;
use warnings FATAL => 'all';
use Getopt::Long;
my (
$command, $ssh_user, $orig_master_host, $orig_master_ip,
$orig_master_port, $new_master_host, $new_master_ip, $new_master_port
);
my $vip = '10.168.1.100/24'; 注意此处是设置虚拟IP
my $key = '1';
my $ssh_start_vip = "/sbin/ifconfig eth0:$key $vip"; 这里也要注意改成现在系统用的网卡eth0
my $ssh_stop_vip = "/sbin/ifconfig eth0:$key down";
GetOptions(
'command=s' => \$command,
'ssh_user=s' => \$ssh_user,
'orig_master_host=s' => \$orig_master_host,
'orig_master_ip=s' => \$orig_master_ip,
'orig_master_port=i' => \$orig_master_port,
'new_master_host=s' => \$new_master_host,
'new_master_ip=s' => \$new_master_ip,
'new_master_port=i' => \$new_master_port,
);
exit &main();
sub main {
print "\n\nIN SCRIPT TEST====$ssh_stop_vip==$ssh_start_vip===\n\n";
if ( $command eq "stop" || $command eq "stopssh" ) {
my $exit_code = 1;
eval {
print "Disabling the VIP on old master: $orig_master_host \n";
&stop_vip();
$exit_code = 0;
};
if ($@) {
warn "Got Error: $@\n";
exit $exit_code;
}
exit $exit_code;
}
elsif ( $command eq "start" ) {
my $exit_code = 10;
eval {
print "Enabling the VIP - $vip on the new master - $new_master_host \n";
&start_vip();
$exit_code = 0;
};
if ($@) {
warn $@;
exit $exit_code;
}
exit $exit_code;
}
elsif ( $command eq "status" ) {
print "Checking the Status of the script.. OK \n";
exit 0;
}
else {
&usage();
exit 1;
}
}
sub start_vip() {
`ssh $ssh_user\@$new_master_host \" $ssh_start_vip \"`;
}
sub stop_vip() {
return 0 unless ($ssh_user);
`ssh $ssh_user\@$orig_master_host \" $ssh_stop_vip \"`;
}
sub usage {
print
"Usage: master_ip_failover --command=start|stop|stopssh|status --orig_master_host=host --orig_master_ip=ip --orig_master_port=port --new_master_host=host --new_master_ip=ip --new_master_port=port\n";
}
评论区