部落格作業第五週-mysql主從複製、高可用性、運維自動化ansible
1、如果主節點已經執行了一段時間，且有大量資料時，如何配置並啟動slave節點
實驗環境：master： centos8 10.0.0.68 mariadb-10.3.17
slave： centos8 10.0.0.58 mariadb-10.3.17
兩臺主機分別安裝mariadb
[root@master ~]#yum -y install mariadb-server
[root@slave ~]#yum -y install mariadb-server
master配置
(1)為主節點指定server-id和開啟二進位制日誌，啟動服務
[root@master ~]#vim /etc/my.cnf.d/mariadb-server.cnf
[server]
# this is only for the mysqld standalone daemon
# Settings user and group are ignored when systemd is used.
# If you need to run mysqld under a different user or group,
# customize your systemd unit file for mysqld/mariadb according to the
# instructions in http://fedoraproject.org/wiki/Systemd
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mariadb/mariadb.log
pid-file=/run/mariadb/mariadb.pid
server-id=68 #指定id
log_bin #開啟二進位制日誌
#
# * Galera-related settings
#
[galera]
"/etc/my.cnf.d/mariadb-server.cnf" 56L, 1478C
[root@master ~]#systemctl start mariadb.service
（2）模擬資料庫執行一段時間匯入測試資料庫
[root@master ~]#mysql < hellodb_innodb.sql
**MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| hellodb |
| information_schema |
| mysql |
| performance_schema |
+--------------------+
4 rows in set (0.000 sec)
修改錶行
**MariaDB [hellodb]> update students set age=25,gender='F' where stuid=25;
Query OK, 1 row affected (0.003 sec)
Rows matched: 1 Changed: 1 Warnings: 0
MariaDB [hellodb]> select * from students;
+-------+---------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+---------------+-----+--------+---------+-----------+
| 1 | Shi Zhongyu | 22 | M | 2 | 3 |
| 2 | Shi Potian | 22 | M | 1 | 7 |
| 3 | Xie Yanke | 53 | M | 2 | 16 |
| 4 | Ding Dian | 32 | M | 4 | 4 |
| 5 | Yu Yutong | 26 | M | 3 | 1 |
| 6 | Shi Qing | 46 | M | 5 | NULL |
| 7 | Xi Ren | 19 | F | 3 | NULL |
| 8 | Lin Daiyu | 17 | F | 7 | NULL |
| 9 | Ren Yingying | 20 | F | 6 | NULL |
| 10 | Yue Lingshan | 19 | F | 3 | NULL |
| 11 | Yuan Chengzhi | 23 | M | 6 | NULL |
| 12 | Wen Qingqing | 19 | F | 1 | NULL |
| 13 | Tian Boguang | 33 | M | 2 | NULL |
| 14 | Lu Wushuang | 17 | F | 3 | NULL |
| 15 | Duan Yu | 19 | M | 4 | NULL |
| 16 | Xu Zhu | 21 | M | 1 | NULL |
| 17 | Lin Chong | 25 | M | 4 | NULL |
| 18 | Hua Rong | 23 | M | 7 | NULL |
| 19 | Xue Baochai | 18 | F | 6 | NULL |
| 20 | Diao Chan | 19 | F | 7 | NULL |
| 21 | Huang Yueying | 22 | F | 6 | NULL |
| 22 | Xiao Qiao | 20 | F | 1 | NULL |
| 23 | Ma Chao | 23 | M | 4 | NULL |
| 24 | Xu Xian | 27 | M | NULL | NULL |
| 25 | Sun Dasheng | 25 | F | NULL | NULL |
+-------+---------------+-----+--------+---------+-----------+
25 rows in set (0.000 sec)
**
（3）生產中遇到執行一段時間的資料庫，先檢視當前二進位制日誌的位置（show master logs;），然後對資料庫進行完全備份
**MariaDB [hellodb]> show master logs;
+--------------------+-----------+
| Log_name | File_size |
+--------------------+-----------+
| mariadb-bin.000001 | 28198 |
| mariadb-bin.000002 | 9157 |
+--------------------+-----------+
2 rows in set (0.000 sec)
**
[root@master ~]#mysqldump -A --single-transaction --master-data=1 -F > /backup/all.sql
檢視備份檔案，003日誌389往後即需要同步的內容
[root@master ~]#vim /backup/all.sql
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
--
-- Position to start replication or point-in-time recovery from
--
CHANGE MASTER TO MASTER_LOG_FILE='mariadb-bin.000003', MASTER_LOG_POS=389; #改行記錄備份時二進位制日誌位置
--
-- GTID to start replication from
--
-- SET GLOBAL gtid_slave_pos='0-68-80';
--
-- Current Database: `hellodb`
--
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `hellodb` /*!40100 DEFAULT CHARACTER SET utf8 */;
USE `hellodb`;
--
-- Table structure for table `classes`
--
DROP TABLE IF EXISTS `classes`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `classes` (
`ClassID` tinyint(3) unsigned NOT NULL AUTO_INCREMENT,
`Class` varchar(100) DEFAULT NULL,
`NumOfStu` smallint(5) unsigned DEFAULT NULL,
（4）建立有複製許可權的賬號
MariaDB [(none)]> grant replication slave on *.* to repluser@'10.0.0.%' identified by '123456';
Query OK, 0 rows affected (0.001 sec)
slave配置
（1）修改server-id 啟動服務
[root@slave ~]#vim /etc/my.cnf.d/mariadb-server.cnf
#
# These groups are read by MariaDB server.
# Use it for options that only the server (but not clients) should see
#
#
# These groups are read by MariaDB server.
# Use it for options that only the server (but not clients) should see
#
# See the examples of server my.cnf files in /usr/share/mysql/
#
# this is read by the standalone daemon and embedded servers
[server]
# this is only for the mysqld standalone daemon
# Settings user and group are ignored when systemd is used.
# If you need to run mysqld under a different user or group,
# customize your systemd unit file for mysqld/mariadb according to the
# instructions in http://fedoraproject.org/wiki/Systemd
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mariadb/mariadb.log
pid-file=/run/mariadb/mariadb.pid
server-id=58 #指定id
（2）將主節點的完全備份檔案拷貝到從節點，進行編輯新增資訊，新增資訊分別為：要複製的主節點IP地址、用於複製的使用者名稱、密碼、資料庫埠號
[root@master ~]#scp /backup/all.sql root@10.0.0.58:/root
The authenticity of host '10.0.0.58 (10.0.0.58)' can't be established.
ECDSA key fingerprint is SHA256:2At7o6y8rVcjVAyXEeyB5nXI7CCJvPe+0QYOi8Y7AvU.
Are you sure you want to continue connecting (yes/no/[fingerprint])? yes
Warning: Permanently added '10.0.0.58' (ECDSA) to the list of known hosts.
root@10.0.0.58's password:
all.sql 100% 476KB 40.5MB/s 00:00
[root@slave ~]#vim all.sql
-- MySQL dump 10.17 Distrib 10.3.17-MariaDB, for Linux (x86_64)
--
-- Host: localhost Database:
-- ------------------------------------------------------
-- Server version 10.3.17-MariaDB-log
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
--
-- Position to start replication or point-in-time recovery from
--
CHANGE MASTER TO
MASTER_HOST='10.0.0.68',
MASTER_USER='repluser',
MASTER_PASSWORD='123456',
MASTER_PORT=3306,
MASTER_LOG_FILE='mariadb-bin.000003', MASTER_LOG_POS=389;
（3）將修改好的檔案匯入到資料庫中
[root@slave ~]#mysql < all.sql
（4）檢視複製狀態
MariaDB [hellodb]> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 10.0.0.68
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.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: mariadb-bin.000003
Slave_IO_Running: No
Slave_SQL_Running: No
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 389
Relay_Log_Space: 256
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
（5）主節點伺服器修改資料檢視從節點是否能同步
主節點資料庫修改
MariaDB [hellodb]> update students set age=30,gender='M' where stuid=25;
Query OK, 1 row affected (0.002 sec)
Rows matched: 1 Changed: 1 Warnings: 0
檢視從節點發現資料已經同步
MariaDB [hellodb]> select * from students;
+-------+---------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+---------------+-----+--------+---------+-----------+
| 1 | Shi Zhongyu | 22 | M | 2 | 3 |
| 2 | Shi Potian | 22 | M | 1 | 7 |
| 3 | Xie Yanke | 53 | M | 2 | 16 |
| 4 | Ding Dian | 32 | M | 4 | 4 |
| 5 | Yu Yutong | 26 | M | 3 | 1 |
| 6 | Shi Qing | 46 | M | 5 | NULL |
| 7 | Xi Ren | 19 | F | 3 | NULL |
| 8 | Lin Daiyu | 17 | F | 7 | NULL |
| 9 | Ren Yingying | 20 | F | 6 | NULL |
| 10 | Yue Lingshan | 19 | F | 3 | NULL |
| 11 | Yuan Chengzhi | 23 | M | 6 | NULL |
| 12 | Wen Qingqing | 19 | F | 1 | NULL |
| 13 | Tian Boguang | 33 | M | 2 | NULL |
| 14 | Lu Wushuang | 17 | F | 3 | NULL |
| 15 | Duan Yu | 19 | M | 4 | NULL |
| 16 | Xu Zhu | 21 | M | 1 | NULL |
| 17 | Lin Chong | 25 | M | 4 | NULL |
| 18 | Hua Rong | 23 | M | 7 | NULL |
| 19 | Xue Baochai | 18 | F | 6 | NULL |
| 20 | Diao Chan | 19 | F | 7 | NULL |
| 21 | Huang Yueying | 22 | F | 6 | NULL |
| 22 | Xiao Qiao | 20 | F | 1 | NULL |
| 23 | Ma Chao | 23 | M | 4 | NULL |
| 24 | Xu Xian | 27 | M | NULL | NULL |
| 25 | Sun Dasheng | 30 | M | NULL | NULL |
+-------+---------------+-----+--------+---------+-----------+
25 rows in set (0.001 sec)
2、當master伺服器當機，提升一個slave成為新的maste
如遇主伺服器掛掉，有兩個從節點，檢視從節點複製狀態，哪個值大哪個為新主節點，成為主服務需要開啟二進位制日誌，關閉read-only選項重啟資料庫服務
（1）比較以下值哪個較大，哪個為新主
（2）選定新主伺服器開啟二進位制日誌，關閉read-only，重啟資料庫服務
（3）選定新主伺服器關閉從節點複製的兩個執行緒
（4）選定新主伺服器刪除從節點資訊
（5）在另一臺未選中為主伺服器關閉從節點執行緒，清理從節點資訊，重新建立主節點複製資訊
（6）檢視新主節點當前二進位制日誌位置，在新從節點建立複製
（7）從節點建立複製，啟動執行緒
3、通過 MHA 0.58 搭建一個資料庫叢集結構
一、MHA功能介紹
MHA（Master HA）是一款開源的 MySQL 的高可用程式，它為 MySQL 主從複製架構提供了 automating master failover 功能。MHA 在監控到 master 節點故障時，會提升其中擁有最新資料的 slave 節點成為新的master 節點，在此期間，MHA 會通過於其它從節點獲取額外資訊來避免一致性方面的問題。MHA 還提供了 master 節點的線上切換功能，即按需切換 master/slave 節點。
二、實驗環境
四臺主機
10.0.0.47 centos7 MHA管理端
10.0.0.68 centos8 mysql5.7 master
10.0.0.58 centos8 mysql5.7 slave1
10.0.0.48 centos8 mysql5.7 slave2
三、實驗步驟
（1）管理端10.0.0.47安裝兩個包，提前將rpm包拷貝到伺服器上
[root@Centos7 ~]# yum -y install mha4mysql-node-0.58-0.el7.centos.noarch.rpm
[root@Centos7 ~]# yum -y install mha4mysql-manager-0.58-0.el7.centos.noarch.rpm
（2）所有節點安裝 mha4mysql-node-0.58-0.el7.centos.noarch.rpm包
[root@centos8 ~]#yum -y install mha4mysql-node-0.58-0.el7.centos.noarch.rpm
（3）在所有節點實現相互之間ssh key驗證
[root@MHA-manager ~]# ssh-keygen
Generating public/private rsa key pair.
Enter file in which to save the key (/root/.ssh/id_rsa):
/root/.ssh/id_rsa already exists.
Overwrite (y/n)? y
Enter passphrase (empty for no passphrase):
Enter same passphrase again:
Your identification has been saved in /root/.ssh/id_rsa.
Your public key has been saved in /root/.ssh/id_rsa.pub.
The key fingerprint is:
SHA256:ZcqwWG++dnbfk8EInqznpWoOJTqpCx5n5eJl7BJW03U root@MHA-manager
The key's randomart image is:
+---[RSA 2048]----+
| |
| . E |
| + . + |
| = * + . |
| o.o.S.o o o |
| o+ ooo + . o |
| o.+.O .. . . o|
|. *.* . o+o.+ + |
| . +o. .+=++ .. o|
+----[SHA256]-----+
[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:IHxJ9vqEMp/UwtZ034seLyuNP0D0By5u/O7iBCM2Cik.
ECDSA key fingerprint is MD5:72:b0:4c:7e:54:e3:92:e5:d0:05:ec:57:2c:7b:31:c3.
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.
[root@MHA-manager ~]# rsync -a .ssh 10.0.0.48:/root/
root@MHA-manager ~]# rsync -a .ssh 10.0.0.58:/root/
[root@MHA-manager ~]# rsync -a .ssh 10.0.0.68:/root/
（4）在管理節點建立配置檔案
[root@MHA-manager ~]# mkdir /etc/mastermha
[root@MHA-manager ~]# vim /etc/mastermha/app1.cnf
[server default]
user=mhauser #用於遠端連結MySQL所有節點的使用者，需要有管理員許可權
password=123456
manager_workdir=/data/mastermha/app1/ #該目錄會自動生成
manager_log=/data/mastermha/app1/manager.log
remote_workdir=/data/mastermha/app1/
ssh_user=root #用於實現遠端ssh基於key驗證的連線
repl_user=repluser #主從複製的使用者
repl_password=magedu
ping_interval=1 #健康檢查的時間間隔
master_ip_failover_script=/usr/local/bin/master_ip_failover #切換VIP的perl指令碼
report_script=/usr/local/bin/sendmail.sh #報警指令碼
check_repl_delay=0 #預設slave中從庫落後主庫relaylog超過100M，主庫不會選擇這個從庫為新的master，因為這個從庫>進行恢復需要很長的時間。通過這個引數，mha觸發主從切換的時候會忽略複製的延時，對於設定candidate_master=1的從>庫非常有用，確保這個從庫一定能成為最新的master
master_binlog_dir=/data/mysql/ #指定二進位制日誌存放的目錄，mha4mysql-manager-0.58版本必須指定，之前的不需要
[server1]
hostname=10.0.0.68
candidate_master=1
[server2]
hostname=10.0.0.58
candidate_master=1 #設定為優先候選master，即使不是叢集中事件最新的slave,也會優先當
master
[server3]
hostname=10.0.0.48
在管理節點安裝mailx軟體包，準備.mailrc檔案，準備兩個相關指令碼
[root@Centos7 ~]# yum -y install mailx.x86_64
[root@Centos7 ~]# cat .mailrc
set from=13593386981@139.com
set smtp=smtp.139.com
set smtp-auth-user=13593386981
set smtp-auth-password=GaoJinZhou00
set smtp-auth=login
set ssl-verify=ignore
[root@Centos7 ~]# chmod +x /usr/local/bin/sendmail.sh
[root@Centos7 ~]# cat /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.2';
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";
$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";
}
[root@Centos7 ~]# chmod +x /usr/local/bin/master_ip_failover
（5）實現master節點
#修改配置檔案
[root@master ~]#cat /etc/my.cnf
[mysqld]
server-id=1 #指定固定id
log-bin=/data/mysql/mysql-bin #開啟二進位制日誌
skip_name_resolve=1 #禁止反向解析
datadir=/data/mysql
socket=/data/mysql/mysql.sock
log-error=/data/mysql/mysql.log
pid-file=/data/mysql/mysql.pid
[client]
socket=/data/mysql/mysql.sock
#啟動服務進入資料庫
#檢視當前二進位制日誌位置
mysql> show master logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 154 |
+------------------+-----------+
1 row in set (0.00 sec)
#建立複製所需賬號，建立mha所需賬號
mysql> grant replication slave on *.* to 'repluser'@'10.0.0.%' identified by '123456';
Query OK, 0 rows affected, 1 warning (0.04 sec)
mysql> grant all on *.* to mhauser@'10.0.0.%' identified by '123456';
Query OK, 0 rows affected, 1 warning (0.00 sec)
#配置VIP
[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 fq_codel state UP group default qlen 1000
link/ether 00:0c:29:10:2e:9d brd ff:ff:ff:ff:ff:ff
inet 10.0.0.68/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
inet6 fe80::20c:29ff:fe10:2e9d/64 scope link
valid_lft forever preferred_lft forever
（6）實現slave1
#修改配置檔案
[root@slave1 ~]#cat /etc/my.cnf
[mysqld]
server-id=2
log-bin=/data/mysql/mysql-bin #開啟二進位制日誌
datadir=/data/mysql
read_only
relay_log_purge=0 #不清除中繼日誌
skip_name_resolve=1 #禁止反向解析
socket=/data/mysql/mysql.sock
log-error=/data/mysql/mysql.log
pid-file=/data/mysql/mysql.pid
[client]
socket=/data/mysql/mysql.sock
#進入資料庫執行change master
mysql> CHANGE MASTER TO
-> MASTER_HOST='10.0.0.68',
-> MASTER_USER='repluser',
-> MASTER_PASSWORD='123456',
-> MASTER_PORT=3306,
-> MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=154;
Query OK, 0 rows affected, 2 warnings (0.04 sec)
mysql> start slave;
(7)實現slave2
#修改配置檔案
[root@slave2 ~]#cat /etc/my.cnf
[mysqld]
server-id=3
log-bin=/data/mysql/mysql-bin
read_only
relay_log_purge=0
skip_name_resolve=1
datadir=/data/mysql
socket=/data/mysql/mysql.sock
log-error=/data/mysql/mysql.log
pid-file=/data/mysql/mysql.pid
[client]
socket=/data/mysql/mysql.sock
#進入資料庫執行change master
mysql> CHANGE MASTER TO
-> MASTER_HOST='10.0.0.68',
-> MASTER_USER='repluser',
-> MASTER_PASSWORD='123456',
-> MASTER_PORT=3306,
-> MASTER_LOG_FILE='mysql-bin.000001',
-> MASTER_LOG_POS=154;
Query OK, 0 rows affected, 2 warnings (0.05 sec)
mysql> start slave;
（8）檢查MHA環境
[root@MHA-manager ~]# masterha_check_ssh --conf=/etc/mastermha/app1.cnf
Sat Oct 17 11:03:38 2020 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Sat Oct 17 11:03:38 2020 - [info] Reading application default configuration from /etc/mastermha/app1.cnf..
Sat Oct 17 11:03:38 2020 - [info] Reading server configuration from /etc/mastermha/app1.cnf..
Sat Oct 17 11:03:38 2020 - [info] Starting SSH connection tests..
Sat Oct 17 11:03:39 2020 - [debug]
Sat Oct 17 11:03:38 2020 - [debug] Connecting via SSH from root@10.0.0.68(10.0.0.68:22) to root@10.0.0.58(10.0.0.58:22)..
Sat Oct 17 11:03:39 2020 - [debug] ok.
Sat Oct 17 11:03:39 2020 - [debug] Connecting via SSH from root@10.0.0.68(10.0.0.68:22) to root@10.0.0.48(10.0.0.48:22)..
Sat Oct 17 11:03:39 2020 - [debug] ok.
Sat Oct 17 11:03:40 2020 - [debug]
Sat Oct 17 11:03:38 2020 - [debug] Connecting via SSH from root@10.0.0.58(10.0.0.58:22) to root@10.0.0.68(10.0.0.68:22)..
Warning: Permanently added '10.0.0.68' (ECDSA) to the list of known hosts.
Sat Oct 17 11:03:39 2020 - [debug] ok.
Sat Oct 17 11:03:39 2020 - [debug] Connecting via SSH from root@10.0.0.58(10.0.0.58:22) to root@10.0.0.48(10.0.0.48:22)..
Sat Oct 17 11:03:40 2020 - [debug] ok.
Sat Oct 17 11:03:40 2020 - [debug]
Sat Oct 17 11:03:39 2020 - [debug] Connecting via SSH from root@10.0.0.48(10.0.0.48:22) to root@10.0.0.68(10.0.0.68:22)..
Warning: Permanently added '10.0.0.68' (ECDSA) to the list of known hosts.
Sat Oct 17 11:03:40 2020 - [debug] ok.
Sat Oct 17 11:03:40 2020 - [debug] Connecting via SSH from root@10.0.0.48(10.0.0.48:22) to root@10.0.0.58(10.0.0.58:22)..
Sat Oct 17 11:03:40 2020 - [debug] ok.
Sat Oct 17 11:03:40 2020 - [info] All SSH connection tests passed successfully.
[root@MHA-manager ~]# masterha_check_repl --conf=/etc/mastermha/app1.cnf
Sat Oct 17 11:44:20 2020 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Sat Oct 17 11:44:20 2020 - [info] Reading application default configuration from /etc/mastermha/app1.cnf..
Sat Oct 17 11:44:20 2020 - [info] Reading server configuration from /etc/mastermha/app1.cnf..
Sat Oct 17 11:44:20 2020 - [info] MHA::MasterMonitor version 0.58.
Sat Oct 17 11:44:21 2020 - [info] GTID failover mode = 0
Sat Oct 17 11:44:21 2020 - [info] Dead Servers:
Sat Oct 17 11:44:21 2020 - [info] Alive Servers:
Sat Oct 17 11:44:21 2020 - [info] 10.0.0.68(10.0.0.68:3306)
Sat Oct 17 11:44:21 2020 - [info] 10.0.0.58(10.0.0.58:3306)
Sat Oct 17 11:44:21 2020 - [info] 10.0.0.48(10.0.0.48:3306)
Sat Oct 17 11:44:21 2020 - [info] Alive Slaves:
Sat Oct 17 11:44:21 2020 - [info] 10.0.0.58(10.0.0.58:3306) Version=5.7.29-log (oldest major version between slaves) log-bin:enabled
Sat Oct 17 11:44:21 2020 - [info] Replicating from 10.0.0.68(10.0.0.68:3306)
Sat Oct 17 11:44:21 2020 - [info] Primary candidate for the new Master (candidate_master is set)
Sat Oct 17 11:44:21 2020 - [info] 10.0.0.48(10.0.0.48:3306) Version=5.7.29-log (oldest major version between slaves) log-bin:enabled
Sat Oct 17 11:44:21 2020 - [info] Replicating from 10.0.0.68(10.0.0.68:3306)
Sat Oct 17 11:44:21 2020 - [info] Current Alive Master: 10.0.0.68(10.0.0.68:3306)
Sat Oct 17 11:44:21 2020 - [info] Checking slave configurations..
Sat Oct 17 11:44:21 2020 - [info] Checking replication filtering settings..
Sat Oct 17 11:44:21 2020 - [info] binlog_do_db= , binlog_ignore_db=
Sat Oct 17 11:44:21 2020 - [info] Replication filtering check ok.
Sat Oct 17 11:44:21 2020 - [info] GTID (with auto-pos) is not supported
Sat Oct 17 11:44:21 2020 - [info] Starting SSH connection tests..
Sat Oct 17 11:44:24 2020 - [info] All SSH connection tests passed successfully.
Sat Oct 17 11:44:24 2020 - [info] Checking MHA Node version..
Sat Oct 17 11:44:25 2020 - [info] Version check ok.
Sat Oct 17 11:44:25 2020 - [info] Checking SSH publickey authentication settings on the current master..
Sat Oct 17 11:44:25 2020 - [info] HealthCheck: SSH to 10.0.0.68 is reachable.
Sat Oct 17 11:44:26 2020 - [info] Master MHA Node version is 0.58.
Sat Oct 17 11:44:26 2020 - [info] Checking recovery script configurations on 10.0.0.68(10.0.0.68:3306)..
Sat Oct 17 11:44:26 2020 - [info] Executing command: save_binary_logs --command=test --start_pos=4 --binlog_dir=/data/mysql/ --output_file=/data/mastermha/app1//save_binary_logs_test --manager_version=0.58 --start_file=mysql-bin.000001
Sat Oct 17 11:44:26 2020 - [info] Connecting to root@10.0.0.68(10.0.0.68:22)..
Creating /data/mastermha/app1 if not exists.. Creating directory /data/mastermha/app1.. done.
ok.
Checking output directory is accessible or not..
ok.
Binlog found at /data/mysql/, up to mysql-bin.000001
Sat Oct 17 11:44:26 2020 - [info] Binlog setting check done.
Sat Oct 17 11:44:26 2020 - [info] Checking SSH publickey authentication and checking recovery script configurations on all alive slave servers..
Sat Oct 17 11:44:26 2020 - [info] Executing command : apply_diff_relay_logs --command=test --slave_user='mhauser' --slave_host=10.0.0.58 --slave_ip=10.0.0.58 --slave_port=3306 --workdir=/data/mastermha/app1/ --target_version=5.7.29-log --manager_version=0.58 --relay_log_info=/data/mysql/relay-log.info --relay_dir=/data/mysql/ --slave_pass=xxx
Sat Oct 17 11:44:26 2020 - [info] Connecting to root@10.0.0.58(10.0.0.58:22)..
Creating directory /data/mastermha/app1/.. done.
Checking slave recovery environment settings..
Opening /data/mysql/relay-log.info ... ok.
Relay log found at /data/mysql, up to slave1-relay-bin.000002
Temporary relay log file is /data/mysql/slave1-relay-bin.000002
Checking if super_read_only is defined and turned on.. not present or turned off, ignoring.
Testing mysql connection and privileges..
mysql: [Warning] Using a password on the command line interface can be insecure.
done.
Testing mysqlbinlog output.. done.
Cleaning up test file(s).. done.
Sat Oct 17 11:44:27 2020 - [info] Executing command : apply_diff_relay_logs --command=test --slave_user='mhauser' --slave_host=10.0.0.48 --slave_ip=10.0.0.48 --slave_port=3306 --workdir=/data/mastermha/app1/ --target_version=5.7.29-log --manager_version=0.58 --relay_log_info=/data/mysql/relay-log.info --relay_dir=/data/mysql/ --slave_pass=xxx
Sat Oct 17 11:44:27 2020 - [info] Connecting to root@10.0.0.48(10.0.0.48:22)..
Creating directory /data/mastermha/app1/.. done.
Checking slave recovery environment settings..
Opening /data/mysql/relay-log.info ... ok.
Relay log found at /data/mysql, up to slave2-relay-bin.000002
Temporary relay log file is /data/mysql/slave2-relay-bin.000002
Checking if super_read_only is defined and turned on.. not present or turned off, ignoring.
Testing mysql connection and privileges..
mysql: [Warning] Using a password on the command line interface can be insecure.
done.
Testing mysqlbinlog output.. done.
Cleaning up test file(s).. done.
Sat Oct 17 11:44:27 2020 - [info] Slaves settings check done.
Sat Oct 17 11:44:27 2020 - [info]
10.0.0.68(10.0.0.68:3306) (current master)
+--10.0.0.58(10.0.0.58:3306)
+--10.0.0.48(10.0.0.48:3306)
Sat Oct 17 11:44:27 2020 - [info] Checking replication health on 10.0.0.58..
Sat Oct 17 11:44:27 2020 - [info] ok.
Sat Oct 17 11:44:27 2020 - [info] Checking replication health on 10.0.0.48..
Sat Oct 17 11:44:27 2020 - [info] ok.
Sat Oct 17 11:44:27 2020 - [info] Checking master_ip_failover_script status:
Sat Oct 17 11:44:27 2020 - [info] /usr/local/bin/master_ip_failover --command=status --ssh_user=root --orig_master_host=10.0.0.68 --orig_master_ip=10.0.0.68 --orig_master_port=3306
IN SCRIPT TEST====/sbin/ifconfig eth0:1 down==/sbin/ifconfig eth0:1 10.0.0.100/24;/sbin/arping -I eth0 -c 3 -s 10.0.0.100/24 10.0.0.2 > /dev/null 2&>1===
Checking the Status of the script.. OK
Sat Oct 17 11:44:28 2020 - [info] OK.
Sat Oct 17 11:44:28 2020 - [warning] shutdown_script is not defined.
Sat Oct 17 11:44:28 2020 - [info] Got exit code 0 (Not master dead).
MySQL Replication Health is OK.
檢視MHA狀態
[root@MHA-manager ~]# masterha_check_status --conf=/etc/mastermha/app1.cnf
app1 is stopped(2:NOT_RUNNING).
（9）啟動MHA
[root@MHA-manager ~]# nohup masterha_manager --conf=/etc/mastermha/app1.cnf &> /dev/null
[root@MHA-manager ~]# masterha_check_status --conf=/etc/mastermha/app1.cnf #主伺服器狀態
app1 (pid:24874) is running(0:PING_OK), master:10.0.0.68
（10）模擬故障
[root@master ~]#systemctl stop mysqld.service #停止主伺服器
[root@MHA-manager ~]# nohup masterha_manager --conf=/etc/mastermha/app1.cnf &> /dev/null #當主節點關閉後會自動退出mha
[root@MHA-manager ~]#
[root@MHA-manager ~]# cat /data/mastermha/app1/manager.log #檢視日誌看VIP漂移到哪個節點 新主節點為10.0.0.58
Sat Oct 17 12:38:44 2020 - [info] Slave started.
Sat Oct 17 12:38:45 2020 - [info] End of log messages from 10.0.0.48.
Sat Oct 17 12:38:45 2020 - [info] -- Slave recovery on host 10.0.0.48(10.0.0.48:3306) succeeded.
Sat Oct 17 12:38:45 2020 - [info] All new slave servers recovered successfully.
Sat Oct 17 12:38:45 2020 - [info]
Sat Oct 17 12:38:45 2020 - [info] * Phase 5: New master cleanup phase..
Sat Oct 17 12:38:45 2020 - [info]
Sat Oct 17 12:38:45 2020 - [info] Resetting slave info on the new master..
Sat Oct 17 12:38:45 2020 - [info] 10.0.0.58: Resetting slave info succeeded.
Sat Oct 17 12:38:45 2020 - [info] Master failover to 10.0.0.58(10.0.0.58:3306) completed successfully.
Sat Oct 17 12:38:45 2020 - [info]
----- Failover Report -----
app1: MySQL Master failover 10.0.0.68(10.0.0.68:3306) to 10.0.0.58(10.0.0.58:3306) succeeded
Master 10.0.0.68(10.0.0.68:3306) is down!
Check MHA Manager logs at MHA-manager:/data/mastermha/app1/manager.log for details.
Started automated(non-interactive) failover.
Invalidated master IP address on 10.0.0.68(10.0.0.68:3306)
The latest slave 10.0.0.58(10.0.0.58:3306) has all relay logs for recovery.
Selected 10.0.0.58(10.0.0.58:3306) as a new master.
10.0.0.58(10.0.0.58:3306): OK: Applying all logs succeeded.
10.0.0.58(10.0.0.58:3306): OK: Activated master IP address.
10.0.0.48(10.0.0.48:3306): This host has the latest relay log events.
Generating relay diff files from the latest slave succeeded.
10.0.0.48(10.0.0.48:3306): OK: Applying all logs succeeded. Slave started, replicating from 10.0.0.58(10.0.0.58:3306)
10.0.0.58(10.0.0.58:3306): Resetting slave info succeeded.
Master failover to 10.0.0.58(10.0.0.58:3306) completed successfully.
Sat Oct 17 12:38:45 2020 - [info] Sending mail..
#在10.0.0.58檢視VIP已經漂移到本機
[root@slave1 ~]#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 fq_codel state UP group default qlen 1000
link/ether 00:0c:29:02:7c:51 brd ff:ff:ff:ff:ff:ff
inet 10.0.0.58/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
inet6 fe80::20c:29ff:fe02:7c51/64 scope link noprefixroute
valid_lft forever preferred_lft forever
#如果需要再次開啟mha需要刪除以下檔案
[root@MHA-manager ~]# ls /data/mastermha/app1/app1.failover.complete
收到郵件報警
4、mysql高可用性-Percona XtraDB Cluster(PXC 5.7）
（不支援centos8）
一、實驗環境
pxc1: 10.0.0.27 centos7
pxc2: 10.0.0.37 centos7
pxc3: 10.0.0.47 centos7
pxc4: 10.0.0.57 centos7
二、實驗步驟
（1）安裝 Percona XtraDB Cluster 5.7
#配置清華大學yum源
[15:40:11 root@pxc1 ~]#cat /etc/yum.repos.d/pxc.repo
[perconal]
name=percona_repo
baseurl = https://mirrors.tuna.tsinghua.edu.cn/percona/release/$releasever/RPMS/$basearch
enabled = 1
gpgcheck = 0
#將yum源拷貝到其他兩個節點
[15:43:37 root@pxc1 ~]#scp /etc/yum.repos.d/pxc.repo 10.0.0.37:/etc/yum.repos.d/
[15:43:37 root@pxc1 ~]#scp /etc/yum.repos.d/pxc.repo 10.0.0.47:/etc/yum.repos.d/
#在三個節點都安裝PXC 5.7
[15:43:37 root@pxc1 ~]#yum install Percona-XtraDB-Cluster-57 -y
[15:28:39 root@pxc2 ~]#yum install Percona-XtraDB-Cluster-57 -y
[root@pxc3 ~]# yum install Percona-XtraDB-Cluster-57 -y
（2）在各個節點上分別配置mysql及叢集配置檔案
/etc/my.cnf為主配置檔案，當前版本中，其餘的配置檔案都放在/etc/percona-xtradb-cluster.conf.d目錄裡，包括mysqld.cnf，mysqld_safe.cnf，wsrep.cnf 三個檔案
#修改PXC的配置檔案
[16:11:47 root@pxc1 ~]#cat /etc/percona-xtradb-cluster.conf.d/wsrep.cnf
[mysqld]
# Path to Galera library
wsrep_provider=/usr/lib64/galera3/libgalera_smm.so
# Cluster connection URL contains IPs of nodes
#If no IP is found, this implies that a new cluster needs to be created,
#in order to do that you need to bootstrap this node
wsrep_cluster_address=gcomm://10.0.0.27,10.0.0.37,10.0.0.47 #三個節點的IP
# In order for Galera to work correctly binlog format should be ROW
binlog_format=ROW
# MyISAM storage engine has only experimental support
default_storage_engine=InnoDB
# Slave thread to use
wsrep_slave_threads= 8
wsrep_log_conflicts
# This changes how InnoDB autoincrement locks are managed and is a requirement for Galera
innodb_autoinc_lock_mode=2
# Node IP address
#wsrep_node_address=192.168.70.63
wsrep_node_address=10.0.0.27 #各個節點，指定自已的IP
# Cluster name
wsrep_cluster_name=pxc-magedu-cluster
#If wsrep_node_name is not specified, then system hostname will be used
wsrep_node_name=pxc-cluster-node-1 #各個節點，指定自已節點名稱
#pxc_strict_mode allowed values: DISABLED,PERMISSIVE,ENFORCING,MASTER
pxc_strict_mode=ENFORCING
# SST method
wsrep_sst_method=xtrabackup-v2
#Authentication for SST method
wsrep_sst_auth="gjz:123456" #取消註釋或者改為自己的密碼
[16:09:38 root@pxc2 ~]#cat /etc/percona-xtradb-cluster.conf.d/wsrep.cnf
[mysqld]
# Path to Galera library
wsrep_provider=/usr/lib64/galera3/libgalera_smm.so
# Cluster connection URL contains IPs of nodes
#If no IP is found, this implies that a new cluster needs to be created,
#in order to do that you need to bootstrap this node
wsrep_cluster_address=gcomm://10.0.0.27,10.0.0.37,10.0.0.47
# In order for Galera to work correctly binlog format should be ROW
binlog_format=ROW
# MyISAM storage engine has only experimental support
default_storage_engine=InnoDB
# Slave thread to use
wsrep_slave_threads= 8
wsrep_log_conflicts
# This changes how InnoDB autoincrement locks are managed and is a requirement for Galera
innodb_autoinc_lock_mode=2
# Node IP address
#wsrep_node_address=192.168.70.63
wsrep_node_address=10.0.0.37
# Cluster name
wsrep_cluster_name=pxc-magedu-cluster
#If wsrep_node_name is not specified, then system hostname will be used
wsrep_node_name=pxc-cluster-node-2
#pxc_strict_mode allowed values: DISABLED,PERMISSIVE,ENFORCING,MASTER
pxc_strict_mode=ENFORCING
# SST method
wsrep_sst_method=xtrabackup-v2
#Authentication for SST method
wsrep_sst_auth="gjz:123456"
[root@pxc3 ~]# cat /etc/percona-xtradb-cluster.conf.d/wsrep.cnf
[mysqld]
# Path to Galera library
wsrep_provider=/usr/lib64/galera3/libgalera_smm.so
# Cluster connection URL contains IPs of nodes
#If no IP is found, this implies that a new cluster needs to be created,
#in order to do that you need to bootstrap this node
wsrep_cluster_address=gcomm://10.0.0.27,10.0.0.37,10.0.0.47
# In order for Galera to work correctly binlog format should be ROW
binlog_format=ROW
# MyISAM storage engine has only experimental support
default_storage_engine=InnoDB
# Slave thread to use
wsrep_slave_threads= 8
wsrep_log_conflicts
# This changes how InnoDB autoincrement locks are managed and is a requirement for Galera
innodb_autoinc_lock_mode=2
# Node IP address
#wsrep_node_address=192.168.70.63
wsrep_node_address=10.0.0.47
# Cluster name
wsrep_cluster_name=pxc-magedu-cluster
#If wsrep_node_name is not specified, then system hostname will be used
wsrep_node_name=pxc-cluster-node-3
#pxc_strict_mode allowed values: DISABLED,PERMISSIVE,ENFORCING,MASTER
pxc_strict_mode=ENFORCING
# SST method
wsrep_sst_method=xtrabackup-v2
#Authentication for SST method
wsrep_sst_auth="gjz:123456"
（3）啟動第一個節點
[root@pxc3 ~]# systemctl start mysql@bootstrap.service
#檢視root密碼
[root@pxc3 ~]# grep "temporary password" /var/log/mysqld.log
2020-10-17T08:22:46.574773Z 1 [Note] A temporary password is generated for root@localhost: +?tLc;9/f7Pf
[root@pxc3 ~]# mysql -p'+?tLc;9/f7Pf'
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.31-34-57-log
Copyright (c) 2009-2020 Percona LLC and/or its affiliates
Copyright (c) 2000, 2020, 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>
#修改root密碼
mysql> alter user 'root'@'localhost' identified by 'magedu'
-> ;
Query OK, 0 rows affected (0.00 sec)
#建立相關使用者並授權
mysql> CREATE USER 'gjz'@'localhost' IDENTIFIED BY '123456';
Query OK, 0 rows affected (0.00 sec)
mysql> GRANT RELOAD, LOCK TABLES, PROCESS, REPLICATION CLIENT ON *.* TO 'gjz'@'localhost';
Query OK, 0 rows affected (0.00 sec)
（4）啟動其他節點
[root@pxc1 ~]# systemctl start mysql
[16:15:27 root@pxc2 ~]#systemctl start mysql
（5）檢視叢集狀態，驗證叢集是否成功
#在任意節點，檢視叢集狀態，只要在第一個節點更改資料庫root密碼其他節點均可同步
[16:51:13 root@pxc2 ~]#mysql -uroot -pmagedu
mysql> show variables like 'wsrep_node_name';
+-----------------+--------------------+
| Variable_name | Value |
+-----------------+--------------------+
| wsrep_node_name | pxc-cluster-node-2 |
+-----------------+--------------------+
1 row in set (0.00 sec)
mysql> show variables like 'wsrep_node_address';
+--------------------+-----------+
| Variable_name | Value |
+--------------------+-----------+
| wsrep_node_address | 10.0.0.37 |
+--------------------+-----------+
1 row in set (0.00 sec)
#在任意節點檢視資料庫
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)
#在任意節點建立資料庫
#節點2建立資料庫
mysql> create database testdb1;
Query OK, 1 row affected (0.00 sec)
#節點1查詢
[root@pxc1 ~]# mysql -uroot -pmagedu
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 11
Server version: 5.7.31-34-57-log Percona XtraDB Cluster (GPL), Release rel34, Revision 7359e4f, WSREP version 31.45, wsrep_31.45
Copyright (c) 2009-2020 Percona LLC and/or its affiliates
Copyright (c) 2000, 2020, 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 databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| testdb1 |
+--------------------+
5 rows in set (0.00 sec)
#同時在三個節點建立資料庫只有一個成功，無需擔心複製衝突
（6）pxc叢集加入新節點
#在PXC叢集中再加一臺新的主機PXC4：10.0.0.57
[17:25:11 root@pxc4 ~]#yum install Percona-XtraDB-Cluster-57 -y
[17:30:45 root@pxc4 ~]#cat /etc/percona-xtradb-cluster.conf.d/wsrep.cnf
[mysqld]
# Path to Galera library
wsrep_provider=/usr/lib64/galera3/libgalera_smm.so
# Cluster connection URL contains IPs of nodes
#If no IP is found, this implies that a new cluster needs to be created,
#in order to do that you need to bootstrap this node
wsrep_cluster_address=gcomm://10.0.0.27,10.0.0.37,10.0.0.47,10.0.0.57
# In order for Galera to work correctly binlog format should be ROW
binlog_format=ROW
# MyISAM storage engine has only experimental support
default_storage_engine=InnoDB
# Slave thread to use
wsrep_slave_threads= 8
wsrep_log_conflicts
# This changes how InnoDB autoincrement locks are managed and is a requirement for Galera
innodb_autoinc_lock_mode=2
# Node IP address
#wsrep_node_address=192.168.70.63
wsrep_node_address=10.0.0.57
# Cluster name
wsrep_cluster_name=pxc-magedu-cluster
#If wsrep_node_name is not specified, then system hostname will be used
wsrep_node_name=pxc-cluster-node-4
#pxc_strict_mode allowed values: DISABLED,PERMISSIVE,ENFORCING,MASTER
pxc_strict_mode=ENFORCING
# SST method
wsrep_sst_method=xtrabackup-v2
#Authentication for SST method
wsrep_sst_auth="gjz:123456"
啟動服務
[17:30:54 root@pxc4 ~]#systemctl start mysql
[17:32:08 root@pxc4 ~]#mysql -uroot -pmagedu
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 11
Server version: 5.7.31-34-57-log Percona XtraDB Cluster (GPL), Release rel34, Revision 7359e4f, WSREP version 31.45, wsrep_31.45
Copyright (c) 2009-2020 Percona LLC and/or its affiliates
Copyright (c) 2000, 2020, 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 STATUS LIKE 'wsrep_cluster_size';
+--------------------+-------+
| Variable_name | Value |
+--------------------+-------+
| wsrep_cluster_size | 4 |
+--------------------+-------+
1 row in set (0.01 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| testdb1 |
+--------------------+
5 rows in set (0.00 sec)
（7）在pxc叢集中修復故障節點
#在任意節點停止服務
[17:43:45 root@pxc4 ~]#systemctl stop mysql.service
#在其它任意節點檢視wsrep_cluster_size變數少了一個節點
mysql> SHOW STATUS LIKE 'wsrep_cluster_size';
+--------------------+-------+
| Variable_name | Value |
+--------------------+-------+
| wsrep_cluster_size | 3 |
+--------------------+-------+
1 row in set (0.01 sec)
mysql> create database testdb2;
Query OK, 1 row affected (0.01 sec)
#在其它任意節點可看到資料已同步
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| testdb1 |
| testdb2 |
+--------------------+
6 rows in set (0.00 sec)
#將停止的節點啟動，發現資料已經同步
[17:48:45 root@pxc4 ~]#systemctl start mysql
[17:48:45 root@pxc4 ~]#mysql -uroot -pmagedu
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.31-34-57-log Percona XtraDB Cluster (GPL), Release rel34, Revision 7359e4f, WSREP version 31.45, wsrep_31.45
Copyright (c) 2009-2020 Percona LLC and/or its affiliates
Copyright (c) 2000, 2020, 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 databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| testdb1 |
| testdb2 |
+--------------------+
6 rows in set (0.00 sec)
mysql>
5、通過 ansible 部署二進位制 mysql 8
實驗環境：
10.0.0.68 ansible主機
10.0.0.58
10.0.0.48
ansible管理首先要基於key驗證，可通過以下指令碼實現
#!/bin/bash
IPLIST="
10.0.0.68
10.0.0.58
10.0.0.48"
rpm -q sshpass &> /dev/null || yum -y install sshpass
[-f /root/.ssh/id_rsa] || ssh-keygen -f /root/.ssh/id_rsa -P ''
export SSHPASS=123456
for IP in $IPLIST;do
sshpass -e ssh-copy-id $IP
done
[15:11:32 root@localhost ~]#vi mysql8.0-install.yml
---
- hosts: mysql
remote_user: root
gather_facts: no
tasks:
- name: add group mysql
group: name=mysql state=present
- name: add user mysql
user: name=mysql state=present group=mysql
- name: Install packeges
yum: name=libaio,numactl-libs,libaio,ncurses-compat-libs
- name: decompression
unarchive: src=/root/mysql-8.0.21-linux-glibc2.12-x86_64.tar.xz dest=/usr/local
- name: create symbolic link
file: src=/usr/local/mysql-8.0.21-linux-glibc2.12-x86_64 dest=/usr/local/mysql state=link
- name: modify path
shell: echo 'PATH=/usr/local/mysql/bin/:$PATH' > /etc/profile.d/mysql.sh
- name: modify path
shell: source /etc/profile.d/mysql.sh
- name: create my.cnf
shell: echo -e '[mysqld]\nserver-id=1\nlog-bin\ndatadir=/data/mysql\nsocket=/data/mysql/mysql.sock\nlog-error=/data/mysql/mysql.log\npid-file=/data/mysql/mysql.pid\n[client]\nsocket=/data/mysql/mysql.sock'> /etc/my.cnf
- name: install mysql
shell: mysqld --initialize-insecure --user=mysql --datadir=/data/mysql
- name: creat mysqld
shell: cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld
- name: set auto run
shell: chkconfig --add mysqld
- name: start mysql
service: name=mysqld state=started
- name: modify password
shell: mysql -e "alter user root@localhost identified by '123456'"
相關文章
- 第五週作業mysql高可用+ansibleMySql
- MySQL運維2-主從複製MySql運維
- 自動化運維 Ansible運維
- ansible自動化運維工具運維
- MYSQL主從複製製作配置方案MySql
- ansible自動化運維入門運維
- Redis日常運維-02主從複製Redis運維
- 自動化運維工具Ansible介紹運維
- ansible自動化運維資料庫運維資料庫
- 【Mysql】MySQL 主主複製 + LVS + Keepalived 實現 MySQL 高可用性MySql
- MySQL 的主從複製（高階篇）MySql
- MongoDB日常運維-03主從複製搭建MongoDB運維
- MySQL主從複製與主主複製MySql
- MySQL的主從複製與MySQL的主主複製MySql
- 自動化運維工具ansible的實踐運維
- 自動化運維工具——ansible詳解（一）運維
- 自動化運維工具——ansible詳解（二）運維
- 自動化運維工具Ansible詳細部署運維
- mysql5.7主從複製，主主複製MySql
- MySQL主從複製、半同步複製和主主複製MySql
- mysql複製--主從複製配置MySql
- MySQL 主從複製MySql
- 【MySql】主從複製MySql
- MySQL主從複製MySql
- MySQL主從複製、半同步複製和主主複製概述MySql
- Python+Django+Ansible Playbook自動化運維PythonDjango運維
- 自動化運維利器Ansible要點彙總運維
- [Linux]Ansible自動化運維② - 工具與模組Linux運維
- MySQL主從複製_複製過濾MySql
- MySQL的主從複製、半同步複製、主主複製詳解MySql
- 配置mysql5.5主從複製、半同步複製、主主複製MySql
- MySQL主從複製原理MySql
- MySQL的主從複製MySql
- mysql--主從複製MySql
- mysql主從複製搭建MySql
- MySql 主從複製配置MySql
- MySQL主從複製配置MySql
- mysql-inception自動化運維MySql運維