• MySQL 主主复制+Keepalived


    一、MySQL主主同步

    需要两台虚拟机192.168.200.111、192.168.200.112

    1、第1台机器:

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

    server-id=1
    log-bin=mysql-binlog
    log-slave-updates=true
    max_binlog_size=1024M
    auto_increment_offset = 1
    auto_increment_increment = 2
    replicate-ignore-db = information_schema
    replicate-ignore-db = performance_schema
    replicate-ignore-db = test
    replicate-ignore-db = mysql
    max_connections = 3000
    max_connect_errors = 30
    skip-character-set-client-handshake
    init-connect='SET NAMES utf8'
    character-set-server=utf8
    wait_timeout=1800
    interactive_timeout=1800
    sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
    relay-log=relay-log-bin
    relay-log-index=slave-relay-bin.index

    [root@localhost ~]# systemctl restart mariadb

    [root@localhost ~]# mysql

    MariaDB [(none)]> grant replication slave on *.* to 'repl'@'192.168.200.112' identified by '123456';

    Query OK, 0 rows affected (0.00 sec)

    MariaDB [(none)]> flush privileges;

    Query OK, 0 rows affected (0.00 sec)

     MariaDB [(none)]> show master status;

    2、第2台机器:

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

    [mysqld]

    server-id= 2

    log-bin=mysql-binlog

    log-slave-updates=true

    max_binlog_size=1024M

    auto_increment_offset = 2

    auto_increment_increment = 2#偶数ID

    replicate-ignore-db = information_schema

    replicate-ignore-db = performance_schema

    replicate-ignore-db = test

    replicate-ignore-db = mysql

    max_connections = 3000

    max_connect_errors = 30

    skip-character-set-client-handshake

    init-connect='SET NAMES utf8'

    character-set-server=utf8

    wait_timeout=1800

    interactive_timeout=1800

    sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

    relay-log=relay-log-bin

    relay-log-index=slave-relay-bin.index

    [root@localhost ~]# systemctl restart mariadb

    [root@localhost ~]# mysql

    MariaDB [(none)]> grant replication slave on *.* to 'repl'@'192.168.200.111' identified by '123456';

    Query OK, 0 rows affected (0.00 sec)

    MariaDB [(none)]> flush privileges;

    Query OK, 0 rows affected (0.00 sec)

    MariaDB [(none)]> show master status;

    3、测试:

    (1)第1台机器

    MariaDB [(none)]> stop slave;

    Query OK, 0 rows affected, 1 warning (0.00 sec)

    MariaDB [(none)]> change master to master_host='192.168.200.112',master_port=3306,master_user='repl',master_password='123',master_log_file='mysql-binlog.000001',master_log_pos=486;

    Query OK, 0 rows affected (0.04 sec)

    MariaDB [(none)]> start slave;

    Query OK, 0 rows affected (0.01 sec)

    MariaDB [(none)]> show slave statusG

    (2)第2台机器

    MariaDB [(none)]> stop slave;

    Query OK, 0 rows affected (0.01 sec)

    MariaDB [(none)]> change master to master_host='192.168.200.111',master_port=3306,master_user='repl',master_password='123',master_log_file='mysql-binlog.000004',master_log_pos=486;

    Query OK, 0 rows affected (0.13 sec)

    MariaDB [(none)]> start slave;

    Query OK, 0 rows affected (0.01 sec)

    MariaDB [(none)]> show slave statusG

    4、创建文件测试:

    第1台机器

    MariaDB [(none)]> create database test01;

    Query OK, 1 row affected (0.01 sec)

    第2台机器

    MariaDB [(none)]> show databases;                     //里面有在第一台机器上创建的表

    MariaDB [(none)]> create database test02;

    Query OK, 1 row affected (0.00 sec)

    第1台机器

    MariaDB [(none)]> show databases;                      //里面有在第二台机器上创建的表

     二、MySQL主主高可用方案

    1、111配置:

    [root@localhost ~]# yum -y install keepalived

    [root@localhost ~]# vim /etc/keepalived/keepalived.conf 

    ! Configuration File for keepalived

    global_defs {

       router_id LVS_MASTER-A

    }

    vrrp_script mysql {

        script "/opt/mysql.sh"

        interval 2

        weight -5                 

        fall 2                 

        rise 1

    }

    vrrp_instance VI_1 {

        state BACKUP

        interface ens32

        virtual_router_id 51

    priority 100

    nopreempt

        advert_int 1

        authentication {

            auth_type PASS

            auth_pass 1111

        }

        track_script {

            mysql

        }

        virtual_ipaddress {

            192.168.200.254

        }

    }

    2、写脚本:

    [root@localhost ~]# vim /opt/mysql.sh

    #!/bin/bash

    counter=$(netstat -na|grep "LISTEN"|grep "3306"|wc -l)

    if [ "${counter}" -eq 0 ]; then

        systemctl stop keepalived

    fi

    3、加权、启动、查看

    [root@localhost ~]# chmod +x /opt/mysql.sh

    [root@localhost ~]# systemctl start keepalived

    [root@localhost ~]# ip a | grep eno16777728

    4、112配置:

    在111上操作:

    [root@localhost ~]# scp /etc/keepalived/keepalived.conf 192.168.200.112:/etc/keepalived/

    [root@localhost ~]# scp /opt/mysql.sh 192.168.200.112:/opt/

    112:

    [root@localhost ~]# yum -y install keepalived

    [root@localhost ~]# vim /etc/keepalived/keepalived.conf                    //稍作调整

    ! Configuration File for keepalived

    global_defs {

       router_id LVS_MASTER-B

    }

    vrrp_script mysql {

        script "/opt/mysql.sh"

        interval 2

        weight -5                 

        fall 2                 

        rise 1

    }

    vrrp_instance VI_1 {

        state BACKUP

        interface ens32

        virtual_router_id 51

    priority 99

    nopreempt

        advert_int 1

        authentication {

            auth_type PASS

            auth_pass 1111

        }

        track_script {

            mysql

        }

        virtual_ipaddress {

            192.168.200.254

        }

    }

    [root@localhost ~]# chmod +x /opt/mysql.sh

    [root@localhost ~]# systemctl start keepalived

    5、测试VIP转移

    先用ip -a查看111上有没有254:

    然后停掉服务:

    [root@localhost ~]# systemctl stop mariadb

    112:ip -a      //发现254转移到了112上

    6、在远程客户端测试

    (1)111、112都进入mysql

    [root@localhost ~]# mysql

    MariaDB [(none)]> grant all on *.* to 'root'@'192.168.200.%' identified by '123456';

    MariaDB [(none)]> flush privileges;

    (2)通过VIP登录测试:

    [root@localhost ~]# mysql -uroot -p123 -h 192.168.200.254

    Welcome to the MariaDB monitor.  Commands end with ; or g.

    Your MariaDB connection id is 5796

    Server version: 5.5.56-MariaDB MariaDB Server

    Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.

    Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.

    MariaDB [(none)]> show databases;                            //里面有之前创建的表

  • 相关阅读:
    小学生都能学会的python(文件操作)
    小学生都能学会的python(深浅拷贝)
    小学生都能学会的python(小数据池)
    小学生都能学会的python(字典{ })
    小学生都能学会的python(列表[ ])
    小学生都能学会的python(编码 and 字符串)
    小学生都能学会的python(一)2018.9.3
    Ionic常用命令
    Ionic1.x项目中的Installing npm packages问题
    hdu1005
  • 原文地址:https://www.cnblogs.com/990624lty-jhc/p/11690649.html
Copyright © 2020-2023  润新知