1 MariaDB主从复制原理
主从复制是一个异步复制的过程,简单来说就是主库发送更新事件到从库,从库读取更新记录,并执行更新记录,使得从库的内容与主库一致. 主从复制的过程分为几个点: 1.客户端更新数据库,master收到数据更新事件,然后写入到binlog 2.master创建一个binlog dump trread 线程,把binlog的内容发送到从库. 3.slave启动并发起连接,连接到主库 4.slave启动后,创建一个I/O线程,读取主库传过来的binlog的内容,写入到relay log(中继日志) 5.然后slave会创建一个SQL线程,从relay log中读取日志事件,从Exec_Master_log_Pos位置开始执行读取到的更新时间,将更新内容重写到slave的db中
2 MariaDB一主一从架构构建
准备两台纯新的CentOS7.6服务器，其中主机称为node1的服务器地址为192.168.130.132，主机称为node2的服务器地址为192.168.130.133
master:192.168.130.132 [root@node1 ~]#yum install mariadb-server -y [root@node1 ~]#vim /etc/my.cnf [mysqld] log-bin=/data/mysql=mysql-bin server-id=132[root@node1 ~]#mkdir /data/mysql [root@node1 ~]#chown -R mysql.mysql /data/mysql/ [root@node1 ~]#systemctl enable --now mariadb.service [root@node1 ~]#mysql MariaDB [(none)]> grant replication slave on *.* to 'repluser'@'192.168.130.%' identified by 'replpass'; MariaDB [(none)]> show master logs; +-------------------+-----------+ | Log_name | File_size | +-------------------+-----------+ | master-bin.000001 | 264 | | master-bin.000002 | 400 | +-------------------+-----------+ 2 rows in set (0.00 sec)
slave1:192.168.130.133
[root@node2 ~]#yum install mariadb-server -y [root@node2 ~]#vim /etc/my.cnf [mysqld] server-id=133 log-bin=/data/mysql/mysql-bin read-only=on [root@node2 ~]#mkdir /data/mysql [root@node2 ~]#chown -R mysql.mysql /data/mysql/ [root@node2 ~]#systemctl enable --now mariadb.service [root@node2 ~]#mysql MariaDB [(none)]> help CHANGE MASTER TO MariaDB [(none)]> CHANGE MASTER TO -> MASTER_HOST='192.168.130.132', -> MASTER_USER='repluser', -> MASTER_PASSWORD='replpass', -> MASTER_PORT=3306, -> MASTER_LOG_FILE='master-bin.000002', -> MASTER_LOG_POS=400; Query OK, 0 rows affected (0.01 sec) 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: master-bin.000002 Read_Master_Log_Pos: 400 Relay_Log_File: relay-log.000002 Relay_Log_Pos: 530 Relay_Master_Log_File: master-bin.000002 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: 400 Relay_Log_Space: 818 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: 132 1 row in set (0.00 sec)
master 导入数据库 [root@node1 ~]#mysql < hellodb_innodb.sql slave1 查看库 [root@node2 ~]#mysql MariaDB [(none)]> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | hellodb | | mysql | | performance_schema | | test | +--------------------+ 5 rows in set (0.00 sec)
数据同步成功,收工!
3 MariaDB级联复制
上一节中我们完成了一主一从的MariaDB的主从复制架构，但是有的时候，我们期望有一个后备的MariaDB的节点，只用来备份存储数据，不需要对外提供服务。为了实现该功能，我们可以在之前的一主一从架构上进行调整，即将从节点的二进制文件打开，然后给他配置一个远程同步数据用户，接着使用一台新的服务器做为从服务器的从属，同步从服务器数据的即可，下面我们在原来的架构上添加一台新的CentOS7.6，作为节点3，IP地址为192.168.132.134
slave1:192.168.130.133 [root@node2 ~]#vim /etc/my.cnf [mysqld] log_slave_updates ##添加这行,级联复制必要 [root@node2 ~]#systemctl restart mariadb.service [root@node2 ~]#mysqldump -A -F --single-transaction --master-data=1 > /data/all.sql [root@node2 ~]#scp /data/all.sql 10.0.0.27:/data [root@node2 ~]#mysql MariaDB [(none)]> grant replication slave on *.* to 'repluser'@'192.168.132.%' identified by '123456'; MariaDB [(none)]> show master logs; +--------------------+-----------+ | Log_name | File_size | +--------------------+-----------+ | mariadb-bin.000001 | 264 | | mariadb-bin.000002 | 398 | +--------------------+-----------+ 2 rows in set (0.00 sec) slave2:192.168.130.134 [root@node3 ~]#yum install mariadb-server -y [root@node3 ~]#vim /etc/my.cnf [mysqld] server-id=134 read-only [root@node3 ~]#systemctl enable --now mariadb.service [root@node3 ~]#vim /data/all.sql CHANGE MASTER TO MASTER_HOST='192.168.132.133', MASTER_USER='repluser', MASTER_PASSWORD='123456', MASTER_PORT=3306, MASTER_LOG_FILE='mariadb-bin.000002', MASTER_LOG_POS=398; [root@node3 ~]#mysql < /data/all.sql [root@node3 ~]#mysql MariaDB [(none)]> start slave; Query OK, 0 rows affected (0.00 sec) MariaDB [(none)]> show slave statusG; *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.132.133 Master_User: repluser Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mariadb-bin.000002 Read_Master_Log_Pos: 398 Relay_Log_File: mariadb-relay-bin.000002 Relay_Log_Pos: 531 Relay_Master_Log_File: mariadb-bin.000002 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: 398 Relay_Log_Space: 827 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: 133 1 row in set (0.00 sec)
4 MariaDB半同步复制
Master:192.168.130.132
MariaDB [(none)]> install plugin rpl_semi_sync_master soname 'semisync_master.so'; MariaDB [(none)]> set global rpl_semi_sync_master_enabled=1; MariaDB [(none)]> set global rpl_semi_sync_master_timeout=1000; MariaDB [(none)]> show global variables like '%semi%'; +------------------------------------+-------+ | Variable_name | Value | +------------------------------------+-------+ | rpl_semi_sync_master_enabled | ON | | rpl_semi_sync_master_timeout | 1000 | | rpl_semi_sync_master_trace_level | 32 | | rpl_semi_sync_master_wait_no_slave | ON | +------------------------------------+-------+ 4 rows in set (0.00 sec) vim /etc/my.cnf [mysqld] rpl_semi_sync_master_enabled=1 rpl_semi_sync_master_timeout=1000
systemctl restart mariadb
slave1:192.168.130.133 MariaDB [(none)]> install plugin rpl_semi_sync_slave soname 'semisync_slave.so'; MariaDB [(none)]> set global rpl_semi_sync_slave_enabled=1; vim /etc/my.cnf [mysqld] rpl_semi_sync_slave_enabled=1 MariaDB [(none)]> show global status like '%semi%'; +----------------------------+-------+ | Variable_name | Value | +----------------------------+-------+ | Rpl_semi_sync_slave_status | ON | +----------------------------+-------+ 1 row in set (0.00 sec) MariaDB [(none)]> show global variables like '%semi%'; +---------------------------------+-------+ | Variable_name | Value | +---------------------------------+-------+ | rpl_semi_sync_slave_enabled | ON | | rpl_semi_sync_slave_trace_level | 32 | +---------------------------------+-------+ 2 rows in set (0.00 sec)
master:192.168.130.132
MariaDB [(none)]> show global status like '%semi%'; +--------------------------------------------+-------+ | Variable_name | Value | +--------------------------------------------+-------+ | Rpl_semi_sync_master_clients | 1 | | 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_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 | +--------------------------------------------+-------+ 14 rows in set (0.00 sec)
5 MariaDB高可用方案MHA
准备三台新安装的CentOS7.6服务器，并使用yum安装MariaDB10.2.23，主机名分别为node1，node2，node3，IP地址分别为192.168.130.132-134
node1:192.168.130.132
[root@localhost ~]#vim /etc/yum.repos.d/mariadb.repo [mariadb] name = MariaDB baseurl = http://mirrors.ustc.edu.cn/mariadb/yum/10.2/centos7-amd64 gpgkey=http://mirrors.ustc.edu.cn/mariadb/yum/RPM-GPG-KEY-MariaDB gpgcheck=1 [root@localhost ~]#yum clean all [root@localhost ~]#yum install mariadb-server -y
node2:192.168.130.133
[root@localhost ~]#vim /etc/yum.repos.d/mariadb.repo [mariadb] name = MariaDB baseurl = http://mirrors.ustc.edu.cn/mariadb/yum/10.2/centos7-amd64 gpgkey=http://mirrors.ustc.edu.cn/mariadb/yum/RPM-GPG-KEY-MariaDB gpgcheck=1 [root@localhost ~]#yum clean all [root@localhost ~]#yum install mariadb-server -y
node3:192.168.130.134
[root@localhost ~]#vim /etc/yum.repos.d/mariadb.repo [mariadb] name = MariaDB baseurl = http://mirrors.ustc.edu.cn/mariadb/yum/10.2/centos7-amd64 gpgkey=http://mirrors.ustc.edu.cn/mariadb/yum/RPM-GPG-KEY-MariaDB gpgcheck=1 [root@localhost ~]#yum clean all [root@localhost ~]#yum install mariadb-server -y
部署MHA
Manager:
[root@manager ~]#yum install -y mha4mysql-node-0.56-0.el6.noarch.rpm mha4mysql-manager-0.56-0.el6.noarch.rpm
Node: [root@node1 ~]#yum install mha4mysql-node-0.56-0.el6.noarch.rpm -y [root@node2 ~]#yum install mha4mysql-node-0.56-0.el6.noarch.rpm -y [root@node3 ~]#yum install mha4mysql-node-0.56-0.el6.noarch.rpm -y Manager: [root@manager ~]#ssh-keygen [root@manager ~]#ssh-copy-id 10.0.0.7 [root@manager ~]#rsync -av .ssh 10.0.0.17:/root [root@manager ~]#rsync -av .ssh 10.0.0.27:/root [root@manager ~]#rsync -av .ssh 10.0.0.37:/root [root@manager ~]#mkdir /etc/mastermha [root@manager ~]#vim /etc/mastermha/app1.cnf [server default] user=mhauser password=123456 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=123456 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=10.0.0.17 [server2] hostname=10.0.0.27 [server3] hostname=10.0.0.37 candidate_master=1 [root@manager ~]#cd /usr/local/bin/ [root@manager /usr/local/bin]#cat sendmail.sh echo "MySQL is down" | mail -s "MHA Warning" 158086268@qq.com [root@manager /usr/local/bin]#chmod +x sendmail.sh [root@manager /usr/local/bin]#chmod +x master_ip_failover master [root@node1 ~]#vim /etc/my.cnf.d/server.cnf [mysqld] server-id=17 log-bin skip_name_resolve=1 general_log [root@node1 ~]#systemctl enable --now mariadb [root@node1 ~]#mysql MariaDB [(none)]> show master logs; +------------------+-----------+ | Log_name | File_size | +------------------+-----------+ | node1-bin.000002 | 342 | +------------------+-----------+ 1 row in set (0.00 sec) MariaDB [(none)]> grant replication slave on *.* to 'repluser'@'10.0.0.%' identified by '123456'; Query OK, 0 rows affected (0.00 sec) MariaDB [(none)]> grant all on *.* to mhauser@'10.0.0.%' identified by '123456'; Query OK, 0 rows affected (0.00 sec) MariaDB [(none)]> exit Bye [root@node1 ~]#ifconfig eth0:1 10.0.0.100/24 [root@node1 ~]#ip a 1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN group default qlen 1000 link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00 inet 127.0.0.1/8 scope host lo valid_lft forever preferred_lft forever inet6 ::1/128 scope host valid_lft forever preferred_lft forever 2: eth0: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP group default qlen 1000 link/ether 00:0c:29:20:8f:47 brd ff:ff:ff:ff:ff:ff inet 10.0.0.17/24 brd 10.0.0.255 scope global noprefixroute eth0 valid_lft forever preferred_lft forever inet 10.0.0.100/24 brd 10.0.0.255 scope global secondary eth0:1 valid_lft forever preferred_lft forever inet6 fe80::20c:29ff:fe20:8f47/64 scope link valid_lft forever preferred_lft forever slave1 [root@node2 ~]#vim /etc/my.cnf.d/server.cnf [mysqld] server-id=27 log-bin read_only relay_log_purge=0 skip_name_resolve=1 [root@node2 ~]#systemctl enable --now mariadb [root@node2 ~]#mysql MariaDB [(none)]> CHANGE MASTER TO -> MASTER_HOST='10.0.0.17', -> MASTER_USER='repluser', -> MASTER_PASSWORD='123456', -> MASTER_PORT=3306, -> MASTER_LOG_FILE='node1-bin.000002', -> MASTER_LOG_POS=342 ; Query OK, 0 rows affected (0.00 sec) MariaDB [(none)]> start slave; Query OK, 0 rows affected (0.00 sec) MariaDB [(none)]> show slave statusG; *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 10.0.0.17 Master_User: repluser Master_Port: 3306 Connect_Retry: 60 Master_Log_File: node1-bin.000002 Read_Master_Log_Pos: 723 Relay_Log_File: node2-relay-bin.000002 Relay_Log_Pos: 936 Relay_Master_Log_File: node1-bin.000002 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: 723 Relay_Log_Space: 1245 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: 17 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) ERROR: No query specified slave2 [root@node3 ~]#vim /etc/my.cnf.d/server.cnf [mysqld] server-id=37 log-bin read_only relay_log_purge=0 skip_name_resolve=1 [root@node3 ~]#systemctl enable --now mariadb [root@node3 ~]#mysql MariaDB [(none)]> CHANGE MASTER TO -> MASTER_HOST='10.0.0.17', -> MASTER_USER='repluser', -> MASTER_PASSWORD='123456', -> MASTER_PORT=3306, -> MASTER_LOG_FILE='node1-bin.000002', -> MASTER_LOG_POS=342 ; Query OK, 0 rows affected (0.00 sec) MariaDB [(none)]> start slave; Query OK, 0 rows affected (0.00 sec) MariaDB [(none)]> show slave statusG; *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 10.0.0.17 Master_User: repluser Master_Port: 3306 Connect_Retry: 60 Master_Log_File: node1-bin.000002 Read_Master_Log_Pos: 723 Relay_Log_File: node3-relay-bin.000002 Relay_Log_Pos: 936 Relay_Master_Log_File: node1-bin.000002 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: 723 Relay_Log_Space: 1245 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: 17 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) ERROR: No query specified manager [root@manager ~]#masterha_check_ssh --conf=/etc/mastermha/app1.cnf Tue Sep 1 19:15:08 2020 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping. Tue Sep 1 19:15:08 2020 - [info] Reading application default configuration from /etc/mastermha/app1.cnf.. Tue Sep 1 19:15:08 2020 - [info] Reading server configuration from /etc/mastermha/app1.cnf.. Tue Sep 1 19:15:08 2020 - [info] Starting SSH connection tests.. Tue Sep 1 19:15:09 2020 - [debug] Tue Sep 1 19:15:08 2020 - [debug] Connecting via SSH from root@10.0.0.17(10.0.0.17:22) to root@10.0.0.27(10.0.0.27:22).. Warning: Permanently added '10.0.0.27' (ECDSA) to the list of known hosts. Tue Sep 1 19:15:08 2020 - [debug] ok. Tue Sep 1 19:15:08 2020 - [debug] Connecting via SSH from root@10.0.0.17(10.0.0.17:22) to root@10.0.0.37(10.0.0.37:22).. Warning: Permanently added '10.0.0.37' (ECDSA) to the list of known hosts. Tue Sep 1 19:15:08 2020 - [debug] ok. Tue Sep 1 19:15:09 2020 - [debug] Tue Sep 1 19:15:08 2020 - [debug] Connecting via SSH from root@10.0.0.27(10.0.0.27:22) to root@10.0.0.17(10.0.0.17:22).. Tue Sep 1 19:15:09 2020 - [debug] ok. Tue Sep 1 19:15:09 2020 - [debug] Connecting via SSH from root@10.0.0.27(10.0.0.27:22) to root@10.0.0.37(10.0.0.37:22).. Warning: Permanently added '10.0.0.37' (ECDSA) to the list of known hosts. Tue Sep 1 19:15:09 2020 - [debug] ok. Tue Sep 1 19:15:09 2020 - [debug] Tue Sep 1 19:15:09 2020 - [debug] Connecting via SSH from root@10.0.0.37(10.0.0.37:22) to root@10.0.0.17(10.0.0.17:22).. Tue Sep 1 19:15:09 2020 - [debug] ok. Tue Sep 1 19:15:09 2020 - [debug] Connecting via SSH from root@10.0.0.37(10.0.0.37:22) to root@10.0.0.27(10.0.0.27:22).. Tue Sep 1 19:15:09 2020 - [debug] ok. Tue Sep 1 19:15:09 2020 - [info] All SSH connection tests passed successfully. [root@manager ~]#masterha_check_repl --conf=/etc/mastermha/app1.cnf Tue Sep 1 19:15:39 2020 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping. Tue Sep 1 19:15:39 2020 - [info] Reading application default configuration from /etc/mastermha/app1.cnf.. Tue Sep 1 19:15:39 2020 - [info] Reading server configuration from /etc/mastermha/app1.cnf.. Tue Sep 1 19:15:39 2020 - [info] MHA::MasterMonitor version 0.56. Creating directory /data/mastermha/app1/.. done. Tue Sep 1 19:15:41 2020 - [info] GTID failover mode = 0 Tue Sep 1 19:15:41 2020 - [info] Dead Servers: Tue Sep 1 19:15:41 2020 - [info] Alive Servers: Tue Sep 1 19:15:41 2020 - [info] 10.0.0.17(10.0.0.17:3306) Tue Sep 1 19:15:41 2020 - [info] 10.0.0.27(10.0.0.27:3306) Tue Sep 1 19:15:41 2020 - [info] 10.0.0.37(10.0.0.37:3306) Tue Sep 1 19:15:41 2020 - [info] Alive Slaves: Tue Sep 1 19:15:41 2020 - [info] 10.0.0.27(10.0.0.27:3306) Version=10.2.33-MariaDB-log (oldest major version between slaves) log-bin:enabled Tue Sep 1 19:15:41 2020 - [info] Replicating from 10.0.0.17(10.0.0.17:3306) Tue Sep 1 19:15:41 2020 - [info] 10.0.0.37(10.0.0.37:3306) Version=10.2.33-MariaDB-log (oldest major version between slaves) log-bin:enabled Tue Sep 1 19:15:41 2020 - [info] Replicating from 10.0.0.17(10.0.0.17:3306) Tue Sep 1 19:15:41 2020 - [info] Primary candidate for the new Master (candidate_master is set) Tue Sep 1 19:15:41 2020 - [info] Current Alive Master: 10.0.0.17(10.0.0.17:3306) Tue Sep 1 19:15:41 2020 - [info] Checking slave configurations.. Tue Sep 1 19:15:41 2020 - [info] Checking replication filtering settings.. Tue Sep 1 19:15:41 2020 - [info] binlog_do_db= , binlog_ignore_db= Tue Sep 1 19:15:41 2020 - [info] Replication filtering check ok. Tue Sep 1 19:15:41 2020 - [info] GTID (with auto-pos) is not supported Tue Sep 1 19:15:41 2020 - [info] Starting SSH connection tests.. Tue Sep 1 19:15:42 2020 - [info] All SSH connection tests passed successfully. Tue Sep 1 19:15:42 2020 - [info] Checking MHA Node version.. Tue Sep 1 19:15:42 2020 - [info] Version check ok. Tue Sep 1 19:15:42 2020 - [info] Checking SSH publickey authentication settings on the current master.. Tue Sep 1 19:15:43 2020 - [info] HealthCheck: SSH to 10.0.0.17 is reachable. Tue Sep 1 19:15:43 2020 - [info] Master MHA Node version is 0.56. Tue Sep 1 19:15:43 2020 - [info] Checking recovery script configurations on 10.0.0.17(10.0.0.17:3306).. Tue Sep 1 19:15:43 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=node1-bin.000002 Tue Sep 1 19:15:43 2020 - [info] Connecting to root@10.0.0.17(10.0.0.17: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 node1-bin.000002 Tue Sep 1 19:15:43 2020 - [info] Binlog setting check done. Tue Sep 1 19:15:43 2020 - [info] Checking SSH publickey authentication and checking recovery script configurations on all alive slave servers.. Tue Sep 1 19:15:43 2020 - [info] Executing command : apply_diff_relay_logs --command=test --slave_user='mhauser' --slave_host=10.0.0.27 --slave_ip=10.0.0.27 --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 Tue Sep 1 19:15:43 2020 - [info] Connecting to root@10.0.0.27(10.0.0.27: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 node2-relay-bin.000002 Temporary relay log file is /var/lib/mysql/node2-relay-bin.000002 Testing mysql connection and privileges.. done. Testing mysqlbinlog output.. done. Cleaning up test file(s).. done. Tue Sep 1 19:15:43 2020 - [info] Executing command : apply_diff_relay_logs --command=test --slave_user='mhauser' --slave_host=10.0.0.37 --slave_ip=10.0.0.37 --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 Tue Sep 1 19:15:43 2020 - [info] Connecting to root@10.0.0.37(10.0.0.37: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 node3-relay-bin.000002 Temporary relay log file is /var/lib/mysql/node3-relay-bin.000002 Testing mysql connection and privileges.. done. Testing mysqlbinlog output.. done. Cleaning up test file(s).. done. Tue Sep 1 19:15:43 2020 - [info] Slaves settings check done. Tue Sep 1 19:15:43 2020 - [info] 10.0.0.17(10.0.0.17:3306) (current master) +--10.0.0.27(10.0.0.27:3306) +--10.0.0.37(10.0.0.37:3306) Tue Sep 1 19:15:43 2020 - [info] Checking replication health on 10.0.0.27.. Tue Sep 1 19:15:43 2020 - [info] ok. Tue Sep 1 19:15:43 2020 - [info] Checking replication health on 10.0.0.37.. Tue Sep 1 19:15:43 2020 - [info] ok. Tue Sep 1 19:15:43 2020 - [info] Checking master_ip_failover_script status: Tue Sep 1 19:15:43 2020 - [info] /usr/local/bin/master_ip_failover --command=status --ssh_user=root --orig_master_host=10.0.0.17 --orig_master_ip=10.0.0.17 --orig_master_port=3306 IN SCRIPT TEST====/sbin/ifconfig eth0:1 down==/sbin/ifconfig eth0:1 10.0.0.100;/sbin/arping -I eth0 -c 3 -s 10.0.0.100 10.0.0.254 >/dev/null 2>&1=== Checking the Status of the script.. OK Tue Sep 1 19:15:46 2020 - [info] OK. Tue Sep 1 19:15:46 2020 - [warning] shutdown_script is not defined. Tue Sep 1 19:15:46 2020 - [info] Got exit code 0 (Not master dead). MySQL Replication Health is OK.
启动MHA:
[root@manager ~]#masterha_manager --conf=/etc/mastermha/app1.cnf
Tue Sep 1 19:23:22 2020 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Tue Sep 1 19:23:22 2020 - [info] Reading application default configuration from /etc/mastermha/app1.cnf..
Tue Sep 1 19:23:22 2020 - [info] Reading server configuration from /etc/mastermha/app1.cnf..
[root@manager ~]#masterha_check_status --conf=/etc/mastermha/app1.cnf
app1 (pid:26714) is running(0:PING_OK), master:10.0.0.17
测试master down后
[root@node1 ~]#systemctl stop mariadb.service [root@manager ~]#cat /data/mastermha/app1/manager.log ... ----- Failover Report ----- app1: MySQL Master failover 10.0.0.17(10.0.0.17:3306) to 10.0.0.37(10.0.0.37:3306) succeeded Master 10.0.0.17(10.0.0.17:3306) is down! Check MHA Manager logs at manager:/data/mastermha/app1/manager.log for details. Started automated(non-interactive) failover. Invalidated master IP address on 10.0.0.17(10.0.0.17:3306) The latest slave 10.0.0.27(10.0.0.27:3306) has all relay logs for recovery. Selected 10.0.0.37(10.0.0.37:3306) as a new master. 10.0.0.37(10.0.0.37:3306): OK: Applying all logs succeeded. 10.0.0.37(10.0.0.37:3306): OK: Activated master IP address. 10.0.0.27(10.0.0.27:3306): This host has the latest relay log events. Generating relay diff files from the latest slave succeeded. 10.0.0.27(10.0.0.27:3306): OK: Applying all logs succeeded. Slave started, replicating from 10.0.0.37(10.0.0.37:3306) 10.0.0.37(10.0.0.37:3306): Resetting slave info succeeded. Master failover to 10.0.0.37(10.0.0.37:3306) completed successfully. Tue Sep 1 19:27:35 2020 - [info] Sending mail..
邮件已接收到:
验证VIP是否漂到新master:
[root@node3 ~]#ip a 1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN group default qlen 1000 link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00 inet 127.0.0.1/8 scope host lo valid_lft forever preferred_lft forever inet6 ::1/128 scope host valid_lft forever preferred_lft forever 2: eth0: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP group default qlen 1000 link/ether 00:0c:29:c0:95:f2 brd ff:ff:ff:ff:ff:ff inet 10.0.0.37/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:fec0:95f2/64 scope link valid_lft forever preferred_lft forever
完成,收工!