- A+
所属分类：服务相关
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文件
|
1 2 3 4 5 |
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做演示
|
1 2 3 4 5 |
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下面
|
1 2 3 4 5 6 7 |
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代表正常
|
1 2 3 4 |
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:
|
1 2 3 4 5 6 7 8 9 |
(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,启动数据库
开始同步：
|
1 2 3 4 |
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代表正常
关事件功能，如果从库不关事件的话，主库一但有事件执行存储过程那将会造成同步失败，注意！
|
1 2 3 4 5 6 7 8 |
(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
|
1 2 3 4 5 6 7 8 9 |
创建一个有权限的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:
|
1 2 3 4 5 6 7 8 9 |
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
管理进程启动脚本
|
1 2 3 |
#!/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监控进程
|
1 2 3 |
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 |
可以 看到进程已经正常启动
接下来是查看集群状态
|
1 2 |
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
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 |
[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
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
[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
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
[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
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 |
#!/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"; } |
- 我的微信
- 这是我的微信扫一扫
-
- 我的微信公众号
- 我的微信公众号扫一扫
-
