1 MariaDB主从复制原理
MariaDB主从复制主要采用异步复制的方式进行：
（1）在主服务器上，每当有写请求进来，先写入磁盘数据文件中，再写入二进制日志文件，然后返回结果给客户端。
（2）当主服务器的二进制日志文件产生新的事件时，主服务器会通知从服务器。
（3）从服务器接收到通知后，由IO thread向主服务器发起二进制日志中的事件请求。
（4）由主服务器的dump thread线程读取主服务器上发生改变的二进制文件。
（5）而后再发送给二进制日志文件给从服务器，从服务器把它保存到自己的中继日志中。
（6）再由从服务器的sql thread线程把中继日志读取出来执行。
（7）sql thread线程把中继日志读取出来执行的结果保存到磁盘文件中去。
2 MariaDB一主一从架构构建
准备两台纯新的CentOS7.6服务器，其中主机称为node1的服务器地址为192.168.130.132，主机称为node2的服务器地址为192.168.130.133
#node1为主节点 [root@node1 ~]#vi /etc/my.cnf.d/server.cnf [mysqld] server-id=132 log-bin [root@node1 ~]#systemctl restart mariadb [root@node1 ~]#mysql -uroot -pmagedu MariaDB [(none)]> grant replication slave on *.* to repluser@'192.168.130.%' identified by 'magedu'; MariaDB [(none)]> show master logs; +------------------+-----------+ | Log_name | File_size | +------------------+-----------+ | node1-bin.000001 | 871 | +------------------+-----------+ #node2为从节点 [root@node2 ~]#vi /etc/my.cnf.d/server.cnf [mysqld] server-id=133 [root@node2 ~]#systemctl restart mariadb [root@node2 ~]#mysql -uroot -pmagedu MariaDB [(none)]> help change master to MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST='192.168.130.132', MASTER_USER='repluser', MASTER_PASSWORD='magedu', MASTER_PORT=3306, MASTER_LOG_FILE='node1-bin.000001', MASTER_LOG_POS=871; MariaDB [(none)]> start slave; MariaDB [(none)]> show slave statusG *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.130.132 Master_User: repluser Master_Port: 3306 Connect_Retry: 60 Master_Log_File: node1-bin.000001 Read_Master_Log_Pos: 871 Relay_Log_File: node2-relay-bin.000002 Relay_Log_Pos: 555 Relay_Master_Log_File: node1-bin.000001 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: 871 Relay_Log_Space: 864 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: 11 Master_SSL_Crl: Master_SSL_Crlpath: Using_Gtid: No Gtid_IO_Pos: Replicate_Do_Domain_Ids: Replicate_Ignore_Domain_Ids: Parallel_Mode: conservative SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it 1 row in set (0.00 sec)
上一节中我们完成了一主一从的MariaDB的主从复制架构，但是有的时候，我们期望有一个后备的MariaDB的节点，用只 动词}备份存储数据，不需要对外提供服务。为了实现该功能，我们可以在之前的一主一从架构上进行调整，即将从节点的二进 制文件打开，然后给他配置一个远程同步数据用户，接着使用一台新的服务器做为从服务器的从属，同步从服务器数据的即 柯林斯，下面我们在原来的架构上添加一台新的CentOS7.6，作为节点3，IP地址为192.168.132.134
规划：1主2从
master→slave→slaves
#主节点：node1 192.168.130.132/24 master #从节点1：node2 192.168.130.133/24 级联slave #从节点2：node3 192.168.130.134/24 slaves #所有节点ssh key 互信配置 [root@node1 ~]#ssh-keygen [root@node1 ~]#ssh-copy-id 192.168.130.132 [root@node1 ~]#rsync -av .ssh 192.168.130.133:/root/ [root@node1 ~]#rsync -av .ssh 192.168.130.134:/root/ The authenticity of host '192.168.130.134 (192.168.130.134)' can't be established. ECDSA key fingerprint is SHA256:KrxogcgTSsA07Rp0eYKm2rrqiSFrzLkdpxKMPRnT3Qo. ECDSA key fingerprint is MD5:56:e3:db:1c:e9:f9:00:12:76:45:c8:2d:63:bd:85:d5. Are you sure you want to continue connecting (yes/no)? yes Warning: Permanently added '192.168.130.134' (ECDSA) to the list of known hosts. root@192.168.130.134's password: sending incremental file list .ssh/ .ssh/authorized_keys .ssh/id_rsa .ssh/id_rsa.pub .ssh/known_hosts sent 3,503 bytes received 144 bytes 663.09 bytes/sec total size is 3,161 speedup is 0.87 #master节点 [root@node1 ~]#vi /etc/my.cnf.d/server.cnf [mysqld] server-id=132 log-bin [root@node1 ~]#systemctl restart mariadb [root@node1 ~]#mysql -uroot -pmagedu MariaDB [hellodb]> show master logs; #记录二进制日志位置，给第二节点使用 +------------------+-----------+ | Log_name | File_size | +------------------+-----------+ | node1-bin.000001 | 1045 | | node1-bin.000002 | 9586 | | node1-bin.000003 | 385 | +------------------+-----------+ [root@node1 ~]#mysqldump -uroot -pmagedu -A -F --single-transaction --master-data=1 > /data/all.sql [root@node1 ~]#scp /data/all.sql 192.168.130.133:/data/ [root@node1 ~]#scp /data/all.sql 192.168.130.134:/data/ #中间级联slave节点（node2） [root@node2 ~]#vi /etc/my.cnf.d/server.cnf [mysqld] server-id=21 log-bin read-only log-slave-updates [root@node2 ~]#systemctl restart mariadb #还原数据库 [root@node2 ~]#vi /data/all.sql CHANGE MASTER TO MASTER_HOST='192.168.130.132', MASTER_USER='repluser', MASTER_PASSWORD='magedu', MASTER_PORT=3306, MASTER_LOG_FILE='node1-bin.000003', MASTER_LOG_POS=385; [root@node2 ~]#mysql -uroot -pmagedu MariaDB [(none)]> show variables like 'sql_log_bin'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | sql_log_bin | ON | +---------------+-------+ MariaDB [(none)]> set sql_log_bin=off; MariaDB [(none)]> show variables like 'sql_log_bin'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ MariaDB [mysql]> source /data/all.sql MariaDB [mysql]> show master logs; #记录二进制日志位置，给第三节点使用 +------------------+-----------+ | Log_name | File_size | +------------------+-----------+ | node2-bin.000001 | 351 | | node2-bin.000002 | 351 | | node2-bin.000003 | 328 | +------------------+-----------+ MariaDB [mysql]> set sql_log_bin=on; MariaDB [mysql]> start slave; MariaDB [mysql]> show slave statusG *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.130.132 Master_User: repluser Master_Port: 3306 Connect_Retry: 60 Master_Log_File: node1-bin.000003 Read_Master_Log_Pos: 385 Relay_Log_File: node2-relay-bin.000012 Relay_Log_Pos: 555 Relay_Master_Log_File: node1-bin.000003 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: 385 Relay_Log_Space: 864 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: 11 Master_SSL_Crl: Master_SSL_Crlpath: Using_Gtid: No Gtid_IO_Pos: Replicate_Do_Domain_Ids: Replicate_Ignore_Domain_Ids: Parallel_Mode: conservative SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it 1 row in set (0.00 sec) #在第三个节点上slaves (node3) [root@node3 ~]#vi /etc/my.cnf.d/server.cnf [mysqld] server-id=134 read-onl [root@node3 ~]#systemctl restart mariadb [root@node3 ~]#vi /data/all.sql CHANGE MASTER TO MASTER_HOST='192.168.130.133', MASTER_USER='repluser', MASTER_PASSWORD='magedu', MASTER_PORT=3306, MASTER_LOG_FILE='node2-bin.000003', MASTER_LOG_POS=328; [root@node3 ~]#mysql -uroot -pmagedu < /data/all.sql [root@node3 ~]#mysql -uroot -pmagedu -e 'start slave'; [root@node3 ~]#mysql -uroot -pmagedu -e 'show slave statusG' *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.130.133 Master_User: repluser Master_Port: 3306 Connect_Retry: 60 Master_Log_File: node2-bin.000003 Read_Master_Log_Pos: 453 Relay_Log_File: node3-relay-bin.000002 Relay_Log_Pos: 680 Relay_Master_Log_File: node2-bin.000003 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: 453 Relay_Log_Space: 989 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: 21 Master_SSL_Crl: Master_SSL_Crlpath: Using_Gtid: No Gtid_IO_Pos: Replicate_Do_Domain_Ids: Replicate_Ignore_Domain_Ids: Parallel_Mode: conservative SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
4 MariaDB半同步复制
#master节点 [root@master ~]#vi /etc/my.cnf.d/mariadb-server.cnf [mysqld] server-id=28 log-bin plugin-load-add=semisync_master rpl_semi_sync_master_enabled=on rpl_semi_sync_master_timeout=3000 #设置3s内无法同步，也将返回成功信息给客户端 [root@master ~]#systemctl restart mariadb [root@master ~]#mysql MariaDB [(none)]> grant replication slave on *.* to repluser@'192.168.130.%' identified by 'magedu'; MariaDB [(none)]> show master logs; +--------------------+-----------+ | Log_name | File_size | +--------------------+-----------+ | mariadb-bin.000001 | 28200 | | mariadb-bin.000002 | 367 | | mariadb-bin.000003 | 393 | | mariadb-bin.000004 | 793 | +--------------------+-----------+ MariaDB [(none)]> show global variables like '%semi%'; +---------------------------------------+--------------+ | Variable_name | Value | +---------------------------------------+--------------+ | rpl_semi_sync_master_enabled | ON | | rpl_semi_sync_master_timeout | 3000 | | rpl_semi_sync_master_trace_level | 32 | | rpl_semi_sync_master_wait_no_slave | ON | | rpl_semi_sync_master_wait_point | AFTER_COMMIT | | rpl_semi_sync_slave_delay_master | OFF | | rpl_semi_sync_slave_enabled | OFF | | rpl_semi_sync_slave_kill_conn_timeout | 5 | | rpl_semi_sync_slave_trace_level | 32 | +---------------------------------------+--------------+ MariaDB [(none)]> show global status like '%semi%'; +--------------------------------------------+-------+ | Variable_name | Value | +--------------------------------------------+-------+ | Rpl_semi_sync_master_clients | 0 | | Rpl_semi_sync_master_get_ack | 0 | | Rpl_semi_sync_master_net_avg_wait_time | 0 | | Rpl_semi_sync_master_net_wait_time | 0 | | Rpl_semi_sync_master_net_waits | 0 | | Rpl_semi_sync_master_no_times | 0 | | Rpl_semi_sync_master_no_tx | 0 | | Rpl_semi_sync_master_request_ack | 0 | | Rpl_semi_sync_master_status | ON | | Rpl_semi_sync_master_timefunc_failures | 0 | | Rpl_semi_sync_master_tx_avg_wait_time | 0 | | Rpl_semi_sync_master_tx_wait_time | 0 | | Rpl_semi_sync_master_tx_waits | 0 | | Rpl_semi_sync_master_wait_pos_backtraverse | 0 | | Rpl_semi_sync_master_wait_sessions | 0 | | Rpl_semi_sync_master_yes_tx | 0 | | Rpl_semi_sync_slave_send_ack | 0 | | Rpl_semi_sync_slave_status | OFF | +--------------------------------------------+-------+ 18 rows in set (0.001 sec) #slave节点，启用半同步复制功能 [root@slave ~]#vi /etc/my.cnf.d/mariadb-server.cnf [mysqld] server-id=38 plugin_load_add=semisync_slave rpl_semi_sync_slave_enabled=on [root@slave ~]#systemctl restart mariadb [root@slave ~]#vi /etc/my.cnf.d/mariadb-server.cnf [root@slave ~]#mysql Welcome to the MariaDB monitor. Commands end with ; or g. Your MariaDB connection id is 8 Server version: 10.3.17-MariaDB MariaDB Server Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or 'h' for help. Type 'c' to clear the current input statement. MariaDB [(none)]> show global variables like '%semi%'; +---------------------------------------+--------------+ | Variable_name | Value | +---------------------------------------+--------------+ | rpl_semi_sync_master_enabled | OFF | | rpl_semi_sync_master_timeout | 10000 | | rpl_semi_sync_master_trace_level | 32 | | rpl_semi_sync_master_wait_no_slave | ON | | rpl_semi_sync_master_wait_point | AFTER_COMMIT | | rpl_semi_sync_slave_delay_master | OFF | | rpl_semi_sync_slave_enabled | ON | | rpl_semi_sync_slave_kill_conn_timeout | 5 | | rpl_semi_sync_slave_trace_level | 32 | +---------------------------------------+--------------+ MariaDB [(none)]> help change master to MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST='192.168.130.28', MASTER_USER='repluser', MASTER_PASSWORD='magedu', MASTER_PORT=3306, MASTER_LOG_FILE='mariadb-bin.000004', MASTER_LOG_POS=793; MariaDB [(none)]> start slave; MariaDB [(none)]> show slave statusG *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.130.28 Master_User: repluser Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mariadb-bin.000004 Read_Master_Log_Pos: 793 Relay_Log_File: mariadb-relay-bin.000002 Relay_Log_Pos: 557 Relay_Master_Log_File: mariadb-bin.000004 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: 793 Relay_Log_Space: 868 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: 28 Master_SSL_Crl: Master_SSL_Crlpath: Using_Gtid: No Gtid_IO_Pos: Replicate_Do_Domain_Ids: Replicate_Ignore_Domain_Ids: Parallel_Mode: conservative SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it Slave_DDL_Groups: 0 Slave_Non_Transactional_Groups: 0 Slave_Transactional_Groups: 0 #master节点 MariaDB [(none)]> show global status like '%semi%'; +--------------------------------------------+-------+ | Variable_name | Value | +--------------------------------------------+-------+ | Rpl_semi_sync_master_clients | 1 | | Rpl_semi_sync_master_get_ack | 0 | | Rpl_semi_sync_master_net_avg_wait_time | 0 | | Rpl_semi_sync_master_net_wait_time | 0 | | Rpl_semi_sync_master_net_waits | 0 | | Rpl_semi_sync_master_no_times | 1 | | Rpl_semi_sync_master_no_tx | 2 | | Rpl_semi_sync_master_request_ack | 1 | | Rpl_semi_sync_master_status | ON | | Rpl_semi_sync_master_timefunc_failures | 0 | | Rpl_semi_sync_master_tx_avg_wait_time | 0 | | Rpl_semi_sync_master_tx_wait_time | 0 | | Rpl_semi_sync_master_tx_waits | 0 | | Rpl_semi_sync_master_wait_pos_backtraverse | 0 | | Rpl_semi_sync_master_wait_sessions | 0 | | Rpl_semi_sync_master_yes_tx | 0 | | Rpl_semi_sync_slave_send_ack | 0 | | Rpl_semi_sync_slave_status | OFF | +--------------------------------------------+-------+ #测试 #在master上实现，创建数据库，立即成功 MariaDB [(none)]> create database db1; Query OK, 1 row affected (0.001 sec) #在所有slave节点实现，停止复制线程 MariaDB [(none)]> stop slave; Query OK, 0 rows affected (0.003 sec) #在master上实现，创建数据库，等待3s才能成功 MariaDB [(none)]> create database db2; Query OK, 1 row affected (3.003 sec) #在slave节点实现，恢复复制线程 MariaDB [(none)]> start slave; Query OK, 0 rows affected (0.006 sec) #在master实现，创建数据库，立即成功 MariaDB [db1]> create database db4; Query OK, 1 row affected (0.002 sec)
5 MariaDB高可用方案MHA
准备三台新安装的CentOS7.6服务器，并使用yum安装MariaDB10.2.23，主机名分别为node1，node2，node3， IP地址分别为192.168.130.132-134
#配置mariadb yum源 vi /etc/yum.repos.d/mariadb.repo [mariadb] name=mariadb baseurl=https://mirrors.tuna.tsinghua.edu.cn/mariadb/yum/10.2/centos74-amd64/ enabled=1 gpgcheck=1 gpgkey=https://mirrors.tuna.tsinghua.edu.cn/mariadb/yum/RPM-GPG-KEY-MariaDB #yum安装MariaDB yum install -y mariadb-server systemctl start mariadb
mha4mysql-manager-0.56-0.el6.noarch.rpm mha4mysql-node-0.56-0.el6.noarch.rpm [root@mha-manager ~]#yum install mha4mysql-*.rpm
mha4mysql-node-0.56-0.el6.noarch.rpm [root@master ~]#yum -y install mha4mysql-node-0.56-0.el6.noarch.rpm [root@slave ~]#yum -y install mha4mysql-node-0.56-0.el6.noarch.rpm
[root@mha-manager ~]#ssh-keygen [root@mha-manager ~]#ssh-copy-id 192.168.130.132 [root@mha-manager ~]#rsync -av .ssh 192.168.130.133:/root/ [root@mha-manager ~]#rsync -av .ssh 192.168.130.134:/root/
[root@mha-manager ~]#mkdir -pv /etc/mastermha/ [root@mha-manager ~]#vim /etc/mastermha/app1.cnf [server default] user=mhauser password=magedu manager_workdir=/data/mastermha/app1/ manager_log=/data/mastermha/app1/manager.log remote_workdir=/data/mastermha/app1/ ssh_user=root repl_user=repluser #复制主从用户信息 repl_password=magedu ping_interval=1 master_ip_failover_script=/usr/local/bin/master_ip_failover report_script=/usr/local/bin/sendmail.sh check_repl_delay=0 [server1] hostname=192.168.130.133 [server2] hostname=192.168.130.134 candidate_master=1
相关脚本
[root@mha-manager ~]#vi /usr/local/bin/sendmail.sh #! /bin/bash echo "MySQL is down" | mail -s "MHA Warning" 941268778@qq.com [root@mha-manager ~]#chmod +x /usr/local/bin/sendmail.sh #安装邮件服务包 [root@mha-manager ~]#yum install mailx -y [root@mha-manager ~]#vi /etc/mail.rc set from=941268778@qq.com set smtp=smtp.qq.com set smtp-auth-user=941268778@qq.com set smtp-auth-password=******** #授权码
[root@mha-manager ~]#vi /usr/local/bin/master_ip_failover #创建故障转移IP脚本 #!/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 = '192.168.130.133';#设置Virtual IP my $gateway = '192.168.130.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 " IN SCRIPT TEST====$ssh_stop_vip==$ssh_start_vip=== "; 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 "; &stop_vip(); $exit_code = 0; }; if ($@) { warn "Got Error: $@ "; 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 "; &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 "; `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 "; }
[root@mha-manager ~]#chmod +x /usr/local/bin/master_ip_failover
[root@master ~]#vi /etc/my.cnf.d/server.cnf [mysqld] server-id=21 log-bin skip-name-resolve=1 general-log [root@master ~]#systemctl restart mariadb [root@master ~]#mysql -uroot -pmagedu
MariaDB [(none)]> show master logs;
+-------------------+-----------+
| Log_name | File_size |
+-------------------+-----------+
| master-bin.000001 | 529 |
+-------------------+-----------+
MariaDB [(none)]> grant replication slave on *.* to repluser@'192.168.130.%' identified by 'magedu';
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> grant all on *.* to mhauser@'192.168.130.%' identified by 'magedu';
Query OK, 0 rows affected (0.01 sec)
#配置VIP
[root@master ~]#ifconfig eth0:1 192.168.130.100/24
[root@slave ~]#vi /etc/my.cnf.d/server.cnf [mysqld] server-id=41 log-bin read-only relay-log-purge=0 skip-name-resolve=1 [root@slave ~]#systemctl restart mariadb MariaDB [(none)]> help change master to MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST='192.168.130.133', MASTER_USER='repluser', MASTER_PASSWORD='magedu', MASTER_PORT=3306, MASTER_LOG_FILE='master-bin.000001', MASTER_LOG_POS=529; MariaDB [(none)]> start slave; MariaDB [(none)]> show slave statusG *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.130.133 Master_User: repluser Master_Port: 3306 Connect_Retry: 60 Master_Log_File: master-bin.000001 Read_Master_Log_Pos: 718 Relay_Log_File: slave-relay-bin.000002 Relay_Log_Pos: 945 Relay_Master_Log_File: master-bin.000001 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: 718 Relay_Log_Space: 1254 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: 21 Master_SSL_Crl: Master_SSL_Crlpath: Using_Gtid: No Gtid_IO_Pos: Replicate_Do_Domain_Ids: Replicate_Ignore_Domain_Ids: Parallel_Mode: conservative SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it 1 row in set (0.00 sec)
[root@mha-manager ~]#masterha_check_ssh --conf=/etc/mastermha/app1.cnf Mon Sep 7 18:10:00 2020 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping. Mon Sep 7 18:10:00 2020 - [info] Reading application default configuration from /etc/mastermha/app1.cnf.. Mon Sep 7 18:10:00 2020 - [info] Reading server configuration from /etc/mastermha/app1.cnf.. Mon Sep 7 18:10:00 2020 - [info] Starting SSH connection tests.. Mon Sep 7 18:10:00 2020 - [debug] Mon Sep 7 18:10:00 2020 - [debug] Connecting via SSH from root@192.168.130.133(192.168.130.133:22) to root@192.168.130.134(192.168.130.134:22).. Warning: Permanently added '192.168.130.134' (ECDSA) to the list of known hosts. Mon Sep 7 18:10:00 2020 - [debug] ok. Mon Sep 7 18:10:01 2020 - [debug] Mon Sep 7 18:10:00 2020 - [debug] Connecting via SSH from root@192.168.130.134(192.168.130.134:22) to root@192.168.130.133(192.168.130.133:22).. Mon Sep 7 18:10:01 2020 - [debug] ok. Mon Sep 7 18:10:01 2020 - [info] All SSH connection tests passed successfully.
[root@mha-manager ~]#masterha_check_repl --conf=/etc/mastermha/app1.cnf Mon Sep 7 18:12:14 2020 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping. Mon Sep 7 18:12:14 2020 - [info] Reading application default configuration from /etc/mastermha/app1.cnf.. Mon Sep 7 18:12:14 2020 - [info] Reading server configuration from /etc/mastermha/app1.cnf.. Mon Sep 7 18:12:14 2020 - [info] MHA::MasterMonitor version 0.56. Mon Sep 7 18:12:16 2020 - [info] GTID failover mode = 0 Mon Sep 7 18:12:16 2020 - [info] Dead Servers: Mon Sep 7 18:12:16 2020 - [info] Alive Servers: Mon Sep 7 18:12:16 2020 - [info] 192.168.130.133(192.168.130.133:3306) Mon Sep 7 18:12:16 2020 - [info] 192.168.130.134(192.168.130.134:3306) Mon Sep 7 18:12:16 2020 - [info] Alive Slaves: Mon Sep 7 18:12:16 2020 - [info] 192.168.130.134(192.168.130.134:3306) Version=10.2.33-MariaDB-log (oldest major version between slaves) log-bin:enabled Mon Sep 7 18:12:16 2020 - [info] Replicating from 192.168.130.133(192.168.130.133:3306) Mon Sep 7 18:12:16 2020 - [info] Primary candidate for the new Master (candidate_master is set) Mon Sep 7 18:12:16 2020 - [info] Current Alive Master: 192.168.130.133(192.168.130.133:3306) Mon Sep 7 18:12:16 2020 - [info] Checking slave configurations.. Mon Sep 7 18:12:16 2020 - [info] Checking replication filtering settings.. Mon Sep 7 18:12:16 2020 - [info] binlog_do_db= , binlog_ignore_db= Mon Sep 7 18:12:16 2020 - [info] Replication filtering check ok. Mon Sep 7 18:12:16 2020 - [info] GTID (with auto-pos) is not supported Mon Sep 7 18:12:16 2020 - [info] Starting SSH connection tests.. Mon Sep 7 18:12:17 2020 - [info] All SSH connection tests passed successfully. Mon Sep 7 18:12:17 2020 - [info] Checking MHA Node version.. Mon Sep 7 18:12:17 2020 - [info] Version check ok. Mon Sep 7 18:12:17 2020 - [info] Checking SSH publickey authentication settings on the current master.. Mon Sep 7 18:12:17 2020 - [info] HealthCheck: SSH to 192.168.130.133 is reachable. Mon Sep 7 18:12:17 2020 - [info] Master MHA Node version is 0.56. Mon Sep 7 18:12:17 2020 - [info] Checking recovery script configurations on 192.168.130.133(192.168.130.133:3306).. Mon Sep 7 18:12:17 2020 - [info] Executing command: save_binary_logs --command=test --start_pos=4 --binlog_dir=/var/lib/mysql,/var/log/mysql --output_file=/data/mastermha/app1//save_binary_logs_test --manager_version=0.56 --start_file=master-bin.000001 Mon Sep 7 18:12:17 2020 - [info] Connecting to root@192.168.130.133(192.168.130.133: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 /var/lib/mysql, up to master-bin.000001 Mon Sep 7 18:12:18 2020 - [info] Binlog setting check done. Mon Sep 7 18:12:18 2020 - [info] Checking SSH publickey authentication and checking recovery script configurations on all alive slave servers.. Mon Sep 7 18:12:18 2020 - [info] Executing command : apply_diff_relay_logs --command=test --slave_user='mhauser' --slave_host=192.168.130.134 --slave_ip=192.168.130.134 --slave_port=3306 --workdir=/data/mastermha/app1/ --target_version=10.2.33-MariaDB-log --manager_version=0.56 --relay_log_info=/var/lib/mysql/relay-log.info --relay_dir=/var/lib/mysql/ --slave_pass=xxx Mon Sep 7 18:12:18 2020 - [info] Connecting to root@192.168.130.134(192.168.130.134:22).. Creating directory /data/mastermha/app1/.. done. Checking slave recovery environment settings.. Opening /var/lib/mysql/relay-log.info ... ok. Relay log found at /var/lib/mysql, up to slave-relay-bin.000003 Temporary relay log file is /var/lib/mysql/slave-relay-bin.000003 Testing mysql connection and privileges.. done. Testing mysqlbinlog output.. done. Cleaning up test file(s).. done. Mon Sep 7 18:12:18 2020 - [info] Slaves settings check done. Mon Sep 7 18:12:18 2020 - [info] 192.168.130.133(192.168.130.133:3306) (current master) +--192.168.130.134(192.168.130.134:3306) Mon Sep 7 18:12:18 2020 - [info] Checking replication health on 192.168.130.134.. Mon Sep 7 18:12:18 2020 - [info] ok. Mon Sep 7 18:12:18 2020 - [info] Checking master_ip_failover_script status: Mon Sep 7 18:12:18 2020 - [info] /usr/local/bin/master_ip_failover --command=status --ssh_user=root --orig_master_host=192.168.130.133 --orig_master_ip=192.168.130.133 --orig_master_port=3306 IN SCRIPT TEST====/sbin/ifconfig eth0:1 down==/sbin/ifconfig eth0:1 192.168.130.100;/sbin/arping -I eth0 -c 3 -s 192.168.130.100 192.168.130.2 >/dev/null 2>&1=== Checking the Status of the script.. OK Mon Sep 7 18:12:21 2020 - [info] OK. Mon Sep 7 18:12:21 2020 - [warning] shutdown_script is not defined. Mon Sep 7 18:12:21 2020 - [info] Got exit code 0 (Not master dead). MySQL Replication Health is OK.
#开启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:1668) is running(0:PING_OK), master:192.168.130.133 #查看到健康性检查 [root@master ~]#tail -f /var/lib/mysql/master.log #可以发现1s检查一次活跃度 200907 18:23:34 20 Query SELECT 1 As Value 200907 18:23:35 20 Query SELECT 1 As Value 200907 18:23:36 20 Query SELECT 1 As Value 200907 18:23:37 20 Query SELECT 1 As Value 200907 18:23:38 20 Query SELECT 1 As Value 200907 18:23:39 20 Query SELECT 1 As Value 200907 18:23:40 20 Query SELECT 1 As Value
[root@master ~]#systemctl stop mariadb
MariaDB [(none)]> show slave statusG *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.130.134 Master_User: repluser Master_Port: 3306 Connect_Retry: 60 Master_Log_File: master-bin.000001 Read_Master_Log_Pos: 1045 Relay_Log_File: slave-relay-bin.000002 Relay_Log_Pos: 1272 Relay_Master_Log_File: master-bin.000001 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: 1045 Relay_Log_Space: 1581 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: 21 Master_SSL_Crl: Master_SSL_Crlpath: Using_Gtid: No Gtid_IO_Pos: Replicate_Do_Domain_Ids: Replicate_Ignore_Domain_Ids: Parallel_Mode: conservative SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
由 Master_Host: 192.168.130.134 可知切换成功，MHA完成