mysql高可用MHA搭建

1.拓扑图:  

mysql高可用MHA搭建

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  mhamaster mhamaster.iyunw.cn

 b.每个主机分别设置ssh-keygen 并且和其他主机能够面密钥连接,这里以db1做演示 

1

2

3

4

5

sshkeygen t rsa

sshcopyid i ~/.ssh/id_rsa.pub root@db1

sshcopyid i ~/.ssh/id_rsa.pub root@db2

sshcopyid i ~/.ssh/id_rsa.pub root@db3

sshcopyid i ~/.ssh/id_rsa.pub root@mhamaster

4.db1,db2,db3都装上mysql并做主从

 a.安装mysql,安装包都在/usr/local/src下面

1

2

3

4

5

6

7

cd /usr/local/src && tar xf mysql5.6.35linuxglibc2.5x86_64.tar.gz

mv /usr/local/src/mysql5.6.35linuxglibc2.5x86_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 defaultsfile=/etc/my.cnf

返回两个OK代表正常

1

2

3

4

echo ‘export PATH=$PATH:/usr/local/mysql/bin’>>/etc/profile&&. /etc/profile

cp /usr/local/mysql/supportfiles/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或者其他用户,用于mhamaster主机管理

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 gccc++ kerneldevel

yum y install perlConfigTiny.noarch perlLogDispatch.noarch   perlParallelForkManager.noarch perlTimeHiRes perlDBDMySQL

rpm ivh mha4mysqlnode0.560.el6.noarch.rpm

         mha-master:

1

2

3

4

5

6

7

8

9

yum  y  install gcc gccc++ kerneldevel

yum y install perlConfigTiny.noarch perlLogDispatch.noarch   perlParallelForkManager.noarch perlTimeHiRes perlDBDMySQL

rpm ivh mha4mysqlnode0.560.el6.noarch.rpm

rpmivh mha4mysqlmanager0.560.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

logbin     = /usr/local/mysql/data/mysqlbin

logerror   = error.log

slowquerylogfile = slow.log

skipexternallocking

skipnameresolve

logslaveupdates

###############################

# FOR Percona 5.6

#extra_port = 3345

gtidmode = 0

#thread_handling=pool-of-threads

#thread_pool_oversubscribe=8

explicit_defaults_for_timestamp

###############################

serverid  =1753306

charactersetserver = utf8

slowquerylog

binlog_format = row

max_binlog_size = 128M

binlog_cache_size = 1M

expirelogsdays = 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

slavenettimeout=30

maxrelaylogsize = 256M

relaylog = relaybin

transaction_isolation = READCOMMITTED

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

skipslavestart

# 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]

noautorehash

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]

interactivetimeout

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”;

}

参考文档

https://www.cnblogs.com/gomysql/p/3675429.html

Add a Comment

邮箱地址不会被公开。 必填项已用*标注