美文网首页
mysql主主，生产

mysql主主，生产

作者: 小小的小帅 | 来源:发表于2020-10-30 18:24 被阅读0次
    • Linux中关闭SELinux的方法
      1、临时关闭：输入命令setenforce 0，重启系统后还会开启。
      2、永久关闭：输入命令vi /etc/selinux/config，将SELINUX=enforcing改为SELINUX=disabled，然后保存退出

    1.安装配置

    添加用户：adduser mysql
    设定密码：passwd mysql
    创建目录：(注意目录、用户)
    cd /usr/local/
    mkdir mysql
    添加权限：
    chown -R mysql /usr/local/mysql
    解压
    tar -xvf mysql-5.7.28-linux-glibc2.12-x86_64.tar.gz
    mv mysql-5.7.28-linux-glibc2.12-x86_64 /usr/local/mysql/mysql-5.7
    cd /usr/local/mysql
    mkdir data
    配置my.cnf: (root权限)
    vim /etc/my.cnf

    • 主节点配置
    [client]
socket=/usr/local/mysql/mysql.sock
[mysqld]
# 每个节点的server-id一定要设置不同  节点1-75，节点2-163
server-id = 78
port = 3306
user = mysql
bind_address = 0.0.0.0
skip_name_resolve = 1
basedir=/usr/local/mysql/mysql-5.7
datadir=/usr/local/mysql/data
socket=/usr/local/mysql/mysql.sock
log-error=/usr/local/mysql/mysql.err
pid-file=/usr/local/mysql/mysql.pid
explicit_defaults_for_timestamp=true
character-set-server=utf8
lower_case_table_names=1
max_connections=1000
max_connect_errors=10000
#根据服务器内存调整
innodb_buffer_pool_size=16G
back_log=900
open_files_limit=102400
thread_cache_size=128
table_open_cache=1024
innodb_buffer_pool_instances=4
innodb_flush_method=O_DIRECT
innodb_log_file_size=1073741824
#######replication settings########
master_info_repository = TABLE
relay_log_info_repository = TABLE
# MySQL复制是基于binlog日志的
log_bin = master-bin
sync_binlog = 1
log_slave_updates
binlog_format = row
relay_log = relay.log
relay_log_recovery = 1
slave_skip_errors = ddl_exist_errors
######semi sync replication settings########
# 设置插件目录路径
plugin_dir=/usr/local/mysql/mysql-5.7/lib/plugin
# 加载插件
plugin_load = "rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"
# 开启master semi sync replication
rpl_semi_sync_master_enabled = 1
# 开启slave semi sync replication
rpl_semi_sync_slave_enabled = 1
# 等待5秒无ack应答自动切换为异步模式
rpl_semi_sync_master_timeout = 5000
# 开启lossless replication
rpl_semi_sync_master_wait_point= AFTER_SYNC
# 至少有1个slave接收到日志
rpl_semi_sync_master_wait_for_slave_count = 1

    innodb_buffer_pool_size根据情况设置，server-id切记一点要修改

    [root@localhost mysql]# find / -name "semisync_master.so"
find: ‘/run/user/1000/gvfs’: Permission denied
/usr/local/mysql/mysql-5.7/lib/plugin/debug/semisync_master.so
/usr/local/mysql/mysql-5.7/lib/plugin/semisync_master.so
[root@localhost mysql]# find / -name "semisync_slave.so"
find: ‘/run/user/1000/gvfs’: Permission denied
/usr/local/mysql/mysql-5.7/lib/plugin/debug/semisync_slave.so
/usr/local/mysql/mysql-5.7/lib/plugin/semisync_slave.so

    初始化mysql (注意目录、用户)

    cd /usr/local/mysql/mysql-5.7/bin
./mysqld --defaults-file=/etc/my.cnf --basedir=/usr/local/mysql/mysql-5.7/ --datadir=/usr/local/mysql/data/ --user=mysql --initialize

    提示报错的话
    yum install libaio-devel.x86_64
    yum -y install numactl

    启动

    cp /usr/local/mysql/mysql-5.7/support-files/mysql.server /etc/init.d/mysql
    启动，mysql启动需要使用root权限，否则配置文件my.cnf会读取不到
    service mysql start

    登录

    cat /usr/local/mysql/mysql.err
cd /usr/local/mysql/mysql-5.7/bin
./mysql -u root -p 
#修改密码
SET PASSWORD = PASSWORD('handhand123');
ALTER USER 'root'@'localhost' PASSWORD EXPIRE NEVER;
FLUSH PRIVILEGES;  
修改远程可访问
use mysql;                                            #访问mysql库
update user set host = '%' where user = 'root';      #使root能再任何host访问
FLUSH PRIVILEGES;

    创建hips账号和数据库

    create user 'hips'@'%' identified by 'handhand123';
    GRANT all on hips.* TO 'hips'@'%' ;

    配置主主同步

    创建同步账号
create user 'slave'@'%' identified by 'HandSlave#2020';
GRANT REPLICATION SLAVE ON *.* TO 'slave'@'%' ;
记录两边binlog位置，执行
show master status;

+-------------------+----------+--------------+------------------+-------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------------+----------+--------------+------------------+-------------------+
| master-bin.000003 |     1901 |              |                  |                   |
+-------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

    在192.168.100.181上执行
change master to master_host='192.168.100.182', master_port=3306, master_user='slave', master_password='HandSlave#2020', master_log_file='master-bin.000003', master_log_pos=1901;
在192.168.100.182上执行
change master to master_host='192.168.100.181', master_port=3306, master_user='slave', master_password='HandSlave#2020', master_log_file='master-bin.000004', master_log_pos=154;
两台Mysql开户同步
start slave;
两个mysql查看同步状态
show slave status;
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

    keepalived 高可用

    1. yum安装：
      yum install keepalived -y
      vim /etc/keepalived/keepalived.conf
      182
    global_defs {
   router_id hips01
   script_user root
}

vrrp_script chkmysqld {
   script "/etc/keepalived/checkmysqld.sh"
   interval 5
   fall 3
   rise 1
}

vrrp_instance VI_1 {
#MASTER或者BACKUP,如果MASTER初始的权重没有BACKUP高，会被主动切换到BACKUP；
    state MASTER
#优先级高的设置nopreempt解决异常恢复后再次抢占的问题。
    nopreempt
#采用的接口网卡，注意后续所有的虚拟IP都需要在网卡支持的网段范围内，
                #比如centos默认是ens33，树莓派是eth0，用ifconfig查看网卡网段，如果网卡只有192.168的内网网段那么虚拟IP也必须要用这个网段
    interface eno16777736
#设置VRID标记，不同服务集群内不能重复
    virtual_router_id 71
#权重，越高越容易被选为主 
    priority 101
    authentication {
        auth_type PASS
        auth_pass 2222
    }
    track_script {
        chkmysqld
    }
    virtual_ipaddress {
        192.168.100.183/16
    }
}

    182

    global_defs {
   router_id hips02
   script_user root
}

vrrp_script chkmysqld {
   script "/etc/keepalived/checkmysqld.sh"
   interval 5
   fall 3
   rise 1
}

vrrp_instance VI_1 {
    state BACKUP
    interface eno16777736
    virtual_router_id 71
    priority 100
    mcast_src_ip  192.168.100.182
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass 2222
    }
    track_script {
        chkmysqld
    }
    virtual_ipaddress {
        192.168.100.83
    }
}

    181

    global_defs {
   router_id hips01
   script_user root
}

vrrp_script chkmysqld {
   script "/etc/keepalived/checkmysqld.sh"
   interval 5
   fall 3
   rise 1
}

vrrp_instance VI_1 {
    state MASTER
    interface eno16777736
    virtual_router_id 71
    priority 101
    mcast_src_ip  192.168.100.181
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass 2222
    }
    track_script {
        chkmysqld
    }
    virtual_ipaddress {
        192.168.100.83
    }
}
    1. mysql检查脚本
      vim /etc/keepalived/checkmysqld.sh
    #!/bin/bash
mysqlhost=localhost
user=slave
password='HandSlave#2020'
RESULT=$(/usr/local/mysql/mysql-5.7/bin/mysql -h $mysqlhost -u $user -p$password -N -s -e "select 1")
echo $RESULT

if [ "${RESULT}" == "1" ]; then
  exit 0
else
service mysql start
  exit 1
fi
    1. 启动keepalived
    chmod a+x /etc/keepalived/checkmysqld.sh
systemctl enable keepalived
systemctl start keepalived
查看VIP
ip a
测试使用VIP连接数据库
mysql -h192.168.100.83 -uroot -p

    keepalived主、从：


    image.png

    keepalived的日志文件路径：
    tail -f /var/log/messages

    Nov  1 03:26:54 ip-192-168-100-181 Keepalived_vrrp[14150]: Sending gratuitous ARP on ens5 for 192.168.100.83

    相关文章

      网友评论

          本文标题：mysql主主，生产

          本文链接：https://www.haomeiwen.com/subject/pwldvktx.html

          延伸阅读

          深度阅读

          • 您也可以注册成为美文阅读网的作者，发表您的原创作品、分享您的心情！

          栏目导航

          热点阅读

          关于我们|服务条款|联系我们|mysql主主，生产|投稿指南|网站地图|RSS订阅|排版工具|手机版

          提供经典美文摘抄,优美散文欣赏,现代诗歌精选,短篇小说,心情随笔,表白情书范文,故事会在线阅读欣赏

          Copyright © 2014-2023 Haomeiwen.com All Rights Reserved. 好美文阅读网 版权所有

          备案信息：桂公网安备 45052102000051号 · 桂ICP备13007215号-3

          本站所收录作品、热点评论等信息部分来源互联网，目的只是为了系统归纳学习和传递资讯

          所有作品版权归原创作者所有，与本站立场无关，如不慎侵犯了你的权益，请联系我们告知，我们将做删除处理！