[리눅스] MySQL 5.7 MHA(Master High Availability) 설정

MySQL 5.7 MHA(Master High Availability) 설정

테스트 환경

호스트명	아이피	DB 역할	MHA	비고
monitor	192.168.0.100	-	mha4mysql-manager, mha4mysql-node
mdb	192.168.0.101	master	mha4mysql-node
sdb	192.168.0.102	slave	mha4mysql-node

MHA(MHA Manager and MHA Node) 구성

그림 출처 : https://github.com/yoshinorim/mha4mysql-manager/wiki/Architecture

MHA(MHA Manager and MHA Node) 설치

MHA 패키지 다운로드 받기

- mha4mysql-manager-0.57

- mha4mysql-node-0.57

https://mega.nz/folder/G4oRjARB#SWzFS59bUv9VrKwdAeIGVw/folder/etZimagL

MHA Manager/Node 패키지 설치

MHA Node 패키지 설치(replication, manager)

- Perl 모듈 설치(Manager/Node)

$ yum -y install perl-CPAN perl-DBD-MySQL perl-Module-Install

- MHA 설치

$ tar xvzf mha4mysql-node-0.57.tar.gz

$ cd mha4mysql-node-0.57

$ perl Makefile.PL

$ make

$ make install

MHA Manager 패키지 설치

- Perl 모듈 설치(Manager/Node)

$ yum -y install perl-CPAN perl-DBD-MySQL perl-Config-Tiny perl-Log-Dispatch perl-Parallel-ForkManager perl-Module-Install

- MHA 설치

$ tar xvzf mha4mysql-manager-0.57.tar.gz

$ cd mha4mysql-manager-0.57

$ perl Makefile.PL

$ make

$ make install

mysql 5.7 설치

mysql 5.7.14 패키지 다운로드

URL : https://downloads.mysql.com/archives/community/downloads.mysql.com/archive

 

$ wget https://downloads.mysql.com/archives/get/p/23/file/mysql-5.7.14-1.el7.x86_64.rpm-bundle.tar

mysql 설치

$ tar xf mysql-5.7.14-1.el7.x86_64.rpm-bundle.tar


$ yum install -y \
mysql-community-client-5.7.14-1.el7.x86_64.rpm \
mysql-community-common-5.7.14-1.el7.x86_64.rpm \
mysql-community-devel-5.7.14-1.el7.x86_64.rpm \
mysql-community-libs-5.7.14-1.el7.x86_64.rpm \
mysql-community-libs-compat-5.7.14-1.el7.x86_64.rpm \
mysql-community-server-5.7.14-1.el7.x86_64.rpm

서비스를 시작하고 부팅 시 자동으로 시작하도록 활성화

$ systemctl --now enable mysqld

mysql 5.7 임시 비밀번호(temporary password)

$ grep 'temporary password' /var/log/mysqld.log
2021-08-26T02:43:55.028238Z 1 [Note] A temporary password is generated for root@localhost: >WOZgw6axeN4

mysql 보안 설정(mysql_secure_installation)

- root 비밀번호 변경

$ mysql_secure_installation

Securing the MySQL server deployment.

Enter password for user root:

The existing password for the user account root has expired. Please set a new password.

New password:

Re-enter new password:
The 'validate_password' plugin is installed on the server.
The subsequent steps will run with the existing configuration
of the plugin.
Using existing password for root.

Estimated strength of the password: 100
Change the password for root ? ((Press y|Y for Yes, any other key for No) : No

 ... skipping.
By default, a MySQL installation has an anonymous user,
allowing anyone to log into MySQL without having to have
a user account created for them. This is intended only for
testing, and to make the installation go a bit smoother.
You should remove them before moving into a production
environment.

Remove anonymous users? (Press y|Y for Yes, any other key for No) : Yes
Success.


Normally, root should only be allowed to connect from
'localhost'. This ensures that someone cannot guess at
the root password from the network.

Disallow root login remotely? (Press y|Y for Yes, any other key for No) : No

 ... skipping.
By default, MySQL comes with a database named 'test' that
anyone can access. This is also intended only for testing,
and should be removed before moving into a production
environment.


Remove test database and access to it? (Press y|Y for Yes, any other key for No) : Yes
 - Dropping test database...
Success.

 - Removing privileges on test database...
Success.

Reloading the privilege tables will ensure that all changes
made so far will take effect immediately.

Reload privilege tables now? (Press y|Y for Yes, any other key for No) : Yes
Success.

All done!

root 접속 테스트

$ mysql -uroot -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 17
Server version: 5.7.14 MySQL Community Server (GPL)

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show global variables like '%dir%';
+-----------------------------------------+----------------------------+
| Variable_name                           | Value                      |
+-----------------------------------------+----------------------------+
| basedir                                 | /usr/                      |
| binlog_direct_non_transactional_updates | OFF                        |
| character_sets_dir                      | /usr/share/mysql/charsets/ |
| datadir                                 | /var/lib/mysql/            |
| ignore_db_dirs                          |                            |
| innodb_data_home_dir                    |                            |
| innodb_log_group_home_dir               | ./                         |
| innodb_max_dirty_pages_pct              | 75.000000                  |
| innodb_max_dirty_pages_pct_lwm          | 0.000000                   |
| innodb_tmpdir                           |                            |
| innodb_undo_directory                   | ./                         |
| lc_messages_dir                         | /usr/share/mysql/          |
| plugin_dir                              | /usr/lib64/mysql/plugin/   |
| slave_load_tmpdir                       | /tmp                       |
| tmpdir                                  | /tmp                       |
+-----------------------------------------+----------------------------+
15 rows in set (0.01 sec)

MHA 접속 계정 생성

> GRANT ALL PRIVILEGES ON *.* TO 'mha'@'192.168.0.%' IDENTIFIED BY 'Mha11@@@';

> FLUSH PRIVILEGES;

$ mysql -uroot -p'password1!' mysql -e "select user, host from user;"
mysql: [Warning] Using a password on the command line interface can be insecure.
+-----------+-----------+
| user      | host      |
+-----------+-----------+
| mha       | %         |
| repl      | %         |
| mysql.sys | localhost |
| root      | localhost |
+-----------+-----------+

 

MHA(MHA Manager and MHA Node) 설정

samples 파일

$ tar xfz mha4mysql-manager-0.57.tar.gz

$ cd mha4mysql-manager-0.57/samples/scripts

$ ls
master_ip_failover  master_ip_online_change  power_manager  send_report

app1.conf 편집

$ cp /usr/local/src/mha4mysql-manager-0.57/samples/conf/app1.cnf /etc/app1.cnf

 

SHELL 계정 생성

mhauser / Mha11@@@

$ useradd -m -d /home/mhauser -g mysql -s /bin/bash mhauser


$ echo "Mha11@@@" | passwd --stdin mhauser

mhauser sudo 권한 부여

$ echo "mhauser         ALL=(ALL)       NOPASSWD: /sbin/ifconfig" >> /etc/sudoers

$ visudo
...
#includedir /etc/sudoers.d

mhauser         ALL=(ALL)       NOPASSWD: /sbin/ifconfig

ssh key 교환(management 서버)

$ su - mhauser


$ ssh-keygen -t rsa -b 4096 -C "mhauser" 
Generating public/private rsa key pair.
Enter file in which to save the key (/home/mhauser/.ssh/id_rsa): 
Created directory '/home/mhauser/.ssh'.
Enter passphrase (empty for no passphrase): 
Enter same passphrase again: 
Your identification has been saved in /home/mhauser/.ssh/id_rsa.
Your public key has been saved in /home/mhauser/.ssh/id_rsa.pub.
The key fingerprint is:
SHA256:NMnXp/SyJDkMacOms420kMQQPLYCqXwNl0tnaUlPq10 mhauser
The key's randomart image is:
+---[RSA 4096]----+
| oo.  o.o.       |
|o ++ + Ooo..     |
|+. oB = %o.Eo .  |
|o..o + =o*.o +   |
| .. o +.S.= + .  |
|     o *   + o   |
|      + .   .    |
|                 |
|                 |
+----[SHA256]-----+

ssh key 복사

$ ssh-copy-id mhauser@192.168.0.101
/bin/ssh-copy-id: INFO: Source of key(s) to be installed: "/home/mhauser/.ssh/id_rsa.pub"
The authenticity of host '192.168.0.101 (192.168.0.101)' can't be established.
ECDSA key fingerprint is SHA256:hjHaJ3ovLxWC4TsTJl/iFC7HGbxzif8hktkJdeONfpM.
ECDSA key fingerprint is MD5:59:7b:d8:f0:82:fc:10:40:7d:a6:78:15:e7:ac:61:d0.
Are you sure you want to continue connecting (yes/no)? yes
/bin/ssh-copy-id: INFO: attempting to log in with the new key(s), to filter out any that are already installed
/bin/ssh-copy-id: INFO: 1 key(s) remain to be installed -- if you are prompted now it is to install the new keys
mhauser@192.168.20.147's password: 

Number of key(s) added: 1

Now try logging into the machine, with:   "ssh 'mhauser@192.168.0.101'"
and check to make sure that only the key(s) you wanted were added.

$ ssh-copy-id mhauser@192.168.0.102

연결 테스트

$ ssh mhauser@192.168.0.101


$ ssh mhauser@192.168.0.102

서로 접속할 수 있도록 키 복사

#모니터링 서버에서 마스터 서버, 슬레이브 서버 키 파일 전송
$ scp id_rsa mhauser@192.168.0.101:/home/mhauser/.ssh/
$ scp id_rsa.pub mhauser@192.168.0.101:/home/mhauser/.ssh/

$ scp id_rsa mhauser@192.168.0.102:/home/mhauser/.ssh/
$ scp id_rsa.pub mhauser@192.168.0.102:/home/mhauser/.ssh/

$ chmod 600 id_rsa

#마스터 서버에서 모니터링(슬레이브 서버)로 키 전송
$ ssh-copy-id mhauser@192.168.0.100