mysql-High-availability Mha+atlas read/write separation

The company recently adjusted for the new MySQL architecture, asked for a solution, I propose to use Mha+atlas to do high-availability cluster read and write separation framework, the multi-party discussion of the final confirmation program, implementation;

First, simply say MHA's working principle

650) this.width=650; "src=" Https://s3.51cto.com/wyfs02/M00/97/46/wKiom1ksLsHgpSvcAAD4jo0FYVc438.png "title=" Clipboard_ copy. png "alt=" Wkiom1kslshgpsvcaad4jo0fyvc438.png "/>

1 Management nodes can manage multiple sets of MySQL architecture, can not be installed on the MySQL host

Through the management node, the other data nodes on the MySQL monitoring, will do every few seconds heartbeat detection

Second, the simple structure diagram of MHA

650) this.width=650; "src=" Https://s4.51cto.com/wyfs02/M01/97/46/wKiom1ksL3PSdpvJAAD5YDV_1to643.png "title=" 2_ copy. png "alt=" Wkiom1ksl3psdpvjaad5ydv_1to643.png "/>

Iii. Deployment of MHA

1, environment preparation (dependent packages, packages, creation of storage directories, etc.) all nodes

#安装依赖包 [[email protected] ~] #yum  install perl-dbd-mysql -y# into the installation package storage directory [[email  Protected] ~] #cd  /home/oldboy/tools/#上传mha安装包 [[email protected]]# rz - bemha4mysql-manager-0.56-0.el6.noarch.rpmmha4mysql-manager-0.56.tar.gzmha4mysql-node-0.56-0.el6.noarch.rpmmha4mysql-node- 0.56.tar.gz# Install node package [[email protected] tools]# rpm - ivhmha4mysql-node-0.56-0.el6.noarch.rpmpreparing...                ########################################### [100%]  1: mha4mysql-node         ########################################## #[100%] #登录数据库 [[email protected]]# mysql -uroot -poldboy123# Add MHA Manage account Mysql> grant  allprivileges on *.* to [email protected] ' 10.0.0.% '  identified by   ' MHA '; #查看是否添加成功mysql > selectuser,host from mysql.user; #主库上创建, automatically copied from the library (viewed from the gallery) 

2, command soft connect all nodes to do

[Email protected] ~]# ln-s/application/mysql/bin/mysqlbinlog/usr/bin/mysqlbinlog[[email protected] ~]# ln-s/ application/mysql/bin/mysql/usr/bin/mysql# error if you do not create a command soft connection to detect MHA replication

3, Deployment Management node (Mha-manager)

1) Deploy the management node on mysql-db03

#使用epel源 [[email protected] ~] #wget-o/etc/yum.repos.d/epel.repo http://mirrors.aliyun.com/repo/epel-6.repo# Install manager Dependency package [[email protected] ~]# Yum install-y perl-config-tinyepel-release Perl-log-dispatch Perl-parallel-forkmanager perl-time-hires# Install manager package [[email protected] tools]# RPM- ivhmha4mysql-manager-0.56-0.el6.noarch.rpm Preparing ... ###########################################[100%]1:mh A4mysql-manager ########################################### [100%]

2) Edit the configuration file

#创建配置文件目录 [[email protected] ~]# mkdir-p/etc/mha# Create log directory [[email protected] ~]# mkdir-p/var/log/mha/app1# Edit MHA configuration file [[ Email protected] ~]# Vim/etc/mha/app1.cnf[server default]candidate_master=1check_repl_delay=0manager_log=/var/log/ Mha/app1/managermanager_workdir=/var/log/mha/app1master_binlog_dir=/application/mysql/datamaster_ip_failover_ Script=/usr/local/bin/master_ip_failoverpassword=mhaping_interval=2repl_password=oldboy123repl_user=repssh_ User=rootuser=mha[server1]hostname=10.0.0.51port=3306[server2]hostname=10.0.0.52port=3306[server3]hostname= 10.0.0.53port=3306

3) Establish Ssh-key secret key login to all nodes

#创建秘钥对 [[email protected] ~]# ssh-keygen-t dsa-p '-F ~/.ssh/id_dsa>/dev/null 2>&1# send the public key, including yourself [[email protected ] ~]# ssh-copy-id-i/root/.ssh/id_dsa.pub [email protected][[email protected] ~]# ssh-copy-id-i/root/.ssh/id_dsa.pub [E Mail Protected][[email protected] ~]# ssh-copy-id-i/root/.ssh/id_dsa.pub [email protected]

4) Start test db03

#测试ssh [[email protected] ~]# Masterha_check_ssh--conf=/etc/mha/app1.cnf# See the following words, the test succeeds Tue Mar 7 01:03:33] All SSH connection tests passed successfully. #测试复制 [[email protected] ~]# MASTERHA_CHECK_REPL--conf=/etc/mha/app1.cnf# See the following words, then test the success of MySQL Replication Health is OK.

5) Start MHA db03

[Email protected] ~]# nohup masterha_manager--conf=/etc/mha/app1.cnf--remove_dead_master_conf--ignore_last_ Failover </dev/null >/var/log/mha/app1/manager.log 2>&1 &

6) Switch Master test

Log in to the database db02

[[email protected] ~]# mysql -uroot -poldboy123# Check the replication status Mysql> show slave  status\G*************************** 1. row ***************************               Slave_IO_State: Waiting for  master to send event                   Master_Host: 10.0.0.51                  Master_User: rep                  Master_Port: 3306                connect_retry: 60              Master_Log_File:  Mysql-bin.000006         read_master_log_pos: 191               Relay_Log_File: mysql-db02-relay-bin.000002                Relay_Log_Pos: 361        Relay_Master_Log_File: mysql-bin.000006             Slave_IO_Running: Yes            slave_sql_running: yes

# login database (DB03)

[[email protected] ~]# mysql -uroot -poldboy123# Check the replication status Mysql> show slave  status\G*************************** 1. row ***************************               Slave_IO_State: Waiting for  master to send event                   Master_Host: 10.0.0.51                  Master_User: rep                   master_port: 3306                Connect_Retry:  60             master_log_file:  Mysql-bin.000006         read_master_log_pos: 191                Relay_Log_File: mysql-db03-relay-bin.000002                Relay_Log_Pos: 361         Relay_Master_Log_File:mysql-bin.000006             Slave_IO_Running: Yes            slave_sql_running: yes

# Shut down the main library

[[email protected] ~]# /etc/init.d/mysqld stopshutting down mysql.....  success! #登录数据库 (DB02) [[email protected] ~]# mysql -uroot -poldboy123# View slave status MySQL > SHOW SLAVE STATUS\G#DB02 's slave is already empty empty set  (0.00 sec) #登录数据库 (db03) [email  protected] ~]# mysql -uroot -poldboy123# View slave status Mysql> show slave  status\G*************************** 1. row ***************************                Slave_IO_State:Waiting for  master to send event                   Master_Host: 10.0.0.52                  Master_User: rep                  master_port: 3306                Connect_Retry: 60              Master_Log_File: mysql-bin.000006           Read_Master_Log_Pos:191               Relay_Log_File: mysql-db03-relay-bin.000002                Relay_Log_Pos: 361        Relay_Master_Log_File: mysql-bin.000006             Slave_IO_Running: Yes            Slave_sql_running: yes

This article is from the "funny Brother Notes" blog, be sure to keep this source http://qiuyt.blog.51cto.com/1229789/1930629

mysql-High-availability Mha+atlas read/write separation