第十五周

最新推荐文章于 2022-08-20 19:07:01 发布

真nan

最新推荐文章于 2022-08-20 19:07:01 发布

阅读量84

点赞数

本文链接：https://blog.csdn.net/qq_33404434/article/details/109125306

版权

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

#!/bin/bash

date=`date +%F`
mbakdir=/data/backup_mysql
xbakdir=/data/backup_xtrabackup
PS3="Please input your backup tool: "

select menu in mysqldump xtrabackup quit; do
case $REPLY in  
1)
	mysqldump  -A --master-data=2 -F --single-transaction > $mbakdir/$date.sql
	echo "databases backup in $mbakdir/$date.sql."
	break
	;;
2)
	xtrabackup --backup --target-dir=$xbakdir &>/dev/null
	echo "databases backup in $xbakdir."
	break
	;;

3)
	break
	;;
*)
	echo "input error!"
esac
done

2 配置Mysql主从同步。

mysql主IP：192.168.37.207，mysql从IP：192.168.37.217。
修改主配置文件

[root@localhost ~]# vim /etc/my.cnf
[mysqld]
server_id=1                  #设置全局唯一ID号
log_bin                      #启动二进制日志

添加具有复制权限的账号，查看当前二进制日志的位置。

MariaDB [(none)]> show master logs;
+--------------------+-----------+
| Log_name           | File_size |
+--------------------+-----------+
| mariadb-bin.000001 |       245 |
+--------------------+-----------+

MariaDB [(none)]> grant replication slave on *.* to repluser@'192.168.37.%' identified by 'password';
Query OK, 0 rows affected (0.00 sec)

修改从配置文件

[root@localhost ~]# vim /etc/my.cnf
[mysqld]
server_id=2                  #设置全局唯一ID号

使用有复制权限的账号连接主服务器，启动复制线程，查看从服务器状态。

MariaDB [(none)]> change master to master_host='192.168.37.207',
    -> master_user='repluser',
    -> master_password='password',
    -> 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.37.207
                  Master_User: repluser
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mariadb-bin.000001
          Read_Master_Log_Pos: 402
               Relay_Log_File: mariadb-relay-bin.000002
                Relay_Log_Pos: 688
        Relay_Master_Log_File: mariadb-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
......

3 使用MHA实现Mysql高可用。

MHA管理节点：192.168.37.207，mysql主节点：192.168.37.217，mysql两从节点：192.168.37.227，192.168.37.237. MHA包版本：mha4mysql-manager-0.56-0.el6.noarch.rpm，mha4mysql-node-0.56-0.el6.noarch.rpm。
配置mysql主节点，创建具有复制权限的账号和MHA管理账号。

[root@localhost ~]# vim /etc/my.cnf
[mysqld]
server_id=217
log_bin
skip_name_resolve=1

MariaDB [(none)]> show master logs;
+--------------------+-----------+
| Log_name           | File_size |
+--------------------+-----------+
| mariadb-bin.000001 |       245 |
+--------------------+-----------+
1 row in set (0.00 sec)

MariaDB [(none)]> grant replication slave on *.* to repluser@'192.168.37.%' identified by 'password';
Query OK, 0 rows affected (0.01 sec)

MariaDB [(none)]> grant all on *.* to mhauser@'192.168.37.%' identified by 'password';
Query OK, 0 rows affected (0.00 sec)

配置mysql从节点，两个从节点配置文件只server_id项不一样，分别为227和237。

[root@localhost ~]# vim /etc/my.cnf
[mysqld]
server_id=227
log_bin
read_only
relay_log_purge=0           #不清楚中继日志
skip_name_resolve=1          #禁止域名解析

MariaDB [(none)]> change master to master_host='192.168.37.217',
    -> master_user='repluser',
    -> master_password='password',
    -> master_log_file='mariadb-bin.000001',
    -> master_log_pos=245;

设置所有节点之间基于ssh-key验证。

[root@localhost ~]# ssh-keygen                         #MHA管理节点
Generating public/private rsa key pair.
Enter file in which to save the key (/root/.ssh/id_rsa): 
Created directory '/root/.ssh'.
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:ohGK68b2VMe7PwIX8QqqHeBtBIfv3WyMtT9NJ0E+TS0 root@localhost.localdomain
The key's randomart image is:
+---[RSA 2048]----+
|  .           .  |
| o .   .   . E . |
|  + .   o o o .  |
| o + o.o . + .   |
|o * +.OoS   o    |
| o *.*.X.  o .   |
|o +.o +.. o o    |
|.=..   ..+ .     |
|o...   .o.o      |
+----[SHA256]-----+
[root@localhost ~]# ssh-copy-id 127.0.0.1
/usr/bin/ssh-copy-id: INFO: Source of key(s) to be installed: "/root/.ssh/id_rsa.pub"
The authenticity of host '127.0.0.1 (127.0.0.1)' can't be established.
ECDSA key fingerprint is SHA256:Q0nwDJMCBTw5qs9DZ84TRfyIPlnPDuAsCRP8arP/bJk.
ECDSA key fingerprint is MD5:02:56:23:9a:31:0c:43:34:4f:d9:5f:36:41:06:ac:77.
Are you sure you want to continue connecting (yes/no)? yes
/usr/bin/ssh-copy-id: INFO: attempting to log in with the new key(s), to filter out any that are already installed
/usr/bin/ssh-copy-id: INFO: 1 key(s) remain to be installed -- if you are prompted now it is to install the new keys
root@127.0.0.1's password: 

Number of key(s) added: 1

Now try logging into the machine, with:   "ssh '127.0.0.1'"
and check to make sure that only the key(s) you wanted were added.

[root@localhost ~]# scp -r .ssh/ root@192.168.37.217:/root/
[root@localhost ~]# scp -r .ssh/ root@192.168.37.227:/root/
[root@localhost ~]# scp -r .ssh/ root@192.168.37.237:/root/

在MHA管理节点安装MHA管理包和节点包，在mysql所有节点安装MHA-node包。

[root@localhost ~]# yum install mha4*               #MHA管理节点
[root@localhost ~]# yum install mha4mysql-node-0.58-0.el7.centos.noarch.rpm  -y     #所有mysql节点

配置MHA管理.

[root@localhost ~]# vim /etc/mastermha/mysql_test.cnf
[server default]
user=mhauser
password=password
manager_workdir=/data/mastermha/mysql_test/
manager_log=/data/mastermha/app1/manager.log
remote_workdir=/data/mastermha/mysql_test/
ssh_user=root
repl_user=repluser
repl_password=password
ping_interval=1


[server1]
hostname=192.168.37.217
candidate_master=1
[server2]
hostname=192.168.37.227
candidate_master=1
[server3]
hostname=192.168.37.237

检查配置,并启动MHA.

[root@localhost ~]# masterha_check_ssh --conf=/etc/mastermha/mysql_test.cnf
[root@localhost ~]# masterha_check_repl --conf=/etc/mastermha/mysql_test.cnf
[root@localhost ~]# masterha_manager --conf=/etc/mastermha/mysql_test.cnf
Mon Oct 19 17:47:22 2020 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Mon Oct 19 17:47:22 2020 - [info] Reading application default configuration from /etc/mastermha/mysql_test.cnf..
Mon Oct 19 17:47:22 2020 - [info] Reading server configuration from /etc/mastermha/mysql_test.cnf..

在主宕机,提升新的从为主时,需要手动修改配置文件去掉新的主read_only选项.MHA只是临时修改.