Super deployment tutorials under Mysql high-availability failover Scenarios MHA _mysql

MHA Introduction
MHA is a MySQL, a Japanese MySQL Daniel written in Perl failover scheme to ensure the high availability of the database system. During downtime (usually within 10-30 seconds), complete failover, deploy MHA, avoid master-slave consistency issues, and save on the cost of purchasing new servers, Does not affect server performance, easy to install, and does not change existing deployments.

also supports online switching, from the current run master to a new master above, only a short time (0.5-2 seconds), at this time only blocking the write operation, does not affect the read operation, easy to host hardware maintenance.

On systems with high availability and data consistency requirements, MHA provides useful functionality to meet maintenance needs almost without interruption.

Advantages:

1.master Automatic monitoring and failover

In the current master-slave replication environment, MHA can monitor master host failures and failover automatically.

Even if some slave do not accept the new relay log EVENTS,MHA will automatically identify the difference relay log events from the latest slave, and apply the difference event to other slaves. So all the slave are the same. MHA Second Level failover (9-12 SEC monitoring to host failure, optional 7 seconds to shut down the power host to avoid brain crack, then apply the difference relay logs, registered to the new master, usually takes time 10-30 seconds that total downtime). In addition, a slave can be configured in a configuration file to first become master. Because MHA fixes the consistency between slave, DBAs don't have to deal with consistency issues.

When the new master is migrated, other slave are restored in parallel. Even if there are thousands of slave, it will not affect the recovery master time, Slave also quickly completed.

Dena company 150+ in the master-slave environment with MHA. When one of the master crashes, MHA4 seconds completes the failover, which is not possible with the active/passive cluster solution.

2. Interactive (manual) master failover

MHA can be used to do only failover, not to monitor MASTER,MHA interactions only as failover.

3. Non-interactive failover

Non-interactive failover is also provided (without monitoring master, automatic failover). This feature is useful, especially if you have installed other software monitoring master. For example, use Pacemaker (Heartbeat) to monitor master failures and VIP takeovers with MHA failover and slave elevation.

4. Switch master to different hosts online

In many cases, it is necessary to transfer master to another host (such as replacing a RAID controller, upgrading master machine hardware, and so on). This is not a master crash, but plan maintenance must be done. Planned maintenance results in downtime and must be recovered as soon as possible. Fast Master switching and graceful blocking writes are required, MHA provides this approach. Graceful master switch, blocking write operations in 0.5-2 seconds. In many cases, the downtime of 0.5-2 seconds is acceptable, and even if the window is not scheduled to be maintained. This means that when a faster machine needs to be replaced, the DBA can easily take action when upgrading the higher version.

5.master crash does not cause master-slave data inconsistency

When master crash, MHA automatically recognizes the difference between slave relay logevents, and then applies it to different slave, and eventually all slave are synchronized. Combined with half synchronization, almost no data is lost.

Other high-availability scenarios

6.MHA deployment does not affect current environment settings

One of the most important design concepts of MHA is to use it as simple as possible. Using the master-slave environment above 5.0+, the other HA scenarios need to change the MySQL deployment settings, MHA will not allow DBAs to do these deployment configurations, both synchronous and semi synchronous. Start/stop/upgrade/downgrade/install/uninstall MHA do not need to change MySQL master (such as Start/stop).

When you need to upgrade MHA to a new version, you don't need to stop MySQL, just update the HMA version, and then restart Mhamanger.

MHA support includes 5.0/5/1/5.5 (5.6 should also be supported, when translating documents MHA developers did not update for version 5.6). Some HA schemes require specific versions of MySQL (such as Mysqlcluster,mysql with global transaction IDs, and so on), and you may not want to migrate applications just for MASTERHA. In many cases, the company has deployed many traditional MySQL applications, development or DBAs do not want to spend too much time migrating to different storage engines or new features (newer bleeding edge distributions don't know if this translates).

7. Do not increase server costs

MHA contains MHA Manager and MHA node. MHA node runs on each MySQL server, the manager can deploy a single machine, monitor 100+ above master, the total number of servers will not be significantly increased. Note that the manager can also run on a machine in slaves.

8. No effect on performance

When monitoring Master,mha only a few seconds (default 3 seconds) Send ping package, do not send large queries. Master-slave replication performance is unaffected

9. Applicable to any storage engine

MySQL applies not only to the InnoDB engine of transaction security, but to the engine in which the master applies, MHA. Even with the Mysiam engine of the legacy environment, you can use MHA without migrating.

Installation Configuration method
1. Introduction to Sample Environment

• CentOS 6.4 x64
• Mysql-5.6.16-linux-glibc2.5-x86_64.tar.gz
• Mha4mysql-manager-0.54.tar.gz
• Mha4mysql-node-0.54.tar.gz

2, Server description

• Masnager 192.168.216.50
• Master 192.168.216.51
• Slave 192.168.216.52

MySQL is installed on the three servers and the manager as the management node

3, the configuration of equivalence

Manager

          ssh-keyger-t RSA

          ssh-coyp-id-i/root/.ssh/id_rsa.pub root@192.168.216.50

          ssh-coyp-id-i/root/.ssh/id_ Rsa.pub root@192.168.216.51

          ssh-coyp-id-i/root/.ssh/id_rsa.pub root@192.168.216.52

          ssh 192.168.216.50 date

          ssh 192.168.216.51 date

          ssh 192.168.216.52 date

Master

          ssh-keyger-t RSA

          ssh-coyp-id-i/root/.ssh/id_rsa.pub root@192.168.216.50

          ssh-coyp-id-i/root/.ssh/id_ Rsa.pub root@192.168.216.51

          ssh-coyp-id-i/root/.ssh/id_rsa.pub root@192.168.216.52

          ssh 192.168.216.50 Date

          ssh 192.168.216.51 date

          ssh 192.168.216.52 date

Slave

          ssh-keyger-t RSA

          ssh-coyp-id-i/root/.ssh/id_rsa.pub root@192.168.216.50

          ssh-coyp-id-i/root/.ssh/id_ Rsa.pub root@192.168.216.51

          ssh-coyp-id-i/root/.ssh/id_rsa.pub root@192.168.216.52

          ssh 192.168.216.50 date

          ssh 192.168.216.51 date

          ssh 192.168.216.52 date

4, install MySQL

    Groupadd MySQL

     useradd-g mysql-s/bin/nologin-m mysql

     mkdir-pv/data/mysql tar-zxvf mysql-5.6.16-linu

     x-glibc2.5-x86_64.tar.gz
     mv Mysql-5.6.16-linux-glibc2.5-x86_64/usr/local/mysql

     cd/usr/local/mysql/ Script

     ./mysql_install_db–user=mysql–basedir=/usr/local/mysql–datadair=/data/mysql

Creating a MySQL configuration file

     Vim/usr/local/mysql/my.cnf

    [MySQL] # CLIENT # port = 3306 socket =/tmp/mysql.sock [mysqld] # general # user = MySQL Default-storage-engine = InnoDB socket =/tm

     P/mysql.sock pid-file =/data/mysql/mysql.pid # MyISAM # key-buffer-size = 32M       Myisam-recover = force,backup # SAFETY # max-allowed-packet = 16M max-connect-errors = 1000000 # DATA STORAGE # datadir =/data/mysql/# BINARY LOGGING # Ser        ver_id = 1 #不同服务器不一样 log-bin =/data/mysql/mysql-bin expire-logs-days = Sync-binlog = 1 # caches and LIMITS # tmp-table-size = 32M max-heap -table-size = 32M Query-cache-type = 0 query-cache-size = 0 max-connections = Thread-cache-size = Open-files-limit = 65535 Table-definition-cache = 1024 Table-open-cache = 2048 # INNODB # Innodb-flush-method = O_direct Innodb-log-files-in-group = 2 innodb-l Og-file-size = 64M Innodb-flush-log-at-trx-commit = 1 innodb-file-per-table = 1 innodb-buffer-p Ool-size = 592M # LOGGING # log-error =/data/mysql/mysql-error.log Log-queries-not-usin

 g-indexes = 1 Slow-query-log = 1 Slow-query-log-file =/data/mysql/mysql-slow.log

5. Configure permissions

Manager

          Grant all privileges on *.* to root@ ' 127.0.0.1 ' identified by ' root ';

          Grant all privileges on *.* to root@ ' localhost ' identified by ' root ';

          Grant all privileges on *.* to root@ ' 192.168.216.50 ' identified by ' root ';

          Grant replication Slave on *.* to slave@ ' 192.168.216.50 ' identified by ' slave ';

          Grant replication Slave on *.* to slave@ ' 192.168.216.51 ' identified by ' slave ';

          Grant replication Slave on *.* to slave@ ' 192.168.216.52 ' identified by ' slave ';

Master

          Grant all privileges on *.* to root@ ' 127.0.0.1 ' identified by ' root ';

          Grant all privileges on *.* to root@ ' localhost ' identified by ' root ';

          Grant all privileges on *.* to root@ ' 192.168.216.50 ' identified by ' root ';

          Grant replication Slave on *.* to slave@ ' 192.168.216.50 ' identified by ' slave ';

          Grant replication Slave on *.* to slave@ ' 192.168.216.51 ' identified by ' slave ';

          Grant replication Slave on *.* to slave@ ' 192.168.216.52 ' identified by ' slave ';

Slave

          Grant all privileges on *.* to root@ ' 127.0.0.1 ' identified by ' root ';

          Grant all privileges on *.* to root@ ' localhost ' identified by ' root ';

          Grant all privileges on *.* to root@ ' 192.168.216.50 ' identified by ' root ';

          Grant all privileges on *.* to root@ ' 192.168.216.51 ' identified by ' root ';

          Grant replication Slave on *.* to slave@ ' 192.168.216.50 ' identified by ' slave ';

          Grant replication Slave on *.* to slave@ ' 192.168.216.51 ' identified by ' slave ';

          Grant replication Slave on *.* to slave@ ' 192.168.216.52 ' identified by ' slave ';

6, install the dependent package

Management Server Manager needs to install all of the following

Perl-config-tiny

perl-params-validate

perl-parallel-forkmanager

perl-log-dispatch

| perl-mime-lite-3.027-2.el6.noarch.rpm

     | | perl-mime-types-1.28-2.el6.noarch.rpm

     | perl-email-date-format-1.002-5.el6.noarch.rpm

     | | perl-mailtools-2.04-4.el6.noarch.rpm

          | perl-timedate-1.16-11.1.el6.noarch.rpm

          | | perl-data-showtable-3.3-3.4.noarch.rpm

| perl-mail-sender-0.8.22-21.1.noarch.rpm

     | | perl-io-socket-ssl-1.31-2.el6.noarch.rpm

          | perl-net-libidn-0.12-3.el6.x86_64.rpm

          | | perl-net-ssleay-1.35-9.el6.x86_64.rpm

     | Perl-win32api-registry

|-perl-mail-sendmail-0.79_16-4.2.noarch.rpm

     RPM-IVH ncftp-debuginfo-3.2.3-1.3.x86_64.rpm

     RPM-IVH perl-parallel-forkmanager-0.7.5-2.2.el6.rf.noarch.rpm

     rpm-ivh perl-params-validate-0.91-2.4.x86_64.rpm

     rpm-ivh perl-config-tiny-2.12-7.1.el6.noarch.rpm

     RPM-IVH perl-mime-types-1.28-2.el6.noarch.rpm

     rpm-ivh perl-email-date-format-1.002-5.el6.noarch.rpm

     rpm- IVH perl-timedate-1.16-11.1.el6.noarch.rpm

     rpm-ivh perl-data-showtable-3.3-3.4.noarch.rpm

     RPM-IVH perl-mailtools-2.04-4.el6.noarch.rpm

     RPM-IVH perl-mime-lite-3.027-2.el6.noarch.rpm

     RPM-IVH perl-net-libidn-0.12-3.el6.x86_64.rpm

     RPM-IVH perl-net-ssleay-1.35-9.el6.x86_64.rpm

     RPM-IVH perl-io-socket-ssl-1.31-2.el6.noarch.rpm

     RPM-IVH perl-mail-sendmail-0.79_16-4.2.noarch.rpm

     RPM-IVH perl-mail-sender-0.8.22-21.1.noarch.rpm

     RPM-IVH perl-log-dispatch-2.22-7.3.noarch.rpm

If Ann perl-dbd-mysql-4.013-3.el6.x86_64.rpm is checking the rep is an error, you need to manually compile the installation

     TAR-ZXVF dbd-mysql-4.027.tar.gz

     cd dbd-mysql-4.0.27

     perl makefile.pl make

     && make install

Node Server Installation

     RPM-IVH ncftp-debuginfo-3.2.3-1.3.x86_64.rpm

     RPM-IVH perl-dbd-mysql-4.013-3.el6.x86_64.rpm

7. Create Soft Connection

     Ln-s/usr/local/mysql/bin/mysqlbinlog/usr/bin/mysqlbinlog

     ln-s/usr/local/mysql/bin/mysql/usr/bin/mysql

Export MySQL Library search path

     vim/etc/ld.so.conf.d/mysql-x86_64.conf

       /usr/local/mysql/lib

     ldconfig

8. Install mha4mysql-node-0.54.tar.gz on all servers

     TAR-ZXVF mha4mysql-node-0.54.tar.gz

     cd mha4mysql-node-0.54

     perl makefile.pl make

     && make install

9, install the mha4mysql-manager-0.54.tar.gz on the Management Service

     TAR-ZXVF mha4mysql-manager-0.54.tar.gz

     cd mha4mysql-manager-0.54

     perl makefile.pl make

     && Make install

     mkdir-pv/etc/masterha

     MKDIR-PV/MASTERHA/APP1

     CP samples/conf/*/ETC/MASTERHA

     CP samples/scripts/*/usr/local/bin

     Vim/etc/masterha/app1.cnf

     [Server default]

     Manager_workdir=/masterha/app1

     manager_log=/masterha/app1/manager.log

 

     user=root

     password=root

 

     Ssh_user=root

     repl_user=slave

     repl_password=slave

     shutdown_script= ""

     #master_ip_failover_ script= "/usr/local/bin/masterha_ip_failover"

     master_ip_online_change_script= "/usr/local/bin/masterha_ip_ Failover "

     report_script=" "

 

     [Server1]

     hostname=192.168.216.50

     master_binlog_dir="/data/ mysql/"

     candidate_master=1

 

     [server2]

     hostname=192.168.216.51

     master_binlog_dir="/data/ mysql/"

     candidate_master=1

 

     [Server3]

     hostname=192.168.216.52

     master_binlog_dir="/data/ mysql/"

     candidate_master=1

10. Test SSH Connection

     Masterha_check_ssh–conf=/etc/masterha/app1.cnf

11. Test Replication

    Masterha_check_repl–conf=/etc/masterha/app1.cnf

12, open the management node process

   Masterha_manager–conf=/etc/masterha/app1.cnf

13. Test failover

Close the main library MySQL to see if the status of the library is switched to the new main library

14, set the failover IP

     Vim/etc/masterha/app1.cnf

          master_ip_failover_script= "/usr/local/bin/masterha_ip_failover"

          master_ip_online_change_script= "/usr/ Local/bin/masterha_ip_failover "

Edit the failover script to set the VIP to 192.168.216.100

 Vim/usr/local/bin/masterha_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 _maste

 

R_IP, $new _master_port); # my $VIP = ' 172.16.21.119/24 '; # Virtual IP My $vip = ' 192.168.216.100/24 ';

# Virtual IP My $key = "1";

My $ssh _start_vip = "/sbin/ifconfig eth0: $key $vip";

 

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_mas Ter_host=s ' => \ $new _master_host, ' new_master_ip=s ' => \ $new _master_ip, ' new_master_port=i ' => \ $new _maste

 

R_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 ") {# $orig _master_host, $orig _master_ip, $orig _master_port are passed.

    # If You are manage master IP address in global Catalog database, # invalidate orig_master_ip here.

    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 ') {# All arguments are passed.

    # If You are manage master IP address in global Catalog database, # Activate NEW_MASTER_IP here.

    # You can also grant write access (create user, set read_only=0, etc.) here.

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

    ' ssh $ssh _user\ @cluster1 \ ' $ssh _start_vip \ ';

  Exit 0;

    else {&usage ();

  Exit 1; } # A Simple system Call this enable the VIP on the new master sub Start_vip () {' ssh $ssh _user\@ $new _master_ho

St \ "$ssh _start_vip \";  # A Simple system called called that disable the VIP on the Old_master sub Stop_vip () {' 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_m

Aster_ip=ip–orig_master_port=port–new_master_host=host–new_master_ip=ip–new_master_port=port\n ";

 }

Test scripts

/usr/local/bin/masterha_ip_failover–command=status–ssh_user=root–orig_master_host=192.168.216.51–orig_master_ ip=192.168.216.51–orig_master_port=3306

Virtual IP to start manually, after the main library failure will automatically transfer.

/usr/local/bin/masterha_ip_failover–command=start–ssh_user=root–orig_master_host=192.168.216.51–orig_master_ip =192.168.216.51–orig_master_port=3306–new_master_host=192.168.216.51

To test IP failover:

Close the main library MySQL to see if the VIP is transferred to the new Main library.