MySQL backup and MHA high availability

Time:2021-11-23

1、 Write scripts to allow users to choose whether to use mysqldump or xtraback for full backup.

#!/bin/bash
. /etc/rc.d/init.d/functions
TIME=`date +%F_%H-%M-%S`
DIR=/data/dbbackup
PASS=123456
PS3 = "please enter the backup method number:"
MENU="
Full backup using mysqldump
Full backup with xtraback
sign out
"
[ -d $DIR ] || mkdir -p $DIR
clear
Which MySQL & > / dev / null & & echo ***********************{echo "MySQL is not installed on this computer, so backup is not required."; exit 9;}
select M in $MENU ;do
        case $REPLY in
        1)
                mysqldump -uroot -p"$PASS" -A -F -E -R --triggers --single-transaction --master-data=1 --flush-privileges --default-character-set=utf8 --hex-blob | \
                        gzip > ${DIR}/${TIME}.sql.gz
                [["0" = = ${pipestatus [*]}]] & & action "backup succeeded!" | action "backup failed, please check!" / bin / false
                exit 1
        ;;

        2)
                Which xtrabackup & > / dev / null | {echo "xtrabackup tool is not installed on this machine, so this function cannot be run."; exit 2;}
                xtrabackup -uroot -p"$PASS" --backup --target-dir="${DIR}/${TIME}/" &>/dev/null && \
                {tar pzcf ${dir} / ${time}. Tar. GZ ${dir} / ${time} / & & {action "backup succeeded!"; RM - RF ${dir} / ${time} /;} | action "backup succeeded, failed to package!!!";}||\
                Action "backup failed, please check!" / bin / false
                exit 2
        ;;
        3)
                echo 您已sign out!
                exit
        ;;
        *)
                Echo input error, please re-enter!
        ;;

        esac
        done

2、 Configure MySQL master-slave synchronization.

1. Install two MySQL servers, install the database software, and use MariaDB on CentOS 7.

2. Open the configuration master server, open the binary log and set the unique server ID.

[[email protected] ~]#vim /etc/my.cnf.d/server.cnf
#Add the following:
[mysqld]
server-id=8
log-bin

3. Restart the service.

[[email protected] ~]#systemctl restart mariadb

4. Create an account.

MariaDB [(none)]>  grant replication slave on *.* to [email protected]'10.0.0.%' identified by '123456';
Query OK, 0 rows affected (0.00 sec)

5. View binaries and locations.

MariaDB [(none)]> SHOW MASTER LOGS;
+--------------------+-----------+
| Log_name           | File_size |
+--------------------+-----------+
| mariadb-bin.000001 |       245 |
+--------------------+-----------+

6. Start configuring slave nodes, set unique server ID, and set read-only.

[mysqld]
server_ Id = 200 # sets a global unique ID number for the current node
log-bin
read_ Only = on # sets the database read-only, which is invalid for super user

7. Restart the service.

[[email protected] ~]#systemctl restart mariadb

8. Set the slave node.

[[email protected] ~]#mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 4
Server version: 5.5.68-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)]> CHANGE MASTER TO  MASTER_HOST='10.0.0.242', MASTER_USER='repluser',  MASTER_PASSWORD='123456',  MASTER_PORT=3306,  MASTER_LOG_FILE='mariadb-bin.000001',  MASTER_LOG_POS=245;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> start  slave;
Query OK, 0 rows affected (0.00 sec)

9. This completes the master-slave synchronization setting of MySQL.

3、 Use MHA to achieve high availability of MySQL.
1. To achieve high availability of MHA, four servers need to be deployed.
​ One management terminal does not support centos8.
​ 1 database master server.
​ 2 database slave servers.
All database servers use MariaDB on CentOS 7.

2. Two packages are required to install MHA on the management node. In particular, lower versions of MariaDB and myql should not use MHA version 0.58.

[[email protected] ~]#yum -y install mha4mysql-node-0.56-0.el6.centos.noarch.rpm 
[[email protected] ~]#yum -y install mha4mysql-manager-0.56-0.el6.centos.noarch.rpm

3. All managed nodes need to install mha4mysql node.

[[email protected] ~]#yum -y install mha4mysql-node-0.56-0.el6.centos.noarch.rpm
[[email protected] ~]#yum -y install mha4mysql-node-0.56-0.el6.centos.noarch.rpm
[[email protected] ~]#yum -y install mha4mysql-node-0.56-0.el6.centos.noarch.rpm

4. Implement SSH key authentication between all nodes.

[[email protected] ~]#ssh-keygen 
[[email protected] ~]#ssh-copy-id 10.0.0.242
[[email protected] ~]#ssh 10.0.0.243
[[email protected] ~]#ssh 10.0.0.77
[[email protected] ~]#ssh 10.0.0.17

5. The management node configures the MHA.

[[email protected] ~]#mkdir /etc/mastermha/
[[email protected] ~]#vim /etc/mastermha/app1.cnf
#Add the following:
[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
[server1]
hostname=10.0.0.242
candidate_master=1    
[server2]
hostname=10.0.0.243
candidate_master=1
[server3]
hostname=10.0.0.77

6. Configure the database master node and restart the database service after completion.

[[email protected] ~]#vim /etc/my.cnf.d/server.cnf
[mysqld]
server_id=100
log-bin
skip_name_resolve=1

7. Configure the slave node of the database. The server IDs of different nodes cannot be the same. Restart the database service after completion.

[[email protected] ~]#vim /etc/my.cnf.d/server.cnf
[mysqld]
server_id=200
log-bin
read_only
relay_log_purge=0
skip_name_resolve=1
[[email protected] ~]#systemctl restart mariadb.service

8. The account created by the database master node shall be consistent with the settings in the MHA configuration file.

MariaDB [(none)]>  grant replication slave on *.* to [email protected]'10.0.0.%' identified by '123456';
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> grant all on *.* to [email protected]'10.0.0.%' identified by '123456';
Query OK, 0 rows affected (0.00 sec)

9. The database master node fully backs up the database.

[[email protected] ~]#mysqldump -A -F --single-transaction --master-data=1 > db.sql
#Edit the backup file, find the place where change master to starts, and change it to the following:
[[email protected] ~]#vim db.sql
CHANGE MASTER TO
MASTER_HOST='10.0.0.242',
MASTER_USER='repluser',
MASTER_PASSWORD='123456',
MASTER_PORT=3306,
MASTER_LOG_FILE='mariadb-bin.000008', MASTER_LOG_POS=245;

10. Copy the database backup file to the slave node and import it. Both slave nodes need to execute.

[[email protected] ~]#scp db.sql 10.0.0.77:/root
[[email protected] ~]#scp db.sql 10.0.0.243:/root
[[email protected] ~]#mysql < db.sql
[[email protected] ~]#mysql < db.sql

11. The database slave node starts the synchronization function, and both slave nodes must execute it.

MariaDB [(none)]> start  slave;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.0.0.242
                  Master_User: repluser
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mariadb-bin.000010
          Read_Master_Log_Pos: 340
               Relay_Log_File: mariadb-relay-bin.000010
                Relay_Log_Pos: 626
        Relay_Master_Log_File: mariadb-bin.000010
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
......

12. Check whether the whole MHA environment is normal.

[[email protected] ~]#masterha_check_ssh --conf=/etc/mastermha/app1.cnf
[[email protected] ~]#masterha_check_status --conf=/etc/mastermha/app1.cnf
#If the following error is reported, the MHA version is higher. You can use version 0.56, and version mha0.58 can only be used for versions above MySQL 5.6 (mariadb10.1).
    Checking if super_read_only is defined and turned on..DBD::mysql::st execute failed: Unknown system variable 'super_read_only' at /usr/share/perl5/vendor_perl/MHA/SlaveUtil.pm line 245.
Mon Jan  4 17:56:55 2021 - [error][/usr/share/perl5/vendor_perl/MHA/MasterMonitor.pm, ln208] Slaves settings check failed!
Mon Jan  4 17:56:55 2021 - [error][/usr/share/perl5/vendor_perl/MHA/MasterMonitor.pm, ln416] Slave configuration failed.
Mon Jan  4 17:56:55 2021 - [error][/usr/share/perl5/vendor_perl/MHA/MasterMonitor.pm, ln427] Error happened on checking configurations.  at /usr/bin/masterha_check_repl line 48.
Mon Jan  4 17:56:55 2021 - [error][/usr/share/perl5/vendor_perl/MHA/MasterMonitor.pm, ln525] Error happened on monitoring servers.
Mon Jan  4 17:56:55 2021 - [info] Got exit code 1 (Not master dead).

12. The management node can monitor the database by starting MHA and execute it in the background.

[[email protected] ~]#nohup masterha_manager --conf=/etc/mastermha/app1.cnf &> /dev/null

13. Check MHA status.

[[email protected] ~]#masterha_check_status --conf=/etc/mastermha/app1.cnf  
app1 (pid:33866) is running(0:PING_OK), master:10.0.0.242

14. Fault test, stop the database service of the master node.

[[email protected] ~]#systemctl stop mariadb.service 
#Master of management node_ Manager exited.
#To view the master node for automatic upgrade:
MariaDB [(none)]> show slave status\G
Empty set (0.00 sec)
#View another slave node. The database master node has automatically changed to 10.0.0.243:
MariaDB [(none)]> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.0.0.243
                  Master_User: repluser
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mariadb-bin.000010
          Read_Master_Log_Pos: 245
               Relay_Log_File: mariadb-relay-bin.000002
                Relay_Log_Pos: 531
        Relay_Master_Log_File: mariadb-bin.000010
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
......

15. Delete the read of the upgraded master node MySQL configuration file_ Only, otherwise data cannot be written after the database service of the new master node is restarted.

16. After repairing the original master node, reconfigure it as a slave node, and modify the MHA management side configuration to reuse the MHA monitoring database server.

Recommended Today

Application of observer pattern in design pattern in Ruby Programming

Observer mode(sometimes referred to as publish / subscribe mode) is softwareDesign patternA kind of.In this mode, a target object manages all its dependent observer objects and actively notifies when its own state changes.This is usually achieved by calling the methods provided by each observer. When implementing the observer mode, it should be noted that the […]