MySQL备份及主从复制

一、编写脚本，支持让用户自主选择，使用mysqldump还是xtraback全量备份。

#!/bin/bash while true;do cat << EOF
1) mysqldump
2) xtrabackup
3) quit
EOF read -p "请选择备份方式：" num case $num in 1) mkdir /backup/mysqldump
      mysqldump -A -F --master-data --single-transaction > /backup/mysqldump/fullbsk_`date +%F`.sql echo "成功备份到/backup/mysqldump" exit 1 ;; 2) mkdir /backup/xtraback
      xtrabackup --backup --target-dir=/backup/xtraback/`date +%F`/ &> /dev/null echo "成功备份到/backup/xtraback" exit 2 ;; 3) exit 3 ;; *) ;; esac done 

二、配置Mysql主从同步。

配置MySQL主节点

1.安装mariadb,修改配置文件 [root@centos7 ~]#yum install mariadb-server -y [root@centos7 ~]#vim /etc/my.cnf [mysqld] server-id=7 log-bin=/data/logbin/mysql
innodb_file_per_table=1 ... [root@centos7 ~]#mkdir /data/logbin [root@centos7 ~]#chown mysql.mysql /data/logbin/ [root@centos7 ~]#systemctl start mariadb 2.创建有复制权限的用户账号 [root@centos7 ~]#mysql
MariaDB [(none)]> grant replication slave on *.* to repluser@'192.168.45.%' identified by 'centos'; MariaDB [(none)]> show master logs; +--------------+-----------+ | Log_name     | File_size | +--------------+-----------+ | mysql.000001 | 284 |
| mysql.000002 | 245 | +--------------+-----------+ 3.主节点完整备份，并复制至从节点 [root@centos7 ~]#mysqldump -A -F --master-data=1 --single-transaction > /data/bak.sql [root@centos7 ~]#scp /data/bak.sql 192.168.45.17:/data/ 

配置MySQL从节点

1.安装mariadb,修改配置文件 [root@centos7 ~]#yum install mariadb-server -y [root@centos7 ~]#vim /etc/my.cnf [mysqld] server-id=17 read-only
innodb_file_per_table=1 ... 2.修改主节点备份文件 [root@centos7 ~]#vim /data/bak.sql ... CHANGE MASTER TO
MASTER_HOST='192.168.45.7', MASTER_USER='repluser', MASTER_PASSWORD='centos', MASTER_PORT=3306, MASTER_LOG_FILE='mysql.000002', MASTER_LOG_POS=245; ... 3.导入主节点备份文件，开启slave [root@centos7 ~]#systemctl start mariadb [root@centos7 ~]#mysql < /data/bak.sql [root@centos7 ~]#mysql
MariaDB [(none)]> start slave; MariaDB [(none)]> show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.45.7 Master_User: repluser
                  Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql.000002 Read_Master_Log_Pos: 245 Relay_Log_File: mariadb-relay-bin.000002 Relay_Log_Pos: 525 Relay_Master_Log_File: mysql.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: 245 Relay_Log_Space: 821 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: 7 

测试MySQL主从同步

1.在主节点导入hellodb数据库

2.查看从节点是否正常同步

三、使用MHA实现Mysql高可用。

实验4台server：
管理节点：
MHA manager server 1台，ip：192.168.45.7
被管理节点：
mysql master server 1台，ip：192.168.45.17
mysql slave server 2台，ip：192.168.45.27 192.168.45.37

1. 在管理节点上安装mha4mysql-manager和mha4mysql-node包
   在被管理节点上安装mariadb-server和mha4mysql-node包
2. 配置mysql主节点,并添加账户

[root@master ~]#vim /etc/my.cnf [mysqld] server-id=17 log-bin
skip_name_resolve=1 ... [root@master ~]#systemctl start mariadb [root@master ~]#mysql
MariaDB [(none)]> grant replication slave on *.* to repluser@'192.168.45.%' identified by 'centos'; MariaDB [(none)]> grant all on *.* to mhauser@'192.168.45.%' identified by 'centos'; 

3. 配置mysql从节点

1.配置从节点192.168.45.27 [root@slave27 ~]#vim /etc/my.cnf [mysqld] server-id=27 read-only
log-bin
relay_log_purge=0 skip_name_resolve=1 ... [root@slave27 ~]#systemctl start mariadb [root@slave27 ~]#mysql
MariaDB [(none)]> CHANGE MASTER TO -> MASTER_HOST='192.168.45.17', -> MASTER_USER='repluser', -> MASTER_PASSWORD='centos', -> MASTER_PORT=3306, -> MASTER_LOG_FILE='mariadb-bin.000001', -> MASTER_LOG_POS=245; MariaDB [(none)]> start slave; MariaDB [(none)]> show slave status \G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.45.17 Master_User: repluser
                  Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mariadb-bin.000001 Read_Master_Log_Pos: 540 Relay_Log_File: mariadb-relay-bin.000002 Relay_Log_Pos: 826 Relay_Master_Log_File: mariadb-bin.000001 Slave_IO_Running: Yes
            Slave_SQL_Running: Yes 2.配置从节点192.168.45.37 [root@slave37 ~]#vim /etc/my.cnf [mysqld] server-id=37 read-only
log-bin
relay_log_purge=0 skip_name_resolve=1 ... [root@slave37 ~]#systemctl start mariadb [root@slave37 ~]#mysql
MariaDB [(none)]> CHANGE MASTER TO -> MASTER_HOST='192.168.45.17', -> MASTER_USER='repluser', -> MASTER_PASSWORD='centos', -> MASTER_PORT=3306, -> MASTER_LOG_FILE='mariadb-bin.000001', -> MASTER_LOG_POS=245; MariaDB [(none)]> start slave; MariaDB [(none)]> show slave status \G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.45.17 Master_User: repluser
                  Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mariadb-bin.000001 Read_Master_Log_Pos: 540 Relay_Log_File: mariadb-relay-bin.000002 Relay_Log_Pos: 826 Relay_Master_Log_File: mariadb-bin.000001 Slave_IO_Running: Yes
            Slave_SQL_Running: Yes 

4. 配置MHA管理节点

[root@mha-manager ~]#mkdir /etc/mha [root@mha-manager ~]#vim /etc/mha/app1.cnf [server default] user=mhauser
password=centos 
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=centos 
ping_interval=1 [server1] hostname=192.168.45.17 candidate_master=1 [server2] hostname=192.168.45.27 [server3] hostname=192.168.45.37 candidate_master=1 

5. 在所有节点实现相互之间ssh key验证

在管理节点上生成key，并将生成的key、公私钥拷贝到其余节点/root/.ssh文件夹中 [root@mha-manager ~]#ssh-keygen [root@mha-manager ~]#ssh-copy-id 192.168.45.7 [root@mha-manager ~]#scp -r .ssh 192.168.45.17:/root/ [root@mha-manager ~]#scp -r .ssh 192.168.45.27:/root/ [root@mha-manager ~]#scp -r .ssh 192.168.45.37:/root/ 

6. MHA验证和启动

[root@mha-manager ~]#masterha_check_ssh --conf=/etc/mha/app1.cnf
Fri Aug 7 15:20:18 2020 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping. Fri Aug 7 15:20:18 2020 - [info] Reading application default configuration from /etc/mha/app1.cnf.. Fri Aug 7 15:20:18 2020 - [info] Reading server configuration from /etc/mha/app1.cnf.. Fri Aug 7 15:20:18 2020 - [info] Starting SSH connection tests.. Fri Aug 7 15:20:40 2020 - [debug] Fri Aug 7 15:20:19 2020 - [debug] Connecting via SSH from root@192.168.45.27(192.168.45.27:22) to root@192.168.45.17(192.168.45.17:22).. Fri Aug 7 15:20:29 2020 - [debug] ok. Fri Aug 7 15:20:29 2020 - [debug] Connecting via SSH from root@192.168.45.27(192.168.45.27:22) to root@192.168.45.37(192.168.45.37:22).. Fri Aug 7 15:20:40 2020 - [debug] ok. Fri Aug 7 15:20:51 2020 - [debug] Fri Aug 7 15:20:19 2020 - [debug] Connecting via SSH from root@192.168.45.37(192.168.45.37:22) to root@192.168.45.17(192.168.45.17:22).. Fri Aug 7 15:20:30 2020 - [debug] ok. Fri Aug 7 15:20:30 2020 - [debug] Connecting via SSH from root@192.168.45.37(192.168.45.37:22) to root@192.168.45.27(192.168.45.27:22).. Fri Aug 7 15:20:50 2020 - [debug] ok. Fri Aug 7 15:20:55 2020 - [debug] Fri Aug 7 15:20:18 2020 - [debug] Connecting via SSH from root@192.168.45.17(192.168.45.17:22) to root@192.168.45.27(192.168.45.27:22).. Fri Aug 7 15:20:34 2020 - [debug] ok. Fri Aug 7 15:20:34 2020 - [debug] Connecting via SSH from root@192.168.45.17(192.168.45.17:22) to root@192.168.45.37(192.168.45.37:22).. Fri Aug 7 15:20:54 2020 - [debug] ok. Fri Aug 7 15:20:55 2020 - [info] All SSH connection tests passed successfully. [root@mha-manager ~]#masterha_check_repl --conf=/etc/mha/app1.cnf
Fri Aug 7 15:21:19 2020 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping. Fri Aug 7 15:21:19 2020 - [info] Reading application default configuration from /etc/mha/app1.cnf.. Fri Aug 7 15:21:19 2020 - [info] Reading server configuration from /etc/mha/app1.cnf.. Fri Aug 7 15:21:19 2020 - [info] MHA::MasterMonitor version 0.56. Creating directory /data/mastermha/app1/.. done. Fri Aug 7 15:21:20 2020 - [info] GTID failover mode = 0 Fri Aug 7 15:21:20 2020 - [info] Dead Servers: Fri Aug 7 15:21:20 2020 - [info] Alive Servers: Fri Aug 7 15:21:20 2020 - [info] 192.168.45.17(192.168.45.17:3306) Fri Aug 7 15:21:20 2020 - [info] 192.168.45.27(192.168.45.27:3306) Fri Aug 7 15:21:20 2020 - [info] 192.168.45.37(192.168.45.37:3306) Fri Aug 7 15:21:20 2020 - [info] Alive Slaves: Fri Aug 7 15:21:20 2020 - [info] 192.168.45.27(192.168.45.27:3306) Version=5.5.65-MariaDB (oldest major version between slaves) log-bin:enabled
Fri Aug 7 15:21:20 2020 - [info] Replicating from 192.168.45.17(192.168.45.17:3306) Fri Aug 7 15:21:20 2020 - [info] 192.168.45.37(192.168.45.37:3306) Version=5.5.65-MariaDB (oldest major version between slaves) log-bin:enabled
Fri Aug 7 15:21:20 2020 - [info] Replicating from 192.168.45.17(192.168.45.17:3306) Fri Aug 7 15:21:20 2020 - [info] Primary candidate for the new Master (candidate_master is set) Fri Aug 7 15:21:20 2020 - [info] Current Alive Master: 192.168.45.17(192.168.45.17:3306) Fri Aug 7 15:21:20 2020 - [info] Checking slave configurations.. Fri Aug 7 15:21:20 2020 - [warning] relay_log_purge=0 is not set on slave 192.168.45.27(192.168.45.27:3306). Fri Aug 7 15:21:20 2020 - [warning] relay_log_purge=0 is not set on slave 192.168.45.37(192.168.45.37:3306). Fri Aug 7 15:21:20 2020 - [info] Checking replication filtering settings.. Fri Aug 7 15:21:20 2020 - [info] binlog_do_db= , binlog_ignore_db= Fri Aug 7 15:21:20 2020 - [info] Replication filtering check ok. Fri Aug 7 15:21:20 2020 - [info] GTID (with auto-pos) is not supported
Fri Aug 7 15:21:20 2020 - [info] Starting SSH connection tests.. Fri Aug 7 15:21:42 2020 - [info] All SSH connection tests passed successfully. Fri Aug 7 15:21:42 2020 - [info] Checking MHA Node version.. Fri Aug 7 15:21:42 2020 - [info] Version check ok. Fri Aug 7 15:21:42 2020 - [info] Checking SSH publickey authentication settings on the current master.. Fri Aug 7 15:21:43 2020 - [info] HealthCheck: SSH to 192.168.45.17 is reachable. Fri Aug 7 15:21:43 2020 - [info] Master MHA Node version is 0.56. Fri Aug 7 15:21:43 2020 - [info] Checking recovery script configurations on 192.168.45.17(192.168.45.17:3306).. Fri Aug 7 15:21: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=mariadb-bin.000001 Fri Aug 7 15:21:43 2020 - [info] Connecting to root@192.168.45.17(192.168.45.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 mariadb-bin.000001 Fri Aug 7 15:21:43 2020 - [info] Binlog setting check done. Fri Aug 7 15:21:43 2020 - [info] Checking SSH publickey authentication and checking recovery script configurations on all alive slave servers.. Fri Aug 7 15:21:43 2020 - [info] Executing command : apply_diff_relay_logs --command=test --slave_user='mhauser' --slave_host=192.168.45.27 --slave_ip=192.168.45.27 --slave_port=3306 --workdir=/data/mastermha/app1/ --target_version=5.5.65-MariaDB --manager_version=0.56 --relay_log_info=/var/lib/mysql/relay-log.info --relay_dir=/var/lib/mysql/ --slave_pass=xxx
Fri Aug 7 15:21:43 2020 - [info] Connecting to root@192.168.45.27(192.168.45.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 mariadb-relay-bin.000002 Temporary relay log file is /var/lib/mysql/mariadb-relay-bin.000002 Testing mysql connection and privileges.. done. Testing mysqlbinlog output.. done. Cleaning up test file(s).. done. Fri Aug 7 15:21:43 2020 - [info] Executing command : apply_diff_relay_logs --command=test --slave_user='mhauser' --slave_host=192.168.45.37 --slave_ip=192.168.45.37 --slave_port=3306 --workdir=/data/mastermha/app1/ --target_version=5.5.65-MariaDB --manager_version=0.56 --relay_log_info=/var/lib/mysql/relay-log.info --relay_dir=/var/lib/mysql/ --slave_pass=xxx
Fri Aug 7 15:21:43 2020 - [info] Connecting to root@192.168.45.37(192.168.45.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 mariadb-relay-bin.000002 Temporary relay log file is /var/lib/mysql/mariadb-relay-bin.000002 Testing mysql connection and privileges.. done. Testing mysqlbinlog output.. done. Cleaning up test file(s).. done. Fri Aug 7 15:21:43 2020 - [info] Slaves settings check done. Fri Aug 7 15:21:43 2020 - [info] 192.168.45.17(192.168.45.17:3306) (current master) +--192.168.45.27(192.168.45.27:3306) +--192.168.45.37(192.168.45.37:3306) Fri Aug 7 15:21:43 2020 - [info] Checking replication health on 192.168.45.27.. Fri Aug 7 15:21:43 2020 - [info] ok. Fri Aug 7 15:21:43 2020 - [info] Checking replication health on 192.168.45.37.. Fri Aug 7 15:21:43 2020 - [info] ok. Fri Aug 7 15:21:43 2020 - [warning] master_ip_failover_script is not defined. Fri Aug 7 15:21:43 2020 - [warning] shutdown_script is not defined. Fri Aug 7 15:21:43 2020 - [info] Got exit code 0 (Not master dead). MySQL Replication Health is OK. [root@mha-manager ~]#masterha_manager --conf=/etc/mha/app1.cnf
Fri Aug 7 15:23:01 2020 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping. Fri Aug 7 15:23:01 2020 - [info] Reading application default configuration from /etc/mha/app1.cnf.. Fri Aug 7 15:23:01 2020 - [info] Reading server configuration from /etc/mha/app1.cnf.. 

7. 测试MHA高可用性

关闭mysql主节点mariadb服务

[root@master ~]#systemctl stop mariadb 

从192.168.45.27上查看

MariaDB [(none)]> show slave status \G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.45.37 Master_User: repluser
                  Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mariadb-bin.000001 Read_Master_Log_Pos: 245 Relay_Log_File: mariadb-relay-bin.000002 Relay_Log_Pos: 531 Relay_Master_Log_File: mariadb-bin.000001 Slave_IO_Running: Yes
            Slave_SQL_Running: Yes 

从管理节点的日志可以看到mysql主节点已经从192.168.45.17切换到192.168.45.37

[root@mha-manager ~]#tail -f /data/mastermha/app1/manager.log ... ----- Failover Report ----- app1: MySQL Master failover 192.168.45.17(192.168.45.17:3306) to 192.168.45.37(192.168.45.37:3306) succeeded

Master 192.168.45.17(192.168.45.17:3306) is down! Check MHA Manager logs at mha-manager:/data/mastermha/app1/manager.log for details. Started automated(non-interactive) failover. The latest slave 192.168.45.27(192.168.45.27:3306) has all relay logs for recovery. Selected 192.168.45.37(192.168.45.37:3306) as a new master. 192.168.45.37(192.168.45.37:3306): OK: Applying all logs succeeded. 192.168.45.27(192.168.45.27:3306): This host has the latest relay log events. Generating relay diff files from the latest slave succeeded. 192.168.45.27(192.168.45.27:3306): OK: Applying all logs succeeded. Slave started, replicating from 192.168.45.37(192.168.45.37:3306) 192.168.45.37(192.168.45.37:3306): Resetting slave info succeeded. Master failover to 192.168.45.37(192.168.45.37:3306) completed successfully. 

本文地址：https://blog.csdn.net/weixin_42573182/article/details/107780140