一、如果主节点已经运行了一段时间，且有大量数据时，如何配置并启动slave节点（写出操作步骤）
- 首先备份master节点数据
- 恢复数据到slave节点
- 复制起始位置为主节点备份时的二进制日志文件
#在主节点做完全备份
[root@CentOS8 ~]# mysqldump -A -F --single-transaction --master-data=1 > /backup/fullback_`date +%F_%T`.sql
[root@CentOS8 ~]# ll /backup/
total 2988
-rw-r--r-- 1 root root 3056439 Oct 17 09:43 fullback_2020-10-17_09:43:38.sql
#从主节点拷贝备份文件到slave节点
[root@CentOS8 ~]# scp /backup/fullback_2020-10-17_09\:43\:38.sql 10.0.0.28:/data/
#将完全备份还原到slave节点
#在slave节点安装mysql
[root@CentOS8 ~]# dnf -y install mariadb-server
添加修改配置文件
[root@CentOS8 ~]# vim /etc/my.cnf.d/mariadb-server.cnf
[mysqld]
server-id=11
read-only
#启动服务
[root@CentOS8 ~]# systemctl start mariadb
#检查完全备份的二进制日志文件及起始POS
[root@CentOS8 ~]# grep '^CHANGE MASTER' /data/fullback_2020-10-17_09\:43\:38.sql
CHANGE MASTER TO MASTER_LOG_FILE='mariadb-bin.000003', MASTER_LOG_POS=389;
#导入完全备份数据到slave节点
[root@CentOS8 ~]# mysql < /data/fullback_2020-10-17_09\:43\:38.sql
#配置slave节点，从完全备份的位置之后开始同步复制
[root@CentOS8 ~]# mysql
MariaDB [(none)]> change master to
-> master_host='10.0.0.8',
-> master_user='repluser',
-> master_password='123456',
-> master_port=3306,
-> MASTER_LOG_FILE='mariadb-bin.000003',
-> MASTER_LOG_POS=389;
Query OK, 0 rows affected (0.003 sec)
#开启slave节点复制
MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.001 sec)
二、当master服务器宕机，提升一个slave成为新的master（写出操作步骤）
#首先查询slave服务器的relay-log.info信息，找到哪个服务器的数据库是最新，让它成为新master
[root@CentOS8 ~]# cat /var/lib/mysql/relay-log.info
./mariadb-relay-bin.000005
27378298
mariadb-bin.000003
27378179
#新选择的master修改配置文件，关闭read-only配置，并启用二进制日志
[root@slave1 ~]#vim /etc/my.cnf.d/mariadb-server.cnf
[mysqld]
server-id=18
read-only=OFF
log-bin
#重启服务，生成二进制日志文件
[root@CentOS8 ~]# systemctl restart mariadb
MariaDB [(none)]> show master logs;
+--------------------+-----------+
| Log_name | File_size |
+--------------------+-----------+
| mariadb-bin.000001 | 379 |
| mariadb-bin.000002 | 375 |
+--------------------+-----------+
#在新选择的master服务器上清除旧的master信息
MariaDB [(none)]> stop slave;
MariaDB [(none)]> reset slave all;
#在新选择的master服务器上做完全备份
[root@CentOS8 ~]# mysqldump -A -F --single-transaction --master-data=1 > backup.sql
#如果旧的master服务器没有完全宕机，还能提取出二进制日志文件，那么分析旧的master 的二进制日志，将未同步到至新master的二进制日志导出来，恢复到新master，尽可能的恢复数据
#将新选择的master服务器上做的完全备份拷贝到其他slave服务器上
[root@CentOS8 ~]# scp backup.sql 10.0.0.28:
#在其它所有slave上重新还原数据库，指向新的master
[root@CentOS8 ~]#vim backup.sql #编辑完全备份文件，添加master信息
CHANGE MASTER TO
MASTER_HOST='10.0.0.18',
MASTER_USER='repluser',
MASTER_PASSWORD='123456',
MASTER_PORT=3306,
MASTER_LOG_FILE=' mariadb-bin.000002 ',
MASTER_LOG_POS=375
MariaDB [hellodb]>stop slave;
MariaDB [hellodb]>reset slave all;
MariaDB [hellodb]>set sql_log_bin=off;
MariaDB [hellodb]>source backup.sql;
MariaDB [hellodb]>set sql_log_bin=on;
MariaDB [hellodb]>start slave;
三、通过 MHA 0.58 搭建一个数据库集群结构
环境：4台主机
10.0.0.7 CentOS7 MHA管理端 mysql5.7
10.0.0.8 CentOS8 Master mysql8.0
10.0.0.18 CentOS8 Slave1 mysql8.0
10.0.0.28 CentOS8 Slave2 mysql8.0
1.安装MHA 0.58
说明: mha4mysql-manager-0.56-0.el6.noarch.rpm 不支持CentOS 8，只支持CentOS7 以下版本
说明: mha4mysql-manager-0.58-0.el7.centos.noarch.rpm ,支持MySQL 5.7 ,但和CentOS8版本上的Mariadb -10.3.17不兼容
共两个包：
mha4mysql-manager
mha4mysql-nod
1.1 在管理节点安装这两个包
[root@mha-manager ~]#yum -y install mha4mysql-manager-0.58-0.el7.centos.noarch.rpm
[root@mha-manager ~]#yum -y install mha4mysql-node-0.58-0.el7.centos.noarch
1.2 在3台mysql服务器上安装mha4mysql-node包，此包支持CentOS 8/7/6
[root@CentOS8 ~]# yum -y install mha4mysql-node-0.58-0.el7.centos.noarch.rpm
2. 在所有节点实现相互之间ssh key验证
[root@mha-manager ~]#ssh-keygen
[root@mha-manager ~]#ssh-copy-id 10.0.0.7
[root@mha-manager ~]#rsync -av .ssh 10.0.0.8:/root/ #保留属性，或用scp -p
[root@mha-manager ~]#rsync -av .ssh 10.0.0.18:/root/
[root@mha-manager ~]#rsync -av .ssh 10.0.0.28:/root/
3. 在管理节点建立配置文件和相关脚本
3.1 配置文件
[root@mha-manager ~]#mkdir /etc/mastermha/
[root@mha-manager ~]#vim /etc/mastermha/app1.cnf
[server default]
user=mhauser #管理 MHA 用户的名称
password=123456 #管理 MHA 用户的密码
manager_workdir=/data/mastermha/app1/ #MHA manager 使用的工作目录
manager_log=/data/mastermha/app1/manager.log #MHA manager 生成日志的保存路径及文件名
remote_workdir=/data/mastermha/app1/ #每一个 MHA node 保存从 master 节点复制而来的二进制日志的工作目录
ssh_user=root MHA manager 和 MHA node 通过此用户连接至 Mysql 所在的主机
repl_user=repluser #主从复制时使用的用户名称
repl_password=123456 主从复制时使用的用户密码
ping_interval=1 MHA manager ping master 节点的时间间隔，单位为秒，当连续 3 次 ping 失败后， MHA manager 认为此 master 节点宕机
master_ip_failover_script=/usr/local/bin/master_ip_failover #故障转移脚本
report_script=/usr/local/bin/sendmail.sh #以邮件告警的方式来发送 failover 报告脚本
check_repl_delay=0 #默认情况下如果一个 slave 节点落后 master 节点 100M 的中继日志， MHA 将不会选择该 slave 节点作为一个新的 master 节点，因为对于这个 slave 节点的恢复需要花费很长时间，通过设置 check_repl_delay=0 ， MHA 在选择一个新的 master 节点时将会忽略复制延时，此参数对于设置了 candidate_master=1 的主机非常有用，因为此备选 master 节点在切换过程中一定是新的 master 节点
master_binlog_dir=/data/mysql/ #master 节点生成二进制日志的目录
[server1]
hostname=10.0.0.8
candidate_master=1 #从不同的 slave 节点中，提升一个可靠的节点作为新的 master 节点，如果此值设置为 1 ，此 slave 节点会优先成为新的 master 节点，即使这个 slave 节点的事件不是最新的
[server2]
hostname=10.0.0.18
candidate_master=1
[server3]
hostname=10.0.0.28
3.2 相关脚本
#报警邮件脚本
[root@mha-manager ~]#cat /usr/local/bin/sendmail.shecho "MySQL is down" | mail -s "MHA Warning" root@xxx.com
[root@mha-manager ~]#chmod +x /usr/local/bin/sendmail.
#故障主备切换脚本
#!/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';#设置Virtual IP
my $gateway = '10.0.0.2';#网关Gateway IP
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 {
"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";
}
4.配置master 服务器
[root@master ~]# yum -y install mysql-server
[root@master ~]#mkdir /data/mysql/
[root@master ~]#chown mysql.mysql /data/mysql
#准备配置文件
[root@master ~]#vim /etc/my.cnf
[mysqld]
server_id=1
log-bin=/data/mysql/mysql-bin
skip_name_resolve=1 #禁止反向解析
general_log #非必须
mysql> show master logs;
+------------------+-----------+-----------+
| Log_name | File_size | Encrypted |
+------------------+-----------+-----------+
| mysql-bin.000001 | 179 | No |
| mysql-bin.000002 | 156 | No |
+------------------+-----------+-----------+
#准备相关账户及权限
mysql> create user repluser@'10.0.0.%' identified by '123456';
Query OK, 0 rows affected (0.00 sec)
mysql> grant replication slave on *.* to repluser@'10.0.0.%';
Query OK, 0 rows affected (0.01 sec)
mysql> create user mhauser@'10.0.0.%' identified by '123456';
Query OK, 0 rows affected (0.01 sec)
mysql> grant all on *.* to mhauser@'10.0.0.%';
Query OK, 0 rows affected (0.01 sec)
#配置Virtual IP，在启动MHA之前未添加，启动之后master也能获取，VIP地址已在failover脚本中定义
[root@master ~]#ifconfig eth0:1 10.0.0.100/24
5.配置slave服务器（需在两个slave服务器上分别配置）
[root@slave1 ~]# yum -y install mysql-server
[root@slave1 ~]#mkdir /data/mysql
[root@slave1 ~]#chown mysql.mysql /data/mysql/
[root@slave1 ~]#vim /etc/my.cnf
[mysqld]
server_id=2 #不同节点此值不同
log-bin=/data/mysql/mysql-bin
read_only
relay_log_purge=0 #表示在默认情况下， slave 节点的中继日志会在 SQL 线程执行完毕后被自动删除，但是在 MHA 环境中，这些中继日志在恢复其它 slave 节点时可能会被用到，因此需要禁用中继日志的自动删除功能。
skip_name_resolve=1
mysql> CHANGE MASTER TO MASTER_HOST='10.0.0.8', MASTER_USER='repluser', MASTER_PASSWORD='123456', MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=156;
mysql> start slave;
6.检查MHA环境
#检查环境
[root@mha-manager ~]#masterha_check_ssh --conf=/etc/mastermha/app1.cnf
[root@mha-manager ~]#masterha_check_repl --conf=/etc/mastermha/app1.cnf
#查看状态
[root@mha-manager ~]#masterha_check_status --conf=/etc/mastermha/app1.cnf
注意：MHA 从 0.58 版本开始增加了 super_read_only 功能，而 MariaDB-10.3.17 中没有 super_read_only 这个变量！在masterha_check_repl时报错如下，因为当时用了mysql服务器安装的是MariaDB-10.3.17，后来更换为mysql 8.0不再报错！
Checking if super_read_only is defined and turned on..DBD::mysql::st execute failed: Unknown system variable 'super_read_only' at /usr/share/perl5/vendor_perl/MHA/SlaveUtil.pm line 245.
Tue Oct 20 14:42:10 2020 - [error][/usr/share/perl5/vendor_perl/MHA/MasterMonitor.pm, ln208] Slaves settings check failed!
Tue Oct 20 14:42:10 2020 - [error][/usr/share/perl5/vendor_perl/MHA/MasterMonitor.pm, ln416] Slave configuration failed.
Tue Oct 20 14:42:10 2020 - [error][/usr/share/perl5/vendor_perl/MHA/MasterMonitor.pm, ln427] Error happened on checking configurations. at /usr/bin/masterha_check_repl line 48.
Tue Oct 20 14:42:10 2020 - [error][/usr/share/perl5/vendor_perl/MHA/MasterMonitor.pm, ln525] Error happened on monitoring servers.
7.在管理节点上启动MHA
#开启MHA，默认前台运行
[root@mha-manager ~]#nohup masterha_manager --conf=/etc/mastermha/app1.cnf &> /dev/null
[root@mha-manager ~]#masterha_manager --conf=/etc/mastermha/app1.cnf
Tue Oct 20 15:55:34 2020 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Tue Oct 20 15:55:34 2020 - [info] Reading application default configuration from /etc/mastermha/app1.cnf..
Tue Oct 20 15:55:34 2020 - [info] Reading server configuration from /etc/mastermha/app1.cnf..
#查看状态
[root@mha-manager ~]# masterha_check_status --conf=/etc/mastermha/app1.cnf
app1 (pid:25452) is running(0:PING_OK), master:10.0.0.8
#可在master服务器上看到每秒一次的健康性检查
[root@master ~]# tail -f /var/lib/mysql/CentOS8.log
2020-10-20T08:01:00.861407Z 24 Query SELECT 1 As Value
2020-10-20T08:01:01.861432Z 24 Query SELECT 1 As Value
2020-10-20T08:01:02.863248Z 24 Query SELECT 1 As Value
2020-10-20T08:01:03.863969Z 24 Query SELECT 1 As Value
#可在master服务器上看到Virtual IP地址
[root@master ~]# ip a
2: eth0: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc fq_codel state UP group default qlen 1000
link/ether 00:0c:29:41:a6:5d 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/8 brd 10.255.255.255 scope global eth0:1
valid_lft forever preferred_lft forever
8.模拟故障
#在master服务器停止mysql服务
[root@master ~]# systemctl stop mysqld
#管理节点MHA自动退出
[root@mha-manager ~]# masterha_manager --conf=/etc/mastermha/app1.cnf
Tue Oct 20 15:55:34 2020 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Tue Oct 20 15:55:34 2020 - [info] Reading application default configuration from /etc/mastermha/app1.cnf..
Tue Oct 20 15:55:34 2020 - [info] Reading server configuration from /etc/mastermha/app1.cnf..
Creating /data/mastermha/app1 if not exists.. ok.
Checking output directory is accessible or not..
ok.
Binlog found at /data/mysql/, up to mysql-bin.000002
Tue Oct 20 16:04:48 2020 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Tue Oct 20 16:04:48 2020 - [info] Reading application default configuration from /etc/mastermha/app1.cnf..
Tue Oct 20 16:04:48 2020 - [info] Reading server configuration from /etc/mastermha/app1.cnf..
[root@mha-manager ~]# masterha_check_status --conf=/etc/mastermha/app1.cnf
app1 is stopped(2:NOT_RUNNING).
#排错日志显示VIP切换成功
[root@mha-manager ~]# cat /data/mastermha/app1/manager.log
----- Failover Report -----
app1: MySQL Master failover 10.0.0.8(10.0.0.8:3306) to 10.0.0.18(10.0.0.18:3306) succeeded
Master 10.0.0.8(10.0.0.8:3306) is down!
Check MHA Manager logs at CentOS7:/data/mastermha/app1/manager.log for details.
Started automated(non-interactive) failover.
Invalidated master IP address on 10.0.0.8(10.0.0.8:3306)
The latest slave 10.0.0.18(10.0.0.18:3306) has all relay logs for recovery.
Selected 10.0.0.18(10.0.0.18:3306) as a new master.
10.0.0.18(10.0.0.18:3306): OK: Applying all logs succeeded.
10.0.0.18(10.0.0.18:3306): OK: Activated master IP address.
10.0.0.28(10.0.0.28:3306): This host has the latest relay log events.
Generating relay diff files from the latest slave succeeded.
10.0.0.28(10.0.0.28:3306): OK: Applying all logs succeeded. Slave started, replicating from 10.0.0.18(10.0.0.18:3306)
10.0.0.18(10.0.0.18:3306): Resetting slave info succeeded.
Master failover to 10.0.0.18(10.0.0.18:3306) completed successfully.
Tue Oct 20 16:04:56 2020 - [info] Sending mail..
# VIP已转移至slave1服务器上
[root@slave1 ~]# ip a
2: eth0: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc fq_codel state UP group default qlen 1000
link/ether 00:0c:29:18:44:08 brd ff:ff:ff:ff:ff:ff
inet 10.0.0.18/24 brd 10.0.0.255 scope global noprefixroute eth0
valid_lft forever preferred_lft forever
inet 10.0.0.100/8 brd 10.255.255.255 scope global eth0:1
valid_lft forever preferred_lft forever
inet6 fe80::20c:29ff:fe18:4408/64 scope link
valid_lft forever preferred_lft forever
# slave1服务器上查看read_only已变为OFF
mysql> show global variables like 'read_only';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| read_only | OFF |
+---------------+-------+
# slave1服务器上查看slave节点为server id=3
mysql> show slave hosts;
+-----------+------+------+-----------+--------------------------------------+
| Server_id | Host | Port | Master_id | Slave_UUID |
+-----------+------+------+-----------+--------------------------------------+
| 3 | | 3306 | 2 | 5ce3037d-12a8-11eb-87f1-000c29ded6af |
# slave2服务器上查看slave状态host地址已指向slave1的地址
mysql> 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: mysql-bin.000001
Read_Master_Log_Pos: 156
Relay_Log_File: CentOS8-relay-bin.000002
Relay_Log_Pos: 324
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
#查看告警邮件：
From root@CentOS7.localdomain Tue Oct 20 17:33:55 2020
Return-Path: <root@CentOS7.localdomain>
Date: Tue, 20 Oct 2020 17:33:55 +0800
To: 343882044@qq.com
Subject: MHA Warning
User-Agent: Heirloom mailx 12.5 7/5/10
Content-Type: text/plain; charset=us-ascii
From: root@CentOS7.localdomain (root)
MySQL is down
9.修复故障的master服务器
#master节点添加下面两个参数到配置文件，并启动mysqld服务
[root@master ~]# vim /etc/my.cnf
read_only
relay_log_purge=0
[root@master ~]# systemctl start mysqld
#在管理节点查看复制的起始位置
[root@mha-manager ~]# grep -i "All other slaves should start" /data/mastermha/app1/manager.log
Tue Oct 20 17:33:49 2020 - [info] All other slaves should start replication from here. Statement should be: CHANGE MASTER TO MASTER_HOST='10.0.0.18', MASTER_PORT=3306, MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=156, MASTER_USER='repluser', MASTER_PASSWORD='xxx';
#master节点复制指向slave1（新的master）节点，并启动复制线程
mysql> CHANGE MASTER TO MASTER_HOST='10.0.0.18', MASTER_USER='repluser', MASTER_PASSWORD='123456', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=156;
mysql> start slave;
#master节点查看slave状态已指向slave1（新的master）节点
mysql> 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: mysql-bin.000001
Read_Master_Log_Pos: 156
Relay_Log_File: CentOS8-relay-bin.000002
Relay_Log_Pos: 324
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
#在管理节点上删除 app1.failover.complete 文件
[root@mha-manager ~]# ll /data/mastermha/app1/app1.failover.complete
-rw-r--r-- 1 root root 0 Oct 20 17:33 /data/mastermha/app1/app1.failover.complete
You have mail in /var/spool/mail/root
[root@mha-manager ~]# rm -f /data/mastermha/app1/app1.failover.complete
#重新启动MHA服务
[root@mha-manager ~]# masterha_manager --conf=/etc/mastermha/app1.cnf
Tue Oct 20 18:11:36 2020 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Tue Oct 20 18:11:36 2020 - [info] Reading application default configuration from /etc/mastermha/app1.cnf..
Tue Oct 20 18:11:36 2020 - [info] Reading server configuration from /etc/mastermha/app1.cnf..
[root@mha-manager ~]# masterha_check_status --conf=/etc/mastermha/app1.cnf
app1 (pid:26408) is running(0:PING_OK), master:10.0.0.18
四、实战案例：Percona XtraDB Cluster(PXC 5.7）
1.准备实验环境
四台主机（centos7）
pxc1: 10.0.0.7
pxc2: 10.0.0.17
pxc3: 10.0.0.27
pxc4: 10.0.0.37
注意：PXC5.7目前不支持CentOS 8，主机需要关闭防火墙和SELinux并保证时间同步，如果已经安装MySQL，必须卸载
2，安装 Percona XtraDB Cluster 5.7
#配置yum源，使用国内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
#将yum配置文件拷贝到pxc2,pxc3上
[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/
#在3个节点都安装pxc
[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. 在各个节点上分别配置mysql集群配置文件
/etc/my.cnf为主配置文件，当前版本中，其余的配置文件都放在/etc/percona-xtradb-cluster.conf.d目录里，包括mysqld.cnf，mysqld_safe.cnf，wsrep.cnf 三个文件
[root@pxc1 # ll /etc/percona-xtradb-cluster.conf.d/
total 12
-rw-r--r-- 1 root root 381 Oct 9 03:01 mysqld.cnf
-rw-r--r-- 1 root root 440 Oct 9 03:01 mysqld_safe.cnf
-rw-r--r-- 1 root root 1066 Oct 9 03:01 wsrep.cnf
#修改配置wsrep.cnf文件
[root@pxc1]# vim /etc/percona-xtradb-cluster.conf.d/wsrep.cnf
[root@pxc1]# grep -Ev "^#|^$" /etc/percona-xtradb-cluster.conf.d/wsrep.cnf
wsrep_provider=/usr/lib64/galera3/libgalera_smm.so
wsrep_cluster_address=gcomm://10.0.0.7,10.0.0.17,10.0.0.27 #添加3个节点的IP
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 #指定各个节点自己的IP
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集群中第一个节点
#启动PXC的第一个节点
[root@pxc1 ~]# systemctl start mysql@bootstrap.service
[root@pxc1 ~]# ss -nlt
State Recv-Q Send-Q Local Address:Port Peer Address:Port
LISTEN 0 128 *:4567 *:*
LISTEN 0 100 127.0.0.1:25 *:*
LISTEN 0 128 *:22 *:*
LISTEN 0 100 [::1]:25 [::]:*
LISTEN 0 80 [::]:3306 [::]:*
LISTEN 0 128 [::]:22 [::]:*
#查看mysql的登录密码
[root@pxc1 ~]# grep "temporary password" /var/log/mysqld.log
2020-10-22T02:56:24.482862Z 1 [Note] A temporary password is generated for root@localhost: s%dK%rrv%5Gf
#登录并修改mysql的密码
[root@pxc1 ~]# mysql -uroot -p's%dK%rrv%5Gf'
mysql> alter user root@localhost identified by '123456';
#创建用于数据同步的账户并授权
mysql> create user sstuser@localhost identified by 's3cretPass';
mysql> grant reload,lock tables,process,replication client on *.* to sstuser@localhost;
#查看相关变量及状态
mysql> show variables like 'wsrep%'\G
mysql> show status like 'wsrep%'\G
Variable_name: wsrep_gcomm_uuid
Value: 30827c2b-1412-11eb-a6ae-7a25be848952
Variable_name: wsrep_cluster_conf_id
Value: 1
Variable_name: wsrep_cluster_size
Value: 1
Variable_name: wsrep_cluster_status
Value: Primary
Variable_name: wsrep_connected
Value: ON
Variable_name: wsrep_ready
Value: ON
Variable_name: wsrep_local_state_comment
Value: Synced
5.启动PXC集群中其它节点
[root@pxc2 ~]# systemctl start mysql
[root@pxc3 ~]# systemctl start mysql
6. 查看集群状态，验证集群是否成功
#在任意节点，查看集群状态
[root@pxc3 ~]# mysql -uroot -p123456
mysql> SHOW VARIABLES LIKE 'wsrep_node_name';
+-----------------+--------------------+
| Variable_name | Value |
+-----------------+--------------------+
| wsrep_node_name | pxc-cluster-node-3 |
+-----------------+--------------------+
1 row in set (0.01 sec)
mysql> SHOW VARIABLES LIKE 'wsrep_node_address';
+--------------------+-----------+
| Variable_name | Value |
+--------------------+-----------+
| wsrep_node_address | 10.0.0.27 |
+--------------------+-----------+
1 row in set (0.01 sec)
mysql> SHOW VARIABLES LIKE 'wsrep_on';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| wsrep_on | ON |
+---------------+-------+
1 row in set (0.01 sec)
mysql> SHOW VARIABLES LIKE 'wsrep_cluster_size';
Empty set (0.00 sec)
mysql> SHOW STATUS LIKE 'wsrep_cluster_size';
+--------------------+-------+
| Variable_name | Value |
+--------------------+-------+
| wsrep_cluster_size | 3 |
+--------------------+-------+
1 row in set (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)
#在任意节点创建数据库，并在其他节点查看数据库状态是否同步
[root@pxc3 ~]# mysql -uroot -p123456
mysql> create database testdb1;
Query OK, 1 row affected (0.01 sec)
[root@pxc2 ~]# mysql -uroot -p123456
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| testdb1 |
+--------------------+
7. 在PXC集群中加入新节点
#在PXC集群中再加一台新的主机PXC4：10.0.0.37
[root@pxc1 ]# scp /etc/yum.repos.d/pxc.repo 10.0.0.37:/etc/yum.repos.d/
[root@pxc4 ~]# yum install Percona-XtraDB-Cluster-57 -y
#修改配置，添加IP地址到wsrep_cluster_address，其他选项做相应修改
[root@pxc4 ~]# 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,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@CentOS7 ~]# systemctl start mysql
[root@CentOS7 ~]# mysql -uroot -p123456
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| testdb1 |
+--------------------+
mysql> SHOW STATUS LIKE 'wsrep_cluster_size';
+--------------------+-------+
| Variable_name | Value |
+--------------------+-------+
| wsrep_cluster_size | 4 |
+--------------------+-------+
8. PXC集群中模拟节点故障并修复故障节点
#在任意节点停止mysql服务，这里是pxc4
[root@pxc4 ~]#systemctl stop mysql
#在其它任意节点查看wsrep_cluster_size变量少了一个节点
[root@pxc1 ~]#mysql -uroot -pmaged
mysql> SHOW STATUS LIKE 'wsrep_cluster_size';
+--------------------+-------+
| Variable_name | Value |
+--------------------+-------+
| wsrep_cluster_size | 3 |
+--------------------+-------+
#创建数据库
mysql> create database testdb2;
Query OK, 1 row affected (0.00 sec)
#在其他节点上可看到数据库已同步
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| testdb1 |
| testdb2 |
+--------------------+
#恢复pxc4的服务，可看到数据自动同步
[root@pxc4~]# systemctl start mysql
[root@pxc4 ~]# mysql -uroot -p123456
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| testdb1 |
| testdb2 |
+--------------------+
7 rows in set (0.00 sec)
mysql> SHOW STATUS LIKE 'wsrep_cluster_size';
+--------------------+-------+
| Variable_name | Value |
+--------------------+-------+
| wsrep_cluster_size | 4 |
+--------------------+-------+
五、通过 ansible 部署二进制 mysql 8
1.环境准备
四台主机
10.0.0.18 CentOS8 做为ansible 管理端
10.0.0.8 CentOS8 远程主机
10.0.0.7 CentOS7 远程主机
10.0.0.6 CentOS6 远程主机
2. 安装ansible
#使用yum安装
[root@Ansible ~]# yum -y install ansible
[root@Ansible ~]# ll /etc/ansible/
total 24
-rw-r--r-- 1 root root 19985 Oct 8 02:06 ansible.cfg
-rw-r--r-- 1 root root 1016 Oct 8 02:06 hosts
drwxr-xr-x 2 root root 6 Oct 8 02:06 roles
3. 添加host
[root@Ansible ~]# vim /etc/ansible/hosts
[dbservers]
10.0.0.8
10.0.0.7
[webservers]
10.0.0.18 ansible_connection=local
10.0.0.6
[appservers]
10.0.0.8
10.0.0.6
[root@Ansible ~]# ansible all --list-hosts
hosts (4):
10.0.0.8
10.0.0.7
10.0.0.18
10.0.0.6
4.实现基于key验证的远程主机登录
#准备基于key验证的脚本
[root@Ansible ~]# vim ssh_push_key.sh
IPLIST="
10.0.0.8
10.0.0.18
10.0.0.7
10.0.0.6
"
rpm -q sshpass &> /dev/null || yum -y install sshpass
[ -f /root/.ssh/id_rsa ] || ssh-keygen -f /root/.ssh/id_rsa -P ''
export SSHPASS=centos
for IP in $IPLIST;do
sshpass -e ssh-copy-id -o StrictHostKeyChecking=no $IP
done
#运行脚本，实现基于key验证的远程主机登录
[root@Ansible ~]# bash ssh_push_key.sh
#使用ping验证
[root@Ansible ~]# ansible all -m ping
10.0.0.18 | SUCCESS => {
"ansible_facts": {
"discovered_interpreter_python": "/usr/libexec/platform-python"
},
"changed": false,
"ping": "pong"
}
10.0.0.8 | SUCCESS => {
"ansible_facts": {
"discovered_interpreter_python": "/usr/libexec/platform-python"
},
"changed": false,
"ping": "pong"
}
10.0.0.7 | SUCCESS => {
"ansible_facts": {
"discovered_interpreter_python": "/usr/bin/python"
},
"changed": false,
"ping": "pong"
}
10.0.0.6 | SUCCESS => {
"ansible_facts": {
"discovered_interpreter_python": "/usr/bin/python"
},
"changed": false,
"ping": "pong"
}
5.准备相关文件和包
[root@Ansible ~]# mkdir -pv /data/ansible/files
#准备mysql 8.0的安装包，上传到/data/ansible/files/下
#准备配置文件
[root@Ansible ~]# vim /data/ansible/files/my.cnf
[mysqld]
user=mysql
log-bin
symbolic-links=0
datadir=/data/mysql
socket=/data/mysql/mysql.sock
innodb_file_per_table=1
pid-file=/data/mysql/mysqld.pid
log-error=/data/mysql/mysql.log
[client]
port=3306
socket=/data/mysql/mysql.sock
[mysqld_safe]
log-error=/var/log/mysqld.log
#准备mysql安装脚本
[root@Ansible ~]# vim /data/ansible/install_mysql.yml
---
# install mysql-8.0.19-linux-glibc2.12-x86_64.tar.xz
- hosts: [dbservers]
remote_user: root
gather_facts: no
tasks:
- name: install packages
yum: name=libaio,autoconf,libtinfo*,libncurses*,perl-Data-Dumper,numactl-libs
- 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
- name: copy tar to remote host and file mode
unarchive: src=/data/ansible/files/mysql-8.0.19-linux-glibc2.12-x86_64.tar.xz dest=/usr/local owner=root group=root
- name: create linkfile /usr/local/mysql
file: src=/usr/local/mysql-8.0.19-linux-glibc2.12-x86_64 dest=/usr/local/mysql state=link
- name: PATH variable
copy: content='PATH=/usr/local/mysql/bin:$PATH' dest=/etc/profile.d/mysql.sh
- name: PATH variable source
shell: source /etc/profile.d/mysql.sh
- name: config my.cnf
copy: src=/data/ansible/files/my.cnf dest=/etc/my.cnf
- name: data dir
shell: mysqld --initialize --user=mysql --datadir=/data/mysql
- name: service script
shell: /bin/cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld
- name: enable service
shell: /etc/init.d/mysqld start;chkconfig --add mysqld;chkconfig mysqld on
- name: change mysql password
shell: mysqladmin -uroot -p`awk '/A temporary password/{print $NF}' /data/mysql/mysql.log` password 123456
6. 开始安装mysql8
[root@Ansible ~]# ansible-playbook /data/ansible/install_mysql.yml
PLAY [10.0.0.8] *******************************************
TASK [install packages] **********************************
changed: [10.0.0.8]
TASK [create mysql group] ****************************
changed: [10.0.0.8]
TASK [create mysql user] ********************************
changed: [10.0.0.8]
TASK [copy tar to remote host and file mode] *************
changed: [10.0.0.8]
TASK [create linkfile /usr/local/mysql] ******************
changed: [10.0.0.8]
TASK [PATH variable] **************************************
changed: [10.0.0.8]
TASK [PATH variable source] ******************************
changed: [10.0.0.8]
TASK [config my.cnf] *************************************
changed: [10.0.0.8]
TASK [data dir] ******************************************
changed: [10.0.0.8]
TASK [service script] *************************************
changed: [10.0.0.8]
TASK [enable service] *************************************
changed: [10.0.0.8]
TASK [change mysql password] *****************************
changed: [10.0.0.8]
PLAY RECAP *************************************************
10.0.0.8 : ok=12 changed=12 unreachable=0 failed=0 skipped=0 rescued=0 ignored=0
#在远程服务器上登录验证
[root@CentOS8 ~]# mysql -uroot -p123456
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 10
Server version: 8.0.19 MySQL Community Server - GPL
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>
1272
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
