1、如果主節(jié)點已經(jīng)運行了一段時間，且有大量數(shù)據(jù)時，如何配置并啟動slave節(jié)點（寫出操作步驟）

環(huán)境準備：兩臺centos8
10.0.0.8
10.0.0.18

#主節(jié)點下載mariadb 設(shè)為開機自啟
[root@master ~]#[root@master ~]# yum -y install mariadb-server;systemctl enable --now mariadb
[root@master ~]#vim /etc/my.cnf
[mysqld]
log-bin 
[root@master ~]# systemctl restart mysqld
#[root@master ~]# mysql
MariaDB [(none)]> 
MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
3 rows in set (0.000 sec)
#模擬數(shù)據(jù)庫已經(jīng)運行了一段時間，上面進行了一些操作
MariaDB [(none)]> create database db1;
MariaDB [(none)]> use db1;
Database changed
MariaDB [db1]> create table test(id int auto_increment primary key ,name char(5));
Query OK, 0 rows affected (0.008 sec)
MariaDB [db1]> insert test (id,name) values(1,'yang');
Query OK, 1 row affected (0.002 sec)
MariaDB [db1]> insert test(id,name)values(2,'wang');
?
MariaDB [db1]> select *from test;
+----+------+
| id | name |
+----+------+
|  1 | yang |
|  2 | wang |
+----+------+
2 rows in set (0.000 sec)
#此時的二進制位置是
MariaDB [(none)]> show master logs;
+--------------------+-----------+
| Log_name           | File_size |
+--------------------+-----------+
| mariadb-bin.000001 |      550|
?
1 row in set (0.000 sec)
#創(chuàng)建授權(quán)賬號
MariaDB [db1]> grant replication slave on *.* to repluser@'10.0.0.%' identified by 'magedu'；
?
#在主服務(wù)器完全備份
[root@master ~]#mkdir /backup
[root@master ~]#mysqldump -A -F --single-transaction --master-data=1 > /backup/fullbackup_`date +%F_%T`.sql
[root@master ~]# ll /backup
total 472
-rw-r--r-- 1 root root 480092 Oct 16 19:47 fullbackup_2020-10-16_19:47:34.sql
#把完全備份的傳給從節(jié)點18
[root@master ~]#scp /backup/fullbackup_2020-10-16_19:47:34.sql 10.0.0.18:/root
?

#配置從節(jié)點
#下載數(shù)據(jù)庫修改配置文件
[root@slave ~]#dnf -y install mariadb-server
root@slave~]#systemctl enable --now mariadb
[root@slave~]#vim /etc/my.cnf.d/mariadb-server.cnf 
[mysqld]
server-id=18
read-only
[root@slave ~]#systemctl restart mariadb
#將完全備份還原到新的從節(jié)點
#配置從節(jié)點，從完全備份的位置之后開始復(fù)制
[root@slave ~]#grep '^CHANGE MASTER' /data/fullbackup_2020-10-16_19:47:34.sql 
CHANGE MASTER TO MASTER_LOG_FILE='mariadb-bin.000003', MASTER_LOG_POS=389;
?
#注意語法，在備份文件中添加主節(jié)點的服務(wù)器 賬號和密碼端口號
[root@slave ~]#vim /data/fullbackup_2020-10-16_19:47:34.sql 
CHANGE MASTER TO
MASTER_HOST='10.0.0.8',
MASTER_USER='repluser',
MASTER_PASSWORD='magedu',
MASTER_PORT=3306,        
MASTER_LOG_FILE='mariadb-bin.000003', MASTER_LOG_POS=389;
?
#把備份導(dǎo)入到數(shù)據(jù)庫中
[root@slave ~]#mysql < /data/fullbackup_2020-10-16_19:47:34.sql 
[root@slave ~]#mysql 
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 9
Server version: 10.3.11-MariaDB MariaDB Server
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
?
#開啟線程
MariaDB [(none)]> start slave;
#開啟之后線程連接成功
MariaDB [(none)]> show slave status\G;
*************************** 1. row ***************************
                Slave_IO_State: Waiting for master to send event
                   Master_Host: 10.0.0.8
                   Master_User: repluser
                   Master_Port: 3306
                 Connect_Retry: 60
               Master_Log_File: mariadb-bin.000003
           Read_Master_Log_Pos: 389
                Relay_Log_File: mariadb-relay-bin.000002
                 Relay_Log_Pos: 557
         Relay_Master_Log_File: mariadb-bin.000003
              Slave_IO_Running: Yes
             Slave_SQL_Running: Yes
?
#主節(jié)點的數(shù)據(jù)已經(jīng)完全同步了過來
MariaDB [db1]> select *from db1.test;
+----+------+
| id | name |
+----+------+
|  1 | yang |
|  2 | wang |
+----+------+
2 rows in set (0.000 sec)
#主節(jié)點上出現(xiàn)binlog dump 連接
MariaDB [(none)]> show processlist;

2、當master服務(wù)器宕機，提升一個slave成為新的master（寫出操作步驟）

在第一個道題實驗主從架構(gòu)的基礎(chǔ)上再加一個從節(jié)點達到1主兩從

#增加一個從節(jié)點10.0.0.28
#把完全備份的傳給從節(jié)點18
[root@master ~]#scp /backup/fullbackup_2020-10-16_19:47:34.sql 10.0.0.18:/root
[root@slave2 ~]#dnf -y install mariadb-server;systemctl enable --now mariadb
[root@slave2 ~]#vim /etc/my.cnf.d/mariadb-server.cnf 
[mysqld]
server-id=28
read-only
?
#
[root@slave2 ~]#systemctl restart mariadb
?
#修改文件同步位置
[root@slave2 ~]#vim fullbackup_2020-10-16_19:47:34.sql
MCHANGE MASTER TO
MASTER_HOST='10.0.0.8',
MASTER_USER='repluser',
MASTER_PASSWORD='magedu',
MASTER_PORT=3306,
MASTER_LOG_FILE='mariadb-bin.000003', MASTER_LOG_POS=389;
?
#導(dǎo)入主節(jié)點的數(shù)據(jù)，同步成功
[root@slave2 ~]#mysql <fullbackup_2020-10-16_19:47:34.sql
[root@slave2 ~]#mysql
MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.002 sec)
?
MariaDB [(none)]> show slave status\G
*************************** 1. row ***************************
                Slave_IO_State: Waiting for master to send event
                   Master_Host: 10.0.0.8
                   Master_User: repluser
                   Master_Port: 3306
                 Connect_Retry: 60
               Master_Log_File: mariadb-bin.000003
           Read_Master_Log_Pos: 389
                Relay_Log_File: mariadb-relay-bin.000002
                 Relay_Log_Pos: 557
         Relay_Master_Log_File: mariadb-bin.000003
              Slave_IO_Running: Yes
             Slave_SQL_Running: Yes
?
#模擬主節(jié)點宕機，先導(dǎo)入testlog.log 執(zhí)行十萬條記錄，看哪個節(jié)點同步的數(shù)據(jù)多 選誰等新主節(jié)點
 [root@master ~]# mysql
 MariaDB [(none)]> use db1
 #執(zhí)行存儲過程的時候 停掉服務(wù)
 MariaDB [db1]> call sp_testlog; 
ERROR 1053 (08S01): Server shutdown in progress
[root@master ~]# systemctl stop mariadb
#比較兩個從節(jié)點的同步情況，選擇slave1作為新主
[root@slave1~]#cat /var/lib/mysql/relay-log.info 
5
./mariadb-relay-bin.000002
4172783
mariadb-bin.000003
4172615
0
[root@slave2 ~]#cat /var/lib/mysql/relay-log.info 
5
./mariadb-relay-bin.000002
4160557
mariadb-bin.000003
4103890
0
?
#在18機器上修改作為新主
#新master修改配置文件，關(guān)閉read-only配置
[root@slave1 ~]#vim /etc/my.cnf.d/mariadb-server.cnf
[mysqld]
server-id=18
log-bin
[root@slave1 ~]#mysql
MariaDB [hellodb]>stop slave;
MariaDB [hellodb]>reset slave all;
[root@centos8 ~]#systemctl restart mariadb
[root@slave1 ~]#mysql
MariaDB [(none)]> show master logs;
+--------------------+-----------+
| Log_name           | File_size |
+--------------------+-----------+
| mariadb-bin.000001 |       330 |
MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| db1                |
| db2                |
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
5 rows in set (0.001 sec)
#18上完全備份并拷貝給28
[root@slave1~]#mysqldump -A -F --single-transaction --master-data=1  > backup.sql
[root@slave1~]#scp backup.sql 10.0.0.28:/root
?
#在28上修改備份文件指向18
[root@slave2 ~]#vim backup.sql 
CHANGE MASTER TO
 MASTER_HOST='10.0.0.18',
 MASTER_USER='repluser',
 MASTER_PASSWORD='magedu',
 MASTER_PORT=3306,
MASTER_LOG_FILE='mariadb-bin.000002', MASTER_LOG_POS=375;
#導(dǎo)入slave1的數(shù)據(jù)，在從節(jié)點上重新指向18，作為18的從節(jié)點
[root@slave2 ~]#mysql <backup.sql
MariaDB [(none)]> start slave;
MariaDB [(none)]> show slave status\G
*************************** 1. row 
Slave_IO_State: Waiting for master to send event
                   Master_Host: 10.0.0.18
                   Master_User: repluser
                   Master_Port: 3306
                 Connect_Retry: 60
               Master_Log_File: mariadb-bin.000002
           Read_Master_Log_Pos: 502
                Relay_Log_File: mariadb-relay-bin.000002
                 Relay_Log_Pos: 684
         Relay_Master_Log_File: mariadb-bin.000002
              Slave_IO_Running: Yes
             Slave_SQL_Running: Yes
#數(shù)據(jù)同步 主從關(guān)系重新建立
MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| db1                |             |
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
5 rows in set (0.001 sec)

3、通過 MHA 0.58 搭建一個數(shù)據(jù)庫集群結(jié)構(gòu)

準備環(huán)境：

環(huán)境:四臺主機 centos8上面的數(shù)據(jù)庫使用腳本跑的二進制安裝mysql5.7.30
10.0.0.7 CentOS7 MHA管理端
10.0.0.8 CentOS8 Master
10.0.0.18 CentOS8 Slave1
10.0.0.28 CentOS8 Slave2

1、在管理節(jié)點上安裝兩個包

#在10.0.0.7上裝上這兩個準備好0.58的mha包和腳本
mha4mysql-manager
mha4mysql-node
[root@mha-manager ~]# ls
anaconda-ks.cfg                 mha4mysql-manager-0.58-0.el7.centos.noarch.rpm  mysql-5.7.30-linux-glibc2.12-x86_64.tar.gz
install_mysql5.7_for_centos.sh  mha4mysql-node-0.58-0.el7.centos.noarch.rpm
#先裝node包再裝manager包
[root@mha-manager ~]#yum -y install mha4mysql-node-0.58-0.el7.centos.noarch.rpm
[root@mha-manager ~]#yum -y install mha4mysql-manager-0.58-0.el7.centos.noarch.rpm
?
#其他機器上裝的node包

修改主機名看起來方便

[root@centos7mini ~]#hostnamectl set-hostname mha-manager
[root@centos7mini ~]#exit
[root@mha-manager ~]# 
?
[root@centos8mini ~]#hostnamectl set-hostname Master
[root@centos8mini ~]#exit
?
[root@centos8 ~]#hostnamectl set-hostname slave1
[root@centos8 ~]#exit
?
[root@centos8 ~]#hostnamectl set-hostname slave2
[root@centos8 ~]#exit

2、在所有MySQL服務(wù)器節(jié)點上安裝node包

#在三個節(jié)點上分別用腳本跑mysql5.7安裝
[root@master ~]#bash install_mysql5.7_for_centos.sh mysql-5.7.30-linux-glibc2.12-x86_64.tar.gz 
#安裝成功后退出生效
?
#在三個節(jié)點上安裝node包，可以在xshell全部會話一下安裝（把包分別拷貝到每個機器上的情況下）
[root@master ~]#yum -y install mha4mysql-node-0.58-0.el7.centos.noarch.rpm
[root@slave1 ~]#yum -y install mha4mysql-node-0.58-0.el7.centos.noarch.rpm
[root@slave2 ~]#yum -y install mha4mysql-node-0.58-0.el7.centos.noarch.rpm
?
?

3、在所有節(jié)點實現(xiàn)相互之間ssh key 驗證（涉及到安全生產(chǎn)中慎用）

#三次回車生成密鑰
[root@mha-manager ~]#ssh-keygen
#拷貝到自己身上
[root@mha-manager ~]# ssh-copy-id 127.0.0.1
/usr/bin/ssh-copy-id: INFO: Source of key(s) to be installed: "/root/.ssh/id_rsa.pub"
The authenticity of host '127.0.0.1 (127.0.0.1)' can't be established.
ECDSA key fingerprint is SHA256:8ISIZGc2lZ0Pp4HdoiYVyNUNyfI09vpHfuMc01Bxhqo.
ECDSA key fingerprint is MD5:1f:d9:62:2d:94:3d:b5:99:c8:e2:45:af:cd:b2:bd:5c.
Are you sure you want to continue connecting (yes/no)? yes
/usr/bin/ssh-copy-id: INFO: attempting to log in with the new key(s), to filter out any that are already installed
/usr/bin/ssh-copy-id: INFO: 1 key(s) remain to be installed -- if you are prompted now it is to install the new keys
?
root@127.0.0.1's password: #輸入密碼
Number of key(s) added: 1
Now try logging into the machine, with:   "ssh '127.0.0.1'"
and check to make sure that only the key(s) you wanted were added..
?
#為了保留屬性 使用rsycn復(fù)制整個目錄本身給其他幾個主機
[root@mha-manager ~]# yum -y install rsync （在全部會話都要下載）
[root@mha-manager ~]#rsync -av .ssh 10.0.0.8:/root/
[root@mha-manager ~]#rsync -av .ssh 10.0.0.18:/root/
[root@mha-manager ~]#rsync -av .ssh 10.0.0.28:/root/

4、在管理節(jié)點7上建立配置文件

#建一個單獨文件 叫什么存在那不重要，存放mha內(nèi)容
[root@mha-manager ~]#mkdir /etc/mastermha/

#mha可以監(jiān)控多組 之所以叫app1是第一組主從
[root@mha-manager ~]#vim /etc/mastermha/app1.cnf 
[server default]
user=mhauser
password=magedu
manager_workdir=/data/mastermha/app1/
manager_log=/data/mastermha/app1/manager.log
remote_workdir=/data/mastermha/app1/
ssh_user=root
repl_user=repluser
repl_password=magedu
ping_interval=1
master_ip_failover_script=/usr/local/bin/master_ip_failover
report_script=/usr/local/bin/sendmail.sh
check_repl_delay=0
master_binlog_dir=/data/mysql/

[server1]
hostname=10.0.0.8
candidate_master=1    
[server2]
hostname=10.0.0.18
[server3]
hostname=10.0.0.28
candidate_master=1  

在manager7上準備相關(guān)腳本

#發(fā)郵件的腳本 還有準備.mailrc文件
#想要郵件報警 需要下載郵件服務(wù)和.mailrc 服務(wù)
[root@mha-manager ~]# yum -y install mailx postfix
#mailrc文件
[root@mha-manager ~]#vim .mailrc
set from=1443964024@qq.com
set smtp=smtp.qq.com
set smtp-auth-user=1443964024@qq.com
set smtp-auth-password=mzhhwiiuddqahbba                                                               
set smtp-auth=login
set ssl-verify=ignore
#測試一下使用命令看能不能收到，郵件服務(wù)能不能用
[root@mha-manager~]#vim /usr/local/bin/sendmail.sh
echo "MySQL is down" | mail -s "MHA Warning" 1443964024@qq.com   
?
[root@mha-manager ~]#chmod +x /usr/local/bin/sendmail.sh
[root@mha-manager ~]# /usr/local/bin/sendmail.sh 可以收到郵件警報了
?
#需要注意的是 網(wǎng)關(guān)ip和虛擬ip即vip 還有所在網(wǎng)卡要手動指定，想要當預(yù)備主節(jié)點的從網(wǎng)卡名一定要是文件里面指定的eth0
以及使用下面文件時 把注釋漢語信息刪掉
[root@mha-manager ~]#vim /usr/local/bin/master_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_master_ip, $new_master_port
);
my $vip = '10.0.0.100/24';                        
my $gateway = '10.0.0.254';
my $interface = 'eth0';
my $key = "1";
my $ssh_start_vip = "/sbin/ifconfig $interface:$key $vip;/sbin/arping -I 
$interface -c 3 -s $vip $gateway >/dev/null 2>&1";
my $ssh_stop_vip = "/sbin/ifconfig $interface:$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" ) {
# $orig_master_host, $orig_master_ip, $orig_master_port are passed.
# If you manage master ip address at 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 manage master ip address at 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\@$orig_master_host \" $ssh_start_vip \"`;
exit 0; }
else {
&usage();
exit 1; }}
# A simple system call that enable the VIP on the new master
sub start_vip() {
`ssh $ssh_user\@$new_master_host \" $ssh_start_vip \"`; }
# A simple system call 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_master_ip=ip --orig_master_port=port --
new_master_host=host --new_master_ip=ip --new_master_port=port\n";
}
#給執(zhí)行權(quán)限
[root@mha-manager ~]#chmod +x /usr/local/bin/master_ip_failover
[root@mha-manager bin]# ll /usr/local/bin/master_ip_failover
-rwxr-xr-x 1 root root 2405 Oct 15 12:37 /usr/local/bin/master_ip_failover
?

5、配置Master

[root@master ~]#mkdir /data/mysql/
[root@master ~]#chown mysql.mysql /data/mysql/
[root@master ~]#vim /etc/my.cnf
[mysqld]
server_id=8
log-bin
skip_name_resolve=1
general_log     #非必須項

[root@Master ~]# systemctl restart mysqld
[root@Master ~]# mysql -uroot -pcentos 
#查看二進制日志位置 并創(chuàng)建授權(quán)賬號
mysql> show master logs;
+-------------------+-----------+
| Log_name          | File_size |
+-------------------+-----------+
| Master-bin.000001 |       154 |
#建立授權(quán)賬號
mysql>grant replication slave on *.* to repluser@'10.0.0.%' identified by 'magedu';
#和配置文件里mhauser密碼一定要匹配
mysql>grant all on *.* to mhauser@'10.0.0.%' identified by 'magedu'; 

mysql> select user,host from mysql.user;
+---------------+-----------+
| user          | host      |
+---------------+-----------+
| mhauser       | 10.0.0.%  |
| repluser      | 10.0.0.%  |
| mysql.session | localhost |
| mysql.sys     | localhost |
| root          | localhost |
+---------------+-----------+

#配置VIP
#安所有節(jié)點裝nte-tools包
[root@master ~]#yum -y install net-tools
[root@slave1 ~]#yum -y install net-tools
[root@slave2 ~]#yum -y install net-tools
[root@master ~]#ifconfig eth0:1 10.0.0.100/24
[root@Master ~]# ip a
1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN group default qlen 1000
    link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
    inet 127.0.0.1/8 scope host lo
       valid_lft forever preferred_lft forever
    inet6 ::1/128 scope host 
       valid_lft forever preferred_lft forever
2: eth0: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc mq state UP group default qlen 1000
    link/ether 00:0c:29:c3:1f:d4 brd ff:ff:ff:ff:ff:ff
    inet 10.0.0.8/24 brd 10.0.0.255 scope global noprefixroute eth0
       valid_lft forever preferred_lft forever
    inet 10.0.0.100/24 brd 10.0.0.255 scope global secondary eth0:1
       valid_lft forever preferred_lft forever

6、分別配置另外的slave節(jié)點

#配置slave1
[root@slave1 ~]vim /etc/my.cnf
[mysqld]
server_id=18   #不同節(jié)點此值各不相同
log-bin
read_only
general_log     #非必須項
relay_log_purge=0 #不讓中繼日志清理
skip_name_resolve=1    #禁止反向解析
[root@slave1 ~]#systemctl restart mysqld
?
#建立主從關(guān)系
[root@slave1 ~]#mysql -uroot -pcentos
mysql> CHANGE MASTER TO
 MASTER_HOST='10.0.0.8',
 MASTER_USER='repluser',
 MASTER_PASSWORD='magedu',
 MASTER_PORT=3306,
 MASTER_LOG_FILE=' Master-bin.000001', MASTER_LOG_POS=154;
#開啟進程
mysql>START SLAVE;
mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.0.0.8
                  Master_User: repluser
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: Master-bin.000001
          Read_Master_Log_Pos: 1198
               Relay_Log_File: slave1-relay-bin.000002
                Relay_Log_Pos: 1365
        Relay_Master_Log_File: Master-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
?
#配置slave2
[root@slave2 ~]#vim /etc/my.cnf
[mysqld]
server-id=28
log-bin
read-only
general_log     #非必須項
relay_log_purge=0
skip_name_resolve=1
#建立主從關(guān)系
[root@slave1 ~]#mysql -uroot -pcentos
mysql> CHANGE MASTER TO
 MASTER_HOST='10.0.0.8',
 MASTER_USER='repluser',
 MASTER_PASSWORD='magedu',
 MASTER_PORT=3306,
 MASTER_LOG_FILE=' Master-bin.000001', MASTER_LOG_POS=154;
 #開機進程
 mysql>START SLAVE;
 mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.0.0.8
                  Master_User: repluser
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: Master-bin.000001
          Read_Master_Log_Pos: 894
               Relay_Log_File: slave2-relay-bin.000002
                Relay_Log_Pos: 1061
        Relay_Master_Log_File: Master-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
?
#測試：主從是否同步
[root@Master ~]# mysql -uroot -pcentos
mysql> create database db1;
Query OK, 1 row affected (0.01 sec)
[root@slave1 ~]#mysql -uroot -pcentos
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| db1                |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.01 sec)
[root@slave2 ~]#mysql -uroot -pcentos
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| db1                |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.01 sec)
?

7、檢查Mha的環(huán)境

#檢查環(huán)境 確認當前環(huán)境是否滿足mha的要求
#檢查ssh環(huán)境是否符合
[root@mha-manager ~]#masterha_check_ssh --conf=/etc/mastermha/app1.cnf
Thu Oct 15 15:09:47 2020 - [info] All SSH connection tests passed successfully.
#檢查復(fù)制的若干要求
[root@mha-manager ~]#masterha_check_repl --conf=/etc/mastermha/app1.cnf
Thu Oct 15 15:10:08 2020 - [info]  OK.
Thu Oct 15 15:10:08 2020 - [warning] shutdown_script is not defined.
Thu Oct 15 15:10:08 2020 - [info] Got exit code 0 (Not master dead).

MySQL Replication Health is OK.

8、啟動MHA

#開啟MHA,默認是前臺運行，加上nohup執(zhí)行完后臺運行
nohup masterha_manager --conf=/etc/mastermha/app1.cnf &> /dev/null 
#前臺運行的狀態(tài)如下 啟動成功
[root@mha-manager ~]# masterha_manager --conf=/etc/mastermha/app1.cnf
Thu Oct 15 15:21:09 2020 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Thu Oct 15 15:21:09 2020 - [info] Reading application default configuration from /etc/mastermha/app1.cnf..
Thu Oct 15 15:21:09 2020 - [info] Reading server configuration from /etc/mastermha/app1.cnf..
?
#查看狀態(tài)，運行中
[root@mha-manager ~]# masterha_check_status --conf=/etc/mastermha/app1.cnf
app1 (pid:3608) is running(0:PING_OK), master:10.0.0.8

4、實戰(zhàn)案例：Percona XtraDB Cluster(PXC 5.7）

1.環(huán)境準備

四臺主機：四臺centos7系統(tǒng)
pxc1:10.0.0.7
pxc2:10.0.0.17
pxc3:10.0.0.27
pxc4:10.0.0.37
?

2.安裝Percona XtraDB Cluster 5.7

#建個倉庫 此處使用清華大學(xué)yum源，官方源太慢了
[root@pxc1 ~]#vim /etc/yum.repos.d/pxc.repo
[percona]
name=percona_repo
baseurl =
https://mirrors.tuna.tsinghua.edu.cn/percona/release/$releasever/RPMS/$basearch
enabled = 1
gpgcheck = 0
[root@pxc1 ~]# yum repolist

#拷貝到另外兩個節(jié)點
[root@pxc1 ~]#scp /etc/yum.repos.d/pxc.repo 10.0.0.17:/etc/yum.repos.d
[root@pxc1 ~]#scp /etc/yum.repos.d/pxc.repo 10.0.0.27:/etc/yum.repos.d
在三個節(jié)點都安裝好PXC 5.7 
[root@pxc1 ~]#yum install Percona-XtraDB-Cluster-57 -y
[root@pxc2 ~]#yum install Percona-XtraDB-Cluster-57 -y
[root@pxc3 ~]#yum install Percona-XtraDB-Cluster-57 -y

3.在各個節(jié)點上分別配置mysql及集群配置文件

#PXC的配置文件必須修改，先修改第一個節(jié)點的
[root@pxc1 ~]#vim /etc/percona-xtradb-cluster.conf.d/wsrep.cnf 
[mysqld]
wsrep_provider=/usr/lib64/galera3/libgalera_smm.so
wsrep_cluster_address=gcomm://10.0.0.7,10.0.0.17,10.0.0.27
binlog_format=ROW
default_storage_engine=InnoDB
wsrep_slave_threads= 8
wsrep_log_conflicts
innodb_autoinc_lock_mode=2
wsrep_node_address=10.0.0.7
wsrep_cluster_name=pxc-cluster
wsrep_node_name=pxc-cluster-node-1
pxc_strict_mode=ENFORCING
wsrep_sst_method=xtrabackup-v2 
wsrep_sst_auth="sstuser:s3cretPass"
?
#修改另外兩個節(jié)點配置文件
[root@pxc2 ~]#vim /etc/percona-xtradb-cluster.conf.d/wsrep.cnf 
[mysqld]
wsrep_provider=/usr/lib64/galera3/libgalera_smm.so
wsrep_cluster_address=gcomm://10.0.0.7,10.0.0.17,10.0.0.27
binlog_format=ROW
default_storage_engine=InnoDB
wsrep_slave_threads= 8
wsrep_log_conflicts
innodb_autoinc_lock_mode=2         
wsrep_node_address=10.0.0.17
wsrep_cluster_name=pxc-cluster
wsrep_node_name=pxc-cluster-node-2
pxc_strict_mode=ENFORCING
wsrep_sst_method=xtrabackup-v2
wsrep_sst_auth="sstuser:s3cretPass"   #取消本行注釋
?
[root@pxc3 ~]#vim /etc/percona-xtradb-cluster.conf.d/wsrep.cnf
[mysqld]
wsrep_provider=/usr/lib64/galera3/libgalera_smm.so #核心庫
wsrep_cluster_address=gcomm://10.0.0.7,10.0.0.17,10.0.0.27
binlog_format=ROW
default_storage_engine=InnoDB
wsrep_slave_threads= 8
wsrep_log_conflicts
innodb_autoinc_lock_mode=2   
wsrep_node_address=10.0.0.27
wsrep_cluster_name=pxc-cluster
wsrep_node_name=pxc-cluster-node-3
pxc_strict_mode=ENFORCING
wsrep_sst_method=xtrabackup-v2
wsrep_sst_auth="sstuser:s3cretPass"   #取消本行注釋

4、啟動PXC集群中第一個節(jié)點 和后續(xù)節(jié)點開啟方法不一樣

#啟動第一個節(jié)點 和后續(xù)節(jié)點開啟方法不一樣
[root@pxc1 ~]#systemctl start mysql@bootstrap.service
#4567和3306端口打開
[root@pxc1 ~]#ss -ntul
Netid State     Recv-Q Send-Q     Local Address:Port                   Peer 
Address:Port              
udp   UNCONN     0      0              127.0.0.1:323                             
  *:*   
udp   UNCONN     0      0                   ::1:323                             
  :::*   
tcp   LISTEN     0      128                   *:22                             
    *:*   
tcp   LISTEN     0      128                   *:4567                           
    *:*   
tcp   LISTEN     0      100            127.0.0.1:25                             
    *:*   
tcp   LISTEN     0      80                   :::3306                           
  :::*   
tcp   LISTEN     0      128                   :::22                             
  :::*   
tcp   LISTEN     0      100                 ::1:25                             
  :::*    
  
#查看root密碼
[root@pxc1 ~]# grep "temporary password" /var/log/mysqld.log
2020-10-17T01:29:27.829098Z 1 [Note] A temporary password is generated for root@localhost: qc(/#nCr?1zk
?
[root@pxc1 ~]#mysql -uroot -p'=tWFP0oRJl8t'
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 12
Server version: 5.7.27-30-57-log
Copyright (c) 2009-2019 Percona LLC and/or its affiliates
Copyright (c) 2000, 2019, 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.
?
#修改root密碼（已經(jīng)不允許使用mysqladmin password修改密碼了 不安全）
mysql> alter user 'root'@'localhost' identified by 'magedu';
Query OK, 0 rows affected (0.01 sec)
#創(chuàng)建相關(guān)用戶并授權(quán)
mysql> CREATE USER 'sstuser'@'localhost' IDENTIFIED BY 's3cretPass';
Query OK, 0 rows affected (0.00 sec)
mysql> GRANT RELOAD, LOCK TABLES, PROCESS, REPLICATION CLIENT ON *.* TO 'sstuser'@'localhost';
Query OK, 0 rows affected (0.01 sec)
 #查看有幾個節(jié)點                                               mysql> show status like  'wsrep_cluster_size';
| Variable_name      | Value |
+--------------------+-------+
| wsrep_cluster_size | 1   |
+--------------------+-------+
                                                                                 

5、啟動PXC集群中其他所有節(jié)點

#后續(xù)節(jié)點直接啟動即可
[root@pxc2 ~]#systemctl start mysql
[root@pxc3 ~]#systemctl start mysql
#4567和 3306端口打開
[root@pxc2 ~]#ss -ntulp

6、查看集群狀態(tài)，驗證集群是否成功

#在任意節(jié)點，查看集群狀態(tài) 密碼都是magedu
[root@pxc1 ~]#mysql -uroot -pmagedu
mysql> SHOW VARIABLES LIKE 'wsrep_node_name';
+-----------------+--------------------+
| Variable_name   | Value             |
+-----------------+--------------------+
| wsrep_node_name | pxc-cluster-node-1 |
+-----------------+--------------------+
1 row in set (0.00 sec)
mysql> SHOW VARIABLES LIKE 'wsrep_node_address';
+--------------------+----------+
| Variable_name     | Value   |
+--------------------+----------+
| wsrep_node_address | 10.0.0.7 |
+--------------------+----------+
1 row in set (0.01 sec)
mysql> SHOW VARIABLES LIKE 'wsrep_on';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| wsrep_on     | ON   |
+---------------+-------+
1 row in set (0.00 sec)
mysql> SHOW STATUS LIKE 'wsrep_cluster_size';
+--------------------+-------+
| Variable_name     | Value |
+--------------------+-------+
| wsrep_cluster_size | 3     |
+--------------------+-------+
1 row in set (0.01 sec)
#在任意節(jié)點查看數(shù)據(jù)庫
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql             |
| performance_schema |
| sys               |
+--------------------+
4 rows in set (0.00 sec)
#在任意節(jié)點創(chuàng)建數(shù)據(jù)庫
mysql> create database testdb1;
Query OK, 1 row affected (0.00 sec)
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql             |
| performance_schema |
| sys               |
| testdb1           |
+--------------------+
5 rows in set (0.00 sec)
mysql> 
#在任意其它節(jié)點驗證數(shù)據(jù)是否同步
[root@pxc2 ~]#mysql -uroot -pmagedu
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql             |
| performance_schema |
| sys               |
| testdb1           |
+--------------------+
5 rows in set (0.01 sec)

7 在PXC集群中加入節(jié)點

#在PXC集群中再加一臺新的主機PXC4：10.0.0.37
[root@pxc4 ~]#yum install Percona-XtraDB-Cluster-57 -y
[root@pxc4 ~]#vim /etc/percona-xtradb-cluster.conf.d/wsrep.cnf
[root@pxc4 ~]#grep -Ev "^#|^$" /etc/percona-xtradb-cluster.conf.d/wsrep.cnf 
[mysqld]
wsrep_provider=/usr/lib64/galera3/libgalera_smm.so
wsrep_cluster_address=gcomm://10.0.0.7,10.0.0.17,10.0.0.27,10.0.0.37
binlog_format=ROW
default_storage_engine=InnoDB
wsrep_slave_threads= 8
wsrep_log_conflicts
innodb_autoinc_lock_mode=2
wsrep_node_address=10.0.0.37
wsrep_cluster_name=pxc-cluster
wsrep_node_name=pxc-cluster-node-4
pxc_strict_mode=ENFORCING
wsrep_sst_method=xtrabackup-v2
wsrep_sst_auth="sstuser:s3cretPass"
[root@pxc4 ~]#systemctl start mysql
?
[root@pxc4 ~]#mysql -uroot -pmagedu
Server version: 5.7.27-30-57-log Percona XtraDB Cluster (GPL), Release rel30, 
Revision 
#加入之后變成4
mysql> SHOW STATUS LIKE 'wsrep_cluster_size';
+--------------------+-------+
| Variable_name     | Value |
+--------------------+-------+
| wsrep_cluster_size | 4     |
+--------------------+-------+ 1 row in set (0.00 sec)
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql             |
| performance_schema |
| sys               |
| testdb1           |
| testdb2           |
| testdb3           |
+--------------------+ 8 rows in set (0.00 sec)
#將其它節(jié)點的配置文件加以修改
[root@@pxc1 ~]#vim /etc/percona-xtradb-cluster.conf.d/wsrep.cnf
wsrep_cluster_address=gcomm://10.0.0.7,10.0.0.17,10.0.0.27,10.0.0.37
#同上修改
[root@pxc2 ~]#vim /etc/percona-xtradb-cluster.conf.d/wsrep.cnf
[root@pxc3 ~]#vim /etc/percona-xtradb-cluster.conf.d/wsrep.cnf

5、通過 ansible 部署二進制 mysql 8

[root@DESKTOP-NHT2EP5 ~]#ll mysql-8.0.19-linux-glibc2.12-x86_64.tar
---------- 1 root root 2651712000 Jul 30 16:48 mysql-8.0.19-linux-glibc2.12-x86_64.tar
?
[root@DESKTOP-NHT2EP5 ~]#cat my.cnf
[mysqld]
datadir=/data/mysql
skip_name_resolve=1
socket=/data/mysql/mysql.sock
log-error=/data/mysql/mysql_error.log
pid-file=/data/mysql/mysql.pid
innodb_file_per_table = 1
server_id=10
log-bin
read_only=ON
relay-log=/data/mysql/relay-log
relay-log-index=/data/mysql/relay-log.index
[client]
port=3306
?

[root@DESKTOP-NHT2EP5 ~]#cat mysql.sh
#!/bin/bash
cp -f /usr/local/mysql/support-files/mysql.server /etc/rc.d/init.d/mysqld && chkconfig --add mysqld
/usr/local/mysql/bin/mysqladmin -u root password "123.com"

[root@DESKTOP-NHT2EP5 ~]#cat mysql.yaml
---
- hosts: 100.0.0.10
  remote_user: root
  vars:
    pakgname: mysql-8.0.19-linux-glibc2.12-x86_64.tar
    linkname: mysql-8.0.19-linux-glibc2.12-x86_64
    datadir:  /data/mysql/
  tasks:
    - name: create mysql group
      group: name=mysql gid=306
    - name: create mysql user
      user: name=mysql uid=306 group=mysql shell=/sbin/nologin system=yes create_home=no home={{ datadir }}
    - name: unzip mysqlzip
      unarchive: src=/root/{{ pakgname }} dest=/usr/local/ owner=mysql group=mysql
    - name: unzip directory soft link
      file: src=/usr/local/{{ linkname }} dest=/usr/local/mysql state=link owner=mysql group=mysql
    - name: create data directory
      file: path=/data/mysql state=directory owner=mysql group=mysql
    - name: copy my.cnf file
      copy: src=/root/my.cnf dest=/etc/my.cnf mode=644
    - name: create database bin soft link
       shell: echo PATH='/usr/local/mysql/bin/:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin:/root/bin:/root/bin' >> /etc/profile
      tags: bins
    - name: exec /etc/profile
      shell: source /etc/profile
      tags: exec
    - name: init data directory
      shell: /usr/local/mysql/bin/mysqld --defaults-file=/etc/my.cnf --initialize --user=mysql --initialize-insecure --datadir={{ datadir }}
      tags: init
    - name: copy scripts
      copy: src=/root/mysql.sh dest=/root/ mode=755
    - name: start mysql
      service: name=mysqld state=started enabled=yes
      tags: sh_script
    - name: secure firm,append systemd
      script: chdir=/root mysql.sh
      tags: sh_mysql