1、如果主节点已经运行了一段时间，且有大量数据时，如何配置并启动slave节点（写出操作步骤）
- 主节点
###先完全备份主节点的数据，然后将备份发送给新的从节点
[root@master ~]# mysqldump -A -F --single-transaction --master-data=1 > /backup/full_`date +%F_%T`.sql
[root@master ~]# scp /backup/full_2020-10-18_04\:51\:34.sql 10.0.0.53:/data/
- 新从节点
###新的丛节点修改/etc/my.cnf配置文件，配置全局唯一的server-id
[root@slave2 ~]# vim /etc/my.cnf
[mysqld]
server-id=53
read-only
###查看并修改收到的备份文件
[root@slave2 data]# vim full_2020-10-18_05\:14\:06.sql
###找到如下信息，并配置好，此时看到的MASTER_LOG_POS为156，文件为master-bin.000009，复制的只是从主节点备份操作后到现在的数据，而原来的数据通过备份文件恢复到新的从节点。
CHANGE MASTER TO MASTER_HOST='10.0.0.51',
MASTER_USER='repl',
MASTER_PASSWORD='replpass',
MASTER_PORT=3306,
MASTER_LOG_FILE='master-bin.000009', MASTER_LOG_POS=156;
--
-- Current Database: `hellodb`
--
###修改完成后，启动数据库，并将备份文件导入，可以直接在shell环境下重定向文件，也可以直接在MySQL中导入。
[root@slave2 data]# mysql > full_2020-10-18_05\:14\:06.sql
###导入完成后，因为是新的从节点，需要手动开启两个线程io线程和sql线程。
mysql> start slave;
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.0.0.51
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-bin.000009
Read_Master_Log_Pos: 156
Relay_Log_File: slave2-relay-bin.000002
Relay_Log_Pos: 325
Relay_Master_Log_File: master-bin.000009
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
###能看到从节点的复制状态，查看数据已经恢复。
2、当master服务器宕机，提升一个slave成为新的master（写出操作步骤）
一主多从架构下，当主节点服务崩溃，且没有其他手段保证服务的情况下，需要将某个从节点提升为主节点，选择其中数据较新的从节点作为新的主节点，可以通过比较 Read_Master_Log_Pos的值，较大的那个成为新主节点。
- 这里指定slave2成为新主节点，修改my.cnf文件，关闭只读，停止线程
[root@slave2 log]# vim /etc/my.cnf
[mysqld]
server-id=53
#read-only
mysql> set global read_only=off;
Query OK, 0 rows affected (0.00 sec)
mysql> stop slave;
Query OK, 0 rows affected (0.01 sec)
mysql> reset slave all;
Query OK, 0 rows affected (0.02 sec)
-
新主节点开始备份数据，发送到其它从节点
[root@slave2 log]# mysqldump -A -F --single-transaction --master-data=1 -F > /backup_`date +%F_%T`.sql [root@slave2 log]# scp /backup_2020-10-18_18\:38\:40.sql 10.0.0.52:/data/ -
回到原来的master服务器，分析master的二进制日志，找出由于服务崩溃还未来得及同步到从节点的二进制日志，导出来恢复到新的主节点去。
-
所有的从节点重新配置主节点复制信息，直接在备份文件中修改，然后导入数据库，开启 Slave_IO线程和Slave_SQL线程，当Slave_IO_Running和Slave_SQL_Running均为yes，即新主从配置完成。
[root@slave1 backup]# vim backup.sql -- Position to start replication or point-in-time recovery from -- CHANGE MASTER TO master_host='10.0.0.53', master_user='repl', master_password='replpass', MASTER_LOG_FILE='slave2-bin.000007', MASTER_LOG_POS=363; -- -- Current Database: `hellodb` [root@slave1 backup]# mysql > backup.sql mysql> start slave; Query OK, 0 rows affected (0.00 sec) mysql> show slave status\G; *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 10.0.0.53 Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: slave2-bin.000007 Read_Master_Log_Pos: 363 Relay_Log_File: slave1-relay-bin.000004 Relay_Log_Pos: 580 Relay_Master_Log_File: slave2-bin.000007 Slave_IO_Running: Yes Slave_SQL_Running: Yes …… ……3、通过 MHA 0.58 搭建一个数据库集群结构
-
环境：
10.0.0.51 centos8 master 10.0.0.52 centos8 slave1 10.0.0.53 centos8 slave2 10.0.0.100 centos7 MHA管理 -
MHA管理服务器安装mha的manager包和node包
[root@mha ~]# yum install -y mha4mysql-manager-0.58-0.el7.centos.noarch.rpm mha4mysql-node-0.58-0.el7.centos.noarch.rpm
-
-
- 其它主从服务器安装mha的node包
[root@slave2 ~]# yum install -y mha4mysql-node-0.58-0.el7.centos.noarch.rpm
[root@slave1 backup]# yum install -y mha4mysql-node-0.58-0.el7.centos.noarch.rpm
[root@master ~]# yum install -y mha4mysql-node-0.58-0.el7.centos.noarch.rpm
- 在mha管理主机上配置所有主机基于key验证
[root@mha ~]# ssh-keygen
Generating public/private rsa key pair.
Enter file in which to save the key (/root/.ssh/id_rsa):
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:dxVawlKpLLyeqg9E7rI9F8yuREf60SEXo2tuQJInWnI root@mha.org
The key's randomart image is:
+---[RSA 2048]----+
| o oo.o |
| . . o. o+ . |
|. E + +.o. o. . |
| = B o =o.o . |
|. *o= So. . |
| + *+... . |
| . +.+o . |
| =.oo o |
| . +*o. |
+----[SHA256]-----+
[root@mha ~]# rsync -av .ssh 10.0.0.51:/root
[root@mha ~]# rsync -av .ssh 10.0.0.52:/root
[root@mha ~]# rsync -av .ssh 10.0.0.53:/root
###验证
[root@mha ~]# ssh 10.0.0.51
Last login: Sun Oct 18 08:42:17 2020 from 10.0.0.10
[root@master ~]# exit
logout
Connection to 10.0.0.51 closed.
- 在MHA管理主机创建配置文件
[root@mha ~]# vim /etc/mha/test.cnf
[server default]
user=mhauser
password=123456
manager_workdir=/data/mha/test/
manager_log=/data/mha/test/manager.log
remote_workdir=/data/mha/test/
ssh_user=root
repl_user=repl
repl_password=replpass
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.53
candidate_master=1
[server2]
hostname=10.0.0.52
[server3]
hostname=10.0.0.51
candidate_master=1
- 准备相关脚本
- sendmail.sh
[root@mha ~]# vim /usr/local/bin/sendmail.sh
#!/bin/bash
echo "MySQL is down" | mail -s "MHA WARNING" root@277297360.com
[root@mha ~]# chmod +x /usr/local/bin/sendmail.sh
[root@mha ~]# cat >> /etc/mail.rc <<EOF
set from=277297360@qq.com
set smtp=smtp.qq.com
set smtp-auth-user=277297360@qq.com
set smtp-auth-password=qyazdpihrzbtbjjf
EOF
- master_ip_failover
[root@mha ~]# vim /usr/local/bin/master_ip_faolover
#!/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.200/24';
my $gateway = '10.0.0.2';
my $interface ='ens33';
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,
);
sub main {
print "\n\nIN SCRIPT TEST====$ssh_stop_vip==$ssh_start_vip===\n\n";
if ( $command eq "stop" || $command eq "stopssh" ) {
my $exit_code = 1;
eval {
print "Disabling the VIP on old master: $orig_master_host \n";
&stop_vip();
if ($@) {
warn "Got Error: $@\n";
exit $exit_code;
}
exit $exit_code;
}
elsif ( $command eq "start" ) {
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_suer\@$orig_master_host \" $ssh_start_vip \"`;
exit 0;
}
else {
&usage();
exit 1;
}
}
sub start_vip() {
`ssh $ssh_suer\@$new_master_host \" $ssh_start_vip \"`;
}
sub stop_vip() {
`ssh $ssh_suer\@$orig_master_host \" $ssh_start_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@mha ~]# chmod +x /usr/local/bin/master_ip_faolover
- 配置master主机
[root@master ~]# vim /etc/my.cnf
[mysqld]
server-id=1
log_bin
skip_name_resolve=1
general_log
[root@master ~]# yum install -y net-tools
[root@master ~]# ifconfig ens33:1 10.0.0.200/24
mysql> create user 'repl'@'10.0.0.%'identified with 'mysql_native_password' by 'replpass';
Query OK, 0 rows affected (0.01 sec)
mysql> grant replication slave on *.* to 'repl'@'10.0.0.%';
Query OK, 0 rows affected (0.00 sec)
mysql> create user 'mhauser'@'10.0.0.%'identified with 'mysql_native_password' by '123456';
Query OK, 0 rows affected (0.00 sec)
mysql> grant all on *.* to 'mhauser'@'10.0.0.%';
Query OK, 0 rows affected (0.01 sec)
###这里忘记提前查看日志就创建账号了，所以待会配置从节点的时候需要再次授权
- 配置slave1
[root@slave1 ~]# vim /etc/my.cnf
[mysqld]
server-id=52
log_bin
read_only
relay_log_purge=0
skip_name_resolve=1
[root@slave1 ~]# systemctl enable --now mysqld
mysql> change master to master_host='10.0.0.53',
-> master_user='repl',
-> master_password='replpass',
-> master_log_file='slave2-bin.000009',
-> master_log_pos=1863;
mysql> create user 'repl'@'10.0.0.%'identified with 'mysql_native_password' by 'replpass';
Query OK, 0 rows affected (0.01 sec)
mysql> grant replication slave on *.* to 'repl'@'10.0.0.%';
Query OK, 0 rows affected (0.00 sec)
mysql> create user 'mhauser'@'10.0.0.%'identified with 'mysql_native_password' by '123456';
Query OK, 0 rows affected (0.00 sec)
mysql> grant all on *.* to 'mhauser'@'10.0.0.%';
Query OK, 0 rows affected (0.01 sec)
mysql> start slave;
- 配置slave2
[root@slave2 ~]# vim /etc/my.cnf
[mysqld]
server-id=51
log_bin
read_only
relay_log_purge=0
skip_name_resolve=1
[root@slave2 ~]# systemctl enable --now mysqld
mysql> change master to master_host='10.0.0.53',
-> master_user='repl',
-> master_password='replpass',
-> master_log_file='slave2-bin.000009',
-> master_log_pos=1863;
mysql> create user 'repl'@'10.0.0.%'identified with 'mysql_native_password' by 'replpass';
Query OK, 0 rows affected (0.01 sec)
mysql> grant replication slave on *.* to 'repl'@'10.0.0.%';
Query OK, 0 rows affected (0.00 sec)
mysql> create user 'mhauser'@'10.0.0.%'identified with 'mysql_native_password' by '123456';
Query OK, 0 rows affected (0.00 sec)
mysql> grant all on *.* to 'mhauser'@'10.0.0.%';
Query OK, 0 rows affected (0.01 sec)
mysql> start slave;
-
MHA环境检查
- SSH环境检查
[root@mha /]# masterha_check_ssh --conf=/etc/mha/test.cnf Sun Oct 18 23:52:53 2020 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping. Sun Oct 18 23:52:53 2020 - [info] Reading application default configuration from /etc/mha/test.cnf.. Sun Oct 18 23:52:53 2020 - [info] Reading server configuration from /etc/mha/test.cnf.. Sun Oct 18 23:52:53 2020 - [info] Starting SSH connection tests.. Sun Oct 18 23:52:54 2020 - [debug] Sun Oct 18 23:52:53 2020 - [debug] Connecting via SSH from root@10.0.0.53(10.0.0.53:22) to root@10.0.0.51(10.0.0.51:22).. Sun Oct 18 23:52:54 2020 - [debug] ok. Sun Oct 18 23:52:54 2020 - [debug] Connecting via SSH from root@10.0.0.53(10.0.0.53:22) to root@10.0.0.52(10.0.0.52:22).. Sun Oct 18 23:52:54 2020 - [debug] ok. Sun Oct 18 23:52:57 2020 - [debug] Sun Oct 18 23:52:54 2020 - [debug] Connecting via SSH from root@10.0.0.51(10.0.0.51:22) to root@10.0.0.53(10.0.0.53:22).. Warning: Permanently added '10.0.0.53' (ECDSA) to the list of known hosts. Sun Oct 18 23:52:55 2020 - [debug] ok. Sun Oct 18 23:52:55 2020 - [debug] Connecting via SSH from root@10.0.0.51(10.0.0.51:22) to root@10.0.0.52(10.0.0.52:22).. Warning: Permanently added '10.0.0.52' (ECDSA) to the list of known hosts. Sun Oct 18 23:52:56 2020 - [debug] ok. Sun Oct 18 23:52:58 2020 - [debug] Sun Oct 18 23:52:54 2020 - [debug] Connecting via SSH from root@10.0.0.52(10.0.0.52:22) to root@10.0.0.53(10.0.0.53:22).. Warning: Permanently added '10.0.0.53' (ECDSA) to the list of known hosts. Sun Oct 18 23:52:57 2020 - [debug] ok. Sun Oct 18 23:52:57 2020 - [debug] Connecting via SSH from root@10.0.0.52(10.0.0.52:22) to root@10.0.0.51(10.0.0.51:22).. Sun Oct 18 23:52:57 2020 - [debug] ok. Sun Oct 18 23:52:58 2020 - [info] All SSH connection tests passed successfully. -
REPL环境检查
[root@mha /]# masterha_check_repl --conf=/etc/mha/test.cnf Mon Oct 19 00:04:39 2020 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping. Mon Oct 19 00:04:39 2020 - [info] Reading application default configuration from /etc/mha/test.cnf.. Mon Oct 19 00:04:39 2020 - [info] Reading server configuration from /etc/mha/test.cnf.. Mon Oct 19 00:04:39 2020 - [info] MHA::MasterMonitor version 0.58. Mon Oct 19 00:04:40 2020 - [info] GTID failover mode = 0 Mon Oct 19 00:04:40 2020 - [info] Dead Servers: Mon Oct 19 00:04:40 2020 - [info] Alive Servers: Mon Oct 19 00:04:40 2020 - [info] 10.0.0.53(10.0.0.53:3306) Mon Oct 19 00:04:40 2020 - [info] 10.0.0.51(10.0.0.51:3306) Mon Oct 19 00:04:40 2020 - [info] 10.0.0.52(10.0.0.52:3306) Mon Oct 19 00:04:40 2020 - [info] Alive Slaves: Mon Oct 19 00:04:40 2020 - [info] 10.0.0.51(10.0.0.51:3306) Version=8.0.21 (oldest major version between slaves) log-bin:enabled Mon Oct 19 00:04:40 2020 - [info] Replicating from 10.0.0.53(10.0.0.53:3306) Mon Oct 19 00:04:40 2020 - [info] Primary candidate for the new Master (candidate_master is set) Mon Oct 19 00:04:40 2020 - [info] 10.0.0.52(10.0.0.52:3306) Version=8.0.21 (oldest major version between slaves) log-bin:enabled Mon Oct 19 00:04:40 2020 - [info] Replicating from 10.0.0.53(10.0.0.53:3306) Mon Oct 19 00:04:40 2020 - [info] Current Alive Master: 10.0.0.53(10.0.0.53:3306) Mon Oct 19 00:04:40 2020 - [info] Checking slave configurations.. Mon Oct 19 00:04:40 2020 - [info] Checking replication filtering settings.. Mon Oct 19 00:04:40 2020 - [info] binlog_do_db= , binlog_ignore_db= Mon Oct 19 00:04:40 2020 - [info] Replication filtering check ok. Mon Oct 19 00:04:40 2020 - [info] GTID (with auto-pos) is not supported Mon Oct 19 00:04:40 2020 - [info] Starting SSH connection tests.. Mon Oct 19 00:04:42 2020 - [info] All SSH connection tests passed successfully. Mon Oct 19 00:04:42 2020 - [info] Checking MHA Node version.. Mon Oct 19 00:04:43 2020 - [info] Version check ok. Mon Oct 19 00:04:43 2020 - [info] Checking SSH publickey authentication settings on the current master.. Mon Oct 19 00:04:43 2020 - [info] HealthCheck: SSH to 10.0.0.53 is reachable. Mon Oct 19 00:04:43 2020 - [info] Master MHA Node version is 0.58. Mon Oct 19 00:04:43 2020 - [info] Checking recovery script configurations on 10.0.0.53(10.0.0.53:3306).. Mon Oct 19 00:04:43 2020 - [info] Executing command: save_binary_logs --command=test --start_pos=4 --binlog_dir=/var/lib/mysql --output_file=/data/mha/test//save_binary_logs_test --manager_version=0.58 --start_file=slave2-bin.000009 Mon Oct 19 00:04:43 2020 - [info] Connecting to root@10.0.0.53(10.0.0.53:22).. Creating /data/mha/test if not exists.. ok. Checking output directory is accessible or not.. ok. Binlog found at /var/lib/mysql, up to slave2-bin.000009 Mon Oct 19 00:04:44 2020 - [info] Binlog setting check done. Mon Oct 19 00:04:44 2020 - [info] Checking SSH publickey authentication and checking recovery script configurations on all alive slave servers.. Mon Oct 19 00:04:44 2020 - [info] Executing command : apply_diff_relay_logs --command=test --slave_user='mhauser' --slave_host=10.0.0.51 --slave_ip=10.0.0.51 --slave_port=3306 --workdir=/data/mha/test/ --target_version=8.0.21 --manager_version=0.58 --relay_dir=/var/lib/mysql --current_relay_log=master-relay-bin.000002 --slave_pass=xxx Mon Oct 19 00:04:44 2020 - [info] Connecting to root@10.0.0.51(10.0.0.51:22).. Checking slave recovery environment settings.. Relay log found at /var/lib/mysql, up to master-relay-bin.000002 Temporary relay log file is /var/lib/mysql/master-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. Mon Oct 19 00:04:44 2020 - [info] Executing command : apply_diff_relay_logs --command=test --slave_user='mhauser' --slave_host=10.0.0.52 --slave_ip=10.0.0.52 --slave_port=3306 --workdir=/data/mha/test/ --target_version=8.0.21 --manager_version=0.58 --relay_dir=/var/lib/mysql --current_relay_log=slave1-relay-bin.000002 --slave_pass=xxx Mon Oct 19 00:04:44 2020 - [info] Connecting to root@10.0.0.52(10.0.0.52:22).. Checking slave recovery environment settings.. Relay log found at /var/lib/mysql, up to slave1-relay-bin.000002 Temporary relay log file is /var/lib/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. Mon Oct 19 00:04:45 2020 - [info] Slaves settings check done. Mon Oct 19 00:04:45 2020 - [info] 10.0.0.53(10.0.0.53:3306) (current master) +--10.0.0.51(10.0.0.51:3306) +--10.0.0.52(10.0.0.52:3306) Mon Oct 19 00:04:45 2020 - [info] Checking replication health on 10.0.0.51.. Mon Oct 19 00:04:45 2020 - [info] ok. Mon Oct 19 00:04:45 2020 - [info] Checking replication health on 10.0.0.52.. Mon Oct 19 00:04:45 2020 - [info] ok. Mon Oct 19 00:04:45 2020 - [warning] master_ip_failover_script is not defined. Mon Oct 19 00:04:45 2020 - [warning] shutdown_script is not defined. Mon Oct 19 00:04:45 2020 - [info] Got exit code 0 (Not master dead). MySQL Replication Health is OK. -
启动MHA
[root@mha ~]# masterha_manager --conf=/etc/mha/test.cnf Mon Oct 19 00:17:41 2020 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping. Mon Oct 19 00:17:41 2020 - [info] Reading application default configuration from /etc/mha/test.cnf.. Mon Oct 19 00:17:41 2020 - [info] Reading server configuration from /etc/mha/test.cnf.. [root@slave2 mysql]# tail -f /var/lib/mysql/slave2.log 2020-10-18T16:20:07.132804Z 20 Query SELECT 1 As Value 2020-10-18T16:20:08.133663Z 20 Query SELECT 1 As Value 2020-10-18T16:20:09.134071Z 20 Query SELECT 1 As Value 2020-10-18T16:20:10.134767Z 20 Query SELECT 1 As Value 2020-10-18T16:20:11.135159Z 20 Query SELECT 1 As Value 2020-10-18T16:20:12.135387Z 20 Query SELECT 1 As Value 2020-10-18T16:20:13.136433Z 20 Query SELECT 1 As Value 2020-10-18T16:20:14.137101Z 20 Query SELECT 1 As Value 2020-10-18T16:20:15.137251Z 20 Query SELECT 1 As Value -
验证：将主节点的MySQL服务停止，看MHA是否会重新设置一个新的主节点
- 先查看从节点slave状态，再停止主节点服务后观察mha-manager日志
mysql> show slave status\G; *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 10.0.0.53 Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: slave2-bin.000009 Read_Master_Log_Pos: 1863 Relay_Log_File: slave1-relay-bin.000002 Relay_Log_Pos: 325 Relay_Master_Log_File: slave2-bin.000009 Slave_IO_Running: Yes Slave_SQL_Running: Yes 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: 1863 Relay_Log_Space: 535 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: 0 Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 1 Master_UUID: c4ee89d2-1121-11eb-8515-000c295e217d Master_Info_File: mysql.slave_master_info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates Master_Retry_Count: 86400 Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: Executed_Gtid_Set: Auto_Position: 0 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: Master_public_key_path: Get_master_public_key: 0 Network_Namespace: 1 row in set (0.00 sec) [root@mha test]# cat manager.log Mon Oct 19 00:06:03 2020 - [info] MHA::MasterMonitor version 0.58. Mon Oct 19 00:06:05 2020 - [info] GTID failover mode = 0 Mon Oct 19 00:06:05 2020 - [info] Dead Servers: Mon Oct 19 00:06:05 2020 - [info] Alive Servers: Mon Oct 19 00:06:05 2020 - [info] 10.0.0.53(10.0.0.53:3306) Mon Oct 19 00:06:05 2020 - [info] 10.0.0.51(10.0.0.51:3306) Mon Oct 19 00:06:05 2020 - [info] 10.0.0.52(10.0.0.52:3306) Mon Oct 19 00:06:05 2020 - [info] Alive Slaves: Mon Oct 19 00:06:05 2020 - [info] 10.0.0.51(10.0.0.51:3306) Version=8.0.21 (oldest major version between slaves) log-bin:enabled Mon Oct 19 00:06:05 2020 - [info] Replicating from 10.0.0.53(10.0.0.53:3306) Mon Oct 19 00:06:05 2020 - [info] Primary candidate for the new Master (candidate_master is set) Mon Oct 19 00:06:05 2020 - [info] 10.0.0.52(10.0.0.52:3306) Version=8.0.21 (oldest major version between slaves) log-bin:enabled Mon Oct 19 00:06:05 2020 - [info] Replicating from 10.0.0.53(10.0.0.53:3306) Mon Oct 19 00:06:05 2020 - [info] Current Alive Master: 10.0.0.53(10.0.0.53:3306) Mon Oct 19 00:06:05 2020 - [info] Checking slave configurations.. Mon Oct 19 00:06:05 2020 - [info] Checking replication filtering settings.. Mon Oct 19 00:06:05 2020 - [info] binlog_do_db= , binlog_ignore_db= Mon Oct 19 00:06:05 2020 - [info] Replication filtering check ok. Mon Oct 19 00:06:05 2020 - [info] GTID (with auto-pos) is not supported Mon Oct 19 00:06:05 2020 - [info] Starting SSH connection tests.. Mon Oct 19 00:06:07 2020 - [info] All SSH connection tests passed successfully. Mon Oct 19 00:06:07 2020 - [info] Checking MHA Node version.. Mon Oct 19 00:06:08 2020 - [info] Version check ok. Mon Oct 19 00:06:08 2020 - [info] Checking SSH publickey authentication settings on the current master.. Mon Oct 19 00:06:08 2020 - [info] HealthCheck: SSH to 10.0.0.53 is reachable. Mon Oct 19 00:06:08 2020 - [info] Master MHA Node version is 0.58. Mon Oct 19 00:06:08 2020 - [info] Checking recovery script configurations on 10.0.0.53(10.0.0.53:3306).. Mon Oct 19 00:06:08 2020 - [info] Executing command: save_binary_logs --command=test --start_pos=4 --binlog_dir=/var/lib/mysql --output_file=/data/mha/test//save_binary_logs_test --manager_version=0.58 --start_file=slave2-bin.000009 Mon Oct 19 00:06:08 2020 - [info] Connecting to root@10.0.0.53(10.0.0.53:22).. Creating /data/mha/test if not exists.. ok. Checking output directory is accessible or not.. ok. Binlog found at /var/lib/mysql, up to slave2-bin.000009 Mon Oct 19 00:06:08 2020 - [info] Binlog setting check done. Mon Oct 19 00:06:08 2020 - [info] Checking SSH publickey authentication and checking recovery script configurations on all alive slave servers.. Mon Oct 19 00:06:08 2020 - [info] Executing command : apply_diff_relay_logs --command=test --slave_user='mhauser' --slave_host=10.0.0.51 --slave_ip=10.0.0.51 --slave_port=3306 --workdir=/data/mha/test/ --target_version=8.0.21 --manager_version=0.58 --relay_dir=/var/lib/mysql --current_relay_log=master-relay-bin.000002 --slave_pass=xxx Mon Oct 19 00:06:08 2020 - [info] Connecting to root@10.0.0.51(10.0.0.51:22).. Checking slave recovery environment settings.. Relay log found at /var/lib/mysql, up to master-relay-bin.000002 Temporary relay log file is /var/lib/mysql/master-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. Mon Oct 19 00:06:09 2020 - [info] Executing command : apply_diff_relay_logs --command=test --slave_user='mhauser' --slave_host=10.0.0.52 --slave_ip=10.0.0.52 --slave_port=3306 --workdir=/data/mha/test/ --target_version=8.0.21 --manager_version=0.58 --relay_dir=/var/lib/mysql --current_relay_log=slave1-relay-bin.000002 --slave_pass=xxx Mon Oct 19 00:06:09 2020 - [info] Connecting to root@10.0.0.52(10.0.0.52:22).. Checking slave recovery environment settings.. Relay log found at /var/lib/mysql, up to slave1-relay-bin.000002 Temporary relay log file is /var/lib/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. Mon Oct 19 00:06:09 2020 - [info] Slaves settings check done. Mon Oct 19 00:06:09 2020 - [info] 10.0.0.53(10.0.0.53:3306) (current master) +--10.0.0.51(10.0.0.51:3306) +--10.0.0.52(10.0.0.52:3306) ###可以看见目前的Master_Host是10.0.0.53，并且Read_Master_Log_Pos为1863，一切正常，现在将master主机的MySQL服务停止 [root@master mysql]# systemctl stop mysqld [root@mha test]# cat manager.log ----- Failover Report ----- test: MySQL Master failover 10.0.0.53(10.0.0.53:3306) to 10.0.0.51(10.0.0.51:3306) succeeded Master 10.0.0.53(10.0.0.53:3306) is down! Check MHA Manager logs at mha.org:/data/mha/test/manager.log for details. Started automated(non-interactive) failover. The latest slave 10.0.0.51(10.0.0.51:3306) has all relay logs for recovery. Selected 10.0.0.51(10.0.0.51:3306) as a new master. 10.0.0.51(10.0.0.51:3306): OK: Applying all logs succeeded. 10.0.0.52(10.0.0.52:3306): This host has the latest relay log events. Generating relay diff files from the latest slave succeeded. 10.0.0.52(10.0.0.52:3306): OK: Applying all logs succeeded. Slave started, replicating from 10.0.0.51(10.0.0.51:3306) 10.0.0.51(10.0.0.51:3306): Resetting slave info succeeded. Master failover to 10.0.0.51(10.0.0.51:3306) completed successfully. Mon Oct 19 00:22:47 2020 - [info] Sending mail.. ###可以看见虚拟ip由10.0.0.53改变到10.0.0.51上去，并且提升master is down，随后选出了新的主节点10.0.0.51 在从节点上查看slave状态亦可观察到。 mysql> show slave status\G; *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 10.0.0.51 Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: master-bin.000012 Read_Master_Log_Pos: 1863 Relay_Log_File: slave1-relay-bin.000002 Relay_Log_Pos: 325 Relay_Master_Log_File: master-bin.000012 Slave_IO_Running: Yes Slave_SQL_Running: Yes 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: 1863 Relay_Log_Space: 535 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: 0 Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 51 Master_UUID: a5854745-1074-11eb-acd9-000c29286335 Master_Info_File: mysql.slave_master_info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates Master_Retry_Count: 86400 Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: Executed_Gtid_Set: Auto_Position: 0 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: Master_public_key_path: Get_master_public_key: 0 Network_Namespace: 1 row in set (0.00 sec)
4、实战案例：Percona XtraDB Cluster(PXC 5.7）
-
配置环境，准备PXC的RPM包,关闭selinux，防火墙
[root@pxc1 ~]# yum install -y Percona-XtraDB-Cluster-57 [root@pxc2 ~]# yum install -y Percona-XtraDB-Cluster-57 [root@pxc3 ~]# yum install -y Percona-XtraDB-Cluster-57 -
准备集群配置文件和mysql配置文件
- PXC配置文件，每个节点不同修改IP地址
[root@pxc1 ~]# vim /etc/percona-xtradb-cluster.conf.d/wsrep.cnf # 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.106,10.0.0.107,10.0.0.108 # 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=10.0.0.106 # Cluster name wsrep_cluster_name=pxc-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="sstuser:s3cretPass"-
MySQL配置文件
[root@pxc1 ~]# vim /etc/percona-xtradb-cluster.conf.d/mysqld.cnf # Template my.cnf for PXC # Edit to your requirements. [client] socket=/var/lib/mysql/mysql.sock [mysqld] server-id=1 ###各节点修改不同id datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid log-bin ###开启二进制日志 log_slave_updates expire_logs_days=7
-
启动其中一个节点PXC1
[root@pxc1 ~]# systemctl start mysql@bootstrap.service [root@pxc1 ~]# cat /var/log/mysqld.log | grep 'temporary password' ###找出mysql的root密码登录 [root@localhost ~]# mysql -uroot -p'jBcyos;El9rM' mysql> alter user 'root'@'localhost'identified by '123456'; mysql> create user 'sstuser'@'localhost'identified by 's3cretPass'; ###这里的密码用户要和PXC配置文件里的wsrep_sst_auth一样 mysql> grant reload,lock tables,process,replication client on *.* to 'sstuser'@'localhost'; ###授权用户 mysql> show status like 'wsrep%'; ###查看pxc状态，其中wsrep_local_state_comment为synced可以加入新节点，wsrep_cluster_size为集群节点数，wsrep_cluster_status为primary表示已连接且准备完成。 +----------------------------------+----------------------------------------------+ | Variable_name | Value | +----------------------------------+----------------------------------------------+ | wsrep_local_state_uuid | 93bf3b2c-11b5-11eb-80f2-bbdffe369789 | | wsrep_protocol_version | 9 | | wsrep_last_applied | 3 | | wsrep_last_committed | 3 | | wsrep_replicated | 0 | | wsrep_replicated_bytes | 0 | | wsrep_repl_keys | 0 | | wsrep_repl_keys_bytes | 0 | | wsrep_repl_data_bytes | 0 | | wsrep_repl_other_bytes | 0 | | wsrep_received | 2 | | wsrep_received_bytes | 152 | | wsrep_local_commits | 0 | | wsrep_local_cert_failures | 0 | | wsrep_local_replays | 0 | | wsrep_local_send_queue | 0 | | wsrep_local_send_queue_max | 1 | | wsrep_local_send_queue_min | 0 | | wsrep_local_send_queue_avg | 0.000000 | | wsrep_local_recv_queue | 0 | | wsrep_local_recv_queue_max | 2 | | wsrep_local_recv_queue_min | 0 | | wsrep_local_recv_queue_avg | 0.500000 | | wsrep_local_cached_downto | 0 | | wsrep_flow_control_paused_ns | 0 | | wsrep_flow_control_paused | 0.000000 | | wsrep_flow_control_sent | 0 | | wsrep_flow_control_recv | 0 | | wsrep_flow_control_interval | [ 100, 100 ] | | wsrep_flow_control_interval_low | 100 | | wsrep_flow_control_interval_high | 100 | | wsrep_flow_control_status | OFF | | wsrep_cert_deps_distance | 0.000000 | | wsrep_apply_oooe | 0.000000 | | wsrep_apply_oool | 0.000000 | | wsrep_apply_window | 0.000000 | | wsrep_commit_oooe | 0.000000 | | wsrep_commit_oool | 0.000000 | | wsrep_commit_window | 0.000000 | | wsrep_local_state | 4 | | wsrep_local_state_comment | Synced | | wsrep_cert_index_size | 0 | | wsrep_cert_bucket_count | 22 | | wsrep_gcache_pool_size | 1320 | | wsrep_causal_reads | 0 | | wsrep_cert_interval | 0.000000 | | wsrep_open_transactions | 0 | | wsrep_open_connections | 0 | | wsrep_ist_receive_status | | | wsrep_ist_receive_seqno_start | 0 | | wsrep_ist_receive_seqno_current | 0 | | wsrep_ist_receive_seqno_end | 0 | | wsrep_incoming_addresses | 10.0.0.106:3306 | | wsrep_cluster_weight | 1 | | wsrep_desync_count | 0 | | wsrep_evs_delayed | | | wsrep_evs_evict_list | | | wsrep_evs_repl_latency | 1.433e-06/2.8316e-06/3.999e-06/9.66327e-07/5 | | wsrep_evs_state | OPERATIONAL | | wsrep_gcomm_uuid | ac75f72a-11b6-11eb-aec9-ba8dee26c886 | | wsrep_cluster_conf_id | 1 | | wsrep_cluster_size | 1 | | wsrep_cluster_state_uuid | 93bf3b2c-11b5-11eb-80f2-bbdffe369789 | | wsrep_cluster_status | Primary | | wsrep_connected | ON | | wsrep_local_bf_aborts | 0 | | wsrep_local_index | 0 | | wsrep_provider_name | Galera | | wsrep_provider_vendor | Codership Oy <info@codership.com> | | wsrep_provider_version | 3.45(ra60e019) | | wsrep_ready | ON | +----------------------------------+----------------------------------------------+ 71 rows in set (0.00 sec) -
打开其它节点的MySQL服务
[root@pxc2 ~]# systemctl start mysql [root@pxc2 ~]# mysql -uroot -p123456 mysql> ###root登录，密码为在pxc1上修改的密码 [root@pxc3 ~]# systemctl start mysql [root@pxc3 ~]# mysql -uroot -p123456 mysql> ###pxc1上尝试执行SQL语句，看其它节点是否同步 mysql> create database test; Query OK, 1 row affected (0.01 sec) mysql> use test Database changed mysql> create table t1(id int); Query OK, 0 rows affected (0.07 sec) mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | | t1 | | test | +--------------------+ 6 rows in set (0.00 sec) ###三个节点均能互相同步数据
5、通过 ansible 部署二进制 mysql 8
-
yum安装ansible
-
配置ansible主配置文件/etc/ansilbe/ansible.cnf
[root@localhost ansible]# vim /etc/ansible/ansible.cfg host_key_checking = False log_path = /var/log/ansible.log -
配置ansible主机清单
[root@localhost local]# vim /etc/ansible/hosts [dbsrvs] 10.0.0.106 ansilbe_connection=local 10.0.0.107 10.0.0.108 10.0.0.109 -
配置所有节点基于ssh-key验证
[root@localhost scripts]# vim push_ssh_key.sh
#!/bin/bash
IPLIST="
10.0.0.106
10.0.0.107
10.0.0.108
10.0.0.109"
rpm -q sshpass &> /dev/null || yum -y isntall 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 -o StrictHostkeyChecking=no $IP
done
-
创建ansible-playbook文件
[root@60C8 ansible]# vim install_mysql8.yml --- - hosts: dbsrvs remote_user: root gather_facts: no tasks: - name: install packages yum: name=libaio,perl-Data-Dumper,perl-Getopt-Long - name: create group group: name=mysql gid=306 - name: create user user: name=mysql uid=306 group=mysql shell=/sbin/nologin system=yes create_home=no home=/data/mysql - name: copy file unarchive: src=/data/ansible/files/mysql-8.0.21-linux-glibc2.12-x86_64.tar.xz dest=/usr/local/ owner=root group=root - name: create linkfile file: src=/usr/local/mysql-8.0.21-linux-glibc2.12-x86_64 dest=/usr/local/mysql state=link - name: datadir shell: chdir=/usr/local/mysql/bin ./mysqld --initialize --datadir=/data/mysql --user=mysql &> /var/password.txt tags: data #####重定向初始化记录至/var/password.txt，正常应该在日志里记录生成的密码，在安装好数据库之后没在日志里看到相关记录。 - name: my.cnf copy: src=/data/ansible/files/my.cnf dest=/etc/my.cnf - 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 tags: service - name: Set path copy: content='PATH=/usr/local/mysql/bin:$PATH' dest=/etc/profile.d/mysql.sh - name: password change script script: /data/ansible/files/Password_change_mysql.sh tags: script -
Password_change_mysql.sh
#!/bin/bash
OLD_PASSWORD=`grep 'temporary password' /var/password.txt | awk -F' ' '{printf $13}'`
NEW_PASSWORD='Changeme_123'
mysqladmin -uroot -p$OLD_PASSWORD password $NEW_PASSWORD
-
运行ansible-playbook
[root@C8 ansible]# ansible-playbook install_mysql8.yml PLAY [dbsrvs] ************************************************************************************************************************************************************* TASK [install packages] *************************************************************************************************************************************************** ok: [10.0.0.107] TASK [create group] ******************************************************************************************************************************************************* ok: [10.0.0.107] TASK [create user] ******************************************************************************************************************************************************** ok: [10.0.0.107] TASK [copy file] ********************************************************************************************************************************************************** changed: [10.0.0.107] TASK [create linkfile] **************************************************************************************************************************************************** changed: [10.0.0.107] TASK [datadir] ************************************************************************************************************************************************************ changed: [10.0.0.107] TASK [my.cnf] ************************************************************************************************************************************************************* changed: [10.0.0.107] TASK [service script] ***************************************************************************************************************************************************** changed: [10.0.0.107] TASK [enable service] ***************************************************************************************************************************************************** changed: [10.0.0.107] TASK [Set path] *********************************************************************************************************************************************************** changed: [10.0.0.107] PLAY RECAP **************************************************************************************************************************************************************** 10.0.0.107 : ok=10 changed=7 unreachable=0 failed=0 skipped=0 rescued=0 ignored=0
- 安装完成
[root@localhost ~]# mysql -uroot -p'Changeme_123'
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 25
Server version: 8.0.21 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>
网友评论