数据库的主从以及高可以用的实现

最新推荐文章于 2024-05-20 09:02:22 发布

疯狂的小企鹅

最新推荐文章于 2024-05-20 09:02:22 发布

阅读量186

点赞数

分类专栏： mairadb 文章标签： linux 数据库 mariadb

本文链接：https://blog.csdn.net/weixin_44232712/article/details/109142511

版权

mairadb 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

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

#!/bin/bash
#
. /etc/init.d/functions
username=root
passname=123456
PS3="Please input a number[1|2]:  "
mysqldumpdir=/mysqldumpdir
xtrabackupdir=/xtrabackupdir-`date +"%F-%T"`
[ -d $mysqldumpdir ] || mkdir $mysqldumpdir
select backup_method in mysqldump xtrabackup;do
    case $backup_method in
        mysqldump)
            mysqldump -u${username} -p${passname} -A -F -E -R -q \
            --single-transaction --master-data=1 --flush-privileges \
            --triggers --default-character-set=utf8 \
            > $mysqldumpdir/`date +"%F-%T"`.sql && \
            action "`date +"%F-%T"` data backup to $mysqldumpdir" true || action "backup data" false
            break
            ;;
        xtrabackup)
            xtrabackup --user=$username --password=$passname \
            --backup --target-dir=$xtrabackupdir  \
            &> /dev/null && action "`date +"%F-%T"` data backup to $xtrabackupdir" true \
            || action "backup data" false
            break
            ;;
        *)
            echo "not support"
            ;;
    esac
done

2、配置Mysql主从同步

基于mysql5.7
master： 192.168.43.9
slave： 192.168.43.29
二进制包：/usr/local/src/mysql-5.7.28-linux-glibc2.12-x86_64.tar.gz

配置 MySQL master

vim /scripts/install_mysql5.7.sh

#!/bin/bash                                                                                                                                                                                  
#
. /etc/init.d/functions
SRC_DIR=`pwd`
MYSQL='mysql-5.7.28-linux-glibc2.12-x86_64.tar.gz'
COLOR="echo -e \\033[01;31m"
END='\033[0m'
MYSQL_ROOT_PASSWORD=123456
[ -d /data ] || mkdir /data
check (){
cd  $SRC_DIR
if [ !  -e $MYSQL ];then
        $COLOR"缺少${MYSQL}文件"$END
        $COLOR"请将相关软件放在${SRC_DIR}目录下"$END
        exit
elif [ -e /usr/local/mysql ];then
        action "数据库已存在，安装失败" false
        exit
else
    return
fi
}

install_mysql(){
    $COLOR"开始安装MySQL数据库..."$END
     yum  -y -q install numactl-libs   libaio mariadb &> /dev/null
    cd $SRC_DIR
    tar xf $MYSQL -C /usr/local/
    MYSQL_DIR=`echo $MYSQL| sed -nr 's/^(.*[0-9]).*/\1/p'`
    ln -s  /usr/local/$MYSQL_DIR /usr/local/mysql
    chown -R  root.root /usr/local/mysql/
    id mysql &> /dev/null || { useradd -s /sbin/nologin -r  mysql ; action "创建mysql用户"; }

    echo 'PATH=/usr/local/mysql/bin/:$PATH' > /etc/profile.d/mysql.sh
    .  /etc/profile.d/mysql.sh
    cat > /etc/my.cnf <<-EOF
[mysqld]
server-id=9
log-bin
datadir=/data/mysql
socket=/data/mysql/mysql.sock                                                                                                   
log-error=/data/mysql/mysql.log
pid-file=/data/mysql/mysql.pid

[client]
socket=/data/mysql/mysql.sock
EOF
    mysqld --initialize --user=mysql --datadir=/data/mysql
    cp /usr/local/mysql/support-files/mysql.server  /etc/init.d/mysqld
    chkconfig --add mysqld
    chkconfig mysqld on
    service mysqld start
    [ $? -ne 0 ] && { $COLOR"数据库启动失败，退出!"$END;exit; }
    MYSQL_OLDPASSWORD=`awk '/A temporary password/{print $NF}' /data/mysql/mysql.log`
    mysqladmin  -uroot -p$MYSQL_OLDPASSWORD password $MYSQL_ROOT_PASSWORD &>/dev/null
    action "数据库安装完成"
}

check
install_mysql

运行脚本
在这里插入图片描述
配置

sed -i '/\[mysqld\]/a\gtid_mode=on\nenforce_gtid_consistency' /etc/my.cnf
service mysqld restart

mysql -uroot -p123456 -e "grant replication slave on *.* to repluser@'192.168.43.%' identified by 'centos';flush privileges;"

配置 MySQL slave

scp 192.168.43.9:/usr/local/src/mysql-5.7.28-linux-glibc2.12-x86_64.tar.gz /usr/local/src/
scp 192.168.43.9:/scripts/install_mysql5.7.sh /usr/local/src/

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

主机：4台，一台MHA Manager (192.168.27.7)，三台主从复制架构(一主多从)，master (192.168.27.17)，slave1 (192.168.27.27)，slave2 (192.168.27.37)

系统：CentOS7.6

数据库：mariadb-server-5.5.60（光盘yum源）

MHA软件包：mha4mysql-manager-0.56，mha4mysql-node-0.56

1、三台主从节点主点先安装mariadb数据库

[root@master ~]# yum install -y mariadb-server
[root@slave1 ~]# yum install -y mariadb-server
[root@slave2 ~]# yum install -y mariadb-server

2、准备MHA软件包，MHA Manager 节点需要 mha4mysql-manager 和

mha4mysql-node，其它三个节点只需要 mha4mysql-node
[root@MHAServer ~]# ll mha4mysql*
-rw-r--r-- 1 root root 87119 Dec  9 10:14 mha4mysql-manager-0.56-0.el6.noarch.rpm
-rw-r--r-- 1 root root 36326 Dec  9 10:14 mha4mysql-node-0.56-0.el6.noarch.rpm
[root@master ~]# ll mha4mysql*
-rw-r--r-- 1 root root 36326 Dec  9 10:14 mha4mysql-node-0.56-0.el6.noarch.rpm
[root@slave1 ~]# ll mha4mysql*
-rw-r--r-- 1 root root 36326 Dec  9 10:14 mha4mysql-node-0.56-0.el6.noarch.rpm
[root@slave2 ~]# ll mha4mysql*
-rw-r--r-- 1 root root 36326 Dec  9 10:14 mha4mysql-node-0.56-0.el6.noarch.rpm

3、安装MHA软件包，MHA Manager 节安装 mha4mysql-manager 时需要依赖 epel 源，这里使用阿里云的epel源

[root@MHAServer ~]# cat /etc/yum.repos.d/base.repo 
[development]
name=dvdbase repo
baseurl=file:///mnt/cdrom/
enabled=1
gpgcheck=1
gpgkey=file:///mnt/cdrom/RPM-GPG-KEY-CentOS-7

[aliyunEpel]
name=aliyun epel
baseurl=https://mirrors.aliyun.com/epel/$releasever/$basearch
enabled=1
gpgcheck=1
gpgkey=https://mirrors.aliyun.com/epel/RPM-GPG-KEY-EPEL-$releasever

[root@MHAServer ~]# yum install -y mha4mysql*
[root@master ~]# yum install -y mha4mysql-node-0.56-0.el6.noarch.rpm 
[root@slave1 ~]# yum install -y mha4mysql-node-0.56-0.el6.noarch.rpm 
[root@slave2 ~]# yum install -y mha4mysql-node-0.56-0.el6.noarch.rpm

4、配置数据库的主从架构，一主多从
(1)、master节点

[root@master ~]# vim /etc/my.cnf

[mysqld]
server-id=1
log-bin
skip_name_resolve=1
...以下省略

[root@master ~]# systemctl start mariadb
[root@master ~]# mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 2
Server version: 5.5.60-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 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.27.%' identified by 'centos';
Query OK, 0 rows affected (0.00 sec)

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

(2)、slave节点，两台节点的配置基本一样（server-id记得修改成不同的值），以slave1(192.168.214.27)为例

root@slave1 ~]# vim /etc/my.cnf

[mysqld]
server-id=2
log-bin
read-only
relay_log_purge=0
skip_name_resolve=1
...以下省略

[root@slave1 ~]# systemctl start mariadb
[root@slave1 ~]# mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 2
Server version: 5.5.60-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='192.168.27.17',
    -> MASTER_USER='repluser',
    -> MASTER_PASSWORD='centos',
    -> MASTER_LOG_FILE='mariadb-bin.000001',
    -> MASTER_LOG_POS=245;
Query OK, 0 rows affected (0.02 sec)

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

MariaDB [(none)]> show slave status\G;

[root@MHAServer ~]# ssh-keygen 
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:KpRQdsCGWlwJfdqlWDzqH7auMTO+OWxBX8m7Hh3rA/8 root@centos7.localdomain
The key's randomart image is:
+---[RSA 2048]----+
| ..*=+.          |
|  +o=.= .        |
| o.. B = .       |
|.  .=.o +        |
|   oo. .S..      |
|   .o +.o. o     |
|   .*+.o.+o      |
|   .+Bo .oo      |
|   .==... .oE    |
+----[SHA256]-----+
[root@MHAServer ~]# ssh-copy-id -i /root/.ssh/id_rsa.pub root@192.168.27.7
[root@MHAServer ~]# scp -r .ssh root@192.168.27.17:/root/
[root@MHAServer ~]# scp -r .ssh root@192.168.27.27:/root/
[root@MHAServer ~]# scp -r .ssh root@192.168.27.37:/root/

6、配置MHA Manager节点的配置文件

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

[server1]
hostname=192.168.27.17
candidate_master=1

[server2]
hostname=192.168.27.27
candidate_master=1

[server3]
hostname=192.168.27.37

7、验证与启动

[root@MHAServer ~]# masterha_check_ssh --conf=/etc/mha/mha.cnf　　#验证ssh
[root@MHAServer ~]# masterha_check_repl --conf=/etc/mha/mha.cnf　　#验证主从复制
[root@MHAServer ~]# masterha_manager --conf=/etc/mha/mha.cnf　　#启动

8、测试当mater节点断开时，主从的切换

[root@master ~]# systemctl stop mariadb    #关闭主节点服务

[root@slave1 ~]# mysql    #在slave1上查看
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 21
Server version: 5.5.60-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 slave status\G;   #查询为空，说明slave1节点已提升为主节点
Empty set (0.00 sec)

MariaDB [(none)]> show variables like 'read_only';    #slave1节点的read-only选项也关闭了
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| read_only     | OFF   |
+---------------+-------+
1 row in set (0.00 sec)

[root@slave2 ~]# mysql    #在slave1上查看
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 23
Server version: 5.5.60-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 slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.27.27    #主节点变为了slave1
                  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

疯狂的小企鹅

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
1
评论
数据库的主从以及高可以用的实现

1、编写脚本，支持让用户自主选择，使用mysqldump还是xtraback全量备份。#!/bin/bash#. /etc/init.d/functionsusername=rootpassname=123456PS3="Please input a number[1|2]: "mysqldumpdir=/mysqldumpdirxtrabackupdir=/xtrabackupdir-`date +"%F-%T"`[ -d $mysqldumpdir ] || mkdir $mysql
复制链接

扫一扫